MySQL Database Migration: EBS to S3

Objective

Follow the instructions below if you want to migrate back to a redundant MySQL Setup that saves regular (.tar) backups to S3.

This tutorial assumes that you previously followed the MySQL Database Migration: S3 to EBS tutorial and already have a functioning MySQL EBS setup. You have an operational Master-DB instance that's producing regular EBS Snapshots.   To complete this migration tutorial a Slave-DB instance is not required.

NOTE:  To complete this tutorial you will need to know the replication username and password of the current setup (i.e. the values of DBREPLICATION_USER and DBREPLICATION_PASSWORD)

Step 1: Launch a Slave-DB using the "MySQL Additional" ServerTemplate

The first step is to launch a new Slave-DB that uses the S3-based ServerTemplate ("MySQL Additional"), which will save regular backups to S3 instead of taking EBS snapshots.

Go to your current deployment with the running Master-DB that's producing regular EBS snapshots.

Click the Add (EC2) Server button and select the latest version of the "MySQL Additional" ServerTemplate.  Provide a nickname and choose the appropriate SSH key, security group, and availability zone.  To make things easier, it's best to choose the same availability zone as your current Master-DB.  Click Add.

Go to the Inputs tab of your deployment and configure the following input parameters.  

NOTE: These values might already be saved in your deployment if it previously had the MySQL S3 ServerTemplates (MySQL Bootstrap and MySQL Additional).

Click the launch server icon.

Before you can launch the server, you must provide any missing input parameters.  You must also configure the server so that it does not try to synchronize with the Master-DB.  INIT_SLAVE_AT_BOOT must be set to False.

Step 2: SSH into the Slave-DB and Stop MySQL

When the server becomes operational, it will be running MySQL, but will have an empty database.  SSH into the Slave-DB instance and stop MySQL so you can start populating the database.  

SSH into the machine and stop MySQL from running by executing the following command:

service mysqld stop 

Step 3:  Create and Attach an EBS Volume from a Snapshot

The next step is to create an EBS volume from the latest EBS snapshot of the Master-DB and attach it to the Slave-DB server.

Step 4: Mount the Filesystem and Copy Data to the Slave-DB's Local Drive

Go back to your ssh console and mount the filesystem.  Use the following commands to mount the volume on the /dev/sdj device.

mkdir /snap
mount -t xfs /dev/sdj /snap

Next, copy the data from the volume to the loca drive of the Slave-DB.  NOTE: This process could take a long time depending on the size of the database.

cp -aR /snap/* /mnt/mysql
chown -R mysql:mysql /mnt/mysql

You can now start MySQL on the Slave-DB by running the following command:

service mysqld start

Step 5: Reconfigure the Slave-DB and Start Replication

Tell the MySQL Slave-DB to be a slave and provide the location of the Master-DB so that it can synchronize with the Master-DB and start replicating data.

Look at the position settings of the EBS snapshot from the shell and execute the following commands:

cat /snap/rs_snapshot_position.yaml

---
Position : 22222
...
File : mysql-bin.000002
...
Master_IP : 10.10.10.10

Now configure the replication position.  From the MySQL console, execute the following commands:

NOTE: You will need to provide your own replication username/password.

stop slave;

CHANGE MASTER to 
   MASTER_HOST = '10.10.10.10',
   MASTER_USER = 'YourUser',
   MASTER_PASSWORD = 'YourPassword',
   MASTER_LOG_FILE = 'mysql-bin.000002',
   MASTER_LOG_POS = 22222;

start slave;

You can now verify that data is being replicated over to the Slave-DB and that data is in-synch.  Run the following command to check the status of Slave-DB:

show slave status \G

If data is up-to-date and synchronized, it should show that both replication thread are running and that the Slave-SB is "caught up" with the Master-DB.

...
Slave_IO_Running : Yes
Slave_SQL_Running : Yes
...
Seconds_Behind_Master : 0

Step 6: Promote the Slave to Master

Now that you have a Slave-DB server with the latest data from the Master-DB, you can now promote it to master and make it the new Master-DB.  When the Slave-DB server becomes the new Master-DB server, the former EBS-based Master-DB server can be terminated.  Change the name of the server from "Slave-DB (S3)" to "Master-DB (S3)."

Step 7: Backup and Clean-up

• Initiate a new MySQL-S3 backup by executing the "DB backup" operational script on the new Master-DB server.
• Launch additional MySQL-S3 Slave-DB server(s) using the "MySQL Additional" ServerTemplate.
• Unmount, detach and delete the EBS volume from the MySQL-EBS Master-DB server (ex: ebs-db1).
• Terminate the MySQL-EBS Master-DB server (ex: ebs-db1).