Backup and restore of MySQL based on Xtrbackup

MySQL Backup restore summary

If operating system error number in a file operation occurs when backing up the database. This error indicates that too many files are open.

Solution Ulimit-n 1048576

You can then make a backup of the database

1. Introduction

The tool used is xtrbackup the tool is open source and very powerful, provided by Perconan Corporation. This is also the world's only one by one open source of the InnoDB and XTRADB database can be hot prepared tools. Characteristics:

(1) The backup process is fast and reliable;

(2) The backup process does not interrupt the executing transaction;

(3) Can save disk space and traffic based on functions such as compression;

(4) Automatic implementation of backup inspection;

(5) Fast restore speed;

Hot standby for InnoDB storage engine and only warm backup for MyISAM storage engine

2. Installation

The latest version of the software can be obtained from http://www.percona.com/software/percona-xtrabackup/.

Yum Intall--percona-xtrabackup-2.2.5-5027.el7.x86_64.rpm

Check for generated files

RPM-QL Percona-xtrabackup

/usr/bin/innobackupex//command to implement backup restore

/usr/bin/xbcrypt

/usr/bin/xbstream

/usr/bin/xtrabackup

/usr/share/doc/percona-xtrabackup-2.2.5

/usr/share/doc/percona-xtrabackup-2.2.5/copying

3. Full backup

Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/(where to back up)

If you want to back up with a user with minimal permissions, you can create such a user based on the following command:

mysql> CREATE USER ' bkpuser ' @ ' localhost ' identified by ' S3cret ';

Mysql> REVOKE all privileges, GRANT OPTION from ' Bkpuser ';

Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' bkpuser ' @ ' localhost ';

mysql> FLUSH privileges;

When you use a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and related files for the MyISAM, MERGE, CSV, and archive tables. Files related to triggers and database configuration information are also backed up. These files are saved to a directory in the time command.

(1) xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.

(2) xtrabackup_checkpoints--the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN (log sequence number) range information;

4. Prepare (prepare) a full backup

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

The--apply-log option of the Nnobakupex command can be used to implement the above functions. As in the following command:

# Innobackupex--apply-log/path/to/backup-dir

In the process of "preparing," Innobackupex can typically use the--use-memory option to specify the amount of memory it can use, which is typically 100M by default. If there is enough memory available, you can partition some memory into the prepare process to improve its completion speed.

5. Recovering data from a full backup

The--copy-back option of the Innobackupex command is used to perform recovery operations, which perform the recovery process by copying all data-related files to the MySQL server DataDir directory. Innobackupex backup-my.cnf to get information about the DataDir directory.

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

Make sure that "innobackupex:completed ok!" appears on the top line of the information above.

When the data is restored to the DataDir directory, it is also necessary to ensure that all data files belong to the owner and the group are the correct users, such as MySQL, otherwise, before starting the mysqld, you need to modify the data file's owner and host groups beforehand. Such as:

# Chown-r mysql:mysql/data/mysql/

6. Use Innobackupex for incremental backup

Each InnoDB page contains an LSN information, and the LSN of the associated page automatically grows whenever the relevant data changes. This is the basis for an incremental backup of the InnoDB table, that is, Innobackupex is implemented by backing up pages that have changed since the last full backup.

To implement the first incremental backup, you can use the following command:

# Innobackupex--incremental/export/increment--incremental-basedir=basedir

Where Basedir refers to the directory where the full backup is located, the Innobackupex command creates a new time-named directory in the/export/increment directory to hold all the incremental backup data after the command execution is completed. Also, when incremental backups are performed after an incremental backup, their--incremental-basedir should point to the same directory as the last incremental backup.

It is important to note that incremental backups can only be applied to InnoDB or xtradb tables, and for MyISAM tables, a full backup is actually performed when performing an incremental backup.

The Prepare incremental backup is a bit different from organizing a full backup, especially to note that:

(1) You need to "replay" a transaction that has already been committed on each backup, including full and individual incremental backups. After replay, all backup data is merged into a full backup.

(2) "Roll Back" uncommitted transactions based on all backups.

As a result, the operation becomes:

# Innobackupex--apply-log--redo-only Base-dir

Then execute:

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

And then the second increment:

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-2

Where Base-dir refers to the directory where the full backup is located, and Incremental-dir-1 refers to the first incremental backup of the directory, incremental-dir-2 refers to the second incremental backup of the directory, and so on, that is, if there are multiple incremental backups, Each time to perform the above operation;

7, recovery data and full backup as long as ready to do it.

