Xtrabackup user guide InnoDB data backup tool _ MySQL

Xtrabackup user guide InnoDB data backup tool 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

BASH

Yum install libaio-devel

Compile and install Xtrabackup

BASH

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

BASH
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.

BASH

Mysql-5.5.10/storage/innobase/xtrabackup/xtrabackup_innodb55/usr/bin/xtrabackup_55
Cp 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:

BASH

-- Defaults-file = #
The default path of the configuration file. if the parameter is not set, xtrabackup searches for the configuration file/etc/my from the following locations 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 the global configuration file is read, the specified configuration file will be read again.

-- Target-dir = name
Path of the backup file storage directory

-- Backup
Back up data to target-dir

-- Prepare
Prepare the backup file before recovery (generate InnoDB log file)

-- Print-param
Print the parameters required for backup or recovery

-- Use-memory = #
This parameter is used when prepare is used to control the memory used by the innodb instance during prepare.

-- Suspend-at-end
Generate an xtrabackup_susponded file in the target-dir Directory, suspend the xtrabackup process, and keep synchronizing changes to the backup file until you manually delete the xtrabackup_susponded file.

-- Throttle = #
The number of I/O operations per second, which limits the I/O operations used for backup, minimizing the impact of backup on normal business of the database

-- Log-stream
This parameter is used during backup. the content of xtrabackup_logfile 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 the innobackupex script.

-- Incremental-lsn = name
During incremental backup, only the new ibd pages with the specified value of LSN are copied. The LSN used for 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 used to create a new idb pages

-- Incremental-dir = name
This parameter is used when prepare is used to specify the storage path of the. delta file and log file generated during prepare.

-- Tables = name
Used to back up data files of the file-per-table type. use a regular expression to specify the innodb table to be backed up.

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

B. normal backup mode

A) normal backup (full backup)

BASH

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). 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

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 backup 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 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.

BASH

# Creating 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
# Back up the database table structure
Cp-r/data0/mysql/data/testinnodb // data0/backup/mysql/

Incremental Backup based on this full backup

BASH

# Creating a backup directory
Mkdir-p/data0/backup/mysql/delta
# Creating 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.

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/

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 backup 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 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

BASH

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

Parameter description:

-- Defaults-file
Same as the -- defaults-file parameter of xtrabackup

-- Apply-log
Encapsulation of the -- prepare parameter of xtrabackup

-- Copy-back
Copy the backup data file to the datadir of the MySQL server during data recovery;

-- Remote-host = HOSTNAME
Store the backup data to the process server through ssh;

-- Stream = [tar]

The output format of the backup file. tar4ibd is used for tar. This file can be obtained from 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 compression, the file can be obtained 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

When -- remote-host or -- stream is specified, the temporary directory of transaction logs is stored. the tmpdir directory specified in the MySQL configuration file is used by default.

-- Redo-only -- apply-log Group,

Only redo logs are supported during forced backup and rollback is skipped. This is necessary for incremental backup.

-- Use-memory = #

This parameter is used when prepare is used to control the memory used by the innodb instance during prepare.

-- Throttle = IOS

Same as the -- throttle parameter of xtrabackup

-- Sleep = is used for ibbackup. it specifies the number of milliseconds for stopping copying every 1 MB of data during Backup. it is also used to minimize the impact on normal services during backup, for details, refer to the ibbackup manual;

-- Compress [= LEVEL]
Backup data