Exporting a database using mysqldump (table)

Mysqldump is used to back up a database or to migrate data between different databases, Mydqldump's backup includes SQL statements to create tables and mount tables.

First, mysqldump use method

(1), back up a single database or some tables in the database, before using mysqldump export, lock the table write operation, when the data export is complete, the lock is released.

Shell> mysqldump [Options] db_name [table_name]

(2), backing up one or more databases specified

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 parameters of mysqldump

-U,--user=username              # Specify user name-P,--password=password          # Specify password-h,--host=hostname              # Specify server ip-p,--port=port                  # Specify connection Port--add-drop-database             # Add DROP DATABASE statement before each database creation statement--add-drop-table                # Add a drop TABLE statement before each table creation statement-n ,--no-create-db               # does not include the creation statement for the database-T,--no-create-info             # does not include the creation statement for the data table-D,--no-data                    # does not include data--compact                       # The output is more concise and does not include various comment statements-c,--Complete-insert            # causes the INSERT statement in the output file to include the field name, by default excluding the field name-F,--flush-logs                 # Refresh log before backup-l,-- Lock-tables                # Lock all tables during backup--help mysqldump                # Command Help

Third, character Set options

The--default-character-set option sets the exported client character set, which is important when exporting the database, and if the client character set and the database character set are inconsistent, the database may require character set conversion when exporting. Converts the database character set to the client character set, telling the converted data to be garbled or "?" and other special characters so that the backup file cannot be recovered.

Four, mysqldump actual combat

(1), Back up the city table under the test database

(2), because the character set of the client and the server is inconsistent, the export file becomes garbled, resulting in unable to import, see as follows:

# mysqldump-uroot-p  

(3), using the-c parameter, causes the INSERT INTO statement to add the table column name, see as follows:

# mysqldump-uroot-p-C Test City > City.txt

(4), add the-d parameter, so that mysqldump only exported the table structure of the city table, the table data is not exported, see as follows:

　

Exporting a database using mysqldump (table)