mysql--using Xtrabackup for backup and restore

Installing xtrabackup using RPM packages

# # Install dependent packages Yum Install Perl Perl Perl perl-dbd--ivh libev4-4.15-7.1x86_64.rpm##installation Percona-- IVH percona-xtrabackup--2.4. 4-1. el6.x86_64.rpm

Create a MySQL account for the backup operation.

# # Xtrabackup Backup Create backup user
CREATE USER ' backuper ' @ ' localhost ' identified by ' [email protected] ';
GRANT SELECT, RELOAD, PROCESS, show DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT on *. * to ' Backupe R ' @ ' localhost ';
FLUSH privileges;

Create a backup file directory

mkdir /export/mysql_backup

Make a full backup of the database, and if you are backing up from the library, add the--slave_info–safe-slave-backup parameter to get the Binlog information for copying the main library. The default backup saves the Binlog information for the current library to the Xtrabackup_binlog_info file, and the--slave_info parameter saves the Binlog information for the copied main library to Xtrabackup_slave_info. If you use a backup to add replication, be careful to choose the correct binlog information.

# # Make a full backup and compress Innobackupex--defaults-file="/export/servers/mysql/etc/my.cnf" --host="localhost" --port=3358 --user="Backuper" --password="[email protected]" --socket="/export/data/mysql/tmp/mysql.sock" --stream=Tar "/export/mysql_backup/"|gzip-"/export/mysql_backup/mysql_full_backup.tar.gz"

Note that checking for successful execution of a command, using a tar backup, can effectively reduce the size of the backup file, but compression and compression can seriously consume CPU resources.

============================================================

Assuming that the above operation is done, copy the backup files to the new server and put them under/export/mysql_backup/

Unzip the backup file

/export/mysql_backup/# #加压备份 tar xvf mysql_full_backup. tar. gz

The data obtained by using the Xtrabackup backup is the data at the end point of the backup, and the transaction logs generated during the backup are saved to the backup file Xtrabackup_logfile, so the backup file needs to be re-committed and the uncommitted transaction rolled back. --apply-log also generates new transaction log files based on the backup-my.cnf.

#使用apply the-log parameter to process the transaction log
Innobackupex--apply-log/export/mysql_backup/

Because the target directory is empty at restore time, and even if the newly installed MySQL server will have some data files such as the system database, before restoring the backup to MySQL, the files under the current MySQL data directory need to be changed, and it is recommended to use the MV command to change the name of the file during the insurance period.

#备份当前MySQL的数据目录
MV /export/data/mysql/export/data/mysql_bak

The Xtrbackup backup operation backs up only data-related files, and does not back up files or directories such as error logs/slow logs, while the previous action MV may remove some of the file directories, so it needs to be created manually again.

# # Create MySQL Data directory mkdir -p/export/data/mysql/tmp/export/data/mysql/data/export/data/mysql/dumps/export/data/mysql/log

Using the Move-back parameter to move data to the MySQL data directory, you can also use the--copy-back parameter to copy only without copying.

Innobackupex--defaults-File="/export/servers/mysql/etc/my.cnf" --move-back/ export/mysql_backup/

After the data is copied to the MySQL data Catalog, the owner of the data files needs to be modified to ensure that the MySQL service has permission to manipulate.

#修改MySQL数据目录的权限
Chown -R mysql:mysql/export/data/mysql

Finally start the MySQL service and check that the data is normal

##==================================================##

mysql--using Xtrabackup for backup and restore