MySQL Data Backup and Restore

Accidents involving database deletion are now common, and the need for data backup is an extremely important task in enterprise data management.There are also many scenarios and methods for data backup and recovery, and several of the main tools in this series will be demonstrated through cases.

This series will start with logical backup and recovery. The main tools for logical backup are mysqldump/mydumper, among which mydumper can specify multithreaded work. This article introduces mysqldump.

1. mysqldump backup

mysqldump is a logical backup tool that comes with MySQL databases and is a hot standby tool.Its backup results in the creation of insert statements that create libraries, tables, etc., and corresponding tables, based on the parameters set in the database.

Mysqldump parameter options are particularly numerous, you can view the corresponding parameters and instructions () through mysqldump --help

[root@testdb ~]# mysqldump --help
mysqldump  Ver 10.13 Distrib 5.7.25-28, for Linux (x86_64)
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

Common options for mysqldump are as follows

1.1 Back up the specified table

mysqldump can back up a specified single table or multiple tables of a specified library, such as the table structure and data of the test1 table of the testdb Library

/* Back up the test1 table of the testdb Library */
/usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  testdb  test1 > test1.sql

The backup file body is as follows:

Description of main parameters:

 --master-data=2) Records the current binlog information, with 1 and 22 values. If the value equals 1, a CHANGE MASTER statement is added; if the value equals 2, a CHANGE MASTER is also generated, but a comment is added before the statement.
As shown in this example (recommended set to 2), this information can be used for post-configuration setup master-slave synchronization --default-character-set=utf8 sets the character set, indicating the character set is recommended --single-transaction is used with RR isolation level to ensure innodb backup data consistency without locking tables. This parameter is important and must be set in production environments testdb * Library Name Tes1) The name of the table that needs to be backed up. If you need to back up more than one table, you can list the table name directly after it, such as test1 test2

1.2. Back up a single database

mysqldump can back up the specified database, either a single library or multiple libraries, starting with a single library, as follows

/* Back up the entire testdb Library */
 /usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  testdb  > testdb.sql

As a result, the structure and data of all tables under the testdb library are backed up

1.3 Back up multiple libraries

Backing up multiple databases can be done with the following commands

/* Backup monitor library and testdb Library */
/usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  --databases monitor testdb  > mul_db.sql

The main information in the results is as follows, which includes creating and switching Libraries

Parameter description (also explained before other parameters, not repeated here):

--databases can be backed up by filling in the name of the database that needs to be backed up

1.4 Back up all databases

If you want to back up all databases, you can use the following commands:

/*  Back up all databases */ 
 /usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  --all-databases   > all_db.sql

Note: There is no information_schema, performance_schema, sys library information in the backup (MySQL 5.7 and above)

1.5 Other cases

In practice, you may also encounter requirements such as backing up only the table structure, backing up only the data, backing up stored procedures and events, etc. with the following parameters:

--no-data Backup only the table structure, no data, can be abbreviated as -d
 --no-create-info backs up data only, does no t backup table building information, or can be abbreviated as -t
 --routines Backup stored procedures and functions, which can be abbreviated as -R
 --events. Backup events, which can be abbreviated as -E
 --triggers Backup triggers
 --flush-logs Switch logs when backup is complete
 --flush-privileges refresh permissions after backup is complete
 --set-gtid-purged Opens a GTID Library This parameter needs to be set, which can be ON, OFF or AUTO
 --where) Specify criteria, such as exporting 1,000 rows of records per table or exporting records with id<=10 per table, for example, by referring to historical articles
--skip-add-drop-table does not generate a statement to delete the table

1.6 Sample script

Back up all databases, including triggers, events, stored procedures, and refresh log and permissions instances

 /usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8  --routines   --triggers --events --flush-logs --flush-privileges --single-transaction  --all-databases >backup.sql

Note: There is a drop table in the backup, so keep the default if you are sure you need to rebuild the table, otherwise, for insurance reasons, add the--skip-add-drop-table parameter

1.7 Tell me something special

mysqldump can also generate flat files, specify delimiters, and so on. This is a simple situation and can be tested by itself

1.8 Use scenarios

mysqldump is a logical backup using the following main scenarios:

  • Back up some libraries or tables
  • Small-scale Libraries
  • Backups with specified conditions, etc.


2. Simulate data deletion by mistake

Simulate the mistake of deleting a record in the test1 table of the testdb Library

mysql> delete from test1 where name='king of Wu in the Three Kingdoms Era';
Query OK, 1 row affected (0.00 sec)


3. Restore data

Restoring data backed up by mysqldump is easy, just import the backed up files into the database.

3.1 Restore the test1 table

There are three ways to recover:

a) Log into the database and restore with the source command

/*  Restore backup data to rec Library */
mysql> use rec;
Database changed
/*  Restore data  */
mysql> source test1.sql;

b) Recovery using mysql-e

/*  This is equivalent to the first */
[root@testdb ~]#   /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p -e "use rec ; source  test1.sql;"
Enter password: 

c) How to import backup files directly from MySQL

[root@testdb ~]#   /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p   rec  < test1.sql
Enter password: 


a) The restore command is convenient to use, but it is not recommended to restore data directly to the target table when restoring data in the actual production environment (especially when dealing with deleting the restored data by mistake). Instead, it is recommended to restore to other instances or other libraries before importing the records that need to be restored into the target table if they are correct.

b) Be careful if there are instructions to delete libraries or delete tables in the backup file; otherwise, if you choose to restore in the same instance, even if you choose to temporarily restore libraries, and the backup file has use db; and the statements for drop table s, all target tables will be deleted.

3.2 Recover mistakenly deleted records to target table

mysql> insert into  testdb.test1 select * from rec.test1 where  name='king of Wu in the Three Kingdoms Era';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

The record is now restored to the database.

4. Principle of mysqldump

You can see the general process by looking at the backup file, or you can see which SQL is executed by opening the general log.

4.1 View the general log

View Configuration First

Then open genera-log

4.2 Start backup

We'll back up with the previously recommended backup script

/usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8  --routines   --triggers --events --flush-logs --flush-privileges --single-transaction  --all-databases   > all_db.sql

4.3 Turn off general-log

Turning on general-log has a significant (especially noticeable) impact on database performance, so turn off general log after testing is complete.Don't forget, remember.

4.4 View general-log content

Start backing up the contents of specific libraries later

Backup of other libraries is the same, omitted.

Above, if you need to reprint, please indicate the source, thank you)


Backing up and restoring data using the mysqldump tool is almost complete. If you want to supplement or obtain the original map, please contact me (follow Public Number: Database Dry Store).

Tags: MySQL mysqldump Database SQL

Posted on Sat, 21 Mar 2020 19:44:50 -0700 by cobalt30