{"rsdb":{"rid":"156495","subhead":"","postdate":"0","aid":"117465","fid":"57","uid":"1","topic":"1","content":"

?<\/p>

5.6\u548c5.7\u7248\u672c\u7684MySQL\uff0c\u6709\u4e2a\u53c2\u6570binlog_row_image\uff0c\u9ed8\u8ba4\u503c\u4e3aFULL\uff0c\u8868\u793a\u8bb0\u5f55\u7684\u662f\u5168\u90e8\u7684binlog\u64cd\u4f5c\u65e5\u5fd7\uff08\u4ec5\u5728binlog_format=ROW\u65f6\u5019\u751f\u6548\uff09\u3002\u6b64\u5916binlog_row_image\u8fd8\u53ef\u4ee5\u662fminimal\uff0c\u8868\u793abinlog\u8bb0\u5f55\u7684\u5c31\u53ea\u662f\u5f71\u54cd\u540e\u7684\u884c\u3002\u5982\u6b64\u4e00\u6765\u4f7f\u7528ROW\u683c\u5f0f\u5c31\u80fd\u8282\u7ea6\u5f88\u591a\u7684\u78c1\u76d8\u7a7a\u95f4\u3002<\/p>

?<\/p>

\u56e0\u6b64\uff0c\u6211\u4eec\u670d\u52a1\u5668\u4e0a\u5c31\u53ef\u4ee5\u76f4\u63a5\u8bbe\u7f6ebinlog_format=ROW\u683c\u5f0f\u4e86\uff0c\u81f3\u4e8ebinlog_row_image\u8bbe\u7f6e\u4e3aFULL\u8fd8\u662fminimal\uff0c\u5404\u4f4d\u5c31\u81ea\u884c\u8003\u8651\u4e86\u3002<\/p>

?<\/p>

\u73af\u5883\u7248\u672c\u5982\u4e0b\uff1a<\/p>

?<\/p>

> SELECT @@version
+-------------+
| @@version? |
|-------------|
| 5.6.34-log? |
+-------------+
?
> SELECT @@binlog_format;
+-------------------+
| @@binlog_format? |
|-------------------|
| ROW? ? ? ? ? ? ? |
+-------------------+<\/p>

?<\/p>

\u5047\u8bbe\u6211\u4eec\u7684\u64cd\u4f5c\u90fd\u662f\u5728\u4e00\u4e2a\u5e93\u91cc\u9762\u6267\u884c\u7684\uff0cMySQL\u670d\u52a1\u5668\u4e0a\u53ea\u8dd1\u4e86\u8fd9\u4e00\u4e2ahellodb\u4e1a\u52a1\u7684\u6570\u636e\u5e93\u3002<\/p>

?<\/p>

\u5982\u679c\u6570\u636e\u5e93\u591a\u7684\u8bdd\uff0c\u8fd8\u4f1a\u589e\u5927\u6062\u590d\u7684\u96be\u5ea6\uff0c\u5982\u4e0b\u4e8b\u4f8b(\u4e0b\u9762\u7684grant\u64cd\u4f5c\u5b9e\u4f8b\u4e0d\u591f\u660e\u663e\uff0c\u4f46\u662f\u5dee\u4e0d\u591a\u5c31\u662f\u90a3\u4e2a\u64cd\u4f5c\u6b65\u9aa4)\uff1a<\/p>

?<\/p>

step1? \u51c6\u5907\u4e00\u4e2a\u5168\u91cf\u5907\u4efd\uff1a<\/strong><\/p>

?<\/p>

mysqldump --flush-logs -A > \/root\/all.sql<\/p>

?<\/p>

step2? \u624b\u5de5\u8bef\u64cd\u4f5c\u5220\u9664\u90e8\u5206\u6570\u636e<\/strong><\/p>

?<\/p>

> use hellodb;
> delete from scores where `StuID`=8 AND `ID`=14;
# \u6a21\u62df\u8bef\u64cd\u4f5c\u5220\u4e861\u6761\u7528\u6237\u6570\u636e,\u7136\u540e\u4e0a\u62a5\u9700\u8981\u56de\u6eda\u64cd\u4f5c\u3002
?
\u6b64\u65f6\u8fd8\u6709\u4e2a\u7ebf\u7a0b\uff0c\u6267\u884c\u4e86 grant all on *.* to 'abc'@'%'; \u5047\u8bbe\u8fd9\u4e2agrant\u64cd\u4f5c\u7684\u662f\u7ba1\u7406\u5458\u6b63\u5e38\u7684\u64cd\u4f5c\u3002
?
> delete from scores where `StuID`=5 AND `ID`=10;
# \u6a21\u62df\u518d\u6b21\u8bef\u64cd\u4f5c\u5220\u4e861\u6761\u7528\u6237\u6570\u636e,\u7136\u540e\u4e0a\u62a5\u9700\u8981\u56de\u6eda\u64cd\u4f5c\u3002
?
........
........
\u5728\u6211\u4eec\u53d1\u73b0\u64cd\u4f5c\u9519\u4e86\uff0c\u5230\u6c47\u62a5\u8fd9\u671f\u95f4\uff0c\u8fd8\u8981\u5f88\u591a\u7528\u6237\u7684\u6b63\u5e38\u64cd\u4f5c\uff0c\u4e5f\u9020\u6210\u4e86\u6570\u636e\u5e93\u7684\u4e00\u4e9b\u66f4\u65b0\u3002\u4f8b\u5982\u4e0b\u9762\u8fd9\u6761\u63d2\u5165\u7684\u8bb0\u5f55\u3002
........
INSERT INTO students VALUES(100,'www',100,'F',3,5);
........
........<\/p>

?<\/p>

step3? mysql\u505c\u673a<\/strong><\/p>

?<\/p>

\/etc\/init.d\/mysql stop<\/p>

?<\/p>

step4 \u5bfc\u51fa\u76f8\u5173\u7684binlog<\/strong><\/p>

?<\/p>

cd \/data\/mysql<\/p>

?<\/p>

\u770b\u4e0b\u6700\u8fd1\u7684binlog\u6587\u4ef6\uff0c\u5047\u5982\u6211\u8fd9\u91cc\u770b\u5230\u7684\u662f mysql.0000010 \u8fd9\u4e2a\u6587\u4ef6\u3002<\/p>

?<\/p>

# \u5148\u5bfc\u51fa\u4e00\u4efdbinlog\u6587\u4ef6\uff0c
mysqlbinlog --base64-output=decode-rows -vv mysql.000010 > \/root\/1.sql<\/p>

?<\/p>

vi \/root\/1.sql \u627e\u5230\u521a\u624d\u6211\u4eec\u8bef\u64cd\u4f5c\u7684\u90e8\u5206\uff0c\u7c7b\u4f3c\u5982\u4e0b(\u4e0b\u9762\u88ab\u6211\u6dfb\u52a0\u4e86\u90e8\u5206\u6ce8\u91ca)\uff1a<\/p>

?<\/p>

BEGIN? ---> \u8fd9\u4e2aBEGIN-COMMIT\u8981\u5220\u9664
\/*!*\/;
# at 662771? --->? \u6ce8\u610f\u8fd9\u4e2aPostion,\u56de\u6eda\u8981\u7528\u5230
#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? ---> \u8fd9\u4e2aBEGIN COMMIT\u8981\u4fdd\u7559,\u8fd9\u4e2a\u662f\u7528\u6237\u7684\u6b63\u5e38\u64cd\u4f5c\u7684sql
#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? ---> \u8fd9\u4e2aBEGIN-COMMIT\u8981\u5220\u9664
\/*!*\/;
# at 663102? --->? \u6ce8\u610f\u8fd9\u4e2aPostion,\u56de\u6eda\u8981\u7528\u5230
#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 ---> \u8fd9\u4e2aBEGIN-COMMIT\u8981\u4fdd\u7559,\u8fd9\u4e2a\u662f\u7528\u6237\u7684\u6b63\u5e38\u64cd\u4f5c\u7684sql
\/*!*\/;
# at 663310? --->? \u6ce8\u610f\u8fd9\u4e2aPostion,\u56de\u6eda\u8981\u7528\u5230
#170116 15:22:59 server id 106? end","orderid":"0","title":"ROW \u683c\u5f0fbinlog \u5728MySQL5.6\u4e0a\u7684\u6570\u636e\u6062\u590d\u5b9e\u9a8c(\u4e00)","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"487","pages":"2","comments":"0","posttime":"2017-02-21 08:15:58","list":"1487636158","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"https:\/\/www.cppentry.com\/upload_files\/","ispic":"0","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"ROW<\/A> \u683c\u5f0f<\/A> binlog<\/A> MySQL5.6<\/A> \u6570\u636e\u6062\u590d<\/A> \u5b9e\u9a8c<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"14.17.22.31","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"ROW \u683c\u5f0fbinlog \u5728MySQL5.6\u4e0a\u7684\u6570\u636e\u6062\u590d\u5b9e\u9a8c","lastview":"1713611425","digg_num":"8525","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}