Quickly Add a Node in InnoDB Cluster or Group Replication

Quickly Add a Node to an InnoDB Cluster or Group Replication

Quickly Add a Node to an InnoDB Cluster or Group Replication (Shutterstock)

In this blog, we’ll look at how to quickly add a node to an InnoDB Cluster or Group Replication using Percona XtraBackup.

Adding nodes to a Group Replication cluster can be easy (documented here), but it only works if the existing nodes have retained all the binary logs since the creation of the cluster. Obviously, this is possible if you create a new cluster from scratch. The nodes rotate old logs after some time, however. Technically, if the

gtid_purged

 set is non-empty, it means you will need another method to add a new node to a cluster. You also need a different method if data becomes inconsistent across cluster nodes for any reason. For example, you might hit something similar to this bug, or fall prey to human error.

Hot Backup to the Rescue

The quick and simple method I’ll present here requires the Percona XtraBackup tool to be installed, as well as some additional small tools for convenience. I tested my example on Centos 7, but it works similarly on other Linux distributions. First of all, you will need the Percona repository installed:

# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm -y -q

Then, install Percona XtraBackup and the additional tools. You might need to enable the EPEL repo for the additional tools and the experimental Percona repo for XtraBackup 8.0 that works with MySQL 8.0. (Note: XtraBackup 8.0 is still not GA when writing this article, and we do NOT recommend or advise that you install XtraBackup 8.0 into a production environment until it is GA). For MySQL 5.7, Xtrabackup 2.4 from the regular repo is what you are looking for:

# grep -A3 percona-experimental-$basearch /etc/yum.repos.d/percona-release.repo 
[percona-experimental-$basearch] 
name = Percona-Experimental YUM repository - $basearch 
baseurl = http://repo.percona.com/experimental/$releasever/RPMS/$basearch 
enabled = 1

# yum install pv pigz nmap-ncat percona-xtrabackup-80 -q 
============================================================================================================================================== 
 Package                             Arch                 Version                             Repository                                 Size 
============================================================================================================================================== 
Installing: 
 nmap-ncat                           x86_64               2:6.40-13.el7                       base                                      205 k 
 percona-xtrabackup-80               x86_64               8.0.1-2.alpha2.el7                  percona-experimental-x86_64                13 M 
 pigz                                x86_64               2.3.4-1.el7                         epel                                       81 k 
 pv                                  x86_64               1.4.6-1.el7                         epel                                       47 k 
Installing for dependencies: 
 perl-DBD-MySQL                      x86_64               4.023-6.el7                         base                                      140 k 
Transaction Summary 
============================================================================================================================================== 
Install  4 Packages (+1 Dependent package) 
Is this ok [y/d/N]: y 
#

You need to do it on both the source and destination nodes. Now, my existing cluster node (I will call it a donor) – gr01 looks like this:

gr01 > select * from performance_schema.replication_group_membersG 
*************************** 1. row *************************** 
  CHANNEL_NAME: group_replication_applier 
     MEMBER_ID: 76df8268-c95e-11e8-b55d-525400cae48b 
   MEMBER_HOST: gr01 
   MEMBER_PORT: 3306 
  MEMBER_STATE: ONLINE 
   MEMBER_ROLE: PRIMARY 
MEMBER_VERSION: 8.0.13 
1 row in set (0.00 sec) 
gr01 > show global variables like 'gtid%'; 
+----------------------------------+-----------------------------------------------+ 
| Variable_name                    | Value                                         | 
+----------------------------------+-----------------------------------------------+ 
| gtid_executed                    | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302662 | 
| gtid_executed_compression_period | 1000                                          | 
| gtid_mode                        | ON                                            | 
| gtid_owned                       |                                               | 
| gtid_purged                      | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-295538 | 
+----------------------------------+-----------------------------------------------+ 
5 rows in set (0.01 sec)

The new node candidate (I will call it a joiner) – gr02, has no data but the same MySQL version installed. It also has the required settings in place, like the existing node address in group_replication_group_seeds, etc. The next step is to stop the MySQL service on the joiner (if already running), and wipe out it’s datadir:

[root@gr02 ~]# rm -fr /var/lib/mysql/*

and start the “listener” process, that waits to receive the data snapshot (remember to open the TCP port if you have a firewall):

[root@gr02 ~]# nc -l -p 4444 |pv| unpigz -c | xbstream -x -C /var/lib/mysql

