Xtrabackup User Guide _ MySQL

Xtrabackup user guide bitsCN.com

1. Introduction to Xtrabackup

A. What is Xtrabackup?

Xtrabackup is a tool used to back up InnoDB data. it supports online hot backup (data read/write is not affected during backup) and is a good alternative to InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex.

1. xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM data tables
2. innobackupex is modified based on the innoback script of InnoDB Hotbackup. innobackupex is a perl script encapsulation and encapsulates xtrabackup. It is mainly used to conveniently back up InnoDB and MyISAM engine tables at the same time, but a read lock is required when processing myisam. Some options are added. For example, slave-info can record the information required by slave after the backup is restored. Based on this information, you can easily use the backup to redo slave.
3. Official Documents: http://www.percona.com/docs/wiki/percona-xtrabackup:start

B. What can Xtrabackup do?

Online (hot) backup of InnoDB and XtraDB tables of the entire database
Incremental Backup (innodb only) based on the last full-database backup of xtrabackup)
Backup is generated in the form of a stream and can be saved directly to a remote machine (useful when the local hard disk space is insufficient)

The tools provided by the MySQL database itself do not support real incremental backup. binary log recovery is point-in-time recovery rather than incremental backup. The Xtrabackup tool supports incremental backup of the InnoDB storage engine. The working principle is as follows:

(1) complete a full backup and record the LSN (Log Sequence Number) of the checkpoint ).
(2) during incremental backup, compare whether the LSN of each page in the tablespace is greater than the LSN of the last backup. If yes, back up the page and record the LSN of the current checkpoint.

First, find and record the last checkpoint ("last checkpoint LSN") in the logfile, and then copy the InnoDB logfile to xtrabackup_logfile starting from the location of the LSN. then, start copying all data files. ibd; stops copying logfiles only after all data files are copied.

Because all the data changes are recorded in the logfile, the data file has been modified during the backup process, and the data can still be consistent by parsing xtrabackup_logfile during restoration.

C. Xtrabackup backup principle

XtraBackup is based on InnoDB's crash-recovery function. It copies the data file of innodb. because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to ensure data recovery is consistent.

InnoDB maintains a redo log, also known as the transaction log, which contains all changes to innodb data. When InnoDB is started, it checks data file and transaction log first, and performs two steps:

During backup, XtraBackup copies innodb data one page at a time without locking the table. at the same time, XtraBackup has another thread that monitors the transactions log. Once the log changes, copy the changed log pages. Why are we in a hurry to copy it? Because the transactions log file is limited in size, it will be written from the beginning after it is fully written, so the new data may overwrite the old data.

During the prepare process, XtraBackup uses the copied transactions log to perform crash recovery ING on the backed-up innodb data file.

D. Implementation details

XtraBackup open the innodb data file in read-write mode, and then copy it. In fact, it will not modify this file. That is to say, the user running XtraBackup must have read and write permissions on the data files of innodb. The read-write mode is used because XtraBackup uses its built-in innodb library to open files, while the innodb Library opens files with rw.

XtraBackup needs to copy a large amount of data from the file system, so it tries its best to use posix_fadvise () to tell the OS not to cache the data read to improve performance. Because the data will not be reused, but the OS is not so smart. If you want to cache a few GB of data, it will put a lot of pressure on the virtual memory of the OS. other processes, such as mysqld, may be pushed out by swap, in this way, the system will be greatly affected.

During innodb page backup, XtraBackup reads and writes 1 MB of data each time, and 1 MB/16 kB = 64 pages. This cannot be configured. After reading 1 MB of data, XtraBackup traverses the 1 MB data page by page. use the buf_page_is_upted () function of innodb to check whether the data on this page is normal. if the data is abnormal, read this page again and read the page again for up to 10 times. if the page still fails, the backup will fail and exit. Each time a transactions log is copied, kB of data is read and written. It cannot be configured.

II. install Xtrabackup

This article uses the source code to install Xtrabackup. The latest version is 1.6.

Xtrabackup is different from the general source code build method. it uses the build method of patching on the MySQL source code. The downloaded Source code package is large because it packages two MySQL sources required for compilation, one 5.1.56 and the other 5.5.10.

Install the dependent packages required for compilation

? View Code BASH

1	yum install　libaio libaio-devel

Compile and install Xtrabackup

? View Code BASH

1234	wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gztar xvf xtrabackup-1.6.tar.gz cd xtrabackup-1.6utils/build.sh innodb55

Note: build. sh is the compilation script provided in the source code package. MySQL 5.1 can use the innodb51_builtin parameter.

After compilation is successful, an xtrabackup program will be generated in the corresponding mysql version Directory. here the generated path is mysql-5.5.10/storage/innobase/xtrabackup/

? View Code BASH

123	ls mysql-5.5.10/storage/innobase/xtrabackup/ Makefile xtrabackup.c xtrabackup_innodb55 xtrabackup.o

Copy the corresponding file and create a soft link to/usr/bin. the reason for doing so is to meet the needs of innobackupex scripts.

? View Code BASH

12345

Cp mysql-5.5.10/storage/innobase/xtrabackup/xtrabackup_innodb55/usr/bin/xtrabackup_55cp innobackupex/usr/bin/innobackupex # This command is not required if the backup is packaged. Cp libtar-1.2.11/libtar/tar4ibd/usr/bin/tar4ibd ln-sf/usr/local/webserver/mysql/bin/mysql/usr/bin/mysql

III. backup and recovery of Xtrabackup

A. common parameters of Xtrabackup are as follows:

? View Code BASH

1234567891011121314151617181920212223242526272829303132333435363738394041424344

-- Defaults-file = # path of the default configuration file. if the parameter is not set, xtrabackup searches for the configuration file/etc/my in sequence. cnf,/etc/mysql/my. cnf,/usr/local/etc/my. cnf ,~ /. My. cnf and read the [mysqld] and [xtrabackup] configuration segments in the configuration file. In [mysqld], you only need to specify datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir, innodb_log_files_in_group, and other parameters to make xtrabackup work normally. -- Defaults-extra-file = # if this parameter is used, after reading the global configuration file, will read the specified configuration file -- target-dir = name backup file storage directory path -- backup implementation backup to target-dir -- prepare implementation of backup file preparation before recovery (generate InnoDB log file) -- print-param: the parameter required for backup or recovery -- use-memory = # this parameter is used in prepare, control the amount of memory used by the innodb instance during prepare-suspend-at-end generates an xtrabackup_suincluded file in the target-dir directory and suspends the xtrabackup process, data File changes are constantly synchronized to the backup file until you manually delete the xtrabackup_suincluded file -- throttle = # IO times per second, limiting the I/O operations used for backup To minimize the impact of backup on the normal business of the database -- log-stream this parameter is used during backup and the xtrabackup_logfile content is output to the standard output, when this parameter is used, the suspend-at-end parameter is automatically used. this parameter is used in stream mode of innobackupex script. -- Incremental-lsn = name when performing incremental backup, only copy the ibd pages with the new value specified by this parameter, the LSN of the previous backup can be used to check the xtrabackup_checkpoints file of the previous backup set -- incremental-basedir = name. this parameter is used during backup, backup is the new idb pages -- incremental-dir = name in the backup set specified by this parameter. this parameter is used in prepare and is generated when prepare is specified. storage path of delta files and log files -- tables = name is used when backing up file-per-table data files, use a regular expression to specify the data file directory of the innodb table -- datadir = nameMySQL database to be backed up.

B. normal backup mode

A) normal backup (full backup)

? View Code BASH

123	mkdir -p /data0/backup/mysqlxtrabackup_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). Therefore, you must back up the data files manually so that xtrabackup can be used for recovery.

Full Backup Recovery

Prepare backup files before recovery

? View Code BASH

1	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

? View Code BASH

1	cp -r /data0/backup/mysql/testinnodb/ /data0/mysql/data/

Delete the corresponding data files in the default data directory and copy the backup data files to the default data directory.

? View Code BASH

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

Modify data directory permissions

? View Code BASH

1	chown -R mysql:mysql /data0/mysql/data

Restart MySQL

? View Code BASH

1	/data0/mysql/mysql restart

B) incremental backup

Incremental Backup advantages:

1. the database is too large and does not have enough space for full backup. incremental backup can effectively save space and improve efficiency.
2. supports hot backup. The table is not locked during the backup process and is not subject to time restrictions.
3. Daily backup only generates a small amount of data, making Remote Backup and transmission easier. Saves space at the same time.
4. backup recovery is based on file operations to reduce the risks of direct database operations.
5. higher backup efficiency and higher recovery efficiency.

I have not studied N for a long time, but the reason is not found yet. The Mysql version in my test environment is 5.5.11, and xtrabackup_55 is compiled based on the source code of MySQL5.5.9. do you have any idea about this? To be verified! Here we will only sort out the implementation steps.

Steps for incremental backup and simulated recovery:

Perform a full backup first. this is the basis for incremental backup.

? View Code BASH

123456789

# Create a backup directory mkdir-p/data0/backup/mysql/base # Create a full backup xtrabackup_55 -- defaults-file =/data0/mysql/my. cnf -- backup -- target-dir =/data0/backup/mysql/base # generated backup data file ls/data0/backup/mysql/base/ibdata1 xtrabackup_checkpoints xtrabackup_logfile # backup database table structure cp -r/data0/mysql/data/testinnodb // data0/backup/mysql/

Incremental Backup based on this full backup

? View Code BASH

1234567

# Create a backup directory mkdir-p/data0/backup/mysql/delta # Create an incremental backup xtrabackup_55 -- defaults-file =/data0/mysql/my. cnf -- backup -- target-dir =/data0/backup/mysql/delta -- incremental-basedir =/data0/backup/mysql/base # generated backup data file ls/data0/backup /mysql/delta/ibdata1.delta xtrabackup_checkpoints xtrabackup_logfile

Note: In the incremental backup directory, all data files end with. delta. Incremental backup only backs up the page that was modified after the last full backup. Therefore, incremental backup only has a few blank spaces. Incremental backup can be incremental based on incremental backup. The incremental backup directory needs to be modified each time. For example, change the second increment to/data0/backup/mysql/delta2)

Incremental Backup Recovery process:

First, you must perform prepare operations on both full and incremental backups.

? View Code BASH

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

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

? View Code BASH

1	cp -r /data0/backup/mysql/testinnodb/ /data0/mysql/data/

Delete the corresponding data files in the default data directory and copy the backup data files to the default data directory.

? View Code BASH

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

Modify data directory permissions

? View Code BASH

1	chown -R mysql:mysql /data0/mysql/data

Restart MySQL

? View Code BASH

1	/data0/mysql/mysql restart

IV. innobackupex backup and recovery

A. Data Backup

Generally, innobackupex is used directly because it can back up InnoDB and MyISAM engine tables at the same time. Note that the datadir parameter in my. cnf must be specified. xtrabackup_55 locates the innodb data file based on it.

Innobackupex syntax

? View Code BASH

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263

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] BACK UP-DIR innobackup -- copy-back [-- defaults-file = MY. CNF] Parameters of BACKUP-DIR description: -- defaults-file: the same as the -- defaults-file parameter of xtrabackup -- apply-log encapsulation of the -- prepare parameter of xtrabackup -- copy-back to copy the backup data file to the MySQL server during data recovery datadir; -- remote-host = HOSTNAME stores the backup data to the process server through ssh; -- stream = [tar] backup file output format; tar4ibd is used for tar, this file can be obtained in the XtarBackup binary file. if -- stream = tar is specified during backup, the Directory of the tar4ibd file must be in $ PATH (because tar4ibd is used for de-compression, in the binary package of XtraBackup ). When you use stream = tar for backup, your xtrabackup_logfile may be temporarily stored in the/tmp directory, if the number of concurrent writes during backup is large, the xtrabackup_logfile may be large (5 GB +) and may be full of your/tmp directory, you can solve this problem by specifying the directory with the -- tmpdir parameter. -- Tmpdir = DIRECTORY: the DIRECTORY where transaction logs are temporarily stored when -- remote-host or -- stream is specified, by default, the temporary directory tmpdir -- redo-only -- apply-log Group specified in the MySQL configuration file is used. only redo is performed when logs are backed up, and rollback is skipped. This is necessary for incremental backup. -- Use-memory = # this parameter is used in prepare, control the memory used by the innodb instance during prepare -- throttle = the -- throttle parameter -- sleep = of IOS and xtrabackup is used for ibbackup. it specifies the number of milliseconds to stop copying each 1 MB of data, to minimize the impact on normal services during backup, you can view the ibbackup Manual. -- compress [= LEVEL] compresses the volume of backup data and only supports ibbackup, xtrabackup is not implemented yet; -- include = REGEXP encapsulates the xtrabackup parameter -- tables, and ibbackup is also supported. Back up the database tables, for example, -- include = "test. *", which means to back up all the tables in the test database. If full backup is required, this parameter is omitted. if two tables test1 and test2 under the test database need to be backed up, they are written as -- include = "test. test1 | test. test2 ". You can also use wildcards, such as -- include = "test. test *". -- Databases = LIST lists the databases to be backed up. if this parameter is not specified, all databases containing MyISAM and InnoDB tables will be backed up. -- uncompress unzips the backup data file and supports ibbackup, xtrabackup has not yet implemented this function; -- slave-info, backup slave database, and an xtrabackup_slave_info file will be generated under the -- slave-info backup directory. the main log file and offset will be saved here, the file content is similar TO: change master to MASTER_LOG_FILE = '', MASTER_LOG_POS = 0 -- socket = socket to specify mysql. the location of the sock so that the backup process can log on to mysql.

Create a directory required for backup before backup

? View Code BASH

1	mkdir -p /data0/backup/mysql

1. normal backup

? View Code BASH

1	innobackupex --database="testinnodb test" --user=root --no-lock --defaults-file=/data0/mysql/my.cnf /data0/backup/mysql 2>/tmp/mysqlbackup.log

Note: Use the root user of mysql to back up the databases testinnodb and test. the backup path is/data0/backup/mysql /. After the backup is successful, the backup directory with the timestamp name will be created under this directory. The no-lock parameter is used to specify that the table is not locked during backup. 2>/tmp/mysqlbackup. log stores the output information during the backup process to/tmp/mysqlbackup. log.

2. package backup

? View Code BASH

1	innobackupex --database="testinnodb test" --user=root --defaults-file=/data0/mysql/my.cnf --no-lock --stream=tar /data0/backup/mysql/ 2>/tmp/innobackup.log 1>/data0/backup/mysql/mike.tar

Note: This backup takes a long time, mainly for compression operations. In this way, the package needs to be decompressed using tar izxvf. Parameter-I must be added.

3. compression and backup

? View Code BASH

1	innobackupex --database="testinnodb test" --user=root --defaults-file=/data0/mysql/my.cnf --no-lock --stream=tar /data0/backup/mysql/ 2>/tmp/innobackup.log|gzip>/data0/backup/mysql/mike.tar.gz

4. back up data to a remote server
　　
Back up data under the current machine to the/data0/directory of 192.168.1.101.

? View Code BASH

1	innobackupex --database="testinnodb test" --user=root --defaults-file=/data0/mysql/my.cnf --no-lock --stream=tar /data0/backup/mysql/|ssh root@192.168.1.101 cat ">"/data0/backup.tar

B. Data recovery

Taking the compressed backup data as an example, the recovery method for normal backup and packaged backup is similar to this, so we will not give an example.

Unpackage backup data

? View Code BASH

1	tar xvzfi mike.tar.gz

Directory of the xtrabackup_logfile required for backup recovery

? View Code BASH

1	innobackupex --apply-log --user=root --defaults-file=/data0/mysql/my.cnf --no-lock /data0/backup/mysql

Copy data, indexes, and logs from the backup directory to the specified initial data directory location in the my. cnf file.

? View Code BASH

1	innobackupex --copy-back --user=root --defaults-file=/data0/mysql/my.cnf --no-lock /data0/backup/mysql

Modify data directory permissions

? View Code BASH

1	chown -R mysql:mysql /data0/mysql/data

Restart MySQL

? View Code BASH

1	/data0/mysql/mysql restart

V. References

Http://www.google.com
Http://www.linuxidc.com/Linux/2011-05/35410.htm
Http://blogold.chinaunix.net/u4/122567/showart_2537465.html

BitsCN.com