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:
1 | # 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:
1 2 3 4 5 | # 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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:
1 | [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):
1 | [root@gr02 ~]# nc -l -p 4444 |pv| unpigz -c | xbstream -x -C /var/lib/mysql |
Then, start the backup job on the donor:
1 2 | [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:
1 2 3 4 5 | [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:
1 2 3 | [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:
1 | [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):
1 2 3 4 | [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:
1 2 3 4 5 6 7 | 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:
1 2 3 4 | 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:
1 2 | gr02 > START GROUP_REPLICATION; Query OK, 0 rows affected (3.91 sec) |
Let’s check the cluster status again:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | 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!
It seems that, to simplify this article, no writes are arriving on gr01.
Before you start Percona XtraBackup, we see that gr01 has:
gtid_executed = 1-302662
Next you take a backup and prepare it on gr02, which then has:
xtrabackup_binlog_info = 1-302662
My question is, why does gr02 have a smaller @@GLOBAL.gtid_executed, requiring
set global gtid_purged?I thought maybe that these transactions were FLUSH or similar, but remember that these are before PXB started on gr01.
I followed the above for MySQL 5.7.25 to recover a corrupted node. If you have the same situation, then I would add one additional step.
When I had the recovered node started again, it had a new server UUID. That meant a mismatch occurred between it and the cluster’s current metadata. To overcome this, I copied the “old” auto.cnf file from the previous server, then copied it back into the MySQL data directory prior to restarting the recovered node.
In the case of a total loss of the server, you could follow the procedure as written and then remove the old instance.
Thank you for a truly excellent article. It worked very well.
Great article, thanks! Just to let you know there is one more caveat: if you execute these steps to “clone” a node (e.g. in a group replication setup, like I did), it will complain when restarting replication with ERROR 3094, logging “Slave SQL for channel ‘group_replication_applier’: Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124” to the mysql log.
This is caused by incorrect filenames in the mysql.slave_relay_log_info table which still reference the filenames from the original node, while xtrabackup already took care of the actual files and renamed them correctly. For me the simple solution was to “RESET SLAVE” on the clone, truncating the above table might work as well.