Xtrabackup back up and restore MySQL

Mysqldump Xtrabackup is a data backup tool for InnoDB. It supports online Hot Backup (which does not affect Data Reading and Writing during Backup) and is a good alternative to InnoDB Hotbackup.

Xtrabackup contains two tools:

* Xtrabackup-A Tool for Hot Backup of innodb and xtradb tables. Other tables cannot be backed up.
* Innobackupex-perl scripts encapsulated in xtrabackup provide the myisam Table backup capability.

What can Xtrabackup do?

* InnoDB and XtraDB tables of the entire database are backed up online (hot ).
* Incremental Backup (innodb only) based on the last full-database backup of xtrabackup)
* A backup is generated in the form of a stream and can be saved directly to a remote machine (this is useful when the local hard disk space is insufficient)

How Xtrabackup works

* Xtrabackup-the specific principle remains to be studied...
* Innobackupex full-Database Backup
1. call xtrabackup to back up the innodb tablespace file (this instant image Time1). During the innodb table backup, the database is not locked, external users can continue to increase or decrease data in the database (this can be called Hot Backup ). The changes between Time1 and Time2 are obtained by a thread constantly sweeping innodb log (ChangeSet1 ).
2. Lock all databases.
3. Copy files in frm, MYD, MYI, MRG, TRG, TRN, and opt formats.
4. When data backup in step 3 is completed (Time2), stop the innodb log thread and copy ChangeSet1 data to the backup.
5. Unlock all databases.
6. Stop the suspension and complete the backup.

Important points

* The innobackupex principle shows that it is not a real hot backup. The smaller the MyISAM table, the more favorable it is. Use innodb tables whenever possible to take advantage of Xtrabackup.
* Shut down the mysql service before restoring the backup. After restoring the backup, check whether the data file permission is correct.
* Performance: the total size of a data directory is 5.6 GB, ibdata is 2 GB, the total time is 4 minutes, and the lock table time is 2.5 minutes. If mysqldump is used for the backup lock table of this database, the time is 5-8 times.

Install
Tar zxf xtrabackup-0.7.tar.gz
Cd xtrabackup-0.7
./Configure
Make

At this point, do not use make install. Then MySQL will be installed. The correct method is:

Cd innobase/xtrabackup/
Make
Make install

Then, two tools will be installed in your/usr/bin directory: xtrabackup, innobackupex-1.5.1

# Multiple databases are backed up
Innobackupex-1.5.1 -- user = root -- databases = "innodb innodb2"/bak/

# Compressed backup (without -- databases, all databases by default)
Innobackupex-1.5.1 -- user = root -- stream = tar/bak/| gzip>/bak/bak.tar.gz
(I is required for decompression. Innobackupex: You must use-I (-- ignore-zeros) option for extraction of the tar stream .)

# Remote backup to the/bak directory of 192.168.1.200.
Innobackupex-1.5.1 -- user = root -- stream = tar/bak | ssh root@192.168.1.200 cat ">"/bak/backup.tar
Restore
# Innobackupex-1.5.1 -- apply-log/bak/2009_0929/
# Innobackupex-1.5.1 -- copy-back/bak/2009_0929/
# Chown-R mysql: mysql/usr/local/mysql/data /*
# Mysqladmin-uroot-p123456 shutdown
# Mysqld_safe -- user = mysql &
Only a few tables are exported.