MySQL replication is a powerful feature that allows data to be copied automatically from one database server (primary) to another (replica). This setup is critical for high availability, load balancing, and disaster recovery. This guide explains the configuration and management of MySQL replication.

Types of MySQL Replication

  1. Asynchronous Replication

    • Data is written to the primary server and sent to replicas as they process it.
    • Low-latency but not ideal for critical applications where data consistency is vital.

  2. Semi-Synchronous Replication

    • Guarantees that at least one replica confirms data receipt before the primary commits a transaction.
    • Balances speed and data safety.

  3. Group Replication

    • A multi-primary replication method where all servers can write data.
    • Ideal for distributed systems and high availability.

Setting Up MySQL Replication

1. Configure the Primary Server

Edit the MySQL configuration file (my.cnf or my.ini):

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

Restart the MySQL service:

bash
 
sudo systemctl restart mysql

Create a replication user with privileges:

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

Export the database state for replication:

bash
 
mysqldump -u root -p --all-databases --master-data > db_dump.sql

2. Configure the Replica Server

Edit the replica’s MySQL configuration file:

c
 
server-id = 2 relay_log = /var/log/mysql/mysql-relay-bin.log

Import the database dump into the replica:

bash
 
mysql -u root -p < db_dump.sql

Start replication with the following command:

sql
 
CHANGE MASTER TO MASTER_HOST='primary_server_ip', MASTER_USER='replicator', MASTER_PASSWORD='secure_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120; START SLAVE;

Monitoring and Managing Replication

  • Check Replica Status:

    sql
     
    SHOW SLAVE STATUS\G;

    Look for fields like Seconds_Behind_Master and Slave_IO_Running.

  • Restarting Replication:
    If replication stops due to errors, use:

    sql
     
    STOP SLAVE; START SLAVE;

  • Handling Errors:
    Skip problematic transactions:

    sql
     
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

Best Practices for MySQL Replication

  1. Use GTID-Based Replication: Global Transaction Identifiers (GTIDs) simplify failover and consistency management.
  2. Secure Replication: Always use SSL/TLS for replication traffic to protect data in transit.
  3. Regular Backups: Back up primary and replica servers regularly to ensure disaster recovery.
  4. Monitor Lag: Use monitoring tools like Percona Monitoring and Management (PMM) to track replication lag.

Common Issues and Troubleshooting

  • Replication Lag: Optimize queries and increase resources on replicas.
  • Broken Replication: Rebuild replication if logs are inconsistent or missing.
  • High Disk Usage: Rotate and purge binary logs regularly:
    sql
     
    PURGE BINARY LOGS TO 'mysql-bin.000010';

Need Assistance?

For advanced replication setup and troubleshooting, contact Cybrohosting’s support team. Open a ticket in your Client Area or email us at support@cybrohosting.com.

Hjälpte svaret dig? 0 användare blev hjälpta av detta svar (0 Antal röster)

Mest populär

How to Use MySQL Views for Simplifying Complex Queries

MySQL Views are a powerful tool that lets you simplify complex queries by creating a virtual...
How to Backup and Restore MySQL Databases

Regular backups are critical to protect your MySQL databases from data loss due to server...
How to Optimize MySQL Query Performance

Optimizing queries is crucial for ensuring efficient database performance, especially for...
Optimizing MySQL Databases for Performance

Efficient database performance is critical for high-traffic websites and applications. This guide...
Sharding in MySQL: Scaling Databases for High Performance

As databases grow, traditional scaling methods like vertical scaling (adding resources to a...