Setup MySQL Master-Slave Replication on Ubuntu VPS: Complete Guide

Understanding MySQL Master-Slave Replication for VPS Hosting

MySQL replication creates copies of your database across multiple servers. The master handles writes while slaves maintain synchronized read-only copies.

You get backup redundancy and distributed read traffic across multiple nodes.

For Hostperl VPS customers, master-slave replication addresses critical hosting needs. You gain automatic backup sync, better read performance, and failover protection.

The setup requires two Ubuntu VPS instances with MySQL installed.

Prerequisites and Server Preparation

Both servers need MySQL 8.0 or higher, unique server IDs, and network connectivity between nodes.

Update both servers and install required packages:

sudo apt update
sudo apt install mysql-server mysql-client
sudo systemctl enable mysql
sudo systemctl start mysql

Verify matching MySQL versions on both servers. Version mismatches cause replication failures and data inconsistencies.

Configure MySQL Master Server Settings

The master needs specific configuration changes. Open /etc/mysql/mysql.conf.d/mysqld.cnf and add these settings under the [mysqld] section:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = your_database_name
max-binlog-size = 100M
expire-logs-days = 7

Each server-id must be unique across all replication participants. Binary logging captures database changes for slave synchronization.

ROW format ensures data consistency across different MySQL versions.

Restart MySQL to apply changes:

sudo systemctl restart mysql

Create Replication User Account

Masters need dedicated user accounts for slave connections. This account requires REPLICATION SLAVE privileges and secure authentication.

Log into MySQL as root and create the replication user:

mysql -u root -p

CREATE USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY 'secure_password_2026';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

Replace the IP range with your slave server's specific address for better security. Avoid wildcard permissions in production.

Lock Tables and Export Master Data

Create a consistent snapshot of master data before configuring slaves. Lock tables to prevent writes during export:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Record the binary log file name and position from SHOW MASTER STATUS output. You'll need these values for slave configuration.

Open a new terminal and export the database without unlocking tables:

mysqldump -u root -p --opt --single-transaction --routines --triggers --all-databases > master_backup.sql

The --single-transaction flag ensures data consistency for InnoDB tables.

After export completes, unlock tables in the first MySQL session:

UNLOCK TABLES;

Setup MySQL Master-Slave Replication on Ubuntu VPS: Slave Configuration

Slaves need different configuration parameters. Edit /etc/mysql/mysql.conf.d/mysqld.cnf on the slave server:

[mysqld]
server-id = 2
relay-log = mysql-relay-log
log-bin = mysql-bin
read-only = 1

The read-only setting prevents accidental writes to slave databases. Users can still modify temp tables and perform admin tasks.

Restart MySQL on the slave:

sudo systemctl restart mysql

Import Master Data to Slave

Transfer the database backup from master to slave server. Use scp or your preferred transfer method:

scp master_backup.sql user@slave_server_ip:/tmp/

Import the backup into the slave MySQL instance:

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

This restores the master's data state at export time. Large databases may take several minutes to import.

Start Slave Replication Process

Configure the slave to connect to the master server. Log into MySQL on the slave and run the CHANGE MASTER command:

CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='secure_password_2026',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

Replace log file and position values with those from SHOW MASTER STATUS.

Start the slave processes:

START SLAVE;

Check replication status to verify connection success:

SHOW SLAVE STATUS\G

Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes. Any errors appear in the Last_Error field.

Test Replication Functionality

Verify replication by creating test data on the master. Connect to the master server and create a test database:

CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE test_data (id INT AUTO_INCREMENT PRIMARY KEY, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP);
INSERT INTO test_data VALUES (NULL, NOW());

Check the slave server for replicated data:

USE replication_test;
SELECT * FROM test_data;

Data should appear on the slave within seconds. Replication lag indicates network issues or slave performance problems.

Monitor Replication Health

Regular monitoring prevents unnoticed replication failures. Check slave status daily and monitor key metrics:

SHOW SLAVE STATUS\G
SELECT * FROM performance_schema.replication_connection_status;

Critical metrics include Seconds_Behind_Master for lag monitoring and Last_Error for failure detection.

Our email monitoring setup tutorial shows how to configure alerts for replication issues.

Troubleshoot Common Replication Problems

Network connectivity issues cause most replication failures. Verify firewall rules allow MySQL traffic on port 3306 between servers.

Test connectivity from slave to master:

telnet master_server_ip 3306

Binary log corruption requires careful recovery. Stop slave processes and re-sync from the current master position:

STOP SLAVE;
SHOW MASTER STATUS;
# Note new position
CHANGE MASTER TO MASTER_LOG_FILE='new_file', MASTER_LOG_POS=new_position;
START SLAVE;

Disk space problems on the master can break replication. Monitor log file sizes and configure automatic purging with expire-logs-days settings.

Security and Access Control

Replication security requires careful network access and user permissions. Create dedicated replication users with minimal privileges.

Restrict access by source IP:

CREATE USER 'replica_user'@'192.168.1.100' IDENTIFIED BY 'strong_password_2026';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.1.100';

Use SSL encryption for replication traffic in production. Configure SSL certificates and modify the CHANGE MASTER command:

CHANGE MASTER TO
MASTER_SSL=1,
MASTER_SSL_CA='/path/to/ca-cert.pem',
MASTER_SSL_CERT='/path/to/client-cert.pem',
MASTER_SSL_KEY='/path/to/client-key.pem';

Frequently Asked Questions

How much replication lag is acceptable for web hosting?

Most web applications tolerate 1-5 seconds of replication lag. E-commerce sites requiring immediate inventory updates need near-zero lag or synchronous replication.

Can I replicate only specific databases instead of all data?

Yes, use the binlog-do-db directive on the master and replicate-do-db on slaves to filter specific databases. This reduces network traffic and slave storage requirements.

What happens if the slave server fails?

Slave failures don't affect master operations. Applications continue reading from the master until you restore the slave or reconfigure load balancing.

How do I promote a slave to master during failover?

Stop slave processes, remove read-only restrictions, and update application configurations to point to the new master server. Document this procedure for emergency situations.

Should I run MySQL replication across different data centers?

Cross-datacenter replication works but increases latency. Use asynchronous replication and monitor network connectivity carefully. Consider the impact of network partitions on application availability.