MySQL Replication

MySQL Replication

November 5, 2021

This configuration is for the version 5.7 and it’s minimum configuration in the official document.

There are 2 types of replication setup.

Binary logging replication

There are a couple of important configurations

server-id: The unique ID on each server, and must be a positive integer between 1 and 2^32-1. The default value is 0.
log-bin: the file name for binary logs. This is required to enable replications using binary loggings. Binary loggings are not required on read replicas, but they can be used for data backups and crash recovery.

Besides,

Create a user who can connect to the main db from each replica and grant REPLICATION SLAVE permission to them. Note that it’s better to create a separate user because this username and password are stored in plain text in the replication metadata repositories.

[mysqld]
server-id = 1
log-bin = mysql-bin

Create a user for replication.

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

[mysqld]
server-id = 11
log-bin = mysql-bin

In order to connect to the main DB,

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='$mysql_main_server',
    MASTER_USER='repl',
    MASTER_PASSWORD='password';
START SLAVE;

Next YouTube video may be helpful to understand the operation and configuration on GTID replication

There are a couple of configurations to enable GTID based replication

gtid_mode = on: GTID based logging is enabled
enforce_gtid_consistency: allow execution of only statements that can be safely logged using a GTID

See an official document for the details. This can be configured as a binlog_format in a configuration file.

Statement-based replication (SBR) (binlog_format=STATEMENT)
- Some statements cannot be replicated
- Less data
- If replicas have different triggers from main DB, then a SBR is required. (Ref)
Row-based replication (RBR) (binlog_format=ROW)
- All changes can be replicated
- More data
Mixed (binlog_format=MIXED)
- Use statement-based replication as default
- Use row-based replication only if the statement-based replication cannot guarantee the proper result

Last updated on January 25, 2025