Percona MySQL Backup

Introduced

Percona is the only one by one open source, free MySQL hot backup tool that enables non-blocking backup of the InnoDB database. Has the following advantages:
1. Complete, fast and reliable backup
2. Transaction execution is not interrupted during backup (InnoDB engine)
3. Backup files can be saved locally or on a network
4. Automatically verify backup validity
5. Faster recovery speed
6. Support full-scale backup, incremental backup, encrypted backup and many other features

Installation

Our servers are CentOS7 and the following are the methods for installing Percona under CentOS7.
1. Installing the Percona source
Execute the following command:

Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-4/ percona-release-0.1-4.noarch.rpm

2. Installation

Execute the following command:

Yum Install percona-xtrabackup-

3. Modify the MY.CNF configuration file

MySQL config file, default in/ETC/MY.CNF. By default, the MySQL data directory is not explicitly specified in the file, and Percona needs to read the data directory location from this file, so you need to explicitly specify the data directory location in MY.CNF. Such as:

Full-scale backup

Percona provides two scripts for various backup operations: Xtrabackup/xtrabackupex, which is recommended for use with Xtrabackupex. The Innobackupex tool is a Perl script, a Xtrabackup package that provides more functionality and is more convenient to use.
Here are the steps for a full-scale backup.
1. Create a backup
Execute the following command, where dbuser/dbuserpass the user name password of the respective database, because the backup, restore process requires a higher database permissions, here for convenience, you can use the root user, or other users with super privileges, where/path/to/ The backup-dir/is used to specify where to store the backup files:

Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

After executing this command, if the console has a print similar to the following, it indicates success.

111225 xx:innobackupex:completed ok!

Creates a timestamp-named folder under the specified Backup-dir directory, and a backup file within the folder. If required, you can specify that the folder is not generated by the-no-timestamp option.

2. Prepare backup files
This step, the official document is written "Preparing a full backup", the previous step back up files, and not directly for the database recovery, but also need a prepare step, that is, to execute the following command:

Innobackupex--apply-log–use-memory=2g/path/to/backup-dir

Where-use-memory is used to specify the memory that this procedure can use, the default is 100MB, and the larger the specified memory, the better if the system allows it.

If the console has output similar to the following, it indicates success:

150806 innobackupex:completed:ok!

After the prepare, the backup file can be used for database recovery.

Incremental backup

Not all of the data has changed every time you make a backup. Every time a full amount of backup, to do a lot of hard work, time-consuming increase, the storage of files to use the disk space is also increased. Therefore, it is better to base on a full-scale backup and then make incremental backups. The following steps are steps to make an incremental backup.
1. First, a full-scale backup is required

Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

After executing this command, if the console has a print similar to the following, it indicates success.

111225 xx:innobackupex:completed ok!

For convenience, this step generates a timestamp named folder, which we call Basedir

2. Make the first incremental backup
Execute the following command, where Basedir refers to the first step full-scale backup, the resulting directory:

Innobackupex--user=dbuser--password=dbuserpass--incremental/path/to/backup-dir/--incremental-basedir=BASEDIR

After the command executes successfully, a timestamp-named folder is generated, which we call incremental-dir-1 for convenience.

3. Make a second incremental backup
The following command should be noted that the second incremental backup is based on the first incremental backup, so the-incremental-basedir property, the value for the first incremental backup generated by the file incremental-dir-1:

Innobackupex--user=dbuser--password=dbuserpass--incremental/path/to/backup-dir/--incremental-basedir= incremental-dir-1

After the command executes successfully, a timestamp-named folder is generated, which we call incremental-dir-2 for convenience.

4. Prepare backup files
This step is different from the "prep" step for the full-scale backup, which requires an option--redo-only, meaning that only the committed transactions are replayed, but not the uncommitted transactions, but for the last incremental backup, you do not need to add this property. In this section, you need to learn more about MySQL's transaction log, as if it were not detailed.
A) for the first full-volume backup operation, execute the following command:

Innobackupex--apply-log--redo-only BASEDIR

The resulting information is as follows:

160103 :xx: innobackupex:completed ok!

b) for the first incremental backup operation, execute the following command:

Innobackupex--apply-log--redo-only base-dir--incremental-DIR=incremental-dir-1

The resulting information is as follows:

160103 : £innobackupex:completed ok!

c) for the second incremental backup, execute the following command, note that because it is the last incremental backup, you do not need to add--redo-only:

Innobackupex--apply-log base-dir--incremental-DIR=incremental-dir-2

If the result information shows "completed ok!", then the processing is complete, at this time the Basedir directory, is already a set of latest data, can be used for data recovery.

Recovering data

The following steps are used to restore the database, regardless of the full-scale or incremental backup.
1. Stop the MySQL service, using the following command, where ${mysql_root_path} refers to the MySQL installation directory:

${mysql_root_path}/support-files/mysql.server stop

2. Delete or move the MySQL data Catalog

If not specified, the directory is in ${mysql_root_path}/data, and if a different directory is specified, view the datadir=xxx in ${mysql_root_path}/support-files/mysql.server. XXX is the data directory. It is recommended that this step do not delete this data directory but move to a different location. After the data recovery is complete, verify that there is no problem, and then delete the old data files, if the data is problematic, you can also move the deleted data directory back.
3. Perform data recovery
Execute the following command to copy the backed up data file to the MySQL data directory.

Innobackupex--copy-back/path/to/backup-dir

If the console has a print similar to the following, it indicates success:

111225 From:£ innobackupex:completed ok! 

4. modifying file properties

After recovering the data directory, the owner of each file and folder is the user who created the backup and needs to be modified to all MySQL users, using the following command, where/mysql/datadir is the MySQL data directory, the specific location of this directory is shown in step 2nd:

chown -R mysql:mysql/mysql/datadir/

5. Start the database

Execute the following command:

${mysql_root_path}/support-files/mysql.server start

Point-in-time recovery

The Innobackupex and MySQL binary log files can be used for point-in-time recovery, which tells the database to revert to a certain state of history. The binary log keeps all the details of modifying the database, and you can use a historical backup plus a binary log to restore the database to a point in time. Consider the following steps to restore data to "2017-12-4 20:00:00":
1. Assume that a full-scale backup has been created with Innobackupex. And, by Basedir/xtrabackup_binlog_info, the log location of the backup is "57 in the mysql-bin.000003 file."
2. Execute the Show binary logs command in the database, as shown below, indicating that the database currently has 4 log files and the latest file is mysql-bin.000004.

3. Restore the backup created by Innobackupex to the database, as described in the "Recovering Data" section of the procedure.
4. Execute the following command to start the self-owned location to the point at which you want to recover, all of which will modify the data SQL statement and write to the file "Mybinlog.sql":

Mysqlbinlog/{mysql_data}/mysql-bin. 000003 /{mysql_data}/mysql-bin. 000004 --start-position= --stop-datetime="2017-12-4 20:00:00"> Mybinlog.sql

5. Import the SQL from the file "Mybinlog" generated in the previous step into the database to restore the database to a specified point in time.

Percona MySQL Backup