MySQL group replication is a plug-in to MySQL server, and each server in the group needs to configure and install the plug-in. This section provides a detailed tutorial that contains the steps required to create a replication group of at least three servers.
18.2.1 Deploy group replication in single master mode
Each server instance in a group can run either on a separate physical machine or on the same machine. This section describes how to create a replication group with three MySQL Server instances on a physical machine. This means that you need three data directories, one for each server instance, and each instance needs to be configured separately.
Figure 18.4
This tutorial describes how to use the group replication plug-in to get and deploy MySQL Server, how to configure each server instance before creating a group, and how to use Performance Schema to verify that everything is working.
18.2.1.1 Deployment Group Replication Example
The first step is to deploy three instances of MySQL server. Group replication is a built-in MySQL plug-in provided by MySQL Server 8.0. For more background information on MySQL plug-ins, see Section 5.6, "MySQL Server Plug-ins". After downloading the MySQL server package, you need to decompress and install the binary files. This process assumes that the MySQL server has downloaded and decompressed to the current directory, which needs to be in the mysql-8.0 directory. Because this tutorial uses a physical machine, each MySQL instance needs a specific data directory for storing the data of the instance. Create a data directory in a directory named data and initialize each directory.
mkdir data mysql-8.0/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-8.0 --datadir=$PWD/data/s1 mysql-8.0/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-8.0 --datadir=$PWD/data/s2 mysql-8.0/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-8.0 --datadir=$PWD/data/s3
data/s1, data/s2 and data/s3 are initialized data directories, including MySQL database and related tables. For more information about the initialization process, see Section 2.10.1, "Initializing the Data Directory".
Warning
Don't use initialize-insecure in production environments, it's only used to simplify tutorials. For more information on security settings, see Section 18.5, Group Replication Security.
18.2.1.2 Configuration Group Replication Example
This section describes the configuration settings required for MySQL Server instances to be used for group replication. For background information, see Section 18.8.2, Group Replication Restrictions.
Group Replication Server Settings
To install and use the group replication plug-in, you must configure the MySQL server instance correctly. It is recommended that the configuration be stored in my.cnf file. For more information, see Section 4.2.7, Use of Documents. Without special explanation, the following is the configuration of the first instance in the group, which is called s1 in this section. The following section shows the example configuration of server.
[mysqld] # server configuration datadir=<full_path_to_data>/data/s1 basedir=<full_path_to_bin>/mysql-8.0/ port=24801 socket=<full_path_to_sock_dir>/s1.sock
These settings configure MySQL server to use the previously created data directory, configure which port the server should open, and start listening for incoming connections.
Note
Non-default port 24801 is used here because in this tutorial, three server instances use the same host name. In environments with three different machines, this setting is not necessary.
Group replication requires network interaction among members, which means that each member must be able to resolve the network addresses of all other members. For example, in this tutorial, all three instances run on a single machine, so to ensure that members can relate to each other, you can also add a line to the configuration file, such as report_host=127.0.0.1.
Replication framework
The following settings configure replication according to MySQL group replication requirements.
server_id=1 gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE
These settings configure server to use unique identifier 1 to enable global transaction identifiers to allow only statements based on GTID security records to be executed and to disable binary log event checksum.
If you are using MySQL version less than 8.0.3, where the default values have been improved for replication, you need to add these lines to the member's configuration file.
log_bin=binlog log_slave_updates=ON binlog_format=ROW master_info_repository=TABLE relay_log_info_repository=TABLE
These settings specify that server opens binary log records, stores replicated metadata in system tables rather than files, and disables binary log event checksum using row-based format. For more details, see Section 18.8.1, Group Replication Requirements.
Group replication settings
Ensure that my.cnf files in server are configured as required at this time, and that server replicates the infrastructure as instantiated by configuration. The following is the configuration of server group replication.
transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "127.0.0.1:24901" group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903" group_replication_bootstrap_group=off
- Configuring transaction_write_set_extraction indicates that for each transaction, the server must collect the write set and encode it as a hash using the XXHASH64 hash algorithm. Starting with MySQL 8.0.2, this setting is the default setting, so you can omit this line.
- Configuring group_replication_group_name tells the plug-in that the group it is joining or creating is named "aaaaaaaaaa-aaaaaa-aaaaaa-aaaa AAAA aaaa"<br/> value group_replication_group_name must be a valid UUID. When GTID is set for group replication events in binary logs, this UUID is used internally. You can use SELECT UUID() to generate a UUID.
- Configuring group_replication_start_on_boot indicates that the plug-in does not automatically start group replication at server startup. This is important when setting up group replication, because it ensures that you can configure the server before starting the plug-in manually. After configuring members, you can set group_replication_start_on_boot to on to automatically start Group Replication when the server restarts.
- Configuring group_replication_local_address tells the plug-in to use IP address 127.0.0.1 and port 24901 to communicate internally with other members of the group.
Important
server listens for connections between group members on this port. This port cannot be used for user applications, it must be reserved for communication between different members of the group when running group replication.
The local address configured by group_replication_local_address must be accessible to all group members. For example, if each server instance is on a different computer, you can use the IP address of the computer, such as 10.0.0.1. If you use a host name, you must use a fully qualified name and make sure that it can be resolved through DNS, and configure the correct / etc/hosts file or other domain name resolution process. Starting with MySQL 8.0.14, you can use the IPv6 address (or resolvable to its host name) and the IPv4 address. A group can contain a mix of members using IPv6 and members using IPv4. For more information on IPv6 networks and group replication support for mixed IPv4 and IPv6 groups, see Section 18.4.5, "Support IPv6 and mixed IPv6 and IPv4 groups".
The recommended port group_replication_local_address is 33061. In this tutorial, we use three server instances running on a computer, so we use ports 24901 to 24903 for internal communication. group_replication_local_address Group Replication uses it as a unique identifier for group members in replication groups. As long as the host name or IP address is different, you can use the same port for all members of the group replication, and as shown in this tutorial, you can use the same host name or IP address as long as you have the same host name or IP address. But the ports are different.
- Configure group_replication_group_seeds to set the hostname and port of group members, and new members use them to establish connections to groups. These members are called seed members. After the connection is established, the group membership information is in the performance_schema.replication_group_members table. Typically, the group_replication_group_seeds list contains hostname: the list of group_replication_local_address for each group member of the port, but this is not mandatory, and a subset of group members can be selected as seeds.
Important
The hostname:port is listed in the group_replication_group_seeds internal IP address of the seed member, and is used for client connection based on configuration group_replication_local_address instead of SQL hostname:port, and is displayed in the performance_schema.replication_group_members table.
The server of the startup group does not use this option because it is the initial server, so it is responsible for booting the group. The second server applies to the only member of the group for membership, and then the group is expanded. The third server can apply to either of the two servers and then expand the group again. The subsequent server repeats this process when it joins.
Warning
When multiple server s join at the same time, make sure they are seed members in the group. Do not use seed members who are also applying for membership because they may not join the group at the time of their visit.
Start the boot member and let it create a group, then make it a seed member of the remaining members being added. This ensures that there is a group when the remaining members are added.
It is not supported to create groups and join multiple members at the same time. This may occur during operational competition, but the behavior of joining a group eventually results in errors or timeouts.
Members to join must communicate with seed members using the same protocol (IPv4 or IPv6) that seed members advertise in the group_replication_group_seeds option. For the purpose of group duplicate IP address whitelist, the whitelist on the seed member must contain the IP address of the joining member of the protocol provided by the seed member, or resolve to the host name of the address of the protocol. In addition to membership, this address or host name must be set and placed on the whitelist group_replication_local_address if the protocol for that address does not match the Notification Protocol for the seed member. If a member does not have a whitelist address of an appropriate agreement, the join attempt is rejected. For more information, see section 18.5.1, "White List of IP Addresses".
- Configuring group_replication_bootstrap_group indicates whether the plug-in is a boot group.
Important
This option can only be used on one server instance at any time, usually when the group is first booted (or when the entire group is crashed and then restored). If you boot groups many times, for example, when multiple server instances set this option, they may artificially cause fissures, where there are two different groups with the same name. Disable this option after the first server instance joins a group.
The configuration of all server members in the group is very similar. You need to change the details of each server (such as server_id, datadir, group_replication_local_address). This will be introduced later in this tutorial.
18.2.1.3 User Credentials
Group replication uses asynchronous replication protocol to achieve distributed recovery and synchronizes group members before they join the group. The distributed recovery process relies on the group_replication_recovery replication channel, which is used to transfer transactions between group members. Therefore, you need to set up a replication user with the correct permissions so that group replication can directly establish a member-to-member recovery replication channel.
Start the server with a configuration file:
mysql-8.0/bin/mysqld --defaults-file=data/s1/s1.cnf
Create MySQL users with REPLICATION-SLAVE privileges. This action should not be logged in the binary log to avoid passing changes to other server instances. Connect to server s1 and execute the following statement:
mysql> SET SQL_LOG_BIN=0;
The following example demonstrates the process of creating a user rpl_user with the password rpl_pass. You need to use the correct username and password when configuring the server.
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; mysql> FLUSH PRIVILEGES;
If binary logging is disabled, it is enabled again after the user is created.
mysql> SET SQL_LOG_BIN=1;
After the above configuration, users need to configure the server using the CHANGE MASTER TO statement, and use group_replication_recovery to replicate the given credentials of the channel the next time they need to restore their status from other members. Execute the following command to replace rpl_user and rpl_pass with the values used when creating users.
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' \\
FOR CHANNEL 'group_replication_recovery';Distributed recovery is the first step in server execution when joining a group. If these credentials are not set correctly, the server will not be able to execute the recovery process and synchronize with other group members, so eventually it will not be able to join the group.
Similarly, the recovery process may fail if a member fails to correctly identify other members by the server's host name. It is recommended that all operating systems running MySQL correctly configure the unique host name, using DNS or local settings. This host name can be validated in the Member_host column of the performance_schema.replication_group_members table. If multiple group members use the default host name set by the operating system, there will be a situation where members can not resolve to the correct member address and can not join the group. In this case, report_host can be used to configure the unique host name for each server.
Use group replication and aching SHA-2 user credentials plug-in
By default, users created in MySQL 8 use section 6.5.1.3, "Caching SHA-2 plug-in authentication". If you configure the user_rpl_user_for distributed recovery to use the cache SHA-2 authentication plug-in without using the group_replication_recovery replication channel of Secure Socket Layer Support (SSL), RSA key for password exchange, see Section 6.4.3, "Creating SSL and RSA certificates and keys". You can copy the public key of a member whose rpl_user should restore its state from the group to that group, or configure the donor to provide the public key when requested.
A safer approach is to copy the public key rpl_user to a member who should restore group status from the donor. Then, you need group_replication_recovery_public_key_path to configure the system variables on the members of the join group and provide the path rpl_user for the public key.
Optionally, the non * full method is to set group_replication_recovery_get_public_key=ON donors so that their rpl_user can provide members'public keys when they join the group. The identity of the server cannot be authenticated, so only group_replication_recovery_get_public_key=ON is set when you determine that there is no risk of server identity being compromised, such as through an intermediary ***.
18.2.1.4 Initiation group replication
After configuring and starting server s1, install the group replication plug-in. Then connect to server and execute the following commands:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Important
Before loading group replication, mysql.session users must exist. mysql.session user added in MySQL version 8.0.2. If you need to initialize the data dictionary with an earlier version, you must perform the MySQL upgrade process (see Section 2.11, "Upgrading MySQL"). If the upgrade is not running, the group replication starts with an error when trying to access the server with user: mysql.session@localhost. Make sure that the user is present in the server and that mysql_upgrade was ran after a server update.
To check whether the plug-in has been successfully installed, execute SHOW PLUGINS and check the output. It should be shown as follows:
mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+
To start a group, instruct server s1 to boot the group, and then start the group replicator. This boot should be done independently by a single sever, which starts the server group and starts only once. This is why the value of boot configuration options is not saved in the configuration file. If it is saved in the configuration file, the server automatically boots the second group with the same name when it restarts. This will result in two different groups having the same name. The same applies to stopping and restarting plug-ins, and this option is set to ON.
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;
When the START GROUP_REPLICATION statement returns, the group is started. You can check that the group is now created and that there is already a member of it:
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+---------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ | group_replication_applier | ce9be252-2b71-11e6-b8f4-00212844f856 | myhost | 24801 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+---------------+
The information in this table confirms that there is a member in the group and has a unique identifier ce9be252-2b71-11e6-b8f4-00212844f856, which is an online client connection on myhost listening port 24801.
To demonstrate that server s are indeed in a group and can handle loading, create a table and add something to it.
mysql> CREATE DATABASE test; mysql> USE test; mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); mysql> INSERT INTO t1 VALUES (1, 'Luis');
Check the contents of tables t1 and binary logs.
mysql> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ mysql> SHOW BINLOG EVENTS; +---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 8.0.2-gr080-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 1 | 150 | | | binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' | | binlog.000001 | 211 | Query | 1 | 270 | BEGIN | | binlog.000001 | 270 | View_change | 1 | 369 | view_id=14724817264259180:1 | | binlog.000001 | 369 | Query | 1 | 434 | COMMIT | | binlog.000001 | 434 | Gtid | 1 | 495 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2' | | binlog.000001 | 495 | Query | 1 | 585 | CREATE DATABASE test | | binlog.000001 | 585 | Gtid | 1 | 646 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3' | | binlog.000001 | 646 | Query | 1 | 770 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) | | binlog.000001 | 770 | Gtid | 1 | 831 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4' | | binlog.000001 | 831 | Query | 1 | 899 | BEGIN | | binlog.000001 | 899 | Table_map | 1 | 942 | table_id: 108 (test.t1) | | binlog.000001 | 942 | Write_rows | 1 | 984 | table_id: 108 flags: STMT_END_F | | binlog.000001 | 984 | Xid | 1 | 1011 | COMMIT /* xid=38 */ | +---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
As shown above, database and table objects are created and their corresponding DDL statements are written to binary logs. In addition, data is inserted into tables and written to binary logs. The importance of binary log entries will be explained in the following sections as group members grow and distributed recovery is performed as new members try to join and become online.
18.2.1.5 Adding instances to groups
At this point, there is a member of the group, and server s1 already has some data. At this point, you can extend the group by adding two other servers previously configured.
18.2.1.5.1 Add a second instance
To add the second instance server s2, first create a configuration file for it. This configuration is similar to the configuration for server s1, except for things like the location of the data directory, the port that S2 will listen on, or its server_id. These different rows are highlighted in the list below.
[mysqld] # server configuration datadir=<full_path_to_data>/data/s2 basedir=<full_path_to_bin>/mysql-8.0/ port=24802 socket=<full_path_to_sock_dir>/s2.sock # # Replication configuration parameters # server_id=2 gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE # # Group Replication configuration # transaction_write_set_extraction=XXHASH64 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "127.0.0.1:24902" group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903" group_replication_bootstrap_group= off
The process of server s1 is similar, starting the server when the configuration file is in place.
mysql-8.0/bin/mysqld --defaults-file=data/s2/s2.cnf
Then configure the recovery credentials as shown below. Because users share in groups, this command is the same as the command used when setting server s1. Execute the following statement on s2.
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' \\
FOR CHANNEL 'group_replication_recovery';Tip
If you are using the cache SHA-2 authentication plug-in (default settings in MySQL 8), refer to the use of group replication and cache SHA-2 user credentials plug-in.
Install the group replication plug-in and start the program to add server to the group. The following example installs the plug-in in the same way as when deploying server s1.
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Add server s2 to the group.
mysql> START GROUP_REPLICATION;
Unlike previous steps, here is a difference from those performed on s1, that is, no SET GLOBAL group_replication_bootstrap_group = ON operation is performed; before starting group replication, the group is created and booted by server s1. At this point, server s2 only needs to be added to existing groups.
Tip
When group replication starts successfully and the server joins the group, it checks the super_read_only variable. By setting super_read_only to ON in the member's configuration file, you can ensure that the server that fails to start group replication for any reason does not accept transactions. If the server should add this group as a read-write instance, for example, as a member of a primary group or multiple primary groups in a single primary group, then when the super_read_only variable is set to ON, it will be set to OFF when it joins.
Check the performance_schema.replication_group_members table again, and you can see that there are now two ONLINE server s in the group.
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+---------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ | group_replication_applier | 395409e1-6dfa-11e6-970b-00212844f856 | myhost | 24801 | ONLINE | | group_replication_applier | ac39f1e6-6dfa-11e6-a69d-00212844f856 | myhost | 24802 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+---------------+
Since server s2 is also marked ONLINE, it must be synchronized with server s1. Verify that it does synchronize with server S1 as follows.
mysql> SHOW DATABASES LIKE 'test'; +-----------------+ | Database (test) | +-----------------+ | test | +-----------------+ mysql> SELECT * FROM test.t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ mysql> SHOW BINLOG EVENTS; +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 2 | 123 | Server ver: 8.0.3-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 2 | 150 | | | binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' | | binlog.000001 | 211 | Query | 1 | 270 | BEGIN | | binlog.000001 | 270 | View_change | 1 | 369 | view_id=14724832985483517:1 | | binlog.000001 | 369 | Query | 1 | 434 | COMMIT | | binlog.000001 | 434 | Gtid | 1 | 495 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2' | | binlog.000001 | 495 | Query | 1 | 585 | CREATE DATABASE test | | binlog.000001 | 585 | Gtid | 1 | 646 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3' | | binlog.000001 | 646 | Query | 1 | 770 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) | | binlog.000001 | 770 | Gtid | 1 | 831 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4' | | binlog.000001 | 831 | Query | 1 | 890 | BEGIN | | binlog.000001 | 890 | Table_map | 1 | 933 | table_id: 108 (test.t1) | | binlog.000001 | 933 | Write_rows | 1 | 975 | table_id: 108 flags: STMT_END_F | | binlog.000001 | 975 | Xid | 1 | 1002 | COMMIT /* xid=30 */ | | binlog.000001 | 1002 | Gtid | 1 | 1063 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:5' | | binlog.000001 | 1063 | Query | 1 | 1122 | BEGIN | | binlog.000001 | 1122 | View_change | 1 | 1261 | view_id=14724832985483517:2 | | binlog.000001 | 1261 | Query | 1 | 1326 | COMMIT | +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
As shown above, the second server has been added to the group and the changes have been automatically copied from server s1. According to the distributed recovery process, this means that after joining a group and before being declared online, server s2 automatically connects to server S1 and retrieves lost data from it. In other words, it copies from the binary log of S1 what it lacks before joining the group's time node.
18.2.1.5.2 Adding other examples
Adding other instances to a group is essentially the same step as adding a second server, except that the configuration must be changed to the corresponding server. Summarize the commands required as follows:
1. Create configuration files
[mysqld] # server configuration datadir=<full_path_to_data>/data/s3 basedir=<full_path_to_bin>/mysql-8.0/ port=24803 socket=<full_path_to_sock_dir>/s3.sock # # Replication configuration parameters # server_id=3 gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE # # Group Replication configuration # group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "127.0.0.1:24903" group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903" group_replication_bootstrap_group= off
2. Start the server
mysql-8.0/bin/mysqld --defaults-file=data/s3/s3.cnf
_ 3. Configure the recovery credentials for the group_replication recovery channel.
SET SQL_LOG_BIN=0; CREATE USER rpl_user@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' \\ FOR CHANNEL 'group_replication_recovery';
4. Install the Group Replication plug-in and start it.
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; START GROUP_REPLICATION;
At this point, server s3 is booted and running, and has joined the group and synchronized with other server members in the group. Visit the performance_schema.replication_group_members table to confirm again that this is true.
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+---------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ | group_replication_applier | 395409e1-6dfa-11e6-970b-00212844f856 | myhost | 24801 | ONLINE | | group_replication_applier | 7eb217ff-6df3-11e6-966c-00212844f856 | myhost | 24803 | ONLINE | | group_replication_applier | ac39f1e6-6dfa-11e6-a69d-00212844f856 | myhost | 24802 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+---------------+
Sending this same query on server s2 or server s1 will produce the same results. In addition, you can verify that server s3 is synchronized:
mysql> SHOW DATABASES LIKE 'test'; +-----------------+ | Database (test) | +-----------------+ | test | +-----------------+ mysql> SELECT * FROM test.t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ mysql> SHOW BINLOG EVENTS; +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 3 | 123 | Server ver: 8.0.3-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 3 | 150 | | | binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' | | binlog.000001 | 211 | Query | 1 | 270 | BEGIN | | binlog.000001 | 270 | View_change | 1 | 369 | view_id=14724832985483517:1 | | binlog.000001 | 369 | Query | 1 | 434 | COMMIT | | binlog.000001 | 434 | Gtid | 1 | 495 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2' | | binlog.000001 | 495 | Query | 1 | 585 | CREATE DATABASE test | | binlog.000001 | 585 | Gtid | 1 | 646 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3' | | binlog.000001 | 646 | Query | 1 | 770 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) | | binlog.000001 | 770 | Gtid | 1 | 831 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4' | | binlog.000001 | 831 | Query | 1 | 890 | BEGIN | | binlog.000001 | 890 | Table_map | 1 | 933 | table_id: 108 (test.t1) | | binlog.000001 | 933 | Write_rows | 1 | 975 | table_id: 108 flags: STMT_END_F | | binlog.000001 | 975 | Xid | 1 | 1002 | COMMIT /* xid=29 */ | | binlog.000001 | 1002 | Gtid | 1 | 1063 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:5' | | binlog.000001 | 1063 | Query | 1 | 1122 | BEGIN | | binlog.000001 | 1122 | View_change | 1 | 1261 | view_id=14724832985483517:2 | | binlog.000001 | 1261 | Query | 1 | 1326 | COMMIT | | binlog.000001 | 1326 | Gtid | 1 | 1387 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:6' | | binlog.000001 | 1387 | Query | 1 | 1446 | BEGIN | | binlog.000001 | 1446 | View_change | 1 | 1585 | view_id=14724832985483517:3 | | binlog.000001 | 1585 | Query | 1 | 1650 | COMMIT | +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
