{"id":239,"date":"2025-02-11T19:26:50","date_gmt":"2025-02-11T11:26:50","guid":{"rendered":"https:\/\/www.xxiaoteng.cn\/?p=239"},"modified":"2026-05-11T21:25:12","modified_gmt":"2026-05-11T13:25:12","slug":"gtid%e7%9a%84%e9%85%8d%e7%bd%ae%e4%b8%8e%e5%ae%9e%e7%8e%b0","status":"publish","type":"post","link":"https:\/\/www.xxiaoteng.cn\/?p=239","title":{"rendered":"GTID\u7684\u914d\u7f6e\u4e0e\u5b9e\u73b0"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">\u4f5c\u7528\uff1a\u57fa\u4e8eGTID\u5b9e\u73b0\u4e3b\u4ece\u590d\u5236\uff08\u91cd\u70b9\uff09<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u6587\u6863\uff1ahttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-gtids-howto.html<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u73af\u5883\u8bf4\u660e\uff1a<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u5173\u95ed\u9632\u706b\u5899+SELINUX\u3001\u914d\u7f6eIP\u4e0e\u4e3b\u673a\u6620\u5c04\u3001\u65f6\u95f4\u540c\u6b65\u3001\u5b89\u88c5\u4f9d\u8d56\u5305<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>IP<\/td><td>\u4e3b\u673a\u540d<\/td><td>\u89d2\u8272<\/td><\/tr><tr><td>192.168.88.101<\/td><td>node1.xiaoteng.cn<\/td><td>master(\u4e3b)<\/td><\/tr><tr><td>192.168.88.102<\/td><td>node2.xiaoteng.cn<\/td><td>slave(\u4ece)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\u5b89\u88c5\u524d\u51c6\u5907\uff1a\u2460 \u914d\u7f6eIP\u3001\u4e3b\u673a\u540d \u2461 \u914d\u7f6eIP\u4e0e\u4e3b\u673a\u6620\u5c04 =&gt; \/etc\/hosts \u2462 \u5173\u95ed\u9632\u706b\u5899\u4e0eSELinux \u2463 \u65f6\u95f4\u540c\u6b65 \u2464 \u5b89\u88c5\u5fc5\u5907\u8f6f\u4ef6\uff0c\u5982vim\u3001wget\u3001rsync<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u8bbe\u7f6e\u4e3b\u673a\u540d\u79f0<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >hostnamectl set-hostname node1.xiaoteng.cn\nhostnamectl set-hostname node2.xiaoteng.cn\n\nsu\u6216\u8005bash\u6307\u4ee4<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u914d\u7f6eIP\u4e0e\u4e3b\u673a\u6620\u5c04<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >vim \/etc\/hosts\n\u5c3e\u90e8\u8ffd\u52a0\u5982\u4e0b\u5185\u5bb9\n192.168.88.101 node1 node1.xiaoteng.cn\n192.168.88.102 node2 node2.xiaoteng.cn<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u5173\u95ed\u9632\u706b\u5899\u4e0eSELinux<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >systemctl stop firewalld\nsystemctl disable firewalld\n\nsetenforce 0\nvim \/etc\/selinux\/config<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u5b89\u88c5\u4e00\u4e9b\u4f9d\u8d56\u8f6f\u4ef6\uff08\u7cfb\u7edf\u5fc5\u5907\u8f6f\u4ef6\uff09<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >dnf install vim wget rsync -y<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u5230\u6b64\u73af\u5883\u51c6\u5907\u5b8c\u6bd5\u4e86\uff01<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">\u6570\u636e\u5e93\u73af\u5883\u51c6\u5907\uff1a\u8bf7\u57fa\u4e8emaster.sh\u4ee5\u53caslave.sh\u81ea\u884c\u5b89\u88c5mysql8\u8f6f\u4ef6\uff0c\u5e76\u8fdb\u884c\u6570\u636e\u540c\u6b65<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">master\uff1a\u5b89\u88c5mysql\u5e76\u8fdb\u884c\u521d\u59cb\u5316<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >source master.sh<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">slave\uff1a\u53ea\u5b89\u88c5mysql\u4e0d\u8fdb\u884c\u521d\u59cb\u5316<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >source slave.sh<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u505c\u6b62mysqld\uff0c\u7136\u540e\u540c\u6b65\u6570\u636e\u5230slave\u4e2d<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">master:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >systemctl stop mysqld\nrm -rf \/export\/server\/mysql\/data\/auto.cnf\nrsync -av \/export\/server\/mysql\/data node2:\/export\/server\/mysql\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u7b2c\u4e00\u6b65\uff1a\u4fee\u6539\u4e3b\u5e93\u914d\u7f6e<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4fee\u6539\u4e3b\u5e93\u7684\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >cat &gt; \/etc\/my.cnf &lt;&lt;EOF\n&#091;mysqld]\nbasedir=\/export\/server\/mysql\ndatadir=\/export\/server\/mysql\/data\nsocket=\/tmp\/mysql.sock\nport=3306\nlog-error=\/export\/server\/mysql\/master.err\nlog-bin=\/export\/server\/mysql\/data\/binlog\nserver-id=10\ncharacter_set_server=utf8mb4\ncollation-server=utf8mb4_unicode_ci\ngtid_mode=on\nenforce_gtid_consistency=on\nlog_slave_updates=1\nEOF<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">GTID\u914d\u7f6e\u8bf4\u660e:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u542f\u7528\u5168\u5c40\u4e8b\u52a1\u6807\u8bc6\u7b26\uff08GTID\uff09\u6a21\u5f0f<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">gtid_mode=on<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u5f3a\u5236GTID\u7684\u4e00\u81f4\u6027\u3002\u8fd9\u610f\u5473\u7740\u5728\u6267\u884c\u4e8b\u52a1\u65f6\uff0cMySQL\u5c06\u786e\u4fdd\u6240\u6709\u6d89\u53ca\u7684\u670d\u52a1\u5668\u90fd\u4f7f\u7528\u76f8\u540c\u7684GTID\u96c6\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">enforce_gtid_consistency=on<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">log_slave_updates=1<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><code class=\"prettyprint\" >log_slave_updates=1<\/code>\u662f MySQL \u4e3b\u4ece\u590d\u5236\u4e2d\u7684\u4e00\u4e2a\u5173\u952e\u53c2\u6570\uff0c\u5b83\u51b3\u5b9a\u4e86\u4ece\u5e93\uff08Slave\uff09\u662f\u5426\u5c06<strong>\u4ece\u4e3b\u5e93\uff08<\/strong>master<strong>\uff09\u540c\u6b65\u8fc7\u6765\u7684\u6570\u636e\u53d8\u66f4\u4e8b\u4ef6<\/strong>\u8bb0\u5f55\u5230\u81ea\u5df1\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff08binlog\uff09<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">\u7b2c\u4e8c\u6b65\uff1a\u4fee\u6539\u4ece\u5e93\u914d\u7f6e<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4fee\u6539\u4ece\u5e93\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >\ncat &gt; \/etc\/my.cnf &lt;&lt;EOF\n&#091;mysqld]\nbasedir=\/export\/server\/mysql\ndatadir=\/export\/server\/mysql\/data\nsocket=\/tmp\/mysql.sock\nport=3306\nlog-error=\/export\/server\/mysql\/slave.err\nlog-bin=\/export\/server\/mysql\/data\/binlog\nrelay-log=\/export\/server\/mysql\/data\/relaylog\nserver-id=20\ncharacter_set_server=utf8mb4\ncollation-server=utf8mb4_unicode_ci\ngtid_mode=on  \nenforce_gtid_consistency=on\nlog_slave_updates=1\nread-only=on\nEOF<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">master\u8282\u70b9\/slave\u8282\u70b9\uff0c\u91cd\u542fMySQL<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" ># \u4e3b\u670d\u52a1\u5668\ntouch \/export\/server\/mysql\/master.err\nchown -R mysql.mysql \/export\/server\/mysql\nsystemctl start mysqld\n\n# \u4ece\u670d\u52a1\u5668\ntouch \/export\/server\/mysql\/slave.err\nchown -R mysql.mysql \/export\/server\/mysql\nsystemctl start mysqld<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u4ece\u8282\u70b9\u8bbe\u7f6e\u4e3b\u5e93\u4fe1\u606f<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u4ee5\u4e0b\u662f\u5b98\u7f51\u63d0\u4f9b\u7684\u8bbe\u7f6e\u53c2\u8003\uff08\u4ec5\u53c2\u8003\uff0c\u9700\u8981\u8c03\u6574\u4e3a\u81ea\u5df1master\u670d\u52a1\u5668\u4fe1\u606f\uff09<\/p>\n\n\n\n<figure class=\"wp-block-embed\"><div class=\"wp-block-embed__wrapper\">\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-gtids-howto.html\n<\/div><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >mysql&gt; CHANGE REPLICATION SOURCE TO\n     &gt;     SOURCE_HOST = host,\n     &gt;     SOURCE_PORT = port,\n     &gt;     SOURCE_USER = user,\n     &gt;     SOURCE_PASSWORD = password,\n     &gt;     SOURCE_AUTO_POSITION = 1;<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">SOURCE_AUTO_POSITION = 1\uff1a \u8fd9\u544a\u8bc9\u4ece\u670d\u52a1\u5668\u4f7f\u7528\u81ea\u52a8\u4f4d\u7f6e\u8ddf\u8e2a\u529f\u80fd\uff0c\u4ee5\u4fbf\u5b83\u53ef\u4ee5\u81ea\u52a8\u4ece\u4e3b\u670d\u52a1\u5668\u83b7\u53d6\u6700\u65b0\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e8b\u4ef6\uff0c\u800c\u65e0\u9700\u624b\u52a8\u6307\u5b9a\u4f4d\u7f6e\u3002<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">\u5177\u4f53\u914d\u7f6e\u4ee3\u7801\uff1a<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">master\u4e3b\u670d\u52a1\u5668\u521b\u5efa\u540c\u6b65\u8d26\u53f7\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >mysql&gt; CREATE USER &#039;slave&#039;@&#039;%&#039; IDENTIFIED WITH mysql_native_password BY &#039;123&#039;;\nmysql&gt; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO &#039;slave&#039;@&#039;%&#039;;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">slave\u4ece\u670d\u52a1\u5668\u57fa\u4e8echange replication source\u8fdb\u884c\u540c\u6b65\u64cd\u4f5c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >mysql&gt; change replication source to\n  source_host=&#039;192.168.88.101&#039;,\n  source_port=3306,\n  source_user=&#039;slave&#039;,\n  source_password=&#039;123&#039;,\n  source_auto_position=1;<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">\u548cAB\u590d\u5236\u6700\u5927\u7684\u4e0d\u540c\u5c31\u662f\u4e0d\u9700\u8981\u5bfb\u627ebinlog\u4ee5\u53ca\u5bf9\u5e94\u7684pos\u70b9\u4f4d\uff0c\u53ea\u9700\u8981source_auto_position=1\u5c31\u53ef\u4ee5\u81ea\u52a8\u540c\u6b65\uff01<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">\u7b2c\u4e09\u6b65\uff1a\u5f00\u542f\u4ece\u5e93\u590d\u5236<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >mysql&gt; start replica;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u67e5\u770b\u590d\u5236\u72b6\u6001<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"prettyprint\" >mysql&gt; show slave status\\G\n...\nSlave_IO_Running: Yes\nSlave_SQL_Running: Yes\n...<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4f5c\u7528\uff1a\u57fa\u4e8eGTID\u5b9e\u73b0\u4e3b\u4ece\u590d\u5236\uff08\u91cd\u70b9\uff09 \u6587\u6863\uff1ahttps:\/\/dev.mysql.com\/doc\/refman [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[],"class_list":["post-239","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=\/wp\/v2\/posts\/239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=239"}],"version-history":[{"count":1,"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=\/wp\/v2\/posts\/239\/revisions"}],"predecessor-version":[{"id":240,"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=\/wp\/v2\/posts\/239\/revisions\/240"}],"wp:attachment":[{"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xxiaoteng.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}