Menu
MySQL

MySQL replication

MySQL replication is the process by which data from one MySQL server (called the master) is automatically copied to one or more MySQL servers (called slaves). This enables a range of use cases like high availability, scalability, and load balancing.

 

 

1. Types of MySQL Replication

  1. Master-Slave Replication: One master replicating to one or more slaves.
  2. Master-Master Replication: Both servers act as masters, replicating to each other. Useful for high availability but may lead to conflicts.
  3. Circular Replication: A multi-master setup where each server is both a master and a slave.Circular Replication: A multi-master setup where each server is both a master and a slave.
  4. Multi-Source Replication: A slave can replicate from multiple masters, allowing for merging data from different sources.

 

 

 

2. Setting Up MySQL Replication

Step 1: Master Configuration

  • Enable Binary Logging: Edit the MySQL configuration file (my.cnf or my.ini on Windows) on the master server to enable binary logging and set a unique server ID.
[mysqld]
log-bin = mysql-bin
server-id = 1  # Unique ID for the master
  • Create a Replication User: Create a user on the master that will be used by the slave to connect and replicate data.
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
  • Lock the Master and Get the Binary Log Coordinates: To ensure consistency, lock the master during the backup.
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Take note of the binary log file (File) and the log position (Position) returned by SHOW MASTER STATUS.

  • Backup the Master Database: You can now take a backup of the master database using mysqldump or another backup tool while the tables are locked.
mysqldump -u root -p --all-databases --master-data > master_backup.sql
  • Unlock the Master: After the backup is completed, you can unlock the master tables.
UNLOCK TABLES;

 

 

Step 2: Slave Configuration

  • Configure the Slave: Edit the MySQL configuration file (my.cnf) on the slave server and set a unique server-id. Ensure it’s different from the master and other slaves.
[mysqld]
server-id = 2  # Unique ID for the slave
  • Restore the Master Backup: Restore the backup that you created from the master onto the slave server.
mysql -u root -p < master_backup.sql
  • Configure the Slave to Connect to the Master: On the slave, set up replication by specifying the master server's host, user, password, binary log file, and position.
CHANGE MASTER TO
MASTER_HOST = 'master_host_ip',
MASTER_USER = 'replica_user',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 12345;
  • Start the Slave: Start the replication process on the slave.
START SLAVE;
  • Check the Slave Status: To verify that replication is working, check the slave status.
SHOW SLAVE STATUS \G;

Look for the following lines:

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

If both are "Yes", replication is working correctly.

 

 

3. Advantages of MySQL Replication

  • High Availability: In case of a master failure, you can promote a slave to act as the new master.
  • Load Balancing: You can distribute read queries across multiple slaves to balance the load and reduce the master’s workload.
  • Backup Solutions: Use replication to take backups from the slave without affecting the performance of the master.

 

 

4. Drawbacks of MySQL Replication

  • Eventual Consistency: Replication is asynchronous by default, so slaves may lag behind the master.
  • Conflict Resolution: In master-master replication, conflicts can occur if both masters are updating the same data simultaneously.

 

 

5. Advanced MySQL Replication Features

  • GTID-Based Replication: MySQL also supports replication using Global Transaction Identifiers (GTIDs), which simplifies failover processes.
  • Multi-Source Replication: You can replicate data from multiple masters into a single slave, allowing for data consolidation from different sources.

 

 

 

 

MySQL