MySQL -- use xtrabackup for backup and restoration, and mysql -- xtrabackup

MySQL -- use xtrabackup for backup and restoration, and mysql -- xtrabackup

Use the rpm package to install xtrabackup

# Install dependency package yum-y install perl-devel libaio-devel perl-Time-HiRes perl-DBD-MySQL rsync # install libev4 package rpm-ivh libev4-4.15-7.1.x86_64.rpm # install percona-xtrabackuprpm-ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

 

Create a MySQL account for the backup operation.

# Xtrabackup backup creation user
Create user 'backuper' @ 'localhost' identified by 'backup @ 8080 ';
Grant select, RELOAD, PROCESS, show databases, SUPER, lock tables, replication client, show view, event on *. * TO 'backuper' @ 'localhost ';
Flush privileges;

 

Create backup file directory

mkdir /export/mysql_backup

 

Complete database backup. If you back up the database from the slave database, add the -- slave_info-safe-slave-backup parameter to obtain the binlog information of the replication master database. By default, the backup will save the binlog information of the current database to the xtrabackup_binlog_info file, and the -- slave_info parameter will save the binlog information of the replication master database to xtrabackup_slave_info. If backup is used to add replication, select the correct binlog information.

# Complete backup and compress innobackupex -- defaults-file = "/export/servers/mysql/etc/my. cnf "\ -- host =" localhost "\ -- port = 3358 \ -- user =" backuper "\ -- password =" backup @ 123 "\ -- socket ="/export/data/ mysql/tmp/mysql. sock "\ -- stream = tar \"/export/mysql_backup/"| gzip->"/export/mysql_backup/mysql_full_backup.tar.gz"

Check whether the command is successfully executed. Using tar backup can effectively reduce the size of the backup file, but compression and compression will seriously consume cpu resources.

 

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

Assume that you have copied the backup file to the new server and put it under/export/mysql_backup /.

Decompress the backup file

# Switch to the directory where the backup files are compressed # It is strongly recommended to create an empty directory to store the backup files, to avoid conflicts between decompressed files and other files, cd/export/mysql_backup/# Pressure backup tar xvf mysql_full_backup.tar.gz

 

The data backed up by xtrabackup is the data at the backup end time. The transaction logs generated during the backup are saved to the xtrabackup_logfile backup file, therefore, log redo and rollback of uncommitted transactions must be performed on the backup file first. -- Apply-log also generates new transaction log files based on the backup-my.cnf.

# Use the apply-log parameter to process transaction logs
Innobackupex -- apply-log/export/mysql_backup/

 

Because the target directory is required to be empty during restoration, even if the newly installed MySQL server has some data files such as the system database, Before restoring the backup to MySQL, we recommend that you change the name of the file in the current MySQL data directory before using the mv command.

# Back up the current MySQL DATA DIRECTORY
Mv/export/data/mysql/export/data/mysql_bak

 

Xtrbackup only backs up data-related files and does not back up files or directories such as error logs or slow logs. Some file directories may be removed from the mv operation in the previous step, therefore, you need to manually create it again.

# Create the MySQL data DIRECTORY mkdir-p/export/data/mysql/tmp/export/data/mysql/data/export/data/mysql/dumps/export/data/mysql/ log

 

Use the move-back parameter to move data to the MySQL data directory. You can also use the -- copy-back parameter to copy data only without copying data.

innobackupex --defaults-file="/export/servers/mysql/etc/my.cnf" --move-back /export/mysql_backup/

 

After the data is copied to the MySQL data directory, you must modify the owner of these data files to ensure that the MySQL service has the permission to manipulate them.

# Modifying MySQL Data Directory Permissions
Chown-R mysql: mysql/export/data/mysql

 

Finally, start the MySQL service and check whether the data is normal.

 

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