mysql replication - ghdrako/doc_snipets GitHub Wiki

MySQL supports several types of replications

  • Asynchronous replication - where the primary logs events (changes) to its binary log, and the replicas read this log asynchronously to make the same changes on their databases. The replicas do not need to confirm receipt of the data for the primary to continue processing transactions, which minimizes transaction latency on the primary.
  • Semi-synchronous replication - adds a layer of reliability over asynchronous replication. Here, the primary waits for at least one replica to acknowledge that it has received and logged the event before the transaction is considered complete. This setup ensures that no data is lost if the primary fails, assuming the replica has the transaction logged.
  • Group Replication - Introduced in MySQL 5.7, Group Replication provides a native way to implement a multi-primary setup within MySQL. It is a plugin that enables databases to be more fault-tolerant and supports automatic conflict detection and resolution among databases in the group.

Setting Up MySQL asynchronous replication

  • Controller configuration: On the primary server, you need to enable binary logging and configure a unique server ID. Modify the my.cnf file as follows:
[mysqld]
log-bin=mysql-bin
server-id=1
  • Replica configuration: On the replica server, also set a unique server ID and configure the connection to the primary. You need the primary log filename and the log position to start the replication:
[mysqld]
server-id=2
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='primary_ip', SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=107;
START REPLICA;

• Monitoring replication: Use the SHOW SLAVE STATUS command to monitor the replication status and check for any errors or lag.