MySQL的二进制日志（binlog）使用分析（4）——利用binlog日志恢复数据

利用binlog日志恢复数据，就是让mysql将保存在binlog日志中指定段落区间的SQL语句重新执行一次而已。

下面通过一个例子说明怎样例如binlog日志恢复数据：

一、数据库信息

1、数据库：hist，wanggx

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hist | | mysql | | performance_schema | | sys | | wanggx | +--------------------+ 6 rows in set (0.01 sec)

2、hist数据库包含的表

mysql> use hist; Database changed mysql> show tables; +----------------+ | Tables_in_hist | +----------------+ | course | | dept | | score | | stu | +----------------+ 4 rows in set (0.00 sec)

3、wanggx数据库包含的表

mysql> use wanggx; Database changed mysql> show tables; +------------------+ | Tables_in_wanggx | +------------------+ | department | | emp | +------------------+ 2 rows in set (0.00 sec)

二、备份策略

在每天凌晨四点对hist和wanggx数据库进行一次完全备份，命令如下：

mysqldump -uroot -p123456 --databases -F -R -x --master-data=2 hist wanggx | gzip >/data_bak/db_bak_$(date +%F).sql.gz

参数说明：

（1）–databases（-B）：指定要备份的数据库名称； （2）-F：刷新日志； （3）-R：备份存储过程等； （4）-x：锁表； （5）–master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息。

在全备份的时候使用-F选项，当数据备份操作开始时，系统就会自动刷新log，会自动产生一个新的binlog日志，这个新的binlog日志用来记录备份之后对数据库的【增、删、改】操作。

查看备份结果：

[root@Mysql11 data_bak]# mysqldump -uroot -p123456 --databases -F -R -x --master-data=2 hist wanggx | gzip >/data_bak/db_bak_$(date +%F).sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 data_bak]# ls /data_bak/ db_bak_2020-07-04.sql.gz

查看备份结束后的日志信息：

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

说明：从日志文件mysql-bin.000012开始记录上次备份之后对数据库的所有修改操作信息。

由于备份时使用了【–master-data】选项，在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息。

[root@Mysql11 data_bak]# cat db_bak_2020-07-04.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- ............... -- -- Position to start replication or point-in-time recovery from -- #### CHANGE MASTER语句、binlog文件及位置点信息 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154; -- -- Current Database: `hist` -- ...........

三、备份之后修改数据

1、修改hist数据库中的stu表的数据

（1）上次数据备份之后，hist数据库中的stu表数据如下：

mysql> use hist; Database changed mysql> select * from stu; +----+----------+------+-----------+-------------+---------+ | id | name | age | address | phone | dept_id | +----+----------+------+-----------+-------------+---------+ | 1 | zhangsan | 21 | Xinxiang | 15578941258 | 1 | | 2 | tom | 20 | Xinxiang | 13778942222 | 1 | | 3 | jack | 21 | Zhengzhou | 13675871454 | 1 | | 4 | john | 22 | Zhengzhou | 13937681111 | 2 | | 5 | mark | 22 | Aanyang | 13055882233 | 2 | +----+----------+------+-----------+-------------+---------+ 5 rows in set (0.01 sec)

（2）对hist数据库中的stu表进行如下操作：

mysql> insert into stu(name,age,address,phone,dept_id) values('Obama',18,'America','13988886666',3); Query OK, 1 row affected (0.01 sec) mysql> insert into stu(name,age,address,phone,dept_id) values('Trump',19,'America','13066668888',3); Query OK, 1 row affected (0.01 sec) mysql> update stu set age=age+1 where dept_id=2; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> delete from stu where id=1; Query OK, 1 row affected (0.01 sec)

（3）修改之后的数据如下：

mysql> select * from stu; +----+-------+------+-----------+-------------+---------+ | id | name | age | address | phone | dept_id | +----+-------+------+-----------+-------------+---------+ | 2 | tom | 20 | Xinxiang | 13778942222 | 1 | | 3 | jack | 21 | Zhengzhou | 13675871454 | 1 | | 4 | john | 23 | Zhengzhou | 13937681111 | 2 | | 5 | mark | 23 | Aanyang | 13055882233 | 2 | | 6 | Obama | 18 | America | 13988886666 | 3 | | 7 | Trump | 19 | America | 13066668888 | 3 | +----+-------+------+-----------+-------------+---------+ 6 rows in set (0.00 sec)

