Use mysqldump to export a database (table)

Use mysqldump to export a database (table)

Mysqldump is used to back up databases or migrate data between different databases. The backup content of mydqldump includes SQL statements used to create and load tables.

I. How to Use mysqldump

(1) Back up a single database or some tables in the database. Before using mysqldump for export, the write operation of the table will be locked. After the data export is complete, the lock will be released.

shell> mysqldump [options] db_name [table_name]

(2) Back up one or more specified databases

shell> mysqldump [options] --database DB1 [DB2,DB3,...]shell> mysqldump [options] -B DB1 [DB2,DB3,...]

(3) back up all databases on the server

shell> mysqldump [options] --all-databaseshell> mysqldump [options] -A

Ii. Main mysqldump Parameters

-U, -- user = username # specify username-p, -- password = password # specify password-h, -- host = hostname # specify server ip-P, -- port = port # specify the connection port -- add-drop-database # add the drop database statement -- add-drop-table before each database creation statement # add drop before each table creation statement table statement-n, -- no-create-db # does not include the database creation statement-t, -- no-create-info # does not include the data table creation statement-d, -- no-data # does not include data -- compact # The output is more concise, excluding the comments-c and -- complete-insert # Make the insert statement in the output file include the field name, the field name-F, -- flush-logs # refresh log-l before backup, -- lock-tables # lock all tables during backup -- help mysqldump # Command help

Iii. Character Set options

-- The default-character-set option can be used to set the exported client character set. This option is very important when exporting the database. If the client character set is inconsistent with the database character set, when exporting data, the database may need character set conversion to convert the database character set to the client character set. As mentioned, the converted data may become garbled or "?" And other special characters to make the backup file unrecoverable.

Iv. Practice of mysqldump

(1) back up the city table under the test database

# mysqldump -uroot -p test city > city.txt 

(2) because the character set of the client and server is inconsistent, the exported file becomes garbled and cannot be imported. See the following:

# mysqldump -uroot -p  --default-character-set=latin1 test city > city.txt 

(3) use the-c parameter to add the column name of the table to the insert into statement, as shown below:

# mysqldump -uroot -p -c test city > city.txt

(4) Add the-d parameter so that mysqldump only exports the table structure of the city table. The table data is not exported, as shown below:

# mysqldump -uroot -p -d test city > city.txt 

Use mysqldump to back up MariaDB

Use mysqldump to export a database

Quickly build a slave Database Based on mysqldump

Restore the backup set created by mysqldump

Use mysqldump command line tool to create logical backup

Implement logical database backup using mysqldump

This article permanently updates the link address: