Preface:

Mysqldump is a tool that can be used to export data from a database. This article will introduce you to the mysqldump tool and share some backup tips.

1. Introduction to mysqldump

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

Mysqldump basic syntax:

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

Mysqldump –help = mysqldump –help = mysqldump –help = mysqldump –help = mysqldump –help = mysqldump

The table above shows some common mysqldump options. If you don’t know what a particular parameter does, you can use mysqldump –help to get help. For Boiler-type parameters, there is usually an opposite parameter, too, as triggers triggers triggers becomes activated by default. Use –skip-triggers to disable it.

2. Some backup tips

Although mysqldump is not suitable for backup of large amount of data, it is widely used in data export field because it is flexible and convenient, and parameters can be customized according to scenarios.

Mysqldump backup tips:

  • It is recommended to use the –single-transaction parameter to obtain a consistent backup and reduce locking tables.
  • According to the demand to export, as long as you want to reduce the size of the export file.
  • If you want to set up a slave library, you are advised to use –master-data = 2 to record the binlog information of the master library.
  • If you want to back up stored procedures, custom functions, and events, add the -r -e parameter. These parameters are disabled by default.
  • Do not add parameters you do not understand, press the default.

Here are a few ways to use mysqldump in different scenarios:

Mysqldump = mysqldump-uroot -pxxxxxx --single-transaction -R -E --all-databases > /tmp/all_database.sqlMysqldump = mysqldump = mysqldump-uroot -pxxxxxx --single-transaction -R -E --all-databases --master-data=2 > /tmp/all_database.sqlMysql > alter 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.sqlMysql > alter table mysqldump-uroot -pxxxxxx --single-transaction db1 tb1 > /tmp/tb1.sql
mysqldump -uroot -pxxxxxx --single-transaction db1 tb1 tb2 tb3 > /tmp/tb.sqlExport a table to a single rowinsertAccording to the mysqldump-uroot -pxxxxxx --single-transaction --skip-extended-insert db1 tb1 > /tmp/tb1.sqlMysql > alter 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.sqlSelect mysqldump from mysqldump-uroot -pxxxxxx --single-transaction --databases db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > /tmp/db1.sqlMysqldump = mysqldump-uroot -pxxxxxx db1 --no-data > /tmp/db1_jiegou.sql
mysqldump -uroot -pxxxxxx db1 --no-create-info > /tmp/db1_data.sqlMysqldump = mysqldump-uroot -pxxxxxx -d -t -R db1 > /tmp/Mysqldump = mysqldump = mysqldump-uroot -pxxxxxx -hxxx.xxx.xx -P3306 --single-transaction --databases db1 > /tmp/db1.sql
Copy the code

Conclusion:

This article introduces how to use the mysqldump tool and some common scenarios. Mysqldump as a utility, I hope you can start to learn, than Navicat and other graphical interface export faster, and the file size is small.