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

This article introduces how to carry out xtrabackup backup and recovery in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

XtraBackup is a backup tool launched by Percona, which is a supplement to mysqldump. There will be some bottlenecks in the use of mysqldump for the import of large quantities of data, which may be felt by students who have done some data migration projects.

The amount of data in data migration, small and good, big and big, see the move, find the right scenario is the best.

If you go to the Percona website to download it now, you will find that the latest version is 2.4.6. You can choose a complete package for download, or install rpm one by one according to your needs. The complete tool is about 60m.

Most of the current MySQL versions are 5.5,5.6, which is relatively new for 5.7. There will be some time transition, and many years ago, there may have been a relatively large number of people using version 2.0.

XtraBackup actually contains two tools, one is xtrabackup and the other is innobackupex. Let's take an earlier version as a demonstration for a moment, and then compare it with the new version.

# xtrabackup-version

Xtrabackup version 1.6.5 for Percona Server 5.1.59

Innobackupex-version

InnoDB Backup Utility v1.5.1 InnoDB Backup Utility xtrabackup; Copyright 2003, 2009 Innobase Oy

And Percona Inc 2009-2012. All Rights Reserved.

You can see that there are some differences between the versions of the two tools.

Xtrabackup is mainly a tool for hot backup innodb or data in xtradb tables. It cannot back up other types of tables or data table structures.

Innobackupex is an perl script that encapsulates xtrabackup and can back up and restore MyISAM tables and data table structures.

So generally speaking, although InnoDB usage scenarios are the most common, we still have to consider MyISAM. On the whole, there are more scenarios using innobackex.

Analysis of backup

Using innobackupex backup, there are many command options, you can use innobackupex-- help to view the details of the parameters to use.

For example, I need to do a complete set. You can do this in a similar way, adding several secondary options to the backup command, backup using socket connection, backup directory under / home/databak/full/20170322.

Innobackupex-- socket=/home/mysql/mysql.sock / home/databak/full/20170322-- no-timestamp-- no-lock-- throttle=100 check the corresponding directory after backup. The backup data is as follows, and several red files are generated in the backup. On the whole, it looks the same as the directory structure of the source library.

# du-sh. / *

2.6G. / backend

4.0K. / backup-my.cnf

646M. / gm

1.0G. / ibdata1

99M. / mobile_activity

5.0G. / mobile_billing

1.1M. / mysql

2.0G. / oem_mon

212K. / performance_schema

112K. / test

4.0K. / xtrabackup_binary

4.0K. / xtrabackup_checkpoints

4.0K. / xtrabackup_logfile, let's take a brief look at the file generated above.

The file at the end of binary is the executable file used in backup, which can correspond to several versions, such as xtrabackup_51,xtrabackup_55, etc.

# more xtrabackup_binary

The contents of the file at the end of xtrabackup_55logfile cannot be viewed directly, but can be viewed with strings. You can see the corresponding binary log through strings parsing, and of course there are Xid for transactions.

# strings xtrabackup_logfile

Xtrabkup 170322 16:33:40

