Leverage the Xtrabackup tool for backup and recovery (MySQL DBA's prerequisite tools) _mysql

Source: Internet
Author: User
Tags dba prepare percona

xtrabackup------MySQL DBA's Prerequisite tools

Attention:

1) document reference Http://www.percona.com/docs/wiki/percona-xtrabackup:start

2 MySQL to use 5.1.50 version or above.

Introduction and installation of Xtrabackup

1, Xtrabackup is a Percona open source project, can be hot backup InnoDB, xtradb, and MyISAM (Will lock the table), can be considered as a free alternative 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 Minutes Minutes Minutes
Named Support Contact Persons 5 10 20
Email, Web, & Chat Support 650) this.width=650; "Height=24> 650) this.width=650; "Height=24> 650) this.width=650; "Height=24>
Phone Support 650) this.width=650; "Height=24> 650) this.width=650; "Height=24> 650) this.width=650; "Height=24>
Login Support 650) this.width=650; "Height=24> 650) this.width=650; "Height=24> 650) this.width=650; "Height=24>
24x7 Support 650) this.width=650; "Height=24> 650) this.width=650; "Height=24> 650) this.width=650; "Height=24>
Fixes for verified Bugs 650) this.width=650; "Height=24> 650) this.width=650; "Height=24>
Hot Bug Fixes 650) this.width=650; "Height=24>
Onsite System Audit Option 650) this.width=650; "Height=24>
Covers old Server versions 650) this.width=650; "Height=24>
Covers non-standard platforms Option
Covers Custom Code Option

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

First look at how to install Xtrabackup, the easiest way to install is to use the RPM package, but want to use the source code installation, its installation is a bit strange, because it uses in the MySQL source code on the way to build the installation of the patch.

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

When you do this, do not make install, then you will install MySQL, the correct way 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, Innobackupex

1 Xtrabackup can only back up InnoDB and xtradb two kinds of data tables, support Online hot backup, can backup the INNODB data table without locking, but this tool can not operate MyISAM engine table

2 Innobackupex is a script package that encapsulates the xtrabackup and can handle both InnoDB and MyISAM, but requires a read lock when processing MyISAM.

As described above, because the operation MyISAM need to read the lock, this will plug the online service write operations, and InnoDB no such restrictions, so the database InnoDB table type of the larger proportion of the more favorable. In practical applications, the Innobackupex method is generally used directly, it has three kinds of operation methods, according to the manual introduction:

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 a hot backup of 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 from the backup directory, index, log to the initial location specified in the My.cnf file.

Xtrabackup can also be used to moving InnoDB tables between servers, and more can refer to official documents and examples.

Reference Links:

1. Official 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

Second, Innobackupex and Xtrabackup backup detailed

Note: Innobackupex will determine the MySQL data location according to the/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/, where the 2>/data/back_data/1.log is to redirect output information from the backup process to 1.log

Innobackupex-1.5.1–slave-info .....

–slave-info will record the replication point of the replication master log, making it easier to replicate again. (used in backup from machine)

Files after backup:

xtrabackup_binlog_info-Store binlog information. (Binlog requires a separate copy of the backup, if necessary binlog)

Xtrabackup_checkpoints-stores the starting and ending positions of the backup.

Recovery:

First, deactivate the database, and then delete all the 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

The default innobackupex-1.5.1 will store the binary log information in the file Xtrabackup_binlog_info (easy to do slave).

Cd/data

Chown-r Mysql:mysql mysql_data/

Restart the 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

Restores:

#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

Preparing to restore

# 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 the MySQL service

3. Compressed (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

The pipeline is used here | To use innobackupex-1.5.1 as the standard input for gzip. Recovery, you only need to use TAR-IZXVF to extract the corresponding files, the operation is completely the same as normal backup.

Restores:

After using TAR–IZXVF to extract the corresponding files, the operation is completely 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 restore:

# 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 the MySQL service

———————————————————————————————————————

Xtrabackup Backup and Recovery

Backup:

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

Recovery:

Need to perform two times Xtrabackup–prepare

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), so when you use Xtrabackup recovery, you must have a corresponding table structure file (. frm).

Incremental backups:

1. Full-scale 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 a directory where incremental backups are made, the data files are terminated with the. Delta. An incremental backup backs up only the page that has been modified since the last full backup, so incremental backups take up only a small amount of space. Incremental backups can be incremental on the basis of incremental backups.

Incremental backup recovery:

We need to do a prepare operation on a full, incremental backup individually.

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/#这一步不是必须的

In this way, the data files under the/data/back_data/can be directly placed in your MySQL data directory, restore the data.

Again, Xtrabackup only backs up the InnoDB data file, the table structure is not backed up, so when you recover, you must have the corresponding table structure file (. frm).

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.innobackupex combined with Xtrabackup

First, Innobackupex full backups:

Innobackupex--user=root/data/back_data/2>/data/back_data/1.log #会生成一个时间文件夹, if this is 2010-10-29_15-57-44

Then, Xtrabackup do an 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:

First deactivate the database, back up the binary log (if any), and then delete all the database files in the database directory.

Cd/data/mysql_data

RM-RF * # Delete all files in the data directory

Restore full-Volume backups:

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 files

To restore an 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.