Setting Up MySQL Replication

MySQL logo on a block.

Image credit: Rubaitul Azad https://unsplash.com/@rubaitulazad

Overview

Setting up MySQL replication an seem challenging. This article will guide you through it. We'll configure one MySQL instance as the source database and another as its replica.

Replication allows data synchronization between these separate databases.

Step 1: Configure the source

  1. Install MySQL on 2 servers.
  2. Enable Binary Logging
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following line in that or equivilent file:
log-bin = /var/log/mysql/mysql-bin.log
  1. Restart services with susdo systemctl restart mysql
  2. Create a unique server id for the source.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following line in that or equivilent file:
server-id = 1

Step 2: Set up the replica

  1. Edit replica config file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following lines in that or equivilent file:
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log-bin = /var/log/mysql/mysql-bin.log
  1. Restart with sudo systemctl restart mysql
  2. On the source create a user run:
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
  1. Lock the master database to take a snapshot:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
  1. Dump the master database:
mysqldump -u root -p --all-databases --master-data > master_dump.sql
  1. Unlock the master db with UNLOCK TABLES;
  2. Copy the dump file to the replica:
scp master_dump.sql user@repllica_server:/path/to/dump/

Step 3: Import data to replica

  1. On the replica import the data: mysql -u root -p < master_dump.sql
  2. Configure replication on the replica:
CHANGE MASTER TO
    MASTER_HOST = 'source_server_ip',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'your_password',
    MASTER_LOG_FILE = 'master_bin_log_file',
    MASTER_LOG_POS = master_bin_log_position;
START SLAVE;
  1. Check the replica status with SHOW SLAVE STATUS\G
  2. Ensure that Slave_IO_Running and Slave_SQL_Running are both Yes.

That's it!

NOTE: Make sure to update the firewall if one is running.