Mysql xtrabackup backup and recovery implementation sharing

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases.

Brief introduction

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases. Features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) Fast reduction speed.

There are two tools in Xtrabackup:

* xtrabackup-A tool for hot backup of innodb and xtradb tables. Other tables cannot be backed up.

* innobackupex-A perl script encapsulated in xtrabackup that provides the ability to back up MyISAM tables. (can back up the whole database and data table).

When using innobakupex backup, it calls xtrabackup to back up all InnoDB tables and copies all related files (.frm) about the table structure definition, as well as MyISAM, MERGE, CSV and ARCHIVE tables

Files related to triggers and database configuration information are also backed up. These files are saved to a directory with time commands.

While backing up, innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (if it is already prepared status), and LSN (log serial number) range information; each InnoDB page (usually 16k size) contains a log serial number, that is, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

(2) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb-the current position of the binary log file and the binary log file used for the InnoDB or XtraDB table.

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

(5) backup-my.cnf-configuration option information used for backup commands

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named by time; the innobackupex command will create a BACKUP-DIR directory to store backup data.

Binary version

Http://www.percona.com/downloads/XtraBackup/

Wget http://www.percona.com/downloads... Up-2.0.3-470.tar.gz

Tar zxvf percona-xtrabackup-2.0.3-470.tar.gz

Cd percona-xtrabackup-2.0.3/bin

Cp * / usr/bin/

This time, mysql5.5.28 is installed and not described here.

Create a directory for data backup

Mkdir-p / opt/mysql_bak/

Modify the database configuration file:

Vim / etc/my.cnf

Datadir = / home/mysql/data/

Mysqladmin-uroot password 123456

Full library backup:

Innobackupex-user=root-password=123456-defaults-file=/etc/my.cnf / opt/mysql_bak/

Separate backup:

Innobackupex-user=root-password=123456-defaults-file=/etc/my.cnf-database=test / opt/mysql_bak

Back up and package compression:

Innobackupex-user=root-password=123456-defaults-file=/etc/my.cnf-database=test-stream=tar / opt/mysql_bak/ | gzip > / opt/mysql_bak/testdb.tar.gz

With time stamp

Innobackupex-- user=root-- password=123456-- defaults-file=/etc/my.cnf-- database=test-- stream=tar / opt/mysql_bak/ | gzip > / opt/mysql_bak/ `date +% F` _ testdb.tar.gz