Introduction to mysqldump

mysqldump is a logical backup tool of MySQL system, which is mainly used to dump databases. It mainly produces a series of SQL statements, which can be encapsulated into a file, which contains the SQL commands needed to rebuild the database, such as CREATE DATABASE, CREATE TABLE, INSERT and so on. When we need to restore these data, we only need to execute this file to restore the corresponding data.

The basic syntax of mysqldump is as follows:

Usage: mysqldump [OPTIONS] database [tables] 
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] 
OR     mysqldump [OPTIONS] --all-databases [OPTIONS] 

Execute mysqldump --help or refer to the official MySQL documentation. We find that there are many configurable parameters of mysqldump tool. The following briefly describes some common parameters.

The above table shows some common mysqldump related options. When you don't understand the role of a parameter, you can execute mysqldump --help to get help. For Boolean parameters, there is usually an opposite parameter, such as -- triggers, which is enabled by default. You can use -- skip triggers to disable it.

Some backup tips

Although mysqldump is not suitable for the backup of large amounts of data, it is still widely used in the field of data export because of its flexibility and convenience and the ability to customize parameters according to scenarios.

Based on my own experience, the author briefly shares some mysqldump backup tips:

• It is recommended to use the -- single transaction parameter to obtain consistent backups and reduce locking tables.
• Export as required. As long as you want the data, try to reduce the size of the exported file.
• If you want to build a slave database, it is recommended to use the -- master data = 2 parameter to record the binlog information of the master database.
• If you want to back up stored procedures, custom functions and events, please add the - R -E parameter, which are not enabled by default.
• Don't add unknown parameters at will, just press the default.

Here are some ways to use mysqldump in different scenarios:

# Backup all databases (including stored procedures, custom functions and events) 
mysqldump -uroot -pxxxxxx --single-transaction -R -E --all-databases > /tmp/all_database.sql 
 
# It is required to record binlog site information, which can be used to build slave database 
mysqldump -uroot -pxxxxxx --single-transaction -R -E --all-databases --master-data=2 > /tmp/all_database.sql 
 
# Back up the specified database 
mysqldump -uroot -pxxxxxx --single-transaction -R -E --databases db1 > /tmp/db1.sql 
mysqldump -uroot -pxxxxxx --single-transaction -R -E --databases db1 db2 > /tmp/db1_db2.sql 
 
# Backup partial table 
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 > /tmp/tb1.sql 
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 tb2 tb3 > /tmp/tb.sql 
 
# Export a table, and the data is displayed as a single row insert 
mysqldump -uroot -pxxxxxx --single-transaction --skip-extended-insert db1 tb1 > /tmp/tb1.sql 
 
# Export partial data of a single table 
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 --where=" create_time >= '2021-06-01 00:00:00' " > /tmp/tb1.sql 
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 --where='id < 10' > /tmp/tb1.sql 
 
# Exclude some table exports 
mysqldump -uroot -pxxxxxx --single-transaction --databases db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > /tmp/db1.sql 
 
# Export structure only or data only 
mysqldump -uroot -pxxxxxx db1 --no-data > /tmp/db1_jiegou.sql 
mysqldump -uroot -pxxxxxx db1 --no-create-info > /tmp/db1_data.sql 
 
# Only the stored procedures and user-defined functions of a library are exported 
mysqldump -uroot -pxxxxxx -d -t -R db1 > /tmp/db1_routine.sql 
 
# Remote export means that the MySQL server is not local 
mysqldump -uroot -pxxxxxx -hxxx.xxx.xx -P3306 --single-transaction --databases db1 > /tmp/db1.sql 

summary

This article mainly introduces the usage of mysqldump tool and some common scenarios. Mysqldump is a practical tool. I hope you can learn it. It is faster and smaller than exporting with graphical interfaces such as Navicat.