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

Percona XtraBackup how to achieve full and incremental backup and recovery, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Percona-xtrabackup mainly has two tools, one is xtrabackup, the other is innobackupex, and the latter is a script packaged by the former.

Among the physical backup tools for MySQL, it is probably the most popular and powerful tool, in addition to the famous physical backup tool is the official mysqlbackup.

Xtrabackup can only back up transactional tables, not non-transactional tables, while innobackupex can be used to back up not only transactional tables, but also non-transactional tables such as MyISAM.

This paper mainly introduces the use and principle of innobakcupex.

The version used in this article:

Xtrabackup 2.3.7 + MySQL 5.6.30

If the MySQL version is 5.7.x, xtrabackup 2.4.x is recommended.

What 0 xtrabackup can do

Make hot standby for the table of InnoDB engine

Incremental backup

Stream compression and transfer to another server

Move the table online

Easier to create from the library

Backup does not increase the load on the server

0 principle

Backup and recovery roughly involve three steps: backup-> prepare-> restore

When the backup runs, the tool remembers the LSN number at that time, opens xtrabackup_logfile, and then begins to copy the datafile, that is, the ibdata1 and ibd files.

Replication takes time, and if the file is modified during the copy, the tool will monitor the redo logfile and record each change and save it in the xtrabackup_logfile.

Next, we deal with backup operations for non-transactional tables such as MyISAM, and innobackupex blocks DML through FLUSH TABLES WITH READ LOCK.

And get the position of binlog [and GTID] at this time (which I understand as similar to mysqldump-single-transaction processing)

After completing the copy of the non-transactional table, execute the UNLOCK TABLES, complete the backup, and stop recording the xtrabackup_logfile.

Then there is the process of doing prepare, which is similar to InnoDB's crash-recovery.

Roll forward the redo log (to the data file) and rollback the uncommitted transactions so that the data is consistent, so for the transaction table, the whole process does not affect the write operation.

Note: InnoDB, XtraDB, MyISAM are definitely supported, other storage engines are uncertain, to be tested.

0 permission requirement

Operating system:

Rwx permission is required for datadir.

MySQL:

The minimum required permissions are:

RELOAD

