Adding a New Node to MySQL Group Replication from a Backup: A Step-by-Step Guide

Jun 30, 2023

Mins to Read

All

Learn how to seamlessly add a new node to MySQL Group Replication from a backup. Scale your cluster, save time, and efficiently manage data updates and recoveries.

We highly recommend checking out our previous blog post on MySQL 8.0 Clone Plugin and its internal process for some valuable background knowledge.

By creating a new node from a backup, you can enhance the functionality and capabilities of your database while efficiently managing data updates and recoveries. Whether you are using MySQL 5.7 or later versions, we have the appropriate methods to add new instances to the cluster: xtrabackup and clone plugin for different MySQL versions and logical approach for Windows Server.

In this blog, we will walk you through each method, providing step-by-step instructions on adding a new node to MySQL Group Replication from a backup. Whether you prefer the hot physical backup, clone plugin, or logical backup approach, you will gain valuable insights to efficiently scale your Group Replication cluster and improve the performance of your database.

Note: Make sure to install MySQL in the new instance and deploy the configuration file to be configured for group replication.

Hot Physical backup approach

In this method, you take a full backup of an existing node and stream the backup data to the new node using a backup tool. The new node can then be configured to join the cluster and start the replication process. This approach offers faster setup and potentially reduces downtime.

Here are the steps involved:

Shut down the MySQL server on the new server (Node 3).

 
root@node3:/home/ubuntu# service mysql status
mysql.service - MySQL Community Server
    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Active: inactive (dead) since Tue 2023-05-11 08:08:45 UTC; 26s ago
   Process: 470 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Process: 619 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
  Main PID: 619 (code=exited, status=0/SUCCESS)
    Status: "Server shutdown complete"


May 11 08:01:56 node3 systemd[1]: Starting MySQL Community Server...
May 11 08:02:06 node3 systemd[1]: Started MySQL Community Server.
May 11 08:08:42 node3 systemd[1]: Stopping MySQL Community Server...
May 11 08:08:45 node3 systemd[1]: mysql.service: Succeeded.
May 11 08:08:45 node3 systemd[1]: Stopped MySQL Community Server.
root@node3:/home/ubuntu#

To ensure a clean state before streaming data to the data directory using xtrabackup, perform a data directory cleanup.

 
root@node3:/home/ubuntu# cd /var/lib/mysql
root@node3:/var/lib/mysql# ls -ltrh
total 0
root@node3:/var/lib/mysql#

Validate the group replication status on the source node (Node 2) to ensure the server is live. It’s not mandatory to use only the live backup. We also have the option to utilize a scheduled backup for this process.

 
mydbops@localhost:(none)>select member_host,member_state,member_role,member_version from performance_schema.replication_group_members;

+---------------+--------------+-------------+----------------+

| member_host   | member_state | member_role | member_version |

+---------------+--------------+-------------+----------------+

| node2         | ONLINE       | SECONDARY   | 8.0.32         |

| node1         | ONLINE       | PRIMARY     | 8.0.32         |

+---------------+--------------+-------------+----------------+

2 rows in set (0.00 sec)

After the streaming process to the new server (Node 3) is finished, initiate the new server using the latest data.

 
root@node3:/var/lib/mysql# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mydbops@localhost:(none)>select member_state from performance_schema.replication_group_members;

+--------------+

| member_state |

+--------------+

| OFFLINE      |

+-------------+

1 row in set (0.01 sec)

mydbops@localhost:(none)>

In order to incorporate the new server (Node 3) into the group replication, it is necessary to configure the GTID (Global Transaction Identifier) in the correct order.

 
root@node3:/var/lib/mysql# cat xtrabackup_binlog_info
binlog.000010  197  9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,d821169a-a0c4-11ed-8f6b-12e73c711187:1-31
root@node3:/var/lib/mysql#


mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------------------------------------------------------------------------------------+
| Variable_name | Value                                                                               |
+---------------+-------------------------------------------------------------------------------------+
| gtid_purged   | 9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,
d821169a-a0c4-11ed-8f6b-12e73c711187:1-31 |
+---------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

