MySQL 8, binary log file position based replication. Getting started, Quick setup

Introduction

In this paper, 2 MySQL databases are replicated using binary log file position based replication : sqlpac and airflow.

MySQL 8.0.22 is the version used in the architecture, both MySQL servers (source and replica)

run on Ubuntu 18.04 servers with the user mysql (id : 10003, group : dba)
listen on port 40008

Firewall is opened on source host vpsfrsqlpac1 / port 40008 for connections coming from the replica server vpsfrsqlpac2.

Below, the topics covered to quickly setup and manage a MySQL 8 binary log file position based replication :

Replication architecture, brief overview + a quick note about terminology change under progress in MySQL products
Preparing the source server
Source server snapshot
Preparing the replica server
Starting replication
Managing replication errors
Disabling replication in a session
Read only mode in replicated databases (MySQL 8.0.22)

Binary log file position based replication architecture

How the binary log file position based replication works ? A quick drawing :

3 threads in the replication mechanism : 1 in the source server, 2 in the replica server

Binary Log Dump thread : thread created in the MySQL source server when the replica connects, this thread sends the binary log contents to a replica.
Replication IO thread : thread located in the MySQL replica server which connects to the source, asks it to send the updates recorded in its binary logs, reads the updates that the source’s Binlog dump thread sends and copies them to local files (relay log files). Relay log files and binary log files have the same format, they can be translated using mysqlbinlog utility.
Replication SQL thread : thread located in the MySQL replica server which reads the relay binary log files and executes the transactions in the replicated databases.

Terminology change

Before continuing, an important note : as of MySQL 8.0.22, terminology is being modified (MySQL Terminology Updates). The following terminology changes will be implemented in all MySQL products in upcoming releases, changes affecting greatly MySQL replication :

Old		New	Old		New
master		source	blacklist		blocklist
slave		replica	whitelist		allowlist

Replication related commands using the old terminology will be progressively deprecated and replaced by the new terms :

Old	New	Ready in
`START \| STOP SLAVE;`	`START \| STOP REPLICA;`	8.0.22
`SHOW SLAVE STATUS;`	`SHOW REPLICA STATUS;`	8.0.22
`SHOW SLAVE HOSTS;`	`SHOW REPLICAS;`	8.0.22
`SHOW MASTER STATUS;`	`SHOW SOURCE STATUS; (?)`	Not yet
`CHANGE MASTER TO MASTER_HOST=…, MASTER_USER=…, …;`	`CHANGE REPLICATION SOURCE TO SOURCE_HOST=…, SOURCE_USER=…, …;`	8.0.23
`SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;` …	`SET GLOBAL SQL_REPLICA_SKIP_COUNTER = N; (?)` …	Not yet …

In this paper, the new terminology MySQL 8.0.22 is used, particularly the SHOW REPLICA STATUS command which replaces the SHOW SLAVE STATUS command.

Preparing the source server (master, in the former terminology)

The following parameters are defined in the source MySQL server :

an identifier, the identifier must be unique : server-id=1
the binary log format (STATEMENT, MIXED or ROW) : binlog-format=ROW
the binary log filenames prefix : log-bin=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1
the binary logs index file : log-bin-index=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.index

$CFG/vpsfrsqlpac1.conf

[mysqld]
server-id=1
binlog-format=ROW
log-bin=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1
log-bin-index=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.index
innodb-flush-log-at-trx-commit=1
sync-binlog=1

The last 2 parameters (innodb_flush_log_at_trx_commit and sync_binlog) ensure the greatest durability and consistency in a replication using InnoDB with transactions.

No binary log filters are applied in the source server (binlog-do-db, binlog-ignore-db…), filters will be applied in the replica server. In this use case, logging is globally enabled in the source server in case of recovery.

Restart MySQL server.

With the above parameters, binary log files are located in the directory /sqlpac/mysql/binlogs/vpsfrsqlpac1 :

-rw-r----- 1 mysql dba 179 Jan 15 08:27 binlog-vpsfrsqlpac1.000002
-rw-r----- 1 mysql dba 180 Jan 15 08:27 binlog-vpsfrsqlpac1.index
-rw-r----- 1 mysql dba 156 Jan 15 08:27 binlog-vpsfrsqlpac1.000003

The index file is only a flat file listing the binary logs (path + filename) :

$BINLOGS/binlog-vpsfrsqlpac1.index

/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.000002
/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.000003

Check the system variable skip-networking is set to OFF in the source server, otherwise replica cannot communicate with the source and replication fails. The replication IO thread connects to the source server using only TCP/IP protocol.

(vpsfrsqlpac1) mysql> SHOW VARIABLES LIKE 'skip_networking'
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+

If set, remove the parameter skip-networking in the configuration file and restart the MySQL server source.

A replication maintenance user with the privilege REPLICATION SLAVE is created in the source server. Replica server (Replication IO thread) will use this user to connect and retrieve informations.

(vpsfrsqlpac1) mysql> CREATE USER 'STANDBY_USER'@'%' IDENTIFIED BY '**********';
(vpsfrsqlpac1) mysql> GRANT REPLICATION SLAVE ON *.* TO 'STANDBY_USER'@'%';
(vpsfrsqlpac1) mysql> FLUSH PRIVILEGES;

When creating the replication maintenance user, check if the new MySQL 8.0 authentication plugin caching_sha2_password is used. It will have an impact on replication (covered later in this guide).

(vpsfrsqlpac1) mysql> SELECT user, plugin FROM mysql.user WHERE user='standby_user';
+--------------+-----------------------+
| user         | plugin                |
+--------------+-----------------------+
| standby_user | caching_sha2_password |
+--------------+-----------------------+

Source server snapshot

Several methods to synchronize replica data with source data :

mysqldump utility
rsync utility
MySQL Enterprise backup (license required)
CLONE statement, new plugin feature as of MySQL 8.0.17. Usable only for InnoDB tables, the instance is fully cloned : MySQL 8, cloning instances for replication using CLONE statement.

The appropriate method will depend on the databases sizes, whether source databases can be set in read only mode… Databases are small and can be in read only mode, the mysqldump method is used here.

Read only mode is applied in the source server :

MySQL < 8.0.22 whole instance set in read only mode	MySQL >= 8.0.22 read only mode per database (new feature)
`(vpsfrsqlpac1) mysql> FLUSH TABLES WITH READ LOCK;`	`(vpsfrsqlpac1) mysql> ALTER DATABASE sqlpac READ ONLY=1; (vpsfrsqlpac1) mysql> ALTER DATABASE airflow READ ONLY=1;`

The current binary log file and the position are then retrieved using SHOW MASTER STATUS command. Keep these informations, they will be used when initializing replication :

(vpsfrsqlpac1) mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| binlog-vpsfrsqlpac1.000005 |     156  |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+

In another terminal, source databases are dumped :

mysql@vpsfrsqlpac1$ mysqldump -uroot -p -S/tmp/vpsfrsqlpac1.sock \
                                 --routines --triggers --events \
                                 --databases sqlpac airflow > bck_sqlpac_airflow.sql.sql

Read only mode is removed in the source server when the dump is completed :

MySQL < 8.0.22	MySQL >= 8.0.22
`(vpsfrsqlpac1) mysql> UNLOCK TABLES;`	`(vpsfrsqlpac1) mysql> ALTER DATABASE sqlpac READ ONLY=0; (vpsfrsqlpac1) mysql> ALTER DATABASE airflow READ ONLY=0;`

Databases are loaded in the replica server :

(vpsfrsqlpac2) mysql> source bck_sqlpac_airflow.sql;

Preparing the replica server (slave, in the former terminology)

First, check the SQL connection from the replica server to the source server using the replication maintenance user created previously (standby_user) :

mysql@vpsfrsqlpac2$ mysql -ustandby_user  -P40008 --protocol=tcp -hvpsfrsqlpac1 -p
mysql>

The following parameters are defined in the replica MySQL server :

an identifier (also unique) : server-id=2
the relay log filenames prefix : relay-log=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2
the relay logs index file : relay-log-index=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2.index
skip-slave-start to avoid replication auto start when restarting the MySQL server (temporary)

$CFG/vpsfrsqlpac2.conf

[mysqld]
server-id=2
relay-log=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2
relay-log-index=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2.index
skip-slave-start

Restart the replica MySQL Server.

Like the binary logs index file in the source server, the relay logs index file is a flat file listing the relay log files (path + filename) :

$RELAYLOGS/binlog-vpsfrsqlpac2.index

/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-srvmystandby.000016
/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-srvmystandby.000017

The informations related to the source server are then provided to the replica server using CHANGE MASTER command : host, port, user, binary log file, binary log position. The binary log informations (filename, position) are the values retrieved when the snapshot of the source server has been performed.

MySQL >= 8.0.23

	MySQL >= 8.0.23
`(vpsfrsqlpac2) mysql> CHANGE MASTER TO MASTER_HOST='vpsfrsqlpac1', MASTER_PORT=40008, MASTER_USER='standby_user', MASTER_PASSWORD='*************', MASTER_LOG_FILE='binlog-vpsfrsqlpac1.000005', MASTER_LOG_POS=156, GET_MASTER_PUBLIC_KEY=1;`	`(vpsfrsqlpac2) mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='vpsfrsqlpac1', SOURCE_PORT=40008, SOURCE_USER='standby_user', SOURCE_PASSWORD='*************', SOURCE_LOG_FILE='binlog-vpsfrsqlpac1.000005', SOURCE_LOG_POS=156, GET_SOURCE_PUBLIC_KEY=1;`

(vpsfrsqlpac2) mysql>
                
CHANGE MASTER
   TO MASTER_HOST='vpsfrsqlpac1',
      MASTER_PORT=40008,
      MASTER_USER='standby_user',
      MASTER_PASSWORD='*************',
      MASTER_LOG_FILE='binlog-vpsfrsqlpac1.000005',
      MASTER_LOG_POS=156,
      GET_MASTER_PUBLIC_KEY=1;

(vpsfrsqlpac2) mysql>
                    
CHANGE REPLICATION SOURCE
   TO SOURCE_HOST='vpsfrsqlpac1',
      SOURCE_PORT=40008,
      SOURCE_USER='standby_user',
      SOURCE_PASSWORD='*************',
      SOURCE_LOG_FILE='binlog-vpsfrsqlpac1.000005',
      SOURCE_LOG_POS=156,
      GET_SOURCE_PUBLIC_KEY=1;

These informations are stored in the table mysql.slave_master_info.

The optional parameter GET_MASTER_PUBLIC_KEY (GET_SOURCE_PUBLIC_KEY as of MySQL 8.0.23) is defined and set to 1 here because the replication maintenance user in the source server (standby_user) has been created with the caching_sha2_password authentication plugin.

At this stage, replication threads are not started :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State:
                  Source_Host: vpsfrsqlpac1
                  Source_User: standby_user
                  Source_Port: 40008
                Connect_Retry: 60
              Source_Log_File: binlog-vpsfrsqlpac1.000005
          Read_Source_Log_Pos: 156
               Relay_Log_File: relay-vpsfrsqlpac2.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: binlog-vpsfrsqlpac1.000005
           Replica_IO_Running: No
          Replica_SQL_Running: No
                           … : …

The filters, if exist, are now applied using the command CHANGE REPLICATION FILTER (new command starting MySQL 5.7.3). Only the databases sqlpac and airflow are replicated in this use case :

(vpsfrsqlpac2) mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (sqlpac,airflow);

Do not forget to add the filters in the replica server configuration file, otherwise when restarting the replica server, filters are lost.

$CFG/vpsfrsqlpac2.conf

[mysqld]
…
replicate-do-db=sqlpac
replicate-do-db=airflow

Filters are not physically stored in a system table (mysql.slave_master_info…), they can only be defined as startup parameters or using CHANGE REPLICATION FILTER command. Many other filter options are possible, refer to the docs.

