Replicating data between two MySQL Group Replication sets using “regular” asynchronous replication with Global Transaction Identifiers (GTID’s)

MySQL introduced Group Replication (GR) in version 5.7, and GR is part of the InnoDB Cluster high-availability solution. InnoDB Cluster consists of Group Replication, MySQL Shell and MySQL Router.

I am not going to explain InnoDB Cluster or Group Replication in this post. So, if you aren’t familiar with either one, I have some previous posts in which I have explained how to work with both. See:

MySQL 8.0 Group Replication – Three-server installation

MySQL 8.0 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication

Adding a replicated MySQL database instance using a Group Replication server as the source


A customer wanted to know how to replicate data between two separate three-node Group Replication groups. They have two data centers in different states, and they want to use each GR group for a different application. And, they wanted to replicate the data between the two (for various reasons).

For Group Replication (GR), you must have a minimum of three nodes in the group, and you can have a maximum of nine nodes. The GR group can be either in single-primary or multiple-primary mode. A five-node Group Replication Group looks like this:

For this (fictional) example, I will be using two three-node Group Replication groups. One group is in Atlanta, Georgia, and the other is in Tampa, Florida. Besides the standard group replication requirements, there are a couple of items you need to take into consideration if you decide to try this. First, this will probably not work very well if you have a very write-heavy application. Group Replication does support multi-threading, but the replicated databases may not always be up-to-date with the source database. Also, please note that you need will need a very good network connection between the two groups. And, if one GR group completely fails, there is a good chance some of the data might not have had time to replicate over to the other group. Just remember – your replication performancee may vary. For this post, I am using six virtual machines on a single server.

The topology so far…

To replicate the data between two groups, you only need to use use “regular” MySQL asynchronous replication with Global Transaction Identifiers (GTIDs)

Note: To learn more about replicating with GTIDs, I wrote two posts explaining the “how to” – see part one and part two). GTID’s are required when using Group Replication.

For the Atlanta group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.151, 192.168.1.152 and 192.168.1.153. For the Tampa group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.161, 192.168.1.162 and 192.168.1.163. The groups look like this:

With our two three-node groups, I will use the primary-write node in the first group to be the source (master) database for the primary-write node (replica/slave) in the second group – and vice-versa.

In the Atlanta group, I will use the primary-write node with the IP address of 192.168.1.151 (MEMBER_HOST MacVM151.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.161).

In the Tampa group, I will use the primary-write node with the IP address of 192.168.1.161 (MEMBER_HOST MacVM161.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.151).

The other nodes in both groups are set to SUPER READ-ONLY, so they can’t be used as a replica (slave) database source, as the replica (slave) needs to be able to perform writes. The topology will looks something like this – with the arrows showing the way the data is replicated (or “flows”):

If that is too confusing, here is a simplified layout: (ATL = Atlanta, TPA = Tampa)

The members of each group

In the Atlanta Group, I have the following members in the Group Replication group:

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

In this group, I want the first node in the list (MEMBER_HOST MacVM151.local) to be the primary, but for some reason, the second node (MacVM152.local) is the primary. To change the PRIMARY to be the first server in the list, I can issue this command:

SELECT group_replication_set_as_primary(‘member_uuid’); (where the member_uuid is equal to the MEMBER_ID of the first node (‘c727957e-4cb6-11e9-abd5-f80a484a9c32’) from the above output.)

mysql> SELECT group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32') |
+--------------------------------------------------------------------------+
| Primary server switched to: c727957e-4cb6-11e9-abd5-f80a484a9c32         |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)

I can verify this change by checking the members again (notice the MEMBER_ROLE column):

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

The Tampa Group is ready to go – as 192.168.1.161 is the PRIMARY server:

Tampa Group:

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | fdc89b12-50d7-11e9-bfa8-012cdcc95c70 | MacVM161.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 1ef93b16-50d8-11e9-b7da-7e47ebc51826 | MacVM162.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 46012962-50d8-11e9-8dc0-de7edddaaccd | MacVM163.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

(Note: Yes, I could have used the second node with IP of 192.168.1.152, but I wanted to use the first node in each group)

Checking the state of each group

I am starting with a clean install of MySQL on all six instances. You can still do this if you have existing data in your groups, but you will need to get the other group to have a beginning set of data. When you begin replication, it won’t automatically export/backup the data and restore/import it to the other group. You will have to backup and restore the data from each group, so the new replicated server will have a starting point at a particular GTID. (I explain how to do this in this post – search for “What if my Group Replication (GR) group already has data?”).

I can see from the GET MASTER STATUS\G on both groups under the Executed_Gtid_Set that I have executed seven and three transactions on the two GR groups, respectively (these transactions were from creating and modifying the group replication views):

Atlanta Group

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2217
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

Tampa Group

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1515
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
1 row in set (0.00 sec)

But, if you want to look at the transactions (which are stored in the binary log mysql-bin.000001 on each server) for each group to verify that you didn’t have any transactions which changed data, you can use the mysqlbinlog tool:

# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
....
SET TIMESTAMP=1553731529/*!*/;
COMMIT
....

I checked both binary logs, and all of the transactions had to do with changing the state of the group replication view. All of these transactions were SET TIMESTAMP transactions (“SET TIMESTAMP=1553731529/*!*/;”). I am now ready to start replication.

Let’s get started

As shown in the above topology images, I am going to setup the Atlanta node (MacVM151.local – IP address 192.168.1.151) to be a source (master) for the Tampa node (MacVM161.local – IP address 192.168.1.161). Since I already have a replication user created for Group Replication, I can use the same user for this “regular” asynchronous replication. If you want more details on asynchronous replication using GTID’s – see this post.

Since I already have a replication channel open with the Group Replication, I will need to specify a new channel. I will name the channel “atl_tpa_replication” (for “Atlanta to Tampa replication”), meaning the Atlanta server will be the source (master) and the Tampa server will be the replica (slave). In replication, it is the job of the replica (slave) to contact the source (master) and retrieve all of the transactions (data) that has not been applied to its database. I will open a MySQL prompt on the Tampa instance with the IP address of 192.168.1.161, and issue this CHANGE MASTER TO statement:

On Tampa

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.151',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'atl_tpa_replication';
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.151',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'atl_tpa_replication';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

I can now start the slave for the “atl_tpa_replication” replication channel, and take a look at the “slave status”:

mysql> start slave for channel 'atl_tpa_replication';
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status for channel 'ATL_TPA_REPLICATION'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.151
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 893
               Relay_Log_File: MacVM161-relay-bin-atl_tpa_replication.000004
                Relay_Log_Pos: 1099
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 893
              Relay_Log_Space: 1919
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 151
                  Master_UUID: c727957e-4cb6-11e9-abd5-f80a484a9c32
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: atl_tpa_replication
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

On the Atlanta group, there were seven transactions which had been applied (GTIDs of 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) – and I can tell this from when I did a “SHOW MASTER STATUS\G” from the Atlanta primary-write server (192.168.1.151):

On Atlanta

mysql>  show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2568
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

I can see which GTID’s I have retrieved (Retrieved_Gtid_Set) and executed (Executed_Gtid_Set) from the SHOW SLAVE STATUS\G command, and these seven GTID’s (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from the server are included:

mysql> show slave status for channel 'atl_tpa_replication'\G
...
           Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...

Since I executed the SHOW SLAVE STATUS\G from the Tampa server, I can also see the existing Tampa GTIDs also fall under the Executed_Gtid_Set section – 160f4761-c55c-422f-8684-d086f6a1db0e:1-3.

Now I can turn on replication to go from Tampa to Atlanta, where the Tampa primary-write server is the source (master) for the Atlanta primary-write server replica (slave). After I run the CHANGE MASTER TO command, I will turn on the replica (slave) with the START SLAVE command. I will then check on the status with SHOW SLAVE STATUS\G.

NOTE: I have changed the CHANNEL name to be tpa_atl_replication (Tampa to Atlanta replication)

On Atlanta

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.161',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'tpa_atl_replication';
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>  show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2568
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.161
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3581
               Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
                Relay_Log_Pos: 1413
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3581
              Relay_Log_Space: 1631
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 161
                  Master_UUID: fdc89b12-50d7-11e9-bfa8-012cdcc95c70
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: tpa_atl_replication
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

From the above, I can now see the three GTIDs from the Tampa server (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) have been replicated over to the Atlanta server, and the seven GTIDs (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from Atlanta also appear in the Executed_Gtid_Set:

           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7

Finally, since I am running Group Replication, these transactions which were replicated between the primary-write servers will also be replicated to the other two servers in each group. I execute the SHOW SLAVE STATUS\G on a secondary server (IP of 192.168.1.152) and see all of the GTID’s which have been replicated between the primary servers:

On secondary server (in Atlanta) with IP of 192.168.1.152:

mysql> show slave status\G
...
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...

Replicate new transactions

Since both of these servers were new installs of MySQL, each instance only has these four databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

I will now create a database on each server, with the GR group location name as the name of the database. These databases will then replicate over to the other groups. And, you will see the executed GTIDs on the source (master) increase from seven (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) to eight (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8).

On Atlanta:

mysql> create database Atlanta;
Query OK, 1 row affected (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 2097
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

When I go to any of the group replication instances in Tampa, I can see the Atlanta database has already been replicated over to the group, and each node has executed this transaction. From node three – 192.168.1.163:

On Tampa

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

And the same happens when I create a database named Tampa – it will get replicated over to the Atlanta Group Replication group:

On Tampa:

mysql> create database Tampa;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| Tampa              |
+--------------------+
6 rows in set (0.00 sec)

On the Tampa Group Replication group, the GTIDs increased from three (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) to four (160f4761-c55c-422f-8684-d086f6a1db0e:1-4). The other GTIDs – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8 – are from the Atlanta Group Replication group. I can confirm this with a SHOW MASTER\G statement:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 3957
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)

Also, I can check to see if the “Tampa” database (which was GTID 160f4761-c55c-422f-8684-d086f6a1db0e:4) has been replicated over to the Atlanta group by running a SHOW SLAVE STATUS\G on any of the Atlanta nodes. I will use the second node (IP address 192.168.1.152):

On Atlanta

mysql> show slave status\G
...
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:4
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
...
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| Tampa              |
+--------------------+
6 rows in set (0.00 sec)

I now have two different MySQL Group Replication groups replicating data between each other.

WARNING: This should only be done if you can ensure that neither group will be modifying the other group’s data. Since replication is not instant, you could make a change on one group at the same time another person is changing the same row – and this scenario would break the replication between the two groups. Or, you could try to modify data that was modified on another group, and you will be modifying stale data.

Can I change the primary-write server in this scenario?

Since I have replication channels on both of the primary-write nodes, what happens if I want to change the primary node to another server? For example, 192.168.1.151 is the PRIMARY for the Atlanta Group Replication, but I want 192.168.1.152 to be the PRIMARY.

On Atlanta, here are the members of the group replication, with 192.168.1.151 (MacVM151.local) as the PRIMARY: (see the MEMBER_ROLE column)

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

If I try and change the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1) to be the PRIMARY server, I will get an error:

From the current PRIMARY node – which is the first node in the group – 192.168.1.151, I will try and make the second node the PRIMARY:

mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
ERROR 13223 (HY000): The function 'group_replication_set_as_primary' failed. There is a slave channel running in the group's current primary member.

In order for me to change the PRIMARY to the second node, I will need to stop the asynchronous replication on the first node (192.168.1.151) by issuing the STOP SLAVE command. This stops the replication between 192.168.1.151 and 192.168.1.161.

I can then change the PRIMARY to be the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1 and IP address of 192.168.1.152). Once the second node is the new PRIMARY, then I can start replication on the second node by running the CHANGE MASTER TO command on 192.168.1.152.

