Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.6Kb
Man Pages (Zip) - 329.3Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Using MySQL Enterprise Backup with Group Replication

17.4.4 Using MySQL Enterprise Backup with Group Replication

This section explains how to back up and subsequently restore a Group Replication member using MySQL Enterprise Backup; the same technique can be used to quickly add a new member to a group. Generally, backing up a Group Replication member is no different to backing up a stand-alone MySQL instance. The recommended process is to use MySQL Enterprise Backup image backups and a subsequent restore, for more information see Backup Operations.

The required steps can be summarized as:

  • Use MySQL Enterprise Backup to create a backup of the source server instance with simple timestamps.

  • Copy the backup to the destination server instance.

  • Use MySQL Enterprise Backup to restore the backup to the destination server instance.

The following procedure demonstrates this process. Consider the following group:

mysql> SELECT * member_host, member_port, member_state FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+
| member_host | member_port | member_state |
+-------------+-------------+--------------+
| node1       |        3306 | ONLINE       |
| node2       |        3306 | ONLINE       |
| node3       |        3306 | ONLINE       |
+-------------+-------------+--------------+

In this example the group is operating in single-primary mode and the primary group member is node1. This means that node2 and node3 are secondaries, operating in read-only mode (super_read_only=ON). Using MySQL Enterprise Backup, a recent backup has been taken of node2 by issuing:

mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/backups/my.mbi_`date +%d%m_%H%M` / 
		      --backup-dir=/backups/backup_'date +%d%m_%H%M' --user=root -pmYsecr3t /
		      --host=127.0.0.1 --no-history-logging backup-to-image

The --no-history-logging option is used because node2 is a secondary, and because it is read-only MySQL Enterprise Backup cannot write status and metadata tables to the instance.

Assume that the primary member, node1, encounters irreconcilable corruption. After a period of time the server instance is rebuilt but all the data on the member was lost. The most recent backup of member node2 can be used to rebuild node1. This requires copying the backup files from node2 to node1 and then using MySQL Enterprise Backup to restore the backup to node1. The exact way you copy the backup files depends on the operating system and tools available to you. In this example we assume Linux servers and use SCP to copy the files between servers:

node2/backups # scp my.mbi_2206_1429 node1:/backups

Connect to the destination server, in this case node1, and restore the backup using MySQL Enterprise Backup by issuing:

mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/backups/`ls -1rt /backups|tail /
		      -n 1` --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log

The backup is restored to the destination server.

If your group is using multi-primary mode, extra care must be taken to prevent writes to the database during the MySQL Enterprise Backup restore stage and the Group Replication recovery phase. Depending on how the group is accessed by clients, there is a possibility of DML being executed on the newly joined instance the moment it is accessible on the network, even prior to applying the binary log before rejoining the group. To avoid this, configure the member's option file with:

group_replication_start_on_boot=OFF
super_read_only=ON
event_scheduler=OFF

This ensures that Group Replication is not started on boot, that the member defaults to read-only and that the event_scheduler is turned off while the member catches up with the group during the recovery phase. Adequate error handling must be configured on the clients to recognise that they are, temporarily, prevented from performing DML during this period.

Start the server instance and connect an SQL client. The restored backup has old binary log files and related metadata that are specific to the instance that was backed up. To reset all of that issue:

mysql> RESET MASTER;

The restored backup has the relay log files associated with the source instance, in this case node2. Therefore reset the logs, metadata, and configuration for all replication channels by issuing:

mysql> RESET SLAVE ALL;

For the restored instance to be able to be able to recover automatically using Group Replication's built-in distributed recovery (see Section 17.9.5, “Distributed Recovery”), configure the gtid_executed variable. The MySQL Enterprise Backup backup from node2 includes the backup_gtid_executed.sql file, usually at the path datadir/meta/, which contains the information required to configure node1. Disable binary logging and then use this file to configure the gtid_executed variable by issuing:

mysql> SET SQL_LOG_BIN=OFF;
mysql> SOURCE datadir/meta/backup_gtid_executed.sql
mysql> SET SQL_LOG_BIN=ON;

Configure the Section 17.2.1.3, “User Credentials” and start Group Replication, for example:

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_user' / 
		FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;

The instance attempts to join the group, executing the restored binary logs from the correct location. Once the instance gains synchrony with the group, it joins as a secondary, with super_read_only=ON. Reset the temporary configuration changes made during the restore. Turn the event scheduler back on in the running process:

mysql> SET global event_scheduler=ON;

Edit the following system variables in the instance's option file:

group_replication_start_on_boot=ON
super_read_only=ON
event_scheduler=ON

User Comments
Sign Up Login You must be logged in to post a comment.