{"id":183,"date":"2026-01-23T03:29:26","date_gmt":"2026-01-23T03:29:26","guid":{"rendered":"https:\/\/blog.gpst.net.cn:4008\/?p=183"},"modified":"2026-01-28T06:24:15","modified_gmt":"2026-01-28T06:24:15","slug":"mysql%e8%bf%90%e7%bb%b4-%e5%9f%ba%e4%ba%8e-gtid-%e6%a8%a1%e5%bc%8f%e7%9a%84-mysql-%e4%bb%8e%e5%ba%93%e6%81%a2%e5%a4%8d%e4%b8%8e%e4%b8%bb%e4%bb%8e%e9%87%8d%e5%bb%ba","status":"publish","type":"post","link":"https:\/\/opshub.com.cn\/?p=183","title":{"rendered":"MySQL\u8fd0\u7ef4\u2014\u2014\u57fa\u4e8e GTID \u6a21\u5f0f\u7684 MySQL \u4ece\u5e93\u6062\u590d\u4e0e\u4e3b\u4ece\u91cd\u5efa"},"content":{"rendered":"\n

\u901a\u8fc7 xtrabackup \u5907\u4efd\u4e3b\u5e93\u6570\u636e\u6062\u590d\u5230\u4ece\u5e93\uff0c\u91cd\u7f6e\u4ece\u5e93\u7684 GTID \u4fe1\u606f\uff0c\u91cd\u65b0\u914d\u7f6e\u4e3b\u4ece\u540c\u6b65\uff08GTID \u6a21\u5f0f\uff09\uff0c\u8ba9\u4ece\u5e93\u4ece\u5907\u4efd\u7684 GTID \u4f4d\u70b9\u5f00\u59cb\u540c\u6b65\u4e3b\u5e93\u540e\u7eed\u7684 binlog\uff0c\u91cd\u5efa\u4e3b\u4ece\u590d\u5236\u5173\u7cfb\u3002<\/p>\n\n\n\n

GTID \u6a21\u5f0f\u7684\u524d\u63d0\u6761\u4ef6<\/h4>\n\n\n\n

\u4e3b\u5e93\u7684 repl<\/code> \u7528\u6237\u5fc5\u987b\u6709\u590d\u5236\u6743\u9650\uff1a<\/p>\n\n\n\n

-- \u4e3b\u5e93\u6267\u884c\nGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'\u4ece\u5e93IP' IDENTIFIED BY 'kga!H6uC6sNo>QjP';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n
\u4e3b\u4ece\u5e93\u5fc5\u987b\u90fd\u5f00\u542f GTID\uff08my.cnf \u914d\u7f6e\uff09\uff1a<\/p>\n\n\n\n
[mysqld]\ngtid_mode=ON\nenforce_gtid_consistency=ON\nlog_slave_updates=ON  # \u4ece\u5e93\u540c\u6b65\u7684\u4e8b\u52a1\u5199\u5165\u81ea\u8eabbinlog\uff08\u53ef\u9009\uff0c\u7ea7\u8054\u590d\u5236\u9700\u8981\uff09<\/code><\/pre>\n\n\n\n
1\u3001\u505c\u4ece\u5e93\uff0c\u5907\u4efd my.cnf\uff0c\u6e05\u7a7a\u4ece\u5e93\u76ee\u5f55<\/p>\n\n\n\n
# \u505c\u4ece\u5e93\uff08CentOS 7\uff09\nsystemctl stop mysqld\n\n# \u5907\u4efdmy.cnf\uff08\u9632\u6b62\u914d\u7f6e\u4e22\u5931\uff09\ncp \/etc\/my.cnf \/etc\/my.cnf.bak\n\n# \u6e05\u7a7a\u4ece\u5e93\u6570\u636e\u76ee\u5f55\uff08\u5982 \/data\/mysql\uff09\nrm -rf \/data\/mysql\/*<\/code><\/pre>\n\n\n\n
2\u3001\u7528 innobackupex \u6062\u590d\u5907\u4efd\u6570\u636e<\/p>\n\n\n\n
innobackupex --defaults-file=3306.my.cnf --copy-back \/stor\/mydata2\/hysound\/2022-07-12_03-01-01\/\nchown -R mysql:mysql \/data\/mysql  # \u66ff\u6362\u4e3a\u4f60\u7684datadir\u8def\u5f84\nchmod -R 755 \/data\/mysql<\/code><\/pre>\n\n\n\n
3\u3001\u63d0\u53d6\u5907\u4efd\u4e2d\u7684 GTID \u4f4d\u70b9\u4fe1\u606f<\/p>\n\n\n\n
\u4ece xtrabackup_info<\/code> \u6587\u4ef6\u4e2d\u83b7\u53d6\u5173\u952e\u4fe1\u606f\uff1a<\/p>\n\n\n\n
binlog_pos = filename 'mysql-bin.000153', position '434434779', GTID of the last change 'caa383cb-f1e0-11eb-a179-2cea7fed4c41:1-3640562,e5ee05ea-ee86-11eb-ac90-2cea7fed4c43:1-401'<\/code><\/pre>\n\n\n\n
4\u3001\u542f\u52a8\u4ece\u5e93<\/p>\n\n\n\n
systemctl start mysqld<\/code><\/pre>\n\n\n\n
5.1\u3001\u4e34\u65f6\u5173\u95ed\u5f53\u524d\u4f1a\u8bdd\u7684 binlog \u8bb0\u5f55<\/p>\n\n\n\n
set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;  -- \u4fdd\u5b58\u5f53\u524dSQL_LOG_BIN\u503c\nset @@SESSION.SQL_LOG_BIN= 0;  -- \u5173\u95ed\u5f53\u524d\u4f1a\u8bdd\u7684binlog\uff08\u907f\u514d\u914d\u7f6e\u64cd\u4f5c\u5199\u5165binlog\uff09<\/code><\/pre>\n\n\n\n
5.2\u3001\u89e3\u51b3 @@GLOBAL.GTID_PURGED<\/code> \u8bbe\u7f6e\u5931\u8d25\u7684\u62a5\u9519<\/p>\n\n\n\n
\u62a5\u9519\u539f\u56e0<\/strong>\uff1a
GTID_PURGED<\/code> \u7528\u4e8e\u6807\u8bb0 \u201c\u5df2\u6e05\u7406\u7684 GTID \u96c6\u5408\u201d\uff0c\u4ec5\u5f53 GTID_EXECUTED<\/code>\uff08\u4ece\u5e93\u5df2\u6267\u884c\u7684 GTID\uff09\u4e3a\u7a7a\u65f6\u624d\u80fd\u8bbe\u7f6e\uff1b\u4ece\u5e93\u6062\u590d\u540e GTID_EXECUTED<\/code> \u4e0d\u4e3a\u7a7a\uff0c\u56e0\u6b64\u9700\u6267\u884c reset master<\/code> \u6e05\u7a7a\u3002<\/p>\n\n\n\n
-- \u539f\u62a5\u9519\uff1aERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.\nreset master;  -- \u91cd\u7f6e\u4e3b\u5e93\u4fe1\u606f\uff08\u4ece\u5e93\u6267\u884c\u6b64\u547d\u4ee4\u4f1a\u6e05\u7a7aGTID_EXECUTED\uff0c\u4e0d\u5f71\u54cd\u4e3b\u5e93\uff09<\/code><\/pre>\n\n\n\n
5.3\u3001\u8bbe\u7f6e GTID_PURGED<\/p>\n\n\n\n
set @@GLOBAL.GTID_PURGED='caa383cb-f1e0-11eb-a179-2cea7fed4c41:1-3640562,e5ee05ea-ee86-11eb-ac90-2cea7fed4c43:1-401';<\/code><\/pre>\n\n\n\n
5.4\u3001\u6062\u590d\u4f1a\u8bdd\u7684binlog\u8bb0\u5f55<\/p>\n\n\n\n
set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;<\/code><\/pre>\n\n\n\n
5.5\u3001\u914d\u7f6e\u4e3b\u4ece\u540c\u6b65\uff08GTID \u6a21\u5f0f\uff09<\/p>\n\n\n\n
\u5173\u952e\u53c2\u6570<\/strong>\uff1amaster_auto_position=1<\/code> \u8868\u793a\u4f7f\u7528 GTID \u6a21\u5f0f\u540c\u6b65\uff0c\u4ece\u5e93\u4f1a\u81ea\u52a8\u627e\u4e3b\u5e93\u4e2d\u672a\u6267\u884c\u7684 GTID \u5f00\u59cb\u540c\u6b65\u3002<\/p>\n\n\n\n
change master to \nmaster_host='10.40.0.3',  -- \u4e3b\u5e93IP\nmaster_port=3306,         -- \u4e3b\u5e93\u7aef\u53e3\nmaster_user='repl',       -- \u4e3b\u4ece\u590d\u5236\u7528\u6237\nmaster_password='kga!H6uC6sNo>QjP',  -- \u590d\u5236\u7528\u6237\u5bc6\u7801\nmaster_auto_position=1;   -- \u542f\u7528GTID\u81ea\u52a8\u5b9a\u4f4d\uff08\u6838\u5fc3\uff0c\u66ff\u4ee3\u4f20\u7edf\u7684log_file+log_pos\uff09<\/code><\/pre>\n\n\n\n
5.6\u3001\u542f\u52a8\u4ece\u5e93\u540c\u6b65\u8fdb\u7a0b<\/p>\n\n\n\n
start slave;<\/code><\/pre>\n\n\n\n
6\u3001\u9a8c\u8bc1\u4e3b\u4ece\u540c\u6b65\u72b6\u6001<\/p>\n\n\n\n
show slave status\\G;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u901a\u8fc7 xtrabackup \u5907\u4efd\u4e3b\u5e93\u6570\u636e\u6062\u590d\u5230\u4ece\u5e93\uff0c\u91cd\u7f6e\u4ece\u5e93\u7684 GTID \u4fe1\u606f\uff0c\u91cd\u65b0\u914d\u7f6e\u4e3b\u4ece\u540c\u6b65\uff08GTID \u6a21\u5f0f\uff09\uff0c\u8ba9\u4ece\u5e93\u4ece\u5907\u4efd\u7684 GTID \u4f4d\u70b9\u5f00\u59cb\u540c\u6b65\u4e3b\u5e93\u540e\u7eed\u7684 binlog\uff0c\u91cd\u5efa\u4e3b\u4ece\u590d\u5236\u5173\u7cfb\u3002 GTID \u6a21\u5f0f\u7684\u524d\u63d0\u6761\u4ef6 \u4e3b\u5e93\u7684 repl \u7528\u6237\u5fc5\u987b\u6709\u590d\u5236\u6743\u9650\uff1a \u4e3b\u4ece\u5e93\u5fc5\u987b\u90fd\u5f00\u542f GTID\uff08my.cnf \u914d\u7f6e\uff09\uff1a 1\u3001\u505c\u4ece\u5e93\uff0c\u5907\u4efd my.cnf\uff0c\u6e05\u7a7a\u4ece\u5e93\u76ee\u5f55 2\u3001\u7528 innobackupex \u6062\u590d\u5907\u4efd\u6570\u636e 3\u3001\u63d0\u53d6\u5907\u4efd\u4e2d\u7684 GTID \u4f4d\u70b9\u4fe1\u606f \u4ece xtrabackup_info \u6587\u4ef6\u4e2d\u83b7\u53d6\u5173\u952e\u4fe1\u606f\uff1a 4\u3001\u542f\u52a8\u4ece\u5e93 5.1\u3001\u4e34\u65f6\u5173\u95ed\u5f53\u524d\u4f1a\u8bdd\u7684 binlog \u8bb0\u5f55 5.2\u3001\u89e3\u51b3 @@GLOBAL.GTID_PURGED \u8bbe\u7f6e\u5931\u8d25\u7684\u62a5\u9519 \u62a5\u9519\u539f\u56e0\uff1aGTID_PURGED \u7528\u4e8e\u6807\u8bb0 \u201c\u5df2\u6e05\u7406\u7684 GTID \u96c6\u5408\u201d\uff0c\u4ec5\u5f53 GTID_EXECUTED\uff08\u4ece\u5e93\u5df2\u6267\u884c\u7684 GTID\uff09\u4e3a\u7a7a\u65f6\u624d\u80fd\u8bbe\u7f6e\uff1b\u4ece\u5e93\u6062\u590d\u540e GTID_EXECUTED \u4e0d\u4e3a\u7a7a\uff0c\u56e0\u6b64\u9700\u6267\u884c reset master \u6e05\u7a7a\u3002 5.3\u3001\u8bbe\u7f6e GTID_PURGED 5.4\u3001\u6062\u590d\u4f1a\u8bdd\u7684binlog\u8bb0\u5f55 5.5\u3001\u914d\u7f6e\u4e3b\u4ece\u540c\u6b65\uff08GTID \u6a21\u5f0f\uff09 \u5173\u952e\u53c2\u6570\uff1amaster_auto_position=1 \u8868\u793a\u4f7f\u7528 GTID […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-183","post","type-post","status-publish","format-standard","hentry","category-8"],"_links":{"self":[{"href":"https:\/\/opshub.com.cn\/index.php?rest_route=\/wp\/v2\/posts\/183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/opshub.com.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/opshub.com.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/opshub.com.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/opshub.com.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=183"}],"version-history":[{"count":2,"href":"https:\/\/opshub.com.cn\/index.php?rest_route=\/wp\/v2\/posts\/183\/revisions"}],"predecessor-version":[{"id":658,"href":"https:\/\/opshub.com.cn\/index.php?rest_route=\/wp\/v2\/posts\/183\/revisions\/658"}],"wp:attachment":[{"href":"https:\/\/opshub.com.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/opshub.com.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/opshub.com.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}