1. What is MySQL Replication:

Replication is the dynamic process of synchronizing data between a primary (master) database server and one or more secondary (slave) database servers. Using this process, it’s possible to hold on copies of data that can be recovered when the primary server fails.

MySQL replication setups can take different shapes. The common topology is probably a simple one master and one or more slaves. Such configuration gives you scale-out for reads/writes since you can utilize your master to handle write-only requests and your slaves to handle read-only requests.

2. Native Replication Scheme:

For replication to work, all read/write events on Master server needs to be writing to a special log file called binary log. Replication slave reads binary log events from the master (from which point in the binary log) and just copies them over to a local log file called relay log. Once all the events in the relay log are executed, the SQL thread automatically deletes the log.

MySQL replication is asynchronous by default. That means Master does not wait for Slave and Slave needn’t be connected to the master all the time.

Asynchronous replication helps to reduce the load on Master, since it only serves writing requests. However, there is a number of seconds that the Slave is behind the Master. This is called Replication Lag, and If it happens, your application might read old data from the slave. The parameter Seconds_Behind_Master can be used as an indication of the current replication lag of the Slave.

3. Setup Master-slave Replication:

Let’s assume the IP address for master and slave are as follows:

Master: 192.168.10.1

Slave: 192.168.10.2

Setting up the Master:

  • bind-address: IP address of the Master
  • server-id: A unique server id number
  • log_bin: A path of the binary-log file

Restart MySQL server:

We need to grant privileges to the slave by creating a new user:

As mentioned, this is the position from which the slave database will start replicating and all data in the Master should be dumped:

File Position
mysql-bin.000028 799
1 row in set (0.00 sec)


Setting up the Slave

Add the same configurations as the master with the Slave Ip address and new unique server ID:

There are few parameters can be used for filtering replication log:
REPLICATE_DO_DB
REPLICATE_IGNORE_DB
REPLICATE_DO_TABLE
REPLICATE_IGNORE_TABLE

Restart MySQL server:

Import the dump file to the Slave.
Repoint the Slave to the Master binary-log file:

Check the MySQL replication slave status:

************************************ 1. row *****************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.1
Master_User: replication_user
Master_Log_File: mysql-bin.000028
Read_Master_Log_Pos: 799
Last_Errno: 0
Last_IO_Errno: 0
Last_SQL_Errno: 0
Seconds_Behind_Master: 0
4. Recommendations:
  • In some failover circumstances, the system still has a downtime, since the Master server need to be restarted or promote the Slave as a new Master manually, and it takes times. By the way, implementing Master-Slave Replication can reduce downtime as low as possible.
  • Since the asynchronous process can cause Replication Lag, this issue can be solved by sending all reading requests to Master server instead of Slave or using a semi-synchronous process is also helpful.
  • Should only make writing requests on the Master server, not the Slave in order to avoid breaking the replication.
  • MySQL replication can be deployed as a Master-Master model, then all servers can handle both read and write requests. However, this approach is not recommended:
    • Data consistency is not guaranteed.
    • It’s still an asynchronous process.
    • It will take time and effort to restart service when it fails.
    • Need more hardware resources for Database proxy.
  • Should deploy MySQL Replication on GTID-based instead of standard bin-log. With GTID, it’s not important anymore in which binary log position a transaction was recorded.

Enable GTID by turning on these parameters: gtid_mode = ON, enforce-gtid-consistency = ON and use this query below:

  • MySQL Replication supports 3 levels of filter: Binary log, Database and Table. However, we should only use Database or Table filters and only deploy in the Slave to keep a full log of changes from Master server.
  • In case of multi-Master server, pay attention to which database is using as a default by the connection. For example, there are 2 DBs in master, you set binlog_format=’statement’ OR ‘mixed’and –replicate-do-db=DB1 on Slave, when execute the following commands:

this update query will not be executed on Slave. To make the update statement replicated to slave, you need to do: use DB1, update t1 …

  • Consider using MySQL Cluster for scale-out purpose, the backup server needs to be replaced as quick as possible when the main server is down, forexample: Galera, Percona, DRBD … combined with the database load balance.

Pham Duy Anh – FPT Telecom

Related posts: