wiki:cs122b-2019-winter-project5-mysql-master-slave

MySQL Master-Slave Replication

This tutorial 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:
    1. 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.
    2. Add new rules in their security group(s) to open their MYSQL/Aurora type to each other using their AWS internal IP.
  2. Watch this video to have a good understanding of this feature. The following steps are mainly based on those instructions in the video.
  3. Set up the master instance.
    1. SSH to the master instance. Run the following command to install MySQL server.
      master-shell> sudo apt-get update
      master-shell> sudo apt-get install mysql-server
      
    2. 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.
    3. Restart the MySQL service.
      master-shell> sudo service mysql restart
      
    4. Login to the MySQL console. Create a new user and give it the permission for replicating the database:
      master-shell> mysql -u root -p
      master-mysql> create user 'repl'@'%' identified by 'slavepassword';
      master-mysql> grant replication slave on *.* to 'repl'@'%' ;
      
    5. Run the following command in MySQL console to get the status about the master MySQL:
      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.
  4. Set up the slave instance:
    1. SSH to the slave instance. Run the following commands to install MySQL server.
      slave-shell> sudo apt-get update
      slave-shell> sudo apt-get install mysql-server
      
    2. 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.
    3. Restart the MySQL service.
      slave-shell> sudo service mysql restart
      
    4. Login to the MySQL console and let the slave know about the master server:
      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.
    5. Start the MySQL slave:
      slave-mysql> start slave;
      slave-mysql> show slave status;
      
      It should show "Slave_IO_State: Waiting for master to send event".
  5. Test if the synchronization works:
    1. 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");
      
    2. 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!
  6. Create moviedb on the master instance:
    1. Populate moviedb on master instance using "create_table.sql" and "data.sql" (You have done this many times)
    2. On the slave instance, check if the data is automatically propagated:
      slave-shell> mysql -u root -p
      slave-mysql> use moviedb; show tables;
      
    Note: The propagation is only from the master to the slave, not the other way around. To test it, you can insert some records to a table in the slave database, and check if it's propagated to the master instance. It should NOT!

Note: When you restart your AWS instances, the IP addresses of your instances could change. At this time, you need to reset the master address on the slave instance to re-connect them. However, note that MASTER_LOG_FILE and MASTER_LOG_POS could change, and thus you need to check the master status again.

master-mysql> show master status;
slave-mysql> stop slave;
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;
slave-mysql> start slave;
slave-mysql> show slave status;

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

shell> sudo \rm -rf /var/lib/mysql/auto.cnf
Last modified 17 months ago Last modified on Mar 4, 2019 10:56:24 PM