Mysqldump is a tool that is commonly used in daily life. When exporting to a database, mysqldump is often used. In this article, you’ll learn how to use the mysqldump tool and share a few backup tips.

1. Introduction to mysqldump

Mysqldump is a logical backup tool that comes with the MySQL system. It is mainly used to dump the database. It produces a series of SQL statements that can be encapsulated into a file that 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, we can restore the corresponding data.

The basic syntax for mysqldump is as follows:

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

Mysqldump –help/mysqldump/mysqldump/mysqldump/mysqldump/mysqldump/mysqldump/mysqldump/mysqldump

The table above shows some common mysqldump options. When you don’t know what a parameter does, you can call mysqldump –help to get help. For Boolean arguments, there is usually a counter argument such as –triggers the default to be turned on, which can be disabled using –skip-triggers.

2. A few 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 advantages such as flexibility and convenience and customization of parameters according to the scene.

The author according to their own use experience, simple to share a few mysqldump backup tips:

  • The –single-transaction parameter is recommended for a consistent backup, reducing the number of locked tables.
  • Export on demand, as long as you want the data, as small as possible export file size.
  • If you want to build a slave library, it is recommended to use the –master-data = 2 parameter to record the master library binlog information.
  • If you want to back up stored procedures, custom functions, and events, add the -r-e parameter, which is turned off by default.
  • Do not know the parameters of random add, according to the default can.

Here’s how to use mysqldump in several different scenarios:

MySQLDUMP-uroot-pxxxxxx -- single-transaction-r-e --all-databases > / TMP /all_database.sql # for record binlog site information Can be used to build the library mysqldump uroot - PXXXXXX - single ws-transaction - R - E - all - databases - master - data = 2 > / TMP /all_database.sql # db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db db database Mysql > database db db; mysql > database db; mysql > database db; mysql > database db -pxxxxxx --single-transaction db1 tb1 > /tmp/tb1.sql mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 tb2 tb3 > / TMP /tb.sql/TMP /tb.sql Mysqldump -uroot-pxxxxxx --single-transaction --skip-extended-insert db1 tb1 > / TMP /tb1.sql # 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 'mysqldump -uroot -pxxxxxx --single-transaction databases --ignore -- table=db1.tb1 --ignore -- table=db1.tb1 / TMP /db1.sql # db db db/TMP /db1.sql  mysqldump -uroot -pxxxxxx db1 --no-data > /tmp/db1_jiegou.sql mysqldump -uroot -pxxxxxx db1 --no-create-info > / TMP /db1_data.sql = 'mysqldump-uroot-pxxxxxx-d-t-r db1 >'; / TMP/db1_routin.sql = 'mysqldump-uroot-pxxxxxx-d-t-r db1 >'; / TMP/db1_routin.sql = 'mysqldump-uroot-pxxxxxx-d-t-r'  mysqldump -uroot -pxxxxxx -P3306 --single-transaction --databases db1 > /tmp/db1.sql


This article mainly introduces the use of mysqldump tool and some common scenarios. As a practical tool, I hope you can learn mysqldump, which is faster and smaller than exporting with a graphical interface such as Navicat.