Set Up MySQL Master-Slave Replication on VPS

July 2026 · 8 min read

MySQL Replication is a mechanism that allows a master database to synchronize data with a slave database in near real-time using binary logs (binlog). The master records every change, while the slave continuously reads the binlog and replays the same queries. Key benefits include live data redundancy protecting against master failure, distributing read queries to slaves to reduce load on the master, and creating a backup source without stopping master operations.

Enable Binary Logging on the Master

First, enable binary logging on the master server to record all data modifications. Edit /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf depending on your installation):

# In the [mysqld] section
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server_id = 1
binlog_format = row
expire_logs_days = 10

The server_id must be unique across all replication instances—master uses 1, slaves use 2, 3, 4, and so on. binlog_format = row is the safest format for replication. expire_logs_days controls how many days binlog files are kept before automatic deletion. Restart MySQL:

sudo systemctl restart mysql

Verify binary logging is enabled:

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

You should see log_bin set to ON.

Create a Replication User on the Master

The master needs a dedicated replication user account that the slave uses to read the binlog and synchronize data:

mysql -u root -p
CREATE USER 'repl_user'@'SLAVE_IP' IDENTIFIED BY 'repl_password_123';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'SLAVE_IP';
FLUSH PRIVILEGES;

Replace SLAVE_IP with the slave server's IP address, for example 203.0.113.50. If you have multiple slaves, avoid wildcard '%' for security. For MySQL 8.0.20+, you can combine CREATE USER and GRANT:

CREATE USER 'repl_user'@'SLAVE_IP' IDENTIFIED BY 'repl_password_123' REQUIRE SSL;

Tip: Use a strong password and never store it in plain-text files. Instead, use MySQL Wallet or mysql_config_editor to securely store replication credentials.

Back Up the Master and Extract the Replication Starting Point

Next, create a backup of the master database while recording the exact binlog position where the slave should start syncing. Use mysqldump with --master-data (MySQL 5.7 and older) or --source-data (MySQL 8.0.20+):

mysqldump -u root -p \
  --master-data=2 \
  --single-transaction \
  --routines \
  --triggers \
  --all-databases > master_backup.sql

Key options:

--master-data=2 — Records a CHANGE MASTER TO statement in the backup as a comment with binlog file and position
--single-transaction — Avoids locking tables, allowing the application to continue using the database
--routines — Exports stored procedures and functions
--triggers — Exports triggers
--all-databases — Backs up all databases

Your backup file will contain a comment like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

Save these LOG FILE and LOG POS values for configuring the slave.

Transfer the Backup to the Slave and Restore

Copy the backup file to the slave server using SCP or another method:

scp master_backup.sql user@SLAVE_IP:/tmp/

On the slave, import the backup:

mysql -u root -p < /tmp/master_backup.sql

Verify that databases were imported:

mysql -u root -p -e "SHOW DATABASES;"

Configure the Slave and Start Replication

Next, make additional configuration changes on the slave. Edit /etc/mysql/mysql.conf.d/mysqld.cnf on the slave:

[mysqld]
server_id = 2
relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
read_only = ON

server_id = 2 must not conflict with the master. read_only = ON prevents the slave from accepting writes (all data changes should flow through the master). Restart MySQL:

sudo systemctl restart mysql

Configure the slave to connect to the master using CHANGE MASTER TO (MySQL 5.7 and older):

mysql -u root -p
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='MASTER_IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password_123',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;
START SLAVE;

For MySQL 8.0.22+, use the updated syntax:

STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='MASTER_IP',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='repl_password_123',
  SOURCE_LOG_FILE='mysql-bin.000003',
  SOURCE_LOG_POS=154;
START REPLICA;

Replace MASTER_IP with the master's IP address. The MASTER_LOG_FILE and MASTER_LOG_POS values come from the comment in your backup file.

Tip: If GTID (Global Transaction ID) is enabled, you don't need to specify MASTER_LOG_FILE and MASTER_LOG_POS. Instead, use CHANGE MASTER TO MASTER_AUTO_POSITION=1 and let MySQL handle transaction identification.

Verify That MySQL Replication Is Working Correctly

