Ensure both MySQL servers are compatible, the master has binary logging enabled, and network connectivity exists with static IPs or hostnames. 2. On the master, edit the MySQL config to set server-id=1, enable log_bin, use binlog_format=ROW, then restart MySQL, create a replication user with REPLICATION SLAVE privilege, and run SHOW MASTER STATUS to record the binary log file and position. 3. On the slave, configure server-id=2, enable relay_log and optionally log_bin, restart MySQL, then execute CHANGE MASTER TO with the master’s IP, replication user credentials, and binary log coordinates, followed by START SLAVE. 4. Verify replication by checking SHOW SLAVE STATUS\G, ensuring Slave_IO_Running and Slave_SQL_Running are both Yes and Seconds_Behind_Master is 0. 5. Test by creating a database on the master and confirming it appears on the slave. Optionally, enable GTID on both servers by setting gtid_mode=ON and enforce_gtid_consistency=ON, then use MASTER_AUTO_POSITION=1 for easier management. Always back up servers, monitor replication lag, secure the replication user, and consider semi-synchronous replication for enhanced data safety, and once configured correctly, MySQL replication is stable and requires minimal maintenance.

Setting up MySQL replication is a reliable way to improve data availability, enable load balancing for read-heavy workloads, and provide a foundation for backups. This guide walks you through the process of setting up a basic master-slave (primary-replica) replication in MySQL.

1. Understand the Replication Setup

MySQL replication works by having one server (the master) record all data changes in its binary log. The slave connects to the master, reads these logs, and applies the same changes locally. This ensures the slave stays synchronized with the master.

Before you begin:

• Both servers should run compatible MySQL versions.
• The master must have binary logging enabled.
• Ensure network connectivity between master and slave.
• Use static IP addresses or resolvable hostnames.

2. Configure the Master Server

First, configure the MySQL master server to allow binary logging and set a unique server ID.

Edit the MySQL Configuration File

On the master server, open the MySQL config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify the following lines under the [mysqld] section:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
expire_logs_days        = 7
max_binlog_size         = 100M

• server-id = 1: Unique identifier for the master.
• log_bin: Enables binary logging.
• binlog_format = ROW: Recommended for consistency and compatibility with GTID (if used later).

Restart MySQL

sudo systemctl restart mysql

Create a Replication User

Log into MySQL on the master:

mysql -u root -p

Create a user for replication:

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

Replace 'secure_password' with a strong password. The '%' allows connections from any host—restrict to the slave’s IP for better security.

Get Binary Log Coordinates

Still in the MySQL shell, run:

SHOW MASTER STATUS;

Note the output:

 ------------------ ---------- -------------- ------------------ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 ------------------ ---------- -------------- ------------------ 
| mysql-bin.000002 |  373     |              |                  |
 ------------------ ---------- -------------- ------------------ 

You’ll need the File and Position values when setting up the slave.

3. Configure the Slave Server

Now set up the slave server to connect to the master and start replication.

Edit the Slave’s MySQL Configuration

Open the config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify:

server-id           = 2
relay_log           = /var/log/mysql/mysql-relay-bin.log
log_bin             = /var/log/mysql/mysql-bin.log

Even slaves can act as masters later (for chaining), so enabling log_bin is optional but recommended.

Restart MySQL on Slave

sudo systemctl restart mysql

Set Up Replication from MySQL Shell

Log into MySQL on the slave:

mysql -u root -p

Run the following command, using the master’s IP, replication user credentials, and the log file/position from earlier:

CHANGE MASTER TO
  MASTER_HOST='master_server_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='secure_password',
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=373;

Then start the slave threads:

START SLAVE;

4. Verify Replication is Working

Check the slave’s status:

SHOW SLAVE STATUS\G

Look for these key fields:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: Should be 0 or increasing then dropping to 0 if catching up.

If either thread is not running, check:

• Network connectivity.
• Firewall settings (port 3306).
• Correct user privileges on the master.
• Error messages in Last_Error field.

5. Test the Replication

On the master, create a test database:

CREATE DATABASE test_replication;

On the slave, run:

SHOW DATABASES;

You should see test_replication listed. This confirms replication is working.

Optional: Use GTID for Simpler Management

For more advanced setups, consider enabling GTID (Global Transaction ID). It simplifies failover and reconfiguration by tracking transactions uniquely.

To enable GTID: Add to both master and slave config:

gtid_mode = ON
enforce_gtid_consistency = ON

Then use CHANGE MASTER TO MASTER_AUTO_POSITION = 1; instead of specifying log file and position.

Final Notes

• Always back up both servers before and after setup.
• Monitor replication lag regularly.
• Secure the replication user and restrict access via firewall.
• Consider using semi-synchronous replication for improved data safety.

Basically, just follow the steps, double-check the config and credentials, and monitor the slave status. Once it's running, MySQL replication is stable and low-maintenance.

The above is the detailed content of How to Set Up MySQL Replication: A Step-by-Step Guide. For more information, please follow other related articles on the PHP Chinese website!