(go) Unlock 4 correct poses for MySQL backup recovery

This article is based on the Dbaplus community on the 104th issue of the online sharing.

Original: http://dbaplus.cn/news-11-1267-1.html

Instructor Introduction

Feng Yu

Point-and-melt Network Advanced DBA

• Oracle OCM, MySQL OCP;

• Currently engaged in MySQL-related operations and architecture work, good at heterogeneous database interaction.

Share an outline:

1. Mysqldump

2. Mysqlbackup

3. Mysqlhotcopy

4. Xtrabackup/innobackupex

Backup above all, today summarizes several commonly used backup methods, as well as the steps of recovery.

First, mysqldump

In our daily work, we use the mysqldump command to create a dump file in SQL format to back up the database. Or we export data to do data migration, the main preparation and other operations. Mysqldump is a logical backup tool that replicates the original database object definitions and table data to produce a set of executable SQL statements. By default, the INSERT statement is generated, and the output or XML-formatted files of other separators can be generated.

Shell> mysqldump [arguments] > file_name

Let's take a brief look at the daily usage:

To back up all databases:

shell> mysqldump--all-databases > Dump.sql (Information_schema,performance_schema,sys not included, If you want to export it also combined with--skip-lock-tables and--database)

To back up the specified database:

shell> mysqldump--databases db1 DB2 db3 > Dump.sql

When we back up only one of the data can save--databases directly written: mysqldump Test > Dump.sql But there are some subtle differences, if not added, the database dump output does not include the creation of the database and the USE statement, So you can import it directly into a database of other names without this parameter.

Of course we can also just back up a table:

Mysqldump--user [username]--password=[password] [database name] [table name] Table_name.sql

After some simple usage, let's look at a few more parameters:

• Master-data gets the binlog location of the backup data and the Binlog file name, which is used when establishing a replication relationship between instances that are recovered from the backup, which is turned on by default.

• Dump-slave is used to dump data on slave to create a new slave. Because we lock the table when we use mysqldump, in most cases our export operations are usually done on a read-only repository, which is required to get the Relay_master_log_file and Exec_master_log_pos of the main library. But this parameter is only available after 5.7.

• No-data,-D do not export any data, only export database table structure

We have just said that when using mysqldump, we will lock the table, and we'll look at its locking mechanism in detail.

We open two windows, in the first inside execute mysqldump-uroot-pxxxxx--master-data=2--databases dbname >/tmp/dbnamedate +%f.sql then a second window landed in, Using the show process command, you can see that the current dump session is executing.