On 192.168.1.151 (Atlanta Group): (Note: You can change the PRIMARY from any node in the Group Replication group)

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

mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1') |
+--------------------------------------------------------------------------+
| Primary server switched to: 247898e0-4cb7-11e9-97a9-12f28adcadd1         |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

I can see the second node is the new PRIMARY: (see the MEMBER_ROLE column)

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Now I can start replication on 192.168.1.152, with 192.168.1.161 as the source (master) by using the CHANGE MASTER TO command.

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.161',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'TPA_ATL_REPLICATION';
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'TPA_ATL_REPLICATION';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

And, the SHOW SLAVE STATUS confirms the SQL and IO threads are running:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.161
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3957
               Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
                Relay_Log_Pos: 400
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Note: With Group Replication, when the PRIMARY goes down, the group will automatically elect a new PRIMARY server, and the group will keep on processing transactions. If you are doing “regular” asynchronous replication from the PRIMARY and it goes down, there isn’t a way to automatically change the source (master), and replication will be broken. You will need to change the replica (slave) to another server in the Group Replication group. And, when the new PRIMARY comes back online, you will need to issue a STOP SLAVE command if the replication is still active on that server. You don’t want two replicas (slaves) attached at the same time.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.
Advertisements

MySQL 8.0 Group Replication – Three-server installation

MySQL InnoDB Cluster was introduced in MySQL version 5.7. MySQL InnoDB Cluster consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. I am not going to go into the details of InnoDB Cluster and how it works, as there are enough manual pages and blogs to cover these topics.

MySQL InnoDB Cluster manual
Blog sites: mysqlhighavailability.com and mysqlserverteam.com.

Instead, I will be showing you how to install Group Replication on three new installations of mysql 8.0 manually, without using the MySQL Shell.

These instructions should enable you to setup Group Replication in less than an hour. I am doing this on a Mac running 10.13, but most of these commands can easily be translated over to Linux or Windows. I will try to supply the correct commands for all three operating systems.

I will be installing Group Replication on three new installations of MySQL (without any data) with the IP addresses (host names) of 192.168.1.151 (ic-1), 192.168.1.152 (ic-2) and 192.168.1.153 (ic-3). It is important that you don’t run any other commands on the server, and that you start with a fresh install of MySQL. If you already have a server with data, you will need to export the data and import it into the other servers before you go any further here. Starting Group Replication with a server with data requires a different set of commands, and this blog might not work in that situation.

Group Replication may be setup as either a single-primary (one server to handle the writes and two servers for reads), or multi-primary (read/write to any of the servers). This post covers setting up a single-primary configuration. The server with the IP address of 192.168.1.151 will be our single-primary (read/write server) and the other two servers will be read-only.

Let’s begin.

Edit your /etc/hosts file, and add the IP addresses and host names for the three servers.

192.168.1.151 ic-1
192.168.1.152 ic-2
192.168.1.153 ic-3

Flush the directory service cache by running this as root:

Mac - dscacheutil -flushcache
Linux - /etc/rc.d/init.d/nscd restart
Windows - ipconfig /flushdns

You will need to add some variables to your MySQL options file, which is located in these directories: (see Using Option Files)

Mac and Linux - /etc/my.cnf
Windows - C:\ProgramData\MySQL\MySQL Server X (where X is the version number of MySQL)

NOTE: On Windows, the my.ini file may be hidden.