Starting replication

Replication is started using START SLAVE command (START REPLICA as of MySQL >= 8.0.22):

(vpsfrsqlpac2) mysql> START REPLICA;
          
(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;

Check the replication and do not forget to remove the parameter skip-slave-start in the replica configuration file when replication works fine.

Replication start error #1 : same UUID, error 13117

In this paper, the replication does not work immediately, error 13117 is raised :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
                       Replica_IO_State:
                           … : …
                Last_IO_Errno: 13117
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
                               these UUIDs must be different for replication to work.

In this use case, the MySQL data directory has been copied from the source to the replica, so in the file auto.cnf, the server UUID identifier is the same :

auto.cnf

 [auto]
server-uuid=a7ce69c7-55fd-11eb-ab71-fa163e1f3eb9

To solve this issue, the file auto.cnf is deleted in the replica server data directory and the replica server is restarted : a new server UUID identifier is generated.

$LOG/vpsfrsqlpac2.log

2021-01-15T09:53:40.716121Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found,
so we assume that this is the first time that this server has been started.
Generating a new UUID: 465cc5e1-5795-11eb-9471-fa163e0fd563.

Replication start error #2 : Authentication plugin caching_sha2_password, error 2061

If the replication maintenance user in the source server has been defined with the authentication plugin caching_sha2_password and the parameter GET_MASTER_PUBLIC_KEY=1 has been omitted in the CHANGE MASTER command, the replication IO thread status may display "Connecting to master" with the error 2061 as the last error number :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State: Connecting to master
                           … : …
                Last_IO_Errno: 2061
                Last_IO_Error: error connecting to master 'standby_user@vpsfrsqlpac1:40008' - retry-time: 60 retries: 8
                               message: Authentication plugin 'caching_sha2_password'
                               reported error: Authentication requires secure connection.

This typical error occurs when the cache plugin caching_sha2_password has been emptied in the source server (restart…).

To apply the parameter GET_MASTER_PUBLIC_KEY=1 (GET_SOURCE_PUBLIC_KEY=1) if omitted at initialization step :

	MySQL >= 8.0.23
`(vpsfrsqlpac2) mysql> STOP REPLICA; CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1; START REPLICA;`	`(vpsfrsqlpac2) mysql> STOP REPLICA; CHANGE REPLICATION SOURCE TO GET_SOURCE_PUBLIC_KEY=1; START REPLICA;`

Replication start successful

(vpsfrsqlpac2) mysql> START REPLICA;          
(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State: Waiting for master to send event
                  Source_Host: vpsfrsqlpac1
                  Source_User: standby_user
                  Source_Port: 40008
                Connect_Retry: 60
              Source_Log_File: binlog-vpsfrsqlpac1.000005
          Read_Source_Log_Pos: 156
               Relay_Log_File: relay-vpsfrsqlpac2.000003
                Relay_Log_Pos: 333
        Relay_Source_Log_File: binlog-vpsfrsqlpac1.000005
             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
              Replicate_Do_DB: sqlpac,airflow
                           … : …
    Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates

Does a source server record its replica servers ? SHOW SLAVE HOSTS | SHOW REPLICAS command

NO. Bear in mind that no metadata informations related to replica servers are stored in a source server (tables, configuration files…), SHOW SLAVE HOSTS command in a source server is confusing (SHOW REPLICAS as of MySQL 8.0.22). This command displays a replica host only when this replica is connected to the source server (BinLog dump thread created).

(vpsfrsqlpac1) mysql> SHOW REPLICAS;
+-----------+------+-------+-----------+---------------------------------------+
| Server_id | Host | Port  | Source_id | Replica_UUID                          |
+-----------+------+-------+-----------+---------------------------------------+
|         2 |      | 40008 |         1 | 465cc5e1-5795-11eb-9471-fa163e0fd563  |
+-----------+------+-------+-----------+---------------------------------------+

Managing errors

Skipping transactions

SHOW REPLICA STATUS command gives all details when replication errors occur :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State: Waiting for master to send event
                           … : …
               Relay_Log_File: relay-vpsfrsqlpac2.000017
                Relay_Log_Pos: 333
                           … : …
           Replica_IO_Running: Yes
          Replica_SQL_Running: No
                           … : …
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'sqlpac.heartbeat'' on query. Default database: 'sqlpac'.
                               Query: 'DROP TABLE `heartbeat` /* generated by server */'

The utility mysqlbinlog can also be used to translate relay log files, relay log files are binary log files. For the above error (position : 333, relay log file : relay-vpsfrsqlpac2.000017) :

mysql@vpsfrsqlpac2$ mysqlbinlog -j 333 $RELAYLOGS/relay-vpsfrsqlpac2.000017
# at 333
…
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 410
…
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `heartbeat` /* generated by server */
/*!*/;
…

The position specified by Relay_Log_Pos (333) is the current position in the relay log file. This position is the last successful replicated event, the replicated event in error is in the next position (# at 410).

The SHOW RELAYLOG EVENTS SQL command in the replica server is more convenient than the utility mysqlbinlog :

(vpsfrsqlpac2) mysql> SHOW RELAYLOG EVENTS IN 'relay-vpsfrsqlpac2.000017' FROM 333 LIMIT 3;
+---------------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------+
| Log_name                  | Pos | Event_type     | Server_id | End_log_pos | Info                                                                         |
+---------------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------+
| relay-vpsfrsqlpac2.000017 | 333 | Anonymous_Gtid |         1 |         733 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                         |
| relay-vpsfrsqlpac2.000017 | 410 | Query          |         1 |         871 | use `sqlpac`; DROP TABLE `heartbeat` /* generated by server */ /* xid=158 */ |
| relay-vpsfrsqlpac2.000017 | 548 | Anonymous_Gtid |         1 |         948 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                         |
+---------------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------+

When you are sure you can safely skip transactions, use SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n before restarting the replication, with n being the number of transactions to skip :


(vpsfrsqlpac2) mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
(vpsfrsqlpac2) mysql> START REPLICA;

Discarding error numbers

Some errors numbers can be automatically ignored using the parameter slave-skip-errors. To be used cautiously obvisouly.

[mysqld]
…
slave-skip-errors=1051,1054

As of MySQL 5.6, a useful shorthand value ddl_exists_error has been introduced, value equivalent to the error code list 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

[mysqld]
…
slave-skip-errors=ddl_exists_error

Disabling replication in a session

For specific reasons, we sometimes need to disable temporarily the replication in a SQL session on the source server. Use the command SET SQL_LOG_BIN = [OFF|ON] to enable/disable replication in a session. When SQL_LOG_BIN is set to OFF, logging to the binary log for the current session is simply disabled. Obviously, the highest privilege SYSTEM_VARIABLES_ADMIN is required (formerly SUPER privilege).

(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=OFF;
(vpsfrsqlpac1) mysql> …
(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=ON;

Read only mode in replicated databases (MySQL 8.0.22)

As of MySQL 8.0.22, the read only option per database is now available, a long awaited feature. The read only mode does not affect the SQL replication threads.

(vpsfrsqlpac2) mysql> ALTER DATABASE sqlpac READ ONLY = 1;
(vpsfrsqlpac2) mysql> ALTER DATABASE airflow READ ONLY = 1;
        
(vpsfrsqlpac2) mysql> SHOW CREATE DATABASE sqlpac;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                 |
+----------+-------------------------------------------------------------------------------------------------+
| sqlpac   | CREATE DATABASE `sqlpac` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */   |
|          |                          /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */                 |
+----------+-------------------------------------------------------------------------------------------------+

When enabling/disabling read only mode for a database in the source server, do not forget to disable the replication : the ALTER DATABASE statement is replicated according to the replication filters applied previously.

(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=0;
(vpsfrsqlpac1) mysql> ALTER DATABASE sqlpac READ ONLY = 0;
(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=1;