Mysql database backup and recovery - using MySQL dump records

brief introduction

Recently, there is a new requirement to make MySQL backup into a visual interface for operation, use code to call MySQL dump program process backup, and use MySQL program process recovery.

Because a lot of instructions are used, we refer to the information found on the Internet and hereby record it

If there is more time in the future, post the solution ideas and codes for MySQL visual backup. Let's talk about it later

mysql dump client can be used to dump database or collect database for backup or transfer data to another sql Server (not necessarily a mysql server). Dump contains sql statements that create tables and / or load tables.

  • If you are backing up on the server and the tables are all myisam tables, you should consider using mysqlhotcopy because you can backup and restore faster.


This paper introduces mysql data backup and recovery from three parts:

  • First, the basic knowledge of mysql;
  • Second, an example of data backup and recovery;
  • Third, the specific parameters of mysqldump are described.

1, Basic knowledge of MySql

1. Connect to mysql

$ Format: mysql -h Host address -u user name -p User password
  1. Connect to MYSQL on this computer
# Press enter to prompt you to enter your password. If MYSQL has just been installed, the super user root does not have a password, so press enter to enter MYSQL. The prompt of MYSQL is: MYSQL >
$ mysql -uroot -p
  1. Connect to MYSQL on remote host
# Suppose the IP address of the remote host is, the user name is root and the password is 12356. Type the following command:
$  mysql -h127.0.0.1 -uroot -p12356
# (Note: u and root do not need to add spaces, and others are the same)
  1. Exit MYSQL command
# Exit command
$ exit && \q

2. Common commands:

1,Display database list:
$ show databases;
# At the beginning, there were only two databases: MySQL and test. MySQL library is very important. It contains MySQL system information. We actually use this library to change passwords and add users.

2,Display data tables in the Library:
$ use mysql; // Open library
# Show all tables
$ show tables;

3,Displays the structure of the data table:
$ describe Table name;
# Abbreviation: desc table name

4,Database creation:
$ create database Library name;

5,Table creation:
use Library name;
$ create table Table name (Field setting list);

6,Delete database and delete table:
$ drop database Library name;

$ drop table Table name;
7,Clear the records in the table:
$ delete from Table name;

8,Show records in table:
$ select * from Table name;

9,mysqldump Program help command
$ mysqldump --help

2, Data backup and recovery

1. Backup: exporting data from database:

  $ mysqldump -h link ip -P(Capitalize)port -u user name -p Password database name > xxxx.sql(route)

Example: mysqldump - h127.0.0.1 - p3307 - uroot - p8888 test > test.sql;

2. Backup export example:

1. Export data and table structure - return all the data and table structure and data in a specific database and a specific table

$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 test up_subjects > test.sql

2. Export table structure without exporting table data - only return the table structure of a specific table in a specific database, do not return data, and add "- d" command parameter

$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 -d test up_subjects > test.sql

3. Export table structure and table data meeting the top conditions - only the table structure and data meeting the specific conditions of a specific table in a specific database are returned

$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 test up_subjects --where="ctime>'2017-01-01' and ctime<'2017-06-30'" >test.sql

4. Export data without exporting table structure - only return the data of a specific table in a specific database, do not return table structure, and add "- t" command parameter

$ mysqldump -uroot -h127.0.0.1 -padmin -t -P3306 test up_subjects >test.sql

5. Export the table structure and data of all tables in a specific database, and add the "– databases" command parameter

$ mysqldump -uroot -h127.0.0.1 -padmin -P3306 --databases test > test.sql

3. Restore imported database data:

Imports the exported local file into the specified database

1. System command line

# Format: mysql -h link IP - p (in uppercase) port - u user name - p password database name < D: xx.sql (Lu Jin) 
$ mysql -uusername -ppassword test < test.sql

2. Or mysql command line

# Select database
user test;
# Import sql
source test.sql;

3. How to restore the entire database:

$ mysql -uroot -h127.0.0.1 -padmin -P3306 < test.sql

4. Specific recovery examples:

1. Log in to the MySQL server first and switch the data to be imported. The specific commands are as follows:

mysql> use test;

mysql> source /home/test/test.sql

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 9 rows affected (0.00 sec)

Records: 9 Duplicates: 0  Warnings: 0

Note: there are 9 lines of affected records, 0 lines of duplicate records and 0 warning records

2. Use the system command line directly

$ mysql -uroot -h127.0.0.1 -padmin -P3306 test < /home/test/test.sql

3, mysqldump parameter description

  1. mysqldump character set settings
$ mysqldump -uusername -ppassword --default-character-set=gb2312 db1 table1 > tb1.sql

mysql dump client can be used to dump database or collect database for backup or transfer data to another sql Server (not necessarily a mysql server). Dump contains sql statements that create tables and / or load tables.

If you are backing up on the server and the tables are myisam tables, you should consider using mysqlhotcopy , because backup and recovery can be done faster.

  • You can use mysqldump – help to view all parameters
$ mysqldump --help

1,–all-databases , -A

Export all databases.

$ mysqldump -uroot -p --all-databases

2,–all-tablespaces , -Y

Export all tablespaces.

$ mysqldump -uroot -p --all-databases --all-tablespaces

3,–no-tablespaces , -y

No tablespace information is exported.

$ mysqldump -uroot -p --all-databases --no-tablespaces


Add a drop database statement before each database is created.

$ mysqldump -uroot -p --all-databases --add-drop-database


Add a drop data table statement before each data table is created. (it is on by default. Use -- skip add drop table to cancel the option)

# (add drop statement by default)
$ mysqldump -uroot -p --all-databases
# (cancel drop statement)
$ mysqldump -uroot -p --all-databases –skip-add-drop-table


Before each table is exported, add LOCK TABLES and then UNLOCK TABLE. (it is on by default. Use -- skip add locks to cancel the option)

# (add LOCK statement by default)
$ mysqldump -uroot -p --all-databases
# (cancel LOCK statement)
$ mysqldump -uroot -p --all-databases –skip-add-locks


Allows the creation of column names that are keywords. This is done by prefixing the table name to each column name.

$ mysqldump -uroot -p --all-databases --allow-keywords


Add 'STOP SLAVE' before 'CHANGE MASTER' and 'START SLAVE' at the end of the export.

$ mysqldump -uroot -p --all-databases --apply-slave-statements


Directory of character set files

$ mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets


Additional comment information. It is on by default and can be cancelled with -- skip comments

#  (default record comment)
$ mysqldump -uroot -p --all-databases
# (uncomment)
$ mysqldump -uroot -p --all-databases --skip-comments   


The exported data will be compatible with other databases or older versions of MySQL. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb and no_key_options,no_tables_options,no_field_options, etc. to use several values, separate them with commas. It is not guaranteed to be fully compatible, but as far as possible.

$ mysqldump -uroot -p --all-databases --compatible=ansi


Export less output information (for debugging). Remove comments, head and tail structures. You can use options: - skip add drop table -- skip add locks -- skip comments -- skip disable keys

$ mysqldump -uroot -p --all-databases --compact

13,–complete-insert, -c

Use the full insert statement (including the column name). This can improve the insertion efficiency, but it may be affected by max_ allowed_ The insertion failed due to the influence of the packet parameter.

$ mysqldump -uroot -p --all-databases --complete-insert

14,–compress, -C

Enable compression to pass all information between the client and the server

$ mysqldump -uroot -p --all-databases --compress

15,–create-options, -a

Include all MySQL attribute options in the CREATE TABLE statement. (on by default)

$ mysqldump -uroot -p --all-databases

16,–databases, -B

Export several databases. All parameters after the parameter are treated as database names.

$ mysqldump -uroot -p --databases test mysql


Output debug information for debugging. The default value is: d:t,/tmp/mysqldump.trace

$ mysqldump -uroot -p --all-databases --debug
# Default value
$ mysqldump -uroot -p --all-databases --debug=" d:t,/tmp/debug.trace"


Check the memory and open file instructions and exit.

$ mysqldump -uroot -p --all-databases --debug-check


Output debugging information and exit

$ mysqldump -uroot -p --all-databases --debug-info


Set the default character set. The default value is utf8

$ mysqldump -uroot -p --all-databases --default-character-set=utf8


Data is exported in INSERT DELAYED mode

$ mysqldump -uroot -p --all-databases --delayed-insert


Delete logs after master backup. This parameter will automatically activate -- master data.

$ mysqldump -uroot -p --all-databases --delete-master-logs


For each table, use / *! 40000 ALTER TABLE tbl_name DISABLE KEYS /; And /! 40000ALTER TABLE tbl_name ENABLE KEYS */; Statement references an INSERT statement. This allows you to import the dump file faster because it creates the index after inserting all rows. This option is only applicable to MyISAM table. It is open by default.

$ mysqldump -uroot -p --all-databases


This option appends the binlog location and file name of the primary to the file of the exported data (show slave status). When set to 1, it will be output to the data file with the CHANGE MASTER command; When set to 2, a comment is added before change. This option will turn on -- lock all tables unless -- single transaction is specified. This option automatically turns off the -- lock tables option. The default value is 0.

$ mysqldump -uroot -p --all-databases --dump-slave=1

$ mysqldump -uroot -p --all-databases --dump-slave=2


This option appends the location and file name of the binlog of the current server to the output file (show master status). If it is 1, the CHANGE MASTER command will be output; If it is 2, add annotation information before the output CHANGE MASTER command. This option will turn on the -- lock all tables option unless -- single transaction is also specified (in this case, the global read lock obtains a short time at the beginning of export; for other contents, refer to the -- single transaction option below). This option automatically turns off the -- lock tables option.

$ mysqldump -uroot -p --host=localhost --all-databases --master-data=1;

$ mysqldump -uroot -p --host=localhost --all-databases --master-data=2;

26,–events, -E

Export events.

$ mysqldump -uroot -p --all-databases --events

27,–extended-insert, -e

Use INSERT syntax with multiple VALUES columns. This makes the export file smaller and speeds up the import. The default is on. Use -- skip extended INSERT to cancel the option.

$ mysqldump -uroot -p --all-databases
# (cancel option)
$ mysqldump -uroot -p --all-databases--skip-extended-insert   


The given field is ignored in the export file. Used with the -- tab option and cannot be used with the -- databases and -- all databases options

$ mysqldump -uroot -p test test --tab="/home/mysql" --fields-terminated-by="#"


Each field in the output file is wrapped with the given character. Used with the -- tab option and cannot be used with the -- databases and -- all databases options

$ mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#"


Each field in the output file is selectively wrapped with a given character. Used with the -- tab option and cannot be used with the -- databases and -- all databases options

$ mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#" --fields-optionally-enclosed-by  ="#"


Each field in the output file ignores the given character. Used with the -- tab option and cannot be used with the -- databases and -- all databases options

$ mysqldump -uroot -p mysql user --tab="/home/mysql" --fields-escaped-by="#"


Refresh the log before starting the export. Please note: if you export multiple databases at a time (using the option -- databases or -- all databases), the logs will be refreshed one by one. In addition to using -- lock all tables or -- master data. In this case, the log will be refreshed once, and the corresponding tables will be locked at the same time. Therefore, if you intend to export and refresh logs at the same time, you should use -- lock all tables or -- master data and -- flush logs.

$ mysqldump -uroot -p --all-databases --flush-logs


After exporting the mysql database, issue a FLUSH PRIVILEGES statement. For proper recovery, this option should be used at any time when exporting mysql database and relying on mysql database data.

$ mysqldump -uroot -p --all-databases --flush-privileges


Ignore SQL errors during export.

$ mysqldump -uroot -p --all-databases --force


Display help and exit.

$ mysqldump --help


Export BINARY string fields in hexadecimal format. You must use this option if you have BINARY data. The affected field types include BINARY, VARBINARY and BLOB.

$ mysqldump -uroot -p --all-databases --hex-blob

38,–host, -h

Host information to be exported

$ mysqldump -uroot -p --host=localhost --all-databases


The specified table is not exported. When you specify to ignore multiple tables, you need to repeat one table at a time. Each table must specify both a database and a table name. For example: - ignore table = database.table1 -- ignore table = database.table2

$ mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user


Add 'master' to the 'CHANGE MASTER TO..' statement generated by -- dump slave_ HOST=<host>,MASTER_ PORT=<port>‘

$ mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port


Use the INSERT IGNORE statement when inserting rows

$ mysqldump -uroot -p --host=localhost --all-databases --insert-ignore


Each line of the output file is divided by a given string. Used with the -- tab option and cannot be used with the -- databases and -- all databases options.

$ mysqldump -uroot -p --host=localhost test test --tab="/tmp/mysql"  --lines-terminated-by="##"

43,–lock-all-tables, -x

Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock, and the -- single transaction and -- lock tables options are automatically turned off.

$ mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

44,–lock-tables, -l

Lock all tables before starting export. Lock the table with READ LOCAL to allow parallel insertion of MyISAM table. For tables that support transactions, such as InnoDB and BDB, - single transaction is a better choice because it does not need to lock the table at all. Note that when exporting multiple databases, - lock tables locks tables for each database separately. Therefore, this option does not guarantee the logical consistency between the tables in the exported file and the database. The export status of different database tables can be completely different.

$ mysqldump -uroot -p --host=localhost --all-databases --lock-tables


Attach warning and error information to the given file

$ mysqldump -uroot -p --host=localhost --all-databases  --log-error=/tmp/mysqldump_error_log.err


The maximum packet length sent and accepted by the server.

$ mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240


Cache size of TCP/IP and socket connections.

$ mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024


Wrap the table with autocommit/commit statements.

$ mysqldump -uroot -p --host=localhost --all-databases --no-autocommit

49,–no-create-db, -n

Only export data without adding a CREATE DATABASE statement.

$ mysqldump -uroot -p --host=localhost --all-databases --no-create-db

50,–no-create-info, -t

Export only data without adding a CREATE TABLE statement.

$ mysqldump -uroot -p --host=localhost --all-databases --no-create-info

51,–no-data, -d

No data is exported, only the database table structure is exported.

$ mysqldump -uroot -p --host=localhost --all-databases --no-data

52,–no-set-names, -N

