MySQL replication – master migration

This cookbook explains how to move MySQL master server to another host. Scenario in short goes: dump out master databases, import data on the new master server and point slave to the new master server. If you follow this simple steps, master migration should proceed without problems.

1. Export databases from the master server
2. Stop replication on the slave
3. Import MySQL dumpfile to the new master server
4. Add grants for existing slave on the new master server
5. Point slave to the new master server
6. Start slave

1. Export databases from the master server
With mysqldump tool, databases from the MySQL server can be exported to the file.

bash> mysqldump [database_name] > /tmp/database.sql

2. Stop replication on the slave
After database export on the master server is finished, replication slave server can be stopped. Log to the MySQL console and stop slave.

mysql> stop slave;

3. Import MySQL dumpfile to the new master server
mysqldump tool will automatically include “create database” command to the SQL dumpfile so data import is very simple.

bash> mysql -p - uroot [database_name] < /tmp/database.sql

4. Add grants for existing slave on the new master server
Existing slave server should have permission to access new master server, so apply following command on the new master server.

mysql> GRANT REPLICATION SLAVE ON *.* TO '[replica_name]'@'[slave_ip]' IDENTIFIED BY '[password]';

5. Point slave to the new master server
Before CHANGE MASTER TO command, please read status on the new master server ...

mysql> show master status;
+------------------+----------+---------------------------------+------------------+
| File             | Position | Binlog_Do_DB                    | Binlog_Ignore_DB |
+------------------+----------+---------------------------------+------------------+
| mysql-bin.000001 |       98 | database1, database2, database3 |                  | 
+------------------+----------+---------------------------------+------------------+
1 row in set (0.00 sec)

... and set status values on the slave:

mysql> CHANGE MASTER TO
MASTER_HOST='[master_ip]',
MASTER_USER='[replica_name]',
MASTER_PASSWORD='[password]',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=98;

6. Start slave
Slave should connect to the new master server and begin reading from the master MySQL server.

mysql> start slave;