MySQL emulation: Recovery of on-line error update

As a DBA, careful, calm is the primary basic quality. However, there will always be a distraction or the development of the students in the wrong operation and so on ... This simulates a false update operation and then resumes.

If the development of students after the wrong operation is best not to tamper with the production environment, you need to record a few information to DBA students to recover data.

1:binlog_format, autocommit, binlog position, point-in-time of mis-operation

mysql> SHOW VARIABLES like '%binlog_format% '; +---------------+-------+| variable_name | Value |+---------------+-------+| Binlog_format | Row   |+---------------+-------+1 row in Set (0.00 sec) mysql> SHOW MASTER STATUS \g*************************** 1. Ro W ***************************            file:binlog.000048        position:442    binlog_do_db:binlog_ignore_db:1 Row in Set (0.00 sec)

The point of time is around June 29, 2015 16:50:00-June 29, 2015 17:50:00

mysql> SHOW VARIABLES like '%autocommit% '; +---------------+-------+| variable_name | Value |+---------------+-------+| Autocommit    | OFF   |+---------------+-------+1 row in Set (0.00 sec)

　　

2: If the operation within a thing, while the autocommit is off, you can directly try to rollback. See if you can get it back.

3: Raw Data

Mysql> SELECT * FROM t1.t1;+------+------+| ID1  | name |+------+------+|    8 | U11  | |    8 | U12  | |    8 | U13  | |    8 | U14  | |    8 | U15  | |    8 | name | |    8 | name | |    8 | name | |    8 | Name |+------+------+9 rows in Set (0.00 sec)

After the wrong operation

mysql> Update t1.t1 Set id1 = 10; Query OK, 9 rows Affected (0.00 sec) rows Matched:9  changed:9  warnings:0mysql> commit; Query OK, 0 rows affected (0.01 sec)

　　

Restore start:

1: Find the current write Binlog information:

mysql> SHOW MASTER STATUS \g*************************** 1. Row ***************************            file:binlog.000048        position:777    binlog_do_db:binlog_ignore_db:1 row In Set (0.00 sec)

2: Estimated misoperation point in time: between 2015-06-29 16:50:00 and 2015-06-29 17:20:00,

3: Open another window parsing binlog

Mysqlbinlog--start-datetime= ' 2015-06-29 01:30:00 '--stop-datetime= ' 2015-06-29 02:10:00 '-vv binlog.000048

