How to setup a slave 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.
All the things you will need¶
Setting up a slave for replication with Percona XtraBackup is really a very straightforward procedure. In order to keep it simple, here is a list of the things you need to follow the steps without hassles:
A system with a MySQL-based server installed, configured and running. This system will be called
TheMaster, as it is where your data is stored and the one to be replicated. We will assume the following about this system:
- the MySQL server is able to communicate with others by the standard TCP/IP port;
- the SSH server is installed and configured;
- you have a user account in the system with the appropriate permissions;
- you have a MySQL’s user account with appropriate privileges.
- server has binlogs enabled and server-id set up to 1.
- Another system, with a MySQL-based server installed on it. We
will refer to this machine as
TheSlaveand we will assume the same things we did about
TheMaster, except that the server-id on
- The backup tool we will use. It should be installed in both computers for convenience.
It is not recommended to mix MySQL variants (Percona Server, MySQL) in your
replication setup. This may produce incorrect
file when adding a new slave.
STEP 1: Make a backup on
TheMaster and prepare it¶
TheMaster, issue the following to a shell:
TheMaster$ xtrabackup --backup --user=yourDBuser --password=MaGiCdB1 --target-dir=/path/to/backupdir
After this is finished you should get:
xtrabackup: completed OK!
This will make a copy of your MySQL data dir
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).
In order for snapshot to be consistent you need to prepare the data:
TheMaster$ xtrabackup --user=yourDBuser --password=MaGiCdB1 \ --prepare --target-dir=/path/to/backupdir
You need to select path where your snapshot has been taken. 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.
If you want to skip writing the user name and password
every time you want to access MySQL,
you can set it up in
.mylogin.cnf as follows:
mysql_config_editor set --login-path=client --host=localhost --user=root --password
For more information, see MySQL Configuration Utility <https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html>.
This is will give you root access to MySQL.
STEP 2: Copy backed up data to TheSlave¶
Use rsync or scp to copy the data from Master to Slave. If you’re syncing the data directly to slave’s data directory it’s advised to stop the mysqld there.
TheMaster$ rsync -avpP -e ssh /path/to/backupdir TheSlave:/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.):
TheSlave$ mv /path/to/mysql/datadir /path/to/mysql/datadir_bak
and move the snapshot from
TheMaster in its place:
TheSlave$ xtrabackup --move-back --target-dir=/path/to/mysql/backupdir
After you copy data over, make sure MySQL has proper permissions to access them.
TheSlave$ chown mysql:mysql /path/to/mysql/datadir
In case the ibdata and iblog files are located in different directories outside of the datadir, you will have to put them in their proper place after the logs have been applied.
STEP 3: Configure The Master’s MySQL server¶
Add the appropriate grant in order for slave to be able to connect to master:
TheMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'$slaveip' IDENTIFIED BY '$slavepass';
Also make sure that firewall rules are correct and that
TheSlave can connect to
TheMaster. Test that you can run the mysql client on
TheSlave, connect to
TheMaster, and authenticate.
TheSlave$ mysql --host=TheMaster --user=repl --password=$slavepass
Verify the privileges.
mysql> SHOW GRANTS;
STEP 4: Configure The Slave’s MySQL server¶
First copy the my.cnf file from
TheSlave$ scp user@TheMaster:/etc/mysql/my.cnf /etc/mysql/my.cnf
then change the following options in /etc/mysql/my.cnf:
and start/restart mysqld on
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 it’s the same as that user’s password on the
TheMaster. Password can be seen and updated in
STEP 5: Start the replication¶
Look at the content of the file
xtrabackup_binlog_info, it will be something like:
TheSlave$ cat /var/lib/mysql/xtrabackup_binlog_info TheMaster-bin.000001 481
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='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;
and start the slave:
TheSlave|mysql> START SLAVE;
STEP 6: Check¶
You should check that everything went OK with:
TheSlave|mysql> SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 13 ...
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
TheSlave. Note that at the beginning, a high value could be shown because
TheSlave has to “catch up” with
Adding more slaves to The Master¶
You can use this procedure with slight variation to add new slaves to a master. We will use Percona XtraBackup to clone an already configured slave. We will continue using the previous scenario for convenience but we will add
TheNewSlave to the plot.
TheSlave, do a full backup:
TheSlave$ xtrabackup --user=yourDBuser --password=MaGiCiGaM \ --backup --slave-info --target-dir=/path/to/backupdir
By using the
--slave-info Percona XtraBackup creates additional file called
Apply the logs:
TheSlave$ xtrabackup --prepare --use-memory=2G --target-dir=/path/to/backupdir/
Copy the directory from the
TheNewSlave (NOTE: Make sure mysqld is shut down on
TheNewSlave before you copy the contents the snapshot into its datadir.):
rsync -avprP -e ssh /path/to/backupdir TheNewSlave:/path/to/mysql/datadir
Add additional grant on the master:
TheMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'$newslaveip' IDENTIFIED BY '$slavepass';
Copy the configuration file from
TheNEWSlave$ scp user@TheSlave:/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:
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 master and the log file for
The NEW Slave:
TheNEWSlave|mysql> CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;
and start the slave:
TheNEWSlave|mysql> START SLAVE;
If both IO and SQL threads are running when you check the
TheNewSlave, server is replicating