MySQL binlog 企业案例升级版

需求：
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据

分析问题解决问题

1 如果没有误操作 数据会是什么样 先模拟

 #-------------------------------------------------------------------------------
 #
 # day8 练习
 # Author：nod
 # Date:18-08-05
 #-------------------------------------------------------------------------------

 #-------------------------------------------------------------------------------
 #
 # 本次练习题当中6为误操作，因而处理方式就是跳过第六步看看正常数据如何
 # 再按步骤1-7在主库上进行操作 恢复数据等进行测试
 #-------------------------------------------------------------------------------

 需求：
 1、创建一个数据库 oldboy
 2、在oldboy下创建一张表t1
 3、插入5行任意数据
 4、全备
 5、插入两行数据，任意修改3行数据，删除1行数据
 6、删除所有数据
 7、再t1中又插入5行新数据，修改3行数据
 需求，跳过第六步恢复表数据

 mysql> create database oldboy;
 mysql> use oldboy;
 mysql> create table t1(id int);
 mysql> insert into t1 values(1),(2),(3),(4),(5);
 mysql> select * from t1;
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    4 |
 |    5 |
 +------+

 # 插入两行数据，任意修改3行数据，删除1行数据
 mysql> insert into t1 values(6),(7);
 mysql> update t1 set id=11 where id=1;
 mysql> update t1 set id=22 where id=2;
 mysql> update t1 set id=33 where id=3;
 mysql> delete from t1 where id=7;

 # 再t1中又插入5行新数据，修改3行数据
 mysql> insert into t1 values(8),(9),(10),(11),(12);
 mysql> update t1 set id=100 where id=10;
 mysql> update t1 set id=110 where id=11;
 mysql> update t1 set id=120 where id=12;

 # 正确效果数据
 mysql> select * from t1;
 +------+
 | id   |
 +------+
 |  110 |
 |   22 |
 |   33 |
 |    4 |
 |    5 |
 |    6 |
 |    8 |
 |    9 |
 |  100 |
 |  110 |
 |  120 |
 +------+

分析后得出对应的解决方法

全备恢复 +第5步骤+第七步骤 跳过第六步骤 需要对mysqlbinlog进行分析

#-------------------------------------------------------------------------------
#
# day8 练习实际步骤
# Author：nod
# Date:18-08-05
#-------------------------------------------------------------------------------

需求：
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据

#-------------------------------------------------------------------------------
# 1-3步骤
#-------------------------------------------------------------------------------
mysql> flush logs;
mysql> create database oldboy;
mysql> use oldboy;
Database changed
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3),(4),(5);

#-------------------------------------------------------------------------------
# 全备
#-------------------------------------------------------------------------------

mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction > /backup/full3.sql

#-------------------------------------------------------------------------------
# 5 插入两行数据，任意修改3行数据，删除1行数据
#-------------------------------------------------------------------------------

mysql> insert into t1 values(6),(7);
mysql> update t1 set id=11 where id=1;
mysql> update t1 set id=22 where id=2;
mysql> update t1 set id=33 where id=3;
mysql> delete from t1 where id=7;

#-------------------------------------------------------------------------------
# 6 删除所有数据   1637
#-------------------------------------------------------------------------------
#
mysql> delete from t1;

#-------------------------------------------------------------------------------
# 7 再t1中又插入5行新数据，修改3行数据
#-------------------------------------------------------------------------------
mysql> insert into t1 values(8),(9),(10),(11),(12);    1702 2586
mysql> update t1 set id=100 where id=10;
mysql> update t1 set id=110 where id=11;
mysql> update t1 set id=120 where id=12;

#-------------------------------------------------------------------------------
# 脏数据
#-------------------------------------------------------------------------------
mysql> select * from t1;
+------+
| id   |
+------+
|    8 |
|    9 |
|  100 |
|  110 |
|  120 |
+------+
5 rows in set (0.00 sec)

#-------------------------------------------------------------------------------
# 数据处理部分
#-------------------------------------------------------------------------------
#

#-------------------------------------------------------------------------------
# 数据分析/backup/full3.sql
#-------------------------------------------------------------------------------

22：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=533;

#-------------------------------------------------------------------------------
# 数据分析mysql-bin.000013
#-------------------------------------------------------------------------------
mysqlbinlog  --base64-output=decode-rows  -vvv /data/mysql/mysql-bin.000013
mysqlbinlog --start-position=533 --stop-position=1590 /data/mysql/mysql-bin.000013 >/backup/inc1.sql
mysqlbinlog --start-position=1702 --stop-position=2586   /data/mysql/mysql-bin.000013 >/backup/inc2.sql