Once configuration is complete, use these commands to verify replication status:

# MySQL 5.7 and older
mysql -u root -p -e "SHOW SLAVE STATUS\G"

# MySQL 8.0.22+
mysql -u root -p -e "SHOW REPLICA STATUS\G"

Look for these critical fields in the output:

Slave_IO_Running or Replica_IO_Running — Must be Yes (slave successfully connects to master)
Slave_SQL_Running or Replica_SQL_Running — Must be Yes (slave successfully replays queries)
Seconds_Behind_Master — Should be 0 or a small number. If NULL, replication is not working.
Last_Error — Any errors will be displayed here

# Check only the critical status fields
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master)"

Tip: If Seconds_Behind_Master is greater than 0, the slave is lagging. Check the slave's server load, network latency, and consider hardware improvements or reducing write load on the master to keep replication synchronized.

Common Issues & Troubleshooting

Error: "Access denied for user 'repl_user'@'SLAVE_IP'"

The slave cannot authenticate to the master with the replication user. Check the following:

Is the password correct? Re-issue the GRANT command on the master with the new password.
Does the slave IP match the IP in the GRANT statement? The slave's external IP must match what you specified in 'repl_user'@'SLAVE_IP'.
Can the slave reach the master's network? Test with ping and nc (netcat) to verify port 3306 connectivity.

# On master, verify the user account
mysql -u root -p
SELECT user, host FROM mysql.user WHERE user='repl_user';

# Recreate the user with the correct password
DROP USER 'repl_user'@'SLAVE_IP';
CREATE USER 'repl_user'@'SLAVE_IP' IDENTIFIED BY 'repl_password_new';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'SLAVE_IP';
FLUSH PRIVILEGES;

Error: "Slave_IO_Running: No" and "Last_Error: I/O error reading"

The slave socket cannot connect to the master, or the master rejected the connection. Troubleshoot:

Is the master IP correct? Use telnet or nc to verify port 3306 is reachable.
Is MySQL running on the master? Check with systemctl status mysql.
Is a firewall blocking the connection? Review iptables, ufw, and any cloud provider security groups.
Is CHANGE MASTER TO configured correctly? Run SHOW SLAVE STATUS and verify MASTER_HOST and port.

# Test connectivity from slave to master
telnet MASTER_IP 3306
# or use netcat
nc -zv MASTER_IP 3306

# Reconfigure CHANGE MASTER TO
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='MASTER_IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password_123';
START SLAVE;

Error: "Slave_SQL_Running: No" and "Last_Error: Error executing query"

The slave is executing a query from the master but encounters an error such as a duplicate key or missing table. First, view the full error:

mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep Last_Error

Solutions depend on the error type:

For a duplicate key error, skip that one query temporarily:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

If a table is missing on the slave, you must re-sync the data. Create a fresh backup from the master.

Error: "Seconds_Behind_Master: NULL"

Replication is not working (either IO or SQL Running is No). Check SHOW SLAVE STATUS output to identify which component failed, then follow the troubleshooting steps above.

Error: "Relay log corruption detected"

The slave's relay log is corrupted or damaged. Restart the slave or delete the relay logs and reconfigure:

STOP SLAVE;
RESET SLAVE;
# Then reconfigure CHANGE MASTER TO and START SLAVE

Frequently Asked Questions

How does MySQL Replication differ from MariaDB Replication?

MariaDB is a MySQL fork that maintains compatibility with MySQL replication. Configuration files and most commands are identical. However, MariaDB has better parallelization for query replay and slightly different GTID implementation. If replicating from MySQL to MariaDB or vice versa, verify compatibility of the replication format.

What is the risk of a single master setup?

The master remains a single point of failure. If it goes down, the slave (with read_only=ON) cannot accept writes. Failover requires a manual procedure to promote the slave to master, or reverting to a backup, both causing downtime. For higher availability, consider multi-master replication or MySQL InnoDB Cluster.

Can I change binlog_format from ROW to STATEMENT?

You can, but with caution. STATEMENT format is unsafe for non-deterministic operations like NOW() and RAND(), while ROW format is safer but consumes more disk space and may be slower. Since data integrity is more important than performance, ROW format is recommended.