本篇文章为大家展示了MySQL中怎么通过binlog日志恢复数据，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

一、数据备份

操作的前一天晚上进行了日常逻辑备份

mysqldump-uroot-pmysql-P3306--all-databases>/mysql/backup/dump/alldb_bak.sql

二、模拟事故

模拟事故发生前后的业务情况

mysql>showtables;+----------------+|Tables_in_test|+----------------+|kk||t1||t2||t3||t4||t5||t6||t7|+----------------+8rowsinset(0.00sec)mysql>desct7;+-------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+----------------+|id|int(11)|NO|PRI|NULL|auto_increment||name|varchar(30)|YES||NULL||+-------+-------------+------+-----+---------+----------------+2rowsinset(0.00sec)mysql>createtablet8asselect*fromt7;QueryOK,3rowsaffected(0.17sec)Records:3Duplicates:0Warnings:0mysql>select*fromt8;+----+--------+|id|name|+----+--------+|1|steven||3|steven||4|steven|+----+--------+3rowsinset(0.00sec)mysql>insertintot8select*fromt7;QueryOK,3rowsaffected(0.04sec)Records:3Duplicates:0Warnings:0mysql>select*fromt8;+----+--------+|id|name|+----+--------+|1|steven||3|steven||4|steven||1|steven||3|steven||4|steven|+----+--------+6rowsinset(0.00sec)mysql>updatet8setid=2whereid=3;QueryOK,2rowsaffected(0.33sec)Rowsmatched:2Changed:2Warnings:0mysql>updatet8setid=3whereid=4;QueryOK,2rowsaffected(0.03sec)Rowsmatched:2Changed:2Warnings:0mysql>select*fromt8;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven||1|steven||2|steven||3|steven|+----+--------+6rowsinset(0.00sec)mysql>droptablet8;QueryOK,0rowsaffected(0.10sec)

三、查看当前binlog

mysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000001|1344||||+------------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)

四、恢复数据

拷贝生产库前一天晚上的备份文件以及备份到事故期间的binlog至临时库

scpalldb_bak.sql192.168.8.32:/mysql/backup/dump/

scp/mysql/data/mysql-bin.000001192.168.8.32:/mysql/backup/dump/

在临时库创建出现事故的database

mysql>createdatabasetest;QueryOK,1rowaffected(0.03sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||test||testdb13||testdb14||testdb15||testdb16||testdb17||testdb18||testdb19||testdb20||testdb21||testdb22||testdb23||testdb24|+--------------------+17rowsinset(0.00sec)

从备份中恢复test数据库

mysql-uroot-pmysql-P3306-otest<alldb_bak.sql

-o是指单独恢复test库，忽略其他数据库

从mysql-bin.000001中查看到drop table t8之前的pos是1164

updatet8setid=3whereid=4/*!*/;#at1133#18112714:12:41serverid330631end_log_pos1164CRC320x1203751cXid=1661COMMIT/*!*/;#at1164#18112714:12:53serverid330631end_log_pos1229CRC320x48fad728Anonymous_GTIDlast_committed=4sequence_number=5rbr_only=noSET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at1229#18112714:12:53serverid330631end_log_pos1344CRC320x2a7eb0d7Querythread_id=3exec_time=1error_code=0SETTIMESTAMP=1543299173/*!*/;DROPTABLE`t8`/*generatedbyserver*//*!*/;

mysqlbinlog--no-defaults--stop-position=1164--database=testmysql-bin.000001|mysql-uroot-ptest

五、根据临时库的数据，将该表恢复至生产库

六、数据验证

mysql>usetest;Databasechangedmysql>showtables;+----------------+|Tables_in_test|+----------------+|kk||t1||t2||t3||t4||t5||t6||t7||t8|+----------------+9rowsinset(0.01sec)mysql>select*fromt8;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven||1|steven||2|steven||3|steven|+----+--------+6rowsinset(0.00sec)

上述内容就是MySQL中怎么通过binlog日志恢复数据，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。