Equivalent to -- skip set charset

$ mysqldump -uroot -p --host=localhost --all-databases --no-set-names


Equivalent to -- add drop table, - add locks, - create options, - quick, - extended insert, - lock tables, - set charset, - Disable keys. This option is enabled by default and can be disabled with -- skip opt

$ mysqldump -uroot -p --host=localhost --all-databases --opt


If there is a primary key or the first unique key, the records of each table are sorted. It is valid when exporting MyISAM table to InnoDB table, but it will take a long time to export.

$ mysqldump -uroot -p --host=localhost --all-databases --order-by-primary

55,–password, -p

Connection database password

56. – pipe (available for Windows system)

Connecting mysql using named pipes

$ mysqldump -uroot -p --host=localhost --all-databases --pipe

57,–port, -P

Connection database port number

# Note capital P
$ mysqldump -uroot  -Plocalhost -ppassword


The connection protocols used include: tcp, socket, pipe, memory

$ mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp

59,–quick, -q

The query is not buffered, but directly exported to standard output. The default is on. Use -- skip quick to cancel this option.

$ mysqldump -uroot -p --host=localhost --all-databases

$ mysqldump -uroot -p --host=localhost --all-databases --skip-quick


Use () to cause table and column names. The default is on. Use – skip quote names ` to cancel this option.

$ mysqldump -uroot -p --host=localhost --all-databases

$ mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names



$ mysqldump -uroot -p --host=localhost --all-databases --replace

62,–result-file, -r

Output directly to the specified file. This option should be used on systems that use carriage return line feed pairs (\ \ r\n) to wrap lines (for example, DOS, Windows). This option ensures that only one row is used.

$ mysqldump -uroot -p --host=localhost --all-databases--result-file=/tmp/mysqldump_result_file.txt

63,–routines, -R

Export stored procedures and custom functions.

$ mysqldump -uroot -p --host=localhost --all-databases --routines


Add 'set names default'_ character_ Set 'to the output file. The default is on. Use -- skip set charset to turn off the option.

$ mysqldump -uroot -p --host=localhost --all-databases

$ mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset


This option submits a BEGIN SQL statement before exporting data. BEGIN will not block any applications and can ensure the consistency of the database during export. It is only applicable to multi version storage engines, only InnoDB. This option and the -- LOCK TABLES option are mutually exclusive, because LOCK TABLES will make any pending transactions implicitly committed. To export large tables, use the -- quick option in combination.

$ mysqldump -uroot -p --host=localhost --all-databases --single-transaction


Add the export time to the output file. The default is on. Use -- skip dump date to turn off the option.

$ mysqldump -uroot -p --host=localhost --all-databases

$ mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date


Disable the – opt option

$ mysqldump -uroot -p --host=localhost --all-databases --skip-opt


Specify the socket file location for connecting to MySQL, and the default path is / TMP / mysql.socket

$ mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock


Create a tab split text file for each table at the given path. Note: only for mysqldump and mysqld servers running on the same machine. Note: the -- databases parameter cannot be specified when using -- tab

$ mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"


Override the -- databases (-B) parameter to specify the table name to be exported. In later versions, table will be used instead of tables.

$ mysqldump -uroot -p --host=localhost --databases test --tables test


Export trigger. This option is enabled by default and disabled with -- skip triggers.

$ mysqldump -uroot -p --host=localhost --all-databases --triggers


Set the time zone time at the top of the export_ Zone = '+ 00:00', to ensure the correctness of TIMESTAMP data exported in different time zones or when the data is moved to other time zones.

$ mysqldump -uroot -p --host=localhost --all-databases --tz-utc

73,–user, -u

Specifies the user name for the connection.

74,–verbose, –v

Output various platform information.

75,–version, -V

Output mysqldump version information and exit

76,–where, -w

Dump only the records selected by the given WHERE condition. Note that if the condition contains spaces or characters specific to the command interpreter, be sure to reference the condition.

$ mysqldump -uroot -p --host=localhost --all-databases --where=" user='root'"

77,–xml, -X

Export XML format

$ mysqldump -uroot -p --host=localhost --all-databases --xml


Directory of client plug-ins for compatibility with different plug-in versions.

$ mysqldump -uroot -p --host=localhost --all-databases--plugin_dir="/usr/local/lib/plugin"


Default permissions for client plug-ins.

$ mysqldump -uroot -p --host=localhost --all-databases--default-auth="/usr/local/lib/plugin/<PLUGIN>"

Reference article link:

MySql database backup and recovery -- Summary of import and export methods using mysqldump_ helloxiaozhe's blog - CSDN blog_ Mysqldump export database MySQL :: MySQL Backup and Recovery :: 1.4.1 Dumping Data in SQL Format with mysqldump

Posted by xydon1 on Mon, 22 Nov 2021 05:59:10 -0800