目录

一、MySQL数据库增量备份恢复1.1 物理冷备份，开启服务1.2 开启二进制日志文件1.3 创建库和表，进行完全备份和增量备份1.4 进行正常操作和误操作，进行增量备份1.5 查看增量备份文件1.6 模拟故障，删除表并进行恢复1.7 使用基于时间点的断点恢复1.8 使用基于位置的断点恢复

一、MySQL数据库增量备份恢复

1.1 物理冷备份，开启服务

[root@localhost ~]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data [root@localhost ~]# ls /opt mysql-2020-01-08.tar.xz mysql-5.7.17 rh [root@localhost ~]# systemctl start mysqld

1.2 开启二进制日志文件

[root@localhost mysql]# vim /etc/my.cnf #####编辑my.cnf配置文件 [client] port = 3306 default-character-set=utf8 socket = /usr/local/mysql/mysql.sock [mysql] port = 3306 default-character-set=utf8 socket = /usr/local/mysql/mysql.sock [mysqld] #####找到这个模块,在[mysqld]项中加入配置 log-bin=mysql-bin user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysqld.pid socket = /usr/local/mysql/mysql.sock server-id = 1 log-bin=/usr/local/mysql/data/mysql-bin ####这个地方开启二进制日志功能 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES [root@localhost mysql]# systemctl restart mysqld ####重启数据库 [root@localhost ~]# ll /usr/local/mysql/data/ 总用量 122924 -rw-r----- 1 mysql mysql 56 8月 9 16:16 auto.cnf -rw-r----- 1 mysql mysql 308 8月 15 05:50 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 8月 15 05:50 ibdata1 -rw-r----- 1 mysql mysql 50331648 8月 15 05:50 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 8月 9 16:16 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 8月 15 05:50 ibtmp1 drwxr-x--- 2 mysql mysql 4096 8月 9 16:16 mysql -rw-r----- 1 mysql mysql 154 8月 15 05:50 mysql-bin.000001 ####二进制日志文件 -rw-r----- 1 mysql mysql 39 8月 15 05:50 mysql-bin.index drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 performance_schema drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 sys

1.3 创建库和表，进行完全备份和增量备份

[root@localhost data]# mysql -uroot -p Enter password: ###输入密码登陆 mysql> create database yiku; ###创建库 Query OK, 1 row affected (0.00 sec) mysql> use yiku; ###使用库 Database changed mysql> create table yibiao (id int(4) primary key,name char(10)); ###创建表 Query OK, 0 rows affected (0.00 sec) mysql> insert into yibiao values(1,'zhangsan'),(2,'lisi'); ##插入记录 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from yibiao; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec) mysql>exit [root@localhost data]# mysqldump -uroot -p yiku > /opt/yiku.sql ###进行完全备份 Enter password: [root@localhost data]# mysqladmin -uroot -p flush-logs ###进行增量备份 Enter password: [root@localhost data]# cd /usr/local/mysql/data/ [root@localhost data]# ls mysql-bin.000001 mysql-bin.000002 ...省略内容... ###发现已经有了增量备份文件，刚刚备份的操作记录都存放在 mysql-bin.000001中， mysql-bin.000002是准备存放后续操作的

1.4 进行正常操作和误操作，进行增量备份

mysql> insert into yibiao values(3,'wangwu'); ###正常操作 Query OK, 1 row affected (0.00 sec) mysql> delete from yibiao where name='wangwu'; ###误操作 Query OK, 1 row affected (0.00 sec) mysql> insert into yibiao values(4,'zhaoliu'); ###正常操作 Query OK, 1 row affected (0.00 sec) mysql> select * from yibiao; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 4 | zhaoliu | +----+----------+ 3 rows in set (0.00 sec) mysql> exit Bye [root@localhost data]# mysqladmin -uroot -p flush-logs; ###再次增量备份 Enter password: [root@localhost data]# ls ###发现再次生成新的增量备份文件ysql-bin.000003 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 ###刚刚的正常操作和无操作都存放到了mysql-bin.000002中

