當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 5.6 binlog_format_ROW 格式binlog 在MySQL5.6上的数据恢复实验

發(fā)布時間：2023/11/27 数据库 34 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 5.6 binlog_format_ROW 格式binlog 在MySQL5.6上的数据恢复实验小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

5.6和5.7版本的MySQL，有個參數(shù)binlog_row_image，默認值為FULL，表示記錄的是全部的binlog操作日志(僅在binlog_format=ROW時候生效)。此外binlog_row_image還可以是minimal，表示binlog記錄的就只是影響后的行。如此一來使用ROW格式就能節(jié)約很多的磁盤空間。

因此，我們服務器上就可以直接設置binlog_format=ROW格式了，至于binlog_row_image設置為FULL還是minimal，各位就自行考慮了。

環(huán)境版本如下：

> SELECT @@version

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

| @@version? |

|-------------|

| 5.6.34-log? |

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

> SELECT @@binlog_format;

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

| @@binlog_format? |

|-------------------|

| ROW? ? ? ? ? ? ? |

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

假設我們的操作都是在一個庫里面執(zhí)行的，MySQL服務器上只跑了這一個hellodb業(yè)務的數(shù)據(jù)庫。

如果數(shù)據(jù)庫多的話，還會增大恢復的難度，如下事例(下面的grant操作實例不夠明顯，但是差不多就是那個操作步驟)：

step1? 準備一個全量備份：

mysqldump --flush-logs -A > /root/all.sql

step2? 手工誤操作刪除部分數(shù)據(jù)

> use hellodb;

> delete from scores where `StuID`=8 AND `ID`=14;

# 模擬誤操作刪了1條用戶數(shù)據(jù),然后上報需要回滾操作。

此時還有個線程，執(zhí)行了 grant all on *.* to 'abc'@'%'; 假設這個grant操作的是管理員正常的操作。

> delete from scores where `StuID`=5 AND `ID`=10;

# 模擬再次誤操作刪了1條用戶數(shù)據(jù),然后上報需要回滾操作。

........

在我們發(fā)現(xiàn)操作錯了，到匯報這期間，還要很多用戶的正常操作，也造成了數(shù)據(jù)庫的一些更新。例如下面這條插入的記錄。

........

INSERT INTO students VALUES(100,'www',100,'F',3,5);

........

step3? mysql停機

/etc/init.d/mysql stop

step4 導出相關的binlog

cd /data/mysql

看下最近的binlog文件，假如我這里看到的是 mysql.0000010 這個文件。

# 先導出一份binlog文件，

mysqlbinlog --base64-output=decode-rows -vv mysql.000010 > /root/1.sql

vi /root/1.sql 找到剛才我們誤操作的部分，類似如下(下面被我添加了部分注釋)：

BEGIN? ---> 這個BEGIN-COMMIT要刪除

/*!*/;

# at 662771? --->? 注意這個Postion,回滾要用到

#170116 15:21:31 server id 106? end_log_pos 662826 CRC32 0xc2733cd6? ? Table_map: `hellodb`.`scores` mapped to number 156

# at 662826

#170116 15:21:31 server id 106? end_log_pos 662873 CRC32 0x0d302d22? ? Delete_rows: table id 156 flags: STMT_END_F

### DELETE FROM `hellodb`.`scores`

### WHERE

###? @1=14 /* INT meta=0 nullable=0 is_null=0 */

###? @2=8 /* INT meta=0 nullable=0 is_null=0 */

###? @3=4 /* SHORTINT meta=0 nullable=0 is_null=0 */

###? @4=57 /* TINYINT meta=0 nullable=1 is_null=0 */

# at 662873

#170116 15:21:31 server id 106? end_log_pos 662904 CRC32 0x7bda6198? ? Xid = 1136

COMMIT/*!*/;

# at 662904? ---> 這個BEGIN COMMIT要保留,這個是用戶的正常操作的sql

#170116 15:21:42 server id 106? end_log_pos 663027 CRC32 0xa7dc153b? ? Query? thread_id=10? ? exec_time=0? ? error_code=0

SET TIMESTAMP=1484551302/*!*/;

grant all on *.* to 'abc'@'%'

/*!*/;

# at 663027

