MySQL Replication on EC2

I am not a DBA, but being in a 3-person start-up makes you a lot of things, at least temporarily. I am not a writer either, but I am hoping that I can share what I have learned and hope somebody else finds it useful.

While running a commercial web application, two things are of at-most importance, reliability and performance. For these two very important parameters, the most common bottleneck tends to be the database. This leads me to the topic of MySQL Replication.

Assuming you have two machines running MySQL Database servers, MySQL replication can be done in two ways, Master-Slave and Master-Master combinations.

Master-Slave
Master-slave combination is used usually for performance reasons. The writes go to the master db server, which are then replicated on the slave db server. The slave can then be used for slower queries and reads, freeing the master for more important work.

Master-Master
Master-Master combination in addition to performance, are also used for reliability reasons. The writes generally go to one of the masters, also known as the primary master (which is pre-determined), and is replicated to the secondary master. The secondary master serves as a stand-by, in case something goes wrong with the primary master. The secondary master can then pick-up the writes (thus becoming the primary), which are then replicated to the old primary master, (now the secondary) when it is brought back up. This provides redundancy. But there are some important caveats to keep in mind, which I will come to later. Since this setup is more complex, I will take you through this below.

EC2
Setting up this replication on EC2 is a little bit tricky. But I will guide you along so that you know exactly what to do. There are quite a number of steps, but each one is fairly easy to do, just need to be careful.
Machines on EC2 have what are known as a public DNS and a private DNS. if you have logged in to the machines, then you have most definitely used their primary DNS. You can obtain these names from the aws console.



For MySQL replication purposes, we will need the private DNS/private IP Address.

Here is the setup that I had :-

• Two 'm1.large' ec2 instances
• Ubuntu 10.6
• MySql 5.1

Detailed Steps

1. Now login to one of the instances, which is your primary ( hereby called server P ). It doesn't matter which one is primary, but if you have already been using one of them, then that is your primary.
2. Now login to the MySQL database instance and create a new user. This user will be used by the secondary master ( hereby called server S ) to connect and obtain updates. :-
   
3. 
   
> USE mysql;
> INSERT INTO user (Host, User, Password, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES ('%', 'slave_username', password('slave_password'), 'Y', 'Y', 'Y', 'Y');
> FLUSH PRIVILEGES;
4. Find out where your mysql configuration file is located ( my.cnf ). In my case, it was located at /etc/mysql/my.cnf. There should be a 'conf.d' folder in the same level.
5. Create a new file called replicate.cnf in /etc/mysql/conf.d/ folder. This file will contain the customizations for replications, such that we don't disturb 'my.cnf' for now.
6. Insert the following :-

7. [mysqld]
   server-id = 1 # for server P
   replicate-same-server-id = 0
   auto-increment-increment = 2
   auto-increment-offset = 1 # set differently for server S
   
   master-host = ip-11-111-1-111.us-west-1.compute.internal # private DNS of server S
   master-user = slave_username
   master-password = password_for_slave_user
   master-connect-retry = 60
   replicate-do-db = database_to_replicate
   
   log-bin = /var/log/mysql/mysql-bin.log
   binlog-do-db = database_to_replicate
   binlog-ignore-db=mysql
   log = /var/lib/mysql/mysql.log
   
   relay-log = /var/log/mysql/mysql-relay-bin
   relay-log-index = /var/log/mysql/mysql-relay-bin.index
   relay-log-info-file = /var/log/mysql/mysql-relay-log.info

8. Repeat steps 2 - 7 for server S.
9. For the replicate.cnf for server S, insert the following :-

10. [mysqld]
    server-id = 2 # for server S
    replicate-same-server-id = 0
    auto-increment-increment = 2
    auto-increment-offset = 2 # set differently for server P
    
    master-host = ip-22-222-2-222.us-west-1.compute.internal # private DNS of server P
    master-user = slave_username
    master-password = password_for_slave_user
    master-connect-retry = 60
    replicate-do-db = database_to_replicate
    
    log-bin = /var/log/mysql/mysql-bin.log
    binlog-do-db = database_to_replicate
    binlog-ignore-db=mysql
    log = /var/lib/mysql/mysql.log
    
    relay-log = /var/log/mysql/mysql-relay-bin
    relay-log-index = /var/log/mysql/mysql-relay-bin.index
    relay-log-info-file = /var/log/mysql/mysql-relay-log.info

11. With me so far? Ok. Make sure '/var/log/mysql/mysql' specified in the cnf actually exists. For the initial stage, you need to manually make sure the data is in synch. This can be done by doing a mysqldump of the database on the primary, and ( scp ) copying the resultant file over to the secondary and running it there. Here is a link to help you out.
12. The mysql default configuration prevents the database from being accessed from different machines. Copy the default my.cnf into my.cnf.bak. Then edit the /etc/mysql/my.cnf file on both servers, and comment out the
    #bind-address = 127.0.0.1
    
13. The last thing needed to set up replication is the synchronization of the servers. On the mysql command-line on server P, enter the following :-

mysql> show master status;
+------------------+----------+--------------+------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------+
| mysql-bin.000006 | 106 | fitz | mysql |
+------------------+----------+--------------+------------+

Keep track of those values as you need this on server S.
On server S, mysql command-line:-

mysql> stop;
mysql> CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='', MASTER_LOG_FILE='< Eg. mysql-bin.000006 >', MASTER_LOG_POS=< Eg. 106 > ;
mysql> slave start;
mysql> show slave status;

This should show that the slave is running or waiting for event from master.

15. Repeat step 14 for server P, make sure you plugin all the right values.

Thats it!
Test to make sure the changes made on one database is being propagated to the other. You can also do a tail on the logs :-

tail -f /var/lib/mysql/mysql.log

Enjoy!