Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about how to carry out full and incremental backup and recovery of MySQL 5.6xtrabackup. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Xtrabackup backup and recovery

Compared to MySQL's own backup mysqldump, it is similar to Oracle's export. Efficiency is fine with a small amount of data, and when the amount of data reaches a certain scale, the time to use mysqldump to perform backups may be acceptable, but the recovery time is basically unbearable.

At this time, you need an easy-to-use and efficient tool, and xtrabackup is one of them, known as the free version of InnoDB HotBackup.

Xtrabackup provides two command-line tools:

Xtrabackup: dedicated to backing up data from the InnoDB engine

Innobackupex: this is a perl script that invokes the xtrabackup command during execution so that you can back up both InnoDB and MyISAM engine objects.

I. the principle of Xtrabackup backup

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

InnoDB maintains a redo log, also known as transaction log, a transaction log that contains all changes to innodb data. When InnoDB starts, it checks data file and transaction log first, and does two steps:

When XtraBackup backs up, it copies innodb data page by page without locking the table. At the same time, XtraBackup has another thread monitoring transactions log, and once the log changes, it copies the changed log pages away. Why the rush to copy away? Because the size of the transactions log file is limited, when it is full, it will start all over again, 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.

Note: xtrabackup only backs up data files, not data table structures (.frm), so when using xtrabackup to restore, you must have corresponding table structure files (.frm).

So back it up manually so that it can be used when xtrabackup is restored.

2. Backup operation:

1. Full backup operation:

Click (here) to collapse or open

[root@zhanglin bin]. / xtrabackup-- backup-- log-stream-- target-dir=/mysqlbak/xtrabackup/20141022-- datadir=/usr/local/mysql/data

. / xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86 / 64) (revision id:)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / usr/local/mysql/data

Xtrabackup: open files limit requested 0, set to 1024

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =. /

Xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

-slightly

[01] Copying. / mdm_pro/org_organ_business_group.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/org_organ_business_group.ibd

[01]... done

[01] Copying. / mdm_pro/coding_rule.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/coding_rule.ibd

[01]... done

[01] Copying. / mdm_pro/org_store_channel.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/org_store_channel.ibd

[01]... done

[01] Copying. / mdm_pro/dim_org_store.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/dim_org_store.ibd

[01]... done

[01] Copying. / mdm_pro/org_organ_region.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/org_organ_region.ibd

[01]... done

[01] Copying. / mdm_pro/org_organ_managing_city.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/org_organ_managing_city.ibd

[01]... done

[01] Copying. / mdm_pro/pro_barcode.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/pro_barcode.ibd

[01]... done

[01] Copying. / mdm_pro/type_duibi_back.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/type_duibi_back.ibd

[01]... done

> > log scanned up to (18573586827)

[01] Copying. / mdm_pro/org_store.ibd to / mysqlbak/xtrabackup/20141022/mdm_pro/org_store.ibd

[01]... done

> > log scanned up to (18573586827)

Xtrabackup: The latest check point (for incremental):\ '18573586827\'

Xtrabackup: Stopping log copying thread.

Log scanned up to (18573586827)

Xtrabackup: Transaction log of lsn (18573586258) to (18573586827) was copied.

[root@zhanglin bin]

two。 Incremental backup concept:

The tools provided by the MySQL database itself do not support true incremental backups, and binary log recovery is a point-in-time (point-in-time) recovery rather than an incremental backup. The Xtrabackup tool supports incremental backups of the InnoDB storage engine, which works as follows:

(1) first complete a full backup and record the LSN (Log Sequence Number) of the checkpoint at this time.

(2) during the process incremental backup, compare whether the LSN of each page in the tablespace is greater than the LSN of the last backup, and if so, back up the page and record the LSN of the current checkpoint.

First, find and record the last checkpoint ("last checkpoint LSN") in logfile, then start copying the logfile of InnoDB to xtrabackup_logfile; from the location of LSN, and then start copying all the data files

Stop copying logfile until you have finished copying all the data files.

Because all data modifications are recorded in logfile, even if the data file is modified during backup, you can still maintain data consistency by parsing xtrabackup_logfile during recovery.

In the directory of incremental backups, the data files end in .delta. Incremental backups only back up page that has been modified since the last full backup, so incremental backups only temporarily use less space. Incremental backups can be incremental on the basis of incremental backups.

Incremental backup benefits:

The main contents are as follows: 1. The database is too large to have enough space for full backup. Incremental backup can save space effectively and has high efficiency.

2. Hot backup is supported. The backup process does not lock the table, is not limited by time, and does not affect the use of users.

3. Daily backup only produces a small amount of data, so remote backup and transmission is more convenient. And save space at the same time.

4. Backup and recovery is based on file operation, which reduces the risk of direct operation to the database.

5. Backup efficiency is higher and recovery efficiency is higher.

A reminder of possible errors:

(a) when doing an incremental backup, if-- target-dir is the same as the full amount, the following error will occur

[root@zhanglin bin] # / xtrabackup-- backup-- log-stream-- target-dir=/mysqlbak/xtrabackup/20141022-- datadir=/usr/local/mysql/data-- incremental-

Basedir=/mysqlbak/xtrabackup/20141022_full_in

Xtrabackup: Error: cannot open / mysqlbak/xtrabackup/20141022_full_in/xtrabackup_checkpoints

Xtrabackup: error: failed to read metadata from / mysqlbak/xtrabackup/20141022_full_in/xtrabackup_checkpoints

(B) and an error will occur if the last directory of the-- incremental-basedir= parameter is not base.

[root@zhanglin bin] # / xtrabackup-- backup-- log-stream-- target-dir=/mysqlbak/xtrabackup/20141022_full_in-- datadir=/usr/local/mysql/data-- incremental-

Basedir=/mysqlbak/xtrabackup/20141022-test

Xtrabackup: Error: cannot open / mysqlbak/xtrabackup/20141022-test/xtrabackup_checkpoints

Xtrabackup: error: failed to read metadata from / mysqlbak/xtrabackup/20141022-test/xtrabackup_checkpoints

Incremental backup operations:

Click (here) to collapse or open

[root@zhanglin bin] # / xtrabackup-- backup-- log-stream-- target-dir=/mysqlbak/xtrabackup/20141022_full_in-- datadir=/usr/local/mysql/data-- incremental-basedir=/mysqlbak/xtrabackup/base

. / xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86 / 64) (revision id:)

Incremental backup from 16888466657 is enabled.

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / usr/local/mysql/data

Xtrabackup: open files limit requested 0, set to 1024

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =. /

Xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

> > log scanned up to (18580258871)

> > log scanned up to (18580258871)

Xtrabackup: using the full scan for incremental backup

[01] Copying. / ibdata1 to / mysqlbak/xtrabackup/20141022_full_in/ibdata1.delta

-slightly

[01] Copying. / mdm_pro/mid_org_area.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/mid_org_area.ibd.delta

[01]... done

[01] Copying. / mdm_pro/org_organ_business_group.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/org_organ_business_group.ibd.delta

[01]... done

[01] Copying. / mdm_pro/coding_rule.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/coding_rule.ibd.delta

[01]... done

[01] Copying. / mdm_pro/org_store_channel.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/org_store_channel.ibd.delta

[01]... done

[01] Copying. / mdm_pro/dim_org_store.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/dim_org_store.ibd.delta

[01]... done

[01] Copying. / mdm_pro/org_organ_region.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/org_organ_region.ibd.delta

[01]... done

[01] Copying. / mdm_pro/org_organ_managing_city.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/org_organ_managing_city.ibd.delta

[01]... done

[01] Copying. / mdm_pro/pro_barcode.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/pro_barcode.ibd.delta

[01]... done

[01] Copying. / mdm_pro/type_duibi_back.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/type_duibi_back.ibd.delta

> > log scanned up to (18580258871)

[01]... done

[01] Copying. / mdm_pro/org_store.ibd to / mysqlbak/xtrabackup/20141022_full_in/mdm_pro/org_store.ibd.delta

[01]... done

Xtrabackup: The latest check point (for incremental):\ '18580258871\'

Xtrabackup: Stopping log copying thread.

Log scanned up to (18580258871)

Xtrabackup: Transaction log of lsn (18580258871) to (18580258871) was copied.

[root@zhanglin bin] #

3. Introduction of xtrabackup parameters:

-- defaults-file=#

The path to the default configuration file, if not, xtrabackup will look for the configuration file / etc/my.cnf, / etc/mysql/my.cnf, / usr/local/etc/my.cnf, ~ / .my.cnf from the following location, and read the [mysqld] and

[xtrabackup] configuration segment. You 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_size in [mysqld]

Six parameters can make xtrabackup work normally.

-- if defaults-extra-file=# uses this parameter, after reading the global configuration file, it will read the configuration file specified here

-- path to the directory where target-dir=name backup files are stored

-- backup implements backup to target-dir

-- prepare implements preparation for backup files before recovery (generates InnoDB log file)

-- print-param prints the parameters required for backup or restore

-- use-memory=# this parameter is used in prepare to control the amount of memory used by innodb instances in prepare.

-- suspend-at-end generates a xtrabackup_suspended file in the target-dir directory, suspends the xtrabackup process, and constantly synchronizes changes in the data file to the backup file until the user deletes it manually

Xtrabackup_suspended file

-- throttle=# IO per second to limit the number of backup operations to minimize the impact of backup on normal database business.

-- log-stream this parameter is used in backup to output the content of xtrabackup_logfile standard. When using this parameter, the suspend-at-end parameter is automatically used, and the stream mode of the innobackupex script uses this parameter.

-- incremental-lsn=name incremental backup only copies ibd pages whose LSN is newer than the value specified by this parameter. Which LSN you went to in the previous backup can see the xtrabackup_checkpoints file of the previous backup set.

-- incremental-basedir=name this parameter is used in backup to back up a newer idb pages than the backup set at the location specified by this parameter.

-- incremental-dir=name this parameter is used in prepare to specify the storage path of .delta files and log files generated during prepare.

-- tables=name is used when backing up data files of type file-per-table, and regular expressions are used to specify the innodb tables to be backed up.

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

III. Full and incremental recovery:

We need to prepare both the full backup and the incremental backup. If you are only doing full recovery, you only need to execute your previous full amount once, and here is the command.

. / xtrabackup-- defaults-file=/etc/my.cnf-- prepare-- target-dir=/mysqlbak/xtrabackup/20141022

. / xtrabackup-defaults-file=/etc/my.cnf-prepare-target-dir=/mysqlbak/xtrabackup/20141022_full_in-incremental-basedir=/mysqlbak/xtrabackup/base

Reminder: xtrabackup only backs up InnoDB data files, the table structure is not backed up, so when restoring, you must have the corresponding table structure file (.frm).

After reading the above, do you have any further understanding of how to perform full and incremental backup and recovery of MySQL 5.6xtrabackup? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.