MySQL Replication Setup on VPS: Step-by-Step

What you will read?

1 Configuring the MySQL Master Server
2 Setting Up the MySQL Slave Server
3 Testing the Replication Setup

MySQL replication is a process that allows data from one MySQL database server (referred to as the master) to be copied automatically to one or more MySQL database servers (known as slaves). This feature is essential for improving data availability, load balancing, and ensuring redundancy in production environments

Configuring the MySQL Master Server

Setting up the master server is the first critical step in enabling MySQL replication. This involves configuring the server to log changes so that they can be transmitted to the slave servers. Follow these steps:

Edit the MySQL Configuration File:
- Locate the my.cnf or my.ini file.

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

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name

The server-id must be a unique identifier for the master server.
The log_bin directive enables binary logging.

Restart the MySQL Service:

Apply the configuration changes by restarting the MySQL service:

sudo systemctl restart mysql

Create a Replication User:

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

Obtain Master Status Information:

While in the MySQL shell, execute the following command to get the log file name and position:

SHOW MASTER STATUS;

Note the File and Position values; they will be needed to configure the slave server.

Setting Up the MySQL Slave Server

Setting Up the MySQL Slave Server
The slave server is configured to replicate data from the master server. Follow these steps to set up the slave server:

Edit the MySQL Configuration File:
- Locate the my.cnf or my.ini file on the slave server.
- Add or modify the following settings under the [mysqld] section:
```
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
```
  - - The server-id must be unique and different from the master server’s ID.

Restart the MySQL Service:

Apply the configuration changes by restarting the MySQL service:

sudo systemctl restart mysql

Connect to the MySQL Shell:

Configure the Slave to Connect to the Master:

Use the CHANGE MASTER TO command to specify the master server’s details:
```
CHANGE MASTER TO 
  MASTER_HOST='master_server_ip',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=120;
```
- - Replace master_server_ip, replicator, strong_password, mysql-bin.000001, and 120 with the appropriate values obtained from the master setup.

Start the Slave Process:

Start the replication process on the slave server:

START SLAVE;

Verify the Slave Status:

Check the replication status to ensure it’s working correctly:
```
SHOW SLAVE STATUS\G;
```

Ensure that Slave_IO_Running and Slave_SQL_Running are both set to Yes.

Testing the Replication Setup

Once the master and slave servers are configured, testing the replication setup ensures that data is being correctly copied from the master to the slave. Follow these steps:

Verify Slave Status:
- On the slave server, check the replication status:
```
SHOW SLAVE STATUS\G;
```

Ensure the following values:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If either value is No, investigate the error messages in the Last_IO_Error or Last_SQL_Error fields.

Insert Data on the Master Server:

USE your_database_name;
CREATE TABLE replication_test (id INT PRIMARY KEY, data VARCHAR(100));
INSERT INTO replication_test VALUES (1, 'Replication test data');

Check Data on the Slave Server:

Log in to the MySQL shell on the slave server and verify that the table and data are replicated:
```
USE your_database_name;
SELECT * FROM replication_test;
```

The output should match the data inserted on the master server.

Test Updates and Deletes:

On the master server, update or delete data in the test table:

UPDATE replication_test SET data = 'Updated test data' WHERE id = 1;
DELETE FROM replication_test WHERE id = 1;

Verify the changes on the slave server to confirm synchronization.

Monitor Ongoing Replication:

Test ongoing replication by inserting or modifying data on the master server and confirming its presence on the slave server.

Setting up MySQL replication on a VPS is a powerful method to enhance the availability, scalability, and redundancy of your database infrastructure. By carefully configuring the master and slave servers, testing the setup, and monitoring performance, you can ensure a reliable replication environment that supports your business or application needs.

MySQL Replication Setup on VPS: Step-by-Step

Table of Contents

Configuring the MySQL Master Server

Setting Up the MySQL Slave Server

Testing the Replication Setup

Related Posts