How to set up MySQL replication on Linux
How to Set Up MySQL Replication on Linux
MySQL replication is a powerful feature that allows you to maintain multiple copies of your database across different servers, providing redundancy, improved performance, and scalability for your applications. This comprehensive guide will walk you through the complete process of setting up MySQL replication on Linux systems, from basic configuration to advanced troubleshooting techniques.
Table of Contents
1. [Understanding MySQL Replication](#understanding-mysql-replication)
2. [Prerequisites and Requirements](#prerequisites-and-requirements)
3. [Planning Your Replication Setup](#planning-your-replication-setup)
4. [Configuring the Master Server](#configuring-the-master-server)
5. [Configuring the Slave Server](#configuring-the-slave-server)
6. [Starting Replication](#starting-replication)
7. [Testing and Verification](#testing-and-verification)
8. [Monitoring Replication Status](#monitoring-replication-status)
9. [Common Issues and Troubleshooting](#common-issues-and-troubleshooting)
10. [Best Practices and Security](#best-practices-and-security)
11. [Advanced Configuration Options](#advanced-configuration-options)
12. [Conclusion](#conclusion)
Understanding MySQL Replication
MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). This asynchronous replication mechanism provides several benefits:
- High Availability: If the master server fails, you can promote a slave to become the new master
- Load Distribution: Read queries can be distributed across multiple slave servers
- Backup Solutions: Slaves can serve as live backups without impacting master performance
- Data Analytics: Reporting and analytics can run on slaves without affecting production workloads
Types of MySQL Replication
1. Asynchronous Replication: The default mode where the master doesn't wait for slaves to confirm receipt of data
2. Semi-synchronous Replication: Master waits for at least one slave to acknowledge receipt before committing
3. Synchronous Replication: All slaves must acknowledge before the master commits (available in MySQL Cluster)
Prerequisites and Requirements
Before setting up MySQL replication, ensure you have the following prerequisites in place:
System Requirements
- Two or more Linux servers (physical or virtual)
- MySQL 5.7 or higher installed on all servers
- Network connectivity between all servers
- Sufficient disk space for binary logs and relay logs
- Root or sudo access on all servers
Network Configuration
- Open MySQL port (default 3306) between master and slave servers
- Stable network connection with low latency
- Firewall rules configured to allow MySQL traffic
MySQL User Privileges
You'll need administrative privileges to:
- Modify MySQL configuration files
- Create replication users
- Restart MySQL services
- Execute replication commands
Planning Your Replication Setup
Server Identification
For this guide, we'll use the following example setup:
- Master Server: 192.168.1.10 (hostname: mysql-master)
- Slave Server: 192.168.1.11 (hostname: mysql-slave)
Replication Strategy
Consider these factors when planning your replication:
- Replication Format: Statement-based, row-based, or mixed
- Binary Log Format: Choose based on your application needs
- Filtering Options: Replicate all databases or specific ones
- SSL Encryption: Enable for secure data transmission
Configuring the Master Server
Step 1: Modify MySQL Configuration
Edit the MySQL configuration file on the master server:
```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Add or modify the following parameters in the `[mysqld]` section:
```ini
[mysqld]
Server ID - must be unique across all servers in replication setup
server-id = 1
Enable binary logging
log-bin = mysql-bin
Binary log format (ROW, STATEMENT, or MIXED)
binlog-format = ROW
Databases to replicate (optional - omit to replicate all databases)
binlog-do-db = your_database_name
Databases to ignore (optional)
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
Binary log retention period (days)
expire_logs_days = 7
Maximum binary log file size
max_binlog_size = 100M
Enable GTID for easier failover management (MySQL 5.6+)
gtid_mode = ON
enforce_gtid_consistency = ON
```
Step 2: Restart MySQL Service
Restart the MySQL service to apply configuration changes:
```bash
sudo systemctl restart mysql
```
Verify the service is running:
```bash
sudo systemctl status mysql
```
Step 3: Create Replication User
Connect to MySQL as root and create a dedicated replication user:
```bash
mysql -u root -p
```
Execute the following SQL commands:
```sql
-- Create replication user
CREATE USER 'replication_user'@'192.168.1.11' IDENTIFIED BY 'strong_password_here';
-- Grant replication privileges
GRANT REPLICATION SLAVE ON . TO 'replication_user'@'192.168.1.11';
-- Apply privileges
FLUSH PRIVILEGES;
-- Check binary log status
SHOW MASTER STATUS;
```
Important: Note down the `File` and `Position` values from `SHOW MASTER STATUS` - you'll need these for slave configuration.
Example output:
```
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
```
Step 4: Create Database Backup (Optional)
If you have existing data, create a backup to initialize the slave:
```bash
Create backup with binary log position
mysqldump -u root -p --all-databases --master-data=2 --single-transaction > master_backup.sql
Transfer backup to slave server
scp master_backup.sql user@192.168.1.11:/tmp/
```
Configuring the Slave Server
Step 1: Modify MySQL Configuration
Edit the MySQL configuration file on the slave server:
```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Add or modify these parameters:
```ini
[mysqld]
Unique server ID
server-id = 2
Enable relay logging
relay-log = mysql-relay-bin
Read-only mode (optional, recommended for slaves)
read-only = 1
Skip slave start on boot (recommended for initial setup)
skip-slave-start
Relay log recovery
relay_log_recovery = ON
Enable GTID (must match master)
gtid_mode = ON
enforce_gtid_consistency = ON
Slave parallel execution (MySQL 5.7+)
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
```
Step 2: Restart MySQL Service
```bash
sudo systemctl restart mysql
sudo systemctl status mysql
```
Step 3: Import Master Data (If Applicable)
If you created a backup from the master, import it:
```bash
mysql -u root -p < /tmp/master_backup.sql
```
Step 4: Configure Slave Connection
Connect to MySQL on the slave server:
```bash
mysql -u root -p
```
Configure the slave to connect to the master:
```sql
-- Stop slave if running
STOP SLAVE;
-- Configure master connection
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replication_user',
MASTER_PASSWORD='strong_password_here',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
-- Alternative: Using GTID (if enabled)
-- CHANGE MASTER TO
-- MASTER_HOST='192.168.1.10',
-- MASTER_USER='replication_user',
-- MASTER_PASSWORD='strong_password_here',
-- MASTER_AUTO_POSITION=1;
```
Starting Replication
Step 1: Start Slave Process
On the slave server, start replication:
```sql
START SLAVE;
```
Step 2: Verify Slave Status
Check the slave status immediately after starting:
```sql
SHOW SLAVE STATUS\G
```
Look for these key indicators of successful replication:
- `Slave_IO_Running: Yes`
- `Slave_SQL_Running: Yes`
- `Last_Error:` (should be empty)
- `Seconds_Behind_Master: 0` (or a small number)
Example output:
```
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
```
Testing and Verification
Step 1: Create Test Database
On the master server, create a test database and table:
```sql
-- Create test database
CREATE DATABASE replication_test;
USE replication_test;
-- Create test table
CREATE TABLE test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert test data
INSERT INTO test_table (message) VALUES
('First replication test'),
('Second replication test'),
('Third replication test');
```
Step 2: Verify Replication
On the slave server, check if the data was replicated:
```sql
-- Switch to test database
USE replication_test;
-- Check if table exists and contains data
SELECT * FROM test_table;
```
You should see the same data that was inserted on the master.
Step 3: Test Real-time Replication
On the master, insert additional data:
```sql
INSERT INTO replication_test.test_table (message) VALUES ('Real-time replication test');
```
Immediately check the slave:
```sql
SELECT * FROM replication_test.test_table ORDER BY id DESC LIMIT 1;
```
The new record should appear on the slave within seconds.
Monitoring Replication Status
Key Metrics to Monitor
Regular monitoring is essential for maintaining healthy replication. Monitor these key metrics:
On the Master Server
```sql
-- Check binary log status
SHOW MASTER STATUS;
-- List connected slaves
SHOW PROCESSLIST;
-- Check binary log files
SHOW BINARY LOGS;
```
On the Slave Server
```sql
-- Comprehensive slave status
SHOW SLAVE STATUS\G
-- Check relay log status
SHOW RELAYLOG EVENTS;
-- Monitor replication lag
SELECT
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag_seconds
FROM (
SELECT FROM_UNIXTIME(
VARIABLE_VALUE,
'%Y-%m-%d %H:%i:%s'
) AS ts
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Slave_last_heartbeat'
) t;
```
Automated Monitoring Script
Create a monitoring script to check replication health:
```bash
#!/bin/bash
replication_monitor.sh
MYSQL_USER="root"
MYSQL_PASS="your_password"
SLAVE_HOST="192.168.1.11"
Check slave status
SLAVE_STATUS=$(mysql -h $SLAVE_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null)
Extract key values
IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
LAST_ERROR=$(echo "$SLAVE_STATUS" | grep "Last_Error:" | cut -d':' -f2- | xargs)
Check replication health
if [ "$IO_RUNNING" = "Yes" ] && [ "$SQL_RUNNING" = "Yes" ]; then
if [ "$SECONDS_BEHIND" = "NULL" ] || [ "$SECONDS_BEHIND" -gt 60 ]; then
echo "WARNING: Replication lag is $SECONDS_BEHIND seconds"
else
echo "OK: Replication is healthy (lag: ${SECONDS_BEHIND}s)"
fi
else
echo "CRITICAL: Replication is broken - IO:$IO_RUNNING SQL:$SQL_RUNNING"
if [ -n "$LAST_ERROR" ]; then
echo "Error: $LAST_ERROR"
fi
fi
```
Common Issues and Troubleshooting
Issue 1: Slave Cannot Connect to Master
Symptoms:
- `Slave_IO_Running: No`
- Error: "Can't connect to MySQL server on 'master_host'"
Solutions:
1. Check network connectivity:
```bash
Test connection from slave to master
telnet 192.168.1.10 3306
or
nc -zv 192.168.1.10 3306
```
2. Verify firewall settings:
```bash
On master server, allow MySQL port
sudo ufw allow from 192.168.1.11 to any port 3306
or
sudo iptables -A INPUT -p tcp -s 192.168.1.11 --dport 3306 -j ACCEPT
```
3. Check MySQL bind address:
```bash
In MySQL config, ensure MySQL binds to all interfaces
bind-address = 0.0.0.0
```
Issue 2: Authentication Errors
Symptoms:
- Error: "Access denied for user 'replication_user'"
Solutions:
1. Verify user exists and has correct privileges:
```sql
-- On master server
SELECT User, Host FROM mysql.user WHERE User = 'replication_user';
SHOW GRANTS FOR 'replication_user'@'192.168.1.11';
```
2. Recreate replication user if necessary:
```sql
DROP USER 'replication_user'@'192.168.1.11';
CREATE USER 'replication_user'@'192.168.1.11' IDENTIFIED BY 'new_strong_password';
GRANT REPLICATION SLAVE ON . TO 'replication_user'@'192.168.1.11';
FLUSH PRIVILEGES;
```
Issue 3: Binary Log Position Mismatch
Symptoms:
- `Slave_IO_Running: No`
- Error: "Got fatal error 1236 from master when reading data from binary log"
Solutions:
1. Reset slave position:
```sql
-- On slave
STOP SLAVE;
RESET SLAVE;
-- Get current master status
-- (Execute on master)
SHOW MASTER STATUS;
-- Reconfigure slave with new position
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;
START SLAVE;
```
Issue 4: Replication Lag
Symptoms:
- `Seconds_Behind_Master` consistently high
- Data appears on slave with significant delay
Solutions:
1. Enable parallel replication (MySQL 5.7+):
```sql
-- On slave
STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
START SLAVE SQL_THREAD;
```
2. Optimize slave hardware:
- Increase RAM for buffer pools
- Use faster storage (SSD)
- Ensure adequate CPU resources
3. Check for long-running queries:
```sql
-- On slave
SHOW PROCESSLIST;
-- Look for queries in "Waiting for" states
```
Issue 5: Duplicate Key Errors
Symptoms:
- `Slave_SQL_Running: No`
- Error: "Duplicate entry 'value' for key 'PRIMARY'"
Solutions:
1. Skip the problematic transaction (use with caution):
```sql
-- On slave
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
```
2. For GTID-based replication:
```sql
-- Identify problematic GTID
SHOW SLAVE STATUS\G
-- Skip specific GTID
SET GTID_NEXT = 'problematic_gtid_here';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;
```
Best Practices and Security
Security Best Practices
1. Use SSL/TLS Encryption:
```sql
-- Configure SSL on master
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
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';
```
2. Restrict Replication User Access:
```sql
-- Create user with minimal required privileges
CREATE USER 'replication_user'@'specific_slave_ip' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON . TO 'replication_user'@'specific_slave_ip';
```
3. Use Strong Passwords:
- Minimum 12 characters
- Mix of uppercase, lowercase, numbers, and symbols
- Avoid dictionary words
Performance Optimization
1. Binary Log Configuration:
```ini
Optimize binary log settings
binlog_cache_size = 1M
max_binlog_cache_size = 2G
sync_binlog = 1
```
2. Slave Configuration:
```ini
Optimize slave performance
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON
slave_parallel_workers = 4
```
3. Monitor and Tune Buffer Sizes:
```ini
Key buffer optimizations
innodb_buffer_pool_size = 70% of available RAM
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
```
Maintenance Procedures
1. Regular Binary Log Cleanup:
```sql
-- Purge old binary logs
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);
-- Or purge logs before specific file
PURGE BINARY LOGS TO 'mysql-bin.000010';
```
2. Monitor Disk Space:
```bash
Check binary log disk usage
du -sh /var/lib/mysql/mysql-bin.*
Check relay log disk usage
du -sh /var/lib/mysql/relay
```
3. Regular Replication Testing:
```bash
Create automated test script
#!/bin/bash
TEST_DB="replication_test"
TIMESTAMP=$(date +%s)
Insert test data on master
mysql -u root -p -e "INSERT INTO $TEST_DB.test_table (message) VALUES ('Test $TIMESTAMP')"
Wait and verify on slave
sleep 2
RESULT=$(mysql -h slave_host -u root -p -e "SELECT COUNT(*) FROM $TEST_DB.test_table WHERE message='Test $TIMESTAMP'" -N)
if [ "$RESULT" -eq 1 ]; then
echo "Replication test PASSED"
else
echo "Replication test FAILED"
fi
```
Advanced Configuration Options
Multi-Master Replication
For advanced setups, you can configure multi-master replication:
```sql
-- On both servers, enable auto-increment offset
SET GLOBAL auto_increment_increment = 2;
SET GLOBAL auto_increment_offset = 1; -- Use 2 on second master
```
Filtered Replication
Configure selective replication for specific databases or tables:
```ini
Master configuration - only log specific databases
binlog-do-db = production_db
binlog-do-db = user_data
Slave configuration - only replicate specific tables
replicate-do-table = production_db.users
replicate-do-table = production_db.orders
replicate-ignore-table = production_db.logs
```
GTID-Based Replication
For easier failover management, use GTID (Global Transaction Identifier):
```ini
Enable GTID on both master and slave
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
```
```sql
-- Configure slave with GTID auto-positioning
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1;
```
Conclusion
Setting up MySQL replication on Linux provides a robust foundation for high availability, scalability, and disaster recovery. This comprehensive guide has covered everything from basic configuration to advanced troubleshooting techniques.
Key Takeaways
1. Proper Planning: Always plan your replication topology and requirements before implementation
2. Security First: Use SSL encryption and restrict user privileges appropriately
3. Monitor Continuously: Implement automated monitoring to catch issues early
4. Test Regularly: Perform regular replication tests to ensure everything works as expected
5. Document Everything: Keep detailed records of your configuration and procedures
Next Steps
After successfully implementing MySQL replication, consider these advanced topics:
- Implementing MySQL Cluster for synchronous replication
- Setting up ProxySQL for intelligent query routing
- Configuring MySQL Router for automatic failover
- Implementing backup strategies using slaves
- Setting up monitoring with tools like Nagios, Zabbix, or Prometheus
Additional Resources
- MySQL Official Documentation on Replication
- MySQL Performance Tuning guides
- Community forums and support channels
- Professional MySQL training and certification programs
By following this guide and implementing the best practices outlined, you'll have a reliable MySQL replication setup that can serve your organization's needs for high availability and scalability. Remember to regularly review and update your configuration as your requirements evolve and new MySQL versions become available.