Chapter 2. MySQL Replication

2.0 Introduction

MySQL replication provides a way to setup a copy (replica) server of the active (source) database, then automatically continuously update such a copy applying all changes which source server receives.

Replica is useful in many situations, particularly:

Hot Standby

A server, normally idle, which replaces an active one in case of a failure.

Read scale

Multiple servers, replicating from the same source, can process more parallel read requests than a single machine.

Geographical distribution

When application serves users in different regions having database server, located locally can help to retrieve data faster.

Analytics server

Complicated analytics queries may take hours to run, set plenty of locks and use a lot of resources. Running them on the replica minimizes impact on other parts of the application.

Backup server

Taking backups from a live database involves high IO resource usage and locking, which is necessary to avoid data inconsistencies between backup and active data set. Taking backups from the dedicated replica reduces impact on production.

Delayed copy

A replica, applying updates with a delay, configured by MASTER_DELAY option, allows to rollback human errors, such as removal of an important table.

Note

Historically source server was called a master and replica server was called a slave. These terms are interchangeable and still used in SQL commands.

MySQL Replication requires special activities on both servers.

Source server stores all updates in binary log files. These files contain encoded update events. Source server writes to a single binary log file at the moment. Once it reaches max_binlog_size the binary log is rotated and a new file is created.

The binary log file supports two formats: STATEMENT and ROW. In the STATEMENT format SQL statements are written as they are and then encoded into binary format. In the ROW format SQL statements are not recorded. Instead, actual updates to table rows are stored.

Tip

It could be useful, when troubleshooting replication errors, to know the actual statement received by the source server. Use option binlog_rows_query_log_events to store the information log event with the original query. Such an event is not participating in replication and could be retrieved for informational purposes only.

Replica server continuously requests binary log events from the source server, then stores them in the special files, called relay log files. It has a separate thread, called IO, or connection thread, which is doing only this job. Another thread, or threads, read events from the relay logs and apply them to the tables.

Each event in the binary log has its own unique identifier: position. Position is unique per file and resets when a new one is created. Replica may use the binary log file name and position as a unique identifier of the event.

While binary log position uniquely identifies event in a particular file it cannot be used to identify if particular event was applied on the replica or not. To resolve this problem Global Transaction Identifiers (GTIDs) were introduced. These are unique identifiers, assigned to each transaction. They are unique across all the life of a MySQL installation. They also use mechanism to uniquely identify server, therefore are safe to use even if replication is possible from multiple sources.

Replica stores information about source binary log coordinates in the special repository, defined by a variable master_info_repository. Such a repository can be stored either in a table or in a file.

This chapter describes how to setup and use MySQL Replication. It covers all typical replication scenarios, including:

  • Two servers one-way source-replica setup.
  • Circular replication
  • Multi-source replication
  • Semisunchronous replication
  • Group replication

2.1 Configuration of the Basic Replication between One Source and One Replica

Problem

You want to prepare two servers for the replication.

Solution

Add configuration option log-bin into the source configuration file, specify unique server_id for both servers, add options to support GTIDs and/or non-default binary log format and create a user with REPLICATION SLAVE privilege on the source.

Discussion

First you need to prepare both servers to be able to handle replication events.

On the source server:

  • Enable binary log by adding option log-bin into configuration file. Changing this option requires restart.

  • Set unique server_id. server_id is dynamic variable and could be changed without taking the server offline, but we strongly recommend to set it in the configuration file too, so it would not be overridden after restart.

  • Create a replication user and grant REPLICATION SLAVE to it:

    mysql> CREATE USER repl@'%' IDENTIFIED BY 'replrepl';
    Query OK, 0 rows affected (0,01 sec)
    
    mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
    Query OK, 0 rows affected (0,03 sec)
    

Warning

In MySQL 8.0 default authentication plugin is caching_sha2_password which requires TLS connection. Therefore, if you want to use this plugin, you need to enable TLS connection for the replica as described in Recipe 2.14 or use option GET_MASTER_PUBLIC_KEY=1 of the CHANGE MASTER command.

Alternatively you can use authentication plugin, allowing insecure connections.

mysql> CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password BY 'replrepl';
Query OK, 0 rows affected (0,01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0,03 sec)
           

On the replica just set unique server_id.

Tip

Since version 8.0 you can use SET PERSIST to save dynamically changed variable permanently:

mysql> SET PERSIST server_id=200;
Query OK, 0 rows affected (0,01 sec)
          

See Persisted System Variables in the MySQL User Reference Manual for details.

At this stage you can tune other options, which affect replication safety and performance, particularly:

binlog_format

Binary log format

GTID support

Support for global transaction identifiers

slave_parallel_type and slave_parallel_workers

Multi-threaded replica support

Binary log on the replica

Define if and how replica will use binary log.

We will cover these options in the following recipes.

2.2 Position-Based Replication in the New Installation Environment

Problem

You want to setup a replica of the just installed MySQL server, using position-based configuration.

Solution

Prepare source and replica servers as described in Recipe 2.1, then obtain current binary log position using SHOW MASTER STATUS command on the source server and point the replica to the appropriate position using CHANGE MASTER ... master_log_file='BINARY LOG FILE NAME', master_log_pos=POSITION; command.

Discussion

For this recipe we assume that you have two freshly installed servers with no user data in them. There is no write activity on any of the servers.

First, prepare them for the replication use as described at Recipe 2.1. Then, on the master, run command SHOW MASTER STATUS:

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      156 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
          

Field File contains name of the current binary log and field Position contains current position. Record values of these fields.

On the replica run CHANGE MASTER command:

mysql> CHANGE MASTER TO MASTER_HOST='sourcehost', -- Host of the source server
    -> MASTER_PORT=3306,                          -- Port of the source server
    -> MASTER_USER='repl',                        -- Replication user
    -> MASTER_PASSWORD='replrepl',                -- Password
    -> MASTER_LOG_FILE='master-bin.000001',       -- Binary log file
    -> MASTER_LOG_POS=156,                        -- Start position
    -> GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected, 1 warning (0.06 sec)
          

To start replica use command START SLAVE:

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
          

Note

While we were writting the book MySQL Enginering team were adding support for the keyword REPLICA to use in the replication SQL commands instead of SLAVE. This will be preferred syntax. However, support for the REPLICA keyword is not yet consistent accross all replication statements and options. Therefore we decided to use old-style syntax for commands and switch to new wording while discussing server roles.

To check if replica is running use SHOW SLAVE STATUS:

mysql> P grep Running
PAGER set to 'grep Running'
mysql> SHOW SLAVE STATUSG
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
1 row in set (0.00 sec)          
          

Listing above confirms that both IO (connection) and SQL (applier) replica threads are running and replication state is fine. We will discuss full output of the SHOW SLAVE STATUS command in Recipe 2.15

Now you can enable writes on the source server.

2.3 Position-Based Replication for the Source Server that is Already in Use

Problem

Setting up a replica for the new installed server is different from the case when future master already has data. In the latter case you need to be especially carefull to do not introduce data inconsistency by specifying wrong starting position. In this recipe we provide instructions on how to setup a replica of the MySQL installation in use.

Solution

Prepare source and replica servers as described in Recipe 2.1, stop all writes on the source server, backup it, then obtain current binary log position using SHOW MASTER STATUS command that will be used for pointing the replica to the appropriate position using CHANGE MASTER ... master_log_file='BINARY LOG FILE NAME', master_log_pos=POSITION; command.

Discussion

As in case of installing a new replica, both servers need to be configured for the replication use as described in Recipe 2.1. Before initiating setup you need to ensure that both servers have unique server_id and source server has binary logging enabled. You can create replication user at this moment or you can do it before setting up a replica.

If you have a server which was already running for a while and want to setup a replica of it you need to take backup first, restore it on the replica, then point to the source server. Challenge for this setup is to use correct binary log position: if the server is accepting writes while backup is running position consistently changing. As a result command SHOW MASTER STATUS will return wrong result unless you stop all writes while taking backup.

Standard backup tools support special options when taking backup of the future source server for a replica.

mysqldump, described in [Link to Come], has the option --master-data. If set to 1 CHANGE MASTER statement with coordinates at the time of the backup start will be written into resulting dump file and executed when the dump is loaded.

% ../bin/mysqldump --host=127.0.0.1 --port=13000 --user=root 
>  --master-data=1 --all-databases > mydump.sql
% grep -b5 "CHANGE MASTER" -m1 mydump.sql
906-
907---
910--- Position to start replication or point-in-time recovery from
974---
977-
978:CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=156;
1052-
1053---
1056--- Current Database: `mtr`
1083---
1086-
          

Tip

If you want to have replication position in the resulting dump file, but do not want CHANGE MASTER command to be automatically executed, set option --master-data to 2: in this case the statement will be written as a comment. You may later execute it manually.

Tools, which make online binary backups, such as Percona XtraBackup or MySQL Enterprise Backup, store binary log coordinates in special metadata files. Consult documentation of your backup tool to find out how to safely backup source server.

Once you have a backup restore it on the replica. For mysqldump use mysql client to load the dump:

% mysql < mydump.sql
          

Once backup is restored start replication using START SLAVE command.

2.4 GTID-based Replication

Problem

You want to setup a replica using global transaction identifiers (GTIDs).

Solution

Add options gtid_mode=ON and enforce_gtid_consistency=ON into both source and replica configuration files, then point the replica to the master using CHANGE MASTER ... AUTO_POSITION=1 command.

Discussion

Position-based replication is easy to setup, but is error-prone. What if you mix up and specify a position in the future? In this case some transactions will be missed. Or, what will happen if you specify a position in the past? In this case the same transaction will be applied twice. You will end up with duplicated, missed or corrupted rows.

To solve this issue Global Transaction Identifiers, or GTIDs, were introduced to uniquely identify each transaction on the server. GTID consists of two parts: unique ID of the server where this transaction were executed first time and unique ID of the transaction on this server. The source server ID is usually the value of the server_uuid global variable and transaction ID is a number, starting from 1.

mysql> show master statusG
*************************** 1. row ***************************
             File: binlog.000001
         Position: 358
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 467ccf91-0341-11eb-a2ae-0242dc638c6c:1
1 row in set (0.00 sec)

mysql> select @@gtid_executed;
+----------------------------------------+
| @@gtid_executed                        |
+----------------------------------------+
| 467ccf91-0341-11eb-a2ae-0242dc638c6c:1 |
+----------------------------------------+
1 row in set (0.00 sec)
        

Transactions, executed by the server, are stored in GTID sets and are visible in SHOW MASTER STATUS output as well as value of gtid_executed variable. The set contains unique ID of the originating server and range of transaction numbers.

In the example below 467ccf91-0341-11eb-a2ae-0242dc638c6c is the source server unique ID and 1-299 is a range of transaction numbers, which were executed on this server.

mysql> select @@gtid_executed;
+--------------------------------------------+
| @@gtid_executed                            |
+--------------------------------------------+
| 467ccf91-0341-11eb-a2ae-0242dc638c6c:1-299 |
+--------------------------------------------+
1 row in set (0.00 sec)
        

GTID sets can contain ranges, individual transactions and groups of them, separated by a colon symbol. GTIDs with different source ids are separated by a comma:

mysql> select @@gtid_executedG
*************************** 1. row ***************************
@@gtid_executed: 000bbf91-0341-11eb-a2ae-0242dc638c6c:1,
467ccf91-0341-11eb-a2ae-0242dc638c6c:1-310:400
1 row in set (0.00 sec)
        

Normally GTIDs are automatically assigned and you do not need to care about their values.

However, in order to use GTIDs you need to add additional preparation steps for your servers.

Two configuration options are required to enable GTIDs: gtid_mode=ON and enforce-gtid-consistency=ON. They must be enabled on both servers before starting replication.

If you are setting up a new replica just adding these options into the configuration file and restarting the servers is enough. Once done you can enable replication using CHANGE MASTER ... AUTO_POSITION=1 command and start it:

mysql> CHANGE MASTER TO MASTER_HOST='sourcehost', -- Host of the source server
    -> MASTER_PORT=3306,                          -- Port of the source server
    -> MASTER_USER='repl',                        -- Replication user
    -> MASTER_PASSWORD='replrepl',                -- Password
    -> GET_MASTER_PUBLIC_KEY=1,
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
	    

However, if replication was already running using position-based setup you need to perform additional steps:

  1. Stop all updates, making both servers read only:

    mysql> SET GLOBAL super_read_only=1;
    Query OK, 0 rows affected (0.01 sec)
    	          

  2. Wait until replica catches up with all updates from the source server: values of File and Position from the SHOW MASTER STATUS output on the source server should match values of Relay_Master_Log_File and Exec_Master_Log_Pos of the SHOW SLAVE STATUS, taken on the replica.

    Warning

    Do not rely on the Seconds_Behind_Master value, because it is incaccurate.

  3. Once the replica has caught up, stop both servers, enable gtid_mode=ON and enforce-gtid-consistency=ON options, start them and enable replication:

    mysql> CHANGE MASTER TO MASTER_HOST='sourcehost', -- Host of the source server
        -> MASTER_PORT=3306,                          -- Port of the source server
        -> MASTER_USER='repl',                        -- Replication user
        -> MASTER_PASSWORD='replrepl',                -- Password
        -> GET_MASTER_PUBLIC_KEY=1,
        -> MASTER_AUTO_POSITION=1;
    Query OK, 0 rows affected, 1 warning (0.06 sec)
    
    mysql> START SLAVE;
    Query OK, 0 rows affected (0.01 sec)
    	    

Note

You are not required to enable binary logging on the replica in order to use GTIDs. But if you are going to write to replica outside of the replication its transactions would not have own GTID assigned. GTIDs will be used only for the replicated events.

See Also

For additional information about setting up MySQL replication with GTIDs, see MySQL User Reference Manual.

2.5 Binary Log Format

Problem

You want to use a binary log format that is the most suitable for your application.

Solution

Decide which format best suites your needs and set it using configuration option binlog_format.

Discussion

Default MySQL binary log format is ROW since version 5.7.7. This is the safest possible format, fitting most applications. It stores encoded table row, modified by the binary log event.

However, binary log format ROW may generate more disk and network traffic than STATEMENT format. This happens, because it stores into binary log file two copies of the modified row: before changes and after the changes. If a table has many columns, values for all of them will be logged two times even if only one column was modified.

If you want binary log to store only changed column and column which could be used to identify changed rows (normally Primary Key) you can use configuration option binlog_row_image=minimal. This will work perfectly if tables on the source server and its replica are identical, but may cause issues if number of columns, their data types or primary key definitions do not match.

To store full row, except TEXT or BLOB columns which were not changed by the statement and are not required to uniquely identify modified row use option binlog_row_image=noblob.

If row format still generates too much traffic you may switch it to STATEMENT. In this case statements, modifying rows, will be recorded, then executed by the replica. To use binary log format STATEMENT set option binlog_format=STATEMENT.

STATEMENT format is not safe to use, because some statements can produce different updates on different servers, even if data originally was identical. These statements are called as not deterministic. In order to deal with this downside MySQL has a special binary log format: MIXED that normally logs events in the STATEMENT format and automatically switches to ROW if a statement is not deterministic.

Warning

If binary log is enabled on replica it should use either the same binary log format as its source server or MIXED unless you disabled binary logging of the replicated events using option log_slave_updates=OFF. This is required, because replica does not convert binary log format and simply copies received events into its own binary log file. If formats do not match replication will stop with an error.

Binary log format can be changed dynamically on the global or session level. To change format on the global level run:

mysql> set global binlog_format='statement';
Query OK, 0 rows affected (0,00 sec)
        

To change format on the global level and store it permanently use:

mysql> set persist binlog_format='row';
Query OK, 0 rows affected (0,00 sec)
        

To change format only on the session level execute:

mysql> set session binlog_format='mixed';
Query OK, 0 rows affected (0,00 sec)
        

While format STATEMENT usually generates less traffic than ROW this is not always the case. For example, complicated statements with long WHERE or IN clauses that modify just a few rows, generate a bigger binary log event with format STATEMENT.

Another issue with the STATEMENT format is that the replica executes recieved events the same way they were running on the source server. Therefore, if a statement is not effective it will run slow on replica too. For example, statements on large tables which have WHERE clause which cannot be resolved using indexes, usually are slow. In this case switching to ROW format may improve performance.

Warning

Normally ROW events use primary key to find the row on the replica that needs to be updated. If a table has no primary key ROW format can work extremely slow. Older versions of MySQL even could update wrong row, because of, now fixed, bugs. Auto-generated primary key which is used by the InnoDB storage engine is no help here, because it may generate different values on the source and replica servers for the same row. Therefore it is mandatory to define primary key for tables when use binary log format ROW.

2.6 Replication Filters

Problem

You want to replicate only events for specific databases or tables.

Solution

Use replication filters on the source, replica, or both sides.

Discussion

MySQL can filter updates to the specific databases or tables. You can setup such filters on the source server to prevent them from being recorded in the binary log or on the replica server, so replication would not execute them.

Filtering on the Source Server

To log only updates to a specific database use configuration option binlog-do-db=db_name. There is no corresponding variable for this option, therefore changing binary log filter requires restart. To log updates for two or more specific databases specify option binlog-do-db as many times as needed:

[mysqld]
binlog-do-db=cookbook
binlog-do-db=test
          

Binary log filters behave differently for ROW and STATEMENT binary log formats. For the statement-based logging only the default database is taken into account. If you are using fully qualified table names they will be logged based on the default database value and not on the database part of the update.

Thus, for the configuration file snippet above the following three updates will be logged in the binary log:

  • % mysql cookbook
    mysql> insert into limbs (thing, legs, arms) values('horse', 4, 0);
    Query OK, 1 row affected (0,01 sec)
              

  • mysql> USE cookbook
    Database changed
    mysql> delete from limbs where thing='horse';
    Query OK, 1 row affected (0,00 sec)
              

  • mysql> USE cookbook
    Database changed
    mysql> insert into donotlog.onlylocal (mysecret) 
        -> values('I do not want to replicate it!');
    Query OK, 1 row affected (0,01 sec)
              

However, this update on the cookbook database would not be logged:

mysql> use donotlog
Database changed
mysql> update cookbook.limbs set arms=8 where thing='squid';
Query OK, 1 row affected (0,01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
          

When binary log format ROW is used the default database is ignored for fully qualified table names. Thus, all these updates will be logged:

% mysql cookbook
mysql> insert into limbs (thing, legs, arms) values('horse', 4, 0);
Query OK, 1 row affected (0,01 sec)
mysql> USE cookbook
Database changed
mysql> delete from limbs where thing='horse';
Query OK, 1 row affected (0,00 sec)
mysql> use donotlog
Database changed
mysql> update cookbook.limbs set arms=8 where thing='squid';
Query OK, 1 row affected (0,01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
          

However, this statement will not be logged:

mysql> USE cookbook
Database changed
mysql> insert into donotlog.onlylocal (mysecret) values('I do not want to replicate it!');
Query OK, 1 row affected (0,01 sec)
          

For multiple table updates only updates to tables belonging to databases specified by filters are logged. In the following examples only updates to table cookbook.limbs are logged:

mysql> use donotlog
Database changed
mysql> update cookbook.limbs, donotlog.onlylocal set heads=1, 
    -> mysecret='I do not want to log it!';
Query OK, 12 rows affected (0,01 sec)
Rows matched: 12  Changed: 12  Warnings: 0
mysql> use cookbook
Database changed
mysql> update cookbook.limbs, donotlog.onlylocal set heads=0, 
    -> mysecret='I do not want to log and replicate this!' where cookbook.limbs.thing='table';
Query OK, 2 rows affected (0,00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
          

Warning

DDL statements, such as ALTER TABLE are always replicated in the STATEMENT format. Therefore filtering rules for this format applies to them no matter the value of the variable binlog_format.

If you want to log updates to all databases on your server and skip only a few of them use binlog-ignore-db filters. Specify filter multiple times to ignore multiple databases.

[mysqld]
binlog-ignore-db=donotlog
binlog-ignore-db=mysql
          

binlog-ignore-db filters work similarly to binlog-do-db filters. In case of STATEMENT binary logging they honor default database and ignore it if ROW binary log format is used. If you did not specify default database and use STATEMENT binary log format all updates will be logged.

If you use binary log format MIXED filtering rules will be applied depending if the update is stored in the STATEMENT or ROW format.

To find out which binary log filters are currently in use run SHOW MASTER STATUS command:

mysql> SHOW MASTER STATUSG
*************************** 1. row ***************************
             File: binlog.000008
         Position: 1202
     Binlog_Do_DB: cookbook,test
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0,00 sec)
          

Warning

Binary log files are often used not only for the replication, but also for point-in-time recovery (PITR). In this case filtered updates cannot be restored, because they are not stored anywhere. If you want to use binary logs for PITR and still filter some databases: log everything on the source server and filter on the replica.

Filtering on the Replica

Replica has more options to filter events. You can filter either specific databases or tables. You can also use wildcards.

Filtering on the database level works in same fashion as on the source server. It is controlled by options replicate-do-db and replicate-ignore-db. If you want to filter multiple databases specify these option as many times as you need.

To filter specific tables use options replicate-do-table and replicate-ignore-table. They take fully qualified table name as an argument.

[mysqld]
replicate-do-db=cookbook
replicate-ignore-db=donotlog
replicate-do-table=donotlog.dataforeveryone
replicate-ignore-table=cookbook.limbs
          

But most flexible and safe syntax for replication filters is replicate-wild-do-table and replicate-wild-ignore-table. As the name suggests they accept wildcards in the arguments. Wildcard syntax is the same as used for the LIKE clause. Refer to [Link to Come] for details on the LIKE clause syntax.

Symbol _ replaces exactly one single character. Thus replicate-wild-ignore-table=cookbook.standings_ filters tables cookbook.standings1 and cookbook.standings2, but does not filter cookbook.standings12 and cookbook.standings.

Symbol % replaces zero or more characters. Thus replicate-wild-do-table=cookbook.movies% instructs replica to apply updates to tables cookbook.movies, cookbook.movies_actors and cookbook.movies_actors_link.

If a table name itself contains a wildcard character which you do not want to replace you need to escape it. Thus option replicate-wild-ignore-table=cookbook.trip_l_g will filter tables cookbook.trip_leg, cookbook.trip_log, but also cookbook.tripslag while replicate-wild-ignore-table=cookbook.trip\_l_g will only filter updates to tables cookbook.trip_leg and cookbook.trip_log. Note, if you specify this option on the command line you may need to double escape wildcard characters depending of the SHELL version you use.

Tip

Table-level fitlers are independent from the default database regardless of the binary log format. Therefore it is safer to use them. If you want to filter all tables in the specific database or databases use wildcards:

[mysqld]
replicate-wild-do-table=cookbook.%
replicate-wild-ignore-table=donotlog.%
            

However, unlike database filters, replicate-wild-do-table and replicate-wild-ignore-table cannot filter stored routines or events. If you need to filter them you have to use database-level filters.

Replication filters can be set for the specific replication channel (Recipe 2.10). To specify per-channel filter prefix database, table name or wildcard expression with the channel name, followed by a colon:

[mysqld]
replicate-do-db=first:cookbook
replicate-ignore-db=second:donotlog
replicate-do-table=first:donotlog.dataforeveryone
replicate-ignore-table=second:cookbook.hitlog
replicate-wild-do-table=first:cookbook.movies%
replicate-wild-ignore-table=second:cookbook.movies%
          

You can specify replication filters not only via configuration options, but also using CHANGE REPLICATION FILTER command:

mysql> CHANGE REPLICATION FILTER
    -> REPLICATE_DO_DB = (cookbook),
    -> REPLICATE_IGNORE_DB = (donotlog),
    -> REPLICATE_DO_TABLE = (donotlog.dataforeveryone),
    -> REPLICATE_IGNORE_TABLE = (cookbook.limbs),
    -> REPLICATE_WILD_DO_TABLE = ('cookbook.%'),
    -> REPLICATE_WILD_IGNORE_TABLE = ('cookbook.trip\_l_g');
Query OK, 0 rows affected (0.00 sec)
          

To find out which replication filters are currently applied use SHOW SLAVE STATUSG command or query tables replication_applier_filters and replication_applier_global_filters in Performance Schema.

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 13000
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: Delly-7390-relay-bin.000002
                Relay_Log_Pos: 365
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: cookbook
          Replicate_Ignore_DB: donotlog
           Replicate_Do_Table: donotlog.dataforeveryone
       Replicate_Ignore_Table: cookbook.limbs
      Replicate_Wild_Do_Table: cookbook.%
  Replicate_Wild_Ignore_Table: cookbook.trip\_l_g
...

mysql> select * from performance_schema.replication_applier_filtersG
*************************** 1. row ***************************
 CHANNEL_NAME: 
  FILTER_NAME: REPLICATE_DO_DB
  FILTER_RULE: cookbook
CONFIGURED_BY: CHANGE_REPLICATION_FILTER
 ACTIVE_SINCE: 2020-10-04 13:43:21.183768
      COUNTER: 0
*************************** 2. row ***************************
 CHANNEL_NAME: 
  FILTER_NAME: REPLICATE_IGNORE_DB
  FILTER_RULE: donotlog
CONFIGURED_BY: CHANGE_REPLICATION_FILTER
 ACTIVE_SINCE: 2020-10-04 13:43:21.183768
      COUNTER: 0
*************************** 3. row ***************************
 CHANNEL_NAME: 
  FILTER_NAME: REPLICATE_DO_TABLE
  FILTER_RULE: donotlog.dataforeveryone
CONFIGURED_BY: CHANGE_REPLICATION_FILTER
 ACTIVE_SINCE: 2020-10-04 13:43:21.183768
      COUNTER: 0
*************************** 4. row ***************************
 CHANNEL_NAME: 
  FILTER_NAME: REPLICATE_IGNORE_TABLE
  FILTER_RULE: cookbook.limbs
CONFIGURED_BY: CHANGE_REPLICATION_FILTER
 ACTIVE_SINCE: 2020-10-04 13:43:21.183768
      COUNTER: 0
*************************** 5. row ***************************
 CHANNEL_NAME: 
  FILTER_NAME: REPLICATE_WILD_DO_TABLE
  FILTER_RULE: cookbook.%
CONFIGURED_BY: CHANGE_REPLICATION_FILTER
 ACTIVE_SINCE: 2020-10-04 13:43:21.183768
      COUNTER: 0
*************************** 6. row ***************************
 CHANNEL_NAME: 
  FILTER_NAME: REPLICATE_WILD_IGNORE_TABLE
  FILTER_RULE: cookbook.trip\_l_g
CONFIGURED_BY: CHANGE_REPLICATION_FILTER
 ACTIVE_SINCE: 2020-10-04 13:43:21.183768
      COUNTER: 0
6 rows in set (0.00 sec)
          

See Also

For additional information about replication filters, see How Servers Evaluate Replication Filtering Rules.

2.7 Rewriting Database on the Replica

Problem

You want to replicate tables to a database with a name, different from the one used on the source server.

Solution

Use option replicate-rewrite-db.

Discussion

MySQL allows rewriting database name on the fly if use replication fileter replicate-rewrite-db.

You can set such a filter in the configuration file, command line:

[mysqld]
replicate-rewrite-db=channel_id:cookbook->recipes
        

or via CHANGE REPLICATION FILTER command:

mysql> CHANGE REPLICATION FILTER 
    -> REPLICATE_REWRITE_DB=((cookbook,recipes))
    -> FOR CHANNEL 'channel_id';
        

Warning

Mind double brackets for the filter value and quotes for the channel name.

MySQL does not support RENAME DATABASE operation. Therefore to rename the database you need to create database with the different name first, then restore data of the original database into this new database.

mysql> CREATE DATABASE recipes;
% mysql recipes < cookbook.sql
          

You need to take dump of the single database with option --no-create-db, so resulting file would not contain CREATE DATABASE statement.

2.8 Multithreaded Replica

Problem

Replica is installed on better hardware than the source, network connection between servers is good, but replication lag is increasing.

Solution

Use multiple replication applier threads.

Discussion

MySQL server is multi-threaded. It applies incoming updates in highly concurrent manner. By default it uses all hardware CPU cores when processing application requests. However, replica by default uses single thread to apply incoming events from the source server. As a result it uses less resources to process replicated events and may delay even on decent hardware.

To resolve this issue use multiple applier threads. To do so set variable slave_parallel_workers to a value, greater than 1. This specifies number of parallel threads that replica will use to apply events. It makes sense to set value of this variable up to number of virtual CPU cores. Variable has no immediate effect: you have to restart replication to apply the change.

mysql> set global slave_parallel_workers=8;
Query OK, 0 rows affected (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
        

Not all replication events can be applied in parallel. What if the binary log contains two statements, updating the same row?

update limbs set arms=8 where thing='squid';
update limbs set arms=10 where thing='squid';
        

Depending on the order of events table limbs will have either eitght or ten arms for the squid. If these two statements are executed in different order on the source and replica they will end up with different data.

MySQL uses one of special algorithms for dependency tracking. Current algorithm is set by variables slave_parallel_type on the replica and binlog_transaction_dependency_tracking on the source.

Default value of the slave_parallel_type variable is DATABASE. With this value, updates belonging to different databases can be applied in parallel while updates to the same database are applied sequentially. This value does not correlate with binlog_transaction_dependency_tracking on the source.

Parallelization on the database level does not perform much better for setups which update less databases than number of CPU cores on the replica. To resolve this issue slave_parallel_type=LOGICAL_CLOCK has been introduced. For this type, transactions belonging to the same binary log group commit on the source are applied in parallel.

After changing variable slave_parallel_type you need to restart slave.

Value of variable binlog_transaction_dependency_tracking defines which transactions belong to the same commit group. Default is COMMIT_ORDER which is generated from the source’s timestamps. With this value transactions, committed nearly at the same time on the master, will be executed in parallel on replica. This mode works perfectly if master actively executes many small transactions. However, if the source server does not commit often it can happen that replica will execute sequentially even those transactions that cannot interfer with each other and practically executed on the source in parallel, just were committed in different times.

To resolve this issue binlog_transaction_dependency_tracking modes WRITESET and WRITESET_SESSION were introduced. In these modes MySQL decides if transactions are depend on each other using hashing algorithm, specified by variable transaction_write_set_extraction and can be any of XXHASH64(default) or MURMUR32. This means that if transactions modify set of rows, independent from each other, they could be executed in parallel, no matter how much time passed between commits on each of them.

With binlog_transaction_dependency_tracking mode, set to WRITESET even transactions originally executed within the same session could be applied in parallel. This may cause issues when replica sees changes in different order than master in some periods of time. It maybe acceptable or not depending on your application needs. To avoid such a situation you may enable option slave_preserve_commit_order which instructs replica to apply binary log events in the same order as they were originally executed on the source server. Another solutions is to set binlog_transaction_dependency_tracking to WRITESET_SESSION. This mode ensures that transactions originated from the same session are never applied in parallel.

Variable binlog_transaction_dependency_tracking is dynamic and you can modify it without stopping the server. You can also set it on the session level for the specific session only.

See Also

For additional information about multithreaded replica, see Improving the Parallel Applier with Writeset-based Dependency Tracking.

2.9 Circular Replication

Problem

You want to setup a chain of servers, which replicate from each other.

Solution

Make each server in the chain a source and a replica of its peers.

Discussion

Sometimes you may need to write to several MySQL servers and want updates to be visible on each of them. With MySQL replication this is possible. It supports such popular setups as two-server, a chain of servers (A -> B -> C -> D -> ... , circular, star as well as any creative setup you can imagine. For our example of the circular replication you just need to setup every server as a source and replica of each other.

Warning

You need to be very careful when use such a replication. Because updates are incoming from any server they can conflict with each other.

Imagine two nodes insert a row with id=42 at the same time. First, each node inserts a row, then recieves exactly same event from the binary log. Replication will stop with duplicate key error.

If then you try to delete a row with id=42 on both nodes you will receive an error again! Because at the time when DELETE statement will be received by the replication channel row already will be deleted.

But the worst can happen if you update a row with same ID. Imagine if node1 sets value to 42 and node2 sets value to 25. After replication events are applied node1 will have a row with value 25 and node2 with value 42. Different from what they initally had after local update!

Still there can be very valid reasons to use circular replication. For example, you may want to use one of nodes mostly for purposes of one application and another one for another application. You can have options and hardware, most suitable for both. Or you may have servers in different geographical locations (e.g. countries) and want to store local data closer to users. Or you can use your servers mostly for reads, but still need to update them. And, finally, you may setup a hot standby server which technically allows writes, but practically receives them only when the main source server dies.

In this recipe we will discuss how to setup a chain of three servers. You can modify this recipe for two or more servers. Then we will discuss safety considerations, required to use replication chains.

Setting Up Circle Replication of Three Servers

Prepare servers to use in the circular replication

  • Follow instructions in Recipe 2.1 for the master server

  • Make sure option log_slave_updates is enabled. Otherwise, if your replication chain includes more than two servers updates would apply only on the neighboring ones.

  • Ensure that option replicate-same-server-id is disabled. Otherwise you may end up in a situation when the same update will be applying in loops forever.

Point nodes to each other

Run on each server CHANGE MASTER command as described in [Link to Come] or in Recipe 2.4. Specify correct connection values. For example, if you want to have a circle of servers hostA -> hostB -> hostC -> hostA, you need to point hostB to hostA, hostA to hostC and hostC to hostB:

hostA> CHANGE MASTER TO MASTER_HOST='hostC', ...
hostB> CHANGE MASTER TO MASTER_HOST='hostA', ...
hostC> CHANGE MASTER TO MASTER_HOST='hostB', ...
                  

Start replication

Start replication using START SLAVE command.

Safety Considerations When Using Replication Chains

When writing to multiple servers, replicating to each other, you need to logically separate objects to which you are going to write. You can do it on different levels.

Business Logic

Make sure at the application level that you do not update same rows on multiple servers at the same time.

Server

Write to only one server at a time. This is good solution for creating a Hot Standby servers.

Databases and Tables

Assign specific set of tables to each server. For example, write only to tables movies, movies_actors, movies_actors_link on the nodeA; to tables trip_leg and trip_log on the nodeB and to tables weatherdata and weekday on the nodeC.

Rows

If you still need to write to the same table on all the servers separate rows which each node can update. If you use integer primary key with AUTO_INCREMENT option you can do it by setting option auto_increment_increment to the number of the servers and setting auto_increment_offset to number of the server in chain, starting from one. For example, on our three-servers setup set auto_increment_increment to 3 and auto_increment_offset to 1 on the nodeA, to 2 on the nodeB and to 3 on the nodeC. We discuss how to tune auto_increment_increment and auto_increment_offset in [Link to Come]

If you do not use AUTO_INCREMENT you need to create a rule at the application level, so identifier will follow its own unique pattern on the each node.

2.10 Multisource Replication

Problem

You want a replica to apply events from two or more source servers.

Solution

Create multiple replication channels by running command CHANGE MASTER ... FOR CHANNEL ‘my source’; for each of source servers.

Discussion

You may want to replicate from multiple servers to one. For example, if separate source servers are updated by different applications and you want to use replica for backups or for analytics. To achieve this you need to use multi-source replica.

Prepare servers for the replication

Prepare source and replica servers as described in Recipe 2.1. For the replica server add additional step: configure master_info_repository and relay_log_info_repository to use tables:

mysql> SET PERSIST master_info_repository = 'TABLE';
mysql> SET PERSIST relay_log_info_repository = 'TABLE';
                

Backup data on the source servers

Make full backup or backup only databases which you want to replicate. E.g., if you want to replicate database cookbook from one server and database production from the another one backup only these databases.

If you are going to use position-based replication use mysqldump with option --master-data=2 which instructs the tool to log CHANGE MASTER command, but comment it out.

% mysqldump --host=source_cookbook --single-transaction --triggers --routines 
> --master-data=2 --databases cookbook > cookbook.sql
                

For the GTID-based replication use option --set-gtid-purged=COMMENTED instead.

% mysqldump --host=source_production --single-transaction --triggers --routines 
> --set-gtid-purged=COMMENTED --databases production > production.sql
                

Tip

You can use position-based and GTID-based replication for different channels.

Restore data on the replica

Restore data, collected from the source servers.

% mysql < cookbook.sql
% mysql < production.sql
                

Warning

Ensure data on source servers do not have databases with the same name. If they have you need to rename one of the databases and use replicate-rewrite-db filter, which will rewrite database name while applying replication events. See Recipe 2.7 for details.

Configure replication channels

For the position-based replication locate in the dump file CHANGE MASTER command:

% cat cookbook.sql | grep "CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000008', MASTER_LOG_POS=2603;
                

and use resulting coordinates to setup replication. Use FOR CHANNEL clause of the CHANGE MASTER command to specify which channel to use.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='source_cookbook',
    -> MASTER_LOG_FILE='binlog.000008',
    -> MASTER_LOG_POS=2603,
    -> FOR CHANNEL 'cookbook_channel';
                

For the GTID-based replication first locate SET @@GLOBAL.GTID_PURGED statement:

% grep GTID_PURGED production.sql
/* SET @@GLOBAL.GTID_PURGED='+9113f6b1-0751-11eb-9e7d-0242dc638c6c:1-385';*/
                

Do this for all channels which will use GTID-based replication:

% grep GTID_PURGED recipes.sql
/* SET @@GLOBAL.GTID_PURGED='+910c760a-0751-11eb-9da8-0242dc638c6c:1-385';*/
                

Then combine them into single set: '9113f6b1-0751-11eb-9e7d-0242dc638c6c:1-385,910c760a-0751-11eb-9da8-0242dc638c6c:1-385', run RESET MASTER to reset GTID execution history and set GTID_PURGED to the set you just compiled:

mysql> RESET MASTER;
Query OK, 0 rows affected (0,03 sec)

mysql> SET @@GLOBAL.gtid_purged = '9113f6b1-0751-11eb-9e7d-0242dc638c6c:1-385,
    '> 910c760a-0751-11eb-9da8-0242dc638c6c:1-385';
Query OK, 0 rows affected (0,00 sec)
                

Then use CHANGE MASTER command to setup new channel:

mysql> CHANGE MASTER TO
    -> MASTER_HOST='source_production',
    -> MASTER_AUTO_POSITION=1,
    -> FOR CHANNEL 'production_channel';   
                

Start replication

Start replication using START SLAVE command:

mysql> START SLAVE FOR CHANNEL'cookbook_channel';
Query OK, 0 rows affected (0,00 sec)

mysql> START SLAVE FOR CHANNEL 'production_channel';
Query OK, 0 rows affected (0,00 sec)
              

Confirm replication is running

Run SHOW SLAVE STATUS and check records for all channels:

mysql> show slave statusG
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
                 Channel_Name: cookbook_channel
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
                 Channel_Name: production_channel
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set (0.00 sec)
                

Or query Performance Schema:

mysql> select CHANNEL_NAME, io.SERVICE_STATE as io_status, 
    -> sqlt.SERVICE_STATE as sql_status, COUNT_RECEIVED_HEARTBEATS, RECEIVED_TRANSACTION_SET
    -> from performance_schema.replication_connection_status as io 
    -> join performance_schema.replication_applier_status as sqlt using(channel_name)G
*************************** 1. row ***************************
             CHANNEL_NAME: cookbook_channel
                io_status: ON
               sql_status: ON
COUNT_RECEIVED_HEARTBEATS: 11
 RECEIVED_TRANSACTION_SET: 9113f6b1-0751-11eb-9e7d-0242dc638c6c:1-387
*************************** 2. row ***************************
             CHANNEL_NAME: production_channel
                io_status: ON
               sql_status: ON
COUNT_RECEIVED_HEARTBEATS: 11
 RECEIVED_TRANSACTION_SET: 910c760a-0751-11eb-9da8-0242dc638c6c:1-385
2 rows in set (0.00 sec)
                

2.11 Semisynchronous Replication

Problem

You want to ensure that at least one replica has the update before the client recieves success for the COMMIT operation.

Solution

Use semisynchronous replication plugin.

Discussion

MySQL replication is asynchronous. This means that the source server can accept writes very fast. All it needs is to store data in the tables and write information about changes into binary log file. However, it does not have any idea if any of replicas recieved updates and, if recieved, applied them.

We cannot guarantee if the asynchronous replica applies updates, but we can set it up to be sure that updates are received and stored in the relay log file. This does not guarantee that the update will be applied or, if applied, it will result in the same values as on the master server, but guarantees that at least two servers will have record of the update which could be applied, say, in case of a disaster recovery. To achieve this use semisynchronous replication plugin.

The semisynchronous replication plugin should be installed on both source and replica server.

On the source server run:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.03 sec)
        

On the replica run:

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
        

Once installed, you can enable semisynchronous replication. On the source set global variable rpl_semi_sync_master_enabled to 1. On the replica use variable rpl_semi_sync_slave_enabled.

You can control semisynchronous replication behavior with help of variables, as seen in Table 2-1:

Table 2-1. Variables, controlling behavior of the semisynchronous replication plugin
VariableWhat it controlsDefault value
rpl_semi_sync_master_timeoutHow many milliseconds to wait for response from the replica. If this value is exceeded, replication silently converts to the asynchronous.10000
rpl_semi_sync_master_wait_for_slave_countFrom which number of replicas the source server need to receive acknowlegement before committing transaction.1
rpl_semi_sync_master_wait_no_slaveWhat will happen if number of connected slaves fail below rpl_semi_sync_master_wait_for_slave_count. As long as these servers later reconnect and acknowledge the transaction, semisynchronous remains functional. If this variable is OFF, replication is converted to asynchronous as soon as number of replicas drops below rpl_semi_sync_master_wait_for_slave_countON
rpl_semi_sync_master_wait_pointAt which moment to expect acknowledgement from the replica that it recieved transaction. This variable supports two possible values. In case of AFTER_SYNC the source writes each transaction into the binary log, then syncs it to the disk. The source waits acknowledgement from the replica about recieved changes, then commits the transaction. In casel of AFTER_COMMIT the source commits the transaction, then waits acknowledgement from the replica and upon success returns to the client.AFTER_SYNC

To find out status of the semisynchronous replication use variables Rpl_semi_sync_*. Source server has plenty of them.

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 9     |
| Rpl_semi_sync_master_no_times              | 3     |
| Rpl_semi_sync_master_no_tx                 | 6     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1021  |
| Rpl_semi_sync_master_tx_wait_time          | 4087  |
| Rpl_semi_sync_master_tx_waits              | 4     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 4     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
        

The most improtant is Rpl_semi_sync_master_clients which shows if the semisynchronous is currently in use and how many semisynchronous replicas are connected. In case if Rpl_semi_sync_master_clients is zero, no semisynchronous replica is connected and asynchronous replication is used.

On the replica server only varuable Rpl_semi_sync_slave_status is available and can have values either ON or OFF.

Warning

If no replica accepts the write in rpl_semi_sync_master_timeout milliseconds, replication will switch to the asynchronous without any message or a warning for the client. Only way to figure out that the replication mode switched to asynchronous is to examine value of the variable Rpl_semi_sync_master_clients or to check error log file for messages like:

2020-10-12T22:25:17.654563Z 0 [ERROR] [MY-013129] [Server] A message intended ↩
for a client cannot be sent there as no client-session is attached. Therefore, ↩
we're sending the information to the error-log instead: ↩
MY-001158 - Got an error reading communication packets
2020-10-12T22:25:20.083796Z 198 [Note] [MY-010014] [Repl] While initializing ↩
dump thread for slave with UUID <09bf4498-0cd2-11eb-9161-98af65266957>, ↩
found a zombie dump thread with the same UUID. ↩
Master is killing the zombie dump thread(180).
2020-10-12T22:25:20.084088Z 180 [Note] [MY-011171] [Server] Stop semi-sync ↩
binlog_dump to slave (server_id: 2).
2020-10-12T22:25:20.084204Z 198 [Note] [MY-010462] [Repl] Start binlog_dump ↩
to master_thread_id(198) slave_server(2), pos(, 4)
2020-10-12T22:25:20.084248Z 198 [Note] [MY-011170] [Server] ↩
Start asynchronous binlog_dump to slave (server_id: 2), pos(, 4).
2020-10-12T22:25:20.657800Z 180 [Note] [MY-011155] [Server] ↩
Semi-sync replication switched OFF.
          

2.12 Group Replication

Problem

You want to apply updates either on all the nodes or nowhere before confirming to the client.

Solution

Use Group Replication.

Discussion

Starting from version 5.7.17 MySQL supports fully synchronous replication with help of the Group Replication plugin. If the plugin is in use MySQL servers, called nodes, create a group that commits transactions together or, if one of members fail, rolls them back. This way the update is either replicated to all group members or nowhere. High availability is ensured.

You can have up to nine servers in the group. More than nine is not supported. There is a very good reason for this limitation: higher number of servers implies higher replication delay. In case of synchronous replication, all updates are applied to all the nodes before transaction completes. Each update transferred to each node, waits when it is applied and only then commits. Thus replication delay depend on the speed of the slowest member and network transfer rate.

While it is technically possible to have less than three servers in the Group Replication setup, smaller number does not provide proper high availability. This is because Paxos algorithm, used by the Group Communication Engine requires 2F + 1 nodes to create a quorum. In other words, in case of a disaster, the number of active nodes should be greater than the number of disconnected nodes.

Group Replication has limitations. First, and the most important one, it supports only storage engine InnoDB. You need to disable other storage engines before enabling the plugin. Each replicated table must have primary key. You should put servers into the local network. While having Group Replication across Internet is possible, it may lead to longer time for applying transactions and disconnecting nodes from the group due to network timeouts. Statements LOCK TABLE and GET_LOCK are not taken into account for the certification process, that means they are local to the node and error prone. You may find full list of limitations in the Group Replication Limitations user reference manual.

To enable Group Replication you need to configure all the participating servers as described in Recipe 2.1 and perform additional preparations.

  1. Prepare configuration file

    [mysqld]
    # Disable unsupported storage engines
    disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
    	
    # Set unique server ID. Each server in the group should have its own ID
    server_id=1
    
    # Enable GTIDs
    gtid_mode=ON
    enforce_gtid_consistency=ON
    
    # Enable replica updates
    log_slave_updates=ON
    
    # Only ROW binary log format supported
    binlog_format=ROW
    
    # Ensure that replication repository is TABLE
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    
    # Ensure that transaction_write_set_extraction is enabled
    transaction_write_set_extraction=XXHASH64
    
    # Add Group Replication options
    plugin_load_add='group_replication.so'
    
    # Any valid UUID, should be same for all group members.
    # Use SELECT UUID() to generate a UUID
    group_replication_group_name="dc527338-13d1-11eb-abf7-98af65266957"
    
    # Host of the local node and port which will be used for communication between members
    # Port number should be different from from the one, used for serving clients
    group_replication_local_address= "seed1:33061"
    
    # Ports and addresses of all nodes in the group. Should be same on all nodes
    group_replication_group_seeds= "seed1:33061,seed2:33061,seed3:33061"
    
    # Since we did not setup Group replication at this stage,
    # it should not be started on boot
    # You may set this option ON after bootstrapping the group
    group_replication_start_on_boot=off
    group_replication_bootstrap_group=off
    
    # Request source server public key for the authentication plugin caching_sha2_password
    group_replication_recovery_get_public_key=1
                  

  2. Start servers.

  3. Choose a node which will be the first node in the group.

  4. Create replication user only on the first member as described in Recipe 2.1 and additionally grant BACKUP_ADMIN to it.

    mysql> CREATE USER repl@'%' IDENTIFIED BY 'replrepl';
    Query OK, 0 rows affected (0,01 sec) 
    
    mysql> GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO repl@'%';
    Query OK, 0 rows affected (0,03 sec)
                  

    You do not need to create replication user on other group members, because CREATE USER statement will be replicated.

  5. Setup replication on the first member to use this user:

    mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='replrepl' 
        -> FOR CHANNEL 'group_replication_recovery';
    Query OK, 0 rows affected (0,01 sec) 
                  

    Channel name group_replication_recovery is the special built-in name of the Group Replication channel.

    Tip

    If you do not want replication credentials to be stored as plain text in the replication repository skip this step and provide credentials later when run START GROUP_REPLICATION. See also Recipe 2.13

  6. Bootstrap the node.

    mysql> SET GLOBAL group_replication_bootstrap_group=ON;
    Query OK, 0 rows affected (0,00 sec) 
    
    mysql> START GROUP_REPLICATION;
    Query OK, 0 rows affected (0,00 sec) 
    
    mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
    Query OK, 0 rows affected (0,00 sec) 
                  

  7. Check Group Replication status by selecting from performance_schema.replication_group_members.

    mysql> SELECT * FROM performance_schema.replication_group_membersG
    *************************** 1. row ***************************
      CHANNEL_NAME: group_replication_applier
         MEMBER_ID: d8a706aa-16ee-11eb-ba5a-98af65266957
       MEMBER_HOST: Delly-7390
       MEMBER_PORT: 33361
      MEMBER_STATE: ONLINE
       MEMBER_ROLE: PRIMARY
    MEMBER_VERSION: 8.0.21
    1 row in set (0.00 sec)
                  

    And wait when the first member state becames ONLINE.

  8. Start the second and the third nodes.

    mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='replrepl' 
        -> FOR CHANNEL 'group_replication_recovery';
    Query OK, 0 rows affected (0,01 sec) 
    
    mysql> START GROUP_REPLICATION;
    Query OK, 0 rows affected (0,00 sec) 
                  

Once you confirm that all members are in state ONLINE you can use Group Replication. Query table performance_schema.replication_group_members to get this information. Healthy setup will output something like this:

mysql> SELECT * FROM performance_schema.replication_group_membersG
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: d8a706aa-16ee-11eb-ba5a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33361
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: e14043d7-16ee-11eb-b77a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33362
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: ea775284-16ee-11eb-8762-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33363
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
3 rows in set (0.00 sec)
        

If you want to start Group Replication with existent data restore it on the first node before bootstraping it. Data will be copied when other nodes join the group.

Tip

In this recipe we started Group Replication in the single-primary mode. This mode allows writes only on one member of the group. This is the safest and recommended option. However, if you want to write on multiple nodes, you may switch to multi-primary node by using function group_replication_switch_to_multi_primary_mode:

mysql> SELECT group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully.     |
+--------------------------------------------------+
1 row in set (1.01 sec)

mysql> SELECT * FROM performance_schema.replication_group_membersG
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: d8a706aa-16ee-11eb-ba5a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33361
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: e14043d7-16ee-11eb-b77a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33362
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: ea775284-16ee-11eb-8762-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33363
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
3 rows in set (0.00 sec)
          

For more details check Changing a Group’s Mode User Manual.

See Also

For additional information about group replication, see Group Replication in the User Reference Manual.

2.13 Storing Replication Credentials Securely

Problem

By default replication credentials are visible in the replication info repository if specified as part of CHANGE MASTER command. You want to hide them from the occasional access by not authorized users.

Solution

Use options USER and PASSWORD of the START SLAVE command.

Discussion

When you specify replication user credentials using CHANGE MASTER command they are stored in plain text, unencrypted, regardless of the master_info_repository option.

Thus, if master_info_repository='TABLE', any user with read access to the mysql database can query table slave_master_info and read the password:

mysql> select User_name, User_password from slave_master_info;
+-----------+---------------+
| User_name | User_password |
+-----------+---------------+
| repl      | replrepl      |
+-----------+---------------+
1 row in set (0.00 sec)
        

Or, if master_info_repository='FILE', any operating system user who can access the file, can get replication credentiasl:

% head -n6 var/mysqld.3/data/master.info
31
binlog.000001
688
127.0.0.1
repl
replrepl
        

If this is not desirable behavior you may specify replication credentials as part of the START SLAVE or START GROUP_REPLICATION command:

mysql> start slave user='repl' password='replrepl';
Query OK, 0 rows affected (0.01 sec)
        

However, if you previously specified replication credentials as part of the CHANGE MASTER command, they will remain visible in the master info repository. To clear previously entered user and password run CHANGE MASTER command with empty arguments for MASTER_USER and MASTER_PASSWORD:

mysql> select User_name, User_password from slave_master_info;
+-----------+---------------+
| User_name | User_password |
+-----------+---------------+
| repl      | replrepl      |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> change master to master_user='', master_password='';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start slave user='repl' password='replrepl';
Query OK, 0 rows affected (0.01 sec)

mysql> select User_name, User_password from slave_master_info;
+-----------+---------------+
| User_name | User_password |
+-----------+---------------+
|           |               |
+-----------+---------------+
1 row in set (0.00 sec)
        

Warning

Once you cleared replication credentials from the master info repository they are not stored anywhere and you will need to provide them each time when restart replication.

2.14 Using TLS (SSL) for Replication

Problem

You want to transfer data between source and replica securely.

Solution

Setup TLS (Transport Layer Security) connections for the replication channel.

Discussion

Connection between source and replica servers is technically similar to any other client connections to the MySQL server. Therefore encrypting it via TLS requires preparations, similar to encrypting client connections as described in [Link to Come].

To create encrypted replication setup follow these steps.

  1. Obtain or create TLS keys and certificates as described at [Link to Come].

  2. Ensure that the source server has TLS configuration parameters under [mysqld] section:

    Note

    While MySQL uses modern safer TLS protocol in the latest versions its configuration options still use abbreviation SSL. MySQL User Reference Manual also often refers TLS as SSL.

    [mysqld]
    ssl_ca=cacert.pem
    ssl_cert=server-cert.pem
    ssl_key=server-key.pem
                  

    You may check if TLS enabled if check value of the system variable have_ssl:

    mysql> SHOW VARIABLES LIKE 'have_ssl';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | have_ssl      | YES   |
    +---------------+-------+
    1 row in set (0,01 sec)
                  

  3. On the replica server put paths to TLS client key and certificate under [client] of the configuration file:

    [client]
    ssl-ca=cacert.pem
    ssl-cert=client-cert.pem
    ssl-key=client-key.pem
                  

    and specify option MASTER_SSL=1 for the CHANGE MASTER command:

    mysql> mysql> CHANGE MASTER TO MASTER_SSL=1;
    Query OK, 0 rows affected (0.03 sec
                  

    Alternatively you can specify paths to the client key and certificate as part of the CHANGE MASTER command:

    mysql> CHANGE MASTER TO
        -> MASTER_SSL_CA='cacert.pem',
        -> MASTER_SSL_CERT='client-cert.pem',
        -> MASTER_SSL_KEY='client-key.pem',
        -> MASTER_SSL=1;
    Query OK, 0 rows affected (0.02 sec)
                  

    Note

    We intentionally omitted other parameters of the CHANGE MASTER command, such as MASTER_HOST for brevity. But you need to use them as described in [Link to Come] or Recipe 2.4

  4. Start replication:

    mysql> START SLAVE;
    Query OK, 0 rows affected (0.00 sec)
                  

CHANGE MASTER command supports other TLS modifiers, compatible with regular client connection encryption options. For example, you can specify a gipher to use with clause MASTER_SSL_CIPHER or enforce source server certificate verification with clause MASTER_SSL_VERIFY_SERVER_CERT.

See Also

For additional information about securing connections between the source and replica servers, see Setting Up Replication to Use Encrypted Connections.

2.15 Replication Troubleshooting

Problem

Replication is not working and you want to fix it.

Solution

Use SHOW SLAVE STATUS command or query replication tables in Performance Schema to undertstand why the replication failed, then fix it.

Discussion

Replication is managed by two kinds of threads: IO and SQL (or connection and applier). IO, or connection, thread is responsible for connecting to the source server, retrieving updates and storing them in the relay log file. There is always one IO thread per replication channel. SQL, or applier, thread reads data from the relay log file and applies changes to the tables. One replication channel may have multiple SQL threads. Connection and applier threads are totally independent and their errors are reported by different replication diagnostic instruments.

There are two main instruments to diagnose replication errors: SHOW SLAVE STATUS command and replication tables in Performance Schema. SHOW SLAVE STATUS existed since very beginning while replication tables in the Performance Schema were added in version 5.7. You will get very similar information by using these two instruments and which to use depends on your preferences. In our opinion SHOW SLAVE STATUS is good for manual review in the command line while it is much easier to write monitoring alerts, querying Performance Schema rather than parse SHOW SLAVE STATUS output.

SHOW SLAVE STATUS

SHOW SLAVE STATUS contains all the information about IO and SQL threads configuration, status and errors. All data is printed in the single row. However, this row is formatted with spaces and newlines. You may examine it comfortably by using G modifier of the MySQL command line client. For multi-source replica SHOW SLAVE STATUS prints information about each channel in the separate row.

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 13000
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: Delly-7390-relay-bin-cookbook.000002
                Relay_Log_Pos: 365
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
                 Channel_Name: cookbook
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 13004
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: Delly-7390-relay-bin-test.000002
                Relay_Log_Pos: 365
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
                 Channel_Name: test
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set (0.00 sec)
          

We intentionally skipped part of the output for brevity. We will not describe each field, but only those required for handling stopped replication (see Table 2-2). If you are curious what other fields mean consult SHOW REPLICA | SLAVE STATUS Statement User Reference Manual.

Table 2-2. Meaning of fields of the SHOW SLAVE STATUS, required to understand and fix an error
FieldDescriptionSubsystem
Slave_IO_State (Replica_IO_State)Status of the IO thread. Contains information on what the connection thread is doing when running, empty if IO thread is stopped and Connecting if connection is not yet established.IO thread status
Master_Host (Source_Host)Host of the source server.IO thread configuration
Master_User (Source_User)Replication user.IO thread configuration
Master_Port (Source_Port)Port of the source serverIO thread configuration
Master_Log_File (Source_Log_File)Binary log on the source server from which IO thread is currently reading.IO thread status
Read_Master_Log_Pos (Read_Source_Log_Pos)Position of the binary log file on the source server from which IO thread is reading.IO thread status
Relay_Log_FileCurrent relay log file.IO thread status
Relay_Log_PosLast position in the relay log file.IO thread status
Relay_Master_Log_File (Relay_Source_Log_File)Binary log on the source server from which SQL thread is executing events.SQL thead status
Slave_IO_Running (Replica_IO_Running)If IO thread is running. Use this field to quickly identify health of the connection thread.IO thread status.
Slave_SQL_Running (Replica_SQL_Running)If SQL thread is running. Use to quickly identify health of the applier thread.SQL thread status
Replicate_*Replication filters.SQL thread configuration
Exec_Master_Log_Pos (Exec_Source_Log_Pos)Position of the binary log file on the source up to which SQL thread executed events.SQL thread state
Until_ConditionUntil conditions if any.SQL thread configuration
Master_SSL_* (Source_SSL_*)SSL options for connecting to the source server.IO thread configuration
Seconds_Behind_Master (Seconds_Behind_Source)Estimated delay between source server and replica.SQL thread status
Last_IO_ErrnoLast error number of the IO thread. Cleared once resolved.IO thread status
Last_IO_ErrorLatest error on the IO thread. Cleared once resolved.IO thread status
Last_Errno, Last_SQL_ErrnoNumber of the last error, received by SQL thread. Creared once resolved.SQL thead status
Last_Error, Last_SQL_ErrorLast error of the SQL thread. Cleared once resolved.SQL thread status
Slave_SQL_Running_State (Replica_SQL_Running_State)Status of the SQL thread. Empty if stopped.SQL thread status
Last_IO_Error_TimestampTime when last IO error happened. Cleared once resolved.IO thread status
Last_SQL_Error_TimestampTime when last SQL error happened. Cleared once resolved.SQL thread state
Retrieved_Gtid_SetGTIDs, retrieved by the connection thread.IO thread status
Executed_Gtid_SetGTIDs, executed by the SQL thread.SQL thread state.
Channel_NameName of the replication channel.IO and SQL threads configuration

We will refer to this table when discuss how to deal with specific IO and SQL threads errors.

Replication Tables in Performance Schema

Alternative diagnostic solution: tables in Performance Schema, unlike SHOW SLAVE STATUS, do not store all the information in the single place, but have it in separate spaces.

Information about IO thread configuration is stored in the table replication_connection_configuration and information about its status is in the table replication_connection_status.

Information about SQL threads is stored in six tables as shown in Table 2-3

Table 2-3. Tables with information, specific to SQL thread(s)
Table NameDescription
replication_applier_configurationSQL thread configuration.
replication_applier_global_filtersGlobal replication filters: filters, applicable for all channels.
replication_applier_filtersReplication filters, specific to particular channels.
replication_applier_statusStatus for the SQL thread, global.
replication_applier_status_by_workerFor multi-threaded slave: status of each SQL thread.
replication_applier_status_by_coordinatorFor multi-threaded slave: status of the SQL thread as seen by the coordinator.

Finally, you will find Group Replication network configuration and status in replication_group_members table and statistics of the Group Replication members in table replication_group_member_stats.

Troubleshooting IO Thread

You can find if replication IO thread is having issues by checking value of the Slave_IO_Running field of the SHOW SLAVE STATUS. If value is not Yes connection thread, likely, experiences issues. Reason why this happens could be found in the Last_IO_Errno and Last_IO_Error fields.

mysql> show slave statusG
*************************** 1. row ***************************
...
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
...
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'repl@127.0.0.1:13000' - ↩
                               retry-time: 60 retries: 1 message: ↩
                               Access denied for user 'repl'@'localhost' (using password: NO)
...
          

Like in the example above replica cannot connect to the source server, because access denied for the user 'repl'@'localhost'. IO thread is still running and will retry connection attempt in 60 seconds (retry-time: 60). Reason for such a failure is clear: either user does not exist on the master or it does not have enough privileges. You need to connect to the source server and fix the user account. Once it is fixed next connection attempt will succeed.

Alernatively you may query table replication_connection_status in Performance Schema:

mysql> select SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP
    -> from performance_schema.replication_connection_statusG
*************************** 1. row ***************************
       SERVICE_STATE: CONNECTING
   LAST_ERROR_NUMBER: 2061
  LAST_ERROR_MESSAGE: error connecting to master 'repl@127.0.0.1:13000' - retry-time: 60 ↩
                      retries: 1 message: Authentication plugin 'caching_sha2_password' ↩
                      reported error: Authentication requires secure connection.
LAST_ERROR_TIMESTAMP: 2020-10-17 13:23:03.663994
1 row in set (0.00 sec)
          

In this example field LAST_ERROR_MESSAGE contains the reason why the IO thread failed to connect: user account on the source server uses authentication plugin caching_sha2_password which requires secure connection. To fix this error you need to stop replication, then run CHANGE MASTER with parameters either MASTER_SSL=1 or GET_MASTER_PUBLIC_KEY=1. In the latter case traffic between replica and source server will stay insecure and only password exchange communication will be secured. See Recipe 2.14 for details.

Trobuleshooting SQL Thread

To find out why applier thread had stopped check Slave_SQL_Running, Last_SQL_Errno and Last_SQL_Error fields:

mysql> show slave statusG
*************************** 1. row ***************************
...
            Slave_SQL_Running: No
...
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'cookbook'; ↩
                               database exists' on query. Default database: 'cookbook'. ↩
                               Query: 'create database cookbook'
          

In the listing above error message shows that CREATE DATABASE command failed, because such a database already exists on the replica.

Same information could be found in the table replication_applier_status_by_worker in Performance Schema:

mysql> select SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP
    -> from performance_schema.replication_applier_status_by_workerG
*************************** 1. row ***************************
       SERVICE_STATE: OFF
   LAST_ERROR_NUMBER: 1007
  LAST_ERROR_MESSAGE: Error 'Can't create database 'cookbook'; database exists' on query. ↩
                      Default database: 'cookbook'. Query: 'create database cookbook'
LAST_ERROR_TIMESTAMP: 2020-10-17 13:58:12.115821
1 row in set (0.01 sec)
          

There are few ways to resolve this issue. First, you may simply drop the database on the replica and restart SQL thread:

mysql> drop database cookbook;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
          

In case if you want to keep database on the replica: for example, in a case if it supposed to have extra tables which do not exist on the source server, you may skip replicated event.

If you use position-based replication use variable sql_slave_skip_counter:

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
          

In this example we skipped one event from the binary log, then restarted replication.

For GTID-based replication setting sql_slave_skip_counter would not work, because it does not include GTID information. Instead, you need to generate empty transaction with GTID of the transaction which replica could not execute. To find out failed GTID check Retrieved_Gtid_Set and Executed_Gtid_Set fields of the SHOW SLAVE STATUS:

mysql> show slave statusG
*************************** 1. row ***************************
...
           Retrieved_Gtid_Set: de7e85f9-1060-11eb-8b8f-98af65266957:1-5
            Executed_Gtid_Set: de7e85f9-1060-11eb-8b8f-98af65266957:1-4,
de8d356e-1060-11eb-a568-98af65266957:1-3
...
          

In this example Retrieved_Gtid_Set contains transactions de7e85f9-1060-11eb-8b8f-98af65266957:1-5 while Executed_Gtid_Set only transactions de7e85f9-1060-11eb-8b8f-98af65266957:1-4. It is clear that transcation de7e85f9-1060-11eb-8b8f-98af65266957:5 was not executed. Transactions with UUID de8d356e-1060-11eb-a568-98af65266957 are local and not executed by the replication applier thread.

You may also find failing transaction if query APPLYING_TRANSACTION field of the replication_applier_status_by_worker table:

mysql> select LAST_APPLIED_TRANSACTION, APPLYING_TRANSACTION
    -> from performance_schema.replication_applier_status_by_workerG
*************************** 1. row ***************************
LAST_APPLIED_TRANSACTION: de7e85f9-1060-11eb-8b8f-98af65266957:4
    APPLYING_TRANSACTION: de7e85f9-1060-11eb-8b8f-98af65266957:5
1 row in set (0.00 sec)
          

Once failing transaction found inject empty transaction with same GTID and restart the SQL thread.

mysql> -- set explicit GTID
mysql> set gtid_next='de7e85f9-1060-11eb-8b8f-98af65266957:5';
Query OK, 0 rows affected (0.00 sec)

mysql> -- inject empty transaction
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> -- revert GTID generation back to automatic
mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

mysql> -- restart SQL thread
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
          

Warning

While skipping binary log event or transaction helps to restart replication at the moment, it may cause bigger issue and lead to data inconsistency between source and replica and, as a result, to future errors. Always analyze why error happened in the first place and try to fix the reason, not simply skip the event.

While SHOW SLAVE STATUS and table replication_applier_status_by_worker both store error messages if you use multi-threaded slave the table can have better information about what happened. Like in this example error message does not give the full understanding of the reason for the failure:

mysql> show slave statusG
*************************** 1. row ***************************
...
               Last_SQL_Errno: 1146
               Last_SQL_Error: Coordinator stopped because there were error(s) ↩
                               in the worker(s). The most recent failure being: ↩
                               Worker 8 failed executing transaction ↩
                               'de7e85f9-1060-11eb-8b8f-98af65266957:7' at ↩
                               master log binlog.000001, end_log_pos 1818. ↩
                               See error log and/or ↩
                               performance_schema.replication_applier_status_by_worker table ↩
                               for more details about this failure or others, if any.
...
          

It reports that worker 8 failed, but does not tell why. Query on replication_applier_status_by_worker returns this information:

mysql> select SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP
    -> from performance_schema.replication_applier_status_by_worker where worker_id=8G
*************************** 1. row ***************************
       SERVICE_STATE: OFF
   LAST_ERROR_NUMBER: 1146
  LAST_ERROR_MESSAGE: Worker 8 failed executing transaction ↩
                     'de7e85f9-1060-11eb-8b8f-98af65266957:7' at master log binlog.000001, ↩
                     end_log_pos 1818; Error executing row event: ↩
                     'Table 'cookbook.limbs' doesn't exist'
LAST_ERROR_TIMESTAMP: 2020-10-17 14:28:01.144521
1 row in set (0.00 sec)
          

Now it is clear that a specific table does not exist. You may analyze why this is the case and correct the error.

Troubleshooting Group Replication

SHOW SLAVE STATUS is not available for Group Replication. Therefore you need to use Performance Schema to troubleshoot issues with it. Performance Schema has two special tables for Group Replication only: replication_group_members, showing details of all members and replication_group_member_stats, displaying statistics for them. However, these tables do not have information about IO and SQL thread errors. These details are available in tables which we discussed for the standard asynchronous replication.

Let’s have a closer look to the Group Replication troubleshooting options.

Quick way to identify if something is wrong with Group replication is a replication_group_members table.

mysql> SELECT * FROM performance_schema.replication_group_membersG
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: de5b65cb-16ae-11eb-826c-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33361
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: e9514d63-16ae-11eb-8f6e-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33362
  MEMBER_STATE: RECOVERING
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f1e717ab-16ae-11eb-bfd2-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33363
  MEMBER_STATE: RECOVERING
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
3 rows in set (0.00 sec)
          

In the listing above only PRIMARY member is in MEMBER_STATE: ONLINE that means it is healthy. Both SECONDARY members are in RECOVERING state and are having troubles to join the group.

Failing member will stay in the RECOVERING state for some time, while Group Replication tries to recover itself and, if the error cannot be automatically recovered, leave the group and stay in the ERROR state.

mysql> SELECT * FROM performance_schema.replication_group_membersG
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: e9514d63-16ae-11eb-8f6e-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33362
  MEMBER_STATE: ERROR
   MEMBER_ROLE: 
MEMBER_VERSION: 8.0.21
1 row in set (0.00 sec)
          

Both listings were taken on the same secondary member of the group, but after it left the group it reports only itself as a Group Replication member and does not display information about other members.

To find reason of the failure you need to examine tables replication_connection_status and replication_applier_status_by_worker.

In our example member e9514d63-16ae-11eb-8f6e-98af65266957 stopped with SQL error. You will find error details in the replication_applier_status_by_worker table:

mysql> SELECT CHANNEL_NAME, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP,
    -> APPLYING_TRANSACTION FROM performance_schema.replication_applier_status_by_workerG
*************************** 1. row ***************************
        CHANNEL_NAME: group_replication_recovery
   LAST_ERROR_NUMBER: 3635
  LAST_ERROR_MESSAGE: The table in transaction de5b65cb-16ae-11eb-826c-98af65266957:15 ↩
                      does not comply with the requirements by an external plugin.
LAST_ERROR_TIMESTAMP: 2020-10-25 20:31:27.718638
APPLYING_TRANSACTION: de5b65cb-16ae-11eb-826c-98af65266957:15
*************************** 2. row ***************************
        CHANNEL_NAME: group_replication_applier
   LAST_ERROR_NUMBER: 0
  LAST_ERROR_MESSAGE: 
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION: 
2 rows in set (0.00 sec)
          

Error message says that the definition of the table in the transaction de5b65cb-16ae-11eb-826c-98af65266957:15 is not compatible with Group Replication plugin. To find out why check Group Replication Requirements and Limitations, identify the table used in the transaction and fix the error.

Error message in the replication_applier_status_by_worker table does not have any hint on which table was used in the transaction. But error log file may have. Open error log file, search for the LAST_ERROR_TIMESTAMP and LAST_ERROR_NUMBER to identify the error and check if previous or next rows have more information.

2020-10-25T17:31:27.718600Z 71 [ERROR] [MY-011542] [Repl] Plugin group_replication reported: ↩
'Table al_winner does not have any PRIMARY KEY. This is not compatible with Group Replication.'
2020-10-25T17:31:27.718644Z 71 [ERROR] [MY-010584] [Repl] Slave SQL for channel ↩
'group_replication_recovery': The table in transaction de5b65cb-16ae-11eb-826c-98af65266957:15 ↩
does not comply with the requirements by an external plugin. Error_code: MY-003635
          

In this example error message on the previous row contains the table name: al_winner, and the reason why it is not compatible with Group Replication: the table has not primary key.

To fix the error you need to fix table definition on the PRIMARY and failing SECONDARY node.

First, login to the PRIMARY node, and add surrogate primary key:

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table al_winner add id int not null auto_increment primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.01 sec)
          

You need to disable binary logging, because otherwise this change will be replicated to the secondary members and replication will stop with the duplicate column name error.

Then run same command on the secondary to fix the table definition and restart Group Replication.

mysql> set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table al_winner add id int not null auto_increment primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.01 sec)

mysql> stop group_replication;
Query OK, 0 rows affected (1.02 sec)

mysql> start group_replication;
Query OK, 0 rows affected (3.22 sec)
          

You need to disable super_read_only first which is set by the Group Replication plugin if nodes are running in single-primary mode.

Once the error is fixed the node joins the group and reports its state as ONLINE.

mysql> SELECT * FROM performance_schema.replication_group_membersG
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: d8a706aa-16ee-11eb-ba5a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33361
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: e14043d7-16ee-11eb-b77a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33362
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
2 rows in set (0.00 sec)
          

Tip

You can find what the failing transaction is doing by running mysqlbinlog command with option verbose:

% mysqlbinlog data1/binlog.000001 
> --include-gtids=de5b65cb-16ae-11eb-826c-98af65266957:15 --verbose
...
SET @@SESSION.GTID_NEXT= 'de5b65cb-16ae-11eb-826c-98af65266957:15'/*!*/;
# at 4015
#201025 13:44:34 server id 1  end_log_pos 4094 CRC32 0xad05e64e 	Query ↩
thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1603622674/*!*/;
...
### INSERT INTO `cookbook`.`al_winner`
### SET
###   @1='Mulder, Mark' /* STRING(120) meta=65144 nullable=1 is_null=0 */
###   @2=21 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `cookbook`.`al_winner`
### SET
###   @1='Clemens, Roger' /* STRING(120) meta=65144 nullable=1 is_null=0 */
###   @2=20 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `cookbook`.`al_winner`
...
### INSERT INTO `cookbook`.`al_winner`
### SET
###   @1='Sele, Aaron' /* STRING(120) meta=65144 nullable=1 is_null=0 */
###   @2=15 /* INT meta=0 nullable=1 is_null=0 */
# at 4469
#201025 13:44:34 server id 1  end_log_pos 4500 CRC32 0xddd32d63 	Xid = 74
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
            

Option verbose required to decode row events.

We fixed error on one node, but the third node did not join the group. After examining content of the table performance_schema.replication_connection_status we found that replication connection options were not setup correctly:

mysql> SELECT CHANNEL_NAME, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP
    -> FROM performance_schema.replication_connection_statusG
*************************** 1. row ***************************
        CHANNEL_NAME: group_replication_applier
   LAST_ERROR_NUMBER: 0
  LAST_ERROR_MESSAGE: 
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
        CHANNEL_NAME: group_replication_recovery
   LAST_ERROR_NUMBER: 13117
  LAST_ERROR_MESSAGE: Fatal error: Invalid (empty) username when attempting ↩
                      to connect to the master server. Connection attempt terminated.
LAST_ERROR_TIMESTAMP: 2020-10-25 21:31:31.413876
2 rows in set (0.00 sec)
          

To fix this we need to run correct CHANGE MASTER command:

mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (1.01 sec)

mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='replrepl'
    -> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.40 sec)
          

Once fixed the node will fail with the same SQL error as the previous one, that has to be fixed the way we described above. Finally, after SQL error is recovered, the node will join the cluster and will be reported as ONLINE.

mysql> SELECT * FROM performance_schema.replication_group_membersG
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: d8a706aa-16ee-11eb-ba5a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33361
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.21
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: e14043d7-16ee-11eb-b77a-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33362
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: ea775284-16ee-11eb-8762-98af65266957
   MEMBER_HOST: Delly-7390
   MEMBER_PORT: 33363
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.21
3 rows in set (0.00 sec)
          

To check performance of the Group Replication query table performance_schema.replication_group_member_stats.

mysql> SELECT * FROM performance_schema.replication_group_member_statsG
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16036502905383892:9
                                 MEMBER_ID: d8a706aa-16ee-11eb-ba5a-98af65266957
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 10154
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 9247
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: d8a706aa-16ee-11eb-ba5a-98af65266957:1-18,
dc527338-13d1-11eb-abf7-98af65266957:1-1588
            LAST_CONFLICT_FREE_TRANSACTION: dc527338-13d1-11eb-abf7-98af65266957:10160
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 5
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 10154
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 2. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16036502905383892:9
                                 MEMBER_ID: e14043d7-16ee-11eb-b77a-98af65266957
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 10037
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 9218
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: d8a706aa-16ee-11eb-ba5a-98af65266957:1-18,
dc527338-13d1-11eb-abf7-98af65266957:1-1588
            LAST_CONFLICT_FREE_TRANSACTION: dc527338-13d1-11eb-abf7-98af65266957:8030
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 5859
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 4180
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 3. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16036502905383892:9
                                 MEMBER_ID: ea775284-16ee-11eb-8762-98af65266957
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 10037
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 9218
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: d8a706aa-16ee-11eb-ba5a-98af65266957:1-18,
dc527338-13d1-11eb-abf7-98af65266957:1-37
            LAST_CONFLICT_FREE_TRANSACTION: dc527338-13d1-11eb-abf7-98af65266957:6581
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 5828
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 4209
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
3 rows in set (0.00 sec)
          

Important fields are COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE that show how many transactions are waiting in the queue on the secondary node to apply, and TRANSACTIONS_COMMITTED_ALL_MEMBERS which show that transactions were applied on all members. For more details consult User Reference Manual.

2.16 Use Processlist to Understand Replication Performance

Problem

Replica is behind the source server and lag is increasing. You want to undertsand what is going on.

Solution

Examine status of the SQL threads using replication tables in Performance Schema as well as regular MySQL performance instrumentation.

Discussion

Replica may fall behind the master if SQL threads are applying updates slower than the source server. This may happen because updates on the master are running concurrently, while on the replica less threads are used to process the same workload. This difference may happen even on replicas with the same or higher number of CPU cores than the master either because you set up less slave_parallel_workers than active threads on the source server, or because they are not used fully due to safety measures used to prevent replica from applying updates in the wrong order.

To understand how many parallel workers are active you may query table replication_applier_status_by_worker.

mysql> select WORKER_ID, LAST_APPLIED_TRANSACTION, APPLYING_TRANSACTION 
    -> from performance_schema.replication_applier_status_by_worker;
+-----------+---------------------------------+--------------------------------------------+
| WORKER_ID | LAST_APPLIED_TRANSACTION        | APPLYING_TRANSACTION                       |
+-----------+---------------------------------+--------------------------------------------+
|         1 | de7e85f9-...-98af65266957:26075 | de7e85f9-1060-11eb-8b8f-98af65266957:26077 |
|         2 | de7e85f9-...-98af65266957:26076 | de7e85f9-1060-11eb-8b8f-98af65266957:26078 |
|         3 | de7e85f9-...-98af65266957:26068 | de7e85f9-1060-11eb-8b8f-98af65266957:26079 |
|         4 | de7e85f9-...-98af65266957:26069 |                                            |
|         5 | de7e85f9-...-98af65266957:26070 |                                            |
|         6 | de7e85f9-...-98af65266957:26071 |                                            |
|         7 | de7e85f9-...-98af65266957:25931 |                                            |
|         8 | de7e85f9-...-98af65266957:21638 |                                            |
+-----------+---------------------------------+--------------------------------------------+
8 rows in set (0.01 sec)
        

In the listing above you may notice that only three threads are currently applying a transaction while others are idle. This is not stable information and you need to run the same query several times to find out if this is a tendency.

Table threads in Performance Schema contains a list of all threads currently running on the MySQL server, including background ones. It has a field name which value is thread/sql/slave_worker in case of the replication SQL thread. You can query it and find more details on what each of the SQL thread workers is doing.

mysql> select thread_id, PROCESSLIST_ID, PROCESSLIST_DB, PROCESSLIST_STATE
    -> from performance_schema.threads where name = 'thread/sql/slave_worker';
+-----------+----------------+----------------+----------------------------------------+
| thread_id | PROCESSLIST_ID | PROCESSLIST_DB | PROCESSLIST_STATE                      |
+-----------+----------------+----------------+----------------------------------------+
|        54 |             13 | NULL           | waiting for handler commit             |
|        55 |             14 | sbtest         | Applying batch of row changes (update) |
|        56 |             15 | sbtest         | Applying batch of row changes (delete) |
|        57 |             16 | NULL           | Waiting for an event from Coordinator  |
|        58 |             17 | NULL           | Waiting for an event from Coordinator  |
|        59 |             18 | NULL           | Waiting for an event from Coordinator  |
|        60 |             19 | NULL           | Waiting for an event from Coordinator  |
|        61 |             20 | NULL           | Waiting for an event from Coordinator  |
+-----------+----------------+----------------+----------------------------------------+
8 rows in set (0.00 sec)
        

In the listing above thread 54 is waiting for a transaction commit, threads 55 and 56 are applying a batch of row changes, while other threads are waiting for an event from the Coordinator.

Since the source server applies changes in high number of threads we may notice that the replication lag is increasing.

mysql> P grep Seconds_Behind_Master
PAGER set to 'grep Seconds_Behind_Master'
mysql> show slave statusG select sleep(60); show slave statusG
        Seconds_Behind_Master: 232
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

        Seconds_Behind_Master: 238
1 row in set (0.00 sec)
        

One of the resolutions for such issues is to set option binlog_transaction_dependency_tracking on the source server to WRITESET_SESSION or WRITESET. These options are discussed in Recipe 2.8 and allow to have higher parallelization on the replica. Note that changes would not take immediate effect, because replica will have to apply binary log events, recorded with default binlog_transaction_dependency_tracking value COMMIT_ORDER.

Still, after a while, you may notice that all SQL thread workers became active and slave lag started decreasing.

mysql> select WORKER_ID, LAST_APPLIED_TRANSACTION, APPLYING_TRANSACTION
    -> from performance_schema.replication_applier_status_by_worker;
+-----------+----------------------------------+---------------------------------------------+
| WORKER_ID | LAST_APPLIED_TRANSACTION         | APPLYING_TRANSACTION                        |
+-----------+----------------------------------+---------------------------------------------+
|         1 | de7e85f9-...-98af65266957:170966 | de7e85f9-1060-11eb-8b8f-98af65266957:170976 |
|         2 | de7e85f9-...-98af65266957:170970 | de7e85f9-1060-11eb-8b8f-98af65266957:170973 |
|         3 | de7e85f9-...-98af65266957:170968 | de7e85f9-1060-11eb-8b8f-98af65266957:170975 |
|         4 | de7e85f9-...-98af65266957:170960 | de7e85f9-1060-11eb-8b8f-98af65266957:170967 |
|         5 | de7e85f9-...-98af65266957:170964 | de7e85f9-1060-11eb-8b8f-98af65266957:170972 |
|         6 | de7e85f9-...-98af65266957:170962 | de7e85f9-1060-11eb-8b8f-98af65266957:170969 |
|         7 | de7e85f9-...-98af65266957:170971 | de7e85f9-1060-11eb-8b8f-98af65266957:170977 |
|         8 | de7e85f9-...-98af65266957:170965 | de7e85f9-1060-11eb-8b8f-98af65266957:170974 |
+-----------+----------------------------------+---------------------------------------------+
8 rows in set (0.00 sec)

mysql> select thread_id, PROCESSLIST_ID, PROCESSLIST_DB, PROCESSLIST_STATE
    -> from performance_schema.threads where name = 'thread/sql/slave_worker';
+-----------+----------------+----------------+----------------------------------------+
| thread_id | PROCESSLIST_ID | PROCESSLIST_DB | PROCESSLIST_STATE                      |
+-----------+----------------+----------------+----------------------------------------+
|        54 |             13 | sbtest         | Applying batch of row changes (update) |
|        55 |             14 | NULL           | waiting for handler commit             |
|        56 |             15 | sbtest         | Applying batch of row changes (delete) |
|        57 |             16 | sbtest         | Applying batch of row changes (delete) |
|        58 |             17 | sbtest         | Applying batch of row changes (update) |
|        59 |             18 | sbtest         | Applying batch of row changes (delete) |
|        60 |             19 | sbtest         | Applying batch of row changes (update) |
|        61 |             20 | sbtest         | Applying batch of row changes (write)  |
+-----------+----------------+----------------+----------------------------------------+
8 rows in set (0.00 sec)

mysql> P grep Seconds_Behind_Master
PAGER set to 'grep Seconds_Behind_Master'
mysql> show slave statusG select sleep(60); show slave statusG
        Seconds_Behind_Master: 285
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

        Seconds_Behind_Master: 275
1 row in set (0.00 sec)
        

Another common reason for the replication lag is a local command, affecting tables, updated by the replication. You may notice that this is the case if query table replication_applier_status_by_worker and compare value of the APPLYING_TRANSACTION_START_APPLY_TIMESTAMP field with current time:

mysql> select WORKER_ID, APPLYING_TRANSACTION,
    -> timediff(NOW(), APPLYING_TRANSACTION_START_APPLY_TIMESTAMP) as exec_time
    -> from performance_schema.replication_applier_status_by_worker;
+-----------+---------------------------------------------+-----------------+
| WORKER_ID | APPLYING_TRANSACTION                        | exec_time       |
+-----------+---------------------------------------------+-----------------+
|         1 | de7e85f9-1060-11eb-8b8f-98af65266957:226091 | 00:05:14.367275 |
|         2 | de7e85f9-1060-11eb-8b8f-98af65266957:226087 | 00:05:14.768701 |
|         3 | de7e85f9-1060-11eb-8b8f-98af65266957:226090 | 00:05:14.501099 |
|         4 | de7e85f9-1060-11eb-8b8f-98af65266957:226097 | 00:05:14.232062 |
|         5 | de7e85f9-1060-11eb-8b8f-98af65266957:226086 | 00:05:14.773958 |
|         6 | de7e85f9-1060-11eb-8b8f-98af65266957:226083 | 00:05:14.782274 |
|         7 | de7e85f9-1060-11eb-8b8f-98af65266957:226080 | 00:05:14.843808 |
|         8 | de7e85f9-1060-11eb-8b8f-98af65266957:226094 | 00:05:14.327028 |
+-----------+---------------------------------------------+-----------------+
8 rows in set (0.00 sec)
        

In the listing above transaction execution time is similar for all threads and around five minutes. That is ridiculously long!

To find out why transactions are executing for such a long time query table threads in the Performance Schema:

mysql> select thread_id, PROCESSLIST_ID, PROCESSLIST_DB, PROCESSLIST_STATE
    -> from performance_schema.threads where name = 'thread/sql/slave_worker';
+-----------+----------------+----------------+------------------------------+
| thread_id | PROCESSLIST_ID | PROCESSLIST_DB | PROCESSLIST_STATE            |
+-----------+----------------+----------------+------------------------------+
|        54 |             13 | NULL           | Waiting for global read lock |
|        55 |             14 | NULL           | Waiting for global read lock |
|        56 |             15 | NULL           | Waiting for global read lock |
|        57 |             16 | NULL           | Waiting for global read lock |
|        58 |             17 | NULL           | Waiting for global read lock |
|        59 |             18 | NULL           | Waiting for global read lock |
|        60 |             19 | NULL           | Waiting for global read lock |
|        61 |             20 | NULL           | Waiting for global read lock |
+-----------+----------------+----------------+------------------------------+
8 rows in set (0.00 sec)
        

It is clear that the replication SQL threads are not doing any useful job and just waiting for a global read lock.

To find out which thread is holding a global read lock try querying table threads in the Performance Schema, but this time filter out replica threads:

mysql> select thread_id, PROCESSLIST_ID, PROCESSLIST_DB, PROCESSLIST_STATE, PROCESSLIST_INFO
    -> from performance_schema.threads
    -> where name != 'thread/sql/slave_worker' and processlist_id is not nullG
*************************** 1. row ***************************
        thread_id: 46
   PROCESSLIST_ID: 7
   PROCESSLIST_DB: NULL
PROCESSLIST_STATE: Waiting on empty queue 
 PROCESSLIST_INFO: NULL
*************************** 2. row ***************************
        thread_id: 50
   PROCESSLIST_ID: 9
   PROCESSLIST_DB: NULL
PROCESSLIST_STATE: Suspending
 PROCESSLIST_INFO: NULL
*************************** 3. row ***************************
        thread_id: 52
   PROCESSLIST_ID: 11
   PROCESSLIST_DB: NULL
PROCESSLIST_STATE: Waiting for master to send event 
 PROCESSLIST_INFO: NULL
*************************** 4. row ***************************
        thread_id: 53
   PROCESSLIST_ID: 12
   PROCESSLIST_DB: NULL
PROCESSLIST_STATE: Waiting for slave workers to process their queues
 PROCESSLIST_INFO: NULL
*************************** 5. row ***************************
        thread_id: 64
   PROCESSLIST_ID: 23
   PROCESSLIST_DB: performance_schema
PROCESSLIST_STATE: executing
 PROCESSLIST_INFO: select thread_id, PROCESSLIST_ID, PROCESSLIST_DB, PROCESSLIST_STATE, ↩
                   PROCESSLIST_INFO from performance_schema.threads where ↩
                   name != 'thread/sql/slave_worker' and processlist_id is not null
*************************** 6. row ***************************
        thread_id: 65
   PROCESSLIST_ID: 24
   PROCESSLIST_DB: NULL
PROCESSLIST_STATE: NULL
 PROCESSLIST_INFO: flush tables with read lock
6 rows in set (0.00 sec)
        

In our example offending thread is the thread executed FLUSH TABLES WITH READ LOCK. This is a common safety lock, performed by backup programs. Since we know the reason of the replica stall, we can either wait until this job finishes or kill the thread. Once done, replica will continue executing updates.

See Also

Troubleshooting performance is a long topic and further detail is outside the scope of this book. For additional information about troubleshooting, see MySQL Troubleshooting.

2.17 Replication Automation

Problem

You want to setup replication, but do not want to configure it manually.

Solution

Use MySQL Admin API, available in MySQL Shell ([Link to Come]).

Discussion

MySQL Shell provides MySQL Admin API that allows you to automate standard replication administrative tasks, such as creating a ReplicaSet of a source server with one or more replicas. Or create InnoDB Cluster, using Group Replication.

InnoDB ReplicaSet

If you want to automate replication setup use MySQL Admin API inside MySQL Shell and InnoDB ReplicaSet. InnoDB ReplicaSet allows you to create a single-primary replication topology with as many secondary read-only servers as you wish. You may later promote one of the secondary servers to primary. Multiple-primary setups, replication filters and automatic failovers are not supported.

First you need to prepare the servers. Ensure that:

  • MySQL is of version 8.0 or newer
  • GTID options gtid_mode and enforce_gtid_consistency are enabled
  • Binary log format is ROW
  • Default storage engine is InnoDB: set option default_storage_engine=InnoDB

Warning

If you are using Ubuntu and want to setup ReplicaSet on the local machine edit /etc/hosts file and either remove loopback address 127.0.1.1 or replace it with 127.0.0.1. Loopback addresses, other than 127.0.0.1 are not supported by MySQL Shell.

Once servers are prepared for the replication you can start configuring them:

 MySQL  127.0.0.1:13000 ssl  JS > dba.configureReplicaSetInstance(
                               -> 'root@127.0.0.1:13000', {clusterAdmin: "'repl'@'%'"})
                               ->
Please provide the password for 'root@127.0.0.1:13000': 
Configuring local MySQL instance listening at port 13000 for use in an InnoDB ReplicaSet...

This instance reports its own address as Delly-7390:13000
Clients and other cluster members will communicate with it through this address by default. 
If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: ********
Confirm password: ********

The instance 'Delly-7390:13000' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'Delly-7390:13000' is already ready to be used in an InnoDB ReplicaSet.
          

Command dba.configureReplicaSetInstance takes two parameters: URI to connect to the server and configuration options. Option clusterAdmin instructs to create a replication user. Then you may provide a password when prompted.

Repeat configuration step for all servers in the ReplicaSet. Specify same replication username and password.

Once all instances are configured, create a ReplicaSet:

 MySQL  127.0.0.1:13000 ssl  JS > var rs = dba.createReplicaSet("cookbook")
A new replicaset with instance 'Delly-7390:13000' will be created.

* Checking MySQL instance at Delly-7390:13000

This instance reports its own address as Delly-7390:13000
Delly-7390:13000: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for Delly-7390:13000.
Use rs.addInstance() to add more asynchronously replicated instances to this 
replicaset and rs.status() to check its status.
          

Command dba.createReplicaSet creates named ReplicaSet and returns ReplicaSet object. Save it into a variable to perform further management.

Internally it creates a database mysql_innodb_cluster_metadata with tables, describing ReplicaSet setup in the instance MySQL Shell connected to. Same time this first instance is set up as a PRIMARY ReplicaSet member. You may check it if run command rs.status():

 MySQL  127.0.0.1:13000 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "cookbook", 
        "primary": "Delly-7390:13000", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "Delly-7390:13000": {
                "address": "Delly-7390:13000", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}
          

Once PRIMARY instance set up add as many secondary instances as desired:

 MySQL  127.0.0.1:13000 ssl  JS > rs.addInstance('root@127.0.0.1:13002')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as Delly-7390:13002
Delly-7390:13002: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'Delly-7390:13002' has not been pre-provisioned (GTID set is empty). 
The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone 
provisioning, which will completely overwrite the state of 'Delly-7390:13002' with 
a physical snapshot from an existing replicaset member. 
To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of 
the new instance if you are sure all updates ever executed in the replicaset were done 
with GTIDs enabled, there are no purged transactions and the new instance contains the 
same GTID set as the replicaset or a subset of it. To use this method by default, 
set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: Delly-7390:13002 is being cloned from delly-7390:13000
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ============================================================    0%  In Progress
    REDO COPY  ============================================================    0%  Not Started

NOTE: Delly-7390:13002 is shutting down...

* Waiting for server restart... ready
* Delly-7390:13002 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 60.00 MB transferred in about 1 second (~60.00 MB/s)

** Configuring Delly-7390:13002 to replicate from Delly-7390:13000
** Waiting for new instance to synchronize with PRIMARY...

The instance 'Delly-7390:13002' was added to the replicaset and is replicating 
from Delly-7390:13000.
          

Each secondary instance performs initial data copy from the PRIMARY member. It can copy data using either clone plugin or incremental recovery from the binary logs. For the server which already has data method clone is preferrable. But you may need to manually restart the server to finish the installation. If you have chosen incremental recovery ensure that no binary log, containing data, is purged. Otherwise replication setup will fail.

Once all secondary members are added ReplicaSet is ready and can be used for writes and reads. You can check its status by running command rs.status(). It supports option extended, controlling verbosity of the output. Still it does not show all the information about replication health. If you want to have all the details use SHOW SLAVE STATUS command or query Performance Schema.

If you want to change which server is a PRIMARY use rs.setPrimaryInstance command. Thus, rs.setPrimaryInstance(“127.0.0.1:13002”) switches PRIMARY server from the server, running on the port 13000 to the server, listening port 13002.

If you disconnected from a server, participating in the ReplicaSet or destroyed ReplicaSet object, reconnect to one of ReplicaSet members and run command rs=dba.getReplicaSet() to re-create ReplicaSet object.

Warning

If you want to manage ReplicaSet with MySQL Shell do not modify replication setup directly by running CHANGE MASTER command. All management should happen via Admin API in MySQL Shell.

InnoDB Cluster

To automate Group Replication create MySQL InnoDB Cluster. InnoDB Cluster is a complete high availability solution that allows you to easily configure and administer a group of at least three MySQL Servers.

Before setting up InnoDB Cluster prepare the servers. Each of the servers in the group should have:

  • Unique server id
  • GTID enabled
  • Option disabled_storage_engines set to "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
  • Option log_slave_updates enabled
  • User account with administrative privileges

You may set other options (???), required for the group replication, but they can also be configured by the MySQL Shell.

Once you setup and started MySQL instances connect MySQL Shell to the one you want to make PRIMARY and configure them.

 MySQL  127.0.0.1:33367 ssl  JS > dba.configureInstance('root@127.0.0.1:33367', 
                               -> {clusterAdmin: "grepl", clusterAdminPassword: "greplgrepl"})
                               ->
Please provide the password for 'root@127.0.0.1:33367': 
Configuring local MySQL instance listening at port 33367 for use in an InnoDB cluster...

This instance reports its own address as Delly-7390:33367
Clients and other cluster members will communicate with it through this address by default. 
If this is not correct, the report_host MySQL system variable should be changed.
Assuming full account name 'grepl'@'%' for grepl

The instance 'Delly-7390:33367' is valid to be used in an InnoDB cluster.

Cluster admin user 'grepl'@'%' created.
The instance 'Delly-7390:33367' is already ready to be used in an InnoDB cluster.
          

Repeat configuration for other instances in the cluster.

Warning

If an instance is manually configured for Group Replication MySQL Shell would not be able to update its options and would not ensure that the group replication configuration persist after restart. Always run dba.configureInstance before setting up InnoDB Cluster.

After instances are configured create a cluster:

 MySQL  127.0.0.1:33367 ssl  JS > var cluster = dba.createCluster('cookbook', 
                               -> {localAddress: ":34367"})
                               ->
A new InnoDB cluster will be created on instance '127.0.0.1:33367'.

Validating instance configuration at 127.0.0.1:33367...

This instance reports its own address as Delly-7390:33367

Instance configuration is suitable.
Creating InnoDB cluster 'cookbook' on 'Delly-7390:33367'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
          

Then add instances to it: cluster.addInstance('root@127.0.0.1:33368', {localAddress: “:34368"}). When MySQL Shell asks you to select a recovery method choose “Clone”. Then, depending if your server supports RESTART command either wait when it is back online or start the node manually. In case of success you will see a message, similar to:

State recovery already finished for 'Delly-7390:33368'

The instance '127.0.0.1:33368' was successfully added to the cluster.
          

Add other instances to the cluster.

Tip

MySQL Shell constructs a local address which Group nodes use to communicate with each other by using the system variable report_host for the host address and formula (current port of the instance) * 10 + 1 for the port number. If the auto-generated value exceeds 65535 the instance cannot be added to the cluster. Therefore, if you use non-standard ports, specify the custom value for the option localAddress.

After instances are added InnoDB Cluster is ready to use. To examine its status use cluster.status() command which supports extended key, controlling verbosity of the output. Default is 0: only basic information printed. With option 2 and 3 you may examine which transactions are received and applied on each member. Command cluster.describe() gives a short overview of the cluster topology.

 MySQL  127.0.0.1:33367 ssl  JS > cluster.describe()
{
    "clusterName": "cookbook", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "Delly-7390:33367", 
                "label": "Delly-7390:33367", 
                "role": "HA"
            }, 
            {
                "address": "Delly-7390:33368", 
                "label": "Delly-7390:33368", 
                "role": "HA"
            }, 
            {
                "address": "Delly-7390:33369", 
                "label": "Delly-7390:33369", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}
          

If you destroyed the Cluster object reconnect to one of the cluster members and re-create it by running command cluster = dba.getCluster().

Note

Both InnoDB ReplicaSet and InnoDB Cluster support software router MySQL Router which you can use for load balancing. We skipped this part, because this is outside of the scope of the book. For the information on how to setup MySQL Router together with InnoDB ReplicaSet and InnoDB Cluster consult the User Reference Manual.

See Also

For additional information about replication automation, see MySQL Shell User Reference Manual.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset