Mysqldump usage _ MySQL

Mysqldump

Mysql provides the msyqldump tool to export the database.

After searching for articles on the Internet, most of them are from one article, with a wide range of contents. Most of them are not used.

I sorted out some of the options I used and found useful, and recorded them below.

The basic format of mysqldump is:

Mysqldump-hhost-Pport-uuser-ppassword dbname> xxxx. SQL

The host port is optional. the default localhost port is 3306.

The options are as follows:

-- All-database,-A Export all data on the connection, including creating databases, creating tables, data, and stored procedures

Mysqldump-uroot-p-all-database> my. SQL

-- Databases,-B exports data from multiple databases. use this option to create database in the SQL file.

Mysqldump-uroot-p -- databases a B c> my. SQL

Mysqldump-uroot-p A> my. SQL (this does not have the CREATE DATABASE statement)

-- No-data,-d: only the structure is imported.

-- No-create-info,-t: only data is exported.

-- Routines,-R: extra export of stored procedures and functions (only use this option to generate information for other tables)

If you only want to export stored procedures and functions:

Mysqldump-uroot-p -- routines -- no-data -- no-create-info A> procedure. SQL

The exported stored procedure contains DEFINER information, which may cause permission issues if it is migrated to another machine.

-- Extended-insert,-e: when exporting data, an INSERT statement with multiple values is generated (default)

-- Skip-extended-insert is the opposite of the previous one. Each row has an INSERT statement.

-- The insert statement exported by complete-INSERT and-c has the field name, which is easier to use when columns are added to the table.

-- Add-locks adds the lock table sentence for the exported INSERT statement (enabled by default ). Like this:

Lock table xxxx

Insert into xxxx VALUES yyyy

Unlock table xxxx

-- The last skip-add-locks is opposite. no sentence is locked.

-- Add-drop-database add drop database sentence before creating database sentence

-- Add-drop-tables add drop table sentence before creating TABLE sentence (default)

-- Quick and-q cancel buffering and output directly to standard output. Add the following to the table with a relatively large import size. You can speed up the export.

-- Single-transaction: commit the transaction before exporting the InnoDB engine that supports the transaction to lock the entire table.

-- Lock-tables: If you want to lock the entire table before exporting the engine that does not support transactions (MyISAM), use this option. (InnoDB is also supported, but -- single-transaction is recommended for engines that support transactions)

-- Hex-blob exports binary data in hexadecimal format

Additional options:

-- No-defaults if you run mysqldump on WIN, the following error occurs: unkown option '-- no-beep. This problem does not occur on linux. (The reason is not detailed. this option should be included in a configuration file and should be removed)

-- Triggers have never been used by individuals. they are used in the same way as -- routines, which is not mentioned here.

These are the most frequently used options.

(The -- no-defaults option can be omitted if no error is reported on linux or Windows)

Export a complete database structure:

Mysqldump -- no-defaults-uroot-p -- add-drop-database -- no-data -- routines-B a> my. SQL

Export a complete database structure and data:

Mysqldump -- no-defaults-uroot-p -- add-drop-database -- routines -- skip-extended-insert -- skip-add-locks -- quick -- hex-blob-B A> my. SQL

The other is to back up the entire database:

Mysqldump -- no-defaults-A> my. SQL

P.S.

No lock table exists in the preceding statements. you need to add the lock table option as needed.