{"id":1029,"date":"2022-03-30T10:58:20","date_gmt":"2022-03-30T02:58:20","guid":{"rendered":"https:\/\/www.buyao007.icu\/?p=1029"},"modified":"2022-03-30T11:26:13","modified_gmt":"2022-03-30T03:26:13","slug":"14-mysql%e4%b8%bb%e4%bb%8e%e5%a4%8d%e5%88%b6","status":"publish","type":"post","link":"https:\/\/www.buyao007.icu\/?p=1029","title":{"rendered":"14.MySQL\u4e3b\u4ece\u590d\u5236"},"content":{"rendered":"\n<p>\u5b59\u5bcc\u9633\uff0c \u6c5f\u6e56\u4eba\u79f0\u6ca1\u4eba\u79f0\u3002\u591a\u5e74\u4e92\u8054\u7f51\u8fd0\u7ef4\u5de5\u4f5c\u7ecf\u9a8c\uff0c\u66fe\u8d1f\u8d23\u8fc7\u5b59\u5e03\u65af\u5927\u89c4\u6a21\u96c6\u7fa4\u67b6\u6784\u81ea\u52a8\u5316\u8fd0\u7ef4\u7ba1\u7406\u5de5\u4f5c\u3002\u64c5\u957fWeb\u96c6\u7fa4\u67b6\u6784\u4e0e\u81ea\u52a8\u5316\u8fd0\u7ef4\uff0c\u66fe\u8d1f\u8d23\u56fd\u5185\u67d0\u5927\u578b\u535a\u5ba2\u7f51\u7ad9\u8fd0\u7ef4\u5de5\u4f5c\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">1.\u4e3b\u4ece\u540c\u6b65\u4ecb\u7ecd<\/h2>\n\n\n\n<p>\u4e24\u53f0\u6216\u8005\u4e24\u53f0\u4ee5\u4e0a\u5b9e\u4f8b\uff0c\u901a\u8fc7binlog\u5b9e\u73b0\u6570\u636e\u540c\u6b65<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">2.\u4e3b\u4ece\u540c\u6b65\u524d\u63d0\uff08\u642d\u5efa\u8fc7\u7a0b\uff09<\/h2>\n\n\n\n<p><strong><em>1.\u642d\u5efa\u8fc7\u7a0b<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>1.\u81f3\u5c11\u4e24\u53f0mysql\u5b9e\u4f8b\uff0c\u4e14server_id,server_uuid\u4e0d\u540c\n\u53ef\u4ee5\u5728\u914d\u7f6e\u6587\u4ef6\u4fee\u6539uuid\uff0c\u4e5f\u53ef\u4ee5\u5220\u9664\/data\/3316\/data\/auto.cnf\u91cd\u542f\u6570\u636e\u5e93<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"865\" height=\"299\" src=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-102.png\" alt=\"\" class=\"wp-image-1030\" srcset=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-102.png 865w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-102-300x104.png 300w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-102-768x265.png 768w\" sizes=\"(max-width: 865px) 100vw, 865px\" \/><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>2.\u4e3b\u5e93\u8981\u5f00binlog<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"316\" src=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-103.png\" alt=\"\" class=\"wp-image-1031\" srcset=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-103.png 700w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-103-300x135.png 300w\" sizes=\"(max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>3.\u4e13\u7528\u7684\u590d\u5236\u7528\u6237\u548c\u6743\u9650\nmysql&gt; create user repl@'10.0.0.%' identified with mysql_native_password by '123';\nmysql&gt; grant replication slave on *.* to repl@'10.0.0.%';\n4.\u9884\u540c\u6b65\u4e3b\u5e93\u6570\u636e\u5230\u4ece\u5e93\n5.\u544a\u8bc9\u4ece\u5e93\u8fde\u63a5\u7684\u8d77\u70b9\nCHANGE MASTER TO\n   MASTER_HOST='10.0.0.51',\n   MASTER_USER='repl',\n   MASTER_PASSWORD='123',\n   MASTER_PORT=3316,\n   MASTER_LOG_FILE='binlog.000021',\n   MASTER_LOG_POS=673,\n   MASTER_CONNECT_RETRY=10;\n6.\u542f\u52a8\u590d\u5236\u7ebf\u7a0b\nmysql&gt; start slave;\nps:\u67e5\u770b\u4e3b\u4ece\u590d\u5236\u72b6\u6001\nmysql&gt; show slave status\\G\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"865\" height=\"598\" src=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-104.png\" alt=\"\" class=\"wp-image-1032\" srcset=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-104.png 865w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-104-300x207.png 300w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-104-768x531.png 768w\" sizes=\"(max-width: 865px) 100vw, 865px\" \/><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>\u91cd\u7f6eslave\u4fe1\u606f\uff1areset slave all\uff1b<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u4f20\u7edf\u590d\u5236\u539f\u7406<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>1.\u6d89\u53ca\u5230\u7684\u7ebf\u7a0b<\/strong>\n\u4e3b\u5e93:\n\tdump thread:\u6295\u9012binlog\n\u4ece\u5e93:\n\tIO:\u8bf7\u6c42\u548c\u63a5\u6536\u65e5\u5fd7.\n\tSQL:\u56de\u653e\u65e5\u5fd7\n<strong>2\u6d89\u53ca\u5230\u7684\u6587\u4ef6<\/strong>\n\u4e3b\u5e93:\n\tbinlog :\u4e8c\u8fdb\u5236\u65e5\u5fd7.\n\u4ece\u5e93:\n\trelaylog:\u4e2d\u7ee7\u65e5\u5fd7\n\tmaster.info:\u4e3b\u5e93\u4fe1\u606f\u6587\u4ef6(IP,PORT,User ,Password,binlog\u76f8\u5173\u4fe1\u606f)\n\trelaylog.info: SQL\u56de\u653e\u5230\u7684\u4fe1\u606f.\n<strong>3\u4e3b\u4ece\u590d\u5236\u539f\u7406<\/strong>\na.\u4ece\u5e93\u6267\u884cchange master to \u547d\u4ee4\uff0c\u5c06\u4e3b\u5e93\u8fde\u63a5\u4fe1\u606f\u548cbinlog\u4f4d\u7f6e\u4fe1\u606f\u5199\u5165master.info\u6587\u4ef6\u6216\u8005slave_master_info\u8868\u4e2d\nb.\u4ece\u5e93\u6267\u884cstart slave\uff0c\u542f\u52a8\u4ece\u5e93\u7684I0,\u548cSQL\u7ebf\u7a0b\nc.IO\u7ebf\u7a0b\u8bfb\u53d6\u4e3b\u5e93\u94fe\u63a5\u4fe1\u606f\uff0c\u8fde\u63a5\u4e3b\u5e93\uff0c\u4e3b\u5e93\u6d3e\u751fdump\u7ebf\u7a0b(dump\u81ea\u52a8\u76d1\u63a7binlog\u53d8\u5316)\nd.IO\u7ebf\u7a0b\u6839\u636ebinlog\u4f4d\u7f6e\u70b9\u4fe1\u606f\uff0c\u83b7\u53d6\u6700\u65b0\u7684binlog\ne.dump\u622a\u53d6\u5e76\u6295\u9012binlog\u7ed9\u4ece\u5e93IO\u7ebf\u7a0b\uff0c\u4e3b\u5e93\u4e0d\u5173\u7cfb\u6295\u9012\u7684\u7ed3\u679c\u3002\nf.IO\u7ebf\u7a0b\u6536\u5230binlog(\u7f13\u5b58)\uff0c\u7acb\u5373\u66f4\u65b0master.info\u6216\u8005slave_master_info\u8868\ng.\u7f13\u5b58binlog\u6570\u636e\u5199\u5165relaylog\nh.SQL\u7ebf\u7a0b\u8bfb\u53d6relaylog. info\u6216\u8005slave_relay_log info\u8868\uff0c\u83b7\u53d6\u5230\u4e0a\u6b21\u56de\u653e\u5230\u7684\u4f4d\u7f6e\u70b9\uff0c\u7ee7\u7eed\u5f80\u540e\u56de\u653e\uff0c\u56de\u653e\u5b8c\u6210\u540e\uff0c\u518d\u6b21\u66f4\u65b0relaylog.info\u4fe1\u606f\ni.\u56de\u653e\u8fc7\u7684relaylog\uff0crelay_log_purge\u7ebf\u7a0b\u4f1a\u5b9a\u671f\u5220\u9664\u8fd9\u4e9b\u65e5\u5fd7\u3002\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">3.\u4e3b\u4ece\u590d\u5236\u76d1\u63a7<\/h2>\n\n\n\n<p><strong><em>1.\u76d1\u63a7\u65b9\u6cd5<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>a.\u81ea\u5e26\u547d\u4ee4show slave status \\G\nb.pt-table-checksum \/ pt-table-sync \/pt-heartbeat\nc.orch(orchestrator)\n<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u81ea\u5e26\u547d\u4ee4show slave status \\G<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>###\u4e3b\u5e93\u590d\u5236\u4fe1\u606f\nMaster_Host: 10.0.0.51\nMaster_User: repl\nMaster_Port: 3316\nConnect_Retry: 10\nMaster_Log_File: binlog.000021\nRead_Master_Log_Pos: 673\n###\u4ece\u5e93SQL\u7ebf\u7a0b\u5df2\u7ecf\u56de\u653e\u7684\u4e2d\u7ee7\u65e5\u5fd7\u4fe1\u606f\nRelay_Log_File: db02-relay-bin.000002\nRelay_Log_Pos: 321\nRelay_Master_Log_File: binlog.000021\nExec_Master_Log_Pos: 673\n\u8bf4\u660e\uff1a\u540e\u4e24\u6761\u53ef\u4ee5\u5e2e\u52a9\u6211\u4eec\u5224\u65ad\u4e3b\u4ece\u5ef6\u65f6\u7684\u65e5\u5fd7\u91cf\n###\u4e3b\u4ece\u590d\u5236\u7ebf\u7a0b\u6545\u969c\nSlave_IO_Running: Yes\nSlave_SQL_Running: Yes\nLast_IO_Errno: 0\nLast_IO_Error: \nLast_SQL_Errno: 0\nLast_SQL_Error:\n###\u8fc7\u6ee4\u590d\u5236\u76f8\u5173\u4fe1\u606f\t\t\t\nReplicate_Do_DB: \nReplicate_Ignore_DB: \nReplicate_Do_Table: \nReplicate_Ignore_Table: \nReplicate_Wild_Do_Table: \nReplicate_Wild_Ignore_Table: \n###\u4e3b\u4ece\u4e4b\u95f4\u5ef6\u65f6\u7684\u79d2\u6570\nSeconds_Behind_Master: 0\n###\u5ef6\u65f6\u4ece\u5e93\u72b6\u6001\nSQL_Delay: 0\nSQL_Remaining_Delay: NULL\n###GTID\u590d\u5236\nRetrieved_Gtid_Set: \nExecuted_Gtid_Set:\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">4.\u4e3b\u4ece\u590d\u5236\u6545\u969c\u5904\u7406<\/h2>\n\n\n\n<p><strong><em>1.IO\u6545\u969c<\/em><\/strong><\/p>\n\n\n\n<p>1.\u4ecb\u7ecd<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Slave_io_running  connecting\/no\/yes\nLast_IO_error \u91cd\u70b9\u5173\u6ce8\u62a5\u9519\u4fe1\u606f<\/code><\/pre>\n\n\n\n<p>2.\u5e38\u89c1\u62a5\u9519\u573a\u666f<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>1.\u901a\u4fe1\n\u5916\u90e8\u7f51\u7edc\u95ee\u9898\nIp\\port\\user\\passwd\n\u8fde\u63a5\u6570\u4e0a\u9650\n2.\u8bf7\u6c42\u65e5\u5fd7\n\u4e3b\u5e93\u65e5\u5fd7\u7f3a\u5931\n\u6587\u4ef6\u540d\\\u4f4d\u7f6e\u70b9<\/code><\/pre>\n\n\n\n<p>3.\u62a5\u9519\u89e3\u51b3<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>1.\u901a\u4fe1\u95ee\u9898\n\u4f7f\u7528\u590d\u5236\u7528\u6237\u6a21\u62df\u767b\u5f55\uff0c\u89e3\u51b3\u767b\u5f55\u8fc7\u7a0b\u7684\u62a5\u9519\n2.\u65e5\u5fd7\n\u6a21\u62df\uff1areset master\n\u5904\u7406\u601d\u8def\uff1astop slave\nreset slave all\nchange master to\uff1bstart slave<\/code><\/pre>\n\n\n\n<p><strong><em>2.SQL\u6545\u969c<\/em><\/strong><\/p>\n\n\n\n<p>1.\u4ecb\u7ecd<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Slave_SQL_Running:   YES\\No\r\nLast_SQL_Errno:\r\nLast_sQL_Error:  \u91cd\u70b9\u5173\u6ce8\u62a5\u9519\u4fe1\u606f\r<\/code><\/pre>\n\n\n\n<p>2.\u62a5\u9519\u5206\u6790<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u521b\u5efa\u7684\u5bf9\u8c61\u5df2\u7ecf\u5b58\u5728\r\n\u518c\u9664\u7684\u5bf9\u8c61\u548c\u4fee\u6539\u5bf9\u8c61\u4e0d\u5b58\u5728\r\n\u7ea6\u675f\u51b2\u7a81\r\n\u4ee5\u4e0a\u95ee\u9898\u539f\u56e0\r\n\u4ece\u5e93\u88ab\u8bef\u5199\u5165\u4e86\r\n\u53cc\u4e3b\r\n\u5f02\u6b65\u540c\u6b65\r<\/code><\/pre>\n\n\n\n<p>3.\u89e3\u51b3\u65b9\u6848<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4ee5\u4e3b\u5e93\u4e3a\u51c6,\u5c06\u4ece\u5e93\u6709\u51b2\u7a81\u7684\u6570\u636e\u5220\u6389.\r\n\u4ece\u5e93\u53ea\u8bfb\r\nset global super_read_only=1;\r\nmysql set global read only=1;\r\n\u4e2d\u95f4\u4ef6:\u8bfb\u5199\u5206\u79bb\r<\/code><\/pre>\n\n\n\n<p><strong><em>3.\u4e3b\u4ece\u5ef6\u65f6\u6545\u969c<\/em><\/strong><\/p>\n\n\n\n<p>1.\u4ec0\u4e48\u662f\u4e3b\u4ece\u5ef6\u65f6<\/p>\n\n\n\n<p>\u4e3b\u5e93\u505a\u7684\u4e8b,\u4ece\u5e93\u5f88\u4e45\u624d\u505a<\/p>\n\n\n\n<p>2.\u76d1\u63a7<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Seconds_Behind_Master: 0\r\nMaster_Log_File: binlog.000004\r\nRead_Master_Log_Pos:1154\r\nRelay_Master_Log_File: binlog.000004\r\nExec_Master_Log_Pos: 1154\r<\/code><\/pre>\n\n\n\n<p>3.\u539f\u56e0<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>\u5916\u90e8\u56e0\u7d20<\/strong>\r\n\u786c\u4ef6\u5dee\u5f02\r\n\u8d44\u6e90\u8017\u5c3d\r\n\u5168\u8868\u626b\u63cf\r\n\u5927\u4e8b\u52a1\r\n\u5b58\u50a8\u8fc7\u7a0b\r\n\u7f51\u7edc\u95ee\u9898\r\n<strong>\u4e3b\u5e93\u56e0\u7d20<\/strong>\r\nIO\u4e32\u884c\u5de5\u4f5c group commit\r\n<strong>\u4ece\u5e93\u56e0\u7d20<\/strong>\r\n\u5355\u4e2aSQ\u7ebf\u7a0b\r\n5.6+\u591aSQL\u7ebf\u7a0b \u53ea\u80fd\u6309\u7167\u5e93\u7ea7\u522b\u5e76\u53d1\r\n5.7+\u589e\u5f3a\u4e86\u591a\u7ebf\u7a0bSQL \u6309\u7167\u4e8b\u52a1\u7ea7\u522b\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">5.\u4e3b\u4ece\u590d\u5236\u8fc7\u6ee4\u590d\u5236<\/h2>\n\n\n\n<p><strong><em>1.\u4ec0\u4e48\u662f\u8fc7\u6ee4\u590d\u5236?<\/em><\/strong><\/p>\n\n\n\n<p>\u5c31\u662f\u9009\u62e9\u6027\u590d\u5236<\/p>\n\n\n\n<p><strong><em>2.\u5e94\u7528\u573a\u666f<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e1a\u52a1\u7684\u5206\u79bb.\r\u90e8\u5206\u6570\u636e\u540c\u6b65\r<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"865\" height=\"513\" src=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-105.png\" alt=\"\" class=\"wp-image-1042\" srcset=\"https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-105.png 865w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-105-300x178.png 300w, https:\/\/www.buyao007.icu\/wp-content\/uploads\/2022\/03\/image-105-768x455.png 768w\" sizes=\"(max-width: 865px) 100vw, 865px\" \/><\/figure>\n\n\n\n<p><strong><em>3.\u5982\u4f55\u5b9e\u73b0<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e3b\u5e93  : \u662f\u5426\u8bb0\u5f55binlog\u6765\u63a7\u5236\u4e0d\u5e38\u7528\r\nbinlog_do_db     :  \u767d\u540d\u5355\r\nbinlog_ignore_db :  \u9ed1\u540d\u5355\r\n\u4ece\u5e93  : SQL\u7ebf\u7a0b\u662f\u5426\u56de\u653e\u6765\u63a7\u5236\r\nreplicate_do_db=world\r\nreplicate_ignore_db=test\r\n\r\nreplicate_do_table=world.city\r\nreplicate_ignore_table:test.t100w\r\n\r\nreplicate_wild_do_table=oldboy.t*\r\nreplicate_wild_ignore_table=oldguo.t*\r<\/code><\/pre>\n\n\n\n<p><strong><em>4.\u914d\u7f6e\u6f14\u7ec3<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>\u5728\u7ebf\u751f\u6548: <\/strong>\r\nmysql> stop slave sql_thread;\r\nmysql> change replication filter replicate_do_db = (oldguo, test);\r\nmysql> start slave sql_thread;\r\n<strong>\u6c38\u4e45\u751f\u6548<\/strong>\r\nmy.cnf\r\nreplicate_do_db = oldguo\r\nreplicate_do_db = test\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">6.\u5ef6\u65f6\u4ece\u5e93<\/h2>\n\n\n\n<p><strong><em>1.\u4ecb\u7ecd<\/em><\/strong><\/p>\n\n\n\n<p>\u662f\u6211\u4eec\u4eba\u4e3a\u914d\u7f6e\u7684\u4e00\u79cd\u7279\u6b8a\u4ece\u5e93.\u4eba\u4e3a\u914d\u7f6e\u4ece\u5e93\u548c\u4e3b\u5e93\u5ef6\u65f6N\u5c0f\u65f6.<\/p>\n\n\n\n<p><strong><em>2.\u4e3a\u4ec0\u4e48\u8981\u6709\u5ef6\u65f6\u4ece<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u7269\u7406\u635f\u574f\r\n\u4e3b\u4ece\u590d\u5236\u975e\u5e38\u64c5\u957f\u89e3\u51b3\u7269\u7406\u635f\u574f.\r\n\u903b\u8f91\u635f\u574f\r\n\u666e\u901a\u4e3b\u4ece\u590d\u5236\u6ca1\u529e\u6cd5\u89e3\u51b3\u903b\u8f91\u635f\u574f\r<\/code><\/pre>\n\n\n\n<p><strong><em>3.\u914d\u7f6e\u5ef6\u65f6\u4ece\u5e93<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL\u7ebf\u7a0b\u5ef6\u65f6:\u6570\u636e\u5df2\u7ecf\u5199\u5165relaylog\u4e2d\u4e86,SQL\u7ebf\u7a0b\"\u6162\u70b9\"\u8fd0\u884c\r\n\u4e00\u822c\u4f01\u4e1a\u5efa\u8bae3-6\u5c0f\u65f6,\u5177\u4f53\u770b\u516c\u53f8\u8fd0\u7ef4\u4eba\u5458\u5bf9\u4e8e\u6545\u969c\u7684\u53cd\u5e94\u65f6\u95f4\u3002\r\nstop slave;\r\nCHANGE MASTER TO MASTER_DELAY = 300;\r\nstart slave;\r<\/code><\/pre>\n\n\n\n<p><strong><em>4.\u5ef6\u65f6\u4ece\u5e93\u5e94\u7528<\/em><\/strong><\/p>\n\n\n\n<p>1.\u6545\u969c\u6062\u590d\u601d\u8def<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>a. \u53d1\u73b0\u95ee\u9898 \r\nb. \u505c\u6389\u4ece\u5e93\u7ebf\u7a0b\r\nc. \u63a7\u5236SQL\u56de\u653e\u65e5\u5fd7\u7684\u622a\u6b62\u4f4d\u7f6e\u70b9.\r\n\u8d77\u70b9\uff1a\u505c\u6b62SQL\u7ebf\u7a0b\u65f6\uff0crelay\u6700\u540e\u5e94\u7528\u4f4d\u7f6e\r\nRelay_log_file: db01-relay-bin.000002\r\nRelay_log_pos: 320\r\n\u7ec8\u70b9\uff1a\u8bef\u5220\u9664\u4e4b\u524d\u7684position\uff08GTID\uff09\r\nd. \u627e\u56de\u6570\u636e,\u5feb\u901f\u6062\u590d\u4e1a\u52a1\r<\/code><\/pre>\n\n\n\n<p>2.\u6545\u969c\u6a21\u62df\u53ca\u6062\u590d\u6f14\u7ec3<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>\u914d\u7f6e\u4ece\u5e93\u5ef6\u8fdf\u65f6\u95f4<\/strong>\r\nstop slave;\r\nCHANGE MASTER TO MASTER_DELAY = 300;\r\nstart slave;\r\n<strong>\u4e3b\u5e93\u521b\u5efa\u6570\u636e\u5e93<\/strong>\r\ncreate database relaydb ;\r\nuse relaydb;\r\ncreate table t1(id int);\r\ninsert into t1 values(1),(2),(3);\r\ncommit;\r\ninsert into t1 values(11),(12),(13);\r\ncommit;\r\ninsert into t1 values(111),(112),(113);\r\ncommit;\r\ndrop database relaydb;\r\n<strong>\u505c\u6b62\u4ece\u5e93SQL\u7ebf\u7a0b<\/strong>\r\nstop slave sql_thread;\r\n<strong>\u65b9\u6cd5\u4e00\uff1a<\/strong>\r\n\u622a\u53d6\u65e5\u5fd7\u70b9\u4f4d\r\nmysql> show slave status\\G\u67e5\u770b\u8d77\u70b9\r\nmysql> show relaylog events in 'db02-relay-bin.000003';\u67e5\u770b\u7ec8\u70b9\r\nmysqlbinlog --start-position=233 --stop-position=1448 \/data\/3316\/data\/binlog.000011 > \/tmp\/bin.sql\r\n\u5bfc\u5165\u6570\u636e\r\nsource \/tmp\/bin.sql\r\n<strong>\u65b9\u6cd5\u4e8c\uff1a<\/strong>\r\nstop slave sql_thread;\r\nCHANGE MASTER TO MASTER_DELAY = 0;\r\nSTART SLAVE SQL_THREAD UNTIL  RELAY_LOG_FILE = 'db01-relay-bin.000002', RELAY_LOG_POS = 1576 ;\r\n\u5982\u679c\u5f00\u542fGTID\uff0c\u53ef\u4ee5\u6309\u7167GTID\u65b9\u5f0fUNTIL\r\nSTART SLAVE UNTIL SQL_BEFORE_GTIDS = \"188be1ed-c84c-11ea-98e7-000c29ea9d83:4\";\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">7.\u4e3b\u4ece\u6570\u636e\u6700\u7ec8\u4e00\u81f4\u6027\u4fdd\u8bc1---(\u589e\u5f3a)\u534a\u540c\u6b65\u590d\u5236<\/h2>\n\n\n\n<p><strong><em>1.\u534a\u540c\u6b65\u590d\u5236-after_commit \u548c after_sync<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>5.5\u5f00\u59cb\u652f\u6301\u534a\u540c\u6b65\u590d\u5236,\u4f46\u662f\u6ca1\u6709GC\u673a\u5236,\u6027\u80fd\u6781\u5dee,\u673a\u4f1a\u6ca1\u4eba\u7528\r\n5.6 \u7248\u672c\u65f6 ,\u52a0\u5165\u4e86GC\u673a\u5236,\u534a\u540c\u6b65\u590d\u5236\u5f00\u59cb\u88ab\u63a5\u6536.\u4f7f\u7528\u7684\u662fafter_commit\u673a\u5236,\u4f46\u662f\u662f\u5728redo commit\u4e4b\u540e\u8fdb\u884c\u7b49\u5f85ACK\u786e\u8ba4.\r\n\u8fd9\u91cc\u4f1a\u6709\u4e00\u4e2a\u75db\u70b9,\u5982\u679c\u4e3b\u5e93redo commit\u9636\u6bb5\u5b95\u673a\u5b95\u673a\u4e86,\u4ece\u5e93\u53c8\u83b7\u53d6\u5230\u4e86binlog,\u4f1a\u51fa\u73b0\u4ece\u5e93\u6bd4\u4e3b\u5e93\u6570\u636e\"\u591a\"\u7684\u95ee\u9898.\u5bfc\u81f4\u6570\u636e\u4e0d\u4e00\u81f4.\r\n5.7\u7248\u672c+\u4ee5\u540e,\u52a0\u5165\u4e86after_sync\u673a\u5236,\u5728binlog commit(binlog sync disk)\u9636\u6bb5,\u7b49\u5f85\u4ece\u5e93ACK,\u4e0d\u7ba1\u8c01\u5b95\u673a,\u90fd\u80fd\u4fdd\u8bc1\u6700\u7ec8\u4e00\u81f4\u6027.\r\n\r\n\u53e6\u5916: \r\n\u4e0d\u7ba1\u54ea\u79cd\u65b9\u5f0f,\u8fd8\u4f1a\u51fa\u73b0,\u5982\u679cACK\u8d85\u65f6,\u4f1a\u88ab\u5207\u6362\u4e3a\u5f02\u6b65\u590d\u5236\u7684\u6a21\u5f0f.\u8fd8\u662f\u6709\u6570\u636e\u4e0d\u4e00\u81f4\u7684\u98ce\u9669.\r\n\u5982\u679c\u516c\u53f8\u80fd\u5bb9\u5fcd,\u53ef\u4ee5\u4f7f\u7528\u8fd9\u79cd\u67b6\u6784,\u5efa\u8bae\u4f7f\u7528\u589e\u5f3a\u534a\u540c\u6b65+GTID\u6a21\u5f0f.\r\n\u5982\u679c\u4e0d\u80fd\u5bb9\u5fcd,\u53ef\u4ee5\u4f7f\u7528MGR  PXC .\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u589e\u5f3a\u534a\u540c\u6b65\u590d\u5236\u914d\u7f6e<\/em><\/strong><\/p>\n\n\n\n<p>1.\u52a0\u8f7d\u63d2\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';\r\nINSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';\r<\/code><\/pre>\n\n\n\n<p>2.\u67e5\u770b\u662f\u5426\u52a0\u8f7d\u6210\u529f<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>show plugins;\r\n\u542f\u52a8:\r\nSET GLOBAL rpl_semi_sync_master_enabled = 1;\r\nSET GLOBAL rpl_semi_sync_slave_enabled = 1;\r<\/code><\/pre>\n\n\n\n<p>3.\u91cd\u542f\u4ece\u5e93\u4e0a\u7684IO\u7ebf\u7a0b<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>STOP SLAVE IO_THREAD;\r\nSTART SLAVE IO_THREAD;\r<\/code><\/pre>\n\n\n\n<p>4.\u67e5\u770b\u662f\u5426\u5728\u8fd0\u884c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>show status like 'Rpl_semi_sync_master_status';\r\nshow status like 'Rpl_semi_sync_slave_status';\r<\/code><\/pre>\n\n\n\n<p>5.\u5176\u4ed6\u7684\u4f18\u5316\u53c2\u6570<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>show variables like '%semi%'; \r\nrpl_semi_sync_master_enabled                =ON\r\nrpl_semi_sync_master_timeout                =1000\r\nrpl_semi_sync_master_trace_level            =32\r\nrpl_semi_sync_master_wait_for_slave_count   =1\r\nrpl_semi_sync_master_wait_no_slave          =ON\r\nrpl_semi_sync_master_wait_point             =AFTER_SYNC\r\nrpl_semi_sync_slave_enabled                 =ON\r\nrpl_semi_sync_slave_trace_level             =32\r\n\r\nmysql> set global binlog_group_commit_sync_delay              =1;\r\nmysql> set global binlog_group_commit_sync_no_delay_count     =1000;\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">8.GTID\u590d\u5236<\/h2>\n\n\n\n<p><strong><em>1.GTID\u4ecb\u7ecd<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GTID(Global Transaction ID)\u662f\u5bf9\u4e8e\u4e00\u4e2a\u5df2\u63d0\u4ea4\u4e8b\u52a1\u7684\u552f\u4e00\u7f16\u53f7\uff0c\u5e76\u4e14\u662f\u4e00\u4e2a\u5168\u5c40(\u4e3b\u4ece\u590d\u5236)\u552f\u4e00\u7684\u7f16\u53f7\u3002\r\n\u5b83\u7684\u5b98\u65b9\u5b9a\u4e49\u5982\u4e0b\uff1a\r\nGTID =server_uuid : transaction_id\r\n7E11FA47-31CA-19E1-9E56-C43AA21293967:29\r\n\u4ec0\u4e48\u662fsever_uuid\uff0c\u548cServer-id \u533a\u522b\uff1f\r\n\u6838\u5fc3\u7279\u6027: \u5168\u5c40\u552f\u4e00,\u5177\u5907\u5e42\u7b49\u6027\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.GTID\u6838\u5fc3\u53c2\u6570<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u91cd\u8981\u53c2\u6570\uff1a\r\ngtid-mode=on\r\nenforce-gtid-consistency=true\r\nlog-slave-updates=1\r\ngtid-mode=on            --\u542f\u7528gtid\u7c7b\u578b\uff0c\u5426\u5219\u5c31\u662f\u666e\u901a\u7684\u590d\u5236\u67b6\u6784\r\nenforce-gtid-consistency=true       --\u5f3a\u5236GTID\u7684\u4e00\u81f4\u6027\r\nlog-slave-updates=1                 --slave\u66f4\u65b0\u662f\u5426\u8bb0\u5165\u65e5\u5fd7\r<\/code><\/pre>\n\n\n\n<p><strong><em>3.GTID\u590d\u5236\u914d\u7f6e\u8fc7\u7a0b<\/em><\/strong><\/p>\n\n\n\n<p>1.\u6e05\u7406\u73af\u5883<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pkill mysqld\r\n \\rm -rf \/data\/3306\/data\/*\r\n \\rm -rf \/data\/3306\/binlog\/*\r\n \\mv \/etc\/my.cnf \/tmp\r\n mkdir -p \/data\/3306\/data \/data\/3306\/binlog\/\r\n chown -R mysql.mysql \/data\/*\r<\/code><\/pre>\n\n\n\n<p>2.\u51c6\u5907\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e3b\u5e93db01\uff1a\r\ncat > \/etc\/my.cnf &lt;&lt;EOF\r\n&#91;mysqld]\r\nbasedir=\/usr\/local\/mysql\/\r\ndatadir=\/data\/3306\/data\r\nsocket=\/tmp\/mysql.sock\r\nserver_id=51\r\nport=3306\r\nsecure-file-priv=\/tmp\r\nlog_bin=\/data\/3306\/binlog\/mysql-bin\r\nbinlog_format=row\r\ngtid-mode=on\r\nenforce-gtid-consistency=true\r\nlog-slave-updates=1\r\n&#91;mysql]\r\nprompt=db01 &#91;\\\\d]>\r\nEOF\r\n\r\nslave1(db02)\uff1a\r\ncat > \/etc\/my.cnf &lt;&lt;EOF\r\n&#91;mysqld]\r\nbasedir=\/usr\/local\/mysql\r\ndatadir=\/data\/3306\/data\r\nsocket=\/tmp\/mysql.sock\r\nserver_id=52\r\nport=3306\r\nsecure-file-priv=\/tmp\r\nlog_bin=\/data\/3306\/binlog\/mysql-bin\r\nbinlog_format=row\r\ngtid-mode=on\r\nenforce-gtid-consistency=true\r\nlog-slave-updates=1\r\n&#91;mysql]\r\nprompt=db02 &#91;\\\\d]>\r\nEOF\r\n\r\nslave2(db03)\uff1a\r\ncat > \/etc\/my.cnf &lt;&lt;EOF\r\n&#91;mysqld]\r\nbasedir=\/usr\/local\/mysql\r\ndatadir=\/data\/3306\/data\r\nsocket=\/tmp\/mysql.sock\r\nserver_id=53\r\nport=3306\r\nsecure-file-priv=\/tmp\r\nlog_bin=\/data\/3306\/binlog\/mysql-bin\r\nbinlog_format=row\r\ngtid-mode=on\r\nenforce-gtid-consistency=true\r\nlog-slave-updates=1\r\n&#91;mysql]\r\nprompt=db03 &#91;\\\\d]>\r\nEOF\r<\/code><\/pre>\n\n\n\n<p>3.\u521d\u59cb\u5316\u6570\u636e\u5e76\u542f\u52a8\u6570\u636e\u5e93<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqld --initialize-insecure --user=mysql --basedir=\/usr\/local\/mysql  --datadir=\/data\/3306\/data \n\/etc\/init.d\/mysqld start<\/code><\/pre>\n\n\n\n<p>4.\u6784\u5efa\u4e3b\u4ece<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>master:51\r\nslave:52,53\r\n\r\n# 51:\r\ncreate user repl@'10.0.0.%' identified with mysql_native_password by '123';\r\ngrant replication slave  on *.* to repl@'10.0.0.%' ;\r\n\r\n# 52\\53:\r\n\r\nchange master to \r\nmaster_host='10.0.0.51',\r\nmaster_user='repl',\r\nmaster_password='123' ,\r\nMASTER_AUTO_POSITION=1;\r\nstart slave;\r\n\u6ce8\u610f\uff1a \r\n\u5982\u679c\u662f\u5df2\u7ecf\u8fd0\u884c\u5f88\u4e45\u7684\u6570\u636e\u5e93\uff0c\u9700\u8981\u6784\u5efa\u4ece\u5e93\uff0c\u90fd\u662f\u9700\u8981\u5907\u4efd\u6062\u590d\u4e3b\u5e93\u6570\u636e\u540e\u518d\u5f00\u542f\u4e3b\u4ece\u7684\u3002\r\nmysqldump\u6765\u8bb2\uff0c\u4e0d\u8981\u52a0--set-gtid-purged=OFF\u529f\u80fd\u3002\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">9.\u591a\u6e90\u590d\u5236(MSR Multi Source Replication): OLAP (\u5728\u7ebf\u5206\u6790\u5904\u7406)<\/h2>\n\n\n\n<p><strong><em>1.\u591a\u6e90\u590d\u5236\u4ecb\u7ecd<\/em><\/strong><\/p>\n\n\n\n<p>5.7\u4ee5\u4e0a\u7248\u672c\uff0c\u9700\u8981\u5f00gtid\u590d\u5236<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>| \u4e3b\u673a\u89d2\u8272 | \u5730\u5740      | \u7aef\u53e3 |\r\n| -------- | --------- | ---- |\r\n| Master1  | 10.0.0.51 | 3306 |\r\n| Master2  | 10.0.0.52 | 3306 |\r\n| Slave    | 10.0.0.53 | 3306 |\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u914d\u7f6e\u8fc7\u7a0b<\/em><\/strong><\/p>\n\n\n\n<p>1.GTID\u73af\u5883\u51c6\u5907:\u7565\uff0c\u53c2\u80038GTID\u590d\u5236<\/p>\n\n\n\n<p>2.\u914d\u7f6e\u591a\u6e90\u590d\u5236<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_1';                \r\n\r\nCHANGE MASTER TO MASTER_HOST='10.0.0.52',MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_2';           \r\n\r\nstart slave for CHANNEL  'Master_1';\r\nstart slave for CHANNEL  'Master_2';\r<\/code><\/pre>\n\n\n\n<p>3.\u591a\u6e90\u590d\u5236\u76d1\u63a7<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>db03 &#91;(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\\G\r\ndb03 &#91;(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\\G\r\n\r\nselect * from performance_schema.replication_connection_configuration\\G\r\nSELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME='master_1'\\G\r\nselect * from performance_schema.replication_applier_status_by_worker;\r<\/code><\/pre>\n\n\n\n<p>4.\u591a\u6e90\u590d\u5236\u914d\u7f6e\u8fc7\u6ee4<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL \"master_1\";\r\nmysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL \"master_2\";\r<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5b59\u5bcc\u9633\uff0c \u6c5f\u6e56\u4eba\u79f0\u6ca1\u4eba\u79f0\u3002\u591a\u5e74\u4e92\u8054\u7f51\u8fd0\u7ef4\u5de5\u4f5c\u7ecf\u9a8c\uff0c\u66fe\u8d1f\u8d23\u8fc7\u5b59\u5e03\u65af\u5927\u89c4\u6a21\u96c6\u7fa4\u67b6\u6784\u81ea\u52a8\u5316\u8fd0\u7ef4\u7ba1\u7406\u5de5\u4f5c\u3002\u64c5\u957fWeb\u96c6 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":246,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"_links":{"self":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/1029"}],"collection":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1029"}],"version-history":[{"count":10,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/1029\/revisions"}],"predecessor-version":[{"id":1051,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/1029\/revisions\/1051"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/media\/246"}],"wp:attachment":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}