MySQL数据恢复 - binlog

binlog(Binary Log)是MySQL记录数据变更事件的文件,通过回放事件可恢复数据。

binlog由一组二进制文件和一个索引文件组成。

启用binlog会影响mysql性能,binlog带来的保障远超性能影响。

binlog能抵御意外宕机,只有完整的事件或事务才会被记录或读取。

binlog不会记录查询操作。

启用/禁用

早期版本默认禁用binlog,MySQL 8.0开始,默认启用binlog。

启用:

--log-bin=ON

禁用

--skip-log-bin 

--disable-log-bin

--log_bin=OFF

若使用mysqld --initializeor或--initialize-insecure选项,初始化数据目录 ,默认会禁用binlog,此时需指定--log-bin选项 。

文件目录

mysql默认在数据目录中创建binlog,--log-bin选项可指定文件路径。

mysql>SHOW VARIABLES LIKE '%log_bin_basename%';

查看文件

mysql>SHOW BINARY LOGS;
mysql>SHOW MASTER STATUS;

导出binlog

binlog为二进制文件,mysqlbinlog能将文件转换成文本文件:

shell> mysqlbinlog log_file | mysql -h server_name
shell> mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-QM0IF54I-bin.000059" > a.log

shell> mysqlbinlog binlog_files | mysql -u root -p

加密传输

shell> mysqlbinlog --read-from-remote-server --host=host_name --port=3306 --user=root --password --ssl-mode=required binlog_files | mysql -u root -p

时间点恢复

指定恢复的开始和结束时间。

如:在2019年9月17日上午10:00执行了大量删除操作,通过如下操作恢复:

shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root -p

命令会将数据恢复到--stop-datetime指定的日期和时间。

如果,确认之后的SQL操作没有问题,可指定恢复开始的时间点:

shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -p

以上两条命令恢复了上午10:00前和上午10:01后的所有数据。

事件位置恢复

如果在同一时间,正确的sql和有问题的sql都已提交,通过指定事件位置,剔除有问题的提交。

先通过时间范围筛binlog记录:

shell> mysqlbinlog --start-datetime="2005-04-20 9:55:00" --stop-datetime="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

查看时间范围内的记录,找出没问题的sql,查看记录对应的log_pos值,log_pos值作为position,指定数据恢复的边界。

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 | mysql -u root -p
shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 | mysql -u root -p

第一条命令恢复事务到指定的位置。

第二个命令从给定的起始位置恢复事务。

binlog参数

SHOW VARIABLES LIKE '%binlog%';