Talking about mysqldump usage method of MySQL database backup and recovery

#mysqldump --help

1. Several common mysqldump methods:

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

mysqldump -u username -p dbname > dbname.sql

(2) Export database structure (without data)

mysqldump -u username -p -d dbname > dbname.sql

(3) Export a data table (containing data) in the database

mysqldump -u username -p dbname tablename > tablename.sql

(4) Export the table structure (without data) of a data table in the database

mysqldump -u username -p -d dbname tablename > tablename.sql

2. mysqldump Common Parameter Description:

--all-databases, -A Export All Databases. mysqldump-uroot-p--all-databases > all_databases_backup.sql

--all-tablespaces, -Y exports 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 drop database statement before each database is created.mysqldump-uroot-p all-databases add-drop-database

9690EN-drop-table Add drop data table statement before each data table is created. (The default is open, using the skip-add-drop-table cancel option) mysqldump-uroot-p all-databases (add drop statement by default) mysqldump-uroot-p_all-databases_skip-add-table (cancel drop statement)

add-locks adds LOCK TABLES before each table export and UNLOCK TABLE after that. (The default is open, using the skip-add-locks cancel option) mysqldump-uroot-p all-databases (add LOCK statement by default) mysqldump-uroot-p all-databases skip-add-locks (cancel LOCK statement)

* comments Additional Comment Information.By default, skip-comments can be used 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 structures such as comments and headers and tails.Options available: skip-add-drop-tableskip-add-locksskip-commentsskip-disable-keysmysqldump-uroot-pall-databases\compact

complete-insert, -c uses the complete insert statement, including the column name.This improves insertion efficiency, but may be subject to max_allowed_The insertion failed due to the effect of the packet parameter.mysqldump-uroot-p all-databases complete-insert

compress, -C Enables compressed transfer of all information between client and server mysqldump-uroot-p_all-databases_compress

databases, -B exports several databases.All name parameters that follow the parameters are treated as database names.mysqldump-uroot-p databases test mysql

* debug outputs debug information for debugging.The default values are: d:t:o, /tmp/mysqldump.tracemysqldump -uroot -p all-databases debugmysqldump -uroot -p all-databases debug="d:t:o, /tmp/debug.238EN"

debug-info outputs debug information and exits mysqldump-uroot-p_all-databases_debug-info

default-character-set Sets the default character set to utf8mysqldump-uroot-p-p-all-databases-character-set=latin1

delayed-insert Export Data mysqldump-uroot-p-all-databases-insert with Delayed Insert (INSERT DELAYED)

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 once (using the option databases or all-databases), the logs will be refreshed on a database-by-database basis.Except with lock-all-tables or master-data.In this case, the log will be refreshed once, and the corresponding tables will be locked at the same time.Therefore, if you intend to export and refresh the logs at the same time, you should use ES303mysqldump-uroot-p all-databases flush-logs

After exporting the mysql database, flush-privileges issues an FLUSH PRIVILEGES statement.For proper recovery, this option should be used to export the mysql database and any time you rely on mysql database data.mysqldump-uroot-p all-databases flush-privileges

SQL error ignored during export.mysqldump-uroot-p-all-databases-force

host, -h Need to Export Host Information mysqldump - uroot - p 87347EN=localhost all-databases

* ignore-table does not export the specified table.Specifies that when multiple tables are ignored, one table at a time needs to be repeated multiple times.Each table must specify both a database and a table name.For example: ignore-table=database.table1ignore-table=database.table2...mysqldump-uroot-phost=localhostall-databasesignore=mysql.user

lock-all-tables, -x submits a request to lock all tables in all databases to ensure data 1 consistency.This is a global read lock and automatically turns off the single-transaction and lock-tables options.mysqldump-uroot-p host=localhost all-databases lock-all-tables

lock-tables, -l locks all tables before starting export.Lock tables with READ LOCAL to allow parallel insertion of MyISAM tables.single-transaction is a better choice for transactional tables such as InnoDB and BDB because it does not require locking tables at all.Note that when exporting multiple databases, lock-tables locks tables for each database separately.Therefore, this option does not guarantee logical 1 consistency between tables in the export file between databases.Different database tables can have completely different export states.mysqldump-uroot-p host=localhost all-databases lock-tables

no-create-db, -n only exports data without adding 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 does not export any data, only the database table structure.mysqldump-uroot-p host=localhost all-databases no-data

* password, -p connection database password

port, -P Connection Database Port Number

user, -u specifies the user name of the connection.

3. Common examples of mysqldump:

(1) mysqldump is commonly used for backup and restore of databases. In the process of backup, we can add any of the above parameters according to our actual situation, assuming there is a database test_db, perform the following command to complete the backup of the entire database:

mysqldump -u root -p test_db > test_db.sql

(2) To restore data, execute the following commands:

mysql -u username -p test_db < test_db.sql

(3) Restoring the database can also use the following methods:

mysql > sourcetest_db.sql