开启Binlog日志

1. 修改配置文件，并重启服务

   vim  /etc/my.cnf [mysqld]     server_id=1 #指定server_id log-bin=/mylog/db50  #指定日志目录及名称                            :wq  mkdir  /mylog   #创建目录 chown  mysql  /mylog   #修改所有者 systemctl  restart mysqld  #重启服务 

2. 查看日志信息

   mysql -uroot -p  #管理员登录  mysql> show master status; #查看日志信息 +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | db50.000001 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 

3. 手动创建3个新的日志文件

   mysql> mysql> flush logs; #刷新日志 Query OK, 0 rows affected (0.14 sec)  mysql> flush logs; #刷新日志 Query OK, 0 rows affected (0.11 sec)  mysql> flush logs; #刷新日志 Query OK, 0 rows affected (0.12 sec)  mysql> system ls /mylog/ #查看日志文件 db50.000001  db50.000002  db50.000003  db50.000004  db50.index mysql>  mysql> show master status; #查看日志信息 +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | db50.000004 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 

4. 删除编号3之前的日志文件

   mysql> mysql> purge  master  logs  to "db50.000003"; #删除日志 Query OK, 0 rows affected (0.05 sec)  mysql> system ls /mylog/ #查看日志文件 db50.000003  db50.000004  db50.index mysql>   mysql> system cat /mylog/db50.index #查看索引文件 /mylog/db50.000003 /mylog/db50.000004 mysql> 

使用Binlog日志恢复数据

启动Binlog

1. 调整/etc/my.cnf配置，并重启服务

   [root@dbsvr1 ~] vim  /etc/my.cnf [mysqld] server_id=1  #定义server_id log-bin=mysql-bin  #定义日志名 binlog_format=”mixed”     #定义日志格式                            [root@dbsvr1 ~] systemctl  restart mysqld.service //重启服务 

2. 确认binlog日志文件

   新启用binlog后，每次启动MySQl服务都会新生成一份日志文件

   [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.* /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index 

   其中mysql-bin.index文件记录了当前保持的二进制文件列表：

   [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index ./mysql-bin.000001 

   重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：

   [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.* /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index /var/lib/mysql/mysql-bin.000002 [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 

利用binlog日志重做数据库操作

1. 执行数据库表添加操作
   创建db1·库tb1表，表结构自定义：

   mysql> CREATE DATABASE db1; Query OK, 1 row affected (0.05 sec) mysql> USE db1; Database changed mysql> CREATE TABLE tb1( -> id int(4) NOT NULL,name varchar(24) -> ); Query OK, 0 rows affected (0.28 sec) 

   插入3条表记录：

   mysql> INSERT INTO tb1 VALUES -> (1,'Jack'), -> (2,'Kenthy'), -> (3,'Bob'); Query OK, 3 rows affected (0.12 sec) Records: 3  Duplicates: 0 Warnings: 0 

   确认插入的表记录数据：

   mysql> SELECT * FROM tb1; +----+--------+ | id | name   | +----+--------+ | 1 | Jack   | | 2 | Kenthy | | 3 | Bob    | +----+--------+ 3 rows in set (0.00 sec) 

2. 删除前一步添加的3条表记录
   执行删除所有表记录操作：

   mysql> DELETE FROM tb1; Query OK, 3 rows affected (0.09  sec) 

   确认删除结果：

   mysql> SELECT * FROM tb1; Empty set (0.00 sec) 

通过binlog日志恢复表记录

binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。

1. 查看mysql-bin.000002日志内容

   [root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170412 12:05:32 server id 1  end_log_pos 123 CRC32 0x6d8c069c  Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AZwGjG0= '/*!*/; # at 123 #170412 12:05:32 server id 1  end_log_pos 154 CRC32 0x17f50164  Previous-GTIDs # [empty] # at 154 #170412 12:05:59 server id 1  end_log_pos 219 CRC32 0x4ba5a976  Anonymous_GTID  last_committed=0        sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #170412 12:05:59 server id 1  end_log_pos 310 CRC32 0x5b66ae13  Query   thread_id=3     exec_time=0     error_code=0 SET TIMESTAMP=1491969959/*!*/; SET @@session.pseudo_thread_id=3/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE DATABASE db1 /*!*/; # at 310 #170412 12:06:23 server id 1  end_log_pos 375 CRC32 0x2967cc28  Anonymous_GTID  last_committed=1        sequence_number=2 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 375 #170412 12:06:23 server id 1  end_log_pos 502 CRC32 0x5de09aae  Query   thread_id=3     exec_time=0     error_code=0 use `db1`/*!*/; SET TIMESTAMP=1491969983/*!*/; CREATE TABLE tb1( id int(4) NOT NULL,name varchar(24) ) /*!*/; # at 502 #170412 12:06:55 server id 1  end_log_pos 567 CRC32 0x0b8cd418  Anonymous_GTID  last_committed=2        sequence_number=3 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 567 #170412 12:06:55 server id 1  end_log_pos 644 CRC32 0x7e8f2fa0  Query   thread_id=3     exec_time=0     error_code=0 SET TIMESTAMP=1491970015/*!*/; BEGIN /*!*/; # at 644 #170412 12:06:55 server id 1  end_log_pos 772 CRC32 0x4e3f728e  Query   thread_id=3     exec_time=0     error_code=0                            //插入表记录的起始时间点  SET TIMESTAMP=1491970015/*!*/; INSERT INTO tb1 VALUES(1,'Jack'),(2,'Kenthy'), (3,'Bob') /*!*/; # at 772 #170412 12:06:55 server id 1  end_log_pos 803 CRC32 0x6138b21f  Xid = 10 //确认事务的时间点  COMMIT/*!*/; # at 803 #170412 12:07:24 server id 1  end_log_pos 868 CRC32 0xbef3f472  Anonymous_GTID  last_committed=3        sequence_number=4 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 868 #170412 12:07:24 server id 1  end_log_pos 945 CRC32 0x5684e92c  Query   thread_id=3     exec_time=0     error_code=0 SET TIMESTAMP=1491970044/*!*/; BEGIN /*!*/; # at 945 #170412 12:07:24 server id 1  end_log_pos 1032 CRC32 0x4c1c75fc         Query   thread_id=3     exec_time=0     error_code=0            //删除表记录的时间点 SET TIMESTAMP=1491970044/*!*/; DELETE FROM tb1 /*!*/; # at 1032 #170412 12:07:24 server id 1  end_log_pos 1063 CRC32 0xccf549b2         Xid = 12 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 

2. 执行指定Pos节点范围内的sql命令恢复数据

   根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：

   [root@dbsvr1 ~]# mysqlbinlog  --start-datetime="2017-04-12 12:06:55"   --stop-datetime="2017-04-12 12:07:23"  /var/lib/mysql/mysql-bin.000002 | mysql -u root -p Enter password:                                     //验证口令 

3. 确认恢复结果

   mysql> SELECT * FROM db1.tb1; +----+--------+ | id | name   | +----+--------+ | 1 | Jack   | | 2 | Kenthy | | 3 | Bob    | +----+--------+ 3 rows in set (0.00 sec)