第一步：配置MySQL打开binlog日志
# vim /etc/mysql/mysql.conf.d/mysqld.cnf
- 找到下面两行，将前面的#去掉
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log # binlog日志目录在/var/log/mysql/目录下
binlog_do_db = nst #这里是你要binlog日志记录的数据库名字(nst 是我的数据库名)
- 重启MySQL
sudo service mysql restart
- 查看binlog是否生效
# mysql -u root -p nst
mysql> show master status; # 可以看到有记录
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | nst | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
第二步：数据测试并进行恢复
- 查看binlog日志文件
# cd /var/log/mysql/
# sudo mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005 # 5.7版本对sql语句加密了，使用--base64-output=decode-rows 查看=
-
进行如下三步操作:
- 创建数据表test
mysql> create table test( -> id int, -> name char(64) -> );- 插入数据
mysql> insert into test values(1, 'feng'),(2, 'scort');- 删除表
mysql> drop table test; -
现在test表没有了，我想恢复test表！那我们就使用binlog日志文件来恢复
- 查看binlog日志文件，记录下要恢复的开始位置和结束位置
# cd /var/log/mysql/ # sudo mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005 # 5.7版本对sql语句加密了，使用--base64-output=decode-rows 查看 -----内容如下----- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170814 8:08:13 server id 1 end_log_pos 123 CRC32 0x77cdc807 Start: binlog v 4, server v 5.7.19-0ubuntu0.16.04.1-log created 170814 8:08:13 # Warning: this binlog is either in use or was not closed properly. # at 123 #170814 8:08:13 server id 1 end_log_pos 154 CRC32 0x6912ae9a Previous-GTIDs # [empty] # at 154 #170814 8:25:22 server id 1 end_log_pos 219 CRC32 0x8e9ef4db Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #170814 8:25:22 server id 1 end_log_pos 334 CRC32 0x91d186ce Query thread_id=228 exec_time=0 error_code=0 use `nst`/*!*/; SET TIMESTAMP=1502670322/*!*/; SET @@session.pseudo_thread_id=228/*!*/; 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/*!*/; DROP TABLE `test` /* generated by server */ /*!*/; # at 334 #170814 8:25:50 server id 1 end_log_pos 399 CRC32 0xe931e689 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 399 #170814 8:25:50 server id 1 end_log_pos 513 CRC32 0xe5e319b0 Query thread_id=228 exec_time=0 error_code=0 SET TIMESTAMP=1502670350/*!*/; create table test( id int, name char(64) ) /*!*/; # at 513 #170814 8:26:32 server id 1 end_log_pos 578 CRC32 0x817100ec Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 578 #170814 8:26:32 server id 1 end_log_pos 649 CRC32 0x489ac5f1 Query thread_id=228 exec_time=0 error_code=0 SET TIMESTAMP=1502670392/*!*/; BEGIN /*!*/; # at 649 #170814 8:26:32 server id 1 end_log_pos 698 CRC32 0x29773e5b Table_map: `nst`.`test` mapped to number 369 # at 698 #170814 8:26:32 server id 1 end_log_pos 754 CRC32 0x05cff49c Write_rows: table id 369 flags: STMT_END_F ### INSERT INTO `nst`.`test` ### SET ### @1=1 ### @2='feng' ### INSERT INTO `nst`.`test` ### SET ### @1=2 ### @2='scort' # at 754 #170814 8:26:32 server id 1 end_log_pos 785 CRC32 0xf408e2ea Xid = 7852 COMMIT/*!*/; # at 785 #170814 8:27:08 server id 1 end_log_pos 850 CRC32 0xbca456aa Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 850 #170814 8:27:08 server id 1 end_log_pos 965 CRC32 0x649ac834 Query thread_id=228 exec_time=0 error_code=0 SET TIMESTAMP=1502670428/*!*/; DROP TABLE `test` /* generated by server */ /*!*/; 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*/;at 334处看到了创建表的sql，这个位置是开始位置;
最后一条记录commit的地方at 785,这个地方就是结束位置- 开始恢复
# sudo mysqlbinlog --start-position=334 --stop-position=785 mysql-bin.000005 | mysql -u root -p nst- 登录库存查看结果,恢复成功
# mysql -u root -p nst mysql> show tables; +-------------------------+ | Tables_in_nst | +-------------------------+ | test | +-------------------------+ # 恢复成功 mysql> select * from test; +------+-------+ | id | name | +------+-------+ | 1 | feng | | 2 | scort | +------+-------+
