Master-Slave Replication Steps and Caution

Master-Slave Replication Configuration

Introduction

Master-slave replication is a fundamental feature in MySQL that ensures data consistency and high availability by replicating data from a primary (master) server to one or more secondary (slave) servers. This process allows for fault tolerance, load balancing, and data redundancy.

Setup

Before configuring master-slave replication, ensure the following:

• Both servers are running MySQL.
• The master server is configured to enable binary logging.
• The slave server has access to the master server’s IP address and port.
• Firewalls or network policies allow communication between the master and slave.

Configuration Steps

1. Enable Binary Logging on the Master

The master server must enable binary logging to record changes in the database.

-- On the master server  
SET GLOBAL log_bin = 'ON';  
SHOW VARIABLES LIKE 'log_bin';  

2. Create a Replication User

A dedicated user is created to manage replication.

-- On the master server  
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'StrongPassword123';  
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';  
FLUSH PRIVILEGES;  

3. Configure the Slave to Connect to the Master

The slave server is configured to connect to the master’s binary log.

-- On the slave server  
CHANGE MASTER TO  
MASTER_HOST='master_ip',  
MASTER_USER='replication_user',  
MASTER_PASSWORD='StrongPassword123',  
MASTER_LOG_FILE='mysql-bin.001';  
START SLAVE;  

4. Set Up the Replication Filter

The replication filter determines which databases and tables are replicated.

-- On the slave server  
CHANGE MASTER TO  
MASTER_LOG_FILE='mysql-bin.001',  
MASTER_SQL_LOG_FILE='mysql-bin.001';  

5. Enable Binary Logging on the Slave

The slave must enable binary logging to apply changes from the master.

-- On the slave server  
SET GLOBAL log_bin = 'ON';  
SHOW VARIABLES LIKE 'log_bin';  

Caution Points

1. Verify Master Server Status

Always confirm the master server is running and the binary log is active.

mysql -u root -p -e "SHOW MASTER STATUS;"  

2. Use Correct Credentials

Ensure the replication user has the correct credentials and permissions.

SHOW GRANTS FOR 'replication_user'@'%';  

3. Network Configuration

Ensure the slave server can reach the master server’s IP address and port.

telnet master_ip 3306  

4. Monitor Replication

Regularly check replication status and logs for errors.

SHOW SLAVE STATUS\G  

5. Avoid Common Pitfalls

• Do not use the same user for replication and daily operations.
• Do not enable binary logging on the slave server unless necessary.
• Ensure the slave server has the same MySQL version as the master.

Conclusion

Master-slave replication requires careful configuration to ensure data consistency and availability. By following the outlined steps and adhering to caution points, administrators can build a reliable replication setup. Regular monitoring and testing are essential to maintain system integrity and performance.