Shulou(Shulou.com)06/01 Report--

Brief introduction to DBA or developers, sometimes mistakenly delete or update data. The traditional method of database recovery is to use the previous backup plus the binlog before misoperation to recover the data. This method takes a long time to restore the backup, and even needs downtime maintenance, which seriously reduces the availability of the system.

The flashback function of MySQL is developed on the basis of MySQL-5.5.18 by Peng Lixun of Taobao. With the change of binlog format and the increase of types, the version based on 5.5.18 cannot be used in the current mainstream version 5.6. Therefore, the civilian software transplants this function to version 5.6, so that the relevant personnel have stronger control over the database.

Use the method to locate the misoperated binlog location mysqlbinlog-v | grep

Give an example

Mysqlbinlog-v-- base64-output=decode-rows / var/lib/mysql/128.000110 | grep-B10-A5 items

The output is similar to

# at 1093

# 160215 15:22:39 server id 10 end_log_pos 1240 CRC32 0x6146d30e Table_map: `zabbix`.`items` mapped to number 73

# at 1240

# 160215 15:22:39 server id 10 end_log_pos 1824 CRC32 0xe2d557b8 Update_rows: table id 73 flags: STMT_END_F

'/ *! * /

# UPDATE `zabbix`.`items`

# WHERE

# @ 1mm 24138

# @ 2room0

# @ 3 thanks'

# @ 4 million dollars

# @ 5cm 10106

Confirm the relevant location assuming that the relevant location is found, the start position 1017 and the end position 2563

Mysqlbinlog-v-base64-output=decode-rows-start-position=1017-stop-position=2563 / var/lib/mysql/128.000110

two。 Use flashback software provided by civilian software

Mysqlbinlog-B-v-- start-position=1017-- stop-position=2563 / var/lib/mysql/128.000110 | mysql-u-p

Note: do the test on the slave machine that is not in use before recovery, and then do it on the main library after the test is successful.

Limit the software to use binlog to record pre-operation and post-operation data mirroring. There are the following restrictions

1) binlog_format=row

2) only insert, update and delete are supported

3) ddl statements such as drop, truncate, alter are not supported

Conclusion the function of flashback can avoid the long process of database recovery in emergency. Elevate Gao availability to one level.

Download address: http://pan.baidu.com/s/1hrEG9BA

Original address: http://www.cnblogs.com/youge-OneSQL/p/5249736.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.