1.5 查看增量备份文件

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt [root@localhost data]# vim /opt/bak.txt ###发现能够查看到刚刚的操作语句 ...省略内容... # at 343 ###正常操作语句段 #200108 0:29:18 server id 1 end_log_pos 390 CRC32 0x9ada4ac9 Write_rows: table id 219 flags: STMT_END_F ### INSERT INTO `yiku`.`yibiao` ### SET ### @1=3 ### @2='wangwu' # at 390 ...省略内容 # at 558 ###误操作语句段，558为误操作语句的开始位置id，200108 0:29:56 为误操作语句的开始位置时间，也是上一语句的结束时间 #200108 0:29:56 server id 1 end_log_pos 610 CRC32 0xb990c664 Table_map: `yiku`.`yibiao` mapped to number 219 # at 610 #200108 0:29:56 server id 1 end_log_pos 657 CRC32 0x1738ac5d Delete_rows: table id 219 flags: STMT_END_F ### DELETE FROM `yiku`.`yibiao` ### WHERE ### @1=3 ### @2='wangwu' # at 657 ...省略内容 # at 825 ###正常操作语句段，825为正常操作语句的开始位置id，200108 0:30:11 为正常操作语句的开始位置时间，也是上一语句的结束时间 #200108 0:30:11 server id 1 end_log_pos 877 CRC32 0x89f71cba Table_map: `yiku`.`yibiao` mapped to number 219 # at 877 #200108 0:30:11 server id 1 end_log_pos 925 CRC32 0xae064160 Write_rows: table id 219 flags: STMT_END_F ### INSERT INTO `yiku`.`yibiao` ### SET ### @1=4 ### @2='zhaoliu' # at 925 #200108 0:30:11 server id 1 end_log_pos 956 CRC32 0xe6213e68 Xid = 57 ...省略内容...

1.6 模拟故障，删除表并进行恢复

[root@localhost data]# mysql -uroot -p Enter password: mysql> drop table yibiao; ###删除原有的表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) mysql> source /opt/yiku.sql; ###恢复库 mysql> show tables; ###恢复成功 +----------------+ | Tables_in_yiku | +----------------+ | yibiao | +----------------+ 1 row in set (0.00 sec) mysql> select * from yibiao; ###恢复成功 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec)

1.7 使用基于时间点的断点恢复

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-10-18 0:29:56' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p ###第一个正常操作的结尾时间是2020-10-18 00:29:56，所以此处用此时间，表示恢复到此时间的操作 ###时间在4.5查看增量备份文件中有写 Enter password: [root@localhost data]# mysql -u root -p Enter password: mysql> use yiku; mysql> select * from yibiao; ###查看表，发现恢复成功了 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | +----+----------+ 3 rows in set (0.00 sec) [root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-10-18 00:30:11' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p ###第二个十误操作，我们要跳过。第三个正常操作的结尾时间是2020-10-18 00:30:11，所以此处用此时间，表示从此时间恢复到结尾的操作 ###时间在4.5查看增量备份文件中有写 Enter password: [root@localhost data]# mysql -u root -p Enter password: mysql> use yiku; mysql> select * from yibiao; ###查看表，发现恢复成功了 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | zhaoliu | +----+----------+ 4 rows in set (0.00 sec)

1.8 使用基于位置的断点恢复

'//先删除刚刚恢复的两个表记录' mysql> delete from yibiao where id=3; Query OK, 1 row affected (0.01 sec) mysql> delete from yibiao where id=4; Query OK, 1 row affected (0.00 sec) mysql> select * from yibiao; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec) mysql> exit [root@localhost data]# mysqlbinlog --no-defaults --stop-position='558' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p ###错误操作真正开始的位置是558，而不是610，所以要恢复到558位置结束 ###位置在4.5查看增量备份文件中有写 Enter password: [root@localhost data]# mysql -u root -p Enter password: mysql> use yiku; mysql> select * from yibiao; ###查看表，发现恢复成功了 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | +----+----------+ 3 rows in set (0.00 sec) [root@localhost data]# mysqlbinlog --no-defaults --start-position='825' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p ###正常操作真正开始的位置是825，而不是877，所以要恢复到825位置结束 ###位置在4.5查看增量备份文件中有写 Enter password: [root@localhost data]# mysql -u root -p Enter password: mysql> use yiku; mysql> select * from yibiao; ###查看表，发现恢复成功了 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | zhaoliu | +----+----------+ 4 rows in set (0.00 sec)