{"id":389,"date":"2020-06-04T17:44:55","date_gmt":"2020-06-04T09:44:55","guid":{"rendered":"http:\/\/www.leohe.net.cn\/?p=389"},"modified":"2020-06-05T14:49:37","modified_gmt":"2020-06-05T06:49:37","slug":"mysql%e4%b8%bb%e4%bb%8e%e6%a8%a1%e5%bc%8f%e4%b8%8b%e4%b8%8d%e5%81%9c%e6%9c%ba%e6%83%85%e5%86%b5%e4%b8%8b%ef%bc%8c%e6%b7%bb%e5%8a%a0%e4%bb%8e%e6%9c%ba%e5%99%a8","status":"publish","type":"post","link":"https:\/\/china-abc.leohe.net.cn\/?p=389","title":{"rendered":"mysql\u4e3b\u4ece\u6a21\u5f0f\u4e0b\u4e0d\u505c\u673a\u60c5\u51b5\u4e0b\uff0c\u6dfb\u52a0\u4ece\u673a\u5668"},"content":{"rendered":"

\u914d\u7f6e\u4ece\u6570\u636e\u5e93<\/strong>
\nvim \/etc\/my.cnf
\n\/\/\u6dfb\u52a0\u6216\u4fee\u6539\u4ee5\u4e0b\u4fe1\u606f\uff1a
\nserver-id=3 \/\/\u8bbe\u7f6e\u4ece\u5e93\u7684\u552f\u4e00\u6807\u8bc6\u7b26 \u4ece\u7684\u5fc5\u987b\u6bd4\u4e3b\u5c0f
\n\u4fdd\u5b58\u9000\u51fa<\/p>\n

\u91cd\u542fmysql\u670d\u52a1<\/strong>
\nservice mysql restart<\/p>\n

\u5907\u4efd\u4e3b\u6570\u636e\u5e93\uff1a(\u4e3b\u5e93\u64cd\u4f5c)<\/strong>
\nmysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -uroot -p --all-databases > \/opt\/all_2020-06-04.sql
\n\u53c2\u6570\u8bf4\u660e\uff1a
\n--skip-lock-tables: \u4e0d\u9501\u8868
\n--single-transaction: \u5bfc\u51fa\u5728\u4e00\u4e2a\u4e8b\u52a1\u4e0b\u8fdb\u884c\uff0c\u53ea\u5728innodb\u4e0b\u8d77\u4f5c\u7528
\n--flush-logs\uff1a\u65e5\u5fd7\u53ea\u5237\u65b0\u4e00\u6b21
\n--hex-blob:BINARY, VARBINARY, BLOB, BIT\u7c7b\u578b\u5bfc\u51fa\u4e3a\u5341\u516d\u8fdb\u5236\u3002
\n--master-data=2:\u628abinlog\u7684\u4f4d\u7f6e\u548c\u6587\u4ef6\u540d\u6dfb\u52a0\u5230\u8f93\u51fa\u4e2d,\u5982\u679c\u7b49\u4e8e1\uff0c\u5c06\u4f1a\u6253\u5370\u6210\u4e00\u4e2aCHANGE MASTER\u547d\u4ee4\uff1b\u5982\u679c\u7b49\u4e8e2\uff0c\u4f1a\u52a0\u4e0a\u6ce8\u91ca\u524d\u7f00\u3002
\n\u4f20\u8f93\u5230\u4ece\uff1a(\u4e3b\u5e93\u64cd\u4f5c)<\/strong>
\nscp \/opt\/all-2020-06-04.sql root@192.168.0.41:\/opt\/<\/p>\n

\u67e5\u770bbinlog\u6587\u4ef6\u540d\u548c\u4f4d\u7f6e\uff1a\uff1a(\u4ece\u5e93\u64cd\u4f5c)<\/strong>
\nhead \/opt\/all_2020-06-04.sql -n80 |grep 'MASTER_LOG*'
\n\u4f1a\u663e\u793a\u4ee5\u4e0b\u4fe1\u606f\uff1a
\n-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000040<\/strong>', MASTER_LOG_POS=154<\/strong>;
\n\u5176\u4e2dmysql-bin.000040 \u548c154\u4e3a\u9700\u8981\u7684\u4fe1\u606f\u3002<\/p>\n

\u4ece\u5bfc\u5165\u6240\u6709\u6570\u636e\uff1a(\u4ece\u5e93\u64cd\u4f5c)<\/strong>
\nmysql -uroot -p < \/opt\/all-2020-06-04.sql<\/p>\n

\u914d\u7f6e\u5e76\u542f\u52a8\u4e3b\u4ece\u590d\u5236<\/strong>
\nMySQL> change master to
\nmaster_host='192.168.0.40',
\nmaster_user='repl',
\nmaster_password='123456',
\nmaster_log_file='mysql-bin.000040',
\nmaster_log_pos=154;<\/p>\n

\u67e5\u770b\u4ece\u72b6\u6001\uff1a(\u4ece\u5e93\u64cd\u4f5c)<\/strong>
\nMySQL> show slave status\\G;
\n\u5982\u679c
\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes
\n\u4e24\u4e2ayes\u8868\u793a\u8bbe\u7f6e\u6210\u529f\uff0c\u5e76\u68c0\u6d4b \u6570\u636e\u662f\u4e0d\u662f\u4e00\u81f4<\/p>\n","protected":false},"excerpt":{"rendered":"

\u914d\u7f6e\u4ece\u6570\u636e\u5e93 vim \/etc\/my.cnf \/\/\u6dfb\u52a0\u6216\u4fee\u6539\u4ee5\u4e0b\u4fe1\u606f\uff1a server-id=3 \/\/\u8bbe\u7f6e\u4ece\u5e93\u7684 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"_links":{"self":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts\/389"}],"collection":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=389"}],"version-history":[{"count":2,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts\/389\/revisions"}],"predecessor-version":[{"id":391,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts\/389\/revisions\/391"}],"wp:attachment":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}