SELECT/!40001 sql_no_cache/* FROM TABLE_NAME; You can see that this SQL is querying the data in No_cache mode.

Then we executed the select on the same table and found it blocked. The cursor has not returned.

Generally encountered this kind of file, we would like to have a lock it? To verify that we look at the lock information, we can see that the dump process is actually locked.

We open the specific general_log and look at the operation at that time:

4101044 Query FLUSH/!40101 local/tables

4101044 Query FLUSH TABLES with READ LOCK

(Close all open tables, with one read lock for all tables in all databases, until the unlock tables is performed on the display, which is often used for data backup.) ）

4101044 Query SHOW MASTER STATUS

(This is because I used the--master-data=2)

So this time the table will be locked.

If I don't add--master-data parameters (mysqldump-uroot-pxx--databases db >/tmp/dbnamedate +%f.sql) MySQL will display the LOCK TABLES for each table to be backed up Table_name1 read,lock TABLES Table_name2 Read, and there will be no read blocking.

There is no way to lock, in fact, there is the use of--single-transaction to put the operation of the backup in a transaction to proceed.

Mysqldump backup process with the--single-transaction parameter:

If it's a 5.6 version of MySQL

Between backups the same flush TABLES with READ LOCK, then set the transaction level set SESSION TRANSACTION isolation levels repeatable READ, then open a transaction start Transa Ction for backup, this time the process of backup is very interesting, it first created a savepoint, and then the database in order to back up the table, the backup completed and then rolled back to the previous savepoint, to ensure the consistency of the data.

If it's a 5.7 version of MySQL

The operation before the backup is the same, just without the savepoint.

However, no matter which version, only the InnoDB table is in a consistent state. Any other MyISAM table or memory table is useless. The advantage of mysqldump is that it can be viewed or edited very conveniently, and it can also be flexibly restored to previous data. It also does not care about the underlying storage engine, both for support transactions and for tables that do not support transactions. However, it cannot be used as a quick backup for large amounts of data or scalable solutions. If the database is too large, even if the backup step takes less time, it is possible to recover the data very slowly because it involves SQL statements that insert disk I/O, create indexes, and so on. For large-scale backup and recovery, it is more appropriate to physically backup and copy the data files in their original format, which can be quickly restored: if your table is primarily a innodb table, or if you have a innodb and MyISAM table, you can consider using the MySQL mysqlbackup command backup.

Restore operation:

Let's look at the current data:

[Email protected] 11:10:34>select * from t;

+-------+

| ID |

+-------+

| 1 |

+-------+

1 row in Set (0.00 sec)

Backup

mysqldump-uroot [email protected]--master-data=1 test >test.sql

To simulate incremental operations:

[email protected] 11:15:17>insert into T values (2);

Query OK, 1 row Affected (0.00 sec)

[Email protected] 11:15:36>select * from t; +------+ | ID | +------+ | 1 | | 2 | +------+ 2 rows in Set (0.00 sec)

Analog Error Operation:

[Email protected] 11:15:41>truncate table t;

Query OK, 0 rows affected (0.01 sec)

[Email protected] 11:16:14>select * from t;

Empty Set (0.00 sec)

To simulate a recovery operation:

Step 1: Find the log position of the error operation

[Email protected] 11:20:57>show master logs;

[Email protected] (none) 11:21:37>show binlog events in ' mysql-bin.000004 ';

See what you can see is 444.

Step 2: Restore to Backup

[Email protected] 11:16:25>source Test.sql

[email protected] 11:17:26>select from T;

+-------+

| ID |

+-------+

| 1 |

+-------+

1 row in Set (0.00 sec)

Step 3: Because we used the Master-data parameter when we backed up, we can see the last position of the backup, and then apply the intermediate log. See what you can see is 187.

We use Mysqlbinlog to get the operation of this period of time, in fact, we can also use this tool to get the operation using SED to undo the operation.

Mysqlbinlog--start-position=187--stop-position=444 mysql-bin.000004 > Increment.sql

[Email protected] 11:44:37>source/u01/my3307/log/increment.sql [email protected] 11:44:50>select from T; +------+ | ID | +------+ | 1 | | 2 | +------+

Data recovery.

Second, Mysqlbackup

Mysqlbackup is an enterprise-specific backup software provided by Oracle, the full name is MySQL Enterprise backup, and is a fee-based software.

: https://www.mysql.com/products/enterprise/backup.html, can try to download.

Let's take a quick look at the use of this tool.

See all the Help:

I'm just intercepting a small part of this, which is a very long, versatile, full-featured, Oracle-driven backup method.

Full-scale backup

Mysqlbackup--user=root--password=ucjmh--databases= ' t1 '--encrypt-password=1--with-timestamp--backup-dir=/u01/ Backup/backup

Explain the parameters:

• Databases the database to be backed up

• With-timestamp produces a backup directory of the current time. Mysqlbackup This tool requires an empty directory to be backed up. So this will be used.

• Backup-dir the backup directory.

• Compress: Compressed backup This provides a variety of compression methods and compression levels. 1--9, the compression ratio is incremented sequentially.

Backup is a way to back up, there are a number of ways, I will be in a recovery case to use a few of the commonly used.

Backup operations:backup, Backup-and-apply-log, Backup-to-image

Update Operations:apply-log, Apply-incremental-backup

Restore Operations:copy-back, Copy-back-and-apply-log

Validation operation:validate

Single-file backup Operations:image-to-backup-dir, Backup-dir-to-image, List-image, extract

In fact, in most cases, a single file backup, created using the Backup-to-image command, performs better than backup. Buckup This command only executes the initial phase of a full backup process. You need to use the Apply-log command to make the backup consistent by running Mysqlbackup again.

Mysqlbackup--user=root--password=ucjmh--databases= ' t1 '--encrypt-password=1--with-timestamp--backup-dir=/u01/ Backup/2017-04-28_12-49-35/apply-log

Of course you can use Backup-and-apply-log directly, but this time the backup will not be used for the increment.

Incremental backup:

Mysqlbackup--user=root--password=ucjmh--databases= ' t1 '--encrypt-password=1--with-timestamp--backup-dir=/u01/ backup/--incremental--incremental-base=dir:/u01/backup/2017-04-28_12-49-35--incremental-backup-dir=/u01/backup /incremental Backup

This is based on the backup of the last backup, of course, can also be based on a log position after doing.

--incremental: Represents an incremental backup;

--incremental-base: Last fully-prepared directory;

--incremental-backup-dir: Saved directory for incremental backups

Say a little bit more about image backup:

Use the following command to make a backup:

Mysqlbackup--user=root--password=ucjmh--databases= ' t1 '--encrypt-password=1--with-timestamp--backup-dir=/u01/ backup/--backup-image=all.mbi Backup-to-image

After the backup can be very clear to find this than backup to save a lot of space, all the files are in binary way in the All.mbi this file, you can use List-image to see the specific content.

Mysqlbackup--backup-image=/u01/backup/2017-04-28_14-50-17/all.mbi List-image

The same can be used Mysqlbackup--backup-image=/u01/backup/2017-04-28_14-50-17/all.mbi extract to extract the specific content.

Because this is a oracle out of the tool, there is a deep rman shadow in, Level 0, Level 1 backup, encryption, heterogeneous machine restore and other features.

For more parameters, see online Help:

• Https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/backup-commands-single-file.html

Restore operation:

View Current Data

[Email protected] 11:51:32>select * from t;

+-------+

| ID |

+-------+

| 1 |

+-------+

1 row in Set (0.01 sec)

Full-scale backup

Mysqlbackup--user=root [email protected]--databases= ' test '--with-timestamp--backup-dir=/data/backup/backup

To simulate incremental operations:

[Email protected] 11:54:04>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

+-------+

2 rows in Set (0.00 sec)

Incremental backup:

Mysqlbackup--user=root [email protected]--databases= ' test '--with-timestamp--backup-dir=/data/backup/-- Incremental--incremental-base=dir:/data/backup/2017-04-29_11-53-20--incremental-backup-dir=/data/backup/ Incremental backup

Simulate no backup operation:

[Email protected] 11:57:10>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

| 3 |

+-------+

3 Rows in Set (0.00 sec)

Analog Error Operation:

[Email protected] 11:57:17>truncate table t; Query OK, 0 rows affected (0.01 sec)

To simulate a recovery operation:

Step 1: Find the log position of the error operation

[Email protected] 11:58:06>show master logs;

[Email protected] 11:58:18>show binlog events in ' mysql-bin.000001 ';

1333

Step 2: Restore full Volume

To detect and apply the log:

Mysqlbackup--backup-dir=/data/backup/2017-04-29_11-53-20 Apply-log

Step 3: Apply increments

Mysqlbackup--backup-dir=/data/backup/2017-04-29_11-53-20--incremental-backup-dir=/data/backup/incremental/ 2017-04-29_11-55-54 Apply-incremental-backup

Step 4: Physical file copy and restore

Mysqlbackup--backup-dir=/data/backup/2017-04-29_11-53-20 Copy-back

When data is restored to backup:

[Email protected] 12:09:49>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

+-------+

2 rows in Set (0.00 sec)

After the recovery is complete, the data directory will generate Backup_variables.txt files (in fact, when the backup is already there), find the time of the backup log position, and then recover from the Binlog no backup.

binlog_position=mysql-bin.000001:1076 mysqlbinlog mysql-bin.000001--start-position=1076--stop-position=1333-vv >increment.sql

[Email protected] 12:14:07>source/u01/my3307/log/increment.sql [email protected] 12:14:16>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

| 3 |

+-------+

3 Rows in Set (0.00 sec)

Data recovery.

Sort through the procedure to see how the Recovery works:

First Detect and apply the fully-prepared transaction log file (this is because I use Backup instead of Backup-and-apply-log when I back up) and then apply the incremental log based on the full standby. This time, if there are multiple incremental backups, it can be applied (based on the LSN point backwards). After all the application is completed, it is a database that can be directly CP.

Personal feeling this tool is better than xtrabackup, but Xtrabackup is open source, so market share will be big, will be more famous, more people use it.

Third, mysqlhotcopy

Mysqlhotcopy Use the lock tables, flush tables, and CP or SCP to quickly back up the database. It is the quickest way to back up a database or a single table, completely physical, but only for backup MyISAM storage engines and archive engines. and is a server command that can only be run on the same machine as the database directory. Unlike mysqldump backups, mysqldump is a logical backup, which is a SQL statement executed at backup time. You need to install the appropriate software dependency package before using the Mysqlhotcopy command. Because this function is very weak, we simply introduce a how to use:

Back up a library

Mysqlhotcopy db_name [/path/to/new_directory]

Back up a single table

Mysqlhotcopy db_name./table_name//path/to/new_directory

More detailed use can be viewed using the Perldoc mysqlhotcopy.

Iv. Xtrabackup/innobackupex

Percona Xtrabackup is an open source utility for MySQL-based hot backup that backs up Innodb,xtradb,myisam storage engine tables from 5.1 to 5.7, and Xtrabackup has two main tools: Xtrabackup , Innobackupex.

(1) Xtrabackup can only back up InnoDB and xtradb two data tables, but not myisam data tables

(2) The Innobackupex encapsulates the Xtrabackup, which is a script wrapper, so the InnoDB and MyISAM can be backed up at the same time, but a read lock is required to handle the MyISAM.

First of all, let's start with a brief look at how Xtrabackup works. Xtrabackup based on the InnoDB Crash-recovery (instance recovery) function, copy the InnoDB physical files (this time the data consistency is not satisfied), and then proceed based on the Redo log recovery, to achieve data consistency.

Detailed information can be parameter https://www.percona.com/doc/percona-xtrabackup/LATEST/how_xtrabackup_works.html I will not translate it.

Let's take a brief look at the specific use in our daily work:

Fully prepared:

Xtrabackup--backup--target-dir=/data/backup/base

Can see first

During the backup process, you can see that a lot of output shows that the data file is being copied, and that the log file thread repeatedly scans the log file and replicates.

Similarly, it also outputs the current Binlog filename and position, which can be used to build a master-slave if Gtid (also output). The last line will definitely be the copy of your LSN. This is because every time a backup is started, 170429 12:54:10 >> Log scanned up to (1676085) are recorded, and then the file is copied, in general, the larger the database is, the longer it takes to copy the files. So the general situation during this period will have a new operation, so that all files may also record is not a point in time data, in order to solve the problem of data, Xtrabackup will start a background process to observe the MySQL transaction log 1 times per second until the end of the backup. and record the changes in the transaction log. We know that the log of things will be reused (redo log), so this process will write redolog to its own log file Xtrabackup_log, the background monitoring process will record all transaction log changes, to ensure data consistency.

Incremental backup:

When we do a full-scale backup, we generate xtrabackup_checkpoints files in the directory, which records the LSN and backup methods, and we can make incremental backups based on this time.

$cat xtrabackup_checkpoints

Backup_type = full-backuped

FROM_LSN = 0

TO_LSN = 1676085

LAST_LSN = 1676085

Compact = 0

Recover_binlog_info = 0

Xtrabackup--backup--target-dir=/data/backup/inc1--incremental-basedir=/data/backup/base

This time xtrabackup is also going to open the Xtrabackup_checkpoints file to view the information for the last backup. This information is also recorded by the xtrabackup_checkpoints to check for incremental backups at this time.

$cat xtrabackup_checkpoints

Backup_type = Incremental

FROM_LSN = 1676085

TO_LSN = 1676085

LAST_LSN = 1676085

Compact = 0

Recover_binlog_info = 0

This also means that you can continue incremental backups on incremental backups.

Similarly, Xtrabackup also supports operations such as compression (--compress), encryption (--encrypt), parallel (--parallel), but unlike Mysqlbackup, which has no simultaneous backup binlog, And Mysqlbackup is backed up by Binlog.

Let's simulate a recovery process in-depth understanding of the principle.

View Current Data:

[email protected] 03:04:33>select from T;

+-------+

| ID |

+-------+

| 1 |

+-------+

1 row in Set (0.00 sec)

Full-scale backup

$xtrabackup--backup--target-dir=/data/backup/base

Simulating incremental data

[email protected] 03:07:16>select from T;

+-------+

| ID |

+-------+

| 1 |

| 2 |

+-------+

2 rows in Set (0.00 sec)

To make an incremental backup:

$xtrabackup--backup--target-dir=/data/backup/inc1--incremental-basedir=/data/backup/base

Simulate no backup operation:

[Email protected] 03:09:42>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

| 3 |

+-------+

3 Rows in Set (0.00 sec)

Analog Error Operation:

[Email protected] 03:09:45>truncate table t; Query OK, 0 rows Affected (0.00 sec)

To simulate a recovery operation:

Step 1: Find the log position of the error operation

[Email protected] 03:10:19>show master logs;

[Email protected] 03:10:47>show binlog events in ' mysql-bin.000001 ';

1333

We need to do a prepare operation for the full-volume, incremental backup separately.

Xtrabackup--prepare--apply-log-only--target-dir=/data/backup/base

Incremental

Xtrabackup--prepare--apply-log-only--target-dir=/data/backup/base \--incremental-dir=/data/backup/inc1

If we use its own restore command, we need to clear the data directory first. Or you'll make a mistake like this.

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

170429 15:37:19 innobackupex:starting The copy-back operation

Important:please Check that the Copy-back run completes successfully.

At the end of a successful copy-back run Innobackupex prints "completed ok!".

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id:8ec05b7) Original data directory/u01 /my3307/data is not empty!

Of course, most of us do not operate on the original instance, we will restore the corresponding backup on the odd instance, and then export the imported to the wrong operation instance. Here we directly clear the directory, and then run again to view the recovered data:

[Email protected] 03:41:56>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

+-------+

2 rows in Set (0.00 sec)

The same restored directory will be more than two files, one is Xtrabackup_binlog_pos_innodb, and the other is xtrabackup_info. You can see your last log,pos in these two files. LSN can also be seen in info. We perform a replay of the Binlog based on this POS and restore the data that was not backed up in Binlog.

1076

$mysqlbinlog mysql-bin.000001--start-position=1076--stop-position=1333-vv >increment.sql

[Email protected] 03:51:25>source/u01/my3307/log/increment.sql [email protected] 03:51:34>select * from t;

+-------+

| ID |

+-------+

| 1 |

| 2 |

| 3 |

+-------+

3 Rows in Set (0.00 sec)

This data recovery is complete.

• Https://www.percona.com/doc/percona-xtrabackup/LATEST/backup_scenarios/full_backup.html

V. Direct replication of the entire database directory

MySQL also has a very simple backup method, which is to copy the database files in MySQL directly. This is the simplest and fastest method. However, before you do this, you will need to stop the server before you can guarantee that the data for the database will not change during replication. If there is data written during the copying of the database, the data will be inconsistent. This situation is possible in the development environment, but it is difficult to allow backup servers in a production environment.

Note: This approach does not apply to tables of the InnoDB storage engine, but is convenient for MyISAM storage engine tables. At the same time, the MySQL version is best when restoring. The only reason to mention this is because when there is an outage window, when the master-slave is built, this is often the fastest.

General production environment backup will use Percona-xtrabackup or mysqlbackup, combined with their own situation, select the appropriate backup strategy, timely to verify the effectiveness of the backup.

Q&a

Q1: With Innobackupex backup MySQL5.5 version of the data, restore the time with backu5.6 recovery, there is no ibdata1 found, what is the reason?

A1: After a cross-version recovery, the meta-information for the recovered data is still earlier, and you need to perform the Mysql_upgrade before starting the server. This is actually an upgrade problem, we can certainly use Innobackupex to do the upgrade operation, the same mysqlbackup is also possible. Because sometimes our library is too large to import the export to upgrade the operation.

Q2: Do you speak the right method for clustering? If not suitable for the cluster, what method?

A2: Of course, it's all the same. The principle is only to copy the data of the database, master-slave, PXC, MHA and so on are used in many environments.

Q3: Production environment, 1T data volume with what backup good?

A3: My suggestion is that you can use the Mysqlbackup, compare down, this speed is very fast, but when your data volume reached a T, you should also consider how to divide the library table, when this is done, is also piecemeal, can be the backup and the normal load are distributed to multiple servers.

Live link

If you need to listen to the live broadcast, please poke:

Https://m.qlchat.com/topic/260000366578461.htm?isGuide=Y

Password: 222

(go) Unlock 4 correct poses for MySQL backup recovery