mysql binlog 恢複指定表資料

mysql  binlog 恢複指定表資料

一、登入資料庫重新整理binlog

1.1)檢視目前的binlog

MySQL> 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已經重新整理成功。

二、查詢二進制日志位置

mysql> 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                                      |

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

三、從二進制日志中擷取表被删除的pos:

mysqlbinlog /log/binlog_3308/bin.000012 -d jz_seckill | grep  --ignore-case DROP  -A3 -B4

    ----------------------------------------------------

    # at 775995049

    #160720  9:37:57 server id 18500  end_log_pos 775995186     Query   thread_id=249201    exec_time=0 error_code=0

    SET TIMESTAMP=1468978677;

    SET @@session.foreign_key_checks=1;

    DROP TABLE `t_sec_praise_record_copy`

    ;

    # at 775995186

    #160720  9:37:58 server id 18500  end_log_pos 775995224     GTID 0-18500-5952648

    ----------------------------------------------------

    775995049這個開始drop的。

四、從binlog中擷取指定資料庫的改變資料

用mysqlbinlog 恢複ivr資料庫在二進制日志test-150-bin.000003中的資料

這裡假設從上一次mysql備份後，隻有一個binlog産生：bin.000012

/usr/local/mysql_3308/bin/mysqlbinlog -d jz_seckill --stop-position=775995049  /log/binlog_3308/bin.000012 >/tmp/seckill.sql

如果從上次備份重新整理binlog，到發現表被删掉的過程中産生了多個binlog。則要按照binlog産生的順序，那

麼恢複的次序應該是按照binglog的産生的序号，從小到大依次恢複。

假如從上次備份，到發現表被删除，共有兩個binlog檔案，分别是test-150-bin.000002，test-150-bin.000003 ，

則按照binlog序号從小到大的排列，恢複的順序應該是：

mysqlbinlog -d jz_seckill /log/binlog_3308/bin.000011 >/tmp/seckill.sql

mysqlbinlog -d jz_seckill  --stop-position=775995049 /log/binlog_3308/bin.000011 >>/tmp/seckill.sql

由于恢複的檔案seckill.sql中包含了整個seckill資料庫的所有表，我們隻要恢複指定的表，還要對恢複出來的sql進行過濾。

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

;

;

;

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腳本，恢複到資料庫即可。

如果單獨恢複單獨一個庫，則可以使用：

（2）你可以通過–one-database 參數選擇性的恢複單個資料庫：

mysqlbinlog --stop-date='2005-04-20 9:59:59' /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd –one-database db_test

mysqlbinlog --stop-date='2005-04-20 9:59:59' /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd –one-database db_test