Use mysqldump to make MySQL text backup

When you use mysqldump to back up MySQL, the backup file is a text file in SQL format, consisting of a series of CREATE TABLE and INSERT statements. When recovering, just use the SQL file as input to the MySQL program, as follows:

Mysqldump mydb mytbl > Mytbl.sql

MySQL MyDB < Mytbl.sql

Note: Do not attempt to load the backup file generated by mysqldump with Mysqlimport! Mysqlimport can only read rows of data and cannot be used to read SQL statements.

When you knock mysqldump at the command line, and then do not add any arguments, the following message will appear:

[root@lx203 ~]# 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 tips above give you 3 ways to use them, and we'll explain them here:

1) mysqldump [OPTIONS] database [tables]

This is the most common use method, giving a database name, followed by one or more table names, indicating that the tables specified under the database are exported.

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 that all tables in the database are exported, including the table of permissions in the MySQL database, so be careful to use

When you knock mysqldump--help at the command line, all the arguments will appear, and we'll show you the main few:

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.

The explanation from what can be learned--opt will enable multiple speed-accelerated options for backup process optimization, which is started by default, and is generally recommended to boot,--skip-opt option to disable it.

But here's the special note: The--opt option, to speed up the backup, will add a read lock on the backup table, which will make it impossible for other users to modify, so do not enable this option backup when the business is busy!

The following explains the options enabled by--opt:

--add-drop-table: Indicates that the drop table if exists statement is added before each CREATE TABLE statement;

--add-locks: means to add a lock command before and after the generated INSERT statement;

--extended-insert: Indicates that an INSERT statement that inserts multiple rows at once is generated

--lock-tables: means to add a read lock on the backup table;

2)--single-transaction

When exporting InnoDB and Falcon tables, it is a good idea to add this option to ensure a stable backup.

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

As mentioned earlier, by default, the mysqldump exported text file contains create TABLE and insert into statements, and if you add--no-create-info to indicate that you do not need a CREATE TABLE statement, likewise, No-data indicates that no INSERT INTO statement is required.

4)--routines--triggers--events

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

These three options also have the corresponding--skip form, indicating that it is excluded from the export file.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/