mysql日志恢复指定表

很多时候,MYSQL数据库由于一些误操作,需要恢复指定表,或某个表指定时间点的数据,这个时候需要根据MYSQL的binlog日志做时间点的恢复,下面将介绍这个方案的具体步骤:

一、登录数据库刷新binlog

1.1)查看当前的binlog

show master status;

+---------------------+----------+--------------+------------------+-------------------+

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+-------------------+

| test-150-bin.000003 |  2895377 |              |                  |                   |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

1.2)刷新binlog

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

1.3)确认刷新binlog成功

mysql> show master status;

+---------------------+----------+--------------+------------------+-------------------+

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+-------------------+

| test-150-bin.000004 |      120 |              |                  |                   |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

用show master status 命令查看当前的binlog已经由test-150-bin.000003变为test-150-bin.000004,

证明binlog已经刷新成功。

二、查询二进制日志位置

show variables like'log_bin%';

+---------------------------------+------------------------------------------+

| Variable_name                   | Value                                    |

+---------------------------------+------------------------------------------+

| log_bin                         | ON                                       |

| log_bin_basename                | /db/mysql5.6/data/test-150-bin       |

| log_bin_index                   | /db/mysql5.6/data/test-150-bin.index |

| log_bin_trust_function_creators | ON                                       |

| log_bin_use_v1_row_events       | OFF                                      |

+---------------------------------+------------------------------------------+

三、从二进制日志中获取表被删除的时间

$ mysqlbinlog test-150-bin.000003|grep  --ignore-case DROP  -A3 -B4

COMMIT/*!*/;

# at 122869

#140126 17:03:35 server id 150  end_log_pos 122989 CRC32 0x8707c4c4     Query   thread_id=12519 exec_time=0     error_code=0

SET TIMESTAMP=1390727015/*!*/;

DROP TABLE `test` /* generated by server */

/*!*/;

# at 122989

#140126 17:04:06 server id 150  end_log_pos 123039 CRC32 0x43476aad     Rotate to test-150-bin.000004  pos: 4

drop语句的前两行表名drop语句的执行时间是在 17:03:35

mysql> SELECT from_unixtime('1390727015');

+-----------------------------+

| from_unixtime('1390727015') |

+-----------------------------+

| 2014-01-26 17:03:35.000000  |

+-----------------------------+

1 row in set

四、从binlog中获取指定数据库的改变数据

用mysqlbinlog 恢复ivr数据库在二进制日志test-150-bin.000003中的数据

这里假设从上一次mysql备份后,只有一个binlog产生,即登录数据库时查询到binlog test-150-bin.000003

mysqlbinlog -d ivr --stop-datetime='2014-01-26 17:03:35' test-150-bin.000003 >recover_ivr.sql

如果从上次备份刷新binlog,到发现表被删掉的过程中产生了多个binlog。则要按照binlog产生的顺序,那

么恢复的次序应该是按照binglog的产生的序号,从小到大依次恢复。

假如从上次备份,到发现表被删除,共有两个binlog文件,分别是test-150-bin.000002,test-150-bin.000003 ,

则按照binlog序号从小到大的排列,恢复的顺序应该是:

mysqlbinlog -d ivr  test-150-bin.000002 >recover_ivr.sql

mysqlbinlog -d ivr --stop-datetime='2014-01-26 17:03:35' test-150-bin.000003 >>recover_ivr.sql

由于恢复的文件recover_ivr.sql中包含了整个ivr数据库的所有表,我们只要恢复指定的表,还要对恢复出来的sql进行过滤。

[mysql@bjrac01 data]$ more recover_ivr.sql |grep  --ignore-case -E 'insert|update|delete' -A2 -B2|grep testtuo

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

--

# at 122721

#140126 17:03:28 server id 150  end_log_pos 122721 CRC32 0xe0f851bb     Intvar

SET INSERT_ID=4/*!*/;

#140126 17:03:28 server id 150  end_log_pos 122838 CRC32 0x9efcc7b2     Query   thread_id=12578 exec_time=0     error_code=0

use `ivr`/*!*/;

SET TIMESTAMP=1390727008/*!*/;

INSERT INTO `testtuo` (`name`) VALUES ('d')

/*!*/;

# at 122838

将过滤后的结果保存为sql脚本,恢复到数据库即可。

下面是转换的精简命令示例:

导出指定时间的所有SQL变更操作记录:

mysqlbinlog --base64-output=decode-rows -v --stop-datetime='2017-05-25 19:38:35' mysql-bin.000007 >/home/fule_0525_sql.txt

过滤出指定表的SQL操作记录:

grep -B3 -w xy_members fule_0525_sql.txt >xy_member.sql