{'; {

'; {

MySQLXid

. / mysql-bin.000009

393102654

The file under 08360000000039DB is even more special, this is the key to data backup and recovery, there is a backup restore all the checkpoints LSN, from the following data, this is a complete, because from_lsn=0.

# cat xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsn = 30754980731

Last_lsn = 30754980731

Under the directory structure of the source database, if we filter it a little bit, we will get a directory structure that is almost the same as that of the tool.

# du-sh. / * | grep-v mysql-bin | grep-v innodb | grep-v log

2.6G. / backend

646M. / gm

1.0G. / ibdata1

4.5M. / ib_lru_dump

99M. / mobile_activity

5.0G. / mobile_billing

1.1M. / mysql

4.0K. / mysql.pid

0. / mysql.sock

2.0G. / oem_mon

212K. / performance_schema

112K. / test so a tool like xtrabackup is a hot backup tool, which is a bit like having file-level copies, but more than that, let's move on.

Full library recovery simulation

Data recovery is one of the most important tasks of DBA, and the word "one" had to be removed many years ago. If the data cannot be recovered, the backup is meaningless.

The data recovery still uses the innobackupex tool, which is a little different in the parameters.

The data recovery here is divided into two steps, prepare and restore recovery. The meaning of prepare is that if there is an uncommitted transaction when we back up the data, but the data exists in the backup, it is a state of data inconsistency. When starting the database, we need to take a roll forward, and then a rollback operation. This embodiment mainly lies in logfile and ibdata. This is done using the option apply-log.

We do it in the following way.

The process of innobackupex-- defaults-file=/home/databak/full/20170322/backup-my.cnf-- user=root-- apply-log / home/databak/full/20170322 actually implicitly calls the executable file xtrabackup_55, calling commands similar to:

Xtrabackup_55-- defaults-file= "/ home/databak/full/20170322/backup-my.cnf" uses 100m of memory by default, or you can adjust it using the option-- use-memory. The whole process will ReFactor the redo log file and ibdata.

After the completion of this step is the most critical place, restore recovery. This process is implemented using the options of copy-back.

Innobackupex-- defaults-file=/home/databak/full/20170322/backup-my.cnf-- user=root-- copy-back / home/databak/full/20170322 the whole process is a lot of copying work.

After completion, you need to modify the owner of the file. The default is root, and then start it.

Incremental backup recovery

Let's take a look at incremental backup and recovery. Let's create some data first. We create a table test2. Exe under the database test.

> create table test2 (id int)

Query OK, 0 rows affected (0.01 sec)

Insert into test2 values (1), (2)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Since we have just done everything, we will continue to make an additional provision.

The commands used are as follows:

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- incremental-basedir=/home/databak/incre/20170322-- incremental / home/databak/incre/20170322 but unfortunately, the execution failed. This mistake has a typical meaning.

170322 18:05:34 innobackupex: Starting ibbackup with command: xtrabackup_55-- defaults-file= "/ etc/my.cnf"-- backup-- suspend-at-end-- target-dir=/home/databak/incre/20170322/2017-03-22 October 18-05-32-- incremental-basedir='/home/databak/incre/20170322'

Innobackupex: Waiting for ibbackup (pid=4079) to suspend

Innobackupex: Suspend file'/ home/databak/incre/20170322/2017-03-22 October 18-05-32 Universe xtrabackupwards suspended`

...

Xtrabackup: Error: cannot open / home/databak/incre/20170322/xtrabackup_checkpoints

Xtrabackup: error: failed to read metadata from / home/databak/incre/20170322/xtrabackup_checkpoints

Innobackupex: Error: ibbackup child process has died at / usr/bin/innobackupex line 349. The reason lies in a key file _ checkpoints

There must be a reference point for adding equipment, that is, where to start, that is, which LSN to start. This LSN does not exist under the specified parameter-incremental-basedir=/home/databak/incre/20170322. Because this is a new directory, you need to point to the directory of the full library backup.

Then the backup will be fine after repair. Yingwei has this reference point LSN, so it needs to be noted that this backup actually has cumulative increments and differential increments.

How do you understand this? for example, do a full backup on Sunday, an additional backup on Monday, and an additional backup from Sunday to Tuesday on Tuesday. This is a cumulative incremental backup, while on Wednesday, make a backup of data changes from Tuesday to Wednesday. It's a differential incremental backup.

The following is a cumulative incremental backup. Because the benchmark is complete last time, a directory will be automatically generated after backup, such as 2017-03-22-18-07-38.

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- incremental-basedir=/home/databak/full/20170322-- incremental / home/databak/incre/20170322 to distinguish between the two increments, I continue to insert two rows of data.

Insert into test2 values (3), (4)

Query OK, 2 rows affected (0. 00 sec) so that the table test2 has four pieces of data, two at a time.

The following is a differential incremental backup. Based on the last addition.

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- incremental-basedir=/home/databak/incre/20170322/2017-03-22 recovery 18-07-38-- is the whole recovery process of incremental / home/databak/incre/20170322 in the following form or a prepare process, first of all complete:

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log-- redo-only / home/databak/full/20170322 is then added. Note the parameters marked red here.

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log-- redo-only / home/databak/full/20170322-- incremental-dir=/home/databak/incre/20170322/2017-03-22room18-07-38 this is actually a merge process. For additional equipment, it will generate the following files, all .delta, .meta and so on.

[test] # ll

Total 132

-rw-r--r-- 1 mysql mysql 61 Mar 22 17:58 db.opt

-rw-rw---- 1 mysql mysql 8556 Mar 22 18:03 test2.frm

-rw-r--r-- 1 root root 81920 Mar 22 18:08 test2.ibd.delta

-rw-r--r-- 1 root root 18 Mar 22 18:08 test2.ibd.meta

It is interesting to add the checkpoint files in the directory. There is a clear incremental description of LSN.

[2017-03-22 October 18-07-38] # cat * checkpoints

Backup_type = incremental

From_lsn = 30754980731

To_lsn = 30754984465

Last_lsn = 30754984465, but the checkpoint file in the complete set after prepare has actually changed.

# cat * checkpoints

Backup_type = full-prepared

From_lsn = 0

To_lsn = 30754984465

Last_lsn = 30754984465 at this time we use the following method to restore the restore.

# innobackupex-- defaults-file=/etc/my.cnf-- user=root-- copy-back / home/databak/incre/20170322/2017-03-22mm 18-07-38 how many data items are in the table test2 at this time? It's two.

This process is equivalent to the completion of a complete + an additional data recovery process.

And we inserted some data after an addition. How can we continue to restore it? it is still a process of prepare. This path needs to be noted, or merge to the full.

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- apply-log-- redo-only / home/databak/full/20170322-- incremental-dir=/home/databak/incre/20170322/2017-03-22 resume 18-11-26 continue to restore.

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- copy-back / home/databak/full/20170322 looks at the data again, and all four pieces of data we want to recover have been restored.

> select * from test2

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

+-+

4 rows in set (0.06 sec)

Option additions in backup

There are many options in innobackupex, some of which are quite distinctive, such as stream option,-- slave-info option, which can easily build slave libraries and generate offset information, such as parallel-- parallel, etc., and can also be backed up according to LSN. The option is-- incremental-lsn.

For stream, the default is packaging, which can be combined with pipelines to achieve compression, such as: innobackupex-- defaults-file=/etc/my.cnf-- user=root-- stream=tar / home/databak/full/20170322_2 | gzip > / home/databak/full/20170322_2/20170322_2.tar.gz

Common scenarios in backup

Many times I don't want to back up the entire library, I just want to back up a table, so how to do this?

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- include='test.test2' / home/databak/full/20170322_2 there are a few points to note here. The tool will still scan one by one, but those that do not match will be ignored, which means that the backup situation is the same as the full directory structure, but the specified table will back up the ibd,frm file.

[test] # ll

Total 1036

-rw-r--r-- 1 mysql mysql 8556 Mar 22 18:34 test2.frm

-rw-r--r-- 1 root root 1048576 Mar 22 19:26 test2.ibd

[test] # cd.. / mysql

[mysql] # ll

Total 0 and it is worth complaining that ibdata will also be fully backed up, which is pretty lame if the file is large.

But don't lose faith in this kind of backup, there is a scenario that is very practical. That's the migration table.

Migration table

In the same scenario, if table test2 needs to be copied to another environment, we can use Innobackupex to make a physical backup, and then restore the import to achieve the purpose of migration.

The following command declares that the backup in the specified directory needs to export the object. The direct product of the process of innobackupex-- apply-log-- export / home/databak/full/20170322_2/2017-03-22 MySQL 19-26-46 is the generation of a .exp file, which is a .cfg file in the native version of MySQL.

[test] # ll

Total 1052

-rw-r--r-- 1 root root 16384 Mar 22 19:29 test2.exp

-rw-r--r-- 1 mysql mysql 8556 Mar 22 18:34 test2.frm

-rw-r--r-- 1 root root 1048576 Mar 22 19:26 test2.ibd truncates the data information of the table test2.

> alter table test2 discard tablespace

Query OK, 0 rows affected (0.07 sec) is followed by a physical copy, copying .exp files and .ibd files to the specified directory, and modifying the master permissions.

Then use import to complete the import.

> alter table test2 import tablespace

Query OK, 0 rows affected (0. 00 sec) another thing worth saying is whether this .exp file is necessary or not.

We can just copy .ibd files. There may be some warnings in the new version, so let's do it again.

[test] > alter table test2 discard tablespace

Query OK, 0 rows affected (0.03 sec) also deletes the .exp file you just copied.

Then copy the ibd file to the specified directory and grant permissions

Import tablespace information.

[test] > alter table test2 import tablespace

Query OK, 0 rows affected (0. 00 sec) looked at the data and found that the data came back.

[test] > select * from test2

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

+-+

4 rows in set (0.00 sec) of course, there are still a lot of things to pay attention to in this process.

On how to carry out xtrabackup backup and restore in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.