Database Replication Using EBS Volumes From One EC2 to Another

Introduction

EBS volume is a storage device that can be attached to an EC2 instance. It functions in a similar way like Pendrive where we copy all files from one device to another. Using EBS volume we can replicate data from one instance to another using master slave replication.

Before starting make sure to set up a master slave relationship between two MySQL servers hosted on the EC2 instance. In this method of master slave replication, we will take a snapshot of the EBS volume of the master instance. From this snapshot, we will be creating a new volume and attaching it to the slave instance, and transfer data for replication.

Use Case

Master slave replication can be used for databases that are hosted inside EC2 instances i.e., it’s not AWS managed RDS. By having the data automatically copied from a master to a slave database, master-slave data replication enables us to effortlessly maintain multiple copies of a MySQL database. We can scale out our database, perform a backup of the data, or study it separately from the main database. This method helps in the efficient cloning of large master databases. This method clones 150GB EBS volume containing the master database in an hour.

Prerequisite

1. Make sure that MySQL is hosted inside the EC2 instance.
2. Servers must be running on the same OS and the same OS version.
3. Both server SQL must be either 32-bit or 64-bit as this method cannot be used for moving data from MySQL 32-bit to MySQL 64-bit.

Steps to Replicate the Data from Master to Slave

1. Login to MySQL from the EC2 instance and create a database and table.
2. Make sure to give the required permission to the slave instance from a master instance using the below query:

3. Close all the tables and lock all the tables of the database by using the below query:

4. Using “reset master” we can delete all binary log files, reset the binary log index, and create a new binary log file and if you want to maintain the existing master slave relationship this step can be skipped.

• Check the status of the master and note down the current master file name and file position as it will be helpful to check when we check the status of the slave.

5. Stop the MySQL instance in the master using the command:

/etc/init.d/mysql stop

6. From the EC2 console go to the snapshot and choose the volume of which you need to take a snapshot.

7. After taking the snapshot we can start MYSQL in a master instance by using the command: /etc/init.d/mysql start

8. Then login into MySQL and unlock the tables.

9. Select the snapshot and from that, you can create the new volume.

10. After creating the new volume stop the second EC2 instance (slave) and remove the volume from it. Attach the new volume as the root volume and start the instance.

11. In the slave instance in user data, we can mention this query:

12. sudo dd if=/dev/xvda1 of=/dev/null bs=1M so that when the instance restarts then data from the volume will be copied into the slave instance

13. After the instance is started login into the slave instance’s MySQL and change the master and mention the file name and position from the master instance and start the slave instance.

14. After starting the slave, we can check the status of the instance and check the database and tables and make sure data is replicated in the slave instance.

NOTE: Sometimes when you start a slave you encounter an error, in that case, stop the slave and then start the slave again.

Conclusion

Using EBS volume it is efficient to replicate the data rather than doing manually as taking a snapshot from the master instance and creating a new volume from the same is efficient.  AWS’s lazy loading method allows for quick restoration of the complete volume from a snapshot.  Up until the data has been loaded, MySQL performance will be sluggish.

About CloudThat