Shulou(Shulou.com)06/01 Report--

1.mysqldump

2.mysqlbackup

3.mysqlhotcopy

4.xtrabackup/innobackupex

5.cp

Backup is everything. Today, summarize several common backup methods, as well as the recovery steps.

1.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 can export the data and do some operations such as data migration, master / standby construction and so on. Mysqldump is a logical backup tool that copies original database object definitions and table data to produce a set of executable SQL statements. By default, insert statements are generated, and you can also generate output from other delimiters or files in XML format.

Shell > mysqldump [arguments] > file_name

Let's take a brief look at everyday usage:

Back up all databases:

Shell > mysqldump-- all-databases > dump.sql (does not include INFORMATION_SCHEMA,performance_schema,sys, but also combines-- skip-lock-tables and-- database if you want to export)

Back up the specified database:

Shell > mysqldump-- databases db1 db2 db3 > dump.sql

It can be omitted when we back up only one data-- databases is directly written as: mysqldump test > dump.sql, but there are some slight differences. If you don't add it, the database dump output does not include creating the database and use statements, so you can import it directly into the database with other names without adding this parameter.

Of course, we can just back up a certain table:

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

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

-- master-data obtains the Binlog location and Binlog file name of the backup data, which is used to establish replication relationships between instances restored by backup. This parameter is enabled by default.

-- dump-slave is used to dump data on slave to create a new slave. Because we lock tables when using mysqldump, in most cases, our export operation is usually done on the read-only standby library. This parameter is needed to obtain the Relay_Master_Log_File and Exec_Master_Log_Pos of the main library, but this parameter will only be available after 5.7s.

-no-data,-d do not export any data, only database table structure

We just said that the table will be locked when using mysqldump. Let's take a look at its locking mechanism in detail.

We open two windows and execute mysqldump-uroot-pxxxxx-- master-data=2-- databases dbname > / tmp/dbname date +% F. SQL in the first one.

Then log in to the second window, and use the command of show process to see that the session of dump is currently being executed.

SELECT / *! 40001 SQL_NO_CACHE * / * FROM table_name; can see that this sql is querying data in no_cache mode.

Then we execute select on the same table and find that it is blocked. The cursor never returns.

Usually when we encounter this kind of document, we wonder if there is a lock.

To verify that we take a look at the lock information, we can see that the dump process is actually locked.

Let's open the specific general_log and take a look at what we did at that time:

4101044 Query FLUSH / *! 40101 LOCAL * / TABLES

4101044 Query FLUSH TABLES WITH READ LOCK (close all open tables and add a read lock to all tables in the database until unlock tables is executed explicitly, which is often used when backing up data. )

4101044 Query SHOW MASTER STATUS (this is because I used-- master-data=2)

So the watch will be locked at this time.

If I don't add the-- master-data parameter (mysqldump-uroot-pxx-- databases db > / tmp/dbname date +% F. SQL) mysql will show execution for each table to be backed up

LOCK TABLES table_name1 READ,LOCK TABLES table_name2 READ

And there will be no blocking of reading.

Is there any way not to lock it? in fact, there is also a way to use-- single-transaction to put the backup operation in a transaction.

Mysqldump backup process with-- single-transaction parameter:

If it is version 5.6 of mysql

Similarly, between backups, first FLUSH TABLES WITH READ LOCK, then set the transaction level SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, and then open a transaction START TRANSACTION for backup. At this time, the backup process is very interesting. It first creates a savepoint, then backs up the tables in the database in turn, and then rolls back to the previous savepoint after the backup is completed to ensure data consistency.

If it is version 5.7of mysql

The operation is the same before backup, except without 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 is easy to view or edit, and it also has the flexibility to recover previous data. It also doesn't care about the underlying storage engine, which applies both to tables that support transactions and to tables that don't support transactions. However, it cannot be used as a fast backup of large amounts of data or as a scalable solution. If the database is too large, even if the backup step does not take too long, it is possible that the recovery of the data will be very slow because it involves SQL statements that are inserted into disk Imando O, create indexes, and so on.

For large-scale backup and recovery, it is more appropriate to make a physical backup and copy the data files in their original format for quick recovery: if your table is mainly an InnoDB table, or if you have an InnoDB and MyISAM table, consider using the MySQL mysqlbackup command to back up

Restore operation:

Let's take a look at the current data:

Dbadmin@test 11:10:34 > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Backup

Mysqldump-uroot-proot@1234-- master-data=1 test > test.sql

Simulate incremental operations:

Dbadmin@test 11:15:17 > insert into t values (2)

Query OK, 1 row affected (0.00 sec)

Dbadmin@test 11:15:36 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Simulation misoperation:

Dbadmin@test 11:15:41 > truncate table t

Query OK, 0 rows affected (0.01 sec)

Dbadmin@test 11:16:14 > select * from t

Empty set (0.00 sec)

Simulate the recovery operation:

Step 1: find the misoperation of log position

Dbadmin@test 11:20:57 > show master logs

Dbadmin@ (none) 11:21:37 > show binlog events in 'mysql-bin.000004'

As you can see, it's 444.

Step 2:

Restore to backup

Dbadmin@test 11:16:25 > source test.sql

Dbadmin@test 11:17:26 > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Step 3:

Because we used the parameters of master-data when we backed up, we can see the last location of the backup directly, and then apply the middle log.

You can see that it is 187.

We use mysqlbinlog to get the operation for this period of time, in fact, we can also use this tool to get the operation and use sed for undo operation.

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

Dbadmin@test 11:44:37 > source / u01/my3307/log/increment.sql

Dbadmin@test 11:44:50 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

At this point, the data is restored.

2.mysqlbackup

It is an enterprise-specific backup software provided by ORACLE Company. Its full name is MySQL Enterprise Backup, and it is a paid software. Download address: https://www.mysql.com/products/enterprise/backup.html can be downloaded on trial. Let's take a brief look at the use of this tool.

View all the help:

I only intercepted a small part of it here, which is a long help, many parameters and complete functions. It is the backup method pushed by the official oracle host.

Full backup

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

Explain the parameters:

-- the database to be backed up by databases

With-timestamp produces a backup directory at the current time. The mysqlbackup tool requires an empty directory to make a backup. So this will be commonly used.

-- the directory backed up by backup-dir

-- compress: compressed backup this provides a variety of compression methods and compression levels. 1Murray 9, the compression ratio increases sequentially.

Backup is the way of backup.

There are several ways, and I will use all the common ones in a recovery case.

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 with the backup-to-image command, performs better than backup. The buckup command only executes the initial phase of a complete backup process. You need to use the apply-log command by running mysqlbackup again to make the backup consistent.

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

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

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-288 12-49-35-- incremental-backup-dir=/u01/backup/incremental backup

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

-- incremental: stands for incremental backup

-- incremental-base: the last complete directory

-- incremental-backup-dir: the saved directory of the incremental backup

Tell me a little more about image backup:

You can make a backup using the following command

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

After backup, you can clearly find that this saves a lot of space than backup, put all the files in the all.mbi file in a binary way, you can use list-image to view the specific content.

Mysqlbackup-- backup-image=/u01/backup/2017-04-288 14-50-17/all.mbi list-image

The same can be used.

Mysqlbackup-- backup-image=/u01/backup/2017-04-288 14-50-17/all.mbi extract

To extract the specific content.

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

For more parameters, please see online help:

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

Restore operation:

View current data

Dbadmin@test 11:51:32 > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.01 sec)

Full backup

Mysqlbackup-user=root-password=root@1234-databases='test'-with-timestamp-backup-dir=/data/backup/ backup

Simulate incremental operations:

Dbadmin@test 11:54:04 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Incremental backup:

Mysqlbackup-- user=root-- password=root@1234-- 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:

Dbadmin@test 11:57:10 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

Simulation misoperation:

Dbadmin@test 11:57:17 > truncate table t

Query OK, 0 rows affected (0.01 sec)

Simulate the recovery operation:

Step 1: find the misoperation of log position

Dbadmin@test 11:58:06 > show master logs

Dbadmin@test 11:58:18 > show binlog events in 'mysql-bin.000001'

1333

Step 2: full recovery

Detect and apply logs:

Mysqlbackup-- backup-dir=/data/backup/2017-04-29011-53-20 apply-log

Step 3: application increment

Mysqlbackup-- backup-dir=/data/backup/2017-04-2911-53-20-- incremental-backup-dir=/data/backup/incremental/2017-04-29011-55-54 apply-incremental-backup

Step 4: physical file replication and restore

Mysqlbackup-- backup-dir=/data/backup/2017-04-29011-53-20 copy-back

When the data is restored to the backup:

Dbadmin@test 12:09:49 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

After the recovery is completed, backup_variables.txt files will be generated in the data directory (in fact, these files already exist at the time of backup), find the log position at the time of backup, and then restore the data without backup from binlog.

Binlog_position=mysql-bin.000001:1076

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

Dbadmin@test 12:14:07 > source / u01/my3307/log/increment.sql

Dbadmin@test 12:14:16 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

At this point, the data is restored.

Comb through the steps to understand how recovery works:

First detect and apply the full transaction log file (this is because I used backup instead of backup-and-apply-log when backing up), and then apply incremental log based on full availability. At this time, it is also possible to have multiple incremental backups (based on the backward application of LSN points). After all the applications are completed, there will be a database that can be directly cp.

Personally, I think this tool is easier to use than xtrabackup, but xtrabackup is open source, so it has a large market share, it is more famous, and more people use it.

3.mysqlhotcopy

