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

Use binlog to reply to mistakenly delete tables and libraries

There are two common ways to view the contents of a binlog log:

1 、 # / usr/local/mysql/bin/mysqlbinlog / usr/local/mysql/data/mysql-bin.000013

2. Mysql > show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Option resolution:

IN 'log_name' specifies the name of the binlog file to query (if not specified is the first binlog file)

FROM pos specifies the starting point of pos (if not specified, it is calculated from the first pos point of the whole file)

LIMIT [offset,] offset (0 if not specified)

Total number of row_count queries (all rows are not specified)

d. Specify to query mysql-bin.000021 this file, starting from pos point: 8224, query 10 items

Mysql > show binlog events in 'mysql-bin.000021' from 8224 limit 10\ G

6. Recover data from binlog logs

# / usr/local/mysql/bin/mysql-uroot-p123456-v

< /root/BAK.zyyshop.sql; 常用选项： --start-position=953 起始pos点 --stop-position=1437 结束pos点 --start-datetime="2013-11-29 13:18:54" 起始时间点 --stop-datetime="2013-11-29 13:21:53" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志) 小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径； B.指定pos结束点恢复(部分恢复)： @ --stop-position=953 pos结束点 注：此pos结束点介于"导入实验数据"与更新"name='李四'"之间，这样可以恢复到更改"name='李四'"之前的"导入测试数据" # /usr/local/mysql/bin/mysqlbinlog --stop-position=953 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop C.指定pso点区间恢复(部分恢复)： 更新 name='李四' 这条数据，日志区间是Pos[1038] -->

End_log_pos [1164], according to transaction interval: Pos [953]-- > End_log_pos [1195]

C1. To resume the operation of name=' Li Si 'alone, you can do this:

# / usr/local/mysql/bin/mysqlbinlog-start-position=1038-stop-position=1164-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

It can also be recovered separately by transaction interval, as follows:

# / usr/local/mysql/bin/mysqlbinlog-start-position=953-stop-position=1195-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

# / usr/local/mysql/bin/mysqlbinlog-- start-datetime= "2013-11-29 13:18:54"-- stop-datetime= "2013-11-29 13:21:53"-- database=zyyshop / usr/local/mysql/data/mysql-bin.000021 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

Summary: the so-called restore is to ask mysql to re-execute the sql statements saved in the specified paragraph in the binlog log one by one.

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.