文章 78
评论 0
浏览 8365
7-MySQL备份和恢复

7-MySQL备份和恢复

5 备份和恢复

5.1 备份恢复概述

5.1.1 为什么要备份

灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

5.1.2 备份类型

  • 完全备份,部分备份
    • 完全备份:整个数据集
    • 部分备份:只备份数据子集,如部分库或表
  • 完全备份、增量备份、差异备份
    • 增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
      clipboard.png

差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单

clipboard.png

注意:二进制日志文件不应该与数据文件放在同一磁盘

  • 冷、温、热备份
    • 冷备:读、写操作均不可进行,数据库停止服务
    • 温备:读操作可执行;但写操作不可执行
    • 热备:读、写操作均可执行
      • MyISAM:温备,不支持热备
      • InnoDB:都支持
  • 物理和逻辑备份
    • 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
    • 逻辑备份:从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

5.1.3 备份什么

  • 数据
  • 二进制日志、InnoDB的事务日志
  • 用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
  • 服务器的配置文件

5.1.4 备份注意要点

  • 能容忍最多丢失多少数据
  • 备份产生的负载
  • 备份过程的时长
  • 温备的持锁多久
  • 恢复数据需要在多长时间内完成
  • 需要备份和恢复哪些数据

5.1.5 还原要点

  • 做还原测试,用于测试备份的可用性
  • 还原演练,写成规范的技术文档

5.1.6 备份工具

  • cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
  • LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
  • mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
  • xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
  • MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
  • mysqlbackup:热备份, MySQL Enterprise Edition 组件
  • mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

5.1.7 基于 LVM 的快照备份