2、修改wanggx数据库中的department表的数据

（1）上次数据备份之后，wanggx数据库中的department表数据如下：

mysql> use wanggx Database changed mysql> select * from department; +---------+----------+ | dept_id | name | +---------+----------+ | 1 | guanli | | 2 | jingji | | 3 | jidian | | 4 | jisuanji | +---------+----------+ 4 rows in set (0.01 sec)

（2）对wanggx数据库中的department表进行如下修改：

mysql> insert into department(name) values('huagong'); Query OK, 1 row affected (0.00 sec) mysql> insert into department(name) values('yishu'); Query OK, 1 row affected (0.03 sec)

（3）修改后的数据如下：

mysql> select * from department; +---------+----------+ | dept_id | name | +---------+----------+ | 1 | guanli | | 2 | jingji | | 3 | jidian | | 4 | jisuanji | | 5 | huagong | | 6 | yishu | +---------+----------+ 6 rows in set (0.01 sec)

四、模拟误操作

由于业务部熟练或者不小心执行了如下操作：

1、删除hist库

mysql> drop database hist; Query OK, 4 rows affected (0.05 sec)

2、创建hist库和t1表，并插入数据

mysql> create database hist; Query OK, 1 row affected (0.01 sec) mysql> use hist; Database changed mysql> create table t1(id int primary key,name char(20)); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values(1,'a'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(2,'b'); Query OK, 1 row affected (0.02 sec)

五、把数据库恢复删除hist数据库之前的状态

1、查看上次备份之后的binlog日志文件名称以及位置点信息

[root@Mysql11 data_bak]# cat db_bak_2020-07-04.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- ............... -- -- Position to start replication or point-in-time recovery from -- #### CHANGE MASTER语句、binlog文件及位置点信息 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154; -- -- Current Database: `hist` -- ...........

查询结果为：日志文件为【mysql-bin.000012】，位置点为【154】。

2、备份binlog文件【mysql-bin.000012】及以后的binlog文件

（1）查看binlog日志文件的信息

mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 923 | | mysql-bin.000002 | 703 | | mysql-bin.000003 | 201 | | mysql-bin.000004 | 542 | | mysql-bin.000005 | 518 | | mysql-bin.000006 | 201 | | mysql-bin.000007 | 448 | | mysql-bin.000008 | 201 | | mysql-bin.000009 | 1595 | | mysql-bin.000010 | 201 | | mysql-bin.000011 | 201 | | mysql-bin.000012 | 3456 | +------------------+-----------+ 12 rows in set (0.00 sec)

（2）发现只需要备份【mysql-bin.000012】一个文件即可

[root@Mysql11 data_bak]# cp /var/lib/mysql/mysql-bin.000012 /data_bak/ [root@Mysql11 data_bak]# ls /data_bak/ db_bak_2020-07-04.sql mysql-bin.000012

3、刷新日志索引操作，重新开始新的binlog日志文件

为了便于我们分析原因，以后所有数据库操作都会写入到下一个日志文件。

mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000013 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

4、查看binlog日志，找到删除hist数据库的位置

查看binlog日志文件的信息

mysql> show binlog events in '/var/lib/mysql/mysql-bin.000012'; +------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------+ | mysql-bin.000012 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 | | mysql-bin.000012 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000012 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 219 | Query | 1 | 291 | BEGIN | | mysql-bin.000012 | 291 | Rows_query | 1 | 407 | # insert into stu(name,age,address,phone,dept_id) values('Obama',18,'America','13988886666',3) | | mysql-bin.000012 | 407 | Table_map | 1 | 464 | table_id: 136 (hist.stu) | | mysql-bin.000012 | 464 | Write_rows | 1 | 538 | table_id: 136 flags: STMT_END_F | | mysql-bin.000012 | 538 | Xid | 1 | 569 | COMMIT /* xid=405 */ | | mysql-bin.000012 | 569 | Anonymous_Gtid | 1 | 634 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 634 | Query | 1 | 706 | BEGIN | | mysql-bin.000012 | 706 | Rows_query | 1 | 822 | # insert into stu(name,age,address,phone,dept_id) values('Trump',19,'America','13066668888',3) | | mysql-bin.000012 | 822 | Table_map | 1 | 879 | table_id: 136 (hist.stu) | | mysql-bin.000012 | 879 | Write_rows | 1 | 953 | table_id: 136 flags: STMT_END_F | | mysql-bin.000012 | 953 | Xid | 1 | 984 | COMMIT /* xid=406 */ | | mysql-bin.000012 | 984 | Anonymous_Gtid | 1 | 1049 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 1049 | Query | 1 | 1121 | BEGIN | | mysql-bin.000012 | 1121 | Rows_query | 1 | 1185 | # update stu set age=age+1 where dept_id=2 | | mysql-bin.000012 | 1185 | Table_map | 1 | 1242 | table_id: 136 (hist.stu) | | mysql-bin.000012 | 1242 | Update_rows | 1 | 1298 | table_id: 136 flags: STMT_END_F | | mysql-bin.000012 | 1298 | Xid | 1 | 1329 | COMMIT /* xid=407 */ | | mysql-bin.000012 | 1329 | Anonymous_Gtid | 1 | 1394 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 1394 | Query | 1 | 1466 | BEGIN | | mysql-bin.000012 | 1466 | Rows_query | 1 | 1516 | # delete from stu where id=1 | | mysql-bin.000012 | 1516 | Table_map | 1 | 1573 | table_id: 136 (hist.stu) | | mysql-bin.000012 | 1573 | Delete_rows | 1 | 1613 | table_id: 136 flags: STMT_END_F | | mysql-bin.000012 | 1613 | Xid | 1 | 1644 | COMMIT /* xid=408 */ | | mysql-bin.000012 | 1644 | Anonymous_Gtid | 1 | 1709 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 1709 | Query | 1 | 1783 | BEGIN | | mysql-bin.000012 | 1783 | Rows_query | 1 | 1853 | # insert into department(name) values('huagong') | | mysql-bin.000012 | 1853 | Table_map | 1 | 1911 | table_id: 137 (wanggx.department) | | mysql-bin.000012 | 1911 | Write_rows | 1 | 1959 | table_id: 137 flags: STMT_END_F | | mysql-bin.000012 | 1959 | Xid | 1 | 1990 | COMMIT /* xid=422 */ | | mysql-bin.000012 | 1990 | Anonymous_Gtid | 1 | 2055 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 2055 | Query | 1 | 2129 | BEGIN | | mysql-bin.000012 | 2129 | Rows_query | 1 | 2197 | # insert into department(name) values('yishu') | | mysql-bin.000012 | 2197 | Table_map | 1 | 2255 | table_id: 137 (wanggx.department) | | mysql-bin.000012 | 2255 | Write_rows | 1 | 2301 | table_id: 137 flags: STMT_END_F | | mysql-bin.000012 | 2301 | Xid | 1 | 2332 | COMMIT /* xid=423 */ | | mysql-bin.000012 | 2332 | Anonymous_Gtid | 1 | 2397 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | ############## 删除hist数据库的位置2397################################################# | mysql-bin.000012 | 2397 | Query | 1 | 2489 | drop database hist | ####################################################################################### | mysql-bin.000012 | 2489 | Anonymous_Gtid | 1 | 2554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 2554 | Query | 1 | 2648 | create database hist | | mysql-bin.000012 | 2648 | Anonymous_Gtid | 1 | 2713 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 2713 | Query | 1 | 2836 | use `hist`; create table t1(id int primary key,name char(20)) | | mysql-bin.000012 | 2836 | Anonymous_Gtid | 1 | 2901 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 2901 | Query | 1 | 2973 | BEGIN | | mysql-bin.000012 | 2973 | Rows_query | 1 | 3025 | # insert into t1 values(1,'a') | | mysql-bin.000012 | 3025 | Table_map | 1 | 3073 | table_id: 141 (hist.t1) | | mysql-bin.000012 | 3073 | Write_rows | 1 | 3115 | table_id: 141 flags: STMT_END_F | | mysql-bin.000012 | 3115 | Xid | 1 | 3146 | COMMIT /* xid=433 */ | | mysql-bin.000012 | 3146 | Anonymous_Gtid | 1 | 3211 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000012 | 3211 | Query | 1 | 3283 | BEGIN | | mysql-bin.000012 | 3283 | Rows_query | 1 | 3335 | # insert into t1 values(2,'b') | | mysql-bin.000012 | 3335 | Table_map | 1 | 3383 | table_id: 141 (hist.t1) | | mysql-bin.000012 | 3383 | Write_rows | 1 | 3425 | table_id: 141 flags: STMT_END_F | | mysql-bin.000012 | 3425 | Xid | 1 | 3456 | COMMIT /* xid=434 */ | | mysql-bin.000012 | 3456 | Rotate | 1 | 3503 | mysql-bin.000013;pos=4 | +------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------+ 57 rows in set (0.00 sec)

也可以使用mysqlbinlog -v来查找。

5、先把上次全备的数据恢复

（1）恢复数据

[root@Mysql11 data_bak]# mysql -uroot -p123456 < /data_bak/db_bak_2020-07-04.sql mysql: [Warning] Using a password on the command line interface can be insecure.

（2）查看数据

mysql> use hist; Database changed mysql> select * from stu; +----+----------+------+-----------+-------------+---------+ | id | name | age | address | phone | dept_id | +----+----------+------+-----------+-------------+---------+ | 1 | zhangsan | 21 | Xinxiang | 15578941258 | 1 | | 2 | tom | 20 | Xinxiang | 13778942222 | 1 | | 3 | jack | 21 | Zhengzhou | 13675871454 | 1 | | 4 | john | 22 | Zhengzhou | 13937681111 | 2 | | 5 | mark | 22 | Aanyang | 13055882233 | 2 | +----+----------+------+-----------+-------------+---------+ 5 rows in set (0.00 sec) mysql> use wanggx; Database changed mysql> show tables; +------------------+ | Tables_in_wanggx | +------------------+ | department | | emp | +------------------+ 2 rows in set (0.00 sec) mysql> select * from department; +---------+----------+ | dept_id | name | +---------+----------+ | 1 | guanli | | 2 | jingji | | 3 | jidian | | 4 | jisuanji | +---------+----------+ 4 rows in set (0.00 sec)

6、恢复上次全备之后对数据进行的修改

（1）恢复删除hist数据库之前的操作

[root@Mysql11 data_bak]# mysqlbinlog --stop-position=2397 /var/lib/mysql/mysql-bin.000012 > /data_bak/hist_before.sql [root@Mysql11 data_bak]# [root@Mysql11 data_bak]# mysql -uroot -p123456 -e "source /data_bak/hist_before.sql" mysql: [Warning] Using a password on the command line interface can be insecure.

查看数据：

mysql> use hist; Database changed mysql> select * from stu; +----+-------+------+-----------+-------------+---------+ | id | name | age | address | phone | dept_id | +----+-------+------+-----------+-------------+---------+ | 2 | tom | 20 | Xinxiang | 13778942222 | 1 | | 3 | jack | 21 | Zhengzhou | 13675871454 | 1 | | 4 | john | 23 | Zhengzhou | 13937681111 | 2 | | 5 | mark | 23 | Aanyang | 13055882233 | 2 | | 6 | Obama | 18 | America | 13988886666 | 3 | | 7 | Trump | 19 | America | 13066668888 | 3 | +----+-------+------+-----------+-------------+---------+ 6 rows in set (0.00 sec) mysql> use wanggx Database changed mysql> select * from department; +---------+----------+ | dept_id | name | +---------+----------+ | 1 | guanli | | 2 | jingji | | 3 | jidian | | 4 | jisuanji | | 5 | huagong | | 6 | yishu | +---------+----------+ 6 rows in set (0.00 sec)

（2）恢复删除hist数据库之后的操作

[root@Mysql11 data_bak]# mysql -uroot -p123456 -e "use hist;drop table t1;" mysql: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 data_bak]# mysqlbinlog --start-position=2648 /var/lib/mysql/mysql-bin.000012 > /data_bak/hist_after.sql [root@Mysql11 data_bak]# mysql -uroot -p123456 -e "source /data_bak/hist_after.sql" mysql: [Warning] Using a password on the command line interface can be insecure.

查看数据：

mysql> use hist; Database changed mysql> show tables; +----------------+ | Tables_in_hist | +----------------+ | course | | dept | | score | | stu | | t1 | +----------------+ 5 rows in set (0.00 sec) mysql> select * from t1; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec)

至此，就完成了数据库的恢复！！！