MySQL – Replication Overview & Simple Setup

Overview

Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Screen Shot 2017-01-31 at 8.00.20 PM.png

Problems solved by Replication

Backups

Replication is a valuable technique for helping with backups.

screen-shot-2017-01-31-at-8-02-07-pm

High Availability and Failover

Replication can help avoid making MySQL a single point of failure in your application.

screen-shot-2017-01-31-at-8-18-54-pm

Scale Out

Replication can help distribute read queries across multiple database servers, which works well for read-intensive applications such as websites.

screen-shot-2017-01-31-at-8-19-35-pm

Data Distribution

Replication can help maintain a copy of data in a geographically distant location for a remote site to use. Also, it can help distribute data to other systems without affecting the running operations on Master node.

screen-shot-2017-02-01-at-10-06-03-am

How MySQL Replication works

async-replication-diagram

  1. Before each transaction on master completes, the master records data changes(also called binary log events) to its binary log serially. Then the master tells the storage engine to commit the transaction.
  2. A worker thread called I/O thread on replica will read the contents of master binary log and dump it into replica’s relay log.
  3. Another worker thread called SQL thread will handle the last part: read and replay the events from the relay log, thus updating the replica’s data to match the master’s.

Note: This two-thread architecture decouples the process of fetching and replaying events on replicas, which allows them to be asynchronous. However, it also places constraints, the most important of which is that replication is serialized on the replica. This means updates that might have run in parallel on the master cannot be parallelized on replicas because they’ll be executed in a single thread, which is a performance for many workloads.

Simple Setup

Environment Preparation

Instances: Amazon Web Services EC2, m3.xlarge(4 vCPU, 15 GiB RAM, 80 GB SSD)
AMIPercona Server for MySQL 5.7 on CentOS created by Jetware
Configshttp://jetware.org/appliances/jetware/percona_57/profile

Master:172.31.11.94
Slave:172.31.11.168

Before start…

  1. Config Security Group to allow all internal traffic among instances in the same group
  2. Remove /jet/var/mysqld/data/auto.cnf and restart MySQL, since we’re using same AMI to create multiple MySQL instances, which results in all instances having the same server-uuid.
    Related error message in the log: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593

    [ec2-user@ip-172-31-11-94 ~]$ rm /jet/var/mysqld/data/auto.cnf
    [ec2-user@ip-172-31-11-94 ~]$ restart mysqld
    [ec2-user@ip-172-31-11-168 ~]$ rm /jet/var/mysqld/data/auto.cnf
    [ec2-user@ip-172-31-11-168 ~]$ restart mysqld

Configure Master

  1. Set up replication account on Master
[ec2-user@ip-172-31-11-94 ~]$ mysql
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
    -> TO repl@'172.31.11.168' IDENTIFIED BY 'azureq';
Query OK, 0 rows affected, 1 warning (0.03 sec)
  1. Add following two lines to my.cnf on Master to enable binary logging and specify a server ID.
    Note: In this particular AMI, add following two lines to /jet/etc/mycnf/my.cnf.d/mysqld.cnf under [mysqld] 
log_bin=/jet/var/mysqld/binlog/mysql-bin
server_id=1

If binary logging wasn’t already specified in the master‘s configuration file, you’ll need to restart MySQL.

Use SHOW MASTER STATUS to verify that binary log file is created on Master.

[ec2-user@ip-172-31-11-94 ~]$ restart mysqld
[ec2-user@ip-172-31-11-94 ~]$ mysql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Configure Slave

  1. Add following two lines to my.cnf on Slave to enable binary logging and specify a server ID.
log_bin=/jet/var/mysqld/binlog/mysql-bin
server_id=2
relay_log=/jet/var/mysqld/relaylog/mysql-relay-bin
# make the slave log replicated events to its own binary log
log_slave_updates=1
# prevent anything but privileged threads from changing data
read_only=1

If binary logging wasn’t already specified in the slave‘s configuration file, you’ll need to restart MySQL.

  1. Next step is to tell the Slave how to connect to the Master and begin replaying its binary logs. Also, we can inspect the output of SHOW SLAVE STATUS to see if the settings are correct.
[ec2-user@ip-172-31-11-168 ~]$ mysql
mysql> CHANGE MASTER TO MASTER_HOST='172.31.11.94',
 -> MASTER_USER='repl',
 -> MASTER_PASSWORD='azureq',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS=0;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> show slave status \G
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 172.31.11.94
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 4
 Relay_Log_File: mysql-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: No
 Slave_SQL_Running: No
 ...
  1. As we can see above, all the settings are set properly except the threads are no running. To start replication, we need to run START SLAVE.
[ec2-user@ip-172-31-11-168 ~]$ mysql
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.31.11.94
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 4
 Relay_Log_File: mysql-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ...

Simple Test

  1. On Master:
  • Create a database hello_db
  • Create a table hello_table in hello_db
  • Insert a record into hello_table
[ec2-user@ip-172-31-11-94 ~]$ mysql
mysql> create database hello_db;
Query OK, 1 row affected (0.00 sec)

mysql> use hello_db;
Database changed

mysql> create table hello_table(id int,name char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into hello_table values(1,'azureq');
Query OK, 1 row affected (0.03 sec)

mysql> select * from hello_db.hello_table;
+------+--------+
| id   | name   |
+------+--------+
| 1    | azureq |
+------+--------+
1 row in set (0.00 sec)
  1. On Slave:

Query hello_db.hello_table

[ec2-user@ip-172-31-11-168 ~]$ mysql
mysql> select * from hello_db.hello_table;
+------+--------+
| id   | name   |
+------+--------+
| 1    | azureq |
+------+--------+
1 row in set (0.00 sec)

Reference:

High Performance MySQL, 3rd Edition    By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko
Understanding MySQL Internals    By Sasha Pachev
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s