#-------------------------------------------------------------------------------
# 在备份库上进行主库全备恢复+步骤6 步骤7操作恢复
#-------------------------------------------------------------------------------
mysql> source /backup/full3.sql;
mysql> source /backup/inc1.sql
mysql> source /backup/inc2.sql

#-------------------------------------------------------------------------------
# ERROR 1666 (HY000): Cannot execute statement: impossible to write to binary log since statement is in row # # format and BINLOG_FORMAT = STATEMENT.
# 解决方法：
# 主库是row模式 因而备份库也要row模式 修改后解决
#-------------------------------------------------------------------------------

#-------------------------------------------------------------------------------
# 验证数据 数据恢复正确
#-------------------------------------------------------------------------------
mysql> select * from t1;
+------+
| id   |
+------+
|  110 |
|   22 |
|   33 |
|    4 |
|    5 |
|    6 |
|    8 |
|    9 |
|  100 |
|   11 |
|  120 |
+------+
11 rows in set (0.00 sec)

#-------------------------------------------------------------------------------
# 将带有误操作（删除）的t1表导出
#-------------------------------------------------------------------------------
[root@db01 backup]# mysqldump -uroot -p123 -S /data/3307/mysql.sock oldboy t1 >/backup/t1.sql

#-------------------------------------------------------------------------------
# 主库进行恢复t1表操作
#-------------------------------------------------------------------------------

#-------------------------------------------------------------------------------
# 查看oldboy下t1的数据，目前数据应该是有问题的数据
#-------------------------------------------------------------------------------
mysql> use oldboy;
mysql> select * from t1;
+------+
| id   |
+------+
|    8 |
|    9 |
|  100 |
|  110 |
|  120 |
+------+
5 rows in set (0.00 sec)

#-------------------------------------------------------------------------------
# 开始恢复t1操作
#-------------------------------------------------------------------------------
mysql> source /backup/t1.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

#-------------------------------------------------------------------------------
# 恢复完毕 检查数据 恢复操作完成
#-------------------------------------------------------------------------------

mysql> select * from t1;
+------+
| id   |
+------+
|  110 |
|   22 |
|   33 |
|    4 |
|    5 |
|    6 |
|    8 |
|    9 |
|  100 |
|   11 |
|  120 |
+------+
11 rows in set (0.00 sec)

分析的binlogcode

