Introduction to MySQL Replication

MySQL Replication, also known as AB Replication, enables data synchronization between two or more database servers. When data is written to the master server, its automatically replicated to the slave servers in real-time.

MySQL replication operates through binary logs (binlog). The master server must have binlog enabled for replication to function.

How Replication Works

The repllication process involves three main steps:

1. The master server records all write operations in the binary log
2. The slave server's I/O thread retrieves the binlog events and writes them to the relay log
3. The slave server's SQL thread reads the relay log and executes the statements sequentially

Benefits of Replication

• Disaster recovery for failover scenarios
• Read-write separation to distribute query load
• Backup strategy that doesn't impact production performance

Replication Topologies

* Single Master - Single Slave
* Dual Master (Master-Master)
* Single Master - Multiple Slaves (for read scalability)
* Multiple Masters - Single Slave (supported since MySQL 5.7)
* Cascade Replication

Replication Mechanism

The master server maintains a binlog containing all data modification statements. It also runs a log dump thread that transmits binlog data to connected slave servers.

Each slave server runs two threads:

• I/O Thread: Connects to the master, requests binlog events, and writes them to the relay log
• SQL Thread: Reads the relay log and executes the statements to maintain data consistency

Configuration Steps

Prerequisites

Ensure both databases contain identical data before starting replication. The configuration process involves:

1. Create a replication user on the master and grant privileges to the slave
2. Configure the master database
3. Configure the slave database

Environment Setup

Installing MySQL on Both Servers

Disable firewall and SELinux:

[root@server1 ~]# systemctl stop firewalld
[root@server1 ~]# systemctl disable firewalld
[root@server1 ~]# sed -ri 's/(SELINUX=).*/\1disabled/g' /etc/selinux/config
[root@server1 ~]# setenforce 0

Install required dependencies:

[root@server1 ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel

Create MySQL user and group:

[root@server1 ~]# groupadd -r -g 306 mysql
[root@server1 ~]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql

Download and extract MySQL binary package:

[root@server1 ~]# cd /usr/src/
[root@server1 src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@server1 src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@server1 src]# cd /usr/local/
[root@server1 local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql

Set ownership and configure environment:

[root@server1 local]# chown -R mysql.mysql /usr/local/mysql
[root@server1 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@server1 ~]# source /etc/profile.d/mysql.sh

Create data directory and initialize MySQL:

[root@server1 ~]# mkdir /opt/data
[root@server1 ~]# chown -R mysql.mysql /opt/data
[root@server1 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/

Note: The initialization command outputs a temporary root password that must be saved.

Configure MySQL:

[root@server1 ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
[root@server1 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@server1 ~]# ldconfig

Create configuration file:

[root@server1 ~]# cat > /etc/my.cnf <<eof basedir="/usr/local/mysql" datadir="/opt/data" eof="" pid-file="/opt/data/mysql.pid" port="3306" skip-name-resolve="" socket="/tmp/mysql.sock" user="mysql">
<p>Configure service script and start MySQL:</p>

[root@server1 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@server1 ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@server1 ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@server1 ~]# service mysqld start

<p>Change root password using the temporary password:</p>

[root@server1 ~]# mysql -uroot -p
mysql> SET PASSWORD = PASSWORD('MyNewPass123!');
mysql> QUIT

<h2>Setting Up Master-Slave Replication</h2>
<h3>Step 1: Prepare Data on Master</h3>
<p>Create databases and tables on the master server:</p>

mysql> CREATE DATABASE appdb;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE userdb;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE orderdb;
Query OK, 1 row affected (0.00 sec)

mysql> USE appdb;
Database changed
mysql> CREATE TABLE users (id INT NOT NULL, username VARCHAR(100) NOT NULL, age TINYINT);
Query OK, 0 rows affected (11.83 sec)

mysql> INSERT INTO users (id, username, age) VALUES (1, 'alice', 25), (2, 'bob', 30), (3, 'charlie', 28);
Query OK, 3 rows affected (0.07 sec)

<h3>Step 2: Lock Tables and Create Backup</h3>
<p>Open a separate terminal on the master server and lock tables to ensure data consistency during backup:</p>

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.76 sec)

<p><strong>Important</strong>: Keep this terminal open until the backup is complete.</p>
<p>In another terminal, create the backup:</p>

[root@server1 ~]# mysqldump -uroot -p'MyNewPass123!' --all-databases > /opt/backup-20230901.sql
[root@server1 ~]# scp /opt/backup-20230901.sql root@192.168.55.129:/opt/

<h3>Step 3: Restore Backup on Slave</h3>
<p>On the slave server, restore the backup:</p>

[root@server2 ~]# mysql -uroot -p'MyNewPass123!' < /opt/backup-20230901.sql

<p>Verify the restored data:</p>

mysql> SHOW DATABASES;
mysql> USE appdb;
mysql> SELECT * FROM users;

<p>Release the table lock on master:</p>

mysql> QUIT

<h3>Step 4: Create Replication User on Master</h3>

mysql> CREATE USER 'repl_user'@'192.168.55.129' IDENTIFIED BY 'ReplPass456!';
Query OK, 0 rows affected (5.50 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.55.129';
Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.09 sec)

<h3>Step 5: Configure Master Database</h3>
<p>Edit the master MySQL configuration:</p>

[root@server1 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin
server-id=1
log-error=/opt/data/error.log

<p>Restart MySQL service:</p>

[root@server1 ~]# service mysqld restart

<p>Check master status:</p>

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

<p><strong>Note</strong>: Record the File and Position values for slave configuration.</p>
<h3>Step 6: Configure Slave Database</h3>
<p>Edit the slave MySQL configuration:</p>

[root@server2 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2
relay-log=mysql-relay-bin
error-log=/opt/data/error.log

<p>Restart MySQL service:</p>

[root@server2 ~]# service mysqld restart

<h3>Step 7: Start Replication</h3>
<p>Connect to the slave and configure replication:</p>

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.55.130',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='ReplPass456!',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=437;
Query OK, 0 rows affected, 2 warnings (0.28 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

<h3>Step 8: Verify Replication Status</h3>

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.55.130
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 437
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 437
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
             Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
                  Master_Server_Id: 1
                   Master_UUID: a1b2c3d4-e5f6-7890-abcd-ef1234567890

<p>Ensure both <code>Slave_IO_Running</code> and <code>Slave_SQL_Running</code> show <code>Yes</code>.</p>
<h2>Testing Replication</h2>
<p>Insert data on the master server:</p>

mysql> USE appdb;
mysql> SELECT * FROM users;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | alice    |   25 |
|  2 | bob      |   30 |
|  3 | charlie  |   28 |
+----+----------+------+
3 rows in set (0.09 sec)

mysql> INSERT INTO users(id, username, age) VALUES (4, 'david', 22);
Query OK, 1 row affected (0.14 sec)

<p>Verify on the slave server:</p>

mysql> USE appdb;
mysql> SELECT * FROM users;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | alice    |   25 |
|  2 | bob      |   30 |
|  3 | charlie  |   28 |
|  4 | david    |   22 |
+----+----------+------+
4 rows in set (0.00 sec)

<p>The data is successfully replicated from master to slave, confirming that the read-write separation setup is working correctly.</p></eof>