Skip to content
logo
Percona XtraBackup
How to setup a replica for replication in 6 simple steps with Percona XtraBackup
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.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

    • All the things you will need
    • STEP 1: Make a backup on the Source and prepare it
    • STEP 2: Copy backed up data to the Replica
    • STEP 3: Configure the Source MySQL server
    • STEP 4: Configure the Replica MySQL server
    • STEP 5: Start the replication
    • STEP 6: Check
    • Adding more replicas to the Source

    How to setup a replica for replication in 6 simple steps with Percona XtraBackup¶

    Data is, by far, the most valuable part of a system. Having a backup done systematically and available for a rapid recovery in case of failure is admittedly essential to a system. However, it is not common practice because of its costs, infrastructure needed or even the boredom associated to the task. Percona XtraBackup is designed to solve this problem.

    You can have almost real-time backups in 6 simple steps by setting up a replication environment with Percona XtraBackup.

    Percona XtraBackup is a tool for backing up your data without interruption. It performs “hot backups” on unmodified versions of MySQL servers (5.1, 5.5 and 5.6), as well as MariaDB and Percona Server for MySQL. It is a totally free and open source software distributed only under the GPLv2 license.

    All the things you will need¶

    Setting up a replica for replication with Percona XtraBackup is a straightforward procedure. You must have the following things to complete the steps:

    • Source A system with a MySQL-based server installed, configured and running. This system is called the Source, and is where your data is stored and used for replication. We assume the following about this server:

      • Communication enabled with others by the standard TCP/IP port

      • Installed and configured SSH server

      • Configured user account in the system with the appropriate permissions and privileges

      • Enabled binlogs and the server-id set up to 1

    • Replica MySQL-based server installed on another server. This server is called the Replica. We assume the same configuration as the Source, except that the server-id on the Replica is 2.

    • Percona XtraBackup The backup tool should be installed in both servers for convenience.

    Note

    It is not recommended to mix MySQL variants (Percona Server, MySQL, MariaDB) in your replication setup. This may produce incorrect xtrabackup_slave_info file when adding a new replica.

    STEP 1: Make a backup on the Source and prepare it¶

    On the Source, issue the following command to a shell:

    $ xtrabackup --backup --user=yourDBuser --password=MaGiCdB1 --target-dir=/path/to/backupdir
    

    After this is finished you should see:

    xtrabackup: completed OK!
    

    This action copies your MySQL data dir to the /path/to/backupdir directory. You have told Percona XtraBackup to connect to the database server using your database user and password, and do a hot backup of all your data in it (all MyISAM, InnoDB tables and indexes in them).

    On the Source, to make the snapshot consistent, prepare the data:

    $ xtrabackup --user=yourDBuser --password=MaGiCdB1 \
                --prepare --target-dir=/path/to/backupdir
    

    Select the path where your snapshot has been taken. If everything is ok you should see 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.

    Percona XtraBackup knows where your data is by reading your my.cnf. If you have your configuration file in a non-standard place, use the flag xtrabackup --defaults-file =/location/of/my.cnf.

    If you want to skip writing the user name and password every time you want to access MySQL, set the information in .mylogin.cnf as follows:

    $ mysql_config_editor set --login-path=client --host=localhost 
    --user=root --password
    

    This setting provides root access to MySQL.

    See also

    MySQL Documentaiton: MySQL Configuration Utility

    STEP 2: Copy backed up data to the Replica¶

    Use rsync or scp to copy the data from Source to Replica. If you’re syncing the data directly to replica’s data directory it’s advised to stop the mysqld there. On the Source, run the following command:

    $ rsync -avpP -e ssh /path/to/backupdir Replica:/path/to/mysql/
    

    After data has been copied you can back up the original or previously installed MySQL datadir (NOTE: Make sure mysqld is shut down before you move the contents of its datadir, or move the snapshot into its datadir.). Run the following command on the Replica:

    $ mv /path/to/mysql/datadir /path/to/mysql/datadir_bak
    

    and, on the Replica, move the snapshot from the Source in its place:

    $ xtrabackup --move-back --target-dir=/path/to/mysql/backupdir
    

    After you have copied data to the Replica, make sure the Replica MySQL has the proper permissions:

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

    In case the ibdata and iblog files are located in different directories outside the datadir, put them in their proper place after the logs have been applied.

    STEP 3: Configure the Source MySQL server¶

    On the Source, add the appropriate grant to allow the replica to connect to the source:

     > GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$replicaip'
    IDENTIFIED BY '$replicapass';
    

    Also make sure that firewall rules are correct and that the Replica can connect to the Source. Test that you can run the mysql client on the Replica, connect to the Source, and authenticate.

    $ mysql --host=Source --user=repl --password=$replicapass
    

    Verify the privileges.

    mysql> SHOW GRANTS;
    

    STEP 4: Configure the Replica MySQL server¶

    Copy the my.cnf file from the Source to the Replica. On the Replica, run the following:

    $ scp user@Source:/etc/mysql/my.cnf /etc/mysql/my.cnf
    

    then change the following options in /etc/mysql/my.cnf:

    server-id=2
    

    and start/restart mysqld on the Replica.

    In case you’re using init script on Debian based system to start mysqld, be sure that the password for debian-sys-maint user has been updated and is the same as the user’s password on the Source. This password can be seen and updated in /etc/mysql/debian.cnf.

    STEP 5: Start the replication¶

    On the Replica, look at the content of the file xtrabackup_binlog_info, it will be something like:

     $ cat /var/lib/mysql/xtrabackup_binlog_info
    Source-bin.000001     481
    

    Execute the CHANGE MASTER statement on a MySQL console and use the username and password you’ve set up in STEP 3:

    TheSlave*mysql> CHANGE MASTER TO
                    MASTER_HOST='$sourceip',
                    MASTER_USER='repl',
                    MASTER_PASSWORD='$replicapass',
                    MASTER_LOG_FILE='Source-bin.000001',
                    MASTER_LOG_POS=481;
    

    and start the replica:

    > START SLAVE;
    

    STEP 6: Check¶

    On the Replica, check that everything went OK with:

    TheSlave|mysql> SHOW SLAVE STATUS \G
    
    The results should look like the following:

     ...
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     ...
     Seconds_Behind_Master: 13
     ...
    

    Both IO and SQL threads need to be running. The Seconds_Behind_Master means the SQL currently being executed has a current_timestamp of 13 seconds ago. It is an estimation of the lag between the Source and the Replica. Note that at the beginning, a high value could be shown because the Replica has to “catch up” with the Source.

    Adding more replicas to the Source¶

    You can use this procedure with slight variation to add new replicas to a source. We will use Percona XtraBackup to clone an already configured replica. We will continue using the previous scenario for convenience but we will add the NewReplica to the plot.

    At the Replica, do a full backup:

     $ xtrabackup --user=yourDBuser --password=MaGiCiGaM \
    --backup --slave-info --target-dir=/path/to/backupdir
    

    By using the xtrabackup --slave-info Percona XtraBackup creates additional file called xtrabackup_slave_info.

    On the Replica, apply the logs:

    $ xtrabackup --prepare --use-memory=2G --target-dir=/path/to/backupdir/
    

    Note

    In the prepare phase, the --use-memory parameter speeds up the process if the amount of RAM assigned to the option is available. Use the parameter only in the prepare phase. In the other phases the parameter makes the application lazy allocate this memory (reserve) but does not affect database pages.

    Copy the directory from the Replica to the NewReplica (NOTE: Make sure mysqld is shut down on the NewReplica before you copy the contents the snapshot into its datadir.):

    rsync -avprP -e ssh /path/to/backupdir NewReplica:/path/to/mysql/datadir
    

    On the Source, add additional grant on the source:

    > GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$newreplicaip'
                     IDENTIFIED BY '$replicapass';
    

    Copy the configuration file from the Replica. On the NewReplica, run the following command:

    $ scp user@Replica:/etc/mysql/my.cnf /etc/mysql/my.cnf
    

    Make sure you change the server-id variable in /etc/mysql/my.cnf to 3 and disable the replication on start:

    skip-slave-start
    server-id=3
    

    After setting server_id, start mysqld.

    Fetch the master_log_file and master_log_pos from the file xtrabackup_slave_info, execute the statement for setting up the source and the log file for the NewReplica:

    TheNEWSlave|mysql> CHANGE MASTER TO
                       MASTER_HOST='$sourceip',
                       MASTER_USER='repl',
                       MASTER_PASSWORD='$replicapass',
                       MASTER_LOG_FILE='Source-bin.000001',
                       MASTER_LOG_POS=481;
    

    and start the replica:

    > START SLAVE;
    

    If both IO and SQL threads are running when you check the the NewReplica, server is replicating the Source.

    See also

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

    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.


    Last update: 2023-01-30
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.