CentOS7 MySQL 增量备份与恢复
一、MySQL 增量备份与恢复概述
1.增量备份的特点
2.MySQL 二进制日志对备份的意义
二、MySQL 增量备份与恢复三种方式
1.一般备份与恢复
2.基于位置的恢复
3.基于时间的恢复
一、MySQL 增量备份与恢复概述
- 使用 mysqldump 进行完全备份，备份的数据中有重复的数据，备份时间与恢复时间长；
- 而增量备份就是备份自上一次备份之后的增加或改变的文件或内容。
1.增量备份的特点
- 与完全备份不同，增量备份没有重复的数据，备份量不大，时间短；
- 但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复；
- 而且要对所有增量备份进行逐个反推恢复，MySQL 没有提供直接的增量备份办法，可以通过 MySQL 二进制日志（binary log）间接实现增量备份。
2.MySQL 二进制日志对备份的意义
二进制日志保存了所有更新数据库的操作。二进制日志在启动 MySQL 服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到 flush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。
- 使用 max_binlog_size 配置项可以设置二进制日志文件的最大值，达到最大值，它就会自动创建新的二进制文件。
二、MySQL 增量备份与恢复三种方式
准备工作：
- 三种增量备份方式都需要有完全备份。
1.一般备份与恢复
1）先录入样本
[root@MySQL ~]# mysql -uroot -p123 # 登录MySQL数据库 mysql> create database zhangsan; # 创建一个名为 zhangsan 的库 mysql> use zhangsan # 进入 zhangsan 库 mysql> create table 成绩表(姓名 char(9),成绩 char(9)); # 创建一个成绩表 mysql> insert into 成绩表 values('张三','88'); # 往成绩表里写入数据 mysql> insert into 成绩表 values('李四','77'); # 往成绩表里写入数据 mysql> select * from 成绩表; # 查看成绩表中数据
2）进行完全备份
[root@MySQL ~]# mkdir /mysql.bak [root@MySQL ~]# mysqldump -uroot -p123 zhangsan 成绩表 > /mysql.bak/zhangsan_成绩表-$(date +%F).sql [root@MySQL ~]# ls -l /mysql.bak/
3）开启日志文件
[root@MySQL ~]# vim /usr/local/mysql/my.cnf 在 [mysqld] 下添加： log_bin=/usr/local/mysql/mysql-bin
4）重启服务
[root@MySQL ~]# systemctl restart mysqld [root@MySQL ~]# netstat -anpt | grep mysqld
5）查看生成的日志文件
[root@MySQL ~]# ll /usr/local/mysql/mysql-bin.* # 查看生成的日志文件 [root@MySQL ~]# mysqladmin -uroot -p123 flush-logs # 刷新日志 [root@MySQL ~]# ll /usr/local/mysql/mysql-bin.* # 再次查看生成的日志文件
6）再次录入样本
[root@MySQL ~]# mysql -uroot -p123 mysql> use zhangsan; mysql> insert into 成绩表 values('王五','66'); mysql> insert into 成绩表 values('赵六','55'); mysql> select * from 成绩表;
7）进行增量备份
[root@MySQL ~]# mysqladmin -uroot -p123 flush-logs # 刷新日志 [root@MySQL ~]# ls -l /usr/local/mysql/mysql-bin.* # 查看生成的日志文件 [root@MySQL ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql.bak/
8）模拟故障，删除成绩表
[root@MySQL ~]# mysql -uroot -p123 -e 'drop table zhangsan.成绩表;' [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
9）先恢复完全备份，再进行增量恢复
先恢复完全备份： [root@MySQL ~]# mysql -uroot -p123 zhangsan < /mysql.bak/zhangsan_成绩表-2021-02-17.sql [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;' 再增量恢复： [root@MySQL ~]# mysqlbinlog --no-defaults /mysql.bak/mysql-bin.000002 | mysql -uroot -p123 [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
2.基于位置的恢复
- 模拟故障，删除成绩表。
[root@MySQL ~]# mysql -uroot -p123 -e 'drop table zhangsan.成绩表;' [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
查看二进制文件来根据位置恢复：
[root@MySQL ~]# mysqlbinlog --no-defaults /mysql.bak/mysql-bin.000002 | grep -v "/"
1）先恢复完全备份，再进行基于停止位置恢复
- 指定 停止位置，即不恢复 赵六 的用户数据。
先恢复完全备份： [root@MySQL ~]# mysql -uroot -p123 zhangsan < /mysql.bak/zhangsan_成绩表-2021-02-17.sql [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;' 再指定停止位置恢复数据： [root@MySQL ~]# mysqlbinlog --no-defaults --stop-position='450' /mysql.bak/mysql-bin.000002 | mysql -uroot -p123 [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
- 再次模拟故障，指定起始位置：即只恢复 赵六 的数据，跳过 王五 的数据。
[root@MySQL ~]# mysql -uroot -p123 -e 'drop table zhangsan.成绩表;' [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
2）先恢复完全备份，再进行基于起始位置恢复
先恢复完全备份： [root@MySQL ~]# mysql -uroot -p123 zhangsan < /mysql.bak/zhangsan_成绩表-2021-02-17.sql [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;' 再指定起始位置恢复数据： [root@MySQL ~]# mysqlbinlog --no-defaults --start-position='450' /mysql.bak/mysql-bin.000002 | mysql -uroot -p123 [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
3.基于时间的恢复
- 模拟故障，删除成绩表。
[root@MySQL ~]# mysql -uroot -p123 -e 'drop table zhangsan.成绩表;' [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
查看二进制文件来根据时间恢复
[root@MySQL ~]# mysqlbinlog --no-defaults /mysql.bak/mysql-bin.000002 | grep -v "/"
1）先恢复完全备份，再指定结束时间进行恢复
[root@MySQL ~]# mysql -uroot -p123 zhangsan < /mysql.bak/zhangsan_成绩表-2021-02-17.sql [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;' [root@MySQL ~]# mysqlbinlog --no-defaults --stop-datetime='2021-02-18 0:25:19' /mysql.bak/mysql-bin.000002 | mysql -uroot -p123 [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
再次模拟故障
[root@MySQL ~]# mysql -uroot -p123 -e 'drop table zhangsan.成绩表;' [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
2）先恢复完全备份，再指定起始时间进行恢复
[root@MySQL ~]# mysql -uroot -p123 zhangsan < /mysql.bak/zhangsan_成绩表-2021-02-17.sql [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;' [root@MySQL ~]# mysqlbinlog --no-defaults --start-datetime='2021-02-18 0:25:19' /mysql.bak/mysql-bin.000002 | mysql -uroot -p123 [root@MySQL ~]# mysql -uroot -p123 -e 'select * from zhangsan.成绩表;'