LOCK TABLES (you don't have to add-- no-lock)

REPLICATION CLIENT (to obtain the position of binary log)

PROCESS (in order to execute show engine innodb status, and need to view all running threads)

Other permissions that may be required:

CREATE TABLESPACE (if you need to restore / migrate a single table through 5.6 + TTS)

SUPER (you may need to start or stop the slave thread in a replication environment)

CREATE\ INSERT\ SELECT (operate on PERCONA_SCHEMA.xtrabackup_history)

0 installation

Installation is super easy (can only be used on linux, but that's enough)

Https://www.percona.com/downloads/XtraBackup/LATEST/

It is easy to use the selected version of down after poking it in.

There are RPM package, DEB package, source package, binary package.

It is recommended to use binary package, decompress and configure environment variables. it can be used in debian system or RHEL system.

For the installation of the source code package, please refer to my blog post:

Http://blog.itpub.net/29773961/viewspace-1853405/

0 configuration

Default read my.cnf option, read priority is the same as MySQL.

For example, you don't need to specify datadir during backup and restore, because you can read the options under the [mysqld] group.

You can also read information about [client], such as loading socket,user,password into (although it is not recommended for security reasons, you can do so).

Of course, you can also specify the configuration file to be read through innobackupex-- defaults-file=xxxx/my.cnf.

0 complete

① backup:

If you add-- no-timestamp, a timestamp directory will not be generated in the specified directory, but will be directly placed in the specified directory. I usually add:

Innobackupex-- user=-- password= $basedir [--no-timestamp] (of course-- user/--password can write directly-u $username-p $password)

In the backed up folder, there are several files that are worth noting:

Xtrabackup_binlog_info records the position of binlog. If GTID is enabled, the GTID will also be removed.

It is useful in scenarios that are used for backup + binlog recovery or for establishing slave.

Xtrabackup_checkpoints records the type of backup and the starting value of the lsn number, whether it is compressed, etc.

Xtrabackup_info records the backup tool information, time, backup object (for full instance or a database table), increment, binlog location, etc.

# cat xtrabackup_binlog_info

Binlog.000001 2321 931d11a2-9a8b-11e6-829f-000c298e914c:1-8

# cat xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsn = 304247338

Last_lsn = 304247338

Compact = 0

Recover_binlog_info = 0

# cat xtrabackup_info

Uuid = cfb49b5f-02e8-11e7-94b4-000c298e914c

Name =

Tool_name = innobackupex

Tool_command =-- password=... / data/dbbak

Tool_version = 2.3.7

Ibbackup_version = 2.3.7

Server_version = 5.6.30-log

Start_time = 2017-03-07 11:47:36

End_time = 2017-03-07 11:47:39

Lock_time = 0

Binlog_pos = filename 'binlog.000001', position' 2321, GTID of the last change '931d11a2-9a8b-11e6-829f-000c298e914c:1-8'

Innodb_from_lsn = 0

Innodb_to_lsn = 304247338

Partial = N

Incremental = N

Format = file

Compact = N

Compressed = N

Encrypted = N

There is also a backup-my.cnf file that records the option parameters that may be involved in backup, such as system tablespace information, independent undo tablespace information, redo-log information, and so on:

# cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

Innodb_checksum_algorithm=innodb

Innodb_log_checksum_algorithm=innodb

Innodb_data_file_path=ibdata1:12M:autoextend

Innodb_log_files_in_group=2

Innodb_log_file_size=50331648

Innodb_fast_checksum=false

Innodb_page_size=16384

Innodb_log_block_size=512

Innodb_undo_directory=.

Innodb_undo_tablespaces=0

② prepare:

The second step is prepare. As mentioned earlier, this process is similar to innodb's crash recovery.

It can also be understood as the process of "apply". Here is the command of full prepare, which is very simple.

Innobackupex-- apply-log $basedir

When-- apply-log, you can specify-- use-memory, increase its value to speed up, if not specified, the default value is 100MB.

③ reverts to datadir:

The recovery process is also very simple (both complete and additional are this recovery command), and you only need to add the-- copy-back parameter.

Innobackupex-- copy-back $basedir

In this way, you can restore the things of $basedir to the datadir. Datadir does not need to specify, it will read the my.cnf to get

The default is that the datadir is empty (or not created). If you want to force writing, you need to add the parameter:-- force-non-empty-directories

0 additional provision

Incremental backup is a little more complicated than full backup, and this article would also like to focus on how to do incremental backup.

For some scenarios, it may not be necessary to complete the data every day.

For example, in some scenarios, you can do a full backup once a week and an incremental backup once a day, which can save disk space and reduce backup time.

The principle of additional equipment is to find the changed data by comparing the information of LSN. When there is a modification operation, the LSN number will be changed. Compared with the last full differential LSN number, the differential data can be backed up.

The whole process is still divided into three steps: backup-> prepare-> restore.

The method of adding ① is different from that of complete equipment:

Innobackupex-user=-password=-incremental $new_dir-incremental-basedir=$basedir

Where-- incremental is the storage directory of this incremental backup.

$new_dir indicates the directory in which the incremental backup is placed.

-- incremental-basedir indicates which backup to make an incremental backup for

The differences in backups are viewed in the xtrabackup_checkpoints of the directory:

For example:

Content in $basedir:

Backup_type = full-prepared

From_lsn = 0

To_lsn = 304247338

Last_lsn = 304247338

Compact = 0

Recover_binlog_info = 0

Content in $new_bkdir:

Backup_type = incremental

From_lsn = 304247338

To_lsn = 304250267

Last_lsn = 304250267

Compact = 0

Recover_binlog_info = 0

You can pay attention to the additional from_lsn number.

Pages larger than this LSN number are changed, and these offsets, that is, need to be backed up incrementally.

② prepare:

Prepare process:

Do prepare from the first backup (that is, full backup), and then add subsequent incremental backups to the full backup in turn.

Notice that there is an extra parameter-- redo-only, which refers to the rollback of committed transactions and uncommitted transactions.

In addition,-- incremental-dir has not been used before, and this parameter represents the incremental backup directory that needs to be merged.

Note that multiple incremental backups here refer to the increments made for the last incremental backup.

That is, it can be understood as:

Complete: 500GB

First incremental backup: 2GB

Second incremental backup: 1GB (incremental data for the first incremental backup)

……

The nth time

Prepare according to the backup order, that is, the order of prepare is:

First full-> incremental backup 1-> incremental backup 2->.-> incremental backup n

The first complete prepare:innobackup-- apply-log-- redo-only $basedir

The second prepare:innobackup-- apply-log-- redo-only $basedir-- incremental-dir=$new_dir_1 ($new_dir_1 here is the first incremental backup)

.

The nth prepare:innobackup-- apply-log $basedir-- incremental-dir=$new_dir_n (the $new_dir_n here is the most recent and last incremental backup

The prepare of the last incremental backup does not need to be specified-- redo-only

Finally, incremental backup and full backup are merged to roll back uncommitted transactions, which is the same as full prepare:

Innobackup-- apply-log $basedir

It looks a little complicated, but it doesn't matter. There will be experiments and diagrams below.

③ reverts to datadir:

It's the same as the full quantity, just copyback it.

Innobackupex-- copy-back $basedir

The prepare of incremental backup is a bit painful, so let's make a summary:

① prepare is complete (plus-- redo-only)

For every incremental backup of ② prepare, you need to add-- redo-only, and the prepare of the last incremental backup does not need-- redo-only.

③ does the final completion of generation-- apply-log

0 experiment

And then there's the experiment...

First set up a backup user and give it permission.

Mysql > CREATE USER xbackup@localhost IDENTIFIED BY 'back123'

Mysql > GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *. * TO xbackup@localhost

0 full backup & restore

Add test data to test.tb

Mysql > CREATE TABLE test.tb (id int primary key, name varchar (16))

Query OK, 0 rows affected (0.07 sec)

Mysql > INSERT INTO test.tb VALUES (1), (2) (2), (3), (3), (4), (5)

Query OK, 5 rows affected (0.02 sec)

Records: 5 Duplicates: 0 Warnings: 0

Create a backup storage directory

$mkdir-p / data/backup/

Specify the backup location and start the backup

$innobackupex-uxbackup-pbackup123-no-timestamp / data/backup/backup

The / data/backup/backup here is the complete directory.

. (omit screen brushing output)

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

170321 16:06:11 completed OK!

When you see completed OK, it shows that it is really ok.

You can take a look at the contents of this directory:

One part is the content of datadir under MySQL, such as library directory, redolog, system tablespace.

Part of it is something generated by the backup tool, which has been introduced before:

Backup-my.cnf

Ibdata1

Ib_logfile0

Ib_logfile1

Mysql

Performance_schema

Test

Xtrabackup_binlog_info

Xtrabackup_binlog_pos_innodb

Xtrabackup_checkpoints

Xtrabackup_info

Xtrabackup_logfile

Conduct prepare

$innobackupex-- apply-log / data/backup/backup

Close mysqld

$mysqladmin-uroot-p shutdown

Enter password:

$ps-ef | grep mysql

Root 2991 2438 1 11:08 pts/0 00:00:00 grep mysql

Remove datadir:

$mv / data/mysql_data / data/mysql_data.bk

Recover data

$innobackupex-- copy-back / data/backup/backup/

Modify the permissions of the new datadir

$chown mysql:mysql-R / data/mysql_data

Start the database

$mysqld &

$ps-ef | grep mysql

Root 2712 2438 86 16:35 pts/0 00:00:02 mysqld

Root 2714 2438 0 16:35 pts/0 00:00:00 grep mysql

Check the contents of the test.tb

$mysql-e "SELECT * FROM test.tb;"

+-+ +

| | id | name |

+-+ +

| | 1 | zhou |

| | 2 | 430 |

| | 3 | YYF |

| | 4 | ChuaN |

| | 5 | Faith |

+-+ +

At this point, the full backup & restore is complete

0 incremental backup & recovery

Let's start with a full list:

$innobackupex-uxbackup-pbackup123-no-timestamp / data/backup/all_backup

Modify the test table and data: (add a field, change two pieces of data)

Mysql > ALTER TABLE test.tb ADD COLUMN picked varchar (16)

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > UPDATE test.tb SET picked='naga' WHERE id=1

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > UPDATE test.tb SET picked='TA' WHERE id=2

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Perform the first incremental backup:

$innobackupex-uxbackup-pbackup123-no-timestamp-incremental / data/backup/incremental-dir-1-incremental-basedir=/data/backup/all_backup/

You can do another incremental backup:

There are two incremental backup methods at this time:

First, always do increments for basedir, which is very easy to recover. You only need to merge the last incremental backup into a full backup and you can restore it.

Second, always make incremental backups for the last increment. The recovery in this way is about to be merged one by one, that is, the idea of additional equipment that I mentioned above seems a little complicated.

Anyway, I like the first one, and I can adapt to most of the scenes.

I painted two pictures with word to help understand.

The first kind:

The first of January is always used as a basedir, so the FROM_LSN number is always 5000.

The second kind:

Always use the last (most recent) backup as a basedir.

The second is introduced here:

The method of multiple incremental backups remains the same. You only need to modify-- incremental-basedir:

Continue to make some changes to test.tb:

Mysql > UPDATE test.tb SET picked='DS' WHERE id=3

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM test.tb

+-- +

| | id | name | picked | |

+-- +

| | 1 | zhou | naga |

| | 2 | 430 | TA |

| | 3 | YYF | DS |

| | 4 | ChuaN | NULL |

| | 5 | Faith | NULL |

+-- +

5 rows in set (0.01 sec)

For the first incremental backup / data/backup/incremental-dir-1, do the second incremental backup and put the second incremental backup to / data/backup/incremental-dir-2/

$innobackupex-uxbackup-pbackup123-no-timestamp-incremental / data/backup/incremental-dir-2/-incremental-basedir=/data/backup/incremental-dir-1

Prepare process, which is also the most painful process in incremental backup:

Because a total of three backups have been made, do the prepare three times first:

First, do prepare for everything:

$innobackupex-apply-log-redo-only / data/backup/all_backup/

Then do the prepare of the first incremental backup:

$innobackupex-apply-log-redo-only / data/backup/all_backup/-incremental-dir=/data/backup/incremental-dir-1

For the second incremental backup prepare, note that the second backup is the last, so there is no need to add the-- redo-only parameter:

$innobackupex-apply-log / data/backup/all_backup/-incremental-dir=/data/backup/incremental-dir-2

Finally, merge two incremental backups with one full backup:

$innobackupex-- apply-log / data/backup/all_backup/

The recovery process is no different from full recovery:

Stop the mysqld.

$mysqladmin-uroot-p shutdown

$ps-ef | grep mysql

Root 3533 3081 0 17:05 pts/1 00:00:00 grep mysql

Remove datadir

$mv / data/mysql_data / data/mysql_data.bk2

Recover data

$innobackupex-- copy-back / data/backup/all_backup/

Modify the permissions of the new datadir

$chown mysql:mysql-R / data/mysql_data

Start

$mysqld &

Check that the complete and two additions have been restored, that is, the status of the last data:

$mysql-uroot-p-e "SELECT * FROM test.tb;"

+-- +

| | id | name | picked | |

+-- +

| | 1 | zhou | naga |

| | 2 | 430 | TA |

| | 3 | YYF | DS |

| | 4 | ChuaN | NULL |

| | 5 | Faith | NULL |

+-- +

At this point, the incremental backup & restore is complete

0 summarize the whole process of xtrabackup backup and recovery:

1. For backup operation, you need to provide MySQL users with sufficient permissions, and mysqld startup users need to have rwx permission to datadir.

2. Prepare, roll back the uncommitted transaction and write the committed transaction to the data file.

3. Stop the mysqld service

4 、 mv data/ data_bak_.../

5. Copyback, go back

6. Modify permissions, new datadir permissions

7. Start the service

Of course, all of the above backup objects are for the entire MySQL instance.

After reading the above, have you mastered how Percona XtraBackup implements full and incremental backup and recovery? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.