(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷,执行数据备份
(6) 备份完成后,删除快照卷
(7) 制定好策略,通过原卷备份二进制日志

5.1.8 实战案例:数据库冷备份和还原

#在目标服务器(192.168.10.82)安装mariadb-server,不启动服务
#在源主机(192.168.10.81)执行
[10:21:40 root@centos8 ~]#systemctl stop mariadb.service
#复制相关文件,如有二进制日志也需要复制
[10:29:58 root@centos8 ~]#scp -r /var/lib/mysql/* 192.168.10.82:/var/lib/mysql
[10:29:58 root@centos8 ~]#scp /etc/my.cnf.d/mariadb-server.cnf 192.168.10.82:/etc/my.cnf.d/
#在目标主机(10.0.0.18)执行
[10:32:02 root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/
[10:32:11 root@centos8 ~]#systemctl start mariadb.service

5.2 mysqldump 备份工具

5.2.1 mysqldump 说明

逻辑备份工具:

mysqldump, mydumper, phpMyAdmin

Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份

命令格式:

mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份,但数据库本身定
义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库,包含数据库本身定义也会备份

mysqldump参考:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump 常见通用选项:

-A, --all-databases         #备份所有数据库,含create database
-B, --databases db_name…    #指定备份的数据库,包括create database语句
-E, --events:              #备份相关的所有event scheduler
-R, --routines:            #备份所有存储过程和自定义函数
--triggers:                #备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=    #指定字符集
--master-data[=#]:         #此选项须启用二进制日志
#1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1,适合于主从复
制多机使用
#2:记录为被注释的#CHANGE MASTER TO语句,适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--
single-transaction)
-F, --flush-logs          #备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文
件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--
single-transaction或-x,--master-data 一起使用实现,此时只刷新一次二进制日志
--compact        #去掉注释,适合调试,节约备份占用的空间,生产不使用
-d, --no-data    #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db #不备份create database,可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force       #忽略SQL错误,继续执行
--hex-blob        #使用十六进制符号转储二进制列,当有包括BINARY, VARBINARY,
BLOB,BIT的数据类型的列时使用,避免乱码
-q, --quick     #不缓存查询,直接输出,加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项:

MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作

-x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--
lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库

-l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--
skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项:

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP
TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选
项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用

5.2.2 生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob

> ${BACKUP}/fullbak_${BACKUP_TIME}.sql

5.2.3 mysqldump 备份还原实战案例

5.2.3.1 实战案例:特定数据库的备份脚本

TIME=`date +%F_%H-%M-%S`
DIR=/mysql/backup
DB=hellodb
PASS=123456

mysqldump -uroot -p${PASS} -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B ${DB} | gzip > ${DIR}/${DB}_${TIME}.sql.gz

5.2.3.2 实战案例:分库备份并压缩,演示思路切勿对真实环境使用

#for循环写法
[11:28:05 root@centos8 ~]#for db in `mysql -uroot -p123456 -e 'show databases;' | grep -Ev '^(information_schema|performance_schema|Database)$'`;do mysqldump -uroot -p123456 -B $db | gzip >/root/mysql/$db.sql.gz;done
#while循环写法
[11:30:04 root@centos8 ~]#mysql -uroot -p123456 -e 'show databases;' | grep -Ev '^(information_schema|performance_schema|Database)$' | while read db;do mysqldump -uroot -p123456 -B $db | gzip >/root/mysql/while_$db.sql.gz;done
#sed写法1
[11:36:17 root@centos8 ~]#mysql -uroot -p123456 -e 'show databases;' | grep -Ev '^(information_schema|performance_schema|Database)$' | sed -nr 's#(.*)#mysqldump -uroot -p123456 -B \1 | gzip >/root/mysql/sed_\1.sql.gz#p' | bash
#sed写法2
[11:40:54 root@centos8 ~]#mysql -uroot -p123456 -e 'show databases' | sed -nr '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -uroot -p123456 -B \1 | gzip >/root/mysql/sed2_\1.sql.gz#p' | bash

5.2.3.3 实战案例:分库备份的实战脚本

TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456

[ -d "$DIR" ] || mkdir $DIR

for DB in `mysql -uroot -p"$PASS" -e "show databases" | grep -Ev "^(Database|.*schema|sys)$"`;do
mysqldump -uroot -p"$PASS" -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done

5.2.3.4 实战案例:完全备份和还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份
[19:26:31 root@centos8 backup]#mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 | gzip >/backup/all-`date +%F`.sql.gz
#还原
[root@centos8 backup]#dnf install mysql-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
mysql> set sql_log_bin=off;
mysql> source /root/all-2021-03-05.sql
mysql> set sql_log_bin=on;

注意:恢复数据时推荐先关闭二进制日志,恢复完成后再次打开

5.2.3.5 实战案例:利用二进制日志,还原数据库最新状态

#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin
#完全备份,并记录备份的二进制位置
[19:35:11 root@centos8 ~]#mysqldump -uroot -p123456 -A -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip >/backup/all-`date +%F`.sql.gz
#修改数据库
mysql> insert into students (name,age,gender)values('zhang',20,'M');
mysql> insert into students (name,age,gender)values('zhang',30,'M');
#损坏数据库
[19:39:06 root@centos8 ~]#rm -rvf /var/lib/mysql/*
#还原
[19:39:06 root@centos8 ~]#cd /backup/
[19:39:31 root@centos8 backup]#gzip -d all-2021-03-05.sql.gz
#CentOS 7需要事先生成数据库相关文件,CentOS8 不需要执行此步
mysql_install_db  --user=mysql
systemctl restart mariadb
#如果启动不了,再次清理/var/lib/mysql/目录中的文件

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       203 | No        |
| mysql-bin.000002 |       203 | No        |
| mysql-bin.000003 |       203 | No        |
| mysql-bin.000004 |       203 | No        |
| mysql-bin.000005 |       789 | No        |
| mysql-bin.000006 |       179 | No        |
| mysql-bin.000007 |       156 | No        |
+------------------+-----------+-----------+
7 rows in set (0.00 sec)

mysql> set sql_log_bin=off;
mysql> source /backup/all-2021-03-05.sql

[19:49:44 root@centos8 ~]#grep '^-- CHANGE MASTER TO' /backup/all-2021-03-05.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156;

#二进制日志的备份
[19:50:37 root@centos8 mysql_log_bin]#mysqlbinlog mysql-bin.000005 --start-position=156 >/backup/inc.sql
mysql> source /backup/inc.sql;
mysql> set sql_log_bin=on;

标题:7-MySQL备份和恢复
作者:Carey
地址:HTTPS://zhangzhuo.ltd/articles/2021/03/05/1614946989110.html

生而为人

取消