Common Methods for Using mysqldump in MySQL Database
• Export the entire database (with data in the database).
mysqldump -u username -p dbname > dbname.sql |
• Export the database structure (without data).
mysqldump -u username -p -d dbname > dbname.sql |
• Export a data table (with data) in a database.
mysqldump -u username -p dbname tablename > tablename.sql |
• Export the table structure (without data) of a data table in the database.
mysqldump -u username -p -d dbname tablename > tablename.sql |
Description of Common Parameters for mysqldump
• –all-databases, -A Export all databases mysqldump -uroot -p –all-databases.
• –all-tablespaces, -Y Export all tablespaces. mysqldump -uroot -p –all-databases –all-tablespaces–no-tablespaces, -y Do not export any tablespace information. mysqldump -uroot -p –all-databases –no-tablespaces.
• –add-drop-database Add the drop database statusment before each database is created. mysqldump -uroot -p –all-databases –add-drop-database.
• –add-drop-table Add the drop data table statement before each data table is created. (This option is enabled by default. Use –skip-add-drop-table to disable the option) mysqldump -uroot -p –all-databases (By default, add the drop statement) mysqldump -uroot -p –all-databases –skip-add-drop-table (Disable the drop statement).
• –add-locks Add LOCK TABLES and UNLOCK TABLE before each table is exported. (This option is enabled by default. Use –skip-add-locks to disable the option) mysqldump -uroot -p –all-databases (By default, add the LOCK statement) mysqldump -uroot -p –all-databases –skip-add-locks (Disable the LOCK statement).
• –comments Add the comments. This option is enabled by default. Use –skip-comments to disable the mysqldump -uroot -p –all-databases (By default, record the comments) mysqldump -uroot -p –all-databases –skip-comments (Disable the comments).
• –complete-insert, -c Use the complete insert statement (including the column name). This may increase the insertion efficiency but may cause the insertion failure due to the impact from the max_allowed_packet parameter. mysqldump -uroot -p –all-databases –complete-insert.
• –compact Export less output information (used for debugging). Delete structures such as comments and head and tail. The following options can be used: –skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keysmysqldump -uroot -p –all-databases –compact.
• –compress, -C Enable compression and transfer all information between the client and server. mysqldump -uroot -p –all-databases –compress.
• –databases, - Export several databases. All name parameters after the parameter are deemed as the database name. mysqldump -uroot -p –databases test mysql.
• –debug Output the debug information, used for debugging. The default value is: d:t:o,/tmp/mysqldump.tracemysqldump -uroot -p –all-databases –debugmysqldump -uroot -p –all-databases –debug=” d:t:o,/tmp/debug.trace”.
• –debug-info Output the debugging information and exit mysqldump -uroot -p –all-databases –debug-info.
• –default-character-set Set the default character set. The default value is utf8mysqldump -uroot -p –all-databases –default-character-set=latin1.
• –delayed-insert Use the delay insertion method (INSERT DELAYED) Export data mysqldump -uroot -p –all-databases –delayed-insert.
• –events, -E Export events. mysqldump -uroot -p –all-databases –events.
• –flush-logs Flush the logs before exporting. Please note: If you export more than one database at a time (Use option –databases or–all-databases) to flush logs one by one. Except the use of –lock-all-tables or –master-data. In this case, the log will be flushed once, and the corresponding tables will be locked at the same time. Therefore, if you intend to export and flush logs at the same time, you should use –lock-all-tables or –master-data and –flush-logs.mysqldump -uroot -p –all-databases –flush-logs.
• –flush-privileges Send a FLUSH PRIVILEGES statement after exporting the MySQL database. To ensure correct restoration, this option should be used at any time when exporting the MySQL database and relying on the MySQL database. mysqldump -uroot -p –all-databases –flush-privileges.
• –force Ignore the SQL errors during the export process. mysqldump -uroot -p –all-databases –force.
• –host, -h The host information should be exported. mysqldump -uroot -p –host=localhost –all-databases.
• –ignore-table Do not export the specified table. When you specify to ignore multiple tables, this operation needs to be repeated several times, one table at a time. You must specify both a database and a table name for each table. For example: –ignore-table=database.table1 –ignore-table=database.table2 ……mysqldump -uroot -p –host=localhost –all-databases –ignore-table=mysql.user.
• –lock-all-tables, -x Submit the request to lock all tables in the databases to ensure data consistency. This is a global read lock and automatically disables the –single-transaction and –lock-tables options. mysqldump -uroot -p –host=localhost –all-databases –lock-all-tables.
• –lock-tables, -l Lock all tables before exporting. Lock the table with READ LOCAL to allow parallel insertions for MyISAM tables. For tables that support transactions such as InnoDB and BDB, –single-transaction is a better choice because it does not need to lock tables. Please note that when several databases are exported, –lock-tables lock the table for each database. Therefore, this option cannot guarantee logical consistency between databases for the tables in the export file. The export states of different database tables can be completely different. mysqldump -uroot -p –host=localhost –all-databases –lock-tables.
• –no-create-db, -n Only export data and do not add the CREATE DATABASE statement. mysqldump -uroot -p –host=localhost –all-databases –no-create-db.
• –no-create-info, -tOnly export data and do not add the CREATE TABLE statement. mysqldump -uroot -p –host=localhost –all-databases –no-create-info.
• –no-data, -d Do not export any data but only export the database table structure. mysqldump -uroot -p –host=localhost –all-databases –no-data.
• –password, -p The password of the connected database.
• –port, -P The port number of the connected database.
• –user, -u Specify the connected user name.