Accidental deletion and data recovery during development

When the data platform accidentally deletes data, but does not know the specific time point, a fuzzy time is given. Colleagues use rollback for reverse recovery (parsing binlog to generate reverse SQL ), I use a backup set to restore positive recovery of applybinlog, and solve the problem as soon as possible [original positive recovery method] select the time before the data goes wrong, the latest backup set is restored to a new instance (r

When the data platform accidentally deletes data, but does not know the specific time point, a fuzzy time is given. Colleagues use rollback for reverse recovery (parsing binlog to generate reverse SQL ), I use the backup set to restore the apply binlog positive recovery, and solve the problem as soon as possible [original positive recovery method] select the time before the data has a problem, the latest backup set is restored to a new instance (r

When the data platform accidentally deletes data, but does not know the specific time point, a fuzzy time is given. Colleagues use rollback for reverse recovery (parsing binlog to generate reverse SQL ), I use the backup set to restore apply binlog to forward recovery and solve the problem as soon as possible.

[Original normal recovery method]

• Select the latest backup set to be restored to a new instance (restorebak. pl recovery tool is supported for easy operation)

• Copy the binlog between the backup set pos and the problematic pos to the new instance (the binlog copy operation is cumbersome)

• Use mysqlbinlog-stop-datetime to apply binlog to the new instance to restore the consistent data snapshot before the data goes wrong (mysqlbinlog supports only one binlog apply at a time, which is cumbersome)

In this example, the pos point search is generally performed through a method similar to mysqlbinlog-no-defaults-stop-datetime = "11:11:11" | grep "specific operation "? -C 3: first, determine a range through the general time period, and then precisely find the specific pos in the range.

In fact, mysql has a function master_pos_wait? You can specify a specific pos and execute the event in the returned period. Therefore, the restoration process is improved and the new instance is directed to the original primary database to pull the binlog.

[New positive direction recovery method]

• Select the latest backup set to be restored to a new instance before the data occurrence time (the recovery tool is supported for easy operation)
• Start slave; select MASTER_POS_WAIT ('mysql-bin.000396', 67698920,0); stop slave ;? (Trigger the stop slave operation after the relay operation reaches the specified pos)
• Show slave status \ G ;?? (Save the replication relationship)
• Change master? Master_host = '', master_user = 'slave ', master_password = 'slave ';? (Cancel the replication relationship to prevent misoperation)

[Problem]

This method will execute several more events on the new instance, so you need to advance the pos set by MASTER_POS_WAIT. It is only applicable to rough scenarios based on time recovery, but this scenario accounts for the vast majority of cases.

?MASTER_POS_WAIT (Log_name,Log_pos[,Timeout])

This function is useful for control of master/slave synchronization. it blocks until the slave has read and applied all updates up to the specified position in the master log. the return value is the number of log events the slave had to wait for to advance to the specified position. the function returns?NULL? If the slave SQL thread is not started, the slave's master information is not initialized, the arguments are incorrect, or an error occurs. It returns?-1? If the timeout has been exceeded. If the slave SQL thread stops while?MASTER_POS_WAIT ()? Is waiting, the function returns?NULL. If the slave is past the specified position, the function returns immediately.

If?Timeout? Value is specified ,?MASTER_POS_WAIT ()? Stops waiting when?Timeout? Seconds have elapsed .?TimeoutMust be greater than 0; a zero or negative?Timeout? Means no timeout.

This function is unsafe for statement-based replication. Beginning with MySQL 5.5.1, a warning is logged if you use this function when?Binlog_format? Is set?STATEMENT. (Bug #47995)

[Share]

Later, my colleagues shared that, in fact, start slave can directly specify the pos and sweat to be stopped.

Start slave [SQL _THREAD] UNTIL MASTER_LOG_FILE ='Log_name', MASTER_LOG_POS =?Log_pos

An? UNTIL? Clause may be added to specify that the slave shocould start and run until the SQL thread reaches a given point in the master binary log or in the slave relay log. when the SQL thread reaches that point, it stops. if? SQL _THREAD? Option is specified in the statement, it starts only the SQL thread. Otherwise, it starts both slave threads. If the SQL thread is running,? UNTIL? Clause is ignored and a warning is issued.

For? UNTIL? Clause, you must specify both a log file name and position. Do not mix master and relay log options.

Any? UNTIL? Condition is reset by a subsequent? Stop slave? Statement,? Start slave? Statement that includes des noUNTIL? Clause, or a server restart.

The? UNTIL? Clause can be useful for debugging replication, or to cause replication to proceed until just before the point where you want to avoid having the slave replicate an event. For example, if an unwise? Drop table? Statement was executed on the master, you can use? UNTIL? To tell the slave to execute up to that point but no farther. To find what the event is, use?Mysqlbinlog? With the master binary log or slave relay log, or by using? Show binlog events? Statement.

If you are using? UNTIL? To have the slave process replicated queries in sections, it is recommended that you start the slave with? -Skip-slave-start? Option to prevent the SQL thread from running when the slave server starts. it is probably best to use this option in an option file rather than on the command line, so that an unexpected server restart does not cause it to be forgotten.

[Final positive recovery method]

• Select the latest backup set to be restored to a new instance before the data occurrence time (the recovery tool is supported for easy operation)
• Start slave until master_log_file = 'mysql-bin.000396', master_log_pos = 67698920;
• Show slave status \ G ;?? (Save the replication relationship)
• Change master? Master_host = '', master_user = 'slave ', master_password = 'slave ';? (Cancel the replication relationship to prevent misoperation)

[Improvement]

You can directly execute the start slave until statement in the recovery tool, which is a big gain.

Thanks to idea of yinfeng and Zhisheng, And to Weixi for sharing their ideas.

Original article address: development, deletion, and data recovery by mistake. Thank you for sharing it with the original author.