MySQL binlog 企业案例升级版
需求：
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据
分析问题解决问题
1 如果没有误操作 数据会是什么样 先模拟
1 #------------------------------------------------------------------------------- 2 # 3 # day8 练习 4 # Author：nod 5 # Date:18-08-05 6 #------------------------------------------------------------------------------- 7 8 9 #------------------------------------------------------------------------------- 10 # 11 # 本次练习题当中6为误操作，因而处理方式就是跳过第六步看看正常数据如何 12 # 再按步骤1-7在主库上进行操作 恢复数据等进行测试 13 #------------------------------------------------------------------------------- 14 15 需求： 16 1、创建一个数据库 oldboy 17 2、在oldboy下创建一张表t1 18 3、插入5行任意数据 19 4、全备 20 5、插入两行数据，任意修改3行数据，删除1行数据 21 6、删除所有数据 22 7、再t1中又插入5行新数据，修改3行数据 23 需求，跳过第六步恢复表数据 24 25 26 27 mysql> create database oldboy; 28 mysql> use oldboy; 29 mysql> create table t1(id int); 30 mysql> insert into t1 values(1),(2),(3),(4),(5); 31 mysql> select * from t1; 32 +------+ 33 | id | 34 +------+ 35 | 1 | 36 | 2 | 37 | 3 | 38 | 4 | 39 | 5 | 40 +------+ 41 42 # 插入两行数据，任意修改3行数据，删除1行数据 43 mysql> insert into t1 values(6),(7); 44 mysql> update t1 set id=11 where id=1; 45 mysql> update t1 set id=22 where id=2; 46 mysql> update t1 set id=33 where id=3; 47 mysql> delete from t1 where id=7; 48 49 # 再t1中又插入5行新数据，修改3行数据 50 mysql> insert into t1 values(8),(9),(10),(11),(12); 51 mysql> update t1 set id=100 where id=10; 52 mysql> update t1 set id=110 where id=11; 53 mysql> update t1 set id=120 where id=12; 54 55 # 正确效果数据 56 mysql> select * from t1; 57 +------+ 58 | id | 59 +------+ 60 | 110 | 61 | 22 | 62 | 33 | 63 | 4 | 64 | 5 | 65 | 6 | 66 | 8 | 67 | 9 | 68 | 100 | 69 | 110 | 70 | 120 | 71 +------+
分析后得出对应的解决方法
全备恢复 +第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 要多加体会
1 #------------------------------------------------------------------------------- 2 # 3 # day8练习题binlog文件 4 #------------------------------------------------------------------------------- 5 6 [root@db01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000013 7 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 8 /*!40019 SET @@session.max_insert_delayed_threads=0*/; 9 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 10 DELIMITER /*!*/; 11 # at 4 12 #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 13 # at 120 14 #180805 23:51:36 server id 6 end_log_pos 220 CRC32 0xeaa89679 Query thread_id=13 exec_time=0 error_code=0 15 SET TIMESTAMP=1533484296/*!*/; 16 SET @@session.pseudo_thread_id=13/*!*/; 17 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; 18 SET @@session.sql_mode=1075838976/*!*/; 19 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; 20 /*!\C utf8 *//*!*/; 21 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; 22 SET @@session.lc_time_names=0/*!*/; 23 SET @@session.collation_database=DEFAULT/*!*/; 24 create database oldboy 25 /*!*/; 26 # at 220 27 #180805 23:52:19 server id 6 end_log_pos 321 CRC32 0x23a9496d Query thread_id=13 exec_time=0 error_code=0 28 use `oldboy`/*!*/; 29 SET TIMESTAMP=1533484339/*!*/; 30 create table t1(id int) 31 /*!*/; 32 # at 321 33 #180805 23:52:30 server id 6 end_log_pos 395 CRC32 0x73300da7 Query thread_id=13 exec_time=0 error_code=0 34 SET TIMESTAMP=1533484350/*!*/; 35 BEGIN 36 /*!*/; 37 # at 395 38 #180805 23:52:30 server id 6 end_log_pos 442 CRC32 0xe87526c3 Table_map: `oldboy`.`t1` mapped to number 283 39 # at 442 40 #180805 23:52:30 server id 6 end_log_pos 502 CRC32 0x681dd9f1 Write_rows: table id 283 flags: STMT_END_F 41 ### INSERT INTO `oldboy`.`t1` 42 ### SET 43 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ 44 ### INSERT INTO `oldboy`.`t1` 45 ### SET 46 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ 47 ### INSERT INTO `oldboy`.`t1` 48 ### SET 49 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ 50 ### INSERT INTO `oldboy`.`t1` 51 ### SET 52 ### @1=4 /* INT meta=0 nullable=1 is_null=0 */ 53 ### INSERT INTO `oldboy`.`t1` 54 ### SET 55 ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ 56 # at 502 57 #180805 23:52:30 server id 6 end_log_pos 533 CRC32 0x0b8ae04d Xid = 9250 58 COMMIT/*!*/; 59 # at 533 60 #180805 23:55:47 server id 6 end_log_pos 607 CRC32 0x9df4f1c1 Query thread_id=13 exec_time=0 error_code=0 61 SET TIMESTAMP=1533484547/*!*/; 62 BEGIN 63 /*!*/; 64 # at 607 65 #180805 23:55:47 server id 6 end_log_pos 654 CRC32 0x5f289eed Table_map: `oldboy`.`t1` mapped to number 324 66 # at 654 67 #180805 23:55:47 server id 6 end_log_pos 699 CRC32 0x56cbcd59 Write_rows: table id 324 flags: STMT_END_F 68 ### INSERT INTO `oldboy`.`t1` 69 ### SET 70 ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ 71 ### INSERT INTO `oldboy`.`t1` 72 ### SET 73 ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ 74 # at 699 75 #180805 23:55:47 server id 6 end_log_pos 730 CRC32 0xd3310e29 Xid = 10117 76 COMMIT/*!*/; 77 # at 730 78 #180805 23:55:52 server id 6 end_log_pos 804 CRC32 0x4fa560fa Query thread_id=13 exec_time=0 error_code=0 79 SET TIMESTAMP=1533484552/*!*/; 80 BEGIN 81 /*!*/; 82 # at 804 83 #180805 23:55:52 server id 6 end_log_pos 851 CRC32 0x0c2cb62f Table_map: `oldboy`.`t1` mapped to number 324 84 # at 851 85 #180805 23:55:52 server id 6 end_log_pos 897 CRC32 0x7641ef30 Update_rows: table id 324 flags: STMT_END_F 86 ### UPDATE `oldboy`.`t1` 87 ### WHERE 88 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ 89 ### SET 90 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */ 91 # at 897 92 #180805 23:55:52 server id 6 end_log_pos 928 CRC32 0xc96642d8 Xid = 10118 93 COMMIT/*!*/; 94 # at 928 95 #180805 23:55:58 server id 6 end_log_pos 1002 CRC32 0xaa5b0537 Query thread_id=13 exec_time=0 error_code=0 96 SET TIMESTAMP=1533484558/*!*/; 97 BEGIN 98 /*!*/; 99 # at 1002 100 #180805 23:55:58 server id 6 end_log_pos 1049 CRC32 0xef25a191 Table_map: `oldboy`.`t1` mapped to number 324 101 # at 1049 102 #180805 23:55:58 server id 6 end_log_pos 1095 CRC32 0x93732ae2 Update_rows: table id 324 flags: STMT_END_F 103 ### UPDATE `oldboy`.`t1` 104 ### WHERE 105 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ 106 ### SET 107 ### @1=22 /* INT meta=0 nullable=1 is_null=0 */ 108 # at 1095 109 #180805 23:55:58 server id 6 end_log_pos 1126 CRC32 0xfeee4a7c Xid = 10119 110 COMMIT/*!*/; 111 # at 1126 112 #180805 23:56:04 server id 6 end_log_pos 1200 CRC32 0x4134c384 Query thread_id=13 exec_time=0 error_code=0 113 SET TIMESTAMP=1533484564/*!*/; 114 BEGIN 115 /*!*/; 116 # at 1200 117 #180805 23:56:04 server id 6 end_log_pos 1247 CRC32 0x3e17dba1 Table_map: `oldboy`.`t1` mapped to number 324 118 # at 1247 119 #180805 23:56:04 server id 6 end_log_pos 1293 CRC32 0x758c18dd Update_rows: table id 324 flags: STMT_END_F 120 ### UPDATE `oldboy`.`t1` 121 ### WHERE 122 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ 123 ### SET 124 ### @1=33 /* INT meta=0 nullable=1 is_null=0 */ 125 # at 1293 126 #180805 23:56:04 server id 6 end_log_pos 1324 CRC32 0x2838e762 Xid = 10120 127 COMMIT/*!*/; 128 # at 1324 129 #180805 23:56:09 server id 6 end_log_pos 1398 CRC32 0x3101a798 Query thread_id=13 exec_time=0 error_code=0 130 SET TIMESTAMP=1533484569/*!*/; 131 BEGIN 132 /*!*/; 133 # at 1398 134 #180805 23:56:09 server id 6 end_log_pos 1445 CRC32 0x4173fe96 Table_map: `oldboy`.`t1` mapped to number 324 135 # at 1445 136 #180805 23:56:09 server id 6 end_log_pos 1485 CRC32 0xc1755087 Delete_rows: table id 324 flags: STMT_END_F 137 ### DELETE FROM `oldboy`.`t1` 138 ### WHERE 139 ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ 140 # at 1485 141 #180805 23:56:09 server id 6 end_log_pos 1516 CRC32 0xeb175961 Xid = 10121 142 COMMIT/*!*/; 143 # at 1516 144 #180805 23:56:43 server id 6 end_log_pos 1590 CRC32 0xce6b49dd Query thread_id=13 exec_time=0 error_code=0 145 SET TIMESTAMP=1533484603/*!*/; 146 BEGIN 147 /*!*/; 148 # at 1590 149 #180805 23:56:43 server id 6 end_log_pos 1637 CRC32 0xcc5d90fa Table_map: `oldboy`.`t1` mapped to number 324 150 # at 1637 151 #180805 23:56:43 server id 6 end_log_pos 1702 CRC32 0x67646caa Delete_rows: table id 324 flags: STMT_END_F 152 ### DELETE FROM `oldboy`.`t1` 153 ### WHERE 154 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */ 155 ### DELETE FROM `oldboy`.`t1` 156 ### WHERE 157 ### @1=22 /* INT meta=0 nullable=1 is_null=0 */ 158 ### DELETE FROM `oldboy`.`t1` 159 ### WHERE 160 ### @1=33 /* INT meta=0 nullable=1 is_null=0 */ 161 ### DELETE FROM `oldboy`.`t1` 162 ### WHERE 163 ### @1=4 /* INT meta=0 nullable=1 is_null=0 */ 164 ### DELETE FROM `oldboy`.`t1` 165 ### WHERE 166 ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ 167 ### DELETE FROM `oldboy`.`t1` 168 ### WHERE 169 ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ 170 # at 1702 171 #180805 23:56:43 server id 6 end_log_pos 1733 CRC32 0xda91d5fc Xid = 10122 172 COMMIT/*!*/; 173 # at 1733 174 #180805 23:57:48 server id 6 end_log_pos 1807 CRC32 0x9c648b02 Query thread_id=13 exec_time=0 error_code=0 175 SET TIMESTAMP=1533484668/*!*/; 176 BEGIN 177 /*!*/; 178 # at 1807 179 #180805 23:57:48 server id 6 end_log_pos 1854 CRC32 0x705c9c37 Table_map: `oldboy`.`t1` mapped to number 324 180 # at 1854 181 #180805 23:57:48 server id 6 end_log_pos 1914 CRC32 0xa37a228b Write_rows: table id 324 flags: STMT_END_F 182 ### INSERT INTO `oldboy`.`t1` 183 ### SET 184 ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ 185 ### INSERT INTO `oldboy`.`t1` 186 ### SET 187 ### @1=9 /* INT meta=0 nullable=1 is_null=0 */ 188 ### INSERT INTO `oldboy`.`t1` 189 ### SET 190 ### @1=10 /* INT meta=0 nullable=1 is_null=0 */ 191 ### INSERT INTO `oldboy`.`t1` 192 ### SET 193 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */ 194 ### INSERT INTO `oldboy`.`t1` 195 ### SET 196 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */ 197 # at 1914 198 #180805 23:57:48 server id 6 end_log_pos 1945 CRC32 0x704ab243 Xid = 10125 199 COMMIT/*!*/; 200 # at 1945 201 #180805 23:57:54 server id 6 end_log_pos 2019 CRC32 0x733a9b67 Query thread_id=13 exec_time=0 error_code=0 202 SET TIMESTAMP=1533484674/*!*/; 203 BEGIN 204 /*!*/; 205 # at 2019 206 #180805 23:57:54 server id 6 end_log_pos 2066 CRC32 0xa8ee9f3a Table_map: `oldboy`.`t1` mapped to number 324 207 # at 2066 208 #180805 23:57:54 server id 6 end_log_pos 2112 CRC32 0x69de9370 Update_rows: table id 324 flags: STMT_END_F 209 ### UPDATE `oldboy`.`t1` 210 ### WHERE 211 ### @1=10 /* INT meta=0 nullable=1 is_null=0 */ 212 ### SET 213 ### @1=100 /* INT meta=0 nullable=1 is_null=0 */ 214 # at 2112 215 #180805 23:57:54 server id 6 end_log_pos 2143 CRC32 0x436ebefc Xid = 10126 216 COMMIT/*!*/; 217 # at 2143 218 #180805 23:57:59 server id 6 end_log_pos 2217 CRC32 0x40f410d1 Query thread_id=13 exec_time=0 error_code=0 219 SET TIMESTAMP=1533484679/*!*/; 220 BEGIN 221 /*!*/; 222 # at 2217 223 #180805 23:57:59 server id 6 end_log_pos 2264 CRC32 0x413676a6 Table_map: `oldboy`.`t1` mapped to number 324 224 # at 2264 225 #180805 23:57:59 server id 6 end_log_pos 2310 CRC32 0xbf31e991 Update_rows: table id 324 flags: STMT_END_F 226 ### UPDATE `oldboy`.`t1` 227 ### WHERE 228 ### @1=11 /* INT meta=0 nullable=1 is_null=0 */ 229 ### SET 230 ### @1=110 /* INT meta=0 nullable=1 is_null=0 */ 231 # at 2310 232 #180805 23:57:59 server id 6 end_log_pos 2341 CRC32 0x65c77e3d Xid = 10127 233 COMMIT/*!*/; 234 # at 2341 235 #180805 23:58:03 server id 6 end_log_pos 2415 CRC32 0x69a74ca6 Query thread_id=13 exec_time=0 error_code=0 236 SET TIMESTAMP=1533484683/*!*/; 237 BEGIN 238 /*!*/; 239 # at 2415 240 #180805 23:58:03 server id 6 end_log_pos 2462 CRC32 0x2180734d Table_map: `oldboy`.`t1` mapped to number 324 241 # at 2462 242 #180805 23:58:03 server id 6 end_log_pos 2508 CRC32 0xbdcc9544 Update_rows: table id 324 flags: STMT_END_F 243 ### UPDATE `oldboy`.`t1` 244 ### WHERE 245 ### @1=12 /* INT meta=0 nullable=1 is_null=0 */ 246 ### SET 247 ### @1=120 /* INT meta=0 nullable=1 is_null=0 */ 248 # at 2508 249 #180805 23:58:03 server id 6 end_log_pos 2539 CRC32 0x6aceade6 Xid = 10128 250 COMMIT/*!*/; 251 # at 2539 252 #180806 0:02:12 server id 6 end_log_pos 2586 CRC32 0xad6d9bf8 Rotate to mysql-bin.000014 pos: 4 253 DELIMITER ; 254 # End of log file 255 ROLLBACK /* added by mysqlbinlog */; 256 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 257 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
