{"id":1174,"date":"2019-05-23T15:24:18","date_gmt":"2019-05-23T20:24:18","guid":{"rendered":"http:\/\/xfloyd.net\/blog\/?p=1174"},"modified":"2019-05-23T15:24:18","modified_gmt":"2019-05-23T20:24:18","slug":"mysql-replication-and-ip-address-change","status":"publish","type":"post","link":"https:\/\/xfloyd.net\/blog\/?p=1174","title":{"rendered":"MySQL replication and IP address change"},"content":{"rendered":"\n<div class=\"item-page\" itemscope=\"\" itemtype=\"https:\/\/schema.org\/Article\">\n\t<meta itemprop=\"inLanguage\" content=\"en-GB\">\n\t\n\t\t\n\t\t\t<div class=\"page-header\">\n\t\t\t\t\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\n\t\t\n\t\t\t\t\t\t<dl class=\"article-info muted\">\n\n\t\t\n\t\t\t<dt class=\"article-info-term\">\n\t\t\t\t\t\t\t\t\tDetails\t\t\t\t\t\t\t<\/dt>\n\n\t\t\t\n\t\t\t\n\t\t\t\n\t\t\t\n\t\t\t\n\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<dd class=\"create\">\n\t\t\t\t\t<span class=\"icon-calendar\" aria-hidden=\"true\"><\/span>\n\t\t\t\t\t<time datetime=\"2013-09-14T16:47:23-04:00\" itemprop=\"dateCreated\">\n\t\t\t\t\t\tCreated: 14 September 2013\t\t\t\t\t<\/time>\n\t\t\t<\/dd>\t\t\t\n\t\t\t\n\t\t\t\t\t\t<\/dl>\n\t\n\t\n\t\t\n\t\t\t\t\t\t\t\t<div itemprop=\"articleBody\">\n\t\t<p>In the recent past, for a variety of reasons, I occasionally had to change the IP address of a MySQL master in a replicated environment.<\/p>\n<p>It was a major annoyance that it was not sufficient to just change the IP address in the MySQL configuration file for slaves and then restart the server. Instead, I basically had to start from scratch, setting up replication anew. There had to be an easier way.<\/p>\n<p>Well, <a href=\"http:\/\/linuxsysadminblog.com\/2008\/07\/changing-the-mysql-replication-master-host\/\">there is<\/a>. What you basically need to do is ensure that the slave is fully caught up (to the point when it was disconnected from the master). Then, stop the slave, adjust slave parameters, and restart.<\/p>\n<p>Which parameters, you ask? Well, the address of the master, the name of the master log file, and the position within the file. The latter two can be obtained by viewing the slave status:<\/p>\n<pre style=\"margin-left: 3em; margin-right: 3em; background-color: #eee;\">mysql&gt; SHOW SLAVE STATUS\\G<br>*************************** 1. row ***************************<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Slave_IO_State: Reconnecting after a failed master event read<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_Host: 10.0.0.1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_User: replicate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_Port: 3306<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Connect_Retry: 60<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_Log_File: mysql-bin.000005<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <strong style=\"color: #f00;\">Read_Master_Log_Pos: 11622542<\/strong><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Relay_Log_File: mysqld-relay-bin.000074<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Relay_Log_Pos: 11622679<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <strong style=\"color: #f00;\">Relay_Master_Log_File: mysql-bin.000005<\/strong><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Slave_IO_Running: No<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Slave_SQL_Running: Yes<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Replicate_Do_DB: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Replicate_Ignore_DB: mythconverg<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Replicate_Do_Table: <br>&nbsp;&nbsp;&nbsp;&nbsp; Replicate_Ignore_Table: <br>&nbsp;&nbsp;&nbsp; Replicate_Wild_Do_Table: <br>Replicate_Wild_Ignore_Table: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Last_Errno: 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Last_Error: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Skip_Counter: 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Exec_Master_Log_Pos: 11622542<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Relay_Log_Space: 11622679<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Until_Condition: None<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Until_Log_File: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Until_Log_Pos: 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_SSL_Allowed: No<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_SSL_CA_File: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_SSL_CA_Path: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_SSL_Cert: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_SSL_Cipher: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Master_SSL_Key: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Seconds_Behind_Master: NULL<br>1 row in set (0.00 sec)<\/pre>\n<p>The next step is to stop the slave:<\/p>\n<pre style=\"margin-left: 3em; margin-right: 3em; background-color: #eee;\">mysql&gt; STOP SLAVE;<\/pre>\n<p>Next comes the aforementioned change of replication parameters, which can be accomplished by issuing the following command:<\/p>\n<pre style=\"margin-left: 3em; margin-right: 3em; background-color: #eee;\">mysql&gt; CHANGE MASTER TO MASTER_HOST='192.168.1.1',<br>    MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=11622542;<\/pre>\n<p>The value of <tt>MASTER_HOST<\/tt> is, of course, the new IP address of the master. The <tt>MASTER_LOG_FILE<\/tt> and <tt>MASTER_LOG_POS<\/tt> values come from the output <tt>SHOW SLAVE STATUS<\/tt> command above.<\/p>\n<p>Finally, restart the slave:<\/p>\n<pre style=\"margin-left: 3em; margin-right: 3em; background-color: #eee;\">mysql&gt; START SLAVE;<\/pre>\n<p>That&#8217;s it. Do verify though that the slave is working properly by reissuing the <tt>SHOW SLAVE STATUS<\/tt> command a few times, watching as the slave catches up with the master.<\/p>\t<\/div>\n\n\t\n\t\t\t\t\t\t\t<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Details Created: 14 September 2013 In the recent past, for a variety of reasons, I occasionally had to change the IP address of a MySQL master in a replicated environment. It was a major annoyance that it was not sufficient to just change the IP address in the MySQL configuration file for slaves and then [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1174"}],"collection":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1174"}],"version-history":[{"count":2,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1174\/revisions"}],"predecessor-version":[{"id":1176,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1174\/revisions\/1176"}],"wp:attachment":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}