[MySQL] Using mysqldump for text backup

[MySQL] Using mysqldump to create text backup when using mysqldump to back up MySQL, the backup file is an SQL text file, which consists of a series of createtable and insert statements. When restoring, you only need to use this SQL file as the mysql program input, as shown in the following figure: mysqldumpmydbmytblmytbl. sqlmysqlmydbmytbl. SQL note

[MySQL] Using mysqldump for text backup when mysqldump is used to back up MySQL, the backup file is an SQL text file, which consists of a series of create table and insert statements. When restoring, you only need to use this SQL file as the mysql program input, as shown in the following figure: mysqldump mydb mytbl. sqlmysql mydb mytbl. SQL note

[MySQL] Using mysqldump for text backup

When using mysqldump to back up MySQL, the backup file is a text file in SQL format, which consists of a series of create table and insert statements. When restoring, you only need to use the SQL file as the input of the mysql program, as shown below:

mysqldump mydb mytbl > mytbl.sqlmysql mydb < mytbl.sql

Note: do not try to use mysqlimport to load the backup file generated by mysqldump!Mysqlimport can only read data rows and cannot be used to read SQL statements.

When you press mysqldump in the command line without adding any parameters, the following message is displayed:

[root@lx203 ~]# mysqldumpUsage: 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 prompts provide three usage methods. We will introduce them one by one:

1) mysqldump [OPTIONS] database [tables]

This is the most common method of use. It provides a database name and one or more table names after it, indicating to export the specified tables in the database.

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

The second method uses the -- databases parameter, which exports all the tables in the listed Database

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

The -- all-databases parameter indicates to export all tables in all databases, including permission tables in the mysql database. Therefore, be careful when using this parameter.

When you press mysqldump -- help in the command line, all parameters will appear. The following describes the main parameters:

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 what you can explain, we can know that -- opt will enable multiple options to speed up the backup process for optimization. It is started by default and is generally recommended to start it, the -- skip-opt option can be disabled.

However, it is important to note that the -- opt option is used to speed up backup,The read lock will be added to the backup table, and other users will not be able to modify it.When your business is busy, do not enable this option for backup!

The following explains the options -- opt enabled:

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

-- Add-locks: indicates that the lock command is added before and after the generated insert statement;

-- Extended-insert: An insert statement that inserts multiple rows at a time.

-- Lock-tables: indicates that the 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 above, by default, the text file exported by mysqldump contains the create table and insert into statements. If -- no-create-info is added, the create table statement is not required. Similarly, -- no-data indicates that the insert into statement is not required.

4) -- routines -- triggers -- events

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

The three options also have the corresponding -- skip format, indicating to exclude it from the export file.