{"id":1306,"date":"2020-05-21T11:23:07","date_gmt":"2020-05-21T03:23:07","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1306"},"modified":"2020-05-21T11:23:07","modified_gmt":"2020-05-21T03:23:07","slug":"mysql-binlog%e6%97%a5%e5%bf%97%e6%9f%a5%e7%9c%8b","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1306","title":{"rendered":"MySQL binlog\u65e5\u5fd7\u67e5\u770b"},"content":{"rendered":"
\n

\u4e00\u3001\u4ec0\u4e48\u662fbinlog<\/strong><\/h2>\n

binlog\u00a0\u662f\u4e00\u4e2a\u4e8c\u8fdb\u5236\u683c\u5f0f\u7684\u6587\u4ef6\uff0c\u7528\u4e8e\u8bb0\u5f55\u7528\u6237\u5bf9\u6570\u636e\u5e93\u00a0\u66f4\u65b0\u7684SQL\u8bed\u53e5\u00a0\u4fe1\u606f\uff0c\u4f8b\u5982\u66f4\u6539\u6570\u636e\u5e93\u8868\u548c\u66f4\u6539\u5185\u5bb9\u7684SQL\u8bed\u53e5\u90fd\u4f1a\u8bb0\u5f55\u5230binlog\u91cc\uff0c\u4f46\u662f\u5bf9\u5e93\u8868\u7b49\u5185\u5bb9\u7684\u67e5\u8be2\u4e0d\u4f1a\u8bb0\u5f55\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0cbinlog\u00a0\u65e5\u5fd7\u662f\u4e8c\u8fdb\u5236\u683c\u5f0f\u7684\uff0c\u4e0d\u80fd\u4f7f\u7528\u67e5\u770b\u6587\u672c\u5de5\u5177\u7684\u547d\u4ee4\uff08\u6bd4\u5982\uff0ccat\uff0cvi\u7b49\uff09\u67e5\u770b\uff0c\u800c\u4f7f\u7528mysqlbinlog\u00a0\u89e3\u6790\u67e5\u770b\u3002<\/p>\n

\u4e8c\u3001binlog \u7684\u4f5c\u7528<\/strong><\/h2>\n

binlog\u4e3b\u8981\u7528\u4e8e\u590d\u5236\uff08Master-Slave \u4e3b\u4ece\u540c\u6b65\uff09\u3001\u6062\u590d\u548c\u5ba1\u8ba1\u3002<\/p>\n

\u4e09\u3001binlog \u4e09\u79cd\u6a21\u5f0f<\/strong><\/h2>\n<\/div>\n
\n

3.1 Row Level \u6a21\u5f0f\uff08RBR\uff09<\/h3>\n
\u4e0d\u8bb0\u5f55\u6bcf\u6761sql\u8bed\u53e5\u7684\u4e0a\u4e0b\u6587\u4fe1\u606f\uff0c\u53ea\u9700\u8bb0\u5f55\u54ea\u6761\u6570\u636e\u88ab\u4fee\u6539\u4e86\uff0c\u4fee\u6539\u6210\u4ec0\u4e48\u6837\u4e86\u3002\n<\/pre>\n
\u4f18\u70b9\uff1a<\/p>\n
* \u51c6\u786e\u6027\u5f3a\uff0c\u80fd\u51c6\u786e\u590d\u5236\u6570\u636e\u7684\u53d8\u66f4\n* \u51cf\u5c11\u6570\u636e\u5e93\u9501\u7684\u4f7f\u7528\n<\/pre>\n
\u7f3a\u70b9\uff1a<\/p>\n
* \u4f1a\u4ea7\u751f\u5927\u91cf\u7684\u65e5\u5fd7,\u5bfc\u81f4\u8f83\u5927\u7684\u7f51\u7edcIO\u548c\u78c1\u76d8IO\n* \u8981\u6c42\u4e3b\u4ece\u6570\u636e\u5e93\u8868\u7ed3\u6784\u5b8c\u5168\u4e00\u81f4\n* \u65e0\u6cd5\u5728\u4ece\u6570\u636e\u5e93\u4e0a\u5355\u72ec\u6267\u884c\u89e6\u53d1\u5668\n<\/pre>\n
3.2 Statement Level \u6a21\u5f0f\uff08SBR\uff09<\/h3>\n
\u6bcf\u4e00\u6761\u4fee\u6539\u6570\u636e\u7684 sql \u90fd\u4f1a\u8bb0\u5f55\u5230 master \u7684 bin_log \u4e2d\uff0cslave \u5728\u590d\u5236\u7684\u65f6\u5019 sql \u8fdb\u7a0b\u4f1a\u89e3\u6790\u6210 master \u7aef\u6267\u884c\u8fc7\u7684\u76f8\u540c\u7684 sql \u5728 slave \u5e93\u4e0a\u518d\u6b21\u6267\u884c\u3002\n<\/pre>\n
\u4f18\u70b9\uff1a<\/p>\n
* \u4e0d\u9700\u8981\u8bb0\u5f55\u6bcf\u4e00\u6761sql\u8bed\u53e5\u548c\u6bcf\u4e00\u884c\u7684\u6570\u636e\u53d8\u5316\uff0c\u51cf\u5c11\u4e86binlog\u65e5\u5fd7\u91cf\uff0c\u8282\u7ea6IO\uff0c\u63d0\u9ad8\u6027\u80fd\n* \u5e76\u4e0d\u5f3a\u5236\u8981\u6c42\u4e3b\u4ece\u6570\u636e\u5e93\u7684\u8868\u7ed3\u6784\u5b9a\u4e49\u5b8c\u5168\u76f8\u540c\n<\/pre>\n
\u7f3a\u70b9\uff1a<\/p>\n
* \u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\u4f1a\u5bfc\u81f4 master-slave \u4e2d\u7684\u6570\u636e\u4e0d\u4e00\u81f4(\u5982sleep()\u51fd\u6570\uff0c last_insert_id()\uff0c\u4ee5\u53causer-defined functions(udf)\u7b49\u4f1a\u51fa\u73b0\u95ee\u9898)\n* \u76f8\u6bd4\u4e8e RBR \u6a21\u5f0f\uff0c\u5728\u6267\u884c\u590d\u5236\u65f6\u9700\u8981\u66f4\u591a\u7684\u884c\u9501\n<\/pre>\n
3.3 MIXED\u6a21\u5f0f\uff08MBR\uff09<\/h3>\n
\u4ee5\u4e0a\u4e24\u79cd\u6a21\u5f0f\u7684\u6df7\u5408\u4f7f\u7528\uff0c\u4e00\u822c\u7684\u590d\u5236\u4f7f\u7528 STATEMENT \u6a21\u5f0f\u4fdd\u5b58 binlog\uff0c\u5bf9\u4e8e STATEMENT \u6a21\u5f0f\u65e0\u6cd5\u590d\u5236\u7684\u64cd\u4f5c\u4f7f\u7528 ROW \u6a21\u5f0f\u4fdd\u5b58 binlog\uff0cMySQL\u4f1a\u6839\u636e\u6267\u884c\u7684 SQL \u8bed\u53e5\u9009\u62e9\u65e5\u5fd7\u4fdd\u5b58\u65b9\u5f0f\u3002\n<\/pre>\n
\u4f18\u70b9\uff1a<\/p>\n
\u51c6\u786e\u6027\u5f3a\uff0c\u6587\u4ef6\u5927\u5c0f\u9002\u4e2d\n<\/pre>\n
\u7f3a\u70b9\uff1a<\/p>\n
\u6709\u53ef\u80fd\u53d1\u751f\u4e3b\u4ece\u4e0d\u4e00\u81f4\u95ee\u9898\n<\/pre>\n
3.4 binlog\u6a21\u5f0f\u7684\u9009\u62e9<\/h3>\n
1. \u4e0d\u7528\u5b58\u50a8\u8fc7\u7a0b\u3001\u89e6\u53d1\u5668\u3001\u51fd\u6570\uff0c\u9009\u62e9\u9ed8\u8ba4\u7684 Statement level\n2. \u7528\u5230MySQL\u7684\u7279\u6b8a\u529f\u80fd\uff08\u5b58\u50a8\u8fc7\u7a0b\u3001\u89e6\u53d1\u5668\u3001\u51fd\u6570\uff09\u9009\u62e9Mixed\u6a21\u5f0f\n3. \u7528\u5230MySQL\u7684\u7279\u6b8a\u529f\u80fd\uff08\u5b58\u50a8\u8fc7\u7a0b\u3001\u89e6\u53d1\u5668\u3001\u51fd\u6570\uff09\uff0c\u53c8\u5e0c\u671b\u6570\u636e\u6700\u5927\u5316\u4e00\u76f4\u5219\u9009\u62e9Row\u6a21\u5f0f\n<\/pre>\n
<\/h2>\n
\u56db\u3001binlog \u5e38\u7528\u53c2\u6570<\/strong><\/h2>\n
\n
\u7ba1\u7406Binlog\u76f8\u5173\u7684SQL\u8bed\u53e5<\/strong><\/h5>\n\n\n\n\n\n\n\n\n
SQL\u8bed\u53e5<\/th>\n\u8bed\u53e5\u542b\u4e49<\/th>\n<\/tr>\n<\/thead>\n
show master logs;<\/td>\n\u67e5\u770b\u6240\u6709Binlog\u7684\u65e5\u5fd7\u5217\u8868<\/td>\n<\/tr>\n
show master status;<\/td>\n\u67e5\u770b\u6700\u540e\u4e00\u4e2aBinlog\u65e5\u5fd7\u7684\u7f16\u53f7\u540d\u79f0\uff0c\u53ca\u6700\u540e\u4e00\u4e2a\u4e8b\u4ef6\u7ed3\u675f\u7684\u4f4d\u7f6e\uff08pos\uff09<\/td>\n<\/tr>\n
flush logs;<\/td>\n\u5237\u65b0Binlog\uff0c\u6b64\u523b\u5f00\u59cb\u4ea7\u751f\u4e00\u4e2a\u65b0\u7f16\u53f7\u7684Binlog\u65e5\u5fd7\u6587\u4ef6<\/td>\n<\/tr>\n
freset master;<\/td>\n\u5237\u6e05\u7a7a\u6240\u6709\u7684Binlog\u65e5\u5fd7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u67e5\u770bBinlog\u76f8\u5173\u7684SQL\u8bed\u53e5<\/strong><\/h5>\n
\uff08show binlog events [IN \u2018log_name\u2019] [FROM pos] [LIMIT [offset,] row_cout] \uff09<\/p>\n\n\n\n\n\n\n\n\n\n
SQL\u8bed\u53e5<\/th>\n\u8bed\u53e5\u542b\u4e49<\/th>\n<\/tr>\n<\/thead>\n
show binlog events;<\/td>\n\u67e5\u770b\u7b2c\u4e00\u4e2aBinlog\u65e5\u5fd7<\/td>\n<\/tr>\n
show binlog events in \u2018binlog.000030\u2019;<\/td>\n\u67e5\u770b\u6307\u5b9a\u7684Binlog\u65e5\u5fd7<\/td>\n<\/tr>\n
show binlog events in \u2018binlog.000030\u2019 from 931;<\/td>\n\u4ece\u6307\u5b9a\u7684\u4f4d\u7f6e\u5f00\u59cb\uff0c\u67e5\u770b\u6307\u5b9a\u7684Binlog\u65e5\u5fd7<\/td>\n<\/tr>\n
show binlog events in \u2018binlog.000030\u2019 from 931 limit 2;<\/td>\n\u4ece\u6307\u5b9a\u7684\u4f4d\u7f6e\u5f00\u59cb\uff0c\u67e5\u770b\u6307\u5b9a\u7684Binlog\u65e5\u5fd7\uff0c\u9650\u5236\u67e5\u8be2\u7684\u6761\u6570<\/td>\n<\/tr>\n
show binlog events in \u2018binlog.000030\u2019 from 931 limit 1,2;<\/td>\n\u4ece\u6307\u5b9a\u7684\u4f4d\u7f6e\u5f00\u59cb\uff0c\u5e26\u6709\u504f\u79fb\uff0c\u67e5\u770b\u6307\u5b9a\u7684Binlog\u65e5\u5fd7\uff0c\u9650\u5236\u67e5\u8be2\u7684\u6761\u6570<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Binlog \u4e2d\u7684Event_type<\/strong><\/h5>\n
\u5b98\u65b9\u63d0\u4f9b\u7684\u670936\u4e2devent_type\uff0c\u4e00\u822c\u4e0d\u4f1a\u7528\u5230\u8fd9\u4e48\u591a\u79cd\uff0c\u4e0b\u9762\u5199\u51fa\u7684\u662f\u51e0\u79cd\u5e38\u7528\u7684\u3002<\/p>\n