Use the Xtrabackup tool to back up and restore (a required tool for MySQL DBA)

Xtrabackup------ Necessary tools for MySQL DBA

Note:

1) Documents Reference http://www.percona.com/docs/wiki/percona-xtrabackup:start

2) mysql version 5.1.50 or later.

1. Introduction and installation of Xtrabackup

1. Xtrabackup is an open-source project of percona. It supports hot backup of innodb, XtraDB, and MyISAM (locks tables). It can be seen as a free alternative to InnoDB Hotbackup.

Percona Support for MySQL

Benefit	Silver (Per Server)	Gold (Unlimited)	Platinum (Unlimited)
Annual Price	$1,500/server	From $15,000.	From $30,000.
Number of Servers Covered	Per-Server	Unlimited	Unlimited
Number of Support Incidents	Unlimited	Unlimited	Unlimited
Response Time SLA	60 Minutes	30 Minutes	30 Minutes
Named Support Contact Persons	5	10	20
Email, Web, & Chat Support
Phone Support
Login Support
24 × 7 Support
Fixes For Verified Bugs
Hot Bug Fixes
Onsite System Audit		Option
Covers Old Server Versions
Covers Non-Standard Platforms			Option
Covers Custom Code			Option

Reference: http://www.percona.com/mysql-support/

First, let's take a look at how to install Xtrabackup. The simplest installation method is to use the RPM package. However, if you want to use the source code for installation, the installation method is a bit odd, it is installed by patching the MySQL source code.

2. installation:

Wget http://www.percona.com/downloads/XtraBackup/XtraBackup-1.4/Linux/binary/i686/

Tar zxf xtrabackup-1.4.tar.gz

Cd xtrabackup-1.4

./Configure

Make

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

Cd innobase/xtrabackup/

Make

Make install

Installation Reference: http://www.percona.com/docs/wiki/percona-xtrabackup:installation:from-source

3. In this way, two useful tools will be installed in the/usr/bin directory: xtrabackup and innobackupex.

1) xtrabackup can only back up InnoDB and XtraDB data tables. It supports online hot backup and can back up Innodb data tables without locking. However, this tool cannot operate Myisam engine tables.

2) innobackupex is a script encapsulation that encapsulates xtrabackup and can simultaneously process Innodb and Myisam, but a read lock is required when processing Myisam.

As described above, because the read lock is required for Myisam operations, this will block write operations for online services, and Innodb does not have such restrictions, so the proportion of Innodb table types in the database is greater, it is more advantageous. In actual application, the innobackupex method is generally used directly. There are three main operation methods, as described in the Manual:

Usage:

Innobackup [-- sleep = MS] [-- compress [= LEVEL] [-- include = REGEXP] [-- user = NAME]

[-- Password = WORD] [-- port = PORT] [-- socket = SOCKET] [-- no-timestamp]

[-- Ibbackup = IBBACKUP-BINARY] [-- slave-info] [-- stream = tar]

[-- Defaults-file = MY. CNF]

[-- Databases = LIST] [-- remote-host = HOSTNAME] BACKUP-ROOT-DIR

Innobackup -- apply-log [-- use-memory = MB] [-- uncompress] [-- defaults-file = MY. CNF]

[-- Ibbackup = IBBACKUP-BINARY] BACKUP-DIR

Innobackup -- copy-back [-- defaults-file = MY. CNF] BACKUP-DIR

----------------------------------

The first command line is to hot back up the mysql database.

The command with the -- apply-log option is to start the mysql service on a backup.

Commands with the -- copy-back option copy data, indexes, and logs from the backup directory to the specified initial location in the my. cnf file.

Xtrabackup can also be used to moving InnoDB tables between servers. For more information, see the official documentation and examples.

Reference link:

1. Documents: http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual

2. Xtrabackup online backup for InnoDB/XTraDB (pdf ):

Http://www.percona.com/ppc2009/PPC2009_xtrabackup.pdf

Ii. innobackupex and xtrabackup backup details

Note: innobackupex determines the Data Location of MySQL based on/et/my. cnf.

1. Normal backup:

Innobackupex [-- 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.logis used to redirect 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

# Ls

2. tar backup-my.cnf ibdata1 ibdata2 mablevi mysql xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile

Prepare for restoration

# 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:

# 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

# Ls

Backup-my.cnf ibdata1 ibdata2 mablevi mysql xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile

Prepare for restoration:

# 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 /*

# 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

---------------------------------------

Xtrabackup backup and recovery

Backup:

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

Recovery:

Xtrabackup-prepare needs to be executed 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 and xtrabackup

First, innobackupex full backup:

Innobackupex -- user = root/data/back_data/2>/data/back_data/1.log # A time folder is generated. Assume 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 -- user = root -- apply-log/data/back_data/2010-10-29_15-57-44 # Application log

Innobackupex -- user = root -- copy-back/data/back_data/2010-10-29_15-57-44 # copy an object

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.