Migrating MySQL Server | Minimal Downtime | MySQL Replication

Migrate MySQL To A New Server/Region/Datacenter
Move MySQL To New Instance
Migrate Mysql Using Master Slave Approach

You might encounter certain situation when migration of MySQL to some other server/instance is necessary. For example, there is a scheduled maintenance of your MySQL instance and it needs a the instances to be restarted and you can't restart as connections will be dropped or you might want to resize/modify the instance but again restarting the instance will drop the connections or may be you are running MySQL in EC2-Classic and want to shift it to VPC . In all these scenario either MySQL instance needs to be restarted which will cause a downtime. So here the process to achieve the same using master/slave approach.

Approach:

• Make current MySQL server as Master.
• Take consistent snapshot of ec2-volume. (This will give master_log_file and master_log_pos)
• Create another MySQL server with the volume created by ec2-consistent-snapshot.
• Run the new MySQL server as slave using master_log_file and master_log_pos.
• Data will start syncing from master to slave based on master_log_file and master_log_pos.
• Once data is synced, point your application to point to slave by changing DNS entry.
• Writes will start coming on Slave. 
• Stope and Reset slave.
• Terminate Master MySQL.

Full Procedure:

Login to existing MySQL.
Edit the MySQL configuration file /etc/my.cnf by adding the following under [mysqld] and restart MySQL service sudo service mysqld resart:

server-id=1
log-bin=mysql-bin

Now take  consistent snapshot of volume using ec2-consistent-snapshot

ec2-consistent-snapshot --aws-access-key-id XXXXXXXXXXXXXXX --aws-secret-access-key XXXXXXXXXXXXXXXXXXXXXXXX --region us-east-1 --freeze-filesystem /vol/ --mysql --mysql-host 10.1.56.144  --mysql-socket /var/lib/mysql/mysql.sock --mysql-username root --description "snapshot for mysql" vol-XXXXXX

Change access key and secret key. Also give region, mount point (/vol/ is used here), socket file, MySQL username and password, host name (private IP used here) and volume Id.

This command will output master_log_file, master_log_pos and snapshot-id of volume as shown below:

ec2-consistent-snapshot: master_log_file="log-bin.000001", master_log_pos=726366868
snap-020530a1cd180a2de

Create a volume from this snapshot.
Create a new MySQL instance and attach the volume created above as the data directory.
Change /etc/my.cnf with server-id=2 under [mysqld] section.
Start MySQL with sudo service mysqld start.
Login to MySQL shell of the new server and execute the following commands:

mysql>STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql>CHANGE MASTER TO MASTER_HOST='10.1.56.144', MASTER_USER='root', MASTER_LOG_FILE='log-bin.000002', MASTER_LOG_POS= 157476861;
Query OK, 0 rows affected (0.01 sec)

mysql>START SLAVE;
Query OK, 0 rows affected (0.01 sec)

Make sure to change the MASTER_LOG_FILE and MASTER_LOG_POS value in this command with the one generated by ec2-consistent-snapshot.
This will run the new MySQL instance as slave and it will start syncing with the master which you can check as shown below:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.1.56.144
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: ip-192-168-6-2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 177
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 28826401-30a3-11e6-bd2c-22000a8a3151
             Master_Info_File: /vol/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

It will take some time to synch with the master depending upon the lag between them. Once the Seconds_Behind_Master is 0, change the DNS entry to point application to the new MySQL.
When writes start coming to new MySQL reset the slave by running following command:

mysql>STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>RESET SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>RESET SLAVE ALL;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
Empty set (0.00 sec)

If you are using MySQL 5.5.16 or later use RESET SLAVE ALL command.
Now the slave is no more a slave, it is now a standalone MySQL server. You can terminate the old MySQL instance.