When using mysqldump to backup MySQL, the backup file is a text file in SQL format, which consists of a series of It consists of create table and insert statement.When restoring, just use the SQL file as the input of the mysql program, as shown below:

mysqldump mydb mytbl > mytbl.sql

mysql mydb < mytbl.sql

Note: Never attempt to use mysqlimport to load backup files generated by mysqldump! mysqlimport can only read data rows and cannot be used to read SQL statements.

When you type mysqldump on the command line without adding any parameters, the following prompt message will appear:

[[email protected] ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS]--databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS]--all-databases [OPTIONS]
For more options, use mysqldump--help

The above prompt information gives 3 usage methods, we will introduce them one by one below:

1) mysqldump [OPTIONS] database [tables]

This is the most common usage method.Given a database name, followed by one or more table names, it means to export the specified tables under the database.

2) mysqldump [OPTIONS]--databases [OPTIONS] DB1 [DB2 DB3...]

The second method uses the--databases parameter, which will export all tables in the listed databases

3) mysqldump [OPTIONS]--all-databases [OPTIONS]

--all-databases parameter means to export all tables in all databases, including the permission table in mysql database, so be sure to use it with caution

When you type mysqldump--help on the command line, all parameters will appear.Below we will introduce the main ones:

1)--opt

--opt Same as--add-drop-table,--add-locks,--create-options,
                   --quick,--extended-insert,--lock-tables,--set-charset,
                    and--disable-keys.Enabled by default, disable with
                   --skip-opt.

From the explanation of what, you can know that--opt will enable multiple options that can speed up the backup process for optimization of the backup process.It is enabled by default.It is generally recommended to enable it.The--skip-opt option can disable it.

However, special attention should be paid here: in order to speed up the backup, the--opt option will add a read lock to the backup table, which will make it impossible for other users to modify it.Therefore, do not enable this option for backup when the business is busy.!

The following explains the options enabled by--opt:

--add-drop-table: Add a drop table if exists statement before each create table statement;

--add-locks: Indicates that a lock command is added before and after the generated insert statement;

--extended-insert: Indicates generating an insert statement that inserts multiple rows at a time

--lock-tables: Indicates that a read lock is added to the backup table;

2)--single-transaction

When exporting innodb and Falcon tables, it is best to add this option to ensure a stable backup.

3)--no-create-info  --no-data

As mentioned earlier, by default, the text file exported by mysqldump contains create table and insert into statements.If--no-create-info is added, it means that the create table statement is not required.Similarly,--no-data means that the insert into statement is not required.

4)--routines--triggers  --events

By default, only triggers are included in the exported text file.If you need to export other objects, you can add the corresponding options.

These three options also have a corresponding--skip form, which means to exclude it from the export file.