The--copy-back option of the Innobackupex command is used to perform recovery operations, which perform the recovery process by copying all data-related files to the MySQL server DataDir directory. Innobackupex backup-my.cnf to get information about the DataDir directory.

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

Make sure that "innobackupex:completed ok!" appears on the top line of the information above.

When the data is restored to the DataDir directory, it is also necessary to ensure that all data files belong to the owner and the group are the correct users, such as MySQL, otherwise, before starting the mysqld, you need to modify the data file's owner and host groups beforehand. Such as:

# Chown-r mysql:mysql/data/mysql/

Here is a script for two backups

Full backup script

fullbackup.sh

#!/bin/bash

#Implement a full backup of the MySQL database

#user: [Email protected]

#

#

Ulimit-n 1048576

Name= '/usr/bin/ls-ln/export/oldbackup/| /usr/bin/tail-n 1 | /usr/bin/awk {print ' $9 '} '

nameu= '/usr/bin/basename/export/oldbackup/$name '

User=encompass

password=aniu7436

Backupdir=/export/backup

num= '/usr/bin/ls-l/export/backup | /usr/bin/wc-l '

/BIN/MYSQL-U$USER-P$PASSWORD-E "flush logs;"

/bin/echo "0" >/tmp/test.yon

/usr/bin/innobackupex--user= $user--password= $password $backupdir &&/bin/echo "1" >/tmp/test.yon && Amp /bin/echo "To complete a full backup at '/bin/date '" >>/tmp/my.txt

If [' Cat/tmp/test.yon '-eq 1] && [$num-eq 3]; Then

Headname= '/usr/bin/ls-ln/export/backup/| /usr/bin/head-n 2 | /bin/grep-| /usr/bin/awk {print ' $9 '} '

/usr/bin/rm-rf/export/backup/$headname

Fi

Incremental backup Script

incrementalbackup.sh

#!/bin/bash

#Realize incremental backup for the MySQL database

#[email protected]

Ulimit-n 1048576

dirnum= '/usr/bin/ls-l/export/backup | /usr/bin/wc-l '

If [' Cat/tmp/test.yon '-eq 0] | | [$dirnum-eq 1]; Then

Exit

Fi

incrementaldir=/export/incremental/

Username=encompass

passwd=aniu7436

Name= '/usr/bin/ls-ln/export/backup/| /usr/bin/tail-n 1 | /usr/bin/awk {print ' $9 '} '

Nameincremental= '/usr/bin/ls-ln/export/incremental/$name | /usr/bin/tail-n 1 | /usr/bin/awk {print ' $9 '} '

Num= '/usr/bin/ls-l/export/incremental/$name | /usr/bin/wc-l '

Count= '/usr/bin/ls-l/export/incremental/| /usr/bin/wc-l '

/BIN/MYSQL-U$USERNAME-P$PASSWD-E "flush logs;"

/bin/echo "0" >/tmp/test.yon

If [$num-lt 2]; Then

/bin/echo "111111111111111111111111111"

/usr/bin/mkdir $incrementaldir $name

/usr/bin/innobackupex--user= $username--password= $passwd--incremental $incrementaldir $name--incremental-basedir= /export/backup/$name &&/bin/echo "1" >/tmp/test.yon &&/bin/echo "to complete a incremental backup at ' Date ' >>/tmp/my.txt

If [$?-ne 0]; Then

names= '/usr/bin/ls-ln/export/incremental/$name | /usr/bin/tail-n 1 | /usr/bin/awk {print ' $9 '} '

/usr/bin/rm-rf/export/incremental/$name/$names &&/bin/echo "1" >/tmp/test.yon

Fi

Else

/bin/echo "222222222222222222222222222"

/usr/bin/innobackupex--user= $username--password= $passwd--incremental $incrementaldir $name--incremental-basedir= $incrementaldir $name/$nameincremental &&/bin/echo "1" >/tmp/test.yon &&/bin/echo "to complete a inc Remental backup at ' date ' >>/tmp/my.txt

If [$?-ne 0]; Then

names= '/usr/bin/ls-ln/export/incremental/$name | /usr/bin/tail-n 1 | /usr/bin/awk {print ' $9 '} '

/usr/bin/rm-rf/export/incremental/$name/$names &&/bin/echo "1" >/tmp/test.yon

Fi

Fi

If [' Cat/tmp/test.yon '-eq 1] && [$count-eq 3]; Then

Headname= '/usr/bin/ls-ln $incrementaldir | /usr/bin/head-n 2 | /bin/grep-| /usr/bin/awk {print ' $9 '} '

/USR/BIN/RM-RF $incrementaldir $headname

Fi

Backup and restore of MySQL based on Xtrbackup