How to install and back up data in Linux Xtrabackup

Xtrabackup has two main tools: Xtrabackup, Innobackupex

(1) Xtrabackup can only back up InnoDB and xtradb two types of data tables, not backup MyISAM datasheet
(2) Innobackupex encapsulates the Xtrabackup, is a script encapsulation, so it can back up the processing InnoDB and MyISAM at the same time, but need to add a read lock when processing MyISAM
(3) Xtrabackup 2.1.x version of the support MySQL 5.5 and 5.6 servers,xtrabackup 2.0.x version of the support of MySQL 5.0, 5.1, 5.5 and 5.6 servers
(4) Use Help: Http://www.percona.com/docs/wiki/percona-xtrabackup:start

Environment: CentOS Release 6.4 percona-xtrabackup-2.0.8-587.tar.gz

1, download

Official website: http://www.percona.com
Download Address: http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.8/binary/Linux/x86_64/ Percona-xtrabackup-2.0.8-587.tar.gz

2, confirm the server has been installed MySQL

# MySQL--version
MySQL Ver 14.14 distrib 5.1.71, for Redhat-linux-gnu (x86_64) using ReadLine 5.1

3. Installation Xtrabackup

Tar zxf percona-xtrabackup-2.0.8-587.tar.gz
CD percona-xtrabackup-2.0.8
CP Bin/innobackupex/usr/bin
CP bin/xtrabackup*/usr/bin

4, Backup and packaging compression

Innobackupex--user=test--password=testtest--defaults-file=/etc/my.cnf--database=test123--stream=tar/data/ Backup/2>/data/backup/test123.log |gzip 1>/data/backup/test123.tar.gz

Description

--database=test123 A separate backup of the test123 database, if you do not add this parameter then that is to do a full library backup
2>/data/backup/test123.log output information to the log
1>/DATA/BACKUP/TEST123.TAR.GZ package compression is stored in the file

Here you can write a script to do the backup (dbbakcup.sh)

#!/bin/bash
###
Dbdirs=/data/backup
Dbname=test123
dblog= $dbname-$ (date +%y%m%d%h%m). log
dbbak= $dbname-$ (date +%y%m%d%h%m). tar.gz
echo $ (date +%f%t%t) ' Start data backup ... '
Innobackupex--user=test--password=testtest--defaults-file=/etc/my.cnf--database= $dbname--stream=tar $dbdirs 2 > $dbdirs/$dblog |gzip 1> $dbdirs/$dbbak
echo $ (date +%f%t%t) ' Data backup ended ... '

5. Recover data

1 Stop the Database first: service mysqld stop
2 decompression TAR-IZXVF test123.tar.gz-c/data/backup/db/(no db, need mkdir/data/backup/db/)
Note: Here the TAR solution must use the-I parameter, otherwise the extracted file has only one backup-my.cnf

3) Recovery

Innobackupex--user=root--password--defaults-file=/etc/my.cnf--apply-log/data/backup/db/(- The command for the Apply-log option is to apply the log in the backup file to the data file in the backup file.
Rsync-avz Baidu ib*/var/lib/mysql/(copy the Baidu directory to the/var/lib/mysql directory)
Chown-r mysql.mysql/var/lib/mysql/(authorized MySQL user)

4 Restart database service mysqld restart
5) Delete garbage cd/var/lib/mysql/&& rm xtrabackup*

Test InnoDB


1. Full Backup test


The full backup test verifies data backup and recovery primarily by testing the following scenarios.


1) Back up the whole library and restore for testing.


Perform a full-library backup:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/*


Recovery log File:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


After verification, the backup was restored correctly.


2 Modify the table structure, back up the whole library, restore the test.


To modify the database table structure:


MYSQL-UROOT-S/TMP/MYSQL.SOCK-E ' ALTER TABLE backup_test.test add name varchar default ' null '; '


Perform a full-library backup:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/*


Recovery log File:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


After verification, the backup was restored correctly.


3 Backup A library, restore the test


Backup Backup_test Library


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--user=backup backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


To modify the current data file:


Mv/home/q/percona-server/data/home/q/percona-server/databak


Mkdir/home/q/percona-server/data


Recovery log File:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--database=backup_test--user=backup Backup/<date_time>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--database=backup_test--user=backup Backup/<date_time>


cp/home/q/percona-server/databak/*/home/q/percona-server/data (copy only files that are not in data)


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


It is verified that there is no problem backing up a single library. The disadvantage is that when restoring, the data directory must be empty. That is, in the recovery phase, you need to rename the existing data directory to a temporary directory, and after the recovery, copy the contents of other libraries under the original Data directory (now temporary directory) to the data directory.


4 Backup A single table, restore the test


Backup Backup_test Library


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test--user=backup backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


To modify the current data file:


Mv/home/q/percona-server/data/home/q/percona-server/databak


Mkdir/home/q/percona-server/data


Recovery log File:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


It is verified that there is no problem backing up a single table. The disadvantage is that when restoring, the data directory must be empty. That is, in the recovery phase, you need to rename the existing data directory to a temporary directory, and after the recovery, copy the contents of other libraries under the original Data directory (now temporary directory) to the data directory.


5) innodb_file_per_table parameter verification


Modify configuration file


Add innodb_file_per_table configuration parameters to the configuration file.


Restart database server


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Create a test library backup_test


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE database backup_test; '


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE TABLE backup_test.test (id int); '


Mysql-uroot-s/tmp/mysql.sock-e ' INSERT into backup_test.test values (1), (2), (3), (4), (5); '


Backup Backup_test Library


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test--user=backup backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


To modify the current data file:


Mv/home/q/percona-server/data/home/q/percona-server/databak


Mkdir/home/q/percona-server/data


Recovery log File:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


It is verified that there is no problem backing up a single library. The disadvantage is that when restoring, the data directory must be empty. That is, in the recovery phase, you need to rename the existing data directory to a temporary directory, and after the recovery, copy the contents of other libraries under the original Data directory (now temporary directory) to the data directory.


2. Incremental backup Test


The incremental backup test verifies data backup and recovery primarily by testing the following scenarios.


1 full-Library backup, create database, incremental backup, restore test


Full-Library Backup


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup backup/


Creating a Database


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE database backup_test_tmp; '


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE TABLE backup_test_tmp.test (id int); '


Mysql-uroot-s/tmp/mysql.sock-e ' INSERT into backup_test_tmp.test values (1), (2), (3), (4), (5); '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--incremental--incremental-basedir= Backup/<date_time> backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/*


Restore full backup LOG files:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


To restore an incremental backup log file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>- -incremental-dir=backup/<date_time_inc>


To restore an incremental backup data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test_tmp.test; '


Test results:


After verification, the backup was restored correctly.


2 full-database backup, modify datasheet structure, incremental backup, restore test


Full-Library Backup


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup backup/


Modifying a datasheet structure


MYSQL-UROOT-S/TMP/MYSQL.SOCK-E ' ALTER TABLE backup_test.test add email varchar default "null"; '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--incremental--incremental-basedir= Backup/<date_time> backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/*


Restore full backup LOG files:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


To restore an incremental backup log file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>- -incremental-dir=backup/<date_time_inc>


To restore an incremental backup data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


The incremental backup is verified to be fine. The disadvantage is that, at the time of recovery, the data directory must be empty first, and secondly, the table structure of the incremental backup needs to be copied separately when the restore takes place. That is, in the recovery phase, the data directory needs to be emptied, and after the data is recovered, a separate copy of the table structure under the Incremental backup folder is required.


3 Full database backup, create database, incremental backup, modify table structure, incremental backup, restore test


Full-Library Backup


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup backup/


Creating a Database


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE database backup_test_tmp; '


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE TABLE backup_test_tmp.test (id int); '


Mysql-uroot-s/tmp/mysql.sock-e ' INSERT into backup_test_tmp.test values (1), (2), (3), (4), (5); '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--incremental--incremental-basedir= Backup/<date_time> backup/


Modifying a datasheet structure


MYSQL-UROOT-S/TMP/MYSQL.SOCK-E ' ALTER TABLE backup_test.test add email varchar default "null"; '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--incremental--incremental-basedir= Backup/<date_time> backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/*


Restore full backup LOG files:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>


Restore incremental Backup 1st log file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>- -incremental-dir=backup/<date_time_inc1>


Restore incremental Backup 2nd log file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--apply-log--user=backup backup/<DATE_TIME>- -incremental-dir=backup/<date_time_inc2>


To restore an incremental backup data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--copy-back--user=backup backup/<DATE_TIME>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


The incremental backup is verified to be fine. The disadvantage is that, at the time of recovery, the data directory must be empty first, and secondly, the table structure of the last incremental backup needs to be copied separately. That is, in the recovery phase, the data directory needs to be emptied, and after the data is recovered, the table structure under the last incremental backup folder needs to be copied separately.


4 full backup of a library, modify the library, incremental backup, recovery test


Fully back up a library


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--database=backup_test backup/


Modify Library


MYSQL-UROOT-S/TMP/MYSQL.SOCK-E ' ALTER TABLE backup_test.test add address varchar default ' null '; '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--database=backup_test--incremental- -incremental-basedir=backup/<date_time> backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/backup_test/*


Restore full backup LOG files:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--apply-log--user=backup backup/<DATE_TIME>


To restore an incremental backup log file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--apply-log--user=backup backup/<DATE_TIME>--incremental-dir=backup/<date_time_inc>


To restore an incremental backup data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--copy-back--user=backup Backup/<date_time>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


The incremental backup is verified to be fine. The disadvantage is that, at the time of recovery, the data directory must be empty first, and secondly, the table structure of the incremental backup needs to be copied separately when the restore takes place. That is, in the recovery phase, you need to rename the existing data directory to a temporary directory, and after the recovery, copy the contents of other libraries under the original Data directory (now temporary directory) to the data directory. After data recovery, you also need to copy the table structure under the incremental backup folder separately.


5) innodb_file_per_table parameter verification


Modify configuration file


Add innodb_file_per_table configuration parameters to the configuration file.


Restart database server


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Create a test library backup_test


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE database backup_test; '


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE TABLE backup_test.test (id int); '


Mysql-uroot-s/tmp/mysql.sock-e ' INSERT into backup_test.test values (1), (2), (3), (4), (5); '


Backup Backup_test Library


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test--user=backup backup/


Modify Library


MYSQL-UROOT-S/TMP/MYSQL.SOCK-E ' ALTER TABLE backup_test.test add phone varchar default ' null '; '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--database=backup_test--incremental- -incremental-basedir=backup/<date_time> backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/backup_test/*


Restore full backup LOG files:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--apply-log--user=backup backup/<DATE_TIME>


To restore an incremental backup log file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--apply-log--user=backup backup/<DATE_TIME>--incremental-dir=backup/<date_time_inc>


To restore an incremental backup data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test--copy-back--user=backup Backup/<date_time>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test; '





Test results:


The incremental backup is verified to be fine. The disadvantage is that, at the time of recovery, the data directory must be empty first, and secondly, the table structure of the incremental backup needs to be copied separately when the restore takes place. That is, in the recovery phase, you need to rename the existing data directory to a temporary directory, and after the recovery, copy the contents of other libraries under the original Data directory (now temporary directory) to the data directory. After data recovery, you also need to copy the table structure under the incremental backup folder separately.


3, differential backup test


A differential backup is actually an incremental backup operation, which is included in the incremental backup test and is no longer repeated for testing.





Test MyISAM


1. Full Backup test


Create MyISAM Datasheet


Mysql-uroot-s/tmp/mysql.sock-e ' CREATE TABLE Backup_test.test_myisam (id int) Engine=myisam; '


Mysql-uroot-s/tmp/mysql.sock-e ' INSERT into Backup_test.test_myisam values (1), (2), (3), (4), (5); '


Full Backup data table


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--database=backup_test.test_myisam backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/backup_test/test_myisam*


Recovery log File:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test_myisam--apply-log-- User=backup backup/<DATE_TIME>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test_myisam--copy-back-- User=backup backup/<date_time>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test_myisam; '





Test results:


After verification, the backup was restored correctly.


2. Incremental backup Test


Full Backup data table


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--database=backup_test.test_myisam backup/


Modifying a datasheet structure


MYSQL-UROOT-S/TMP/MYSQL.SOCK-E ' ALTER TABLE BACKUP_TEST.TEST_MYISAM add name varchar default ' null '; '


Incremental backups


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--user=backup--database=backup_test.test_myisam-- Incremental--incremental-basedir=backup/<date_time> backup/


To turn off the database service:


Kill-9 $ (ps-ef|grep "MySQL" |gawk ' $!~/grep/{print $} ' |tr-s ' n ')


Delete the current data file:


rm-rf/home/q/percona-server/data/backup_test/test_myisam*


Restore full backup LOG files:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test_myisam--apply-log-- User=backup backup/<DATE_TIME>


Restore incremental BACKUP log files


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test_myisam--apply-log-- User=backup backup/<DATE_TIME>--incremental-dir=backup/<date_time_inc>


To recover a data file:


Innobackupex--defaults-file=/home/q/percona-server/etc/my.cnf--database=backup_test.test_myisam--copy-back-- User=backup backup/<date_time>


Data validation:


Mysqld_safe--DEFAULTS-FILE=/HOME/Q/PERCONA-SERVER/ETC/MY.CNF &


Mysql-uroot-s/tmp/mysql.sock-e ' select * from Backup_test.test_myisam; '





Test results:


After verification, the backup was restored correctly.


3, differential backup test


A differential backup is actually an incremental backup operation, which is included in the incremental backup test and is no longer repeated for testing.





Test summary


Set up the above scenarios, tested and found that the Xtrabackup Backup recovery tool still has some potential problems, but these problems can be circumvented or improve the script to improve and avoid the problem.

how Xtrabackup works.

* Xtrabackup-specific principles to be studied ...
* Innobackupex Full Library Backup

1. Call Xtrabackup the InnoDB tablespace file (this instant image Time1) backup, and in this InnoDB table backup period the database is unlocked, the external can continue to add or subtract data to the library (this can be called hot backup). The changes between Time1 and Time2 are constantly swept InnoDB log by a thread (CHANGESET1).

2. Lock all libraries.

3. Backup frm,myd,myi,mrg,trg,trn,opt format files in direct copy mode.

4. When the data in step 3 is backed up (Time2), stop the thread that sweeps InnoDB log and copy the CHANGESET1 data to the backup.

5. Unlock all libraries.

6. Terminate suspend, backup completed.

Note the main points

* According to the principle of innobackupex that it is not a real hot backup, MyISAM the less the smaller the more advantageous. To use the benefits of Xtrabackup, use the InnoDB table as much as possible.

* Shut down the MySQL service before restoring the backup, and verify that the data file permissions are correct after restoring the backup.

* Performance: Back up a data directory total size of 5.6G, which Ibdata 2G, total time 4 minutes, lock table time 2.5 minutes. If you use mysqldump to do this library backup lock table time is 5-8 times.