Detailed explanation of the use of MySQL mysqldump

1. Introduction to mysqldump

mysqldump is a logical backup tool that comes with MySQL.

Its backup principle is to connect to the MySQL database through the protocol, query the data that needs to be backed up, and convert the queried data into corresponding insert statements. When we need to restore these data, we only need to execute these insert statements to restore the corresponding data.

2. Backup Command

2.1 Command Format

mysqldump [options] database name [table name] > script name

or

mysqldump [options] --database name [options table name] > script name

or

mysqldump [options] --all-databases [options] > script name

2.2 Option Description

2.3 Examples

Back up all databases:

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

Back up the specified database:

mysqldump -uroot -p test > /backup/mysqldump/test.db

Back up the specified database and table (multiple tables are separated by spaces)

mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db

Back up the specified database excluding certain tables

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

3. Restore Command

3.1 System command line

mysqladmin -uroot -p create db_name 
mysql -uroot -p db_name < /backup/mysqldump/db_name.db

Note: Before importing the backup database, if db_name does not exist, it needs to be created; and it can only be imported if the database name is the same as the database name in db_name.db.

3.2 Source Method

mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

Examples

Note: Username: root Password: DbPasswd The generated sql script is db.sql

Export the table structure of the database as DBName (without exporting data)

mysqldump -uroot -pDbPasswd -d DBName > db.sql

Export the table structure and all data of the database DBName (without adding -d)

mysqldump -uroot -pDbPasswd DBName > db.sql;

Export the structure of the table (test) of the database DBName

mysqldump -uroot -pDbPasswd -d DBName test > db.sql

Export the structure and all data of the table (test) of the database DBName (without adding -d)

mysqldump -uroot -pDbPasswd DBName test > db.sql 

This is the end of this article about the detailed use of MySQL mysqldump. For more relevant MySQL mysqldump content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!