[[email protected] mysqllog]# mysqlbinlog--start-datetime= ' 2015-06-29 01:30:00 '--stop-datetime= ' 2015-06-29 02:10:00 '-VV binlog.000048/*!40019 set @ @session. max_insert_delayed_threads=0*/;/*!50003 Set @[email protected ] @COMPLETION_TYPE, completion_type=0*/;D elimiter/*!*/;# at 4#150629 1:44:39 Server ID 1 end_log_pos 107 start:binlog V 4, Server v 5.5.20-log created 150629 1:44:39# warning:this Binlog is either in use or was not closed properly. BINLOG ' Dwwrvq8baaaazwaaagsaaaabaaqans41ljiwlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaav aaegggaaaaicagcaa== '/*!*/;# at 107#150629 1:45:06 Server ID 1 end_log_pos 173 querythread_id=7exec_time=0error_code=0se T timestamp=1435567506/*!*/; SET @ @session. pseudo_thread_id=7/*!*/; SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/; SET @ @session. sql_mode=0/*!*/; SET @ @session. auto_increment_increment=1, @ @session. auto_increment_oFfset=1/*!*/;/*!\c latin1 *//*!*/; SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/; SET @ @session. lc_time_names=0/*!*/; SET @ @session. collation_database=default/*!*/;  begin/*!*/;# at 173# in 215#150629 1:44:56 server ID 1 end_log_pos 215 table_map: ' t1 '. ' T1 ' mapped to number 33#150629 1:44:56 Server ID 1 end_log_pos 415 update_rows:table ID flags:stmt_end_fbinlog ' IAWRVRMBAAAAKGAAANCAAAAAACEAAAAAAAE aanqxaaj0mqaca/4c/jwdiawrvrgbaaaayaaaaj8baaaaaceaaaaaaaeaav///ayaaaaddtex/agaaaaddtex/ayaaaaddtey/agaaaaddtey/ ayaaaaddtez/agaaaaddtez/ayaaaaddte0/agaaaaddte0/ayaaaaddte1/agaaaaddte1/ Ayaaaaebmftzfwiaaaabg5hbwx8bgaaaaruyw1l/agaaaaebmftzfwgaaaabg5hbwx8caaaaaruyw1l/ayaaaaebmftzfwiaaaabg5hbwu= '/* !*/;### UPDATE ' t1 '. ' T1 ' # # # where### @1=6 */INT meta=0 nullable=1 is_null=0 */### @2= ' U11 '/* STRING (meta=65084) n ullable=1 is_null=0 */### set### @1=8/INT meta=0 nullable=1 is_null=0 */### @2= ' U11 '/* STRING (meta=65084 nulla)ble=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=6 * INT meta=0 nullable=1 is_null=0 */### @2= ' U12 '/* STRING (60 ) meta=65084 nullable=1 is_null=0 */### set### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' U12 '/* STRING (Me) ta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # where### @1=6/* INT meta=0 nullable=1 is_null=0 */### @2= ' U13  '/* STRING meta=65084 nullable=1 is_null=0 */### set### @1=8/INT meta=0 nullable=1 is_null=0 */### @2= ' U13 '/* STRING meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # where### @1=6/* INT meta=0 nullable=1 is_null=0 *    /### @2= ' U14 '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' U14 '/* STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=6 */INT meta=0 Nullable =1 is_null=0 */### @2= ' U15 '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=8/* INT meta=0 nullable=1 I S_null=0 */### @2= ' U15'/* STRING meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=6 */INT meta=0 nullable=1 is_null =0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=6 */INT meta=0 nu llable=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=8/* INT meta=0 Nulla ble=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=6 /* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=6/* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING ' meta=65084 nullable=1 is_null=0 */### S et### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING ' meta=65084 nullable=1 is_null=0 */# at 415#150629 1:45:06 Server ID 1 end_log_pos 442 Xid = 313commit/*!*/;# at 442#150629 2:00:29 Server ID 1 End_log_pos 508 querythread_id=7exec_time =0error_code=0set timestamp=1435568429/*!*/;  begin/*!*/;# at 508# in 550#150629 2:00:27 server ID 1 end_log_pos 550 table_map: ' t1 '. ' T1 ' mapped to number 33#150629 2:00:27 Server ID 1 end_log_pos update_rows:table ID flags:stmt_end_fbinlog ' KWMRVRMBAAAAKGAAACYCAAAAACEAAAAAAAE aanqxaaj0mqaca/4c/jwdkwmrvrgbaaaayaaaao4caaaaaceaaaaaaaeaav///agaaaaddtex/aoaaaaddtex/agaaaaddtey/aoaaaaddtey/ agaaaaddtez/aoaaaaddtez/agaaaaddte0/aoaaaaddte0/agaaaaddte1/aoaaaaddte1/ Agaaaaebmftzfwkaaaabg5hbwx8caaaaaruyw1l/aoaaaaebmftzfwiaaaabg5hbwx8cgaaaaruyw1l/agaaaaebmftzfwkaaaabg5hbwu= '/* !*/;### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 is_null=0 */### @2= ' U11 '/* STRING (meta=65084) n ullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U11 '/* STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' U12 '/* STRING (a) meta=65084 nullable=1 is_null=0 */### set### @1=1 0/* INT meta=0 nullable=1 is_null=0 */### @2= ' U12 '/* STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' U13 '/* STRING (meta=65084 nullable=1 is_null=0 */##) # set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U13 '/* STRING (meta=65084 nullable=1 is_null=0 */### U)  PDATE ' t1 '. ' T1 ' # # # where### @1=8 * INT meta=0 nullable=1 is_null=0 */### @2= ' U14 '/* STRING (meta=65084 nullable=1) Is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U14 '/* STRING (meta=65084 nullable=1) _null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 is_null=0 */### @2= ' U15 '/* STRING (META=6) 5084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U15 '/* STRING meta=65084 nullable=1 is_null=0 * /### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 * INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING (meta=65084 nul) lable=1 is_null=0 */### set### @1=10/INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING (meta=65084 nulla) ble=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 * INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING (6  0) meta=65084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING (60) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # where### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' Name '/* STRING meta=65084 nullable=1 is_null=0 */### set### @1=10/INT meta=0 nullable=1 is_null=0 */### @2= ' na Me '/* STRING meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 Is_nu Ll=0 */### @2= ' name '/* STRING meta=65084 nullable=1 is_null=0 */### set### @1=10/INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING meta=65084 nullable=1 is_null=0 */# at 750#150629 2:00:29 Server ID 1 end_log_pos 777 Xid = 318commit/*!*/;D E Limiter; # End of log filerollback/* Added by Mysqlbinlog */;/*!50003 SET [email protected]_completion_type*/;

　　

Find the error update:

# # # UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 is_null=0 */### @2= ' U11 '/* STRING (meta=65084 nulla) ble=1 is_null=0 */### set### @1=10/INT meta=0 nullable=1 is_null=0 */### @2= ' U11 '/* STRING (meta=65084 nullable) =1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 is_null=0 */### @2= ' U12 '/* STRING (each) m eta=65084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U12 '/* STRING ($) meta =65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # where### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' U13 ' /* STRING meta=65084 nullable=1 is_null=0 */### set### @1=10 * INT meta=0 nullable=1 is_null=0 */### @2= ' U13 '/* STRING meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # where### @1=8/* INT meta=0 nullable=1 is_null=0 */    # # @2= ' U14 '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U14 '/* STRING (60) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # where### @1=8/* INT meta=0 nullable=1 is_null=0 */### @2= ' U15 '/* STRING meta=65084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null=0 */### @2= ' U1 5 '/* STRING meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 Is_nul L=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nullable=1 is_null= 0 */### @2= ' name '/* * STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8 */INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### set### @1=10/* INT meta=0 nu llable=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1  =8/* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING (a) meta=65084 nullable=1 is_null=0 */### set### @1=10 /* INT meta=0 nullable=1 is_null=0 */### @2= ' name '/* * STRING ($) meta=65084 nullable=1 is_null=0 */### UPDATE ' t1 '. ' T1 ' # # # where### @1=8/* I NT meta=0 nullable=1 is_null=0 */### @2= ' name '/* STRING ($) meta=65084 nullable=1 is_null=0 */

Error-handling statements after processing

UPDATE ' t1 '. ' T1 ' WHERE id1=8/* INT meta=0 nullable=1 is_null=0 */name= ' U11 '/* STRING (meta=65084 nullable=1 is_null) =0 */set id1=10/* INT meta=0 nullable=1 is_null=0/name= ' U11 '/* STRING * meta=65084 nullable=1 is_null=0 */update ' T1 '. ' T1 ' WHERE id1=8/* INT meta=0 nullable=1 is_null=0 */name= ' U12 '/* STRING ' meta=65084 nullable=1 is_null=0 */se T id1=10/* INT meta=0 nullable=1 is_null=0 */name= ' U12 '/* STRING ($) meta=65084 nullable=1 is_null=0 */update ' t1 '. ' t 1 ' WHERE id1=8/* INT meta=0 nullable=1 is_null=0 */name= ' U13 '/* STRING (meta=65084 nullable=1 is_null=0 */set id1=)   Ten/* INT meta=0 nullable=1 is_null=0 */name= ' U13 '/* STRING (meta=65084 nullable=1 is_null=0 */update ' t1 '). ' T1 ' WHERE Id1=8/* INT meta=0 nullable=1 is_null=0 */name= ' U14 '/* STRING ($) meta=65084 nullable=1 is_null=0 */set id1=10/* I  NT meta=0 nullable=1 is_null=0 */name= ' U14 '/* STRING ($) meta=65084 nullable=1 is_null=0 */update ' t1 '. ' T1 ' WHERE id1=8 /* INT meta=0 nullable=1 is_null=0 */name= ' U15 '/* STRING ($) meta=65084 nullable=1 is_null=0 */set id1=10/* INT meta=0 nullable=1 is_null=0 */name= ' U15 '/* STRING ($) meta=65084 nullable=1 is_null=0 */update ' t1 '. ' T1 ' WHERE id1=8 */INT meta=0 nullable=1 Is_nu Ll=0 */name= ' name '/* * STRING ($) meta=65084 nullable=1 is_null=0 */set id1=10/* INT meta=0 nullable=1 is_null=0 */NA Me= ' name '/* * STRING (meta=65084 nullable=1 is_null=0 */update ' t1 '. ' T1 ' WHERE id1=8 */INT meta=0 nullable=1 is_null=0 */name= ' name '/* STRING (meta=65084 nullable=1 is_null=0 */set id1=10/* INT meta=0 nullable=1 is_null=0 */name= ' n Ame '/* STRING meta=65084 nullable=1 is_null=0 */update ' t1 '. ' T1 ' WHERE id1=8 */INT meta=0 nullable=1 is_null=0 */n Ame= ' name '/* * STRING (meta=65084 nullable=1 is_null=0 */set id1=10/* INT meta=0 nullable=1 is_null=0 */name= ' name ' /* STRING meta=65084 nullable=1 is_null=0 */update ' t1 '. ' T1 ' WHERE id1=8 */INT meta=0 nullable=1 is_null=0 */name= ' Name '/* STRING ' meta=65084 nullable=1 is_null=0 * * 

According to the corresponding statement of a sentence to recover.

　　

MySQL emulation: Recovery of on-line error update