Skip to content
Starting November 2023 Percona XtraBackup 2.4 has reached EOL status. If you have 5.7 databases, we encourage you to upgrade to 8.0 and then install Percona XtraBackup 8.0. Learn more

logo
Percona XtraBackup
How to create a new (or repair a broken) GTID based slave
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup Works
      • Understand version numbers
      • Installing Percona XtraBackup 2.4
      • Installing Percona XtraBackup on Debian and Ubuntu
      • Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
      • Installing Percona XtraBackup from a Binary Tarball
      • Compiling and Installing from Source Code
      • Running Percona XtraBackup in a Docker container
      • Connection and Privileges Needed
      • Configuring xtrabackup
      • The Backup Cycle - Full Backups
      • Incremental Backup
      • Compressed Backup
      • Encrypted Backup
      • Percona XtraBackup User Manual
      • Throttling Backups
      • Lockless binary log information
      • Encrypted InnoDB Tablespace Backups
      • lock-ddl-per-table Option Improvements
      • How-tos and Recipes
        • Release notes index
        • Percona XtraBackup 2.4.29 (2023-12-18)
        • Percona XtraBackup 2.4.28 (2023-04-04)
        • Percona XtraBackup 2.4.27 (2022-12-06)
        • Percona XtraBackup 2.4.26
        • Percona XtraBackup 2.4.25
        • Percona XtraBackup 2.4.24
        • Percona XtraBackup 2.4.23
        • Percona XtraBackup 2.4.22
        • Percona XtraBackup 2.4.21
        • Percona XtraBackup 2.4.20
        • Percona XtraBackup 2.4.19
        • Percona XtraBackup 2.4.18
        • Percona XtraBackup 2.4.17
        • Percona XtraBackup 2.4.16
        • Percona XtraBackup 2.4.15
        • Percona XtraBackup 2.4.14
        • Percona XtraBackup 2.4.13
        • Percona XtraBackup 2.4.12
        • Percona XtraBackup 2.4.11
        • Percona XtraBackup 2.4.10
        • Percona XtraBackup 2.4.9
        • Percona XtraBackup 2.4.8
        • Percona XtraBackup 2.4.7-2
        • Percona XtraBackup 2.4.7
        • Percona XtraBackup 2.4.6
        • Percona XtraBackup 2.4.5
        • Percona XtraBackup 2.4.4
        • Percona XtraBackup 2.4.3
        • Percona XtraBackup 2.4.2
        • Percona XtraBackup 2.4.1
      • The xtrabackup Option Reference
      • The innobackupex Option Reference
      • The xbcloud Binary
      • Exponential Backoff
      • Using the xbcloud binary with Microsoft Azure Cloud Storage
      • The xbcrypt binary
      • The xbstream binary
      • Known issues and limitations
      • Frequently Asked Questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version Checking

    • STEP 1: Take a backup from any server on the replication environment, source or replica.
      • STEP 2: Prepare the backup
      • STEP 3: Move the backup to the destination server
      • STEP 4: Configure and start replication
      • STEP 5: Check the replication status

    How to create a new (or repair a broken) GTID based slave¶

    MySQL 5.6 introduced the new Global Transaction ID (GTID) support in replication. Percona XtraBackup automatically stores the GTID value in the xtrabackup_binlog_info when doing the backup of MySQL and Percona Server for MySQL 5.7 with the GTID mode enabled. This information can be used to create a new (or repair a broken) GTID based replica.

    STEP 1: Take a backup from any server on the replication environment, source or replica.¶

    The following command takes a backup and saves it in the /data/backups/$TIMESTAMP folder:

    $ innobackupex /data/backups/
    

    In the destination folder, there will be a file with the name xtrabackup_binlog_info. This file contains both binary log coordinates and the GTID information.

    $ cat xtrabackup_binlog_info
    

    The results should be similar to the following:

    mysql-bin.000002    1232     c777888a-b6df-11e2-a604-080027635ef5:1-4
    

    That information is also printed by innobackupex after taking the backup:

    innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 1232, GTID of the last change 'c777888a-b6df-11e2-a604-080027635ef5:1-4'
    

    STEP 2: Prepare the backup¶

    The backup will be prepared with the following command:

    TheMaster$ innobackupex --apply-log /data/backups/$TIMESTAMP/
    

    You need to select the path where your snapshot has been taken, for example /data/backups/2013-05-07_08-33-33. If everything is ok you should get the same OK message. Now, the transaction logs are applied to the data files, and new ones are created: your data files are ready to be used by the MySQL server.

    STEP 3: Move the backup to the destination server¶

    Use rsync or scp to copy the data to the destination server. If you are synchronizing the data directly to the already running replica’s data directory it is advised to stop the MySQL server there.

    $ rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP NewSlave:/path/to/mysql/
    

    After you copy the data over, make sure MySQL has proper permissions to access them.

    $ chown mysql:mysql /path/to/mysql/datadir
    

    STEP 4: Configure and start replication¶

    Set the gtid_purged variable to the GTID from xtrabackup_binlog_info. Then, update the information about the source node and, finally, start the replica.

    # Using the mysql shell
    NewSlave > SET SESSION wsrep_on = 0;
    NewSlave > RESET MASTER;
    NewSlave > SET SESSION wsrep_on = 1;
    NewSlave > SET GLOBAL gtid_purged='<gtid_string_found_in_xtrabackup_binlog_info>';
    NewSlave > CHANGE MASTER TO
                 MASTER_HOST="$masterip",
                 MASTER_USER="repl",
                 MASTER_PASSWORD="$slavepass",
                 MASTER_AUTO_POSITION = 1;
    NewSlave > START SLAVE;
    

    Note

    The example above is applicable to Percona XtraDB Cluster. The wsrep_on variable is set to 0 before resetting the source (RESET MASTER). The reason is that Percona XtraDB Cluster will not allow resetting the source if wsrep_on=1.

    STEP 5: Check the replication status¶

    Following command will show the replica status:

    NewSlave > SHOW SLAVE STATUS\G
    
    The results should be similar to as follows:

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    [...]
    Retrieved_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:5
    Executed_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:1-5
    

    We can see that the replica has retrieved a new transaction with number 5, so transactions from 1 to 5 are already on this replica.

    That’s all, we have created a new replica in our GTID based replication environment.

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.

    2022-11-10
    Percona LLC and/or its affiliates, © 2024 Cookie Preferences
    Made with Material for MkDocs