Mysqldump usage (MySQL database backup and recovery), mysqldumpmysql

Mysqldump usage (MySQL database backup and recovery), mysqldumpmysql

# Mysqldump -- help

1. Several common methods for mysqldump:

(1) Export the entire database (including data in the database)

Mysqldump-u username-p dbname> dbname. SQL

(2) Export the database structure (excluding data)

Mysqldump-u username-p-d dbname> dbname. SQL

(3) export a data table in the database (including data)

Mysqldump-u username-p dbname tablename> tablename. SQL

(4) export the table structure of a data table in the database (excluding data)

Mysqldump-u username-p-d dbname tablename> tablename. SQL

2. Common mysqldump parameters:

-- All-databases,-A export all databases. mysqldump-uroot-p -- all-databases> all_databases_backup. SQL

-- All-tablespaces,-Y: export all tablespaces. Mysqldump-uroot-p-all-databases-all-tablespaces

-- No-tablespaces,-y does not export any tablespace information. Mysqldump-uroot-p-all-databases-no-tablespaces

-- Add-drop-database: add the drop database statement before each database is created. Mysqldump-uroot-p-all-databases-add-drop-database

-Add-drop-table: add the drop table statement before each data table is created. (The default value is "Open" and "-skip-add-drop-table" is used to cancel the operation.) mysqldump-uroot-p-all-databases (the drop statement is added by default) mysqldump-uroot-p-all-databases-skip-add-drop-table (cancel the drop Statement)

-Add-locks adds lock tables before each TABLE is exported and then UNLOCK the TABLE. (The default value is "Open", and the "-skip-add-locks" option is used to cancel) mysqldump-uroot-p-all-databases (the LOCK statement is added by default) mysqldump-uroot-p-all-databases-skip-add-locks (cancel LOCK Statement)

-Comments: add comments. Enabled by default. You can use-skip-comments to cancel mysqldump-uroot-p-all-databases (default record comment) mysqldump-uroot-p-all-databases-skip-comments (uncomment)

-Compact exports less output information (for debugging ). Remove comments, headers, and tails. Option:-skip-add-drop-table-skip-add-locks-skip-comments-skip-disable-keysmysqldump-uroot-p-all-databases-compact

-Complete-insert,-c uses the complete insert Statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Mysqldump-uroot-p-all-databases-complete-insert

-Compress,-C enables compression between the client and the server to pass all information mysqldump-uroot-p-all-databases-compress

-Databases,-B Exports several databases. All name parameters following the parameter are considered as the database name. Mysqldump-uroot-p-databases test mysql

-Debug outputs the debug information 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 debugging information and exit mysqldump-uroot-p-all-databases-debug-info.

-Default-character-set: sets the default character set. The default value is utf8mysqldump-uroot-p-all-databases-default-character-set = latin1.

-Delayed-insert: Use the insert delayed method to export data mysqldump-uroot-p-all-databases-delayed-insert

-Events,-E export event. Mysqldump-uroot-p-all-databases-events

-Refresh the log before flush-logs starts exporting. Note: If you export multiple databases at a time (use the option-databases or-all-databases), the logs will be refreshed one by one. In addition to using-lock-all-tables or-master-data. In this case, the log is refreshed once, and the corresponding table is locked at the same time. Therefore, if you want to export and refresh logs at the same time, use-lock-all-tables or-master-data and-flush-logs. Mysqldump-uroot-p-all-databases-flush-logs

-Flush-privileges issues a flush privileges statement after exporting the mysql database. To restore data correctly, this option should be used to export data from the mysql database and dependent mysql database at any time. Mysqldump-uroot-p-all-databases-flush-privileges

-Force ignores SQL errors during export. Mysqldump-uroot-p-all-databases-force

-Host,-h host information to be exported mysqldump-uroot-p-host = localhost-all-databases

-Ignore-table: the specified table is not exported. When you specify to ignore multiple tables, you need to repeat multiple times for each table. The database and table names must be specified for each table. Example:-ignore-table = database. table1-ignore-table = database. table2 ...... Mysqldump-uroot-p-host = localhost-all-databases-ignore-table = mysql. user

-Lock-all-tables,-x submits a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the-single-transaction and-lock-tables options are automatically disabled. Mysqldump-uroot-p-host = localhost-all-databases-lock-all-tables

-Lock-tables: All tables are locked before-l is exported. Use read local to lock the table to allow Concurrent Insertion of 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 the table at all. Note that when exporting multiple databases,-lock-tables locks the tables for each database. Therefore, this option does not guarantee the logical consistency between the tables in the exported files in the database. The export statuses of different database tables can be completely different. Mysqldump-uroot-p-host = localhost-all-databases-lock-tables

-No-create-db,-n only exports data, without adding the create database statement. Mysqldump-uroot-p-host = localhost-all-databases-no-create-db

-No-create-info,-t only exports data, without adding the create table statement. Mysqldump-uroot-p-host = localhost-all-databases-no-create-info

-No-data,-d: only the database table structure is exported without exporting any data. Mysqldump-uroot-p-host = localhost-all-databases-no-data

-Password,-p password used to connect to the database

-Port and-P port number used to connect to the database

-User,-u specifies the connection user name.

3. mysqldump common instances:

(1) mysqldump is often used for database backup and restoration. During the backup process, we can add any of the above parameters according to our actual situation. If we have a database named test_db, run the following command, back up the entire database:

Mysqldump-u root-p test_db> test_db. SQL

(2) To restore the data, run the following command:

Mysql-u username-p test_db <test_db. SQL

(3) You can use the following methods to restore a database:

Mysql> sourcetest_db. SQL

The above discussion about how to use mysqldump (MySQL database backup and recovery) is all the content that I have shared with you. I hope you can give me a reference and support me a lot.