1. MySQL的binlog恢复（Windows下） 初识binlog　　MySQL的binlog就是大家经常所说的Binary Log，即bin-log，是MySQL存储对数据库改动的二进制文件，也就是记录了所有DDL与DML（select除外）语句. 　　首先查看 mysqlbinlog 是否已经打开:-- 查看MySQL的日志是否打开(log_bin项) mysql> SHOW VARIABLES LIKE 'log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 2 rows in set (0.01 sec) -- 查看日志过期时间(过期之后日志会自动删除) mysql> show variables like '%expire_logs_days%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ 1 row in set (0.00 sec)　　如果 log_bin 是关闭状态的话,我们需要修改mysql配置文件.比如我的mysql安装位置是:D:\mysql\bin\mysql.exe. 所以修改D:\mysql\my.ini 文件,添加以下配置:# 如果 log_bin=mysql-bin 则日志文件会默认存放在 C:\ProgramData\MySQL\MySQL Server 5.5\data\ log-bin=D:/mysql/data/mysql-bin binlog_format=mixed # 设置日志永不过期(一般设置为10 即十天之后过期删除) expire_logs_days = 0　　修改之后执行 services.msc 打开服务,重启mysql服务生效.数据恢复测试1.准备表person,插入基本数据:mysql> CREATE TABLE `person` ( -> `id` varchar(255) , -> `name` varchar(255) , -> `age` int(10) -> ); Query OK, 0 rows affected (0.02 sec) mysql> select * from person; Empty set (0.01 sec) mysql> INSERT INTO person VALUES (uuid(),'张三',18);INSERT INTO person VALUES (uuid(),'王五',30); Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> select * from person; +--------------------------------------+------+------+ | id | name | age | +--------------------------------------+------+------+ | bcaa790d-6499-11ec-86bc-8c16453d981b | 张三 | 18 | | bcaae031-6499-11ec-86bc-8c16453d981b | 王五 | 30 | +--------------------------------------+------+------+ 2 rows in set (0.00 sec)2.模拟误删除person表:mysql> delete from person; Query OK, 2 rows affected (0.00 sec)3.尝试恢复表数据:{card-default label="3.1 查看binlog日志位置信息" width="100%"}查看当前日志名称:mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1856 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)查看当前日志节点: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 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 | | mysql-bin.000001 | 107 | Query | 1 | 263 | use `test`; CREATE TABLE `person` ( `id` varchar(255) , `name` varchar(255) , `age` int(10) ) | | mysql-bin.000001 | 263 | Query | 1 | 371 | use `test`; DROP TABLE `person` /* generated by server */ | | mysql-bin.000001 | 371 | Query | 1 | 522 | use `test`; CREATE TABLE `person` ( `id` varchar(255) , `name` varchar(255) , `age` int(10) ) | | mysql-bin.000001 | 522 | Query | 1 | 768 | use `test`; ALTER TABLE `person` MODIFY COLUMN `id` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL FIRST , MODIFY COLUMN `name` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `id` | | mysql-bin.000001 | 768 | Query | 1 | 836 | BEGIN | | mysql-bin.000001 | 836 | Table_map | 1 | 887 | table_id: 36 (test.person) | | mysql-bin.000001 | 887 | Write_rows | 1 | 967 | table_id: 36 flags: STMT_END_F | | mysql-bin.000001 | 967 | Xid | 1 | 994 | COMMIT /* xid=112 */ | | mysql-bin.000001 | 994 | Query | 1 | 1062 | BEGIN | | mysql-bin.000001 | 1062 | Query | 1 | 1197 | use `test`; -- INSERT INTO person VALUES (uuid(),'张三',18); delete from person | | mysql-bin.000001 | 1197 | Xid | 1 | 1224 | COMMIT /* xid=119 */ | | mysql-bin.000001 | 1224 | Query | 1 | 1292 | BEGIN | | mysql-bin.000001 | 1292 | Table_map | 1 | 1343 | table_id: 36 (test.person) | | mysql-bin.000001 | 1343 | Write_rows | 1 | 1425 | table_id: 36 flags: STMT_END_F | | mysql-bin.000001 | 1425 | Xid | 1 | 1452 | COMMIT /* xid=123 */ | | mysql-bin.000001 | 1452 | Query | 1 | 1520 | BEGIN | | mysql-bin.000001 | 1520 | Table_map | 1 | 1571 | table_id: 36 (test.person) | | mysql-bin.000001 | 1571 | Write_rows | 1 | 1653 | table_id: 36 flags: STMT_END_F | | mysql-bin.000001 | 1653 | Xid | 1 | 1680 | COMMIT /* xid=124 */ | | mysql-bin.000001 | 1680 | Query | 1 | 1748 | BEGIN | | mysql-bin.000001 | 1748 | Query | 1 | 1829 | use `test`; delete from person | | mysql-bin.000001 | 1829 | Xid | 1 | 1856 | COMMIT /* xid=152 */ | +------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 23 rows in set (0.00 sec)　　可以看待在1680节点开始到1856节点结束是处理最后的删除表操作.所以我们恢复数据的时候,只需要恢复数据到1680节点之前就行 {/card-default} {card-default label="3.2 通过binlog恢复数据" width="100%"}　　打开命令窗,切换路径到与安装的mysql执行文件同目录。比如我的mysql安装路径是:D:\mysql\bin\mysql.exe，所以目录切换到*D:\mysql\bin* 。 　　一般有两种恢复操作：一种是将从日志文件中抽取到的sql存为件，然后执行.sql文件;另一种是直接将抽取到的sql执行.恢复命令格式如下：# 抽取sql存为文件 mysqlbinlog --start-position=开始节点号 --stop-posion=结束节点号 日志文件名称(保险起见使用使用绝对路径) > 存储位置名称 # 执行.sql文件 source 路径名称.sql # 直接执行抽取到的sql mysqlbinlog --start-position=开始节点号 --stop-posion=结束节点号 日志文件名称(保险起见使用使用绝对路径) | mysql -uroot -p123456 通过3.1的分析,可以得知需要执行的起始节点位置和日志路径.所以执行命令如下:# 第一种情况: 先存.sql 然后执行(两条命令分开执行) mysqlbinlog --start-position=4 --stop-position=1680 D:\mysql\data\mysql-bin.000001 > d:\\test1.sql source d://test1.sql # 第二种情况: 直接执行 mysqlbinlog --start-position=4 --stop-position=1680 D:\mysql\data\mysql-bin.000001 | mysql -uroot -p1216{/card-default} {callout color="#f0ad4e"}执行完毕之后可以看到,数据已经恢复到以前两条数据没有删除的状态.{/callout}