Differences

This shows you the differences between two versions of the page.

--- mysql:changemasters [2015/06/23 14:53] – rlunaro
+++ mysql:changemasters [2022/12/02 21:02] (current) – external edit 127.0.0.1
@@ Line 18: / Line 18: @@
 </code>
+As it is said in the document, take note of the value of MASTER_LOG_FILE and MASTER_LOG_POS:
-This explains how to change the master of a running mysql database by the mechanism of:
+<code>
+head dump.sql -n80 | grep "MASTER_LOG_POS"
+</code>
-  - Create a new slave wihtout downtime
+In the slave, edit the config file:
-  - Stop the current master and start a new master (the last slave)
+<code>
+server-id               = 101
+binlog-format       = mixed
+log_bin                 = mysql-bin
+relay-log               = mysql-relay-bin
+log-slave-updates = 1
+read-only               = 1
+</code>
+Restart future-master.
+Import data:
+<code>
+mysql -h localhost -u root -p < .\20150623-total-dump-1114.sql
+</code>
+Now, in the future-master server we must execute:
+<code>
+CHANGE MASTER TO MASTER_HOST='MASTER',MASTER_USER='replicant',MASTER_PASSWORD='<<slave-server-password>>', MASTER_LOG_FILE='<<value from above>>', MASTER_LOG_POS=<<value from above>>;
+START SLAVE;
+</code>
+The server future-master will begin to replicate the pending changes from the master server. To check
+out the pending tasks it has:
+<code>
+show slave status\G
+</code>
+<WRAP center round important 60%>
+It's important that that \G must be written as-is: an uppercase G and no space between
+the \ and the last character of the command, because otherwise it won't be understood.
+</WRAP>
+The synchronization will be finished when --for instance-- this value shows this:
+<code>
+Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
+</code>
+I've compared the value ''Read_Master_Log_Pos'' with other slave just to
+make sure all the changes were updated.
+===== Second: make the slave to change the master =====
+We have this:
+{{ :mysql:20150623_mysql_step_1.png?200 |}}
+And now we want this:
+{{:mysql:20150623_mysql_step_3.png?200|}}
+For that, we have to:
+  - Stop the master
+  - Check that both slaves are up-to-date
+  - issue a [[https://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html|change master to]] command to the old slave
+==== Stop the master ====
+Nothing new under the sun:
+<code>
+/etc/rc.d/mysql stop
+</code>
+or:
+<code>
+net stop mysql
+</code>
+==== Check that both slaves are up-to-date ====
+Among the information that ''show slave status\G'' displays, I've chosen Read_Master_Log_Pos to make sure
+both database servers are in the same position.
+==== Create (if it does not exist) a replication user in future-master ====
+<code>
+grant replication slave, replication client on *.* to YOUR-REPLICATION-USERNAME identified by 'YOUR-REPLICATION-PASSWORD';
+</code>
+==== Check master status an take note of the master log file and master log position ====
+We have to issue a ''show master status'' command to get the file and log position of our new master:
+<code>
+mysql> show master status\G
+*************************** 1. row ***************************
+             File: YOUR-MASTER-LOG-FILE
+         Position: YOUR-MASTER-LOG-POS
+     Binlog_Do_DB:
+ Binlog_Ignore_DB:
+Executed_Gtid_Set:
+row in set (0.00 sec)
+</code>
+With this two values, YOUR-MASTER-LOG-FILE and YOUR-MASTER-LOG-POS, we can move to the
+slave and issue a change master to command.
+==== Issue a change master command ====
+In the slave server I've issued a ''change master to'' command:
+<code>
+stop slave;
+change master to master_host='future-master',
+master_port=3306,
+master_user='YOUR-REPLICATION-USERNAME',
+master_password='YOUR-REPLICATION-PASSWORD',
+master_log_file = 'YOUR-MASTER-LOG-FILE',
+master_log_pos = YOUR-MASTER-LOG-POSITION;
+start slave;
+</code>
+And I've stopped and started the slave.
+===== Final check =====
+You can double check that your slave is working with the new configuration just by the method of looking
+one change made in the new master.
-https://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html