At present, the GTID (Global Transaction Identifier) is identical in both the global configuration and the xtrabackup_binlog_info file. If they happen to differ, follow the steps below to ensure they are set to the same value.

 
mydbops@localhost:(none)>reset master;
Query OK, 0 rows affected (0.01 sec)


mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+
1 row in set (0.00 sec)

mydbops@localhost:(none)>set global gtid_purged="9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,d821169a-a0c4-11ed-8f6b-12e73c711187:1-31";
Query OK, 0 rows affected (0.00 sec)

mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------------------------------------------------------------------------------------+
| Variable_name | Value                                                                               |
+---------------+-------------------------------------------------------------------------------------+
| gtid_purged   | 9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,
d821169a-a0c4-11ed-8f6b-12e73c711187:1-31 |
+---------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mydbops@localhost:(none)>

After setting the GTID_PURGED value, it is necessary to execute the “change master” command and initiate the group replication process.

 
mydbops@localhost:(none)>change master to master_user='repl',master_password='******' FOR CHANNEL 'group_replication_recovery';

Query OK, 0 rows affected, 5 warnings (0.03 sec)

mydbops@localhost:(none)>start group_replication;

Query OK, 0 rows affected, 1 warning (2.11 sec)

mydbops@localhost:(none)>select member_host,member_state,member_role,member_version from performance_schema.replication_group_members;

+---------------+--------------+-------------+----------------+

| member_host   | member_state | member_role | member_version |

+---------------+--------------+-------------+----------------+

| node2         | ONLINE       | SECONDARY   | 8.0.32         |

| node3         | ONLINE       | SECONDARY   | 8.0.32         |

| node1         | ONLINE       | PRIMARY     | 8.0.32         |

+---------------+--------------+-------------+----------------+

3 rows in set (0.00 sec)

Clone plugin approach

In this method, you stream the data to the new server using the clone plugin. Once the streaming is complete, you can retrieve the GTID_EXECUTED value from performance_schema.clone_status or status_recovery.

Here are the steps involved:

Retrieve the GTID_EXECUTED value from the clone_status or status_recovery on the new server (Node 3).

 
root@node3:/var/lib/mysql/#clone# cat '#status_recovery'
1677837049968723
1677837050743086
/var/lib/mysql/binlog.000010
1051
9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,
d821169a-a0c4-11ed-8f6b-12e73c711187:1-19
root@node3:/var/lib/mysql/#clone#

mydbops@localhost:(none)>select STATE,BEGIN_TIME,END_TIME,BINLOG_FILE,BINLOG_POSITION,GTID_EXECUTED from performance_schema.clone_status\G
*************************** 1. row ***************************
         STATE: Completed
    BEGIN_TIME: 2023-04-11 09:00:19.433
      END_TIME: 2023-04-11 09:00:25.198
   BINLOG_FILE: binlog.000010
BINLOG_POSITION: 1081
 GTID_EXECUTED: 9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,
d821169a-a0c4-11ed-8f6b-12e73c711187:1-34
1 row in set (0.00 sec)


mydbops@localhost:(none)>show variables like 'gtid_purged';
+---------------+-------------------------------------------------------------------------------------+
| Variable_name | Value                                                                               |
+---------------+-------------------------------------------------------------------------------------+
| gtid_purged  | 9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,
d821169a-a0c4-11ed-8f6b-12e73c711187:1-34 |
+---------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

If the current GTID is the same in both the global variables for MySQL and in xtrabackup_binlog_info, clone_recovery, or clone_status, then there is no need to take any further action. However, if they are not identical, please follow the steps below to ensure they are set to the same value.

 
mydbops@localhost:(none)>reset master;
Query OK, 0 rows affected (0.01 sec)

mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+
1 row in set (0.00 sec)

mydbops@localhost:(none)>set global gtid_purged="9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,d821169a-a0c4-11ed-8f6b-12e73c711187:1-31";
Query OK, 0 rows affected (0.00 sec)


mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------------------------------------------------------------------------------------+
| Variable_name | Value                                                                               |
+---------------+-------------------------------------------------------------------------------------+
| gtid_purged   | 9e470ac3-a0c1-11ed-b594-1253971a943d:1-7,
d821169a-a0c4-11ed-8f6b-12e73c711187:1-31 |
+---------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

After setting the value of gtid_purged, the next steps involve running the “change master” command and initiating the group replication.

 
#mydbops@localhost:(none)>change master to master_user='repl',master_password='******' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.04 sec)

mydbops@localhost:(none)>start group_replication;
Query OK, 0 rows affected, 1 warning (7.73 sec)

mydbops@localhost:(none)>select member_host,member_state,member_role,member_version from performance_schema.replication_group_members;
+---------------+--------------+-------------+----------------+
| member_host   | member_state | member_role | member_version |
+---------------+--------------+-------------+----------------+
| node2         | ONLINE       | SECONDARY   | 8.0.32         |
| node3         | ONLINE       | SECONDARY   | 8.0.32         |
| node1         | ONLINE       | PRIMARY     | 8.0.32         |
+---------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Logical backup approach

For Windows servers, a logical backup option is available prior to version 8.0.17. For server versions greater than 8.0.17, the recommended approach is to use a clone plugin.

Here are the steps involved in the Logical Backup Approach:

In this method, use the built-in utility mysqldump to take a logical backup.

To ensure the inclusion of GTID and binlog details, it is necessary to use the “–master-data=2” option when taking a backup with mysqldump.

Once the backup is completed, the backup file will contain the following details at the beginning of the table.

 
--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'b8192f4b-a054-32d6-ac0b-88b32af3ba1a:1-367';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=564;

--

Once the value of gtid_purged is obtained from the dump file, use the same value to set the gtid_purged on the server as well.

 
mydbops@localhost:(none)>reset master;
Query OK, 0 rows affected (0.01 sec)

mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+
1 row in set (0.00 sec)

mydbops@localhost:(none)>set global gtid_purged="b8192f4b-a054-32d6-ac0b-88b32af3ba1a:1-367";
Query OK, 0 rows affected (0.00 sec)

mydbops@localhost:(none)>show global variables like 'gtid_purged';
+---------------+-------------------------------------------------------------------------------------+
| Variable_name | Value                                                                               |
+---------------+-------------------------------------------------------------------------------------+
| gtid_purged   | b8192f4b-a054-32d6-ac0b-88b32af3ba1a:1-367
 |
+---------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

After setting the gtid_purged value, proceed by running the “change master” command and then start the group replication process.

 
mydbops@localhost:(none)>change master to master_user='repl',master_password='*****' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.03 sec)

mydbops@localhost:(none)>start group_replication;
Query OK, 0 rows affected, 1 warning (2.11 sec)

mydbops@localhost:(none)>select member_host,member_state,member_role,member_version from performance_schema.replication_group_members;
+---------------+--------------+-------------+----------------+
| member_host   | member_state | member_role | member_version |
+---------------+--------------+-------------+----------------+
| node2         | ONLINE       | SECONDARY   | 8.0.32         |
| node3         | ONLINE       | SECONDARY   | 8.0.32         |
| node1         | ONLINE       | PRIMARY     | 8.0.32         |
+---------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

In summary, regardless of the chosen approach, it is crucial to thoroughly examine the configuration of the new node before incorporating it into the cluster. This involves ensuring that the database structure and data align with the other nodes in the cluster.

By following these steps, we can seamlessly integrate the new instance into the existing Group Replication cluster, taking into account factors such as the version compatibility and overall convenience.

Ensuring the smooth addition of new nodes to the cluster contributes to the stability and reliability of the system, enabling it to handle increased workload and maintain high availability. It is an essential practice for maintaining a robust and scalable database infrastructure.

Optimize your MySQL Group Replication setup with Mydbops' InnoDB Cluster Consulting Services. Our experts can help you streamline your database management and improve performance. Contact us today to discuss your specific needs.

{{cta}}