mysql误删数据的恢复（淘宝技术团队）

转自：http://www.taobaodba.com/  

阿里巴巴集团数据库技术团队

数据平台开发误删数据，但是不知道具体时间点，就给了一个模糊时间，同事使用rollback逆向恢复（解析binlog产生反向SQL），我使用备份集恢复apply binlog正向恢复，尽快解决问题

【原正向恢复方式】

• 选择数据出问题时间之前最近的备份集恢复在一个新实例（restorebak.pl恢复工具支持，操作方便）

• 将备份集pos与数据出问题pos之间的binlog拷贝到新实例上（拷贝binlog操作繁琐）

• 使用mysqlbinlog –stop-datetime来apply binlog到新实例，以便恢复到数据出问题前的一致性数据快照（mysqlbinlog每次只支持单个binlog apply ,操作繁琐)

 

其中关于pos点的查找一般都是通过类似mysqlbinlog –no-defaults –stop-datetime=”2012-11-11 11:11:11″ |grep “具体操作”  -C 3方式来查找的，先通过大体时间段确定一个范围，然后在范围内的精确查找具体的pos

 

其实mysql有个函数master_pos_wait 可以指定具体pos并返回期间执行事件，于是改进恢复过程，将新实例直接指向原主库拉binlog

【新正向恢复方式】

• 选择数据出问题时间之前最近的备份集恢复在一个新实例（恢复工具支持，操作方便）
• start slave;select MASTER_POS_WAIT(‘mysql-bin.000396′,67698920,0);stop slave; （relay 到指定pos后 触发stop slave操作）
• show slave status\G;  （保存复制关系）
• change master to  master_host=’ ‘, master_user=’slave’,master_password=’slave’; （取消复制关系，防止误操作开启复制）

【问题】

这种方式在新实例上会多执行几个事件，所以需要将MASTER_POS_WAIT设置的pos提前一些，只适用于基于时间恢复的粗略场景，但是这种场景占绝大多数情况

 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 a 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 to STATEMENT. (Bug #47995)

 

【分享】

后来同事分享其实start slave直接可以指定停止的pos，汗

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 should 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 the 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, the UNTIL clause is ignored and a warning is issued.

For an 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, a START SLAVE statement that includes 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 a 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 the –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.

【最终正向恢复方式】

• 选择数据出问题时间之前最近的备份集恢复在一个新实例（恢复工具支持，操作方便）
• start slave until master_log_file=’mysql-bin.000396′,master_log_pos=67698920;
• show slave status\G;  （保存复制关系）
• change master to  master_host=’ ‘, master_user=’slave’,master_password=’slave’; （取消复制关系，防止误操作开启复制）

【改进】

可以直接把start slave until语句直接做到恢复工具中，很大的收获

感谢印风和智盛的idea，感谢维西的分享