Xtrabackup full backup, incremental backup, restore MySQL database

Xtrabackup User Guide InnoDB Data Backup tool

Xtrabackup is a innodb to do data backup tools, Support Online hot backup ( backup without affecting the data read and write, that is, no lock), is a commercial backup tool InnoDB hotbackup a good alternative

I. Introduction of Xtrabackup

A, what is Xtrabackup?

Xtrabackup is a innodb to do data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative.

Xtrabackup has two main tools: Xtrabackup, Innobackupex

1,Xtrabackup can only back up InnoDB and xtradb two kinds of data tables, but cannot back up MyISAM data table
2, Innobackupex is a reference to the InnoDB hotbackup innoback script modified. Innobackupex is a Perl script package that encapsulates xtrabackup. The main purpose is to back up the tables of the InnoDB and MyISAM engines at the same time, but Innobackupex needs to add a read lock when processing MyISAM . and added some options to use.

If Slave-info can record backup recovery, as slave need some information, according to this information, can easily use Backup to redo slave.

3. Official documents: Http://www.percona.com/docs/wiki/percona-xtrabackup:start

B, what can xtrabackup do

Online (Hot) backup InnoDB, xtradb tables for the entire library
Incremental backup based on Xtrabackup's last backup of the whole library (InnoDB only)
　generate backups as streams that can be saved directly to a remote machine (useful when the hard disk space is low)

The MySQL database itself provides tools that do not support true incremental backups, and binary log recovery is point-in-time (Point-in-time) recovery instead of incremental backups.

The Xtrabackup tool supports incremental backups of the InnoDB storage engine and works as follows:

(1) Complete a full backup first and record the LSN (log Sequence number) of the checkpoint at this point.
(2) When a process incremental backup, compares the LSN of each page in the tablespace to the LSN at the time of the last backup, and if so, backs up the page and logs the LSN of the current checkpoint.

First, the final checkpoint ("last checkpoint LSN") is found and recorded in logfile, and the logfile to Xtrabackup_logfile of InnoDB is started from the position of the LSN; Start copying all data files. ibd; Copy logfile is not stopped until the end of the copy of all data files.

Because the logfile inside records all the data modification situation, so, in the backup process, the data file has been modified, still can be restored by parsing xtrabackup_logfile to keep the data consistent.

C, Xtrabackup Backup principle

Xtrabackup is based on the InnoDB crash-recovery function. It replicates the data file of InnoDB, because it does not lock the table, the copied information is inconsistent, use crash-recovery when recovering, make the data recovery consistent.

InnoDB maintains a redo log, also known as transaction log, the transaction log, which contains all the changes to the InnoDB data. When InnoDB starts, it checks the data file and the transaction log, and it does a two-step operation:

Xtrabackup when backing up, a page by page copy of InnoDB data, and do not lock the table, at the same time, xtrabackup there is another thread monitoring transactions log, once the log changes, the changed log pages copied away. Why the rush to copy away? Because the transactions log file is limited in size, when it is full, it starts from scratch, so the new data may overwrite the old data.

During the prepare process, Xtrabackup uses the copied transactions log to crash recovery the backed up InnoDB data file.

D. Implementation Details

Xtrabackup Open the InnoDB data file in Read-write mode and then copy it. It does not actually modify this file. That is, the user running xtrabackup must have read and write access to the InnoDB data file. The reason for using the read-write mode is because Xtrabackup uses its built-in InnoDB library to open the file, while the InnoDB library opens the file with RW.

Xtrabackup to replicate large amounts of data from the file system, so it uses posix_fadvise () as much as possible to tell the OS not to cache the data it reads to improve performance. Because this data is not reused, the OS is not so smart. If you want to cache, a few g of data, the OS virtual memory will be a great pressure, other processes, such as mysqld is likely to be swap out, so the system will be greatly affected.

In the process of backing up InnoDB page, Xtrabackup reads and writes 1MB of data each time, 1mb/16kb=64 a page. This is not configurable. After reading the 1MB data, xtrabackup the 1MB data one page at a time, using InnoDB's buf_page_is_corrupted () function to check if the data on this page is normal, and if the data is not normal, reread the page and reread it up to 10 times. If it fails, the backup fails and exits. When copying transactions log, read and write 512KB data at a time. The same cannot be configured.

Second, xtrabackup installation

This article installs the Xtrabackup through the source way, the newest version is 1.6.

Xtrabackup is different from the general way of building Code, it adopts the method of building the patch on the MySQL source code. Download the source of the package is relatively large is because the package is packaged in the compilation required two MySQL Source, a 5.1.56, a 5.5.10.

Install dependent packages required for compilation

Bash

Yum Install Libaio Libaio-devel

Compiling and installing Xtrabackup

Bash

Copy the Code code as follows:

wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz
Tar xvf xtrabackup-1.6.tar.gz
CD xtrabackup-1.6
Utils/build.sh innodb55

Note: build.sh is a compilation script provided in the source package, MySQL 5.1 can be used with parameters innodb51_builtin.

After the compilation is successful, a xtrabackup program is generated in the corresponding MySQL version directory, and the path I generate here is mysql-5.5.10/storage/innobase/xtrabackup/

Bash
LS mysql-5.5.10/storage/innobase/xtrabackup/
Makefile xtrabackup.c xtrabackup_innodb55 XTRABACKUP.O

Copy the file and create a soft link to/usr/bin, and this is done because the Innobackupex script needs to be met.

Bash

Copy the Code code as follows:

CP mysql-5.5.10/storage/innobase/xtrabackup/xtrabackup_innodb55/usr/bin/xtrabackup_55
CP Innobackupex/usr/bin/innobackupex
#备份时如果打包就需要这个命令, not required.
CP LIBTAR-1.2.11/LIBTAR/TAR4IBD/USR/BIN/TAR4IBD
Ln-sf/usr/local/webserver/mysql/bin/mysql/usr/bin/mysql

　　Three Xtrabackup Backup and Recovery use

A, xtrabackup Common parameters options are as follows:

Bash

--defaults-file=#

The path to the default profile, and if it does not, Xtrabackup will look for configuration files/etc/my.cnf,/etc/mysql/my.cnf,/USR/LOCAL/ETC/MY.CNF, ~/from the following location in turn. My.cnf and reads the [mysqld] and [xtrabackup] configuration segments in the configuration file.

[mysqld] only need to specify DataDir, Innodb_data_home_dir, Innodb_data_file_path, Innodb_log_group_home_dir, innodb_log_files_in _group, innodb_log_file_size6 parameters can let xtrabackup work normally.

--defaults-extra-file=#
If this parameter is used, the configuration file specified here is read again after the global profile is read

--target-dir=name
Storage directory path for backup files

--backup
Implement Backup to Target-dir

--prepare
Implement preparation for backup files before recovery (generate InnoDB log file)

--print-param
Parameters required to print a backup or restore

--use-memory=#
This parameter is used when prepare, controlling the amount of memory used by InnoDB instances when prepare

--suspend-at-end
Generate a xtrabackup_suspended file in the Target-dir directory, suspend the xtrabackup process, and continuously synchronize changes to the data file to the backup file until the user manually deletes the xtrabackup_suspended file

--throttle=#
Io times per second, limiting the amount of I/O operations used at backup, minimizing the impact of backups on the normal business of the database

--log-stream
This parameter is used at backup time to output the contents of the Xtrabackup_logfile to standard output, which is automatically used when using the Suspend-at-end parameter, which is used by the stream mode of the Innobackupex script.

--incremental-lsn=name
An incremental backup only copies the LSN specified by this parameter to the new IBD pages, which LSN was last backed up to see the previous backup set of the Xtrabackup_checkpoints file

--incremental-basedir=name
This parameter is used at backup time to back up the new IDB pages for the backup set specified in this parameter

--incremental-dir=name
This parameter is used when prepare, specifying prepare when the. delta file and log file are stored

--tables=name
Used when backing up data files of type file-per-table, using regular expressions to specify InnoDB tables that need to be backed up

--datadir=name
The data file directory of the MySQL database.

B, normal backup mode

A) normal backup (full-scale backup)

Bash

Copy the Code code as follows:

Mkdir-p/data0/backup/mysql
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/
Cp-r/data0/mysql/data/testinnodb//data0/backup/mysql/

Note: Xtrabackup only backs up data files and does not back up the data table structure (. frm), so this is a manual backup so that it can be used when the xtrabackup is restored.

Full-scale backup recovery

To prepare for a backup file before it is restored

Bash

xtrabackup_55--defaults-file=/data0/mysql/my.cnf--prepare--target-dir=/data0/backup/mysql/

Copy the corresponding database table structure from the backup directory to the default data directory

Bash

Cp-r/data0/backup/mysql/testinnodb//data0/mysql/data/

Delete the corresponding data files in the default data directory and copy the backed up data files to the default data directory

Bash

rm/data0/mysql/data/*
cp/data0/backup/mysql/ib*/data0/mysql/data/

Modify Data Directory Permissions

Bash

Chown-r Mysql:mysql/data0/mysql/data

Restart MySQL

Bash

/data0/mysql/mysql restart

b) Incremental backup

Incremental backup Benefits:

1, the database is too large not enough space for full backup, make incremental backup effective space saving, and high efficiency.
2, support hot backup. The backup process does not lock the table and is not subject to time constraints and does not affect user use.
3, daily backup only produce a small amount of data, remote backup transfer more convenient. While saving space.
4, backup recovery based on file operations, reduce the risk of direct database operations.
5, the backup efficiency is higher, the recovery efficiency is higher.

This I study n long unsuccessful, the reason is not found yet. I test the environment of the MySQL version is 5.5.11, XTRABACKUP_55 is based on the MySQL5.5.9 source code compiled, do not know and this has a relationship? to be verified! Only the implementation steps are sorted out here.

Incremental backup and simulated recovery steps:

Do a full backup first, incremental backups need to be based on this

Bash

Copy the Code code as follows:

#建立备份目录
Mkdir-p/data0/backup/mysql/base
#建立一个全量备份
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/base
#生成的备份数据文件
ls/data0/backup/mysql/base/
Ibdata1 xtrabackup_checkpoints Xtrabackup_logfile
#备份数据库表结构
Cp-r/data0/mysql/data/testinnodb//data0/backup/mysql/

Incremental backup based on this full-scale backup

Bash

Copy the Code code as follows:

#建立备份目录
Mkdir-p/data0/backup/mysql/delta
#建立一个增量备份
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/delta-- Incremental-basedir=/data0/backup/mysql/base
#生成的备份数据文件
ls/data0/backup/mysql/delta/
Ibdata1.delta xtrabackup_checkpoints Xtrabackup_logfile

Note: In the incremental backup directory, the data files end with a. Delta. Incremental backups only back up the page that was modified after the last full backup, so incremental backups take up less space. Incremental backups can be incremental based on incremental backups. The incremental backup directory needs to be modified every time. For example, the second increment is changed to/data0/backup/mysql/delta2)  

Incremental backup recovery process:  

You first need to do a prepare operation for the full-volume, incremental backup separately. &NBSP

bash 

xtrabackup_55--defaults-file=/data0/mysql/my.cnf--prepare--target-dir=/data0/ backup/mysql/base 
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--prepare--target-dir=/data0/backup /mysql/base--incremental-dir=/data0/backup/mysql/delta/ 

copies the corresponding database table structure from the backup directory to the default data directory  

BASH　　&NBSP

Cp-r/data0/backup/mysql/testinnodb//data0/mysql/data/ 

Delete the corresponding data files in the default data directory and copy the backed up data files to the default data directory  

bash 
rm/data0/backup/mysql/ib* 
cp/data0/ backup/mysql/ib*/data0/mysql/data/ 

Modify Data directory permissions  

bash 
Chown-r mysql:mysql/data0/　mysql/data 

Restart mysql 

bash 
/data0/mysql/mysql restart 

Xtrabackup full backup, incremental backup, restore MySQL database