Mysql利用binlog日志恢复数据库

发表于

如果没有对mysql进行备份或者导出文件的话，数据库一旦误操作，还可以通过mysql的操作日志binlog来进行恢复。

  一般情况下默认安装的Mysql数据库是不会开启日志文件的，需要手动进行开启。
在/etc/my.cnf配置文件中[mysqld] #选项添加
log-bin=mysql-bin #日志文件名称，未指定位置，默认数据文件位置
配置完成后重启Mysql，我是centos7.3使用systemctl restart mysqld.service命令重启，这时候可能会报错，在进入到刚才的配置文件，同样的位置加上server-id=1，如果在集群环境下，需要配置不同的值。
登录Mysql 输入查询语句 show master logs; 会得到如下结果，这样子binlog日志就开启成功！

1
2
3
4
5
6
7
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

日志文件在到达一定大小的时候会新建一个，当然也可以手动刷新，可以使用sql查看正在写入的日志文件

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看当前正在写入的日志内容

1
2
3
4
5
6
7
8
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

查看指定日志文件

1
2
3
4
5
6
7
8
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

手动启用新的日志文件

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     3112 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

数据恢复：
binlog文件中记录Mysql的所有操作记录，可以进行完成的恢复，或者基于时间点和操作位置进行恢复，进行数据恢复之前最好对数据库进行一次完整备份。
1.完整恢复，需要执行上次完整备份之后的日志文件(前提条件上次要进行备份)

1
mysql localhost mysql-bin.000001 | mysql -uroot -p

2.基于时间段的恢复

如果能清楚记得操作错误的时间点，可以恢复到误操作时间点之前

1
mysqlbinlog --stop-date='2018-01-01 9:00:00' mysql-bin.000001 | mysql -uroot -p

如果只记得误操作时间段，可以通过时间段的方式跳过误操作

1
mysqlbinlog --start-datetime="2018-01-1 9:00:00" --stop-datetime="2018-01-1 14:00:00" mysql-bin.000001 | mysql -u root -p

在我查看教程使用的过程中，发现通过上述方法恢复数据库的时会有报错，例如字段不存在，表结构不匹配等错误无法恢复。这时候可以通过导出sql文件的方式手动进行恢复。

1
2
3
mysqlbinlog  binlog.000001 >1.sql #导出成sql格式
mysqlbinlog --start-datetime="2018-01-01 9:00:00" --stop-datetime="2018-01-01 14:00:00" mysql-bin.000001 > /data/test01.log #按时间点导出
mysqlbinlog --start-position=1  --stop-position=200 mysql-bin.000001 > /data/test02.log #按事件位置导出

通过查看导出的日志文件，通过手动运行sql的方式来恢复数据库。