Mysqldump command-MySQL database backup and restoration _ MySQL

Mysqldump command-MySQL database backup and restoration mysqldump

BitsCN.com

During the last two days, I did not notice that some tables were to be built on another server (the test server database is distributed, different tables may be on different servers ). Now there is a lot of test data in it, and I don't want to add it again. So I thought I could not back it up and restore it to the target machine. Then I had a hard time yesterday and asked my colleagues to check information online. Record the usage here.
Mysqldump command: MySQL database backup and restoration

I. common operations:
Back up the entire database
Format:
Mysqldump-h host name-P port-u user name-p password (-database) database name> file name. SQL
Mysqldump-h {hostname}-P {port}-u {username}-p {password} {databasename}> {backupfile. SQL}
For example:

The code is as follows:
1
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 db_test> backfile1. SQL
Back up the MySQL database in the format of a table with deletion
Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.
Format: mysqldump -- add-drop-table-u {username}-p {password} {databasename}> {backfile. SQL}
For example:

The code is as follows:
1
Mysqldump -- add-drop-table-uzhuchao-p123456 db_test> backfile2. SQL
Directly compress and back up the MySQL database
Format: mysqldump-h {hostname}-u {username}-p {password} {databasename} | gzip> export backfile. SQL .gz}
For example:

The code is as follows:
1
Mysqldump-hlocalhost-uzhuchao-p123456 db_test1 | gzip> backfile3. SQL .gz
Back up a MySQL database table
Format: mysqldump-h host name-P port-u username-p password (-tables |-quick) database name table name 1 (table name 2 ...) > File name. SQL (which can be defaulted in parentheses ).
Mysqldump-h {hostname} (-P {port})-u {user}-p {password} (-tables |-quick) {databasename} {table1} {table2}> {backfile. SQL}
For example:

The code is as follows:
1
2
3
4
Mysqldump-hlocalhost-uzhuchao-p123456 db_test tbl_test> backfile4-1. SQL
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 db_test tbl_test> backfile4-2. SQL
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 -- quick db_test tbl_test> backfile4-3. SQL
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 -- tables db_test tbl_test1 tbl_test2> backfile4-4. SQL
Back up multiple MySQL databases at the same time
Format: mysqldump-h {hostname} (-P {port})-u {username}-p {password}-databases {databasename1} {databasename2} {databasename3}> multibackfile. SQL
For example:

The code is as follows:
1
Mysqldump-hlocalhost-uzhuchao-p123456-databases db_test1 db_test2 db_test3> multibackfile. SQL
Back up database structures only
Format: mysqldump-no-data-databases {databasename1} {databasename2 }>{ structurebackfile. SQL}
For example:

The code is as follows:
1
Mysqldump-no-data-databases db_test1 db_test2> structurebackfile. SQL
Back up all databases on the server
Format: mysqldump-all-databases> allbackupfile. SQL

========================================================== ======================================

Command for restoring MySQL database
Format: mysql-h {hostname}-u {username}-p {password} {databasename} <{backfile. SQL}
For example:

The code is as follows:
1
Mysql-hlocalhost-uroot-p123456 db_test4 <back_file1. SQL
Restore a compressed MySQL database
Format: gunzip <your backfile. SQL .gz} | mysql-u {username}-p {password} {databasename}
For example:

The code is as follows:
1
Gunzip <backfile. SQL .gz | mysql-uzhuchao-p123456 db_test5
Transfer Database to new server
Mysqldump-u {username}-p {password} {databasename} | mysql-host = *. *-C {databasename}

II. others:
1. if the port is 3306 by default,-P {port number} can be omitted.

2. the content in the command line format {} is a variable.
Host Name: {hostname}
Port: {port} (3306 by default, which can be default)
Username: {user} {username} (such as root)
Password: {password}
Database name: {databasename}
Table name: {table} {table1} {table2}
File name: {backfile. SQL}

Author: "Stone blog"

BitsCN.com