How to set up MySQL Replication

In this topic I will discuss the steps on how to set up MySQL replication. I will show you the commands and configurations you will need on both Master and Slave databases.

Enable Binary logging in Master Database

The first step is to open the my.cnf or my.ini file and modify the [mysqld] block to have the following configuration. Change the bind-address to become the master server IP Address.

[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin=mysql-bin
server-id=1
bind-address=0.0.0.0

Notice the assigned server-id value is 1, that is because we need to assign a unique server-id on each of your master and slave configurations. Then restart the MySQL daemon on your host server.

systemctl restart mysqld

For security reasons, it is recommended to create a separate user that has limited access only to replication tasks. Change slave.example.com to become the slave server IP Address.

CREATE USER 'slave'@'slave.example.com' IDENTIFIED BY 'somerandompassword';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'slave.example.com';
GRANT SELECT, LOCK TABLES ON website.* TO 'slave'@'slave.example.com';
FLUSH PRIVILEGES;

To start the slave replication process at the correct point, you need to take note of the master’s current coordinates within its binary log. If you are using InnoDB, this procedure uses FLUSH TABLES WITH READ LOCK, which blocks COMMIT operations for InnoDB tables.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Open another terminal while keeping the READ LOCK in effect on the current terminal (quitting the open connection will unlock the tables). And then, on the other terminal create a snapshot of the database.

mysqldump --all-databases --master-data > dbdump.db

You can also create a dump of a specific database only.

mysqldump website --master-data > dbdump.db

Unlock the tables after you have successfully created a snapshot of your databases. Unlock this in the initial terminal opened.

UNLOCK TABLES;

Slave Host Server

Subsequently, login to your slave host server and enable the slave configuration.  Edit the my.cnf or my.ini file and add the following under the [mysqld] block. Furthermore, notice that the assigned server-id value this time is 2.

[mysqld]
server-id=2

Restart the MySQL daemon on your slave host server.

systemctl restart mysqld

Load your database snapshots after the slave configuration has been set up.

mysql < dbdump.db

If you created a snapshot for a specific database, you first need to create an empty database on your slave.

CREATE DATABASE `website` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
exit;

mysql website < dbdump.db

Slave to Master Host Connection

To configure the slave connection to the master, execute the following statement on the slave, replacing the option value with the actual value relevant to your system. And also remember the output of “SHOW MASTER STATUS” we got from the master.

mysql 
CHANGE MASTER TO MASTER_HOST = '1.2.3.4', MASTER_PORT = 3306, MASTER_USER = 'slave', MASTER_PASSWORD = 'somerandompassword', MASTER_LOG_FILE='mysql-bin.001238', MASTER_LOG_POS=154;

If you are replicating only a specific database or table, you will need to apply the filter that matches the database or table names.

mysql
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (website);
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘website.%');

Start slave thread.

START SLAVE;

Check your slave thread if it is running properly.

SHOW SLAVE STATUS\G;

Pay attention to the value of Slave_IO_State Slave_IO_Running Last_IO_Error and Slave_SQL_Running_State. You should see an indication that the replication is running without an error.

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Last_IO_Error:
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

That’s it! We just covered the process on How to set up MySQL replication. You now have a working replication to backup your data. Check out also my other tutorial about Database Charset and Collation Fix in MySQL.