The following variables need to be in the options file under the [mysqld] section: (you may remove the # comment lines if you want, but read each line before deleting it)

You will need to change the server_id value for each server so each has a unique ID. I simply used 1, 2 and 3 for the three servers.

# All members in the group requires a unique server_id greater than zero
server_id=1

# this is the default port for MySQL - you can change it if you want, but it should be the same on all servers
port=3306

# you may specify a name for the binary log, or leave it blank to use the default name
# however, binary logging is required
log_bin=mysql-bin  

# these settings are required
log_slave_updates=ON
binlog_checksum=NONE
enforce_gtid_consistency=ON
gtid_mode=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

# Group Replication specific options

# this is the name of the plugin
# load the plugin - in Windows, the plugin will be named group_replication.dll

plugin_load_add ="group_replication.so"

# this is required
group_replication = FORCE_PLUS_PERMANENT

# you will turn this on and back off during Group Replication setup
group_replication_bootstrap_group = OFF

# the group_replication_group_name is the UUID for the entire group
# Each server has their own UUID in the file auto.cnf located in the MySQL data directory
# You can generate your own group_replication_group_name on Linux with `uuidgen -t`, 
# on a Mac use "uuidgen" 
# for Windows - The Windows SDK comes with a tool called uuidgen
# all members use this value as group_replication_group_name

# PLEASE NOTE: this group_replication_group_name must be unique for each Group Replication (GR) group
# so each server within a group will have the same value
# Example: if you have two GR groups with three nodes in each group, then three nodes in one group
# will have the same group_replication_group_name, and the other three nodes in the different
# group will have a different group_replication_group_name
group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E

In the config file make sure group_replication_start_on_boot is set to OFF or add a # (comment) to the line with the value of ON as shown below. You will want to uncomment this line after you setup and start Group Replication, so if the server is rebooted, Group Replication will begin automatically.

# uncomment this line AFTER you have started Group Replication
# so that Group Replication will start after a reboot

#group_replication_start_on_boot = ON

You will need to change this line to match each of the servers, and while you can change the group_replication_local_address (or port number – the recommended default value is 33061), it doesn’t matter what port you use as long as that port isn’t being used by another application.

# change this to be the local address for each server
# the port number can be anything except ports already in use
# and do not use 3306

group_replication_local_address = '192.168.1.151:33061'

This line must contain all of the servers that will be in your group. The group_replication_group_seeds is a list of group members used to establish the connection from the new member to the group.

# add all of the members of the group here, along with the same port numbers

group_replication_group_seeds = '192.168.1.151:33061,192.168.1.152:33061,192.168.1.153:33061'

That is all you need for the configuration file. After you have made the changes, reboot the MySQL instance.


NOTE: If you installed MySQL as the root user, be sure that the OS user “mysql” owns all of the mysql directories. You will need to change the directory name (in this example it is /usr/local) to be the directory where you installed the MySQL Server.

# be sure that the mysql user own the mysql directory
# if you install MySQL via root on a Mac or Linux, there is a good chance that root owns the directory

$ cd /usr/local
$ chown -R mysql mysql*


Now we are ready to install Group Replication. Be sure to restart the mysqld processes to make the /etc/my.cnf changes permanent.

Let’s start with Server #1, which will be our read-write primary server.

On Server #1 (IP 192.168.1.151)

Since we included the variable group_replication_group_seeds in the options file, each instance has already been added to the group.

The table performance_schema.replication_group_members shows network and status information for replication group members. The network addresses shown are the addresses used to connect clients to the group, and should not be confused with the member’s internal group communication address specified by group_replication_local_address. (source: https://dev.mysql.com/doc/refman/8.0/en/replication-group-members-table.html)

You can run this command from a mysql prompt on each of the servers to see if they have joined the group successfully:

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

Or, you can run the command with the \G at the end instead of the semi-colon (;)

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: OFFLINE
   MEMBER_ROLE: 
MEMBER_VERSION: 
1 row in set (0.00 sec)

The MEMBER_ID 60889f20-48ed-11e8-b6e2-0998e2a48fe0 is the UUID for this particular MySQL instance. The UUID is located in the auto.cnf file in the MySQL data directory.

# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=60889f20-48ed-11e8-b6e2-0998e2a48fe0

You can take a look at the MASTER STATUS of the primary server, and it should be relatively blank.

# command to run from MySQL prompt

SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

The active binary log for the server is “mysql-bin.000001“. You can take a look at the events in the log and see nothing has happened on the server.

# command to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001'\G
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 151
End_log_pos: 124
       Info: Server ver: 8.0.11, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 151
End_log_pos: 151
       Info: 
2 rows in set (0.00 sec)


Now go to Server #2 (IP 192.168.1.152)

Confirm that Server #2 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

OPTIONAL: If you want, you can run the same informational commands for Server #2 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)


Now go to Server #3 (IP 192.168.1.153)

Confirm that Server #3 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

OPTIONAL: If you want, you can run the same informational commands on Server #3 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)


Make sure that the “group_replication” plugin is active on all three servers. You value of PLUGIN_STATUS should be ACTIVE. Run this command on all three servers.

# command to run from MySQL prompt

select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G
mysql> select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.2
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.9
         PLUGIN_AUTHOR: ORACLE
    PLUGIN_DESCRIPTION: Group Replication (1.1.0)
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: FORCE_PLUS_PERMANENT
1 row in set (0.00 sec)

You should see the same output on the other two servers (IP 192.168.1.152 and 192.168.1.152) as on Server #1.


Next you will want to create the replication users. Since we will be turning on replication, we don’t want to write this to the binary logs. Execute this command on all of the servers.

# run this on all three servers
# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

NOTE: If you are using SHA-256 for your passwords, you need to specify sha256_password in your CREATE USER statement (see below). See: https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH sha256_password BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

Once the users have been created, we can start execute our CHANGE MASTER statement and start Group Replication. Execute these two commands on all of the servers.

# run this on all three servers

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

We have to create the replication users, but we don’t want to write this to the binary log, as it would get replicated to the other servers and cause an error.

On Server #1 (IP 192.168.1.151)

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.04 sec)

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

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

Since we are only running single-primary mode, we don’t need to do a CHANGE MASTER on Server #1. In replication, the slave is responsible for connecting to the master to get the write statements. Since the secondary nodes won’t be accepting any writes, then the primary won’t need to connect to them. But, since the primary could fail, and be brought back into the group as a read-only slave, we need to go ahead and run the following CHANGE MASTER statement.

The CHANGE MASTER statement will produce two warnings – we can look at the warnings and ignore them.

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now we will need to turn group_replication_bootstrap_group to ON, and then we can start Group Replication (but do this only for on Server #1). We will want to turn group_replication_bootstrap_group to OFF after we have started Group Replication for the first time.

# commands to run from MySQL prompt

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
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 (3.10 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

Check the status of the MASTER server:

# commands to run from MySQL prompt

SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 458
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1
1 row in set (0.00 sec)

There has only been one GTID executed on the MASTER – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1. Check the binlog to see what has been written to it.

# commands to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001';
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |       151 |         124 | Server ver: 8.0.11, Binlog ver: 4                                 |
| mysql-bin.000001 | 124 | Previous_gtids |       151 |         151 |                                                                   |
| mysql-bin.000001 | 151 | Gtid           |       151 |         229 | SET @@SESSION.GTID_NEXT= '8e2f4761-c55c-422f-8684-d086f6a1db0e:1' |
| mysql-bin.000001 | 229 | Query          |       151 |         291 | BEGIN                                                             |
| mysql-bin.000001 | 291 | View_change    |       151 |         390 | view_id=15247058086370221:1                                       |
| mysql-bin.000001 | 390 | Query          |       151 |         458 | COMMIT                                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

We still only have one member of the Group Replication, and we can see which server is a member:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
1 row in set (0.00 sec)

You can see one member of the group (Server #1) is online. Group Replication has been started on Server #1.

Now we can add Server #2. We will do the same steps as we did for Server #1, except we don’t need to bootstrap Group Replication, as it has already been started. Oon Server #2 (IP 192.168.1.152):

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.03 sec)

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

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

# commands to run from MySQL prompt

SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Run the CHANGE MASTER statement:

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The CHANGE MASTER statement produced two warnings – we can look at the warnings and ignore them.
# commands to run from MySQL prompt

SHOW WARNINGS\G
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

Start Group Replication on Server #2:

# commands to run from MySQL prompt

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

NOTE: If you put in the wrong password in your CHANGE MASTER statement, the server will be in the group, but will be in a MEMBER_STATE of RECOVERING and it will never join the group.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | RECOVERING   | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Check to see that Server #2 has joined the group. You can execute this command on Server #1 or #2. (This was executed from Server #1)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Or you can run it with the \G at the end.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Running the same commands from Server #2 gets the same results, as you can query the status of the Group from any member of the group.

This was executed from Server #2

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

And the option with \G at the end:
# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Group replication now has Server #1 and Server #2. Now we can add Server #3 (IP 192.168.1.153)

We run the same commands as above. Note:I only show the commands to run – I do not show the screen output here.

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;

If you didn’t see any errors, Group Replication is ready to go. As before, you can check the status of the group from any server.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: bedc9968-48ee-11e8-9735-0a5899f91373
   MEMBER_HOST: MacVM153.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
3 rows in set (0.00 sec)

Or you can run:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

NOTICE: Be sure to change group_replication_start_on_boot to ON in your my.ini or my.cnf configuration file for all of the servers as shown below.

(in the my.cnf or my.ini file)
group_replication_start_on_boot = ON

If the MEMBER_STATE of three servers is ONLINE, then you are finished and Group Replication has been installed!


Creating an InnoDB Cluster

If you have an existing deployment of Group Replication and you want to use it to create a cluster, pass the adoptFromGR option to the dba.createCluster() function. The created InnoDB cluster matches whether the replication group is running as single-primary or multi-primary. See: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.

Adding a replicated MySQL database instance using a Group Replication server as the source

You say you want a Replication?

One of the best features of MySQL is the ability to use MySQL‘s built-in database replication feature to automatically replicate data from one server (source/master) to another (slave/replica). Group Replication was added in MySQL 5.7 as a way to provide a high-availability solution using a new variation of MySQL replication.

(In some earlier posts, I explained how to setup Group Replication using three MySQL database servers and how to create a sandbox for testing MySQL 8.0 InnoDB Cluster.)

The “regular version” of MySQL replication is still very powerful and relatively easy to setup. Some advantages of replication in MySQL include:

  • Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
  • Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
  • Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
  • Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.
  • Source: https://dev.mysql.com/doc/refman/8.0/en/replication.html

    Even if you have Group Replication installed, you still might want to replicate that data to a separate server for various reasons. This post will explain a little about MySQL replication, and show you how to connect a replica (slave) server to a single server in a Group Replication group.

    What is replication and how does it work?

    To use MySQL replication, you need to understand something called the binary log (also called the binlog).

    The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched zero rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

    • For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
    • Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. <a target=new href=https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.htmlSee Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
    • Source: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

    The replication process is simple. The replica (slave) database connects to the source (master) database via the I/O thread and retrieves the events from the binary log which have occurred since the last time it connected to the source (master) database. The source (master) database performs a binlog dump (which contains the new events), and the replica’s I/O thread transfers this dump to the replica (slave) server, and then stores these events on the replica’s MySQL instance in a “binary-log-type” file known as the relay log. Another thread (the SQL thread) then reads the events from the relay log and applies these changes to the database. This type of replication is asynchronous replication, because it is a one-way transfer of data (See the image below). There is another replication option called semi-synchronous, which ensures that the transaction on the source (master) database is written to the relay log of the replica (slave) before the transaction is committed.

    For more information about the threads, see: https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html

    The replica (slave) database has two options for retrieving the transactions from the source (master) database. The first is an older method, which requires the replica (slave) server to keep track of the last binary log accessed on the master for retrieving the transactions. The replica (slave) also has to keep track of the last position within the last binary log it used. For example, the last binary log was mysql-bin.000343 and the last position in that log was 49583. So, the next time the replica (slave) connects to the source (master) database, it asks for all of the transactions which occurred after this position (49583) in this binary log (mysql-bin.000343).

    Global Transaction Identifiers

    The second method involves using global transaction identifiers (GTID), which was introduced in MySQL version 5.6. A GTID is a unique identifier created and associated with each transaction committed on the server of origin (the source/master database). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology. Source: https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html

    The GTID is comprised of a server’s UUID (a 36-character unique server identifier), a colon, and an incremental number. A GTID for a single transaction would look something like this: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 (as in: UUID:transaction number).

    With GTID replication, the replica (slave) doesn’t need to keep track of the last binary log being used, nor does it need to remember the position with that binary log. Since the GTID’s contain incremental numbers, the replica (slave) only has to remember the last GTID it processed. For example, the source (master) server may have processed a group of GTID’s such as 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-30493. If the last GTID the replica (slave) retrieved was 3E11FA47-71CA-11E1-9E33-C80AA9429562:30201, then the replica (slave) will tell the source (master) that it needs every transaction (GTID) after 3E11FA47-71CA-11E1-9E33-C80AA9429562:30201.

    Also, using GTID’s makes it much easier to see how many transactions have been executed on the source (master) database, and how many transactions have been retrieved and applied by the replica (slave) database. On the replica (slave) database, after replication has been started, the “SHOW SLAVE STATUS” command will display something like this:


    mysql> SHOW SLAVE STATUS\G

    Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-35683
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-34652

    This tells the database administrator (DBA) that the replica (slave) server has retrieved 35683 transactions from the source (master) database, but has only applied (executed) 34652 transactions.

    GTID’s also make it easier to skip bad transactions on the slave. Let’s say that the last transaction, 34652, was “bad”. The DBA would need to view that transaction by looking at it from the relay log using the mysqlbinlog tool (example: mysqlbinlog –include-gtids=8e2f4761-c55c-422f-8684-d086f6a1db0e:34652). And, let’s assume it was safe to delete this transaction, then the DBA would issue this command, effectively skipping this transaction and setting the next GTID to be used as 8e2f4761-c55c-422f-8684-d086f6a1db0e:34653:


    STOP SLAVE;
    SET GTID_NEXT=”8e2f4761-c55c-422f-8684-d086f6a1db0e:34653″;
    BEGIN; COMMIT;
    SET GTID_NEXT=”AUTOMATIC”;
    START SLAVE;

    Group Replication

    Group Replication (GR) was introduced in MySQL version 5.7, and GR allows you to have a minimum of three servers in a high-availability group (with a maximum of nine servers). Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently. Source: https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

    Group Replication requires the use of Global Transaction Identifiers (GTID’s). With “regular” single-source replication (master/slave), you can also use GTID’s – or the older method of specifying the binary log and position in the binary log. However, for replicating from a Group Replication group member, you must use GTID’s.

    Replicating from a member in Group Replication

    To replicate the data from a member of a Group Replication group, you simply need to point the replica (slave) database to one of the nodes in the group. This is the same method you would use if you were simply replicating off a single server. However, if the Group Replication server you are using for the source (master) goes down, then you will need to point the replica (slave) database to another member of the group. Since Group Replication uses the same UUID in the GTIDs, you can simply point the replica (slave) to any of the other servers in the group and replication will continue without any problems.

    Configuring the replica (slave) server

    You will need to add the following to your MySQL configuration file (my.cnf or my.ini) under the [mysqld] section, to enable replication using GTID’s:


    gtid-mode = on
    enforce-gtid-consistency = 1
    master-info-repository=TABLE
    relay-log-info-repository=TABLE

    binlog_format=row
    server-id = 3456 # this must be a unique number for each server
    log-slave-updates
    relay-log-recovery

    For this example, I will be using a newly-created (without any data) Group Replication group, and I will be adding a new replica (slave) server to it.

    NOTE: If you have a Group Replication group with data on it, then you will need to import a copy of your current data over to the new replica (slave) server before starting replication. For more information – see “What if my Group Replication (GR) group already has data?”

    After you have added the variables above to the MySQL server and rebooted the instance, you are now ready to make the server a replica (slave) of one of the servers in the Group Replication group. But first, we need to add a replication user to the Group Replication group.

    Configuring the source (master) server

    The new replica (slave) server will be replicating off a new three-node Group Replication (GR) group. I have already started Group Replication, and the GR is in single-primary mode.

    Before I turn on replication to my new replica (slave) server, I can use the SHOW MASTER STATUS command to look at the primary server to see if any transactions have been executed yet (under the Executed_Gtid_Set heading):


    mysql> SHOW MASTER STATUS\G
    *************************** 1. row ***************************
    File: mysql-bin.000001
    Position: 1164
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
    1 row in set (0.00 sec)

    The Executed_Gtid_Set shows three transactions. When I look at these transactions using mysqlbinlog, I can see all three transactions are a SET TIMESTAMP command:

    # mysqlbinlog mysql-bin.000001
    ...
    SET TIMESTAMP=1553124003/*!*/;
    COMMIT
    ...
    

    When you start Group Replication and a single member to the group, a view of the group is created, and this SET TIMESTAMP is executed. Since I have three members in this GR group, I will have three SET TIMESTAMP transactions. These transactions will be sent over to the new slave, but the transactions are harmless. I could tell the replica (slave) to ignore the transactions, but for this example, I will use them to see if replication is working.

    When I created the Group Replication group, I already created a replication user named rpl_user on all three servers in the group. I can use this same user for my new single replica (slave). You don’t need to create this user on the replica (slave) server. However, I created the user with a domain wildcard (%) – so you might want to restrict user access via a domain or IP address.

    Starting the replica (slave) server

    To start replication, you need to tell the replica (slave) server which server you will use to retrieve data. You do this with the CHANGE MASTER command:

    mysql> CHANGE MASTER TO 
        -> MASTER_HOST = '192.168.1.152',
        -> MASTER_PORT = 3306,
        -> MASTER_USER = 'rpl_user',
        -> MASTER_PASSWORD = 'R3plic4tion!',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.04 sec)
    

    You are now ready to start the replica (slave) server with the START SLAVE command:

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

    Next, we need to see if replication has started. You can check this with the SHOW SLAVE STATUS command:

    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.152
                      Master_User: rpl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 1164
                   Relay_Log_File: Rep01-relay-bin.000002
                    Relay_Log_Pos: 1370
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 1164
                  Relay_Log_Space: 1570
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 152
                      Master_UUID: 247898e0-4cb7-11e9-97a9-12f28adcadd1
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    

    As you can see above under the Retrieved_Gtid_Set – the replica (slave) has already retrieved and processed the three transactions which were on the Group Replication group.

               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
    

    You can also see that the I/O and SQL threads are running:

                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    

    Let’s test replication by adding a database on one of the Group Replication nodes, and then checking to see if it replicates to the replica (slave).

    I am going to create the new database on the primary write server – which has an IP address of 192.168.1.151. We pointed the replica (slave) to 192.168.1.152, so the new database will get replicated to 192.168.1.152, and then over to our replica (slave) server (which has an IP address of 192.168.1.220).

    On 192.168.1.151:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 1164
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
    1 row in set (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database rep_test;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    

    And we can see the new database has been replicated to the MySQL server on 192.168.1.152:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    

    And it is now also on the new replica (slave) server:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | sys                |
    +--------------------+
    5 rows in set (0.02 sec)
    

    That’s all you need to do. Replication is now up and running!

     

    What if my Group Replication (GR) group already has data?

    If you have an existing GR group with data, you need the new replica (slave) to have a copy of the data that is already in the group – to give the new replica (slave) a starting point for replication. The binary logs will probably not have all of the transactions that have been applied to the database – and even if it did, it would take an extremely long time to replicate those events. It is much easier to restore a backup to the new replica (slave) server.

    Once you load the new replica (slave) with the data from a backup, you can then start replication from the last GTID that was executed on the source (master) before you performed the backup. To get a copy of the data to move over to the new replica (slave), you can use the mysqldump utility – or if you are an Enterprise Edition subscriber, you can use the mysqlbackup utility (which is much faster than mysqldump). Using mysqldump will take a considerable amount of time if you have a lot of data (Example: mysqldump will take about an hour to dump 15-20 gigabytes and 4-5 hours to restore 15-20 gigabytes – while mysqlbackup would take less than 10 minutes for both processes. Your actual results may vary.)

    After you restore the backup to the replica (slave) server, you can then start replication. Prior to the backup, I executed a few transactions on the Group Replication servers to increase the GTID numbers. I used this mysqlbackup command to backup one of the read-only servers in the Group Replication group:

    /usr/local/meb/bin/mysqlbackup --user=mysqlbackup --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log --with-timestamp --encrypt-password
    

    Also look at the screen output from the backup, and you will see that it contains the GTID information of the source (master) database:

    ...
    190322 21:07:48 MAIN    INFO: GTID_EXECUTED is 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
    ...
    

    This number also matches the SHOW MASTER STATUS\G output from one of the Group Replication servers:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 13044
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
    1 row in set (0.00 sec)
    

    I can now restore this backup to the new replica (slave) server using the mysqlbackup utility. First, I will shut down the MySQL instance, copy the backup to this server, and then I can run the copy-back command:

    # /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/Desktop/2019-03-22_21-07-46 copy-back
    MySQL Enterprise Backup  Ver 8.0.15-commercial for macos10.14 on x86_64 (MySQL Enterprise - Commercial)
    Copyright (c) 2003, 2019, Oracle and/or its affiliates. All rights reserved.
    ...
    mysqlbackup completed OK! with 7 warnings
    

    Here are a few things to consider when restoring data:

    • If you don’t delete the old data directory, you will need to use the force variable at the end of the command to overwrite the existing MySQL data directory.
    • The MySQL config files (my.cnf or my.ini) are not restored. If you don’t have the same settings for variables like innodb_data_file_path, you will get an error when you try and restart MySQL after the restore.
    • Be sure to change the owner of your MySQL directories. If you run mysqlbackup as root, the directories will be owned by root and you won’t be able to start MySQL.
      # cd /usr/local/mysql
      # ls -l
      total 1224
      ...
      drwxr-x---  26 root    wheel      884 Mar 22 21:30 data
      ...
      # chown -R mysql data
      

    The backup from the source (master) server has been restored to the replica (slave), and I have restarted the MySQL database instance. Now I can reset the slave, use the CHANGE MASTER TO command, start the slave, and I will have the same starting point as the source (master) database.

    mysql> reset slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> CHANGE MASTER TO 
        -> MASTER_HOST = '192.168.1.152',
        -> MASTER_PORT = 3306,
        -> MASTER_USER = 'rpl_user',
        -> MASTER_PASSWORD = 'R3plic4tion!',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW SLAVE STATUS\G
    ... 
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    ...
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    ... 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
    ...
    mysql> 
    

    If you look at the Executed_Gtid_Set, you can see the replica (slave) database has 45695 transactions from the source (master) database. But, since we haven’t started replication yet, and the replica (slave) database has not connected to the source (master) database, the Retrieved_Gtid_Set is blank. Since we restored the first 45695 transactions, those will already be included in the Executed_Gtid_Set.

    After more transactions are executed on the source (master) database, when you execute the SHOW SLAVE STATUS command later, you will see something like the following – where the Retrieved_Gtid_Set doesn’t begin with the number one, but instead it begins with the next number (45696) after the original set of GTID’s (45695) that were restored from the backup. Since the replica (slave) didn’t actually retrieve the first 45695 transactions, those won’t appear in the Retrieved_Gtid_Set, but you can see all of the GTID’s are in the Executed_Gtid_Set.

     
    mysql> SHOW SLAVE STATUS\G
    ...
               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:45696-45753
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45753
    ...
    

    And we can compare this list to the source (master) database:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 13044
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45753
    1 row in set (0.00 sec)
    

    Now we know the replica (slave) database is caught up to the source (master) database.

    Restoring a slave using mysqldump

    Using mysqldump to backup your source (master) database and restore it to a replica (slave) database isn’t as easy as using mysqlbackup. With mysqldump, you can’t backup and restore the mysql.user table, which contains all of your user information. So, you can’t backup the mysql database and restore it. Since I only have one database (rep_test) on my Group Replication source (master) database, I can use the following command to create a mysqldump backup file.

    /usr/local/mysql/bin/mysqldump --databases rep_test --set-gtid-purged=ON --master-data --add-drop-database --add-drop-table --triggers --user=root -p > /users/tonydarnell/Desktop/2019_03_22_2300_dbdump.txt
    

    This creates a backup file which also includes the GTID information from the source (master) server. If you had more databases, you would need to list them after the databases variable. I have a new install of MySQL version 8.0.15 that I will be using to restore this data, and after copying the backup file to the new replica (slave) server, the restore command is very simple:

    # mysql -uroot -p < 2019_03_22_2300_dbdump.txt
    Enter password: 
    

    I can now take a look at the transactions which have been applied to the server by using the SHOW MASTER STATUS\G command:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 155
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-26
    1 row in set (0.00 sec)
    

    And – this Executed_Gtid_Set matches the source (master) database (this was before I added thousands of records as shown in the mysqlbackup example). But – I don’t have any of the users that were created on the master, because I can’t export and import the mysql database. It is easier and probably more secure if you just add whatever users you need on this new replica (slave) database.

    If you don’t have access to the users you need, there is a way to get the information from the mysql.user table.

    mysql> select user, host from mysql.user;
    +------------------+----------------+
    | user             | host           |
    +------------------+----------------+
    | mysqlbackup      | %              |
    | root             | %              |
    | rpl_user         | %              |
    | mysql.infoschema | localhost      |
    | mysql.session    | localhost      |
    | mysql.sys        | localhost      |
    | root             | localhost      |
    | root             | macvm151.local |
    +------------------+----------------+
    8 rows in set (0.00 sec)
    

    In this example, I am only going to re-create the user named mysqlbackup. I could use the SHOW CREATE USER command, but it doesn’t give me the exact format I need to re-create the user:

    mysql> show create user mysqlbackup\G
    *************************** 1. row ***************************
    CREATE USER for mysqlbackup@%: CREATE USER 'mysqlbackup'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$:\n*~t^;+s/,+g1L6EDOPfmWHQxa/z7C.mHStg15xDyMq7UzHsi2hTKM10' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
    1 row in set (0.00 sec)
    

    Instead of trying to extract what I need from the mysql table, I have found it is easier to just re-create the user, and then re-apply the grants. BUT – I don’t really want this to be stored in the binary log, so I can prevent it from writing to the binary log with the SET SQL_LOG_BIN=0 command:

    SET SQL_LOG_BIN=0;
    CREATE USER 'mysqlbackup'@'%' IDENTIFIED WITH sha256_password BY 'new-password';
    FLUSH PRIVILEGES;
    SET SQL_LOG_BIN=1;
    

    (I turned on writing to the binary log with the SET SQL_LOG_BIN=1 command.

    As for the grants, I have found it is easier to just grab the grants from the mysql.tables_priv table:

    mysql> show grants for mysqlbackup;
    +----------------------------------------------------------------------------------------------------+
    | Grants for mysqlbackup@%                                                                           |
    +----------------------------------------------------------------------------------------------------+
    | GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `mysqlbackup`@`%`                       |
    | GRANT INSERT, CREATE, DROP, ALTER ON `mysql`.`backup_history_new` TO `mysqlbackup`@`%`             |
    | GRANT INSERT, CREATE, DROP ON `mysql`.`backup_history_old` TO `mysqlbackup`@`%`                    |
    | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `mysql`.`backup_history` TO `mysqlbackup`@`%` |
    | GRANT INSERT, UPDATE, CREATE, DROP ON `mysql`.`backup_progress` TO `mysqlbackup`@`%`               |
    | GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlbackup`@`%`              |
    +----------------------------------------------------------------------------------------------------+
    6 rows in set (0.00 sec)
    

    And, with a little editing, I can extract the GRANT commands and run these as well – and again, I am going to suppress these from writing to the binary logs.

    SET SQL_LOG_BIN=0;
    GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `mysqlbackup`@`%`;
    GRANT INSERT, CREATE, DROP, ALTER ON `mysql`.`backup_history_new` TO `mysqlbackup`@`%`;
    GRANT INSERT, CREATE, DROP ON `mysql`.`backup_history_old` TO `mysqlbackup`@`%`;
    GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `mysql`.`backup_history` TO `mysqlbackup`@`%`;
    GRANT INSERT, UPDATE, CREATE, DROP ON `mysql`.`backup_progress` TO `mysqlbackup`@`%`;
    GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlbackup`@`%`;
    FLUSH PRIVILEGES;
    SET SQL_LOG_BIN=1;
    

    I now have all the users I need, and I can start replication:

    mysql> select user, host from mysql.user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | mysqlbackup      | %         |
    | mysql.infoschema | localhost |
    | mysql.session    | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
    +------------------+-----------+
    5 rows in set (0.00 sec)
    
    mysql> CHANGE MASTER TO 
        -> MASTER_HOST = '192.168.1.152',
        -> MASTER_PORT = 3306,
        -> MASTER_USER = 'rpl_user',
        -> MASTER_PASSWORD = 'R3plic4tion!',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.152
                      Master_User: rpl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000002
              Read_Master_Log_Pos: 9463
                   Relay_Log_File: Rep01-relay-bin.000002
                    Relay_Log_Pos: 408
            Relay_Master_Log_File: mysql-bin.000002
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 9463
                  Relay_Log_Space: 616
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 152
                      Master_UUID: 247898e0-4cb7-11e9-97a9-12f28adcadd1
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-26
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    

    I am going to test replication by adding another database on the source (master):

    mysql> create database rpl_test2;
    Query OK, 1 row affected (0.01 sec)
    

    I can now see the new database on the replica (slave) server:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | rpl_test2          |
    | sys                |
    +--------------------+
    6 rows in set (0.00 sec)
    

    And when I check the GTID’s, I can see that GTID 27 (the transaction which created the database rpl_test2) has been retrieved and applied:

    mysql> show slave status\G
    ...
               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:27
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-27
    ...
    

    Since I imported the first 26 GTID’s, those will not appear in the Retrieved_Gtid_Set. But, the Executed_Gtid_Set shows that the new replica (slave) database has all of the same transactions as the source (master) database.


    Note: For more information on mysqldump replication options, see: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-replication-options


    Other posts on restoring backups to servers

    I have two older posts which explain how to do this. Some of the MySQL variables might have changed, but these posts will explain the process behind using both utilities.

    Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2

    Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2

     


    Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
    Tony is the author of Twenty Forty-Four: The League of Patriots 
    Visit http://2044thebook.com for more information.
    Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
    Visit https://amzn.to/2oPFLI0 for more information.