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.
To complete this tutorial you will need to know the replication username and password of the current setup (the Input values of DBREPLICATION_USER and DBREPLICATION_PASSWORD)
The following diagram is a logical representation of what following this tutorial accomplishes. (That is, move from what is represented on the left of the green arrow to what is on the right.)
Step 1: Launch a Slave-DB using the "MySQL Additional" ServerTemplate
Step 2: SSH into the Slave-DB and Stop MySQL
Step 3: Create and Attach an EBS Volume from a Snapshot
Step 4: Mount the Filesystem and Copy the Data to the Slave-DB's Local Drive
Step 5: Reconfigure the Slave-DB and Start Replication
Step 6: Promote the Slave to Master
Step 7: Backup and Clean-up
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. For more information on the Add Server assistant, see Add Server Assistant. 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).
|BACKUPFILE_PREFIX||The prefix that will be used in the filename of the backups (ex: app_backup)|
|BACKUP_S3_BUCKET||The name of the S3 bucket that will store the backups (ex: my_app_bucket)|
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.
|INIT_SLAVE_AT_BOOT||Change this parameter to be set to False so that it does not try to connect to the existing Master-DB server. |
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
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.
Go to Manage -> Storage -> EBS Snapshots and find the most recent snapshot of the Master-DB. Click the launch icon to create a volume from that snapshot.
Be sure to create the volume in the same availability zone as the Slave-DB (ex: us-east-1a).
You can now attach the volume to the Slave-DB server. Click the Attach button.
Select the Slave-DB server (ex: Slave-DB (S3)). Select and remember the device that you are attaching it to (ex: /dev/sdj)
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 local 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
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
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)."
© 2006-2014 RightScale, Inc. All rights reserved.
RightScale is a registered trademark of RightScale, Inc. All other products and services may be trademarks or servicemarks of their respective owners.