\u7b2c\u4e00\u6b65 - \u914d\u7f6e\u4e3b\u6570\u636e\u5e93<\/strong><\/span><\/div>\n
\u6253\u5f00\u4e3b\u670d\u52a1\u5668\u4e0a\u7684mysql\u914d\u7f6e\u6587\u4ef6\u3002<\/p>\n
\r\nsudo nano \/etc\/mysql\/my.cnf\r\n\r\n\u4e00\u65e6\u8fdb\u5165\u8be5\u6587\u4ef6\uff0c\u6211\u4eec\u9700\u8981\u8fdb\u884c\u4e00\u4e9b\u66f4\u6539\u3002\r\n\r\n\u7b2c\u4e00\u6b65\u662f\u627e\u5230\u5982\u4e0b\u6240\u793a\u7684\u90e8\u5206\uff0c\u5c06\u670d\u52a1\u5668\u7ed1\u5b9a\u5230\u672c\u5730\u4e3b\u673a\uff1a\r\n\r\nbind-address = 127.0.0.1\r\n\u5c06\u6807\u51c6IP\u5730\u5740\u66ff\u6362\u4e3a\u670d\u52a1\u5668\u7684IP\u5730\u5740\u3002\r\n\r\nbind-address = 12.34.56.789 \r\n\u4e0b\u4e00\u4e2a\u914d\u7f6e\u66f4\u6539\u662f\u6307\u4f4d\u4e8e[mysqld]\u90e8\u5206\u4e2d\u7684server-id\u3002 \u60a8\u53ef\u4ee5\u4e3a\u6b64\u70b9\u9009\u62e9\u4efb\u4f55\u6570\u5b57\uff08\u53ef\u80fd\u66f4\u5bb9\u6613\u4ece1\u5f00\u59cb\uff09\uff0c\u4f46\u8be5\u6570\u5b57\u5fc5\u987b\u662f\u552f\u4e00\u7684\uff0c\u5e76\u4e14\u4e0d\u80fd\u4e0e\u590d\u5236\u7ec4\u4e2d\u7684\u4efb\u4f55\u5176\u4ed6\u670d\u52a1\u5668\u6807\u8bc6\u5339\u914d\u3002 \u6211\u8981\u53bb\u6253\u7535\u8bdd\u8fd9\u4e2a1\u3002\r\n\r\n\u786e\u4fdd\u6b64\u884c\u5df2\u53d6\u6d88\u6ce8\u91ca\u3002\r\n\r\nserver-id = 1\r\n\u79fb\u52a8\u5230log_bin\u884c\u3002 \u8fd9\u662f\u4fdd\u5b58\u590d\u5236\u7684\u771f\u5b9e\u7ec6\u8282\u7684\u5730\u65b9\u3002 \u4ece\u5c5e\u7a0b\u5e8f\u5c06\u590d\u5236\u5728\u65e5\u5fd7\u4e2d\u6ce8\u518c\u7684\u6240\u6709\u66f4\u6539\u3002 \u5bf9\u4e8e\u8fd9\u4e00\u6b65\uff0c\u6211\u4eec\u53ea\u9700\u8981\u53d6\u6d88\u6ce8\u91ca\u5f15\u7528log_bin\u7684\u884c\uff1a\r\n\r\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\r\n\u6700\u540e\uff0c\u6211\u4eec\u9700\u8981\u6307\u5b9a\u5c06\u5728\u4ece\u670d\u52a1\u5668\u4e0a\u590d\u5236\u7684\u6570\u636e\u5e93\u3002 \u60a8\u53ef\u4ee5\u901a\u8fc7\u4e3a\u6240\u6709\u60a8\u9700\u8981\u7684\u6570\u636e\u5e93\u91cd\u590d\u6b64\u884c\uff0c\u5305\u62ec\u591a\u4e2a\u6570\u636e\u5e93\u3002\r\n\r\nbinlog_do_db = newdatabase\r\n\u5b8c\u6210\u6240\u6709\u66f4\u6539\u540e\uff0c\u7ee7\u7eed\u4fdd\u5b58\u5e76\u9000\u51fa\u914d\u7f6e\u6587\u4ef6\u3002\r\n\r\n\u5237\u65b0MySQL\u3002\r\n<\/pre>\n
\r\nsudo service mysql restart\r\n<\/pre>\n
\u63a5\u4e0b\u6765\u7684\u6b65\u9aa4\u5c06\u5728MySQL shell\u4e2d\u8fdb\u884c\uff0c\u672c\u8eab\u3002<\/p>\n
\u6253\u5f00MySQL shell\u3002<\/p>\n
\r\nmysql -u root -p\r\n<\/pre>\n
\u6211\u4eec\u9700\u8981\u7ed9\u4ece\u5c5e\u6743\u9650\u3002 \u60a8\u53ef\u4ee5\u4f7f\u7528\u6b64\u884c\u547d\u540d\u60a8\u7684\u4ece\u5c5e\u5e76\u8bbe\u7f6e\u5176\u5bc6\u7801\u3002 \u547d\u4ee4\u5e94\u91c7\u7528\u4ee5\u4e0b\u683c\u5f0f\uff1a<\/p>\n
\r\nGRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';\r\n<\/pre>\n
\r\nFLUSH PRIVILEGES;\r\n<\/pre>\n
\u4e0b\u4e00\u90e8\u5206\u662f\u6709\u70b9bit\u3002\u3002 \u4e3a\u4e86\u5b8c\u6210\u4efb\u52a1\uff0c\u4f60\u9700\u8981\u5728\u9664\u4e86\u4f60\u5df2\u7ecf\u4f7f\u7528\u4e86\u51e0\u6b65\u5012\u884c\u4e00\u6253\u5f00\u4e00\u4e2a\u65b0\u7a97\u53e3\u6216\u6807\u7b7e \u3002<\/p>\n
\u5728\u5f53\u524d\u6807\u7b7e\u9875\u5207\u6362\u5230\u201cnewdatabase\u201d\u3002<\/p>\n
\r\nUSE newdatabase;\r\n<\/pre>\n
\u63a5\u4e0b\u6765\uff0c\u9501\u5b9a\u6570\u636e\u5e93\u4ee5\u9632\u6b62\u4efb\u4f55\u65b0\u7684\u66f4\u6539\uff1a<\/p>\n
\r\nFLUSH TABLES WITH READ LOCK;\r\n<\/pre>\n
\u7136\u540e\u8f93\u5165\uff1a<\/p>\n
\r\nSHOW MASTER STATUS;\r\n<\/pre>\n
\u4f60\u4f1a\u770b\u5230\u4e00\u4e2a\u8868\u5e94\u8be5\u770b\u8d77\u6765\u50cf\u8fd9\u6837\uff1a<\/p>\n
\r\nmysql> SHOW MASTER STATUS;\r\n+------------------+----------+--------------+------------------+\r\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000001 | 107 | newdatabase | |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
\u8fd9\u662f\u4ece\u6570\u636e\u5e93\u5c06\u5f00\u59cb\u590d\u5236\u7684\u4f4d\u7f6e\u3002 \u8bb0\u5f55\u8fd9\u4e9b\u6570\u5b57\uff0c\u4ed6\u4eec\u5c06\u5728\u4ee5\u540e\u6709\u7528\u3002<\/p>\n
\u5982\u679c\u5728\u540c\u4e00\u4e2a\u7a97\u53e3\u4e2d\u8fdb\u884c\u4efb\u4f55\u65b0\u7684\u66f4\u6539\uff0c\u6570\u636e\u5e93\u5c06\u81ea\u52a8\u89e3\u9501\u3002 \u56e0\u6b64\uff0c\u60a8\u5e94\u8be5\u6253\u5f00\u65b0\u7684\u9009\u9879\u5361\u6216\u7a97\u53e3\uff0c\u7136\u540e\u7ee7\u7eed\u4e0b\u4e00\u6b65\u3002<\/p>\n
\u7ee7\u7eed\u6570\u636e\u5e93\u4ecd\u7136\u9501\u5b9a\uff0c\u5728\u65b0\u7a97\u53e3\u4e2d\u4f7f\u7528mysqldump\u5bfc\u51fa\u6570\u636e\u5e93\uff08\u786e\u4fdd\u60a8\u5728bash shell\u4e2d\u800c\u4e0d\u662f\u5728MySQL\u4e2d\u952e\u5165\u6b64\u547d\u4ee4\uff09\u3002<\/p>\n
\r\nmysqldump -u root -p --opt newdatabase > newdatabase.sql\r\n<\/pre>\n
\u73b0\u5728\uff0c\u8fd4\u56de\u5230\u60a8\u7684\u539f\u59cb\u7a97\u53e3\uff0c\u89e3\u9501\u6570\u636e\u5e93\uff08\u4f7f\u5b83\u4eec\u53ef\u5199\u5165\uff09\u3002 \u901a\u8fc7\u9000\u51fashell\u5b8c\u6210\u3002<\/p>\n
\r\nUNLOCK TABLES;\r\nQUIT;\r\n<\/pre>\n
\u73b0\u5728\u4f60\u5df2\u7ecf\u5b8c\u6210\u4e86master\u6570\u636e\u5e93\u7684\u914d\u7f6e\u3002<\/p>\n
\u7b2c\u4e8c\u6b65 - \u914d\u7f6e\u4ece\u6570\u636e\u5e93<\/strong><\/span><\/div>\n
\u914d\u7f6e\u4e3b\u6570\u636e\u5e93\u4e4b\u540e\u3002 \u4f60\u53ef\u4ee5\u628a\u5b83\u653e\u5728\u4e00\u8fb9\uff0c\u6211\u4eec\u73b0\u5728\u5c06\u5f00\u59cb\u914d\u7f6e\u4ece\u6570\u636e\u5e93\u3002<\/p>\n
\u767b\u5f55\u5230\u4ece\u670d\u52a1\u5668\uff0c\u6253\u5f00MySQL shell\u5e76\u521b\u5efa\u8981\u4ece\u4e3b\u670d\u52a1\u5668\u590d\u5236\u7684\u65b0\u6570\u636e\u5e93\uff08\u7136\u540e\u9000\u51fa\uff09\uff1a<\/p>\n
\r\nCREATE DATABASE newdatabase;\r\nEXIT;\r\n\u5bfc\u5165\u5148\u524d\u4ece\u4e3b\u6570\u636e\u5e93\u5bfc\u51fa\u7684\u6570\u636e\u5e93\u3002\r\n\r\nmysql -u root -p newdatabase \r\n\r\nNow we need to configure the slave configuration in the same way as we did the master:\r\n\r\nsudo nano \/etc\/mysql\/my.cnf\r\n\r\nWe have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it\u2019s something different.\r\n\r\nserver-id = 2\r\n\r\nFollowing that, make sure that your have the following three criteria appropriately filled out:\r\n\r\nrelay-log = \/var\/log\/mysql\/mysql-relay-bin.log\r\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\r\nbinlog_do_db = newdatabase\r\n\r\nYou will need to add in the relay-log line: it is not there by default.\r\nOnce you have made all of the necessary changes, save and exit out of the slave configuration file.\r\n\r\nRestart MySQL once again:\r\n\r\nsudo service mysql restart\r\n\r\nThe next step is to enable the replication from within the MySQL shell.\r\n\r\nOpen up the the MySQL shell once again and type in the following details, replacing the values to match your information:\r\n\r\nCHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;\r\n\r\nThis command accomplishes several things at the same time:\r\n\r\nIt designates the current server as the slave of our master server. \r\nIt provides the server the correct login credentials\r\nLast of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.\r\n\r\n\r\nWith that\u2014you have configured a master and slave server. \r\n\r\nActivate the slave server:\r\n\r\nSTART SLAVE;\r\n\r\nYou be able to see the details of the slave replication by typing in this command. The \\G rearranges the text to make it more readable.\r\n\r\nSHOW SLAVE STATUS\\G \r\n\r\nIf there is an issue in connecting, you can try starting slave with a command to skip over it:\r\n\r\nSET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; \r\n\r\nAll done. \r\n\r\nSee More\r\n\r\nMySQL replication has a lot different options, and this was just a brief overview.\r\n\r\nIf you have any further questions about the specific capabilities of MySQL, feel free to post your questions in our Q&A Forum and we\u2019ll be happy to answer them. \r\n\r\nBy Etel Sverdlov\r\n<\/pre>\n
\u73b0\u5728\u6211\u4eec\u9700\u8981\u4ee5\u4e0e\u6211\u4eec\u505a\u4e3b\u673a\u76f8\u540c\u7684\u65b9\u5f0f\u914d\u7f6e\u4ece\u673a\u914d\u7f6e\uff1a<\/p>\n
\r\nsudo nano \/etc\/mysql\/my.cnf\r\n\r\n\u6211\u4eec\u5fc5\u987b\u786e\u4fdd\u6211\u4eec\u5728\u8fd9\u4e2a\u914d\u7f6e\u4e2d\u8bbe\u7f6e\u4e86\u4e00\u4e9b\u4e1c\u897f\u3002 \u7b2c\u4e00\u4e2a\u662f\u670d\u52a1\u5668\u6807\u8bc6\u3002 \u8fd9\u4e2a\u6570\u5b57\uff0c\u5982\u524d\u6240\u8ff0\u9700\u8981\u662f\u552f\u4e00\u7684\u3002 \u56e0\u4e3a\u5b83\u88ab\u8bbe\u7f6e\u4e3a\u9ed8\u8ba4\uff08\u4ecd\u7136\u662f1\uff09\uff0c\u4e00\u5b9a\u8981\u6539\u53d8\u5b83\u7684\u4e1c\u897f\u4e0d\u540c\u3002\r\n\r\nserver-id = 2\r\n\u4e4b\u540e\uff0c\u8bf7\u786e\u4fdd\u60a8\u5df2\u6b63\u786e\u586b\u5199\u4ee5\u4e0b\u4e09\u4e2a\u6761\u4ef6\uff1a\r\n\r\nrelay-log = \/var\/log\/mysql\/mysql-relay-bin.log\r\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\r\nbinlog_do_db = newdatabase\r\n<\/pre>\n
\u60a8\u5c06\u9700\u8981\u5728\u4e2d\u7ee7\u65e5\u5fd7\u884c\u4e2d\u6dfb\u52a0\uff1a\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u4e0d\u5b58\u5728\u3002 \u4e00\u65e6\u5b8c\u6210\u6240\u6709\u5fc5\u8981\u7684\u66f4\u6539\uff0c\u4fdd\u5b58\u5e76\u9000\u51fa\u4ece\u914d\u7f6e\u6587\u4ef6\u3002<\/p>\n
\u518d\u6b21\u91cd\u65b0\u542f\u52a8MySQL\uff1a<\/p>\n
\r\nsudo service mysql restart\r\n<\/pre>\n
\u4e0b\u4e00\u6b65\u662f\u5728MySQL shell\u4e2d\u542f\u7528\u590d\u5236\u3002<\/p>\n
\u518d\u6b21\u6253\u5f00MySQL shell\uff0c\u8f93\u5165\u4ee5\u4e0b\u8be6\u7ec6\u4fe1\u606f\uff0c\u66ff\u6362\u503c\u4ee5\u5339\u914d\u60a8\u7684\u4fe1\u606f\uff1a<\/p>\n
\r\nCHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;\r\n<\/pre>\n
\u6b64\u547d\u4ee4\u540c\u65f6\u5b8c\u6210\u51e0\u4e2a\u4e8b\u60c5\uff1a<\/p>\n
\u5b83\u5c06\u5f53\u524d\u670d\u52a1\u5668\u6307\u5b9a\u4e3a\u6211\u4eec\u7684\u4e3b\u670d\u52a1\u5668\u7684\u4ece\u5c5e\u3002<\/p>\n
\u5b83\u4e3a\u670d\u52a1\u5668\u63d0\u4f9b\u6b63\u786e\u7684\u767b\u5f55\u51ed\u636e<\/p>\n
\u6700\u540e\uff0c\u5b83\u8ba9\u4ece\u670d\u52a1\u5668\u77e5\u9053\u4ece\u54ea\u91cc\u5f00\u59cb\u590d\u5236; \u4e3b\u65e5\u5fd7\u6587\u4ef6\u548c\u65e5\u5fd7\u4f4d\u7f6e\u6765\u81ea\u6211\u4eec\u4e4b\u524d\u5199\u4e0b\u7684\u6570\u5b57\u3002<\/p>\n
\u8fd9\u6837\uff0c\u60a8\u5df2\u7ecf\u914d\u7f6e\u4e86\u4e3b\u670d\u52a1\u5668\u548c\u4ece\u670d\u52a1\u5668\u3002<\/p>\n
\u6fc0\u6d3b\u4ece\u670d\u52a1\u5668\uff1a<\/p>\n
\r\nSTART SLAVE;\r\n<\/pre>\n
\u901a\u8fc7\u952e\u5165\u6b64\u547d\u4ee4\uff0c\u60a8\u53ef\u4ee5\u770b\u5230\u4ece\u590d\u5236\u7684\u8be6\u7ec6\u4fe1\u606f\u3002 \\ G\u91cd\u65b0\u6392\u5217\u6587\u672c\uff0c\u4f7f\u5176\u66f4\u6613\u8bfb\u3002<\/p>\n
\r\nSHOW SLAVE STATUS\\G\r\n<\/pre>\n
\u5982\u679c\u5728\u8fde\u63a5\u4e2d\u5b58\u5728\u95ee\u9898\uff0c\u53ef\u4ee5\u5c1d\u8bd5\u4f7f\u7528\u547d\u4ee4\u542f\u52a8\u4ece\u5668\u4ef6\u4ee5\u8df3\u8fc7\u5b83\uff1a<\/p>\n
\r\nSET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; \r\n<\/pre>\n
\u5168\u505a\u5b8c\u4e86\u3002<\/p>\n
\n
\u539f\u6587\u6765\u81ea\uff1ahttps:\/\/www.howtoing.com\/how-to-set-up-master-slave-replication-in-mysql<\/a><\/p>\n
\u672c\u6587\u5730\u5740\uff1a https:\/\/lrxjmw.cn\/master-slave-mysql.html<\/a>\u7f16\u8f91\uff1a\u5434\u5411\u5e73\uff0c\u5ba1\u6838\u5458\uff1a\u9004\u589e\u5b9d<\/span><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
\u5bfc\u8bfb MySQL\u590d\u5236\u662f\u4e00\u4e2a\u5141\u8bb8\u60a8\u901a\u8fc7\u81ea\u52a8\u4ece\u4e3b\u6570\u636e\u5e93\u590d\u5236\u5230\u4ece\u6570\u636e\u5e93\u6765\u8f7b\u677e\u7ef4\u62a4MySQL\u6570\u636e\u7684\u591a\u4e2a\u526f\u672c\u7684\u8fc7\u7a0b\u3002 \u8fd9 […]<\/p>\n","protected":false},"author":668,"featured_media":63812,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-107742","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/107742","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/668"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=107742"}],"version-history":[{"count":5,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/107742\/revisions"}],"predecessor-version":[{"id":293930,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/107742\/revisions\/293930"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/63812"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=107742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=107742"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=107742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}