Mysql backup tool innobackupex, xtrabackup-2.1 installation and testing _ MySQL

Mysql backup tool innobackupex, xtrabackup-2.1 installation and testing bitsCN.com

Mysql backup tool innobackupex, xtrabackup-2.1 installation and testing

1. Introduction to Xtrabackup

1.1: 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.

1.2: 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.

1.3: 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.

1.4: 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.

2. xtrabackup-2.1.2-611 installation

This article through the source code installation xtrabackup-2.1.2-611

Xtrabackup-2.1.2-611 download link:

Wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.2/binary/Linux/x86_64/percona-xtrabackup-2.1.2-611.tar.gz

Download list of other versions:

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

2.1: install the dependent packages required for compilation

Yum install libaio-devel perl-Time-HiRes curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL. * package-y

The installation procedure is as follows:

1. decompress the package and type the command file directory.

Tar xvf percona-xtrabackup-2.1.2-611.tar.gz

Cd/workspace/percona-xtrabackup-2.1.2/bin/

2. View files

Ll

-Rwxr-xr-x 1 root 110729 May 18 innobackupex

Lrwxrwxrwx 1 root 12 Jun 3 innobackupex-1.5.1-> innobackupex

-Rwxr-xr-x 1 root 2211237 May 18 xbcrypt

-Rwxr-xr-x 1 root 2285672 May 18 xbstream

-Rwxr-xr-x 1 root 13033745 May 18 xtrabackup

-Rwxr-xr-x 1 root 16333506 May 18 xtrabackup_55

-Rwxr-xr-x 1 root 80988093 May 18 xtrabackup_56

3. copy files such as innobackupex and xtrabackup to the/bin and Directory of the mysql program.

Cp */mysql/bin/

4. soft link the files in the mysql installation directory to the/usr/bin/directory. This is more convenient than variables, so deployment and installation are completed !!!!!!!!!!!

Ln-s/mysql/bin/*/usr/bin/

5. verification:

Run the following command and press the tab key to automatically complete the association!

[Root @ slave1 ~] # Innobackupex

Innobackupex-1.5.1 innobackupex

[Root @ slave1 ~] # Xtrabackup

Xtrabackup xtrabackup_55 xtrabackup_56

III. Introduction to innobackupex parameters

Because innobackupex can back up InnoDB and MyISAM engine tables at the same time, we will focus on the backup and recovery use of innobackupex.

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.

3.1.innobackupex syntax and parameter description

Innobackup [-- sleep = MS] [-- compress [= LEVEL] [-- include = REGEXP] [-- user = NAME] [-- password = WORD]

[-- Port = PORT] [-- socket = SOCKET] [-- no-timest a mp] [-- 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