Mysqlhotcopy uses lock tables, flush tables, and cp or scp to quickly back up the database. It is the fastest way to back up a database or a single table. it belongs to physical backup, but it can only be used to back up MyISAM storage engine and ARCHIVE engine, and it is a server command that can only run on the machine where the database directory is located. Unlike mysqldump backup, mysqldump is a logical backup, which is a sql statement executed. The corresponding software dependency package needs to be installed before using the mysqlhotcopy command.

Because this function is very weak, we will only briefly introduce how to use it:

Back up a library

Mysqlhotcopy db_name [/ path/to/new_directory]

Back up a table

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

For more detailed use, you can use perldoc mysqlhotcopy to view

4.xtrabackup/innobackupex

Percona XtraBackup is an open source MySQL-based hot backup utility that backs up tables from the InnoDB,XtraDB,MyISAM storage engine from versions 5.1 to 5.7.

Xtrabackup has two main tools: xtrabackup and innobackupex

(1) xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables.

(2) innobackupex encapsulates xtrabackup, which is a script package, so you can back up and process innodb and myisam at the same time, but you need to add a read lock when processing myisam.

First of all, let's take a brief look at how xtrabackup works. Xtrabackup is based on innodb's crash-recovery (instance recovery) function, first copy innodb the physical files (at this time the consistency of the data cannot be satisfied), and then restore based on redo log to achieve the consistency of the data. Detailed information can be parameter https://www.percona.com/doc/percona-xtrabackup/LATEST/how_xtrabackup_works.html I will not translate.

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

Complete:

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

You can see first.

During the backup process, you can see a lot of output showing that the data files have been copied, and the log file thread repeatedly scans the log files and copies.

Similarly, it also outputs the current binlog filename and position, and if there is a gtid (which will also be output), it can be used to build a master / slave. The last line must be the message that your lsn has been copy.

This is because every time you start a backup, you record 170429 12:54:10 > > log scanned up to (1676085), and then start copying files. Generally speaking, the larger the database, the longer it takes to copy files, so there are usually new operations during this period, so all files may not record data at a point in time.

To solve the data problem, XtraBackup starts a background process to observe mysql's transaction log once per second until the backup is completed. And record the changes in the transaction log. We know that transaction logs are redolog, so this process will write redolog to its log file xtrabackup_log, and this background monitoring process will record all transaction log changes to ensure data consistency.

Incremental backup:

When we have done a full backup, we will generate xtrabackup_checkpoints files in the directory, which records the lsn and backup method, and we can do incremental backups based on this full backup.

$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

At this time, xtrabackup also opened the xtrabackup_checkpoints file to check the information of the last backup. At this time, the xtrabackup_checkpoints that went to check the incremental backup also recorded this information.

$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.

The same xtrabackup also supports compression (--compress), encryption (--encrypt), parallel (--parallel) and other operations, but unlike mysqlbackup, there is no backup binlog at the same time, while mysqlbackup backs up binlog.

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

View current data:

Dbadmin@test 03:04:33 > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Full backup

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

Analog incremental data

Dbadmin@test 03:07:16 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Make an incremental backup:

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

Simulate no backup operation:

Dbadmin@test 03:09:42 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

Simulation misoperation:

Dbadmin@test 03:09:45 > truncate table t

Query OK, 0 rows affected (0.00 sec)

Simulate the recovery operation:

Step 1: find the misoperation of log position

Dbadmin@test 03:10:19 > show master logs

Dbadmin@test 03:10:47 > show binlog events in 'mysql-bin.000001'

1333

We need to prepare both the full backup and the incremental backup.

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

Increment

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

-- incremental-dir=/data/backup/inc1

If we use its own restore command, we have to empty the data directory first. Otherwise, the following error will be reported

$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 our data will not be operated on the original instance, and the corresponding backup will be restored on the Qitar instance, and then exported and imported to the misoperated instance. Here, we directly clear the directory, and then run it again to view the recovered data:

Dbadmin@test 03:41:56 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

There will be two more files in the same restored directory, one is xtrabackup_binlog_pos_innodb and the other is xtrabackup_info. You can see your last log,pos in both files. You can also see lsn in info. Based on this pos, we replay the binlog to recover the data that has not been backed up in the binlog.

1076

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

Dbadmin@test 03:51:25 > source / u01/my3307/log/increment.sql

Dbadmin@test 03:51:34 > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

So far, the data recovery is complete.

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

5. Copy the entire database directory directly

MySQL has a very simple backup method, which is to copy the database files in MySQL directly. This is the simplest and fastest way.

Before that, however, stop the server so that the data in the database will not change during replication. If there are still data writes in the process of copying the database, it will cause data inconsistency. This is fine in a development environment, but it is difficult to allow backup servers in a production environment.

Note: this method does not apply to tables of the InnoDB storage engine, but is convenient for tables of the MyISAM storage engine. At the same time, the version of MySQL should be the same when restoring.

This is only mentioned because when there is a shutdown window, when building a master and slave, this is often the fastest.

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.