#170116 15:21:49 server id 106? end_log_pos 663102 CRC32 0xa7570f25? ? Query? thread_id=10? ? exec_time=0? ? error_code=0

SET TIMESTAMP=1484551309/*!*/;

BEGIN? ---> 這個BEGIN-COMMIT要刪除

/*!*/;

# at 663102? --->? 注意這個Postion,回滾要用到

#170116 15:21:49 server id 106? end_log_pos 663157 CRC32 0x20b81986? ? Table_map: `hellodb`.`scores` mapped to number 156

# at 663157

#170116 15:21:49 server id 106? end_log_pos 663204 CRC32 0x26d9f8b8? ? Delete_rows: table id 156 flags: STMT_END_F

### DELETE FROM `hellodb`.`scores`

### WHERE

###? @1=10 /* INT meta=0 nullable=0 is_null=0 */

###? @2=5 /* INT meta=0 nullable=0 is_null=0 */

###? @3=7 /* SHORTINT meta=0 nullable=0 is_null=0 */

###? @4=63 /* TINYINT meta=0 nullable=1 is_null=0 */

# at 663204

#170116 15:21:49 server id 106? end_log_pos 663235 CRC32 0x81f9c1d6? ? Xid = 1138

COMMIT/*!*/;

# at 663235

#170116 15:22:59 server id 106? end_log_pos 663310 CRC32 0xb3b0508d? ? Query? thread_id=10? ? exec_time=0? ? error_code=0

SET TIMESTAMP=1484551379/*!*/;

BEGIN ---> 這個BEGIN-COMMIT要保留,這個是用戶的正常操作的sql

/*!*/;

# at 663310? --->? 注意這個Postion,回滾要用到

#170116 15:22:59 server id 106? end_log_pos 663373 CRC32 0x17a48bfc? ? Table_map: `hellodb`.`students` mapped to number 152

# at 663373

#170116 15:22:59 server id 106? end_log_pos 663424 CRC32 0x0acbd405? ? Write_rows: table id 152 flags: STMT_END_F

### INSERT INTO `hellodb`.`students`

### SET

###? @1=100 /* INT meta=0 nullable=0 is_null=0 */

###? @2='www' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

###? @3=100 /* TINYINT meta=0 nullable=0 is_null=0 */

###? @4=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

###? @5=3 /* TINYINT meta=0 nullable=1 is_null=0 */

###? @6=5 /* INT meta=0 nullable=1 is_null=0 */

# at 663424

#170116 15:22:59 server id 106? end_log_pos 663455 CRC32 0x1f37c970? ? Xid = 1139

COMMIT/*!*/;

step5 準備恢復的數(shù)據(jù)

mysqlbinlog mysql.000010 --stop-position=662771 > /root/22.sql? ? ? ? ? ? ? ? ? ? ? ? ? ? # 導出step2中第一個DELETE前的數(shù)據(jù)

mysqlbinlog mysql.000010 --start-position=662904? --stop-position=663027 > /root/33.sql? # 導出step2中這個正常的grant授權操作語句

mysqlbinlog mysql.000010 --start-position=663310? > /root/44.sql? ? ? ? ? ? ? ? ? ? ? ? ? # 導出step2中的那個正常的INSERT操作及其后面的全部SQL操作

step6 開始恢復數(shù)據(jù)

/etc/init.d/mysql start

mysql < /root/all.sql

mysql < /root/22.sql

mysql < /root/33.sql

mysql < /root/44.sql

step7 檢查恢復后結果

> use hellodb;

> SELECT * from students where `StuID`=100 AND `Name`='www';

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

|---------+--------+-------+----------+-----------+-------------|

|? ? 100 | www? ? |? 100 | F? ? ? ? |? ? ? ? 3 |? ? ? ? ? 5 |

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

> SELECT * from scores where `StuID`=8 AND `ID`=14;

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

|------+---------+------------+---------|

|? 14 |? ? ? 8 |? ? ? ? ? 4 |? ? ? 57 |

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

> SELECT * from scores where `StuID`=5 AND `ID`=10;

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

|------+---------+------------+---------|

|? 10 |? ? ? 5 |? ? ? ? ? 7 |? ? ? 63 |

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

可以看到恢復的效果不錯。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

数据库

mysql 5.6 binlog_format_ROW 格式binlog 在MySQL5.6上的数据恢复实验

總結