Note: Please go to docs.rightscale.com to access the current RightScale documentation set. Also, feel free to Chat with us!
Home > ServerTemplates > Archive > Pre-11H1 > Migration Tutorials > MySQL Database Migration: EBS to S3

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.

Prerequisites

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)

Overview

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.)

 

ebs_to_s3_diagrams.gif

Steps

This tutorial is broken down into 7 steps:

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

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.   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.

screen-MigrateS3Launch.png

 

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.
Text: 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.

Create an EBS Volume

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.

screen-MigrateS3Snapshot.png

 

Be sure to create the volume in the same availability zone as the Slave-DB (ex: us-east-1a).

screen-MigrateS3CreateVolume.png

Click Create.

Attach the volume to the Slave-DB

You can now attach the volume to the Slave-DB server.  Click the Attach button.

screen-MigrateS3Attach.png

 

Select the Slave-DB server (ex: Slave-DB (S3)).  Select and remember the device that you are attaching it to (ex: /dev/sdj)

screen-MigrateS3SelectServer.png

Click OK.

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 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

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).
You must to post a comment.
Last modified
21:11, 16 May 2013

Tags

Classifications

This page has no classifications.

Announcements

None


© 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.