Using Percona XtraBackup in daily life

The high prevalence of MySQL databases leads to its use in a variety of projects, including those that require non-stop operation and minimal recovery time. Standard tools such as mysqldump are inconvenient to use databases in size, which is measured in tens of gigabytes. Unfortunately, mysqldump causes table locking, and the operation of removing a dump or restoring a database can take far more than one hour, and sometimes even 5-10 hours.

Fortunately, Percona XtraBackup comes to our rescue.


')


Just want to draw attention to the fact that most of the examples will be provided, provided that you use InnoDB, because when using MyISAM in the dump process, the tables will be locked, which will give you a gain only in backup / restore speed.

Many will immediately try to shower sleepers, such as use a replica. Absolutely agree, the replica server is the perfect solution. We can do both dumps and locks with it - as much as we want. But:

1) We need to always monitor the status of the replica, so that it is relevant, for which additional resources are needed.

2) In this example, we are considering the issue of recovery time, which is why mysqldump with a replica will not speed up the situation at the time of the DB crash.

My goal was not to translate all XtraBackup documentation, but just want to show you how to quickly backup / copy / restore large databases and how we do it.

For those who do not want to think, and we are admins lazy (and laziness is the engine of progress), it will be enough to copy a couple of lines and put them in crowns. Who wants to dig deeper, based on the lines provided by me, can "finish" for themselves.

To get all the charms of an incremental backup, it is necessary that all tables be of type InnoDB and the option innodb_file_per_table = 1 be set. Physically, the table will represent two files — the table definition and the table data.

Suppose, by default, the root password for MySQL lies in /root/.mysql

Create a full backup

innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --rsync /var/lib/mysql-xtra 2>&1 

--no-timestamp we do not need to create a subdirectory with a timestamp

--rsync use rsync to copy non-InnoDB database files, copy the part before the lock, copy the part inside the lock. Does not work in streaming mode!

--throttle if you need to not ship disks - use.

We roll binary logs that have accumulated during the creation of the dump

 innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 /var/lib/mysql-xtra 2>&1 

Important note:

The / var / lib / mysql-xtra folder should be empty if you are using --no-timestamp

For example, we want to make an incremental backup

 innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 --rsync --incremental /var/lib/mysql-xtra-inc --incremental-basedir=/var/lib/mysql-xtra 2>&1 

Similarly, we roll binary logs that have accumulated during the creation of backup

 innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 --apply-log /var/lib/mysql-xtra --incremental-dir=/var/lib/mysql-xtra-inc 2>&1 

At this stage, we have received a full backup and incremental backup.

The full backup folder can be copied to another server in the mysql folder of the server and you have a complete copy of the server ready.

The recovery procedure is quite simple:

 #  mysql service mysql stop #    mv /var/lib/mysql /var/lib/mysql.old mkdir /var/lib/mysql #   innobackupex --defaults-file=/etc/my.cnf --copy-back /var/lib/mysql-xtra #      chown -R mysql.mysql /var/lib/mysql #  mysql service mysql start innobackupex --defaults-file=/etc/my.cnf --copy-back /var/lib/mysql-xtra 

If we want to restore an incremental backup, then we first roll up its full backup.

 innobackupex --apply-log --redo-only /var/lib/mysql-xtra --incremental-dir=/var/lib/mysql-xtra-inc 

Now our full backup contains the changes from the incremental backup and you can restore the backup according to the full backup recovery scheme.

For example, we do not want to backup the entire server, but only the base. This can be done in this way:

 #   c   ,  test innobackupex --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --databases "test" --rsync /var/lib/mysql-xtra 2>&1 #          innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf --password=`cat /root/.mysql` --no-timestamp --throttle=40 /var/lib/mysql-xtra 2>&1 #  mysql service mysql stop #    mkdir -p /var/lib/mysql.old/test mv /var/lib/mysql/test /var/lib/mysql.old/test #   mkdir /var/lib/mysql/test innobackupex --copy-back /var/lib/mysql-xtra/ #   chown -R mysql.mysql /var/lib/mysql #  mysql service mysql start 

Useful options:
--parallel = 4 parallel copying, only makes sense with innodb_file_per_table = 1
--no-lock does not block the labels, BUT! Make sure that ALTER TABLE, INSERT / UPDATE / DELETE on MyISAM tables are not executed during backup creation.
--compress since version 2.0 can compress, use if the discs are not rubber.

Innobackupex also allows you to copy separate plates, partitions, but due to the extreme rarity of use in this article I will not consider.

For InnoDB labels, you can use xtrabackup, which is fairly simple to use. Since we often have a mix of tables, we do not use it. But I will describe a couple of commands that will be enough for you to create / restore a database.

To start the backup creation process:

 xtrabackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql --target-dir=/var/lib/mysql-xtra --backup 

To restore, you need to execute the command with the option "--prepare":

 xtrabackup --prepare --target-dir=/var/lib/mysql-xtra/ --datadir=/var/lib/mysql 

The data recovery command must be executed twice, after which the log files for MySQL will be created. After that, you need to copy the log file and the data file to the MySQL folder (by default - / var / lib / mysql).

Incremental backups

It is clear that doing full backup on large volumes will be extremely resource-intensive, so XtraBackup has the ability to incremental backups and recovery. To do this, you first need to make a full backup, and then in a separate folder - the so-called. delta backup:

 xtrabackup --defaults-file=/etc/mysql/my.cnf --target-dir=/var/lib/mysql-xtra-inc --incremental-basedir=/var/lib/mysql-xtra --backup 

To restore, run the following command:

 xtrabackup --target-dir=/var/lib/mysql-xtra-inc --incremental-basedir=/var/lib/mysql-xtra --prepare 

That's all for now.

Author: Igor Medynsky, senior system administrator centos-admin.ru