mysql 5.7.13 log_第三个模块 MySQL5.7 通过binlog日志恢复数据

现在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 |

+------+-------+