Setting up MySQL master-slave replication Print

In master-slave replication, one server (the master) accepts writes and the second (the slave) receives a copy of every change. This gives you a live second copy for failover, a server to run reports and backups against without loading the master, and a way to scale reads.

Replication is not a backup. A mistaken DELETE or DROP on the master is applied on the slave within seconds. Keep proper backups as well — see Backing up your account and websites in Plesk.

Note on syntax

MySQL 8 renamed these commands: CHANGE REPLICATION SOURCE TO, START REPLICA, SHOW REPLICA STATUS, with SOURCE_HOST and so on. MariaDB and older MySQL use the MASTER/SLAVE wording shown below. Substitute the new names if you run MySQL 8 or later.

Configuring the master

1. Edit the configuration

In /etc/mysql/my.cnf, a file in /etc/mysql/conf.d/, or /etc/my.cnf depending on the distribution:

[mysqld]
bind-address = 203.0.113.10
server-id    = 1
log_bin      = /var/log/mysql/mysql-bin.log
binlog_do_db = example

• bind-address — the master's own address, so the slave can reach it.
• server-id — any unique number; 1 is convenient. It must differ from the slave's.
• log_bin — the binary log the slave reads changes from.
• binlog_do_db — the database to replicate. Repeat the line for each one.

Restart the service:

systemctl restart mariadb

Exposing MySQL to the network means restricting access to it. Allow port 3306 in the firewall only from the slave's address. An open database port is found and attacked within hours.

2. Create the replication user

mysql -u root -p

CREATE USER 'slave_user'@'203.0.113.20' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'203.0.113.20';
FLUSH PRIVILEGES;

Use the slave's actual address rather than '%', and a strong password.

3. Lock the tables and note the position

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Record the File and Position values — the slave needs both to know where to start.

The lock makes the database read-only, so the dump and the recorded position match exactly. Keep this session open: closing it releases the lock.

4. Take the dump in a second session

In a new terminal, leaving the first one untouched:

mysqldump -u root -p --opt example > /root/example.sql

Then, back in the first session:

UNLOCK TABLES;

Plan for this: writes to the database fail or wait while the lock is held, so on a busy site do it at a quiet time. On a large database, mysqldump --single-transaction --master-data=2 avoids the lock altogether for InnoDB tables, and records the position in the dump itself.

Configuring the slave

5. Import the dump

Copy the dump to the slave, then:

mysql -u root -p -e "CREATE DATABASE example;"
mysql -u root -p example < /root/example.sql

6. Edit the configuration

[mysqld]
bind-address = 203.0.113.20
server-id    = 2
relay-log    = /var/log/mysql/mysql-relay-bin.log
log_bin      = /var/log/mysql/mysql-bin.log
binlog_do_db = example

The server-id must differ from the master's. Restart the service:

systemctl restart mariadb

7. Point the slave at the master

CHANGE MASTER TO
  MASTER_HOST     = '203.0.113.10',
  MASTER_USER     = 'slave_user',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS  = 107;
START SLAVE;

The log file and position are the values recorded in step 3. Getting them wrong means the slave either misses changes or tries to reapply them — both leave the copies inconsistent.

8. Check that it works

SHOW SLAVE STATUS\G

Look for:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0
• Last_Error empty

If either process is not running, Last_Error and Last_IO_Error explain why — usually authentication, a firewall blocking port 3306, or a wrong log position.

Then test properly: create a table on the master and confirm it appears on the slave.

Running it afterwards

• Monitor it. Replication stops at the first statement it cannot apply and does not restart itself. A slave that silently stopped weeks ago is worse than none, because you will rely on it.
• Do not write to the slave. Writes made directly on it diverge from the master and cause errors later. Set read_only = 1 on the slave to prevent it.
• Watch the lag. Seconds_Behind_Master growing means the slave cannot keep up — usually disk or a long-running query.

For a configuration where both servers accept writes, see Setting up MySQL master-master replication — though it is worth reading the warnings there before choosing it.

If you would like help designing a replication setup, describe your application and what it needs to survive in a ticket.