MySQL Replication

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

There are 2 types of replication setup.

• Using binary log file positions
• Using Global Transaction Identifiers

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.

Main DB configuration

[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'@'%';

Replica DB configuration

[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;

Replication with GTID

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

The other configuration

[Replication format]

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

TODOs

• Add log_slave_updates
• Add binlog_row_image

• 2024 2
• 2023 20
• 2022 11
• 2021 12
• 2020 21
• 2019 5