MySQL Master-Slave Replication

2020-06-13

This artical shows how to setup a MySQL cluster on AWS that includes a master and a slave. Reads can be sent to any of the two instances, and writes have to be sent to the master instance.

1. Setup two new AWS instances:

Create two new AWS instances identical to your previous instance used to run Fabflix. We call these “instance 2” (as the master) and “instance 3”‘ (as the slave). We assume their public IPs are 2.2.2.2 and 3.3.3.3, and their internal AWS IPs are 172.2.2.2 and 172.3.3.3, respectively.
Add new rules in their security group(s) to open their MYSQL/Aurora type to each other using their AWS internal IP.

2. Set up the master instance.

SSH to the master instance. Run the following command to install MySQL server.

1 2	master-shell> sudo apt-get update master-shell> sudo apt-get install mysql-server

Edit the /etc/mysql/mysql.conf.d/mysqld.cnf file and set the bind-address to 0.0.0.0. Also, uncomment the lines of server-id and log_bin properties.

Restart the MySQL service.

1	master-shell> sudo service mysql restart

1
2
3

master-shell> mysql -u root -p
master-mysql> create user 'repl'@'%' identified by 'slavepassword';
master-mysql> grant replication slave on *.* to 'repl'@'%' ;

Run the following command in MySQL console to get the status about the master MySQL:
1
master-mysql> show master status;

Keep the values of the File and Position columns, as they are needed in the next step. For example, these values are mysql-bin.000001 and 337.

3. Set up the slave instance:

SSH to the slave instance. Run the following commands to install MySQL server.

1 2	slave-shell> sudo apt-get update slave-shell> sudo apt-get install mysql-server

Edit the /etc/mysql/mysql.conf.d/mysqld.cnf file and set the bind-address to 0.0.0.0. Also, uncomment the server-id property and use value 2. Do not uncomment the log_bin property.

Restart the MySQL service.

1	slave-shell> sudo service mysql restart

1 2	slave-shell> mysql -u root -p slave-mysql> CHANGE MASTER TO MASTER_HOST='172.2.2.2', MASTER_USER='repl', MASTER_PASSWORD='slavepassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=337;

Note that the values used for MASTER_LOG_FILE and MASTER_LOG_POS are the ones from the last step.

Start the MySQL slave:

1 2	slave-mysql> start slave; slave-mysql> show slave status;

It should show “Slave_IO_State: Waiting for master to send event”.

4. Test if the synchronization works:

On the master instance, create a database with a table with a tuple:

master-shell> mysql -u root -p
master-mysql> create database pets;
master-mysql> use pets;
master-mysql> create table cats(name varchar(20)); 
master-mysql> insert into cats values ("fluffy");

On the slave instance, check if the data has been propagated from the master.

slave-shell> mysql -u root -p
slave-mysql> show databases;
slave-mysql> use pets;
slave-mysql> show tables; 
slave-mysql> select * from cats;

We should be able to see the database, table, and record on this instance propagated from the master. If so, congratulations!

Others

It may be easy for you to create new AWS instances using an image of an existing instance. To do so, (1) select an existing instance and “Create Image” for it; and (2) Select the generated image under “IMAGES” -> “AMIs”, and “Launch” instances using this image. If you use this approach, make sure to delete the file “auto.cnf” used by MySQL with the instance’s auto-generated UUID.

1	shell> sudo \rm -rf /var/lib/mysql/auto.cnf