{"id":2893,"date":"2016-09-25T18:22:55","date_gmt":"2016-09-25T10:22:55","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2893"},"modified":"2016-09-25T18:22:55","modified_gmt":"2016-09-25T10:22:55","slug":"mysql%e4%b8%bb%e4%bb%8e%e7%8e%af%e5%a2%83%e9%87%8d%e5%bb%ba","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2893.html","title":{"rendered":"MySQL\u4e3b\u4ece\u73af\u5883\u91cd\u5efa"},"content":{"rendered":"<p>=Start=<\/p>\n<h5>\u7f18\u7531\uff1a<\/h5>\n<p>\u4e4b\u524d\u8bb0\u5f55\u4e86\u4e00\u7bc7\u300c<a href=\"http:\/\/ixyzero.com\/blog\/archives\/2843.html\" target=\"_blank\">MySQL\u4e3b\u4ece\u73af\u5883\u642d\u5efa<\/a>\u300d\u7684\u6587\u7ae0\uff0c\u5e76\u5728\u6b64\u8fc7\u7a0b\u4e2d\u5b9e\u9645\u642d\u5efa\u4e86\u4e00\u5957MySQL\u4e3b\u4ece\u73af\u5883\uff0c\u4f46\u662f\u540e\u6765\u5b9e\u9645\u4f7f\u7528\u7684\u65f6\u5019\u53d1\u73b0\u5b58\u5728\u51e0\u4e2a\u95ee\u9898\uff08\u4e3b\u8981\u662f\u4e3b\u4ece\u6570\u636e\u4e0d\u4e00\u81f4\uff0c\u6839\u636e\u6bd4\u8f83Slave\u4e0a\u7684Master_Log_File\u3001Read_Master_Log_Pos\/Exec_Master_Log_Pos\u51e0\u4e2a\u9009\u9879\u548cMaster\u4e0a\u5bf9\u5e94\u7684File\u3001Position\u5f97\u51fa\u8be5\u7ed3\u8bba\uff09\uff0c\u6240\u4ee5\u9700\u8981\u91cd\u65b0\u642d\u5efa\u4e3b\u4ece\uff0c\u5728\u6b64\u8bb0\u5f55\u4e00\u4e0b\u5927\u4f53\u8fc7\u7a0b\u548c\u5176\u4e2d\u9047\u5230\u7684\u95ee\u9898\u3002<\/p>\n<h5>\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n<p>\u91cd\u5efa\u4e3b\u4ece\u7684\u6b65\u9aa4<\/p>\n<table class=\"confluenceTable wrapped\">\n<colgroup>\n<col \/>\n<col \/>\n<col \/><\/colgroup>\n<thead>\n<tr>\n<th class=\"confluenceTh\" colspan=\"1\">\n<div class=\"tablesorter-header-inner\">Step<\/div>\n<\/th>\n<th class=\"confluenceTh\">\n<div class=\"tablesorter-header-inner\">Master<\/div>\n<\/th>\n<th class=\"confluenceTh\">\n<div class=\"tablesorter-header-inner\">Slave<\/div>\n<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">1<\/td>\n<td class=\"confluenceTd\">\u91cd\u7f6eMaster\u72b6\u6001\uff0c\u5e76\u9501\u8868\u51c6\u5907\u6570\u636e\u5e93\u5907\u4efd\u64cd\u4f5c\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; RESET MASTER;\nmysql&gt; FLUSH TABLES WITH READ LOCK;\nmysql&gt; SHOW MASTER STATUS; #\u6ce8\u610f\u8bb0\u5f55\u6b64\u5904File\u548cPosition\u7684\u503c<\/pre>\n<\/td>\n<td class=\"confluenceTd\"><\/td>\n<\/tr>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">2<\/td>\n<td class=\"confluenceTd\">\u5728\u4e0d\u65ad\u5f00\u4e0a\u9762MySQL\u8fde\u63a5\u7684\u60c5\u51b5\u4e0b\u6267\u884c\u4e0b\u9762\u7684shell\u547d\u4ee4\u8fdb\u884c\u6570\u636e\u5e93\u5907\u4efd\uff1a<\/p>\n<pre class=\"lang:default decode:true\">shell&gt; mysqldump -uroot -p db_name &gt; db_name.sql  #\u5907\u4efd\u6307\u5b9a\u6570\u636e\u5e93\n\u6216\nshell&gt; mysqldump -uroot -p db_name --master-data &gt; db_name.sql\n\u6216\nshell&gt; mysqldump -uroot -p --opt --single-transaction --comments --hex-blob --dump-date --no-autocommit --all-databases &gt; all_db.sql  #\u5907\u4efd\u6240\u6709\u6570\u636e\u5e93<\/pre>\n<p>\u5728\u6570\u636e\u5907\u4efd\u6210\u529f\u7684\u60c5\u51b5\u4e0b\uff0c\u89e3\u9664\u9501\u8868\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; UNLOCK TABLES;<\/pre>\n<\/td>\n<td class=\"confluenceTd\"><\/td>\n<\/tr>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">3<\/td>\n<td class=\"confluenceTd\">\u5c06\u5907\u4efd\u7684\u6570\u636e\u53d1\u9001\u5230\u8fdc\u7aefSlave\u6240\u5728\u670d\u52a1\u5668\uff1a<\/p>\n<pre class=\"lang:default decode:true\">shell&gt; scp -C db_name.sql user@slave_ip:\/tmp\/<\/pre>\n<\/td>\n<td class=\"confluenceTd\"><\/td>\n<\/tr>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">4<\/td>\n<td class=\"confluenceTd\"><\/td>\n<td class=\"confluenceTd\">\u505c\u6389\u8fd8\u5728\u8fd0\u884c\u4e2d\u7684\u5907\u4efd\u64cd\u4f5c\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; STOP SLAVE;<\/pre>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">5<\/td>\n<td class=\"confluenceTd\"><\/td>\n<td class=\"confluenceTd\">\u5c06\u521a\u624d\u5728Master\u4e0a\u5907\u4efd\u7684\u6570\u636e\u5bfc\u5165Slave\u5bf9\u5e94\u7684\u6570\u636e\u5e93(<span style=\"color: #0000ff;\">\u5982\u679c\u8be5\u6570\u636e\u5e93\u4e0d\u5b58\u5728\u7684\u8bdd\u9700\u8981\u65b0\u5efa<\/span>)\uff1a<\/p>\n<pre class=\"lang:default decode:true\">shell&gt; mysql -uroot -p db_name &lt; db_name.sql<\/pre>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">6<\/td>\n<td class=\"confluenceTd\"><\/td>\n<td class=\"confluenceTd\">\u5728Slave\u4e0a\u4fee\u6539Master\u7684\u4e00\u4e9b\u4fe1\u606f\uff1a<\/p>\n<pre class=\"lang:default decode:true \">mysql&gt; RESET SLAVE;\nmysql&gt; CHANGE MASTER TO\n       MASTER_HOST='&lt;MASTER_HOST&gt;',\n       MASTER_USER='&lt;SLAVE_USER&gt;',\n       MASTER_PASSWORD='&lt;SLAVE_PASSWORD&gt;',\n       MASTER_LOG_FILE='&lt;Relay_Master_Log_File&gt;',\n       MASTER_LOG_POS=&lt;Exec_Master_Log_Pos&gt;;<\/pre>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"confluenceTd\" colspan=\"1\">7<\/td>\n<td class=\"confluenceTd\" colspan=\"1\"><\/td>\n<td class=\"confluenceTd\" colspan=\"1\">\u542f\u52a8Slave &amp; \u67e5\u770bSlave\u72b6\u6001\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; START SLAVE;\nmysql&gt; SHOW SLAVE STATUS\\G<\/pre>\n<p>\u5f53\u770b\u5230\u4e0b\u9762\u4e24\u4e2a\u9009\u9879\u7684\u503c\u4e3a &#8216;Yes&#8217; \u65f6\u8868\u660eSlave\u542f\u52a8\u6210\u529f\uff1a<br \/>\nSlave_IO_Running: Yes<br \/>\nSlave_SQL_Running: Yes<\/p>\n<p>\u5f53\u7136\uff0c\u4e5f\u9700\u8981\u6ce8\u610f\u4e00\u4e0b\u4ee5\u4e0b\u51e0\u4e2a\u9009\u9879\u548cMaster\u662f\u5426\u4e00\u81f4\uff1a<br \/>\nMaster_Log_File: mysql-bin.000001<br \/>\nRead_Master_Log_Pos: 107<br \/>\nExec_Master_Log_Pos: 107<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h5>\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/2366018\/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/2366018\/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o<\/a><\/li>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/change-master-to.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/change-master-to.html<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2008\/07\/07\/how-show-slave-status-relates-to-change-master-to\/\">https:\/\/www.percona.com\/blog\/2008\/07\/07\/how-show-slave-status-relates-to-change-master-to\/<\/a><\/li>\n<li><a href=\"http:\/\/dba.stackexchange.com\/questions\/30782\/update-mysql-slave-when-changing-the-hostname-ip-of-master\">http:\/\/dba.stackexchange.com\/questions\/30782\/update-mysql-slave-when-changing-the-hostname-ip-of-master<\/a><\/li>\n<\/ul>\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u4e4b\u524d\u8bb0\u5f55\u4e86\u4e00\u7bc7\u300cMySQL\u4e3b\u4ece\u73af\u5883\u642d\u5efa\u300d\u7684\u6587\u7ae0\uff0c\u5e76\u5728\u6b64\u8fc7\u7a0b\u4e2d\u5b9e\u9645\u642d\u5efa\u4e86\u4e00\u5957MySQL\u4e3b [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23],"tags":[16,658],"class_list":["post-2893","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-mysql","tag-replication"],"views":3674,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2893"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2893\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}