binlog(Binary Log)恢复MySQL数据

binlog(Binary Log)是一组文件,MySQL使用binlog记录数据变更信息(“ 事件 ”),可通过回放事件恢复数据。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选项 。

binlog文件目录

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

mysql>SHOW VARIABLES LIKE '%log_bin_basename%';

查看binlog文件列表

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%';