mysql学习笔记之备份与恢复

5 .备份恢复

相关关键词：

策略规划，恢复演练，还原与恢复

恢复点目标（PRO）：容忍丢失多少数据

恢复时间目标（RTO）：需要多久将数据恢复

备份理由：灾难恢复，错误修改，审计，测试

5.1 逻辑备份与恢复

5.1.1 备份

##查看帮助mysqldump --help##备份某个表mysqldump -uroot -h227.0.0.1 -p'password'  sakila test > sakila_test.sql##备份某个数据库mysqldump -uroot -p'password' -h227.0.0.1 --single-transaction --triggers --routines --databases sakila > sakila.sql##备份所有数据库mysqldump --single-transaction --quick --flush-logs --master-data=2 --all-databases --triggers --routines --events --set-gtid-purged=off  > backup.sql

5.1.2 恢复

1. 完全恢复

##查看当前二进制日志mysql> show master status;##备份时切换日志，并记录后续日志，如新日志从mysql-bin.000007 开始##读取增量的二进制日志mysqlbinlog --skip-gtids mysql-bin.000007 > bin7.sqlmysqlbinlog --skip-gtids mysql-bin.000008 > bin8.sql##完全恢复mysql> source backup.sql;mysql> source bin7.sql;mysql> source bin8.sql;

2. 基于时间点的恢复

shell> mysqlbinlog --stop-date="2019-09-11 9:59:59" mysql-bin.000007 > bin7_pos1.sqlshell> mysqlbinlog --start-date="2019-09-11 10:01:01" mysql-bin.000007 > bin7_pos2.sqlshell> mysqlbinlog --skip-gtids mysql-bin.000008 > bin8.sqlmysql> source backup.sql;mysql> source bin7_pos1.sql;mysql> source bin7_pos2.sql;mysql> source bin8.sql;

3. 基于位置的恢复

shell>mysqlbinlog --start-date="2019-09-11 9:59:59" --stop-date="2019-09-11 10:01:01"  mysql-bin.000007 >bin7_pos.sql#找到位置号，如3601  和 3609shell> mysqlbinlog --stop-position="3601" mysql-bin.000007 > bin7_pos1.sqlshell> mysqlbinlog --start-position="3609" mysql-bin.000007 > bin7_pos2.sqlmysql> source backup.sql;mysql> source bin7_pos1.sql;mysql> source bin7_pos2.sql;mysql> source bin8.sql;

5.1.3 mysqlpump备份与恢复

```shell 略

## 5.2 物理备份### 5.2.1 Xtrabackup安装1.添加源```shellyum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm##检查yum list|grep  percona-xtrabackup-24.x86_64

2.安装

##缺少依赖包libev.so.4的处理rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm##安装rpm -ivh percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm

5.2.2 备份原理

5.2.3 全量热备份

##创建备份用户create user 'backup'@'%' identified by 'Bk_123456';grant reload,lock tables,replication client,create tablespace,process,super on *.* to 'backup'@'%';

##普通备份innobackupex --default-file=/data/mysql/3306/my3306.cnf  --host=192.168.56.16 --user=backup --password='Bk_123456'  --datadir=/data/mysql/3306/data  --port=3306 --slave-info --no-timestamp  /home/mysql/backup/dbfull_3306_`date '+%Y%m%d_%H%M'`##流式压缩备份innobackupex --default-file=/data/mysql/3306/my3306.cnf  --host=192.168.56.16 --user=backup --password='Bk_123456'  --datadir=/data/mysql/3306/data  --port=3306   --no-timestamp --stream=xbstream -> /home/mysql/backup/backup.xbstream.`date '+%Y%m%d_%H%M'`

##加密备份innobackupex --default-file=/data/mysql/3306/my3306.cnf  --host=192.168.56.16 --user=backup --password='Bk_123456'  --datadir=/data/mysql/3306/data  --port=3306 --encrypt=AES256 --encrypt-threads=10 --encrypt-key=111111111111111111111111 --encrypt-chunk-size 512 --no-timestamp --stream=xbstream -> /home/mysql/backup/backup.xbstream.encrypt.`date '+%Y%m%d_%H%M'`

5.2.4 增量热备份

##普通增量innobackupex --defaults-file=/data/mysql/3306/my3306.cnf   --host=192.168.56.16 --user=root --password='password'  --datadir=/data/mysql/3306/data --incremental-basedir=/data/mysql/backup/2019-09-11_16-47-59 --incremental /data/mysql/backup/increment##流式加密增量innobackupex --defaults-file=/data/mysql/3306/my3306.cnf   --host=192.168.56.16 --user=root --password='password'  --datadir=/data/mysql/3306/data --incremental-basedir=/data/mysql/backup/2019-09-11_16-47-59 --incremental --compress --compress-threads=10 --encrypt=AES256 --encrypt-threads=10 --encrypt-key=111111111111111111111111 --stream=xbstream ./ > /data/mysql/backup/increment/increment.stream

如果是第二次增量，-incremental-basedir= 上次增量的目录

5.3 物理恢复

5.3.1 全量备份的恢复

##流式解压mkdir /home/tempxbstream -x < /data/mysql/backup/backup.xbstream -C /home/tempinnobackupex --decompress --decrypt=AES256 --encrypt-key=1111111111111111111111111 /home/tempinnobackupex --copy-back  --defaults-file=/data/mysql/3306/my3306.cnf /home/temp##应用redoinnobackupex --host=192.168.56.16 --user=backup --password='Bk_123456' --apploy-log  /home/mysql/backup/2019-09-12_10-18-57##恢复全备（关闭数据库，清空数据目录）innobackupex --default-file=/data/mysql/3306/my3306.cnf --datadir=/data/mysql/3306/data  --copy-back  /home/mysql/backup/2019-09-12_10-18-57chown -R mysql:mysql /data/mysql

5.3.2 增量备份的恢复

##innobackupex --host=192.168.56.16 --user=backup --password='Bk_123456' --apply-log --redo-only  /home/mysql/backup/2019-09-12_10-18-57##innobackupex --host=192.168.56.16 --user=backup --password='Bk_123456' --apply-log --redo-only  /home/mysql/backup/2019-09-12_10-18-57 --incremental-dir=/data/mysql/backup/increment/2019-09-12_09-11-23 ##innobackup --host=192.168.56.16 --user=backup --password='Bk_123456' --apply-log  /home/mysql/backup/2019-09-12_10-18-57 --incremental-dir=/data/mysql/backup/increment/2019-09-13_09-11-50 ##恢复全备（关闭数据库，清空数据目录）innobackupex --default-file=/data/mysql/3306/my3306.cnf --datadir=/data/mysql/3306/data  --copy-back  /home/mysql/backup/2019-09-12_10-18-57chown -R mysql:mysql /data/mysql