How to Set Up Master-Slave Replication

Prerequisites

Before setting up replication, make sure you have:

• Two VPS servers (master and slave)
• MySQL/MariaDB installed on both
• Root or sudo privileges on both
• Network connectivity between servers

Step 1: Configure the Master Server

Connect to your master VPS:

ssh hxroot@MASTER_IP -p 22

Edit MySQL config:

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

Add or modify:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database
binlog_do_db = another_database
bind-address = 0.0.0.0

Restart MySQL:

sudo systemctl restart mysql

Step 2: Create Replication User on Master

sudo mysql

CREATE USER 'replicator'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note the File and Position values (e.g., mysql-bin.000001, 12345).

Keep this session open.

Step 3: Backup and Transfer Data to Slave

In another terminal, export master data (while locked):

mysqldump --all-databases --master-data > /tmp/master.sql

Transfer to slave:

scp /tmp/master.sql root@SLAVE_IP:/tmp/

Back on master session, unlock tables:

UNLOCK TABLES;

Step 4: Configure the Slave Server

Connect to slave VPS:

ssh hxroot@SLAVE_IP -p 22

Import data:

mysql < /tmp/master.sql

Edit slave MySQL config:

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

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1

Restart MySQL:

sudo systemctl restart mysql

Step 5: Start Replication on Slave

sudo mysql

CHANGE MASTER TO
    MASTER_HOST='MASTER_IP',
    MASTER_USER='replicator',
    MASTER_PASSWORD='strong_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=12345;

START SLAVE;

Step 6: Verify Replication

SHOW SLAVE STATUSG

Look for:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Test Replication

On master:

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test (id INT);

On slave:

SHOW DATABASES;  # Should show test_replication

Troubleshooting

If replication fails, check:

SHOW SLAVE STATUSG | grep "Last_Error"

Restart replication after fixing:

STOP SLAVE;
START SLAVE;

Reset Replication (Start Over)

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO ... (as above)
START SLAVE;