Then, start the backup job on the donor:

[root@gr01 ~]# xtrabackup --user=root --password=*** --backup --parallel=4 --stream=xbstream --target-dir=./ 2> backup.log |pv|pigz -c --fast| nc -w 2 192.168.56.98 4444 
240MiB 0:00:02 [81.4MiB/s] [ <=>

On the joiner side, we will see:

[root@gr02 ~]# nc -l -p 4444 |pv| unpigz -c | xbstream -x -C /var/lib/mysql 
21.2MiB 0:03:30 [ 103kiB/s] [ <=> ] 
[root@gr02 ~]# du -hs /var/lib/mysql 
241M /var/lib/mysql

BTW, if you noticed the difference in transfer rate between the two, please note that on the donor side I put

|pv|

 before the compressor while in the joiner before decompressor. This way, I can monitor the compression ratio at the same time!

The next step will be to prepare the backup on joiner:

[root@gr02 ~]# xtrabackup --use-memory=1G --prepare --target-dir=/var/lib/mysql 2>prepare.log 
[root@gr02 ~]# tail -1 prepare.log 
181019 19:18:56 completed OK!

and fix the files ownership:

[root@gr02 ~]# chown -R mysql:mysql /var/lib/mysql

Now we should verify the GTID position information and restart the joiner (I have the

group_replication_start_on_boot=off

 in my.cnf):

[root@gr02 ~]# cat /var/lib/mysql/xtrabackup_binlog_info 
binlog.000023 893 aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302662 
[root@gr02 ~]# systemctl restart mysqld

Now, let’s check if the position reported by the node is consistent with the above:

gr02 > select @@GLOBAL.gtid_executed; 
+-----------------------------------------------+ 
| @@GLOBAL.gtid_executed                        | 
+-----------------------------------------------+ 
| aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302660 | 
+-----------------------------------------------+ 
1 row in set (0.00 sec)

No, it is not. We have to correct it:

gr02 > reset master; set global gtid_purged="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302662"; 
Query OK, 0 rows affected (0.05 sec) 
Query OK, 0 rows affected (0.00 sec)

Finally, start the replication:

gr02 > START GROUP_REPLICATION; 
Query OK, 0 rows affected (3.91 sec)

Let’s check the cluster status again:

gr01 > select * from performance_schema.replication_group_membersG 
*************************** 1. row *************************** 
CHANNEL_NAME: group_replication_applier 
MEMBER_ID: 76df8268-c95e-11e8-b55d-525400cae48b 
MEMBER_HOST: gr01 
MEMBER_PORT: 3306 
MEMBER_STATE: ONLINE 
MEMBER_ROLE: PRIMARY 
MEMBER_VERSION: 8.0.13 
*************************** 2. row *************************** 
CHANNEL_NAME: group_replication_applier 
MEMBER_ID: a60a4124-d3d4-11e8-8ef2-525400cae48b 
MEMBER_HOST: gr02 
MEMBER_PORT: 3306 
MEMBER_STATE: ONLINE 
MEMBER_ROLE: SECONDARY 
MEMBER_VERSION: 8.0.13 
2 rows in set (0.00 sec) 
gr01 > select * from performance_schema.replication_group_member_statsG 
*************************** 1. row *************************** 
                              CHANNEL_NAME: group_replication_applier 
                                   VIEW_ID: 15399708149765074:4 
                                 MEMBER_ID: 76df8268-c95e-11e8-b55d-525400cae48b 
               COUNT_TRANSACTIONS_IN_QUEUE: 0 
                COUNT_TRANSACTIONS_CHECKED: 3 
                  COUNT_CONFLICTS_DETECTED: 0 
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302666 
            LAST_CONFLICT_FREE_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:302665 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 2 
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 3 
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 
*************************** 2. row *************************** 
                              CHANNEL_NAME: group_replication_applier 
                                   VIEW_ID: 15399708149765074:4 
                                 MEMBER_ID: a60a4124-d3d4-11e8-8ef2-525400cae48b 
               COUNT_TRANSACTIONS_IN_QUEUE: 0 
                COUNT_TRANSACTIONS_CHECKED: 0 
                  COUNT_CONFLICTS_DETECTED: 0 
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-302666 
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 0 
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 
2 rows in set (0.00 sec)

OK, our cluster is consistent! The new node joined successfully as secondary. We can proceed to add more nodes!

Leave a Reply

Your email address will not be published. Required fields are marked *