Peter Zaitsev: How to Quickly Add a Node to an InnoDB Cluster or Group Replicati ...

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 thePercona XtraBackup tool to be installed, as well as some additional small tools for convenience. I tested my example on Centos 7, but it works similarlyon other linux distributions. First of all, you will need the Percona repository installed:

# yum install -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 forXtraBackup 8.0that works with mysql 8.0. ( Note: XtraBackup 8.0 is still not GA when writing this article, andwe 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 =$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 base205 k percona-xtrabackup-80 x86_64 8.0.1-2.alpha2.el7percona-experimental-x86_6413 M pigzx86_64 2.3.4-1.el7 epel 81 k pvx86_64 1.4.6-1.el7 epel 47 k Installing for dependencies: perl-DBD-MySQLx86_64 4.023-6.el7 base140 k Transaction Summary ============================================================================================================================================== Install4 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_members\G *************************** 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 ingroup_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 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-aaaaa

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

本文标题:Peter Zaitsev: How to Quickly Add a Node to an InnoDB Cluster or Group Replicati ...

技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(126)