How to set up 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.
Things you need¶
Setting up a replica for replication with Percona XtraBackup is a straightforward procedure. In order to keep it simple, here is a list of the things you need to follow the steps without hassles:
Source
A system with a MySQL-based server installed, configured and running. This
system is called Source
. The Source
server stores your data and can be replicated. We 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.
Replica
Another system, with a MySQL-based server installed on it. We refer to this machine as Replica
and assume the same things
we did about Source
, except that the server-id on Replica
is 2.
Percona XtraBackup
We use this backup tool. Install Percona XtraBackup on both computers for convenience.
Note
It is not recommended to mix MySQL variants (Percona Server, MySQL) in your replication setup. This may produce incorrect xtrabackup_slave_info
file when adding a new replica.
1. Make a backup on the Source
and prepare it¶
At the Source
, issue the following to a shell:
$ xtrabackup --backup --user=yourDBuser --password=MaGiCdB1 --target-dir=/path/to/backupdir
After this is finished you should get:
Expected output
xtrabackup: completed OK!
This operation makes a copy of 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).
In order for snapshot to be consistent, prepare the data on the source:
$ xtrabackup --prepare --target-dir=/path/to/backupdir
Select the path where your snapshot has been taken. Apply the transaction logs to the data files and your data files are ready to be used by the MySQL server.
Percona XtraBackup reads the my.cnf file to locate your data.
If you have your configuration file in a non-standard place,
you should use the flag --defaults-file
=/location/of/my.cnf
.
If you want to skip writing the username 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.
This statement provides root access to MySQL.
2. Copy backed up data to the Replica¶
On the Source, use rsync or scp to copy the data from the Source to the
Replica. If you are syncing the data directly to replica’s data directory,
we recommend that you stop the mysqld
there.
$ rsync -avpP -e ssh /path/to/backupdir Replica:/path/to/mysql/
After data is 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 commands on the Replica:
$ mv /path/to/mysql/datadir /path/to/mysql/datadir_bak
and move the snapshot from the Source
in its place:
$ xtrabackup --move-back --target-dir=/path/to/mysql/backupdir
After you copy data over, make sure the Replica MySQL has the proper permissions to access them.
$ chown mysql:mysql /path/to/mysql/datadir
If the ibdata and iblog files are located in directories outside the datadir, you must put these files in their proper place after the logs have been applied.
3. Configure the Source’s MySQL server¶
On the source, run the following command to add the appropriate grant. This grant allows the replica to be able to connect to source:
mysql> GRANT REPLICATION REPLICA ON *.* TO 'repl'@'$replicaip'
IDENTIFIED BY '$replicapass';
Also make sure that firewall rules are correct and that the Replica
can
connect to the Source
. Run the following command on the Replica to test
that you can run the mysql client on Replica
, connect to the Source
,
and authenticate.
mysql> mysql --host=Source --user=repl --password=$replicapass
Verify the privileges.
mysql> SHOW GRANTS;
4. Configure the Replica’s MySQL server¶
Copy the my.cnf
file from the Source
to the Replica
:
$ scp user@Source:/etc/mysql/my.cnf /etc/mysql/my.cnf
and 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
it’s the same as that user’s password on the Source
. Password can be seen
and updated in /etc/mysql/debian.cnf
.
5. Start the replication¶
On the Replica
, review the content of the xtrabackup_binlog_info
file:
$ cat /var/lib/mysql/xtrabackup_binlog_info
The results should resemble the following:
Expected output
Source-bin.000001 481
Do the following on a MySQL console and use the username and password you’ve set up in STEP 3:
Use the CHANGE_REPLICATION_SOURCE_TO
statement
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='$sourceip',
SOURCE_USER='repl',
SOURCE_PASSWORD='$replicapass',
SOURCE_LOG_FILE='Source-bin.000001',
SOURCE_LOG_POS=481;
Start the replica:
START REPLICA;
6. Check¶
On the Replica
, check that everything went OK with:
SHOW REPLICA STATUS\G
The result shows the status:
Expected output
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 13
Both IO
and SQL
threads need to be running. The Seconds_Behind_Source
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 a 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 --slave-info
Percona XtraBackup creates additional file
called xtrabackup_slave_info
.
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
For example, to set up a new user, user2
, you add another grant on
the Source:
> GRANT REPLICATION REPLICA ON *.* TO 'user2'@'$newreplicaip'
IDENTIFIED BY '$replicapass';
On the NewReplica
, copy the configuration file from the Replica
:
$ 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 source_log_file and source_log_pos from the
file xtrabackup_slave_info
, execute the statement for setting up the
source and the log file for the NewReplica:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='$sourceip',
SOURCE_USER='repl',
SOURCE_PASSWORD='$replicapass',
SOURCE_LOG_FILE='Source-bin.000001',
SOURCE_LOG_POS=481;
Start the replica:
> START REPLICA;
If both IO and SQL threads are running when you check the NewReplica
,
server is replicating the Source
.
Get expert help¶
If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.