Configuring MySQL Master-Slave Replication on Linux

Environment Preparation

Operating System: Linux (CentOS/RHEL)
Database Version: MySQL 5.7+
Primary Node IP: 10.0.0.10
Secondary Node IP: 10.0.0.20

Initializing the Database Schema

Execute the following SQL command on both the primary and secondary instances to ensure schema consistency before enabling replication:

CREATE DATABASE app_data;

Primary Node Configuraton

Edit the MySQL configuration file (typically located at /etc/my.cnf or /etc/mysql/my.cnf) on the primary server. Append the following directives under the [mysqld] section:

log-bin=db-primary-log
binlog_format=ROW
server_id=101
binlog_do_db=app_data

The binlog_format=ROW setting records the exact row-level changes rather than the SQL statements that triggered them, ensuring deterministic replication across different environments. The server_id must be a unique integer across all participating nodes in the replication topology.

Creating the Replication User

On the primary node, create a dedicated user for replication traffic. Adjust the password validation policy if necessary to allow the intended credentials, then grant the required replication privileges:

SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_length=4;
GRANT REPLICATION SLAVE ON *.* TO 'repl_admin'@'10.0.0.%' IDENTIFIED BY 'SecureRepl4!';
FLUSH PRIVILEGES;

Secondary Node Configuration

Modify the configuration file on the secondary server. Add these parameters to the [mysqld] block:

log-bin=db-secondary-log
binlog_format=ROW
server_id=102
relay_log=relay-log-node2
read_only=1

Including relay_log specifies the name and location of the relay log files. The read_only directive prevents accidental data modifications on the secondary node, preserving data consistency with the primary source.

Activating the Primary Node

Restart the MySQL service on the primary server to apply the configuration changes:

systemctl restart mysqld

SHOW MASTER STATUS;

Note the File and Position values from the output, as they will be required for the secondary node connection setup.

Linking the Secondary to the Primary

Restart the MySQL daemon on the secondary node, then access the MySQL shell. Configure the replication connection using the credentials and log coordinates obtained from the primary status output:

CHANGE MASTER TO
  MASTER_HOST='10.0.0.10',
  MASTER_USER='repl_admin',
  MASTER_PASSWORD='SecureRepl4!',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='db-primary-log.000001',
  MASTER_LOG_POS=120;

Initiate the replication process and verify its operational status:

START SLAVE;
SHOW SLAVE STATUS\G

Ensure that both Slave_IO_Running and Slave_SQL_Running display Yes in the status output. Once confirmed, data modifications executed on the primary node will automatically synchronize to the secondary database.

Tags: MySQL database replication Master-Slave Architecture Linux Administration Server Configuration

Posted on Sat, 15 Aug 2026 17:00:03 +0000 by franklyn

Freaks City