要对比分析begin commit 要多加体会

 #-------------------------------------------------------------------------------
 #
 # day8练习题binlog文件
 #-------------------------------------------------------------------------------

 [root@db01 backup]# mysqlbinlog  --base64-output=decode-rows  -vvv /data/mysql/mysql-bin.000013
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 # at 4
 #180805 23:51:27 server id 6  end_log_pos 120 CRC32 0xd5be46c7     Start: binlog v 4, server v 5.6.38-log created 180805 23:51:27
 # at 120
 #180805 23:51:36 server id 6  end_log_pos 220 CRC32 0xeaa89679     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484296/*!*/;
 SET @@session.pseudo_thread_id=13/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=1075838976/*!*/;
 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=33/*!*/;
 SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 create database oldboy
 /*!*/;
 # at 220
 #180805 23:52:19 server id 6  end_log_pos 321 CRC32 0x23a9496d     Query    thread_id=13    exec_time=0    error_code=0
 use `oldboy`/*!*/;
 SET TIMESTAMP=1533484339/*!*/;
 create table t1(id int)
 /*!*/;
 # at 321
 #180805 23:52:30 server id 6  end_log_pos 395 CRC32 0x73300da7     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484350/*!*/;
 BEGIN
 /*!*/;
 # at 395
 #180805 23:52:30 server id 6  end_log_pos 442 CRC32 0xe87526c3     Table_map: `oldboy`.`t1` mapped to number 283
 # at 442
 #180805 23:52:30 server id 6  end_log_pos 502 CRC32 0x681dd9f1     Write_rows: table id 283 flags: STMT_END_F
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
 # at 502
 #180805 23:52:30 server id 6  end_log_pos 533 CRC32 0x0b8ae04d     Xid = 9250
 COMMIT/*!*/;
 # at 533
 #180805 23:55:47 server id 6  end_log_pos 607 CRC32 0x9df4f1c1     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484547/*!*/;
 BEGIN
 /*!*/;
 # at 607
 #180805 23:55:47 server id 6  end_log_pos 654 CRC32 0x5f289eed     Table_map: `oldboy`.`t1` mapped to number 324
 # at 654
 #180805 23:55:47 server id 6  end_log_pos 699 CRC32 0x56cbcd59     Write_rows: table id 324 flags: STMT_END_F
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=7 /* INT meta=0 nullable=1 is_null=0 */
 # at 699
 #180805 23:55:47 server id 6  end_log_pos 730 CRC32 0xd3310e29     Xid = 10117
 COMMIT/*!*/;
 # at 730
 #180805 23:55:52 server id 6  end_log_pos 804 CRC32 0x4fa560fa     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484552/*!*/;
 BEGIN
 /*!*/;
 # at 804
 #180805 23:55:52 server id 6  end_log_pos 851 CRC32 0x0c2cb62f     Table_map: `oldboy`.`t1` mapped to number 324
 # at 851
 #180805 23:55:52 server id 6  end_log_pos 897 CRC32 0x7641ef30     Update_rows: table id 324 flags: STMT_END_F
 ### UPDATE `oldboy`.`t1`
 ### WHERE
 ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
 ### SET
 ###   @1=11 /* INT meta=0 nullable=1 is_null=0 */
 # at 897
 #180805 23:55:52 server id 6  end_log_pos 928 CRC32 0xc96642d8     Xid = 10118
 COMMIT/*!*/;
 # at 928
 #180805 23:55:58 server id 6  end_log_pos 1002 CRC32 0xaa5b0537     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484558/*!*/;
 BEGIN
 /*!*/;
 # at 1002
 #180805 23:55:58 server id 6  end_log_pos 1049 CRC32 0xef25a191     Table_map: `oldboy`.`t1` mapped to number 324
 # at 1049
 #180805 23:55:58 server id 6  end_log_pos 1095 CRC32 0x93732ae2     Update_rows: table id 324 flags: STMT_END_F
 ### UPDATE `oldboy`.`t1`
 ### WHERE
 ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
 ### SET
 ###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
 # at 1095
 #180805 23:55:58 server id 6  end_log_pos 1126 CRC32 0xfeee4a7c     Xid = 10119
 COMMIT/*!*/;
 # at 1126
 #180805 23:56:04 server id 6  end_log_pos 1200 CRC32 0x4134c384     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484564/*!*/;
 BEGIN
 /*!*/;
 # at 1200
 #180805 23:56:04 server id 6  end_log_pos 1247 CRC32 0x3e17dba1     Table_map: `oldboy`.`t1` mapped to number 324
 # at 1247
 #180805 23:56:04 server id 6  end_log_pos 1293 CRC32 0x758c18dd     Update_rows: table id 324 flags: STMT_END_F
 ### UPDATE `oldboy`.`t1`
 ### WHERE
 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
 ### SET
 ###   @1=33 /* INT meta=0 nullable=1 is_null=0 */
 # at 1293
 #180805 23:56:04 server id 6  end_log_pos 1324 CRC32 0x2838e762     Xid = 10120
 COMMIT/*!*/;
 # at 1324
 #180805 23:56:09 server id 6  end_log_pos 1398 CRC32 0x3101a798     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484569/*!*/;
 BEGIN
 /*!*/;
 # at 1398
 #180805 23:56:09 server id 6  end_log_pos 1445 CRC32 0x4173fe96     Table_map: `oldboy`.`t1` mapped to number 324
 # at 1445
 #180805 23:56:09 server id 6  end_log_pos 1485 CRC32 0xc1755087     Delete_rows: table id 324 flags: STMT_END_F
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=7 /* INT meta=0 nullable=1 is_null=0 */
 # at 1485
 #180805 23:56:09 server id 6  end_log_pos 1516 CRC32 0xeb175961     Xid = 10121
 COMMIT/*!*/;
 # at 1516
 #180805 23:56:43 server id 6  end_log_pos 1590 CRC32 0xce6b49dd     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484603/*!*/;
 BEGIN
 /*!*/;
 # at 1590
 #180805 23:56:43 server id 6  end_log_pos 1637 CRC32 0xcc5d90fa     Table_map: `oldboy`.`t1` mapped to number 324
 # at 1637
 #180805 23:56:43 server id 6  end_log_pos 1702 CRC32 0x67646caa     Delete_rows: table id 324 flags: STMT_END_F
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=11 /* INT meta=0 nullable=1 is_null=0 */
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=33 /* INT meta=0 nullable=1 is_null=0 */
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
 ### DELETE FROM `oldboy`.`t1`
 ### WHERE
 ###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
 # at 1702
 #180805 23:56:43 server id 6  end_log_pos 1733 CRC32 0xda91d5fc     Xid = 10122
 COMMIT/*!*/;
 # at 1733
 #180805 23:57:48 server id 6  end_log_pos 1807 CRC32 0x9c648b02     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484668/*!*/;
 BEGIN
 /*!*/;
 # at 1807
 #180805 23:57:48 server id 6  end_log_pos 1854 CRC32 0x705c9c37     Table_map: `oldboy`.`t1` mapped to number 324
 # at 1854
 #180805 23:57:48 server id 6  end_log_pos 1914 CRC32 0xa37a228b     Write_rows: table id 324 flags: STMT_END_F
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=8 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=9 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=11 /* INT meta=0 nullable=1 is_null=0 */
 ### INSERT INTO `oldboy`.`t1`
 ### SET
 ###   @1=12 /* INT meta=0 nullable=1 is_null=0 */
 # at 1914
 #180805 23:57:48 server id 6  end_log_pos 1945 CRC32 0x704ab243     Xid = 10125
 COMMIT/*!*/;
 # at 1945
 #180805 23:57:54 server id 6  end_log_pos 2019 CRC32 0x733a9b67     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484674/*!*/;
 BEGIN
 /*!*/;
 # at 2019
 #180805 23:57:54 server id 6  end_log_pos 2066 CRC32 0xa8ee9f3a     Table_map: `oldboy`.`t1` mapped to number 324
 # at 2066
 #180805 23:57:54 server id 6  end_log_pos 2112 CRC32 0x69de9370     Update_rows: table id 324 flags: STMT_END_F
 ### UPDATE `oldboy`.`t1`
 ### WHERE
 ###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
 ### SET
 ###   @1=100 /* INT meta=0 nullable=1 is_null=0 */
 # at 2112
 #180805 23:57:54 server id 6  end_log_pos 2143 CRC32 0x436ebefc     Xid = 10126
 COMMIT/*!*/;
 # at 2143
 #180805 23:57:59 server id 6  end_log_pos 2217 CRC32 0x40f410d1     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484679/*!*/;
 BEGIN
 /*!*/;
 # at 2217
 #180805 23:57:59 server id 6  end_log_pos 2264 CRC32 0x413676a6     Table_map: `oldboy`.`t1` mapped to number 324
 # at 2264
 #180805 23:57:59 server id 6  end_log_pos 2310 CRC32 0xbf31e991     Update_rows: table id 324 flags: STMT_END_F
 ### UPDATE `oldboy`.`t1`
 ### WHERE
 ###   @1=11 /* INT meta=0 nullable=1 is_null=0 */
 ### SET
 ###   @1=110 /* INT meta=0 nullable=1 is_null=0 */
 # at 2310
 #180805 23:57:59 server id 6  end_log_pos 2341 CRC32 0x65c77e3d     Xid = 10127
 COMMIT/*!*/;
 # at 2341
 #180805 23:58:03 server id 6  end_log_pos 2415 CRC32 0x69a74ca6     Query    thread_id=13    exec_time=0    error_code=0
 SET TIMESTAMP=1533484683/*!*/;
 BEGIN
 /*!*/;
 # at 2415
 #180805 23:58:03 server id 6  end_log_pos 2462 CRC32 0x2180734d     Table_map: `oldboy`.`t1` mapped to number 324
 # at 2462
 #180805 23:58:03 server id 6  end_log_pos 2508 CRC32 0xbdcc9544     Update_rows: table id 324 flags: STMT_END_F
 ### UPDATE `oldboy`.`t1`
 ### WHERE
 ###   @1=12 /* INT meta=0 nullable=1 is_null=0 */
 ### SET
 ###   @1=120 /* INT meta=0 nullable=1 is_null=0 */
 # at 2508
 #180805 23:58:03 server id 6  end_log_pos 2539 CRC32 0x6aceade6     Xid = 10128
 COMMIT/*!*/;
 # at 2539
 #180806  0:02:12 server id 6  end_log_pos 2586 CRC32 0xad6d9bf8     Rotate to mysql-bin.000014  pos: 4
 DELIMITER ;
 # End of log file
 ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;