Xtrabackup installation and application _ MySQL

Xtrabackup installation and application bitsCN.com

Xtrabackup is an open-source project of percona. it supports hot backup of innodb, XtraDB, and MyISAM (locks the table)

Official website http://www.percona.com/docs/wiki/percona-xtrabackup:start

Installation:
Rpm-ivh -- nodephotoshop/target = _ blank class = infotextkey> ps xtrabackup-1.2-22.rhel5.x86_64.rpm
-- Nodephotoshop/target = _ blank class = infotextkey> ps (do not check the dependency between software), because install xtrabackup requires mysql_client, but my installation is through the source code.

Prelude:
Set environment variables correctly
For example, export PATH = $ PATH:/usr/local/mysql/bin. if mysql is not set to PATH, an error is returned.

1. innobackupex-1.5.1 and xtrabackup backup details
========================================================== ========================================================== ==========
The innobackupex-1.5.1 determines the location of MySQL data based on/et/my. cnf.

1. normal backup:
Innobackupex-1.5.1 [-- defaults-file =/etc/my. cnf] -- user = root [-- host = 192.168.1.52] [-- password = xxx] [-- port = 3306]
/Data/back_data/2>/data/back_data/1.log

The backup directory is/data/back_data/
Here 2>/data/back_data/1.log, redirects the output information in the backup process to 1.log

Innobackupex-1.5.1 -- slave-info .....
-- Slave-info records the replication point of the master log to facilitate replication. (Used for backup slave machine)

Backup files:
Xtrabackup_binlog_info -- stores binlog information. (Binlog requires another copy of the backup, if binlog is required)
Xtrabackup_checkpoints -- stores the backup start position and end position.

Recovery:
Stop the database and delete all database files in the Database Directory.
Cd/data/mysql_data
Rm-rf * # Delete all files in the data directory

Innobackupex-1.5.1 -- user = root -- apply-log/data/back_data/2010-10-26_16-09-37 # application log
Innobackupex-1.5.1 -- user = root -- copy-back/data/back_data/2010-10-26_16-09-37

By default, the innobackupex-1.5.1 stores binary log information in the xtrabackup_binlog_info File (to facilitate Slave ).
Cd/data
Chown-R mysql: mysql mysql_data/
Restart mysql service

2. package (Tar) Backup:
Innobackupex-1.5.1 -- user = root [-- password = xxx] -- stream = tar/data/back_data/2/2>/data/back_data/2.log 1>/data/back_data/2.tar

Restore:
Cd/data/back_data/2/
Tar ixvf 2.tar
Root @ mablevi-desktop:/data/back_data/2 # ls
2. tar backup-my.cnf ibdata1 ibdata2 mablevi mysql xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile
Prepare for restoration
Root @ mablevi-desktop :~ # Innobackupex-1.5.1 -- user = xxx [-- password = xxx] -- apply-log/data/back_data/
......
Innobackupex: completed OK!

Delete all files in the data directory
Rm-rf/data/mysql_data /*
Copy:
Root @ mablevi-desktop :~ # Innobackupex-1.5.1 -- user = xxx [-- password = xxx] -- copy-back/data/back_data/
......
Innobackupex: completed OK!

Cd/data
Chown-R mysql: mysql mysql_data/
Restart mysql service

3. tar gzip backup
Innobackupex-1.5.1 -- user = root [-- password = xxx] -- stream = tar
/Data/back_data/2/2>/data/back_data/2.log | gzip>/data/back_data/2.tar.gz
Pipeline is used here | use the innobackupex-1.5.1 as the standard input for gzip. To recover the backup, you only need to use tar-izxvf to decompress the corresponding file and perform the same operations as normal backup.

Restore:
After extracting the corresponding file using tar-izxvf, the operation is exactly the same as normal backup.
Cd/data/back_data/2/
Tar ixvf 2.tar

Root @ mablevi-desktop: // data/back_data/2 # ls
Backup-my.cnf ibdata1 ibdata2 mablevi mysql xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile
Prepare for restoration:
Root @ mablevi-desktop :~ # Innobackupex-1.5.1 -- user = xxx [-- password = xxx] -- apply-log/data/back_data/2/
......
Innobackupex: completed OK!

Delete all files in the data directory
Rm-rf/data/mysql_data /*

Root @ mablevi-desktop:/data/back_data/2 # innobackupex-1.5.1 -- user = xxx [-- password = xxx] -- copy-back/data/back_data/2/

Cd/data
Chown-R mysql: mysql mysql_data/
Restart mysql service

Bytes ---------------------------------------------------------------------------------------------------------------------
Xtrabackup backup and recovery

Backup:
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/data/back_data/

Recovery:
Run xtrabackup -- prepare twice
Xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/data/back_data/
Xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/data/back_data/

Note that xtrabackup only backs up data files and does not back up the data table structure (. frm). Therefore, when using xtrabackup for restoration, you must have a table structure file (. frm ).

Incremental Backup:
1. Full Backup
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/data/back_data/
2. incremental backup
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/data/back_data_inc/-- incremental-basedir =/data/back_data/
In the incremental backup directory, all data files end with. delta. Incremental backup only backs up the page modified after the last full backup. Therefore, incremental backup only uses less space.

Incremental backup can be incremental based on incremental backup.

Incremental backup recovery:
We need to perform prepare operations on both full and incremental backups.
Xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/data/back_data/2010-10-26_16-09-37
Xtrabackup -- prepare -- target-dir =/data/back_data/2010-10-26_16-09-37 -- incremental-dir =/data/back_data_inc
Xtrabackup -- prepare -- target-dir =/data/back_data/# This step is not required.
In this way, data files under/data/back_data/can be directly stored in your MySQL data directory to restore data.
Remind you again that xtrabackup only backs up InnoDB data files, and the table structure is not backed up. Therefore, you must have the corresponding table structure file (. frm) when restoring ).

Rm-rf/data/mysql_data/ib *

Cp-I/data/back_data/2010-10-26_16-09-37/ib */data/mysql_data/

Cd/data
Chown-R mysql: mysql mysql_data/

2. combination of innobackupex-1.5.1 and xtrabackup
========================================================== ========================================================== ========
First, innobackupex-1.5.1 full backup:
Innobackupex-1.5.1 -- user = root/data/back_data/2>/data/back_data/1.log # will generate a time folder, here if it is 2010-10-29_15-57-44
Then, xtrabackup performs incremental backup:
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/data/back_data_inc/4 -- incremental-basedir =/data/back_data/2010-10-29_15-57-44

Recovery:
Stop the database, back up binary logs (if any), and delete all database files in the Database Directory.
Cd/data/mysql_data
Rm-rf * # Delete all files in the data directory

Restore full backup:
Innobackupex-1.5.1 -- user = root -- apply-log/data/back_data/2010-10-29_15-57-44 # application log
Innobackupex-1.5.1 -- user = root -- copy-back/data/back_data/2010-10-29_15-57-44 # copy files
Restore incremental backup:
Xtrabackup -- prepare -- target-dir =/data/back_data/2010-10-29_15-57-44 -- incremental-dir =/data/back_data_inc/5

Cd/data
Chown-R mysql: mysql mysql_data/

Restart the mysql service.

BitsCN.com