User Tools

Site Tools


mysql:changemasters

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
mysql:changemasters [2015/06/23 18:09] rlunaromysql:changemasters [2022/12/02 22:02] (current) – external edit 127.0.0.1
Line 43: Line 43:
 </code> </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>
  
-This explains how to change the master of a running mysql database by the mechanism of+<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>
  
-  Create a new slave wihtout downtime +The synchronization will be finished when --for instance-- this value shows this: 
-  Stop the current master and start a new master (the last slave)+
  
 +<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:
 +1 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 
  
mysql/changemasters.1435075788.txt.gz · Last modified: 2022/12/02 22:02 (external edit)