Before configuring MySQL master-slave replication, ensure both servers run the same MySQL version and have compatible system configurations.

Configure the Master Server

Edit the MySQL configuration file /etc/my.cnf on the master node:

[mysqld]
log-bin=mysql-bin
server-id=101

• log-bin enables binary logging, required for replication.
• server-id must be a unique positive integer across all servers in the replication topology.

Configure the Slave Server

On the slave server, edit /etc/my.cnf:

[mysqld]
server-id=102
# log-bin is optional on slaves, but recommended for backup or cascading replication

Restart MySQL Services

Apply changes by restarting MySQL on both servers:

systemctl restart mysqld

Create Replication User on Master

Connect to the master MySQL instance and create a dedicated replication user:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

For enhanced security, restrict the user to specific IPs (e.g., 'repl_user'@'192.168.1.102') instead of using '%'.

Lock Tables and Capture Binary Log Position

To insure data consistency during snapshot capture, execute:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Output example:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      1247 |              |                  |
+------------------+----------+--------------+------------------+

Do not close this session until the slave is configured. The lock prevents writes that could alter the binlog position.

Configure Slave to Connect to Master

On the slave server, execute:

CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='SecurePass123!',
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=1247;

Start the replication process:

START SLAVE;

Verify Replication Status

Check the slave’s replication state using:

SHOW SLAVE STATUS\G

Key fields to verify:

• Slave_IO_Running: Yes — Indicates the I/O thread is successfully connecting to the master and fetching binlog events.
• Slave_SQL_Running: Yes — Confirms the SQL thread is applying relay log events to the local database.
• Master_Host, Master_User, Master_Log_File, Read_Master_Log_Pos — Validate connection and position details.
• Seconds_Behind_Master — Shows replication lag in seconds (0 means synchronized).

If either Slave_IO_Running or Slave_SQL_Running is No, inspect Last_Error in the output for troubleshooting. Common issues include:

• Network connectivity problems between master and slave
• Incorrect credentials or user permissions
• Binlog file or position mismatch
• Manual data modifications on the slave causing primary key conflicts

The Waiting for master to send event state in Slave_IO_State indicates normal operation — the slave is idle, awaiting new transactions from the master.

After confirming both threads are running, unlock tables on the master:

UNLOCK TABLES;