{"id":809,"date":"2026-02-24T11:29:28","date_gmt":"2026-02-24T03:29:28","guid":{"rendered":"http:\/\/47.100.165.17\/wordpress\/?p=809"},"modified":"2026-02-24T11:29:28","modified_gmt":"2026-02-24T03:29:28","slug":"%e3%80%90mysql%e3%80%91-%e4%b8%bb%e4%bb%8e%e5%a4%8d%e5%88%b6%e6%95%85%e9%9a%9c%e6%8e%92%e6%9f%a5%e5%8f%8a%e8%a7%a3%e5%86%b3%e6%96%b9%e6%a1%88","status":"publish","type":"post","link":"https:\/\/yunyanglib.cn\/?p=809","title":{"rendered":"\u3010MySQL\u3011 \u4e3b\u4ece\u590d\u5236\u6545\u969c\u6392\u67e5\u53ca\u89e3\u51b3\u65b9\u6848"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\" id=\"u43e0b2f5\">\u54c8\u55bd\u5404\u4f4d\u5c0f\u4f19\u4f34\uff0c\u6211\u662f\u4e91\u626c\uff5e<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u43e0b2f5\">\u5728 MySQL \u751f\u4ea7\u8fd0\u7ef4\u91cc\uff0c\u4e3b\u4ece\u590d\u5236\u662f\u6570\u636e\u5907\u4efd\u3001\u8bfb\u5199\u5206\u79bb\u3001\u9ad8\u53ef\u7528\u67b6\u6784\u7684\u6838\u5fc3\uff0c\u4f46\u914d\u7f6e\u3001\u7f51\u7edc\u3001\u78c1\u76d8\u3001\u6570\u636e\u4e00\u81f4\u6027\u3001\u65e5\u5fd7\u6e05\u7406\u7b49\u95ee\u9898\uff0c\u603b\u80fd\u8ba9\u590d\u5236\u7a81\u7136\u4e2d\u65ad\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u43e0b2f5\">\u4eca\u5929\u6211\u628a<strong>\u5b9e\u6218\u4e2d 9 \u7c7b\u9ad8\u9891\u4e3b\u4ece\u590d\u5236\u6545\u969c<\/strong>\u6574\u7406\u6210\u6587\uff0c\u5305\u542b\u95ee\u9898\u6a21\u62df\u3001\u62a5\u9519\u5206\u6790\u3001\u5206\u6b65\u89e3\u51b3\u65b9\u6848\uff0c\u9047\u5230\u95ee\u9898\u76f4\u63a5\u5bf9\u7167\u5904\u7406\uff0c\u9ad8\u6548\u6551\u573a\uff01<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"TQ8AQ\">1. server_id \u91cd\u590d\u5bfc\u81f4\u590d\u5236\u4e2d\u65ad<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"rJCZF\">\u95ee\u9898\u6a21\u62df<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ud80d332d\">MySQL \u4e3b\u4ece\u590d\u5236\u8981\u6c42\u6240\u6709\u8282\u70b9\u7684<code>server_id<\/code>\u552f\u4e00\uff08\u53d6\u503c\u8303\u56f4 1-4294967295\uff09\u3002\u82e5\u4ece\u5e93<code>server_id<\/code>\u4e0e\u4e3b\u5e93\u4e00\u81f4\uff0c\u4f1a\u89e6\u53d1\u590d\u5236\u6821\u9a8c\u5931\u8d25\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u67e5\u770b\u4e3b\u5e93<code>server_id<\/code>\uff1a\u5728\u4ece\u5e93\u6267\u884c<code>show slave status\\G<\/code>\uff0c\u4ece<code>Master_Server_Id<\/code>\u5b57\u6bb5\u83b7\u53d6\u4e3b\u5e93 ID\uff08\u4f8b\uff1a184151\uff09\uff1b<\/li>\n\n\n\n<li>\u6545\u610f\u5c06\u4ece\u5e93<code>server_id<\/code>\u6539\u4e3a\u4e0e\u4e3b\u5e93\u4e00\u81f4\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"EFrao\" class=\"wp-block-code\"><code>set global server_id=184151; -- \u4e0e\u4e3b\u5e93ID\u76f8\u540c\n\nstop slave; -- \u91cd\u542f\u590d\u5236\n\nstart slave;<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u67e5\u770b\u62a5\u9519\uff1a\u6267\u884c<code>show slave status\\G<\/code>\uff0c\u4f1a\u63d0\u793a \u201cserver_id of slave is equal to server_id of master\u201d\u3002<br><img decoding=\"async\" width=\"1900\" src=\"https:\/\/i-blog.csdnimg.cn\/direct\/0dff4597b8fc439f8b441b57e1f61b4d.png\"><\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"VzBvh\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4fee\u6539\u4ece\u5e93<code>server_id<\/code>\u4e3a\u552f\u4e00\u503c\uff08\u4f8b\uff1a184152\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"C4qNy\" class=\"wp-block-code\"><code>set global server_id=184152; -- \u786e\u4fdd\u4e0e\u4e3b\u5e93\u53ca\u5176\u4ed6\u4ece\u5e93\u4e0d\u91cd\u590d<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u91cd\u542f\u590d\u5236\u5e76\u9a8c\u8bc1\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"e9VXT\" class=\"wp-block-code\"><code>stop slave;\n\nstart slave;\n\nshow slave status\\G -- \u89c2\u5bdfSlave_IO_Running\u548cSlave_SQL_Running\u662f\u5426\u5747\u4e3aYes<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img fetchpriority=\"high\" decoding=\"async\" width=\"884\" height=\"524\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-231.png\" alt=\"\" class=\"wp-image-829\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-231.png 884w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-231-300x178.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-231-768x455.png 768w\" sizes=\"(max-width: 884px) 100vw, 884px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"WPz05\">2. \u4e3b\u4ece\u7aef\u53e3\u4e0d\u901a\uff083306 \u7aef\u53e3\u88ab\u62e6\u622a\uff09<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"BPpD8\">\u95ee\u9898\u6a21\u62df<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ua1c3e04d\">\u4e3b\u5e93\u9632\u706b\u5899\u62e6\u622a\u4ece\u5e93\u5bf9 3306 \u7aef\u53e3\u7684\u8bbf\u95ee\uff0c\u5bfc\u81f4\u4ece\u5e93\u65e0\u6cd5\u8fde\u63a5\u4e3b\u5e93\u83b7\u53d6 Binlog\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4e3b\u5e93\u6dfb\u52a0\u9632\u706b\u5899\u89c4\u5219\uff0c\u7981\u6b62\u4ece\u5e93 IP\uff08\u4f8b\uff1a192.168.184.152\uff09\u8bbf\u95ee 3306\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"UrGwP\" class=\"wp-block-code\"><code>iptables -A INPUT -s 192.168.184.152 -p tcp --dport 3306 -j DROP<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u4ece\u5e93\u6d4b\u8bd5\u7aef\u53e3\u8fde\u901a\u6027\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"m79be\" class=\"wp-block-code\"><code>telnet 192.168.184.151 3306 -- \u63d0\u793a\"Connection refused\"\u6216\u8d85\u65f6<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u67e5\u770b\u590d\u5236\u72b6\u6001\uff1a<code>show slave status\\G<\/code>\u4f1a\u663e\u793a \u201cSlave_IO_Running: Connecting\uff0cSeconds_Behind_Master: NULL\u201d\uff0c\u62a5\u9519 \u201c<code>error connecting to master 'repl@192.168.184.151:3306' - retry-time: 60 retries: 2 message: Can't connect to MySQL server on '192.168.184.151:3306'<\/code>\u201d\u3002<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" width=\"1024\" height=\"446\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-223-1024x446.png\" alt=\"\" class=\"wp-image-813\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-223-1024x446.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-223-300x131.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-223-768x335.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-223-1536x670.png 1536w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-223.png 1849w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Tp3wV\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4e3b\u5e93\u5220\u9664\u62e6\u622a\u89c4\u5219\uff08\u5148\u67e5\u770b\u89c4\u5219\u884c\u53f7\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"AEp92\" class=\"wp-block-code\"><code>iptables -L -n --line-numbers -- \u627e\u5230DROP\u89c4\u5219\u7684\u884c\u53f7\uff08\u4f8b\uff1a1\uff09\n\niptables -D INPUT 1 -- \u5220\u9664\u7b2c1\u6761\u89c4\u5219\uff1b\u82e5\u9700\u6e05\u7a7a\u6240\u6709\u89c4\u5219\uff0c\u6267\u884ciptables -F<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u4ece\u5e93\u9a8c\u8bc1\u7aef\u53e3\u8fde\u901a\u6027\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"AjjZw\" class=\"wp-block-code\"><code>telnet 192.168.184.151 3306 -- \u51fa\u73b0\"Connected to...\"\u8868\u793a\u901a\u4e86<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u91cd\u542f\u590d\u5236\u5e76\u9a8c\u8bc1\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"TSXLh\" class=\"wp-block-code\"><code>stop slave;\n\nstart slave;\n\nshow slave status\\G -- \u786e\u8ba4Slave_IO_Running\u8f6c\u4e3aYes<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" width=\"1024\" height=\"657\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-224-1024x657.png\" alt=\"\" class=\"wp-image-815\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-224-1024x657.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-224-300x192.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-224-768x493.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-224.png 1102w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"mgE5S\">3. \u4ece\u5e93\u78c1\u76d8\u7a7a\u95f4\u6ee1\u5bfc\u81f4\u590d\u5236\u4e2d\u65ad<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Vvln3\">\u95ee\u9898\u6a21\u62df<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"uef16d221\">\u4ece\u5e93\u78c1\u76d8\u8017\u5c3d\u540e\uff0c\u65e0\u6cd5\u5199\u5165 Relay Log \u6216\u6570\u636e\u6587\u4ef6\uff0c\u5bfc\u81f4\u590d\u5236\u505c\u6b62\u751a\u81f3 MySQL \u8fdb\u7a0b\u5d29\u6e83\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u67e5\u770b\u4ece\u5e93\u78c1\u76d8\u7a7a\u95f4\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"Rs85l\" class=\"wp-block-code\"><code>df -Th -- \u663e\u793a\u78c1\u76d8\u4f7f\u7528\u7387100%<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u6a21\u62df\u78c1\u76d8\u5360\u6ee1\uff1a\u521b\u5efa 15G \u4e34\u65f6\u6587\u4ef6\uff08\u9700\u786e\u4fdd\u5269\u4f59\u7a7a\u95f4\u4e0d\u8db3 15G\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"ckuiJ\" class=\"wp-block-code\"><code>dd if=\/dev\/zero of=fill bs=1M count=15000<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u4e3b\u5e93\u5199\u5165\u6570\u636e\uff08\u4f8b\uff1a\u901a\u8fc7\u5b58\u50a8\u8fc7\u7a0b\u63d2\u5165 1 \u4e07\u884c\u6d4b\u8bd5\u6570\u636e\uff09\uff0c\u4ece\u5e93\u6267\u884c<code>show slave status\\G<\/code>\u4f1a\u53d1\u73b0\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u590d\u5236\u5ef6\u8fdf\uff08Seconds_Behind_Master\uff09\u6301\u7eed\u589e\u52a0\uff1b<\/li>\n\n\n\n<li>\u6267\u884c<code>stop slave<\/code>\u65f6\uff0cMySQL \u53ef\u80fd\u56e0\u78c1\u76d8\u6ee1\u800c\u5f02\u5e38\u4e2d\u65ad\u3002<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"912\" height=\"133\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-232.png\" alt=\"\" class=\"wp-image-830\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-232.png 912w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-232-300x44.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-232-768x112.png 768w\" sizes=\"(max-width: 912px) 100vw, 912px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"qmY3V\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u91ca\u653e\u4ece\u5e93\u78c1\u76d8\u7a7a\u95f4\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"JZaXc\" class=\"wp-block-code\"><code>du -sh * -- \u67e5\u770b\u5f53\u524d\u76ee\u5f55\u5927\u6587\u4ef6\/\u6587\u4ef6\u5939\uff08\u4f8b\uff1a\u627e\u5230fill\u4e34\u65f6\u6587\u4ef6\uff09\n\nrm -f fill -- \u5220\u9664\u65e0\u7528\u5927\u6587\u4ef6\uff1b\u82e5\u4e3a\u65e5\u5fd7\u6587\u4ef6\uff0c\u53ef\u6e05\u7406MySQL\u6162\u67e5\u8be2\u65e5\u5fd7\u3001Binlog\uff08\u9700\u786e\u8ba4\u5b89\u5168\uff09\n\ndf -Th -- \u786e\u8ba4\u78c1\u76d8\u4f7f\u7528\u7387\u964d\u81f3\u5408\u7406\u8303\u56f4\uff08\u5efa\u8bae&lt;80%\uff09<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u91cd\u542f MySQL \u670d\u52a1\uff08\u82e5\u5df2\u5d29\u6e83\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"b25hi\" class=\"wp-block-code\"><code>\/etc\/init.d\/mysql.server start<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u9a8c\u8bc1\u590d\u5236\u72b6\u6001\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"pSKfn\" class=\"wp-block-code\"><code>show slave status\\G -- \u786e\u8ba4\u590d\u5236\u6062\u590d\uff0c\u5ef6\u8fdf\u9010\u6e10\u964d\u4f4e<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"ydjKD\">4. \u4e3b\u5e93\u65b0\u589e\u5185\u5bb9\u5728\u4ece\u5e93\u5df2\u5b58\u5728\uff08\u6570\u636e\u51b2\u7a81\uff09<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"o5G5C\">\u95ee\u9898\u6a21\u62df<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u23b7df30\">\u4ece\u5e93\u624b\u52a8\u521b\u5efa\u4e86\u4e3b\u5e93\u540e\u7eed\u8981\u65b0\u589e\u7684\u5bf9\u8c61\uff08\u5982\u6570\u636e\u5e93\u3001\u8868\uff09\uff0c\u5bfc\u81f4\u4e3b\u5e93 SQL \u540c\u6b65\u5230\u4ece\u5e93\u65f6\u89e6\u53d1 \u201c\u5df2\u5b58\u5728\u201d \u9519\u8bef\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4ece\u5e93\u624b\u52a8\u521b\u5efa\u6570\u636e\u5e93\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"Pw6NG\" class=\"wp-block-code\"><code>create database maria_test;<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u4e3b\u5e93\u6267\u884c\u76f8\u540c\u5efa\u5e93\u8bed\u53e5\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"g9Zss\" class=\"wp-block-code\"><code>create database maria_test;<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u4ece\u5e93\u67e5\u770b\u62a5\u9519\uff1a<code>show slave status\\G<\/code>\u4e2d<code>Last_SQL_Error<\/code>\u63d0\u793a \u201c<code>Can't create database 'maria_test'; database exists<\/code>\u201d\u3002<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"454\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-225-1024x454.png\" alt=\"\" class=\"wp-image-816\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-225-1024x454.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-225-300x133.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-225-768x341.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-225-1536x681.png 1536w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-225.png 1804w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"UKkEj\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"uf97f42ce\">\u9700\u6839\u636e\u590d\u5236\u6a21\u5f0f\uff08GTID \/ \u4f4d\u70b9\uff09\u8df3\u8fc7\u51b2\u7a81\u4e8b\u52a1\uff1a<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"VdTro\">\u65b9\u6848 1\uff1aGTID \u6a21\u5f0f<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u67e5\u770b\u4ece\u5e93\u5df2\u6267\u884c\u7684 GTID\uff1a<code>show slave status\\G<\/code>\u4e2d<code>Executed_Gtid_Set<\/code>\uff08\u4f8b\uff1a65c07f5e-1e37-11ed-b657-fa163e0d61f4:1-10139\uff09\uff1b<\/li>\n\n\n\n<li>\u8df3\u8fc7\u4e0b\u4e00\u4e2a GTID\uff08\u51b2\u7a81\u4e8b\u52a1\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"zs9qn\" class=\"wp-block-code\"><code>stop slave;\n\n-- \u8bbe\u7f6e\u4e0b\u4e00\u4e2aGTID\u4e3aExecuted_Gtid_Set\u7684\u4e0b\u4e00\u4e2a\u503c\uff0810139+1=10140\uff09\n\nset @@session.gtid_next='65c07f5e-1e37-11ed-b657-fa163e0d61f4:10140';\n\nbegin; commit; -- \u7a7a\u4e8b\u52a1\u4ee3\u66ff\u51b2\u7a81\u4e8b\u52a1\n\nset session gtid_next='AUTOMATIC'; -- \u6062\u590d\u81ea\u52a8GTID\u5206\u914d\n\nstart slave;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"abRlW\">\u65b9\u6848 2\uff1a\u4f4d\u70b9\u6a21\u5f0f<\/h4>\n\n\n\n<pre id=\"iK4f2\" class=\"wp-block-code\"><code>stop slave;\n\nset GLOBAL SQL_SLAVE_SKIP_COUNTER=1; -- \u8df3\u8fc71\u4e2a\u4e8b\u52a1\uff08\u51b2\u7a81\u4e8b\u52a1\uff09\n\nstart slave;\n\nshow slave status\\G -- \u786e\u8ba4SQL\u7ebf\u7a0b\u6062\u590d<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"PMfAg\">5. \u4e3b\u5e93\u8981\u66f4\u65b0\u7684\u8bb0\u5f55\u4ece\u5e93\u6ca1\u6709<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"iVLqv\">\u95ee\u9898\u6a21\u62df<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u81d92659\">\u4e3b\u5e93\u6267\u884c\u66f4\u65b0\u64cd\u4f5c\u65f6\uff0c\u82e5\u4ece\u5e93\u5bf9\u5e94\u8bb0\u5f55\u5df2\u88ab\u624b\u52a8\u5220\u9664\uff0c\u4f1a\u5bfc\u81f4\u4ece\u5e93 SQL \u7ebf\u7a0b\u6267\u884c\u540c\u6b65\u4e8b\u52a1\u65f6\u89e6\u53d1 \u201c\u8bb0\u5f55\u4e0d\u5b58\u5728\u201d \u9519\u8bef\uff0c\u590d\u5236\u4e2d\u65ad\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u4e3b\u5e93\u51c6\u5907\u6d4b\u8bd5\u6570\u636e<\/strong>\uff1a\u521b\u5efa\u8868\u5e76\u63d2\u5165\u521d\u59cb\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre id=\"zluIm\" class=\"wp-block-code\"><code>use maria_test; -- \u6cbf\u7528\u524d\u6587\u521b\u5efa\u7684\u6d4b\u8bd5\u5e93\n-- \u521b\u5efa\u6d4b\u8bd5\u8868\uff08\u6307\u5b9aInnoDB\u5f15\u64ce\u548cutf8mb4\u5b57\u7b26\u96c6\uff0c\u786e\u4fdd\u4e3b\u4ece\u4e00\u81f4\uff09\nCREATE TABLE test_repl (\nid int NOT NULL AUTO_INCREMENT,\na int NOT NULL,\nPRIMARY KEY (id) -- \u4e3b\u952e\u786e\u4fdd\u6570\u636e\u552f\u4e00\u6027\n) ENGINE=InnoDB CHARSET=utf8mb4;\n-- \u63d2\u51652\u6761\u6d4b\u8bd5\u6570\u636e\ninsert into test_repl values (1,1),(2,2);<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>\u4ece\u5e93\u624b\u52a8\u5220\u9664\u8bb0\u5f55<\/strong>\uff1a\u6a21\u62df\u6570\u636e\u4e0d\u4e00\u81f4\u573a\u666f<\/li>\n<\/ol>\n\n\n\n<pre id=\"nsFMi\" class=\"wp-block-code\"><code>use maria_test;\ndelete from test_repl where id=1; -- \u5220\u9664id=1\u7684\u8bb0\u5f55\uff0c\u4e3b\u5e93\u6b64\u8bb0\u5f55\u4ecd\u5b58\u5728<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>\u4e3b\u5e93\u6267\u884c\u66f4\u65b0\u64cd\u4f5c<\/strong>\uff1a\u89e6\u53d1\u540c\u6b65\u51b2\u7a81<\/li>\n<\/ol>\n\n\n\n<pre id=\"NypFr\" class=\"wp-block-code\"><code>use maria_test;\nupdate test_repl set a=2 where id=1; -- \u66f4\u65b0\u4e3b\u5e93\u4e2d\u5b58\u5728\u3001\u4ece\u5e93\u5df2\u5220\u9664\u7684\u8bb0\u5f55<\/code><\/pre>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li><strong>\u4ece\u5e93\u67e5\u770b\u62a5\u9519<\/strong>\uff1a\u6267\u884c<code>show slave status\\G<\/code>\uff0c<code>Last_SQL_Error<\/code>\u4f1a\u63d0\u793a \u201c<code>Could not execute Update_rows event on table maria_test.test_repl; Can't find record in 'test_repl'<\/code>\u201d\uff0c\u660e\u786e\u8868\u793a\u8981\u66f4\u65b0\u7684\u8bb0\u5f55\u4e0d\u5b58\u5728\u3002<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"445\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-226-1024x445.png\" alt=\"\" class=\"wp-image-817\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-226-1024x445.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-226-300x130.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-226-768x334.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-226-1536x667.png 1536w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-226.png 1890w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"u85Wz\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ubb27650e\">\u6838\u5fc3\u601d\u8def\uff1a\u901a\u8fc7\u89e3\u6790\u4ece\u5e93 Relay Log\uff08\u4e2d\u7ee7\u65e5\u5fd7\uff09\u83b7\u53d6\u4e3b\u5e93\u66f4\u65b0\u64cd\u4f5c\u7684\u539f\u59cb\u6761\u4ef6\uff0c\u5728\u4ece\u5e93\u8865\u5168\u7f3a\u5931\u8bb0\u5f55\u540e\u6062\u590d\u590d\u5236\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u5b9a\u4f4d Relay Log \u4f4d\u7f6e<\/strong>\uff1a\u4ece<code>show slave status\\G<\/code>\u7ed3\u679c\u4e2d\u83b7\u53d6\u5173\u952e\u4fe1\u606f\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Relay_Log_File<\/code>\uff1a\u5f53\u524d\u4f7f\u7528\u7684\u4e2d\u7ee7\u65e5\u5fd7\u6587\u4ef6\uff08\u4f8b\uff1amysql-relay-bin.000023\uff09<\/li>\n\n\n\n<li><code>Relay_Log_Pos<\/code>\uff1a\u5f53\u524d\u540c\u6b65\u4f4d\u7f6e\uff08\u4f8b\uff1a1097\uff09<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"334\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-227-1024x334.png\" alt=\"\" class=\"wp-image-819\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-227-1024x334.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-227-300x98.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-227-768x250.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-227.png 1353w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>\u89e3\u6790 Relay Log \u83b7\u53d6\u539f\u59cb\u6570\u636e<\/strong>\uff1a\u4f7f\u7528mysqlbinlog\u5de5\u5177\u89e3\u7801\u4e2d\u7ee7\u65e5\u5fd7\uff0c\u63d0\u53d6\u66f4\u65b0\u64cd\u4f5c\u7684\u539f\u59cb\u8bb0\u5f55\u503c<\/li>\n<\/ol>\n\n\n\n<pre id=\"moWZ3\" class=\"wp-block-code\"><code># \u8fdb\u5165Relay Log\u6240\u5728\u76ee\u5f55\uff08\u901a\u5e38\u4e0eBinlog\u76ee\u5f55\u4e00\u81f4\uff09\ncd \/data\/mysql\/binlog\n# \u89e3\u6790\u6307\u5b9a\u4f4d\u7f6e\u7684\u65e5\u5fd7\uff0c\u8f93\u51fa\u4e3a\u53ef\u8bfbSQL\u6587\u4ef6\uff08--base64-output=decode-rows\u786e\u4fdd\u884c\u6570\u636e\u89e3\u7801\uff09\nmysqlbinlog mysql-relay-bin.000017 --start-position=1129 --base64-output=decode-rows -v > \/data\/backup\/20260224.sql<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>\u67e5\u770b\u89e3\u6790\u7ed3\u679c<\/strong>\uff1a\u6253\u5f00\/data\/backup\/20260224.sql\uff0c\u627e\u5230UPDATE\u8bed\u53e5\u5bf9\u5e94\u7684WHERE\u6761\u4ef6\uff08\u5373\u4e3b\u5e93\u66f4\u65b0\u524d\u7684\u539f\u59cb\u6570\u636e\uff09\uff0c\u53ef\u786e\u8ba4\u7f3a\u5931\u8bb0\u5f55\u4e3aid=1, a=1\u3002<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"553\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-228-1024x553.png\" alt=\"\" class=\"wp-image-821\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-228-1024x553.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-228-300x162.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-228-768x414.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-228.png 1512w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>\u4ece\u5e93\u8865\u5168\u7f3a\u5931\u8bb0\u5f55<\/strong>\uff1a\u6839\u636e\u89e3\u6790\u7ed3\u679c\u63d2\u5165\u7f3a\u5931\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre id=\"IGfVl\" class=\"wp-block-code\"><code>use maria_test;\ninsert into test_repl select 1,1; -- \u8865\u5168id=1\u7684\u539f\u59cb\u8bb0\u5f55<\/code><\/pre>\n\n\n\n<ol start=\"5\" class=\"wp-block-list\">\n<li><strong>\u91cd\u542f\u590d\u5236\u5e76\u9a8c\u8bc1<\/strong>\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"bG49k\" class=\"wp-block-code\"><code>stop slave; -- \u505c\u6b62\u590d\u5236\nstart slave; -- \u91cd\u542f\u590d\u5236\nshow slave status\\G -- \u67e5\u770b\u72b6\u6001\uff0c\u786e\u8ba4Slave_IO_Running\u548cSlave_SQL_Running\u5747\u4e3aYes\uff0c\u65e0\u62a5\u9519<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"736\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-229-1024x736.png\" alt=\"\" class=\"wp-image-822\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-229-1024x736.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-229-300x216.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-229-768x552.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-229.png 1069w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"bjznk\">6. \u627e\u4e0d\u5230\u4e3b\u5e93\u7684 Binlog \u4f4d\u70b9\uff08Binlog \u88ab\u6e05\u7406\uff09<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"IiC7v\">\u95ee\u9898\u6a21\u62df<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u5b36d78d\">\u4e3b\u5e93\u6e05\u7406\u4e86\u4ece\u5e93\u672a\u540c\u6b65\u7684 Binlog \u6587\u4ef6\uff08\u5982\u6267\u884c<code>purge binary logs<\/code>\uff09\uff0c\u5bfc\u81f4\u4ece\u5e93\u542f\u52a8\u590d\u5236\u65f6\u63d0\u793a \u201cBinlog file not found\u201d\u3002<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4ece\u5e93\u505c\u6b62\u590d\u5236\uff1a<code>stop slave;<\/code>\uff1b<\/li>\n\n\n\n<li>\u4e3b\u5e93\u5237\u65b0\u5e76\u6e05\u7406 Binlog\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"MwJ4R\" class=\"wp-block-code\"><code>flush logs; -- \u751f\u6210\u65b0Binlog\u6587\u4ef6\uff08\u4f8b\uff1amysql-bin.000025\uff09\n\ncreate database a1; -- \u5199\u5165\u65b0\u4e8b\u52a1\uff08Binlog\u8bb0\u5f55\u5728000025\uff09\n\nflush logs; -- \u518d\u6b21\u5237\u65b0\uff0c\u751f\u6210000026\n\nshow binary logs;\n\npurge binary logs to 'mysql-bin.000026'; -- \u6e05\u7406000026\u4e4b\u524d\u7684Binlog<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u4ece\u5e93\u542f\u52a8\u590d\u5236\uff1a<code>start slave;<\/code>\uff0c\u6267\u884c<code>show slave status\\G<\/code>\u4f1a\u62a5\u9519 \u201c<code>Cannot replicate because the master purged required binary logs<\/code>\u201d\u3002<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"382\" src=\"http:\/\/47.100.165.17\/wordpress\/wp-content\/uploads\/2026\/02\/image-230-1024x382.png\" alt=\"\" class=\"wp-image-826\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-230-1024x382.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-230-300x112.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-230-768x286.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-230-1536x573.png 1536w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/02\/image-230.png 1894w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"CzI08\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ubdcf4659\">\u6839\u636e\u662f\u5426\u6709\u5176\u4ed6\u6b63\u5e38\u4ece\u5e93\uff0c\u5206\u4e24\u79cd\u573a\u666f\u5904\u7406\uff1a<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"Mk3fq\">\u573a\u666f 1\uff1a\u6709\u5176\u4ed6\u6b63\u5e38\u4ece\u5e93\uff08\u63a8\u8350\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ua958012b\">\u5c06\u6545\u969c\u4ece\u5e93\u6539\u4e3a \u201c\u4ece\u4ece\u5e93\u201d\uff0c\u4ece\u6b63\u5e38\u4ece\u5e93\u540c\u6b65\u7f3a\u5931\u4e8b\u52a1\uff1a<\/p>\n\n\n\n<pre id=\"ECuNL\" class=\"wp-block-code\"><code>stop slave;\n\n-- \u6307\u5411\u6b63\u5e38\u4ece\u5e93\uff08\u4f8b\uff1a192.168.184.152\uff09\uff0c\u4f7f\u7528GTID\u81ea\u52a8\u5b9a\u4f4d\u4f4d\u70b9\n\nchange master to\n\nmaster_host='192.168.184.152',\n\nmaster_user='repl', -- \u590d\u5236\u8d26\u53f7\n\nmaster_password='Uid_dQc63',\n\nMASTER_AUTO_POSITION=1;\n\nstart slave;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"J6utz\">\u573a\u666f 2\uff1a\u65e0\u5176\u4ed6\u4ece\u5e93\uff08\u91cd\u5efa\u590d\u5236\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u778cb864\">\u901a\u8fc7 XtraBackup \u5907\u4efd\u4e3b\u5e93\uff0c\u91cd\u5efa\u4ece\u5e93\u590d\u5236\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u4e3b\u5e93\u5907\u4efd\u6570\u636e\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"cGep7\" class=\"wp-block-code\"><code>cd \/data\/backup\n\nrm -rf .\/* -- \u6e05\u7a7a\u65e7\u5907\u4efd\n\n# \u6d41\u5f0f\u5907\u4efd\uff08\u9002\u5408\u8de8\u673a\u5668\u4f20\u8f93\uff09\n\nxtrabackup --defaults-file=\/data\/mysql\/conf\/my.cnf -uroot -p --backup --stream=xbstream --target-dir=.\/ &gt; extrabackup.xbstream<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>\u4ece\u5e93\u6062\u590d\u6570\u636e\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"XSI99\" class=\"wp-block-code\"><code>mkdir \/data\/backup\/recover -- \u521b\u5efa\u6062\u590d\u76ee\u5f55\n\nscp \u4e3b\u5e93IP:\/data\/backup\/extrabackup.xbstream \/data\/backup\/recover\/ -- \u4f20\u8f93\u5907\u4efd\n\n\/etc\/init.d\/mysql.server stop -- \u505c\u6b62\u4ece\u5e93MySQL\n\nrm \/data\/mysql\/data\/* -rf &amp;&amp; rm \/data\/mysql\/binlog\/* -rf -- \u6e05\u7a7a\u65e7\u6570\u636e\n\ncd \/data\/backup\/recover\n\nxbstream -x &lt; extrabackup.xbstream -- \u89e3\u538b\u5907\u4efd\n\nxtrabackup --prepare --target-dir=.\/ -- \u51c6\u5907\u5907\u4efd\uff08\u4e00\u81f4\u6027\u6062\u590d\uff09\n\n# \u590d\u5236\u5907\u4efd\u5230\u6570\u636e\u76ee\u5f55\n\nxtrabackup --defaults-file=\/data\/mysql\/conf\/my.cnf --copy-back --target-dir=.\/\n\nchown -R mysql.mysql \/data\/mysql -- \u4fee\u6539\u6743\u9650\n\n -- \u542f\u52a8\u4ece\u5e93<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>\u4ece\u5e93\u5efa\u7acb\u4e3b\u4ece\u5173\u7cfb\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"TDJie\" class=\"wp-block-code\"><code>cat \/data\/backup\/recover\/xtrabackup_binlog_info -- \u67e5\u770b\u5907\u4efd\u5bf9\u5e94\u7684\u4e3b\u5e93Binlog\u4f4d\u70b9\n\nstop slave;\n\nchange master to\n\nmaster_host='\u4e3b\u5e93IP',\n\nmaster_user='repl',\n\nmaster_password='Uid_dQc63',\n\nMASTER_AUTO_POSITION=1; -- \u6216\u6307\u5b9aBinlog\u6587\u4ef6\u548c\u4f4d\u70b9\n\nstart slave;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"xzSxG\">7. GTID \u7a7a\u6d1e\u7684\u4ea7\u751f\u573a\u666f<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u13d0f383\">GTID\uff08\u5168\u5c40\u4e8b\u52a1 ID\uff09\u662f\u4e3b\u4ece\u590d\u5236\u4e2d\u552f\u4e00\u6807\u8bc6\u4e8b\u52a1\u7684 ID\uff0c\u82e5 GTID \u5e8f\u5217\u51fa\u73b0\u4e0d\u8fde\u7eed\uff08\u5982 1-2 \u540e\u76f4\u63a5 5-6\uff09\uff0c\u5219\u79f0\u4e3a \u201cGTID \u7a7a\u6d1e\u201d\uff0c\u53ef\u80fd\u5bfc\u81f4\u590d\u5236\u5f02\u5e38\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ubQXJ\">\u5e38\u89c1\u4ea7\u751f\u539f\u56e0<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u624b\u52a8\u8df3\u8fc7\u4e8b\u52a1<\/strong>\uff1a\u5982\u6267\u884c<code>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1<\/code>\uff08\u4f4d\u70b9\u6a21\u5f0f\uff09\u6216\u624b\u52a8\u6307\u5b9a<code>gtid_next<\/code>\u8df3\u8fc7\u4e8b\u52a1\uff1b<\/li>\n\n\n\n<li><strong>\u4e3b\u4ece\u5207\u6362<\/strong>\uff1a\u4e3b\u5e93\u6545\u969c\u5207\u6362\u540e\uff0c\u65b0\u4e3b\u5e93\u7684 GTID \u5e8f\u5217\u4e0e\u65e7\u4e3b\u5e93\u672a\u8854\u63a5\uff1b<\/li>\n\n\n\n<li><strong>slave-skip-errors=all<\/strong>\uff1a\u914d\u7f6e\u4ece\u5e93\u8df3\u8fc7\u6240\u6709 SQL \u9519\u8bef\uff0c\u82e5\u67d0 DDL\/DML \u4e8b\u52a1\u88ab\u8df3\u8fc7\uff0c\u4f1a\u5bfc\u81f4 GTID \u7f3a\u5931\u3002<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"nAq4r\">\u89c4\u907f\u5efa\u8bae<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u7981\u6b62\u5728 GTID \u6a21\u5f0f\u4e0b\u4f7f\u7528<code>SQL_SLAVE_SKIP_COUNTER<\/code>\uff1b<\/li>\n\n\n\n<li>\u4e3b\u4ece\u5207\u6362\u524d\uff0c\u786e\u4fdd\u6240\u6709\u4ece\u5e93\u5df2\u540c\u6b65\u65e7\u4e3b\u5e93\u7684 GTID\uff1b<\/li>\n\n\n\n<li>\u907f\u514d\u8bbe\u7f6e<code>slave-skip-errors=all<\/code>\uff0c\u4ec5\u9488\u5bf9\u7279\u5b9a\u65e0\u5bb3\u9519\u8bef\uff08\u5982 1062 \u4e3b\u952e\u51b2\u7a81\uff09\u8df3\u8fc7\u3002<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"WDVH0\">8. \u4e3b\u4ece UUID \u91cd\u590d\u95ee\u9898<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"t1bgl\">\u95ee\u9898\u73b0\u8c61<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u4ce9f0da\">\u4ece\u5e93\u542f\u52a8\u590d\u5236\u65f6\u63d0\u793a \u201cThe server UUIDs are equal\u201d\uff0c\u5373\u4e3b\u4ece\u5e93 UUID \u76f8\u540c\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"SblDI\">\u6839\u672c\u539f\u56e0<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u849bcdc4\">\u4ece\u5e93\u670d\u52a1\u5668\u662f\u4e3b\u5e93\u7684\u514b\u9686\u673a\uff0c\u800c MySQL \u7684 UUID \u5b58\u50a8\u5728\u6570\u636e\u76ee\u5f55\u7684<code>auto.cnf<\/code>\u6587\u4ef6\u4e2d\uff08\u683c\u5f0f\uff1a<code>server-uuid=xxxx-xxxx-xxxx-xxxx<\/code>\uff09\uff0c\u514b\u9686\u4f1a\u5bfc\u81f4<code>auto.cnf<\/code>\u5185\u5bb9\u4e00\u81f4\uff0cUUID \u91cd\u590d\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"F1Kal\">\u89e3\u51b3\u65b9\u6848<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u505c\u6b62\u4ece\u5e93 MySQL\uff1a<code>\/etc\/init.d\/mysql.server stop<\/code>\uff1b<\/li>\n\n\n\n<li>\u5220\u9664\u4ece\u5e93\u7684<code>auto.cnf<\/code>\u6587\u4ef6\uff08\u8def\u5f84\u4f8b\uff1a\/data\/mysql\/data\/auto.cnf\uff09\uff1b<\/li>\n\n\n\n<li>\u91cd\u542f\u4ece\u5e93 MySQL\uff1a<code>\/etc\/init.d\/mysql.server start<\/code>\uff1b<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u91cd\u542f\u540e MySQL \u4f1a\u81ea\u52a8\u751f\u6210\u65b0\u7684 UUID\uff0c\u5199\u5165\u65b0\u7684<code>auto.cnf<\/code>\uff1b<\/li>\n<\/ul>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li>\u91cd\u65b0\u5efa\u7acb\u4e3b\u4ece\u590d\u5236\uff1a<code>change master to ...<\/code>\u540e\u542f\u52a8\u590d\u5236\u5373\u53ef\u3002<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"s5mdk\">9. \u4ece\u5e93\u662f\u5426\u53ef\u80fd\u8bfb\u5230\u6bd4\u4e3b\u5e93\u66f4\u65b0\u7684\u6570\u636e\uff1f<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u642f1338\">\u5728 \u201c\u4e00\u4e3b\u4e24\u4ece\u201d \u67b6\u6784\uff081 \u4e2a\u5f02\u6b65\u4ece\u5e93 + 1 \u4e2a\u534a\u540c\u6b65\u4ece\u5e93\uff09\u4e2d\uff0c\u9700\u7ed3\u5408 MySQL \u590d\u5236\u7c7b\u578b\u548c\u4e24\u9636\u6bb5\u63d0\u4ea4\u539f\u7406\u5206\u6790\uff1a<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ZyNCI\">\u524d\u7f6e\u77e5\u8bc6<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u4e24\u9636\u6bb5\u63d0\u4ea4<\/strong>\uff1aInnoDB \u4e8b\u52a1\u63d0\u4ea4\u5206\u4e24\u6b65\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Prepare \u9636\u6bb5\uff1a\u5199\u5165 Redo Log\uff0c\u6807\u8bb0\u4e8b\u52a1\u4e3a \u201c\u51c6\u5907\u63d0\u4ea4\u201d\uff1b<\/li>\n\n\n\n<li>Commit \u9636\u6bb5\uff1a\u5199\u5165 Binlog\uff0c\u518d\u5c06 Redo Log \u6807\u8bb0\u4e3a \u201c\u5df2\u63d0\u4ea4\u201d\u3002<\/li>\n<\/ul>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>\u590d\u5236\u7c7b\u578b<\/strong>\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5f02\u6b65\u590d\u5236\uff1a\u4e3b\u5e93\u63d0\u4ea4\u4e8b\u52a1\u540e\u7acb\u5373\u8fd4\u56de\u5ba2\u6237\u7aef\uff0c\u4e0d\u7b49\u5f85\u4ece\u5e93\u540c\u6b65\uff1b<\/li>\n\n\n\n<li>\u534a\u540c\u6b65\u590d\u5236\uff08after_commit\uff09\uff1a\u4e3b\u5e93\u6267\u884c Commit \u9636\u6bb5\u540e\uff0c\u7b49\u5f85\u4ece\u5e93\u786e\u8ba4\u63a5\u6536 Binlog \u518d\u8fd4\u56de\uff1b<\/li>\n\n\n\n<li>\u589e\u5f3a\u534a\u540c\u6b65\u590d\u5236\uff08after_sync\uff09\uff1a\u4e3b\u5e93\u6267\u884c Prepare \u9636\u6bb5\u540e\uff0c\u5148\u53d1\u9001 Binlog \u7ed9\u4ece\u5e93\uff0c\u5f85\u4ece\u5e93\u786e\u8ba4\u540e\u518d\u6267\u884c Commit \u9636\u6bb5\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"rE78a\">\u7ed3\u8bba\uff1a\u7279\u5b9a\u573a\u666f\u4e0b\u53ef\u80fd<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u573a\u666f 1\uff1a\u534a\u540c\u6b65\u4e3a after<\/strong><strong>_<\/strong><strong>commit<\/strong>\uff1a\u4e3b\u5e93\u5df2\u6267\u884c Commit\uff0cRedo Log \u6807\u8bb0\u4e3a \u201c\u5df2\u63d0\u4ea4\u201d\uff0c\u4e3b\u5e93\u5ba2\u6237\u7aef\u53ef\u67e5\u65b0\u6570\u636e\uff1b\u6b64\u65f6\u5f02\u6b65\u4ece\u5e93\u5373\u4f7f\u540c\u6b65\u5230 Binlog\uff0c\u6570\u636e\u4e0e\u4e3b\u5e93\u4e00\u81f4\uff0c\u4e0d\u4f1a \u201c\u66f4\u65b0\u201d\u3002<\/li>\n\n\n\n<li><strong>\u573a\u666f 2\uff1a\u534a\u540c\u6b65\u4e3a after<\/strong><strong>_<\/strong><strong>sync<\/strong>\uff1a\u4e3b\u5e93\u53d1\u9001 Binlog \u7ed9\u4ece\u5e93\u540e\uff0c\u4ece\u5e93\uff08\u542b\u5f02\u6b65\u4ece\u5e93\uff09\u5df2\u56de\u653e Binlog\uff0c\u4f46\u4e3b\u5e93\u5c1a\u672a\u6267\u884c Commit \u9636\u6bb5\uff08Redo Log \u4ecd\u4e3a Prepare\uff09\uff1b\u6b64\u65f6\u4e3b\u5e93\u5ba2\u6237\u7aef\u67e5\u4e0d\u5230\u65b0\u6570\u636e\uff0c\u4f46\u5f02\u6b65\u4ece\u5e93\u5df2\u80fd\u67e5\u5230 \u2014\u2014 \u5373<strong>\u5f02\u6b65\u4ece\u5e93\u8bfb\u5230\u6bd4\u4e3b\u5e93\u66f4\u65b0\u7684\u6570\u636e<\/strong>\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"LCG3S\">\u89c4\u907f\u5efa\u8bae<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u3616a989\">\u82e5\u9700\u907f\u514d\u6b64\u95ee\u9898\uff0c\u53ef\u5c06\u534a\u540c\u6b65\u590d\u5236\u6a21\u5f0f\u6539\u4e3a<code>after_commit<\/code>\uff08\u9700\u5728\u4e3b\u5e93\u914d\u7f6e<code>rpl_semi_sync_master_wait_point=AFTER_COMMIT<\/code>\uff09\uff0c\u4f46\u4f1a\u727a\u7272\u90e8\u5206\u6027\u80fd\uff08\u4e3b\u5e93\u9700\u7b49\u5f85 Commit \u540e\u518d\u8fd4\u56de\uff09\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"b5sPO\">\u603b\u7ed3<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u6dbe574c\">MySQL \u4e3b\u4ece\u590d\u5236\u6545\u969c\u6392\u67e5\u7684\u6838\u5fc3\u601d\u8def\u662f\uff1a<strong>\u5148\u5b9a\u4f4d\u6545\u969c\u73af\u8282\uff08IO \u7ebf\u7a0b \/ SQL \u7ebf\u7a0b\uff09\u2192 \u67e5\u770b\u9519\u8bef\u65e5\u5fd7\uff08\/var\/log\/mysqld.log\uff09\u2192 \u7ed3\u5408\u590d\u5236\u6a21\u5f0f\uff08GTID \/ \u4f4d\u70b9\uff09\u9009\u62e9\u89e3\u51b3\u65b9\u6848<\/strong>\u3002\u65e5\u5e38\u8fd0\u7ef4\u4e2d\uff0c\u5efa\u8bae\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>\u5b9a\u671f\u68c0\u67e5\u4ece\u5e93\u78c1\u76d8\u7a7a\u95f4\u3001\u590d\u5236\u5ef6\u8fdf\u3001GTID \u5b8c\u6574\u6027\uff1b<\/li>\n\n\n\n<li>\u4e3b\u5e93 Binlog \u4fdd\u7559\u65f6\u95f4\u8db3\u591f\u957f\uff08\u907f\u514d\u672a\u540c\u6b65\u5c31\u6e05\u7406\uff09\uff1b<\/li>\n\n\n\n<li>\u7981\u6b62\u624b\u52a8\u4fee\u6539\u4ece\u5e93\u6570\u636e\uff08\u51cf\u5c11\u6570\u636e\u51b2\u7a81\uff09\uff1b<\/li>\n\n\n\n<li>\u534a\u540c\u6b65\u590d\u5236\u4f18\u5148\u9009\u62e9 after_commit \u6a21\u5f0f\uff08\u5e73\u8861\u4e00\u81f4\u6027\u4e0e\u6027\u80fd\uff09\u3002<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"udb8c3904\">\u4ee5\u4e0a\u5c31\u662f\u672c\u6b21\u4e3b\u4ece\u590d\u5236\u6545\u969c\u6392\u67e5\u7684\u5168\u91cf\u5b9e\u6218\u603b\u7ed3\uff0c\u5e0c\u671b\u80fd\u5e2e\u5927\u5bb6\u5feb\u901f\u641e\u5b9a\u7ebf\u4e0a\u95ee\u9898\uff5e<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"udb8c3904\">\u6709\u7591\u95ee\u6b22\u8fce\u7559\u8a00\u4ea4\u6d41\uff0c\u6211\u4eec\u4e0b\u671f\u518d\u89c1\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u54c8\u55bd\u5404\u4f4d\u5c0f\u4f19\u4f34\uff0c\u6211\u662f\u4e91\u626c\uff5e \u5728 MySQL \u751f\u4ea7\u8fd0\u7ef4\u91cc\uff0c\u4e3b\u4ece\u590d\u5236\u662f\u6570\u636e\u5907\u4efd\u3001\u8bfb\u5199\u5206\u79bb\u3001\u9ad8\u53ef\u7528\u67b6\u6784\u7684\u6838\u5fc3\uff0c\u4f46\u914d\u7f6e<\/p>\n<div class=\"more-link\">\n\t\t\t\t <a href=\"https:\/\/yunyanglib.cn\/?p=809\" class=\"link-btn theme-btn\"><span>Read More <\/span> <i class=\"fa fa-caret-right\"><\/i><\/a>\n\t\t\t<\/div>\n","protected":false},"author":1,"featured_media":828,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[16],"class_list":["post-809","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=809"}],"version-history":[{"count":11,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/809\/revisions"}],"predecessor-version":[{"id":831,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/809\/revisions\/831"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/media\/828"}],"wp:attachment":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}