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

Many novices are not very clear about how to complete and incremental backup recovery with lnnobackupex in mysql. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

The hot backup (physical backup) of MySQL can take the way of full backup plus incremental backup to reduce the pressure of database Imax O and the occupation of system resources. Incremental backup is mainly based on full or incremental backup to back up those changed pages. The principle of backup is based on a growing LSN sequence, which is similar to Oracle's SCN. During recovery, we need to roll forward committed transactions and roll back uncommitted transactions. Incremental backup and incremental recovery are mainly described here.

1. Additional knowledge points

As not all information changes between each backup, the incremental backup strategy uses this to reduce the storage needs and the duration of making a backup. This can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. Every time the database is modi?ed, this number gets incremented. An incremental backup copies all pages since a speci?c LSN. Once the pages have been put together in their respective order, applying the logs will recreate the process that affected the database, yielding the data at the moment of the most recently created backup.

Additional backup is the page that has changed since the last backup, which can reduce the overhead of storage and system resources. Incremental backups are primarily targeted at InnoDB because InnoDB uses a log sequence number (LSN). InnoDB's LSN is a growing sequence, similar to Oracle's SCN, recording changes in InnoDB. An incremental backup is what happens after a specific LSN is backed up. By reorganizing these LSN sequentially, the database can be restored to the point of failure or at any time.

Innobackupex-incremental / data/backups-incremental-lsn=1291135

Innobackupex-incremental / data/backups-incremental-lsn=1358967

As above, we can use the-- incremental-lsn option to implement incremental backups

Warning: This procedure only affects XtraDB or InnoDB-based tables. Other tables with a different storage engine, e.g. MyISAM, will be copied entirely each time an incremental backup is performed.

For non-XtraDB or InnoDB storage engines, hot backup will still back up all data files, index files, format files, etc.

Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full ones. This is, perhaps, the stage where more attention is needed:

? First, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones.

? Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.

For the Prepare phase of incremental backup, there are two things to note, one is that committed transactions require replayed, and one uncommitted transaction requires rollback.

If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able to add the incremental ones. If you do this on an incremental one, you won't be able to add data from that moment and the remaining increments. Having this in mind, the procedure is very straight-forward using the-redo-only option, starting with the base backup:

If committed transactions are replay and uncommitted transactions are rolled back during the Prepare phase, subsequent incremental backups cannot be added to the current full. Therefore, the-- redo-only option should be used throughout the Prepare phase.

-- redo-only should be used when merging all incrementals except the last one. That's why the previous line doesn't contain the-- redo-only option. Even if the-redo-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.

For cases where there are multiple increments, only the last increment does not need to be used-- the redo-only option. If used, rollback will be done when the server starts.

II. Demonstration

1. Prepare the experimental environment

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.6.25-log |

+-+

1 row in set (0.00 sec)

Mysql > create database inc_rec

Query OK, 1 row affected (0.00 sec)

Mysql > use inc_rec

Database changed

Mysql > create table andy (id int)

Query OK, 0 rows affected (0.08 sec)

Mysql > insert into andy values (1), (2)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

two。 Fully prepared

[root@mysql02 full] # innobackupex-defaults-file=/etc/my.cnf-user=root-password=oracle-port=3606 / xtrabackup/full/

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

170609 03:53:56 completed OK!

3. View the full build file

[root@mysql02 full] # ll/ xtrabackup/full/

Total 4

Drwxr-x---. 6 root root 4096 Jun 9 03:53 2017-06-09 03-53-51

4. New data for simulating business

Mysql > insert into andy values (3), (4)

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

5. Incremental backup

-- create an incremental backup directory and empower it

[root@mysql02 full] # mkdir-p / xtrabackup/increament/

[root@mysql02 full] # chown-R mysql:mysql / xtrabackup/increament/

[root@mysql02 full] # ll / xtrabackup/

Total 8

Drwxr-xr-x. 3 mysql mysql 4096 Jun 9 03:53 full

Drwxr-xr-x. 2 mysql mysql 4096 Jun 9 04:00 increament

-- officially start incremental backup

[root@mysql02 full] # innobackupex-defaults-file=/etc/my.cnf-user=root-password=oracle-incremental\

-- incremental-basedir=/xtrabackup/full/2017-06-09 / 03-53-51 / / xtrabackup/increament/

# # the following is incremental backup output

170609 04:02:21 Backup created in directory'/ xtrabackup/increament/2017-06-09 December 04-02-16max'

MySQL binlog position: filename 'binlog.000003', position' 894'

. . . Omit

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

170609 04:02:22 completed OK!

Add:

[root@mysql02 2017-06-09 04-02-16] # pwd

/ xtrabackup/increament/2017-06-090004-02-16

-- View the relevant documents generated by the additional equipment

[root@mysql02 2017-06-09 04-02-16] # ll

Total 8580

-rw-r-. 1 root root 418 Jun 9 04:02 backup-my.cnf

-rw-r-. 1 root root 360448 Jun 9 04:02 ibdata1.delta

-rw-r-. 1 root root 44 Jun 9 04:02 ibdata1.meta

Drwxr-x---. 2 root root 4096 Jun 9 04:02 inc_rec

Drwxr-x---. 2 root root 4096 Jun 9 04:02 mysql

Drwxr-x---. 2 root root 4096 Jun 9 04:02 performance_schema

Drwxr-x---. 2 root root 4096 Jun 9 04:02 test

-rw-r-. 1 root root 18 Jun 9 04:02 xtrabackup_binlog_info

-rw-r-. 1 root root 117 Jun 9 04:02 xtrabackup_checkpoints

-rw-r-. 1 root root 584 Jun 9 04:02 xtrabackup_info

-rw-r-. 1 root root 8388608 Jun 9 04:24 xtrabackup_logfile

-- the file xtrabackup_info contains backup type

[root@mysql02 2017-06-09 04-02-16] # more xtrabackup_info | grep ^ incremental

Incremental = Y

-- the file xtrabackup_checkpoints contains checkpoint information related to backup

[root@mysql02 2017-06-09 04-02-16] # more xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 1648193

To_lsn = 1652210

Last_lsn = 1652210

Compact = 0

Recover_binlog_info = 0

-- the file xtrabackup_binlog_info contains the location of binlog

[root@mysql02 2017-06-09 04-02-16] # more xtrabackup_binlog_info

Binlog.000003 894

6. Misoperation, truncate table

Mysql > truncate table andy

Query OK, 0 rows affected (0.22 sec)

Mysql > select * from andy

Empty set (0.01sec)

7. Stop the mysql database

[root@mysql02 ~] # service mysql stop

[root@mysql02 ~] # ps-ef | grep mysql

8. Restore the database (restore the full first, and then restore the incremental backup in order of incremental backup)

8.1 restore the full backup set first:

[root@mysql02 full] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log-- redo-only / xtrabackup/full/2017-06-09 / 03-53-51 /

170609 04:19:30 completed OK!

8.2 when restoring incremental backup sets: (if there are multiple incremental backups, it is best not to use-- redo-only for the last incremental backup, and-- redo-only for the rest)

[root@mysql02 full] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log / xtrabackup/full/2017-06-090003-53-51-- incremental-dir=/xtrabackup/increament/2017-06-090004-02-16 /

170609 04:24:45 completed OK! # the result shows that completed OK is a complete success

Note: / xtrabackup/full/2017-06-090003-53-51 is the full base directory and incremental-dir is the incremental backup directory

9. Rename the original folder to a new location and create the original folder

[root@mysql02 full] # mv / data/mysql / data/mysqlbak

[root@mysql02 full] # mkdir-p / data/mysql

10. Copy the restored file to the original data location

[root@mysql02 full] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- copy-back / xtrabackup/full/2017-06-09 / 03-53-51 /

170609 04:33:06 completed OK! # the result shows that completed OK is a complete success

Description: / xtrabackup/full/2017-06-090003-53-51 / is a full base directory

11. Permission modification

[root@mysql02 ~] # mkdir-p / data/mysql/binarylog (note: here I binlog is under the path of datadir, so I want to create a directory for binlog separately)

Chown-R mysql:mysql / data/mysql

twelve。 Start the restored instance

[root@mysql02 mysql] # mysqld_safe-- defaults-file=/etc/my.cnf &

[root@mysql02] # mysql-uroot-poracle

Mysql > use inc_rec

Mysql > select * from andy

+-+

| | id |

+-+

| | 1 |

| | 2 |

| 3 | > restore successfully!

| | 4 |

+-+

Summary:

A, incremental backup is completed on an incremental or complete basis.

B. The basis of incremental backup is that InnoDB engine uses LSN mechanism, and there is no incremental backup for non-InnoDB engine, which is complete every time.

C. During the recovery of incremental backups, committed transactions need to be rolled forward and uncommitted transactions rolled back.

D. The recovery of incremental backup should be replay one by one according to the success of the backup, and the-- apply-log-- redo-only option should be used.

E. Only the last incremental backup does not need to use the-- redo-only option.

F, if you want to do full recovery or point-in-time recovery, you need to combine binlog to achieve.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, 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.