How to Set Up MySQL Master-Slave Replication?

In MySQL database management, master-slave replication setup is an indispensable method, especially for high-traffic websites and critical applications. With this structure, all data written to the master database is instantly copied to one or more slave databases. So, how is this replication achieved? Here is a step-by-step guide.

Why Master-Slave Replication?

One of the biggest advantages of master-slave replication is distributing the read load across slave servers, reducing the pressure on the master. For example, in an e-commerce site, read operations like product listing are handled by slaves, while write operations like order creation remain on the master. Additionally, if the master server crashes, you can quickly promote a slave to master for uninterrupted service.

Preparing the Master Server

First, configure the master server for replication. Open the MySQL configuration file (usually /etc/mysql/my.cnf or /etc/my.cnf) and add the following parameters:

• server-id: 1 (a unique ID for the master)
• log-bin: /var/log/mysql/mysql-bin.log (path to binary log file)
• binlog-do-db: name of the database to replicate (optional)

Example configuration:

[mysqld] server-id = 1 log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = myapp

Then restart MySQL: sudo systemctl restart mysql

Creating a Replication User

On the master server, create a user that the slave will use to connect. Connect to MySQL as root and run the following commands:

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

Here, '%' allows connections from any IP; for security, it is recommended to specify a particular IP address.

Retrieving Master Status Information

Obtain the master's binary log file name and position for use during slave setup:

SHOW MASTER STATUS;

Note the File and Position values from the output. For example: File: mysql-bin.000001, Position: 107

Preparing the Slave Server

Now switch to the slave server. Add the following lines to its configuration file:

• server-id: 2 (must be different from the master)
• relay-log: /var/log/mysql/mysql-relay-bin.log

Example:

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

Restart MySQL.

Setting Up the Slave Connection

Using the information obtained from the master, connect the slave to the master. Connect to MySQL as root and run the following command:

CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='replica', MASTER_PASSWORD='strong_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

Then start the slave:

START SLAVE;

To check replication status:

SHOW SLAVE STATUS\G;

In the output, Slave_IO_Running and Slave_SQL_Running should both be Yes. Otherwise, inspect the error message.

Common Issues and Solutions

• Firewall blocking: Ensure port 3306 on the master server is accessible from the slave.
• server-id conflict: Use unique server-id values across all servers.
• Binary log position mismatch: If the slave already contains old data, take a dump from the master, load it onto the slave, then connect with the correct position.

Important: When setting up replication on live systems, always take backups and perform the operation during low traffic hours.

Conclusion

Master-slave replication is a fundamental database management technique that we at HostingServer.com.tr recommend to our clients. This setup enhances application performance and reduces the risk of data loss. Remember to regularly check replication status after setup. With proper configuration and monitoring, MySQL replication can save you from major headaches.