In MySQL, innobackupex and xtrabackup are used for big data backup and restoration. innobackupex

In MySQL, innobackupex and xtrabackup are used for big data backup and restoration. innobackupex

Backup and restoration of large data volumes is always a challenge. When MYSQL exceeds 10 Gb, it is slow to export data using mysqldump. Xtrabackup is recommended here. This tool is much faster than mysqldump.

1. Introduction to Xtrabackup

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.

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

Because innobackupex supports innodb and myisam, this article describes how to use innobackupex.

2. Install xtrabackup

1,

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

2. Installation

Select different versions as needed. I chose the rpm installation package. If the following error is reported:
Copy codeThe Code is as follows:
[Root @ localhost xtrabackup] # rpm-ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm
Warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Error: Failed dependencies:
Perl (Time: HiRes) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64

Solution:

Copy codeThe Code is as follows:

[Root @ localhost xtrabackup] # yum-y install perl-devel libaio-devel perl-Time-HiRes perl-DBD-MySQL // install the dependency package
[Root @ localhost xtrabackup] # rpm-ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm // reinstall
Warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ######################################## ### [100%]
1: percona-xtrabackup ##################################### ###### [100%]

Note:

Xtrabackup 2.2.4 does not support mysql 5.1.73. Therefore, if you want to use a new xtrabackup, you must use a mysql database of a later version. Otherwise, the following error will be reported:
Innobackupex: Error: Unsupported server version: '5. 1.73'

Ii. modify my. cnf

Check whether there is datadir under mysqld.
Copy codeThe Code is as follows:
[Mysqld]
Datadir =/var/lib/mysql

If this parameter is not added, the following problems may occur during data restoration:
Copy codeThe Code is as follows:
Xtrabackup: Error: Please set parameter 'datadir'
Innobackupex: fatal error: no 'mysqld' group in MySQL options
Innobackupex: fatal error: OR no 'datadir 'option in group 'mysqld' in MySQL options

Iii. Backup and restoration of all databases

1. All Database backups

Copy codeThe Code is as follows:
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root/home/tank/backup/

If the following error is reported:

Copy codeThe Code is as follows:
InnoDB: Error: log file./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the. cnf file 50331648 bytes!
Innobackupex: Error: The xtrabackup child process has died at/usr/bin/innobackupex line 2679.

Solution: Add the following content to mysqld Of my. cnf:

Copy codeThe Code is as follows:
[Mysqld]
Innodb_log_file_size = 5 M

2. Single Database Backup

Copy codeThe Code is as follows:
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- database = backup_test/home/tank/backup/

3. database backup and Compression

Copy codeThe Code is as follows:
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- stream = tar/home/tank/backup | gzip>/home/tank/backup/'date ready before f_1_h-1_m-1_s'.tar.gz

4. Full data restoration

Copy codeThe Code is as follows:
#/Etc/init. d/mysqld stop // stop mysql
# Mv/var/lib/mysql/var/lib/mysql_bak // data directory backup
# Mkdir-p/var/lib/mysql // recreate the data directory

// -- The apply-log command is used to start the mysql service on a backup.
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- apply-log/home/tank/backup/2014-09-18_16-35-12

// -- Copy-back command to copy data, indexes, and logs from the backup directory to the specified initial location in my. cnf File
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- copy-back/home/tank/backup/2014-09-18_16-35-12

# Chown-R mysql. mysql/var/lib/mysql // change the File Ownership
#/Etc/init. d/mysqld stop // start mysql

Iv. Incremental backup and Restoration

1. Create a test database and table

Copy codeThe Code is as follows:
Create database backup_test; // create a database

Create table 'backup '(// CREATE a TABLE
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) not null default '',
'Create _ time' timestamp not null default CURRENT_TIMESTAMP,
'Del 'tinyint (1) not null default '0 ',
Primary key ('id ')
) ENGINE = myisam default charset = utf8 AUTO_INCREMENT = 1;

2. Incremental Backup

Copy codeThe Code is as follows:
Mysql> insert into backup (name) VALUES ('tank'), ('tank1 '); // INSERT data

# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- incremental-basedir =/home/tank/backup/2014-09-18_16-35-12 -- incremental/home/tank/backup/

3. Perform Incremental backup on the basis of Incremental Backup

Copy codeThe Code is as follows:
Mysql> insert into backup (name) VALUES ('tank2'), ('tank3'), ('tank6'), ('tank6 '); // insert data

// 2014-09-18_18-05-20 directory, the directory generated by the last Incremental Backup
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- incremental-basedir =/home/tank/backup/2014-09-18_18-05-20 -- incremental/home/tank/backup/

View the Incremental Backup record file:

Copy codeThe Code is as follows:
[Root @ localhost 2014-09-18_16-35-12] # cat xtrabackup_checkpoints // files in the full backup directory
Backup_type = full-prepared
From_lsn = 0 // The start of full backup is 0
To_lsns = 23853959
Last_lsns = 23853959
Compact = 0

[Root @ localhost 2014-09-18_18-05-20] # cat xtrabackup_checkpoints // files in the first Incremental Backup Directory
Backup_type = incremental
From_lsn = 23853959
To_lsns = 23854112
Last_lsns = 23854112
Compact = 0

[Root @ localhost 2014-09-18_18-11-43] # cat xtrabackup_checkpoints // file in the second Incremental Backup Directory
Backup_type = incremental
From_lsn = 23854112
To_lsns = 23854712
Last_lsns = 23854712
Compact = 0

After the Incremental backup is complete, delete the database backup_test and drop the database backup_test. In this way, you can compare and restore the database.

4. incremental Restoration

Copy codeThe Code is as follows:
# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- apply-log -- redo-only/home/tank/backup/2014-09-18_16-35-12

# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- apply-log -- redo-only/home/tank/backup/2014-09-18_16-35-12 -- incremental-dir =/home/tank/backup/2014-09-18_18-05-20

# Innobackupex -- defaults-file =/etc/my. cnf -- user = root -- apply-log -- redo-only/home/tank/backup/2014-09-18_16-35-12 -- incremental-dir =/home/tank/backup/2014-09-18_18-11-43

There are three directories:

1),/home/tank/backup/2014-09-18_16-35-12, full backup directory
2),/home/tank/backup/2014-09-18_18-05-20, the directory generated by the first Incremental backup
3),/home/tank/backup/2014-09-18_18-11-43, the directory generated by the second Incremental backup

The incremental restoration is not over yet. The most important step is to perform a full restoration. Stop the database, delete/var/lib/mysql, and restore the database.

The principle of Incremental backup is to integrate the data in the incremental Directory into the full variable directory, and then restore the full data volume.

In general, innobackupex is fast and supports innodb and myisam. It is not very convenient to use. It is unreasonable to restore all databases for single-database restoration or Incremental backup restoration.

5. Description of common innobackup Parameters

-- 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]
Backup file output format. tar4ibd is used for tar. This file can be obtained from XtarBackup binary files. 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]
Compresses the volume of backup data. Only ibbackup is supported, and xtrabackup is not implemented yet;

-- Include = REGEXP
Encapsulation of the xtrabackup parameter -- tables also supports ibbackup. 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, the parameter is 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 including MyISAM and InnoDB tables will be backed up;

-- Uncompress
Decompress the backup data file and support ibbackup. xtrabackup has not yet implemented this function;

-- Slave-info,
An xtrabackup_slave_info file is generated in the backup slave database and in the -- slave-info Backup Directory. The main log file and offset are saved here. The file content is similar: change master to MASTER_LOG_FILE = '', MASTER_LOG_POS = 0

-- Socket = SOCKET
Specify the location of mysql. sock so that the backup process can log on to mysql.

The result of backing up the mysql database with xtrabackup is only data, but there are still insert statements.

All the data backed up, including the insert
 
Statement for backing up and restoring a database in mysql

Detailed description of mysqldump backup restoration and mysqldump Import and Export Statements
Mysqldump backup:
Mysqldump-u username-p password-h host database a-w "SQL condition" -- lock-all-tables> path
Case:
Mysqldump-uroot-p1234-hlocalhost db1 a-w "id in (select id from B)" -- lock-all-tables> c: \ aa.txt
Restore mysqldump:
Mysqldump-u username-p password-h host database <path
Case:
Mysql-uroot-p1234 db1 <c: \ aa.txt
Mysqldump export by conditions:
Mysqldump-u username-p password-h host database a -- where "Condition Statement" -- no-Table creation> path
Mysqldump-uroot-p1234 dbname a -- where "tag = '88 '" -- no-create-info> c: \ a. SQL
Mysqldump:
Mysqldump-u username-p password-h host database <path
Case:
Mysql-uroot-p1234 db1 <c: \ a.txt

Mysqldump:
Mysqldump-u username-p password-h host database table
Case:
Mysqldump-uroot-p sqlhk9 a -- no-data
Some main parameters of mysqldump

-- Compatible = name
It tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.
-- Complete-insert,-c
The exported data adopts the complete INSERT method containing the field name, that is, all values are written in one row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.
-- Default-character-set = charset
Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.
-- Disable-keys
Tell mysqldump to add/* at the beginning and end of the INSERT statement /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.
-- Extended-insert = true | false
By default, mysqldump enables the -- complete-insert mode, so if you do not want to use it, use this option to set it... the remaining full text>