天天看點
傳回

mysql恢複--flashback

author：skate

time:2013/07/23 

mysqlbinlog flashback功能是淘寶彭立勳(http://www.penglixun.com/)開源的一個工具

主要功能：對rows格式的binlog可以進行逆向操作，如delete反向生成insert，update生成反向的update,insert反向生成delete。在DBA誤操作時，可以把資料庫恢複到以前某個時間點

1.源碼安裝

下載下傳：

http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog

安裝

#cd mysql-5.5.18

#wget http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff

#patch -p0 <5.5.18_flashback.diff

2.直接下載下傳二進制檔案：http://download.csdn.net/download/wyzxg/5809143

安裝完之後，看mysqlbinlog是否帶有“-B”參數，如果帶有-B，說明具有flashback功能

使用場景

場景一：因誤操作等原因，需要把資料庫恢複到之前某個狀态

測試：

測試表：

mysql> select * from zxg;

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

| a    | b    |

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

|    1 | 1    |

|    2 | 2    |

|    3 | 3    |

|    5 | 4    |

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

4 rows in set (0.00 sec)

檢視position

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000018 |      459 |              |                  |

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

1 row in set (0.00 sec)

開始做DML操作

mysql> insert into zxg select * from zxg;

Query OK, 4 rows affected (0.02 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into zxg select * from zxg;

Query OK, 8 rows affected (0.03 sec)

Records: 8  Duplicates: 0  Warnings: 0

檢視這時的position

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000018 |      893 |              |                  |

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

1 row in set (0.00 sec)

用mysqlbinlog分析binlog，找到要要恢複的position位置：

[[email protected] data]# /mysqlinstall/mysqlbinlog  -v --base64-output=decode-rows  --start-position=459 --stop-position=893  mysql-bin.000018  | more

;

;

DELIMITER ;

# at 459

#130723 22:19:15 server id 2  end_log_pos 530   Query   thread_id=141   exec_time=0     error_code=0

SET TIMESTAMP=1374589155;

SET @@session.pseudo_thread_id=141;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;

SET @@session.sql_mode=0;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;

;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33;

SET @@session.lc_time_names=0;

SET @@session.collation_database=DEFAULT;

BEGIN

;

# at 530

# at 578

#130723 22:19:15 server id 2  end_log_pos 578   Table_map: `testdb2`.`zxg` mapped to number 44

#130723 22:19:15 server id 2  end_log_pos 635   Write_rows: table id 44 flags: STMT_END_F

### INSERT INTO testdb2.zxg

### SET

###   @1=1

###   @2='1'

### INSERT INTO testdb2.zxg

### SET

###   @1=2

###   @2='2'

### INSERT INTO testdb2.zxg

### SET

###   @1=3

###   @2='3'

### INSERT INTO testdb2.zxg

### SET

###   @1=5

###   @2='4'

# at 635

#130723 22:19:15 server id 2  end_log_pos 662   Xid = 2290949

COMMIT;

# at 662

#130723 22:19:17 server id 2  end_log_pos 733   Query   thread_id=141   exec_time=0     error_code=0

SET TIMESTAMP=1374589157;

BEGIN

;

# at 733

# at 781

#130723 22:19:17 server id 2  end_log_pos 781   Table_map: `testdb2`.`zxg` mapped to number 44

#130723 22:19:17 server id 2  end_log_pos 866   Write_rows: table id 44 flags: STMT_END_F

### INSERT INTO testdb2.zxg

### SET

###   @1=1

###   @2='1'

### INSERT INTO testdb2.zxg

### SET

###   @1=2

###   @2='2'

### INSERT INTO testdb2.zxg

### SET

###   @1=3

###   @2='3'

### INSERT INTO testdb2.zxg

### SET

###   @1=5

###   @2='4'

### INSERT INTO testdb2.zxg

### SET

###   @1=1

###   @2='1'

### INSERT INTO testdb2.zxg

### SET

###   @1=2

###   @2='2'

### INSERT INTO testdb2.zxg

### SET

###   @1=3

###   @2='3'

### INSERT INTO testdb2.zxg

### SET

###   @1=5

###   @2='4'

# at 866

#130723 22:19:17 server id 2  end_log_pos 893   Xid = 2290950

COMMIT;

DELIMITER ;

# End of log file

ROLLBACK ;

">[email protected]_COMPLETION_TYPE*/;

[[email protected] data]#

說明：如果binlog很大，可以搜尋表名，找到start-position和stop-position，在這例子中可以看找到的position是否可記錄的一樣。

用mysqlbinlog驗證flashback的binlog是否和預期的一樣

[[email protected] data]# /mysqlinstall/mysqlbinlog  -B -v --base64-output=decode-rows  --start-position=459 --stop-position=893  mysql-bin.000018  | more

;

;

DELIMITER ;

# at 459

# at 530

# at 578

# at 635

# at 662

# at 733

# at 781

# at 866

DELIMITER ;

#130723 22:19:17 server id 2  end_log_pos 893   Xid = 2290950

COMMIT;

#130723 22:19:17 server id 2  end_log_pos 781   Table_map: `testdb2`.`zxg` mapped to number 44

#130723 22:19:17 server id 2  end_log_pos 866   Write_rows: table id 44 flags: STMT_END_F

### DELETE FROM testdb2.zxg

### WHERE

###   @1=1

###   @2='1'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=2

###   @2='2'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=3

###   @2='3'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=5

###   @2='4'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=1

###   @2='1'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=2

###   @2='2'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=3

###   @2='3'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=5

###   @2='4'

#130723 22:19:17 server id 2  end_log_pos 733   Query   thread_id=141   exec_time=0     error_code=0

SET TIMESTAMP=1374589157;

BEGIN

;

#130723 22:19:15 server id 2  end_log_pos 662   Xid = 2290949

COMMIT;

#130723 22:19:15 server id 2  end_log_pos 578   Table_map: `testdb2`.`zxg` mapped to number 44

#130723 22:19:15 server id 2  end_log_pos 635   Write_rows: table id 44 flags: STMT_END_F

### DELETE FROM testdb2.zxg

### WHERE

###   @1=1

###   @2='1'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=2

###   @2='2'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=3

###   @2='3'

### DELETE FROM testdb2.zxg

### WHERE

###   @1=5

###   @2='4'

DELIMITER ;

# End of log file

ROLLBACK ;

">[email protected]_COMPLETION_TYPE*/;

确認無誤的話，就可以恢複了

# /mysqlinstall/mysqlbinlog  -B   --start-position=459 --stop-position=893  mysql-bin.000018 | mysql testdb2

然後再查表zxg資料，發現表zxg已經恢複，如下

mysql> select * from zxg;

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

| a    | b    |

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

|    1 | 1    |

|    2 | 2    |

|    3 | 3    |

|    5 | 4    |

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

4 rows in set (0.00 sec)

說明：在生産環境這種場景使用不現實，但在開發測試環境或獨有資料庫，這種場景到可以一用，用之前最好備好

場景二：因對某個表誤操作，隻需要把表恢複到誤操作之前

這種場景實際遇到比較多，隻是在場景一的基礎上把誤操作的binlog位置找到，然後用mysqlbinlog反向解析，為了安全起見，

最好在另外一台機器上，建立相同的表(所在資料庫也要一樣)，然後在此基礎上恢複，最後在把恢複結果導入源庫中。這樣比較安全

參考：

http://www.mysqlsupport.cn/mysqlbinlog-flashback/  

--end--

Mysql Management
上一篇: 超圖軟體上市 ——股票代碼300036
下一篇: 周例會的作用

繼續閱讀