mysql 使用binary logs 恢复数据
确认binarylogs 是否开启
show binary logs
如果没有启动注意在my.cnf的mysqld节中开启
log-bin=mysql-bin
/mysql/bin/mysqlbinlog mysql-bin.000014 检查要恢复数据的起始时间（14:35:50）和截止时间（14:36:13）
DELIMITER /*!*/; # at 4 #201029 14:35:50 server id 1 end_log_pos 120 CRC32 0x33a2b7cf Start: binlog v 4, server v 5.6.49-log created 201029 14:35:50 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' xmKaXw8BAAAAdAAAAHgAAAABAAQANS42LjQ5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADGYppfEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAc+3 ojM= '/*!*/; ****** # at 3438740 #201029 14:36:13 server id 1 end_log_pos 3438771 CRC32 0x1520e1dd Xid = 13625 COMMIT/*!*/; DELIMITER ; # End of log file
/www/server/mysql/bin/mysqlbinlog --start-datetime="2020-10-29 14:35:50" --stop-datetime="2020-10-29 14:36:13" --database=huifu /www/server/data/mysql-bin.000014 | /usr/bin/mysql -uroot -p****** -v huifu
使用mysqlbinlog 命令 指定起始时间 结束时间，以及数据库名进行恢复。
收藏一篇将binlog比较细的博文 http://www.linuxe.cn/post-393.html
一、使用mysqlbinlog命令管理binlog或者relaylog 1、mysqlbinlog命令常用选项 -d | --database：从二进制日志文件中过滤出某个库的日记信息 --base64-output=decode-rows：将row格式日志进行解码，否则看到的是乱码，因为rows格式是加密的 --start-datetime=：指定时间节点，同理还有--stop-datetime指定结束时间 --start-position=：指定位置节点，同理还有--stop-position指定结束位置 -v|-vv|-vvv：详细的信息更详细 2、mysqlbinlog命令示例 1、显示某binlog或relaylog所有内容，并且对rows格式的日志解码 1 mysqlbinlog -vvv --base64-output=decode-rows master.bin.000003 #将rows的日志内容解码，可用于数据恢复时查找正确的操作 2、显示指定日期范围内的binlog或relaylog，并且对rows格式的日志解码 1 mysqlbinlog -vvv --base64-output=decode-rows --start-datetime='2018-02-13 11:37:00' --stop-datetime='2018-02-15 12:00:00' master-bin.000001 3、显示指定position范围内的binlog或relaylog，并且对rows格式的日志解码 1 mysqlbinlog -vvv --base64-output=decode-rows --start-position=3052 master-bin.000002 #显示从3052开始的所有内容 2 mysqlbinlog -vvv --base64-output=decode-rows --start-position=3052 --stop-position=3849 master-bin.000002 #显示3052-3849的内容 4、显示指定某个库的binlog或relaylog，并且对rows格式的日志解码 1 mysqlbinlog -vvv --base64-output=decode-rows --start-datetime='2018-10-12 14:42:00' --stop-datetime='2018-10-12 14:45:00' --database=test_database mysql-bin.000008 > /tmp/bin.sql 5、gtid模式下，导出多个binlog或relaylog文件中的数据 1 mysqlbinlog --include-gtids='yourgtid:1-100' mysql-bin.00001 mysql-bin.00002 mysql-bin.00003 mysql-bin.00004 > /tmp/gtid.sql #假设101的GTID为drop table，就截取到100即可 二、使用show binlog events查看事件（适用于relaylog） 1 mysql > show master logs; #显示当前主库所有二进制日志情况 2 mysql > show binary logs; #和上面作用一样 3 mysql > flush logs; #刷新并生成新的二进制日志文件 4 mysql > show binlog | relaylog events in 'master-bin.000002'; #显示二进制日志的内容 5 mysql > show binlog | relaylog events in 'master-bin.000002' limit5; #只显示5条 6 mysql > show binlog | relaylog events in 'master-bin.000002' from 3390 limit5; #从3390位置开始显示5条 7 mysql > purge master logs to 'master-bin.000002'; #删除指定的二进制日志 8 mysql > purge master logs before '2018-02-17 10:30:00'; 使用show binlog event命令时关注Event_type为Query的语句，这些就是引起数据库内容变化的SQL，Pos和End_log_pos字段内容是语句的开始和结束位置，具体的SQL在Info字段中。由于每个DDL本身就是一个事件，所以能直接在Info中看到，而DML语句都是由BEGIN开头，COMMIT结束，所以Pos信息应该以Begin为准，End_log_pos则是看COMMIT的。 企业微信截图_20181012115349.png 三、删除binlog日志的正确方法 由于二进制日志会随着数据库操作的增多而不断增长，在一段时间后需要删除一些无用的二进制日志，由于直接rm删除可能会破话binlog的文件索引文件，所以需要用purge命令来操作（reset master虽然也可以清空日志文件，但会导致从库出错） 1 mysql > PURGE {MASTER | BINARY} LOGS TO 'xxxxx-bin.00000X' #删除日志文件到指定的文件为止 2 mysql > PURGE {MASTER | BINARY} LOGS BEFORE 'date' #删除某个日期之前的文件 四、使用binlog进行数据恢复 如果不小心删错了库或表，可以使用历史备份与备份之后的binlog日志进行数据的恢复。建议找一台机器恢复数据后再导出到生产环境中 模拟事故过程 1、建立一个数据库，并在其中创建一张表并插入数据 1 mysql > create database student; 2 mysql > use student; 3 mysql > create table student(id int,name varchar(10),age int); 4 mysql > insert into student values ('1','tanglu','28'),('2','zouxiaolu','28'),('3','beibei','28'); 2、进行完整的备份，这样之前的数据已经有了备份 1 mysqldump -u root -p --master-data=2 student > student.sql 3、再次插入三条数据 1 mysql> insert into student values('4','doudou','8'); 2 Query OK, 1 row affected (0.01 sec) 3 4 mysql> insert into student values('5','mengmeng','28'); 5 Query OK, 1 row affected (0.00 sec) 6 7 mysql> insert into student values('6','haha','10'); 8 Query OK, 1 row affected (0.01 sec) 4、模拟误操作，删除之前表格中的所有数据 1 mysql> delete from student ; 恢复数据过程 1、重启数据库或者执行flush-logs来获得一个新的二进制日志文件，这样故障操作日志都在之前的binlog文件里 1 mysql > flush logs; 2、用完整备份恢复数据，这样前面3条数据就恢复成功 1 mysql -u root -p student < student.sql 3、由于备份开启了master-data=2，所以通过备份文件中的change master信息可以查找出binlog的起始点，假设为355。然后分析binlog或者show events in语句查找误操作的SQL的起始点，通常误操作都在最后一个binlog文件中去查找。这里假设误操作的起始点为1294，所以只需要导出这之前的语句用于恢复 1 mysqlbinlog --database=linuxe --start-positon=355 --stop-position=1294 linuxe-binlog.000003 > server2.sql 2 # mysqlbinlog --database=linuxe --start-datetime="" --stop-datetime="" mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 > xxxx.sql #以时间为维度一次性从多个binlog中恢复数据的办法,如果同一秒有多个的话需要将start改为position 4、恢复数据 1 mysql > set sql_log_bin=0 #临时关闭binlog，避免导入时生成重复binlog 2 mysql > source /tmp/binlog.sql
