{"id":590,"date":"2025-12-10T10:33:04","date_gmt":"2025-12-10T02:33:04","guid":{"rendered":"https:\/\/blog.zhouhonghe.com\/?p=590"},"modified":"2025-12-10T18:54:45","modified_gmt":"2025-12-10T10:54:45","slug":"mysql%e4%b8%80%e5%8f%b0replicate%e4%bb%8e%e6%9c%ba%e5%a4%87%e4%bb%bd%e5%a4%9a%e5%8f%b0master%e4%b8%bb%e6%9c%ba","status":"publish","type":"post","link":"https:\/\/blog.zhouhonghe.com\/?p=590","title":{"rendered":"MySQL\u4e00\u53f0Replicate\u4ece\u673a\u540c\u6b65\u5907\u4efd\u591a\u53f0Master\u4e3b\u673a"},"content":{"rendered":"

\u4e3b\u673aA\u4e0a\u7684\u6570\u636e\u5e93DBA1\u3001DBA2\uff0c\u4e3b\u673aB\u4e0a\u7684\u6570\u636e\u5e93DBB1\uff0c\u5b9e\u65f6\u540c\u6b65\u5230\u4ece\u673aC\u4e0a\u3002<\/p>\n

1. A\u670d\u52a1\u5668\u914d\u7f6e<\/strong><\/p>\n

# \/etc\/my.cnf
\nserver-id=1<\/strong><\/em>
\nlog-bin=binlog-a
\nbinlog-format=ROW
\ngtid-mode=ON
\nenforce-gtid-consistency=ON
\nbinlog-do-db=DBA1<\/strong><\/em>
\nbinlog-do-db=DBA2<\/strong><\/em><\/code><\/p>\n

# \u521b\u5efa\u7528\u6237
\nCREATE USER 'replicate<\/strong><\/em>'@'%' IDENTIFIED BY 'password<\/strong><\/em>';
\nGRANT REPLICATION SLAVE ON *.* TO 'replicate<\/strong><\/em>'@'%';
\nFLUSH PRIVILEGES;<\/code><\/p>\n

2. B\u670d\u52a1\u5668\u914d\u7f6e<\/strong><\/p>\n

# \/etc\/my.cnf
\nserver-id=2<\/strong><\/em>
\nlog-bin=binlog-b
\nbinlog-format=ROW
\ngtid-mode=ON
\nenforce-gtid-consistency=ON
\nbinlog-do-db=DBB1<\/strong><\/em><\/code><\/p>\n

# \u521b\u5efa\u7528\u6237
\nCREATE USER 'replicate<\/strong><\/em>'@'%' IDENTIFIED BY 'password<\/strong><\/em>';
\nGRANT REPLICATION SLAVE ON *.* TO 'replicate<\/strong><\/em>'@'%';
\nFLUSH PRIVILEGES;<\/code><\/p>\n

3. C\u670d\u52a1\u5668\u914d\u7f6e<\/strong><\/p>\n

# \/etc\/my.cnf
\nserver-id=3<\/strong><\/em>
\nlog-bin=binlog-c
\nbinlog-format=ROW
\ngtid-mode=ON
\nenforce-gtid-consistency=ON
\nrelay-log=relaylog-c
\nread-only=1<\/p>\n

# \u53ea\u590d\u5236\u9700\u8981\u7684\u6570\u636e\u5e93
\nreplicate-do-db=DBA1<\/strong><\/em>
\nreplicate-do-db=DBA2<\/strong><\/em>
\nreplicate-do-db=DBB1<\/strong><\/em>
\n<\/code><\/p>\n

\u914d\u7f6e\u591a\u6e90\u590d\u5236
\n-- \u6e05\u9664\u65e7\u8bbe\u7f6e\uff08\u53ef\u9009\uff09
\nSTOP SLAVE;
\nRESET SLAVE ALL;
\nRESET MASTER;<\/code><\/p>\n

-- \u914d\u7f6eA\u670d\u52a1\u5668\u590d\u5236\u901a\u9053
\nCHANGE MASTER TO
\nMASTER_HOST='IP-A<\/strong><\/em>',
\nMASTER_PORT='3306<\/strong><\/em>',
\nMASTER_USER='USER<\/strong><\/em>',
\nMASTER_PASSWORD='PASSWORD<\/strong><\/em>',
\nMASTER_AUTO_POSITION=1
\nFOR CHANNEL 'channel_A<\/strong><\/em>';<\/code><\/p>\n

-- \u914d\u7f6eB\u670d\u52a1\u5668\u590d\u5236\u901a\u9053
\nCHANGE MASTER TO
\nMASTER_HOST='IP-B<\/strong><\/em>',
\nMASTER_PORT='3306<\/strong><\/em>',
\nMASTER_USER='USER<\/strong><\/em>',
\nMASTER_PASSWORD='PASSWORD<\/strong><\/em>',
\nMASTER_AUTO_POSITION=1
\nFOR CHANNEL 'channel_B<\/strong><\/em>';<\/code><\/p>\n

-- \u542f\u52a8\u4e24\u4e2a\u590d\u5236\u901a\u9053
\nSTART SLAVE FOR CHANNEL 'channel_A<\/strong><\/em>';
\nSTART SLAVE FOR CHANNEL 'channel_B<\/strong><\/em>';
\n<\/code><\/p>\n

\u68c0\u67e5\u590d\u5236\u72b6\u6001
\n-- \u67e5\u770b\u6240\u6709\u901a\u9053\u72b6\u6001
\nSHOW SLAVE STATUS\\G<\/p>\n

-- \u67e5\u770b\u6307\u5b9a\u901a\u9053\u72b6\u6001
\nSHOW SLAVE STATUS FOR CHANNEL 'channel_A<\/strong><\/em>'\\G
\nSHOW SLAVE STATUS FOR CHANNEL 'channel_B<\/strong><\/em>'\\G<\/code><\/p>\n

\u6570\u636e\u5bfc\u5165\u5bfc\u51fa<\/strong><\/p>\n

# \u5bfc\u51fa
\nmysqldump -uroot -p --single-transaction --source-data=2 --set-gtid-purged=ON --databases DBA1 DBA2 > db_dump.sql<\/code>
\n# \u5bfc\u5165
\nmysql -uroot -p < db_dump.sql<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

\u4e3b\u673aA\u4e0a\u7684\u6570\u636e\u5e93DBA1\u3001DBA2\uff0c\u4e3b\u673aB\u4e0a\u7684\u6570\u636e\u5e93DBB1\uff0c\u5b9e\u65f6\u540c\u6b65\u5230\u4ece\u673aC\u4e0a\u3002 1. A\u670d\u52a1\u5668\u914d\u7f6e # \/ […]<\/p>... ","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-590","post","type-post","status-publish","format-standard","hentry","category-others"],"_links":{"self":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts\/590","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=590"}],"version-history":[{"count":21,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts\/590\/revisions"}],"predecessor-version":[{"id":622,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts\/590\/revisions\/622"}],"wp:attachment":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}