Summary of statements used to back up and restore a MySQL database in Command Line Mode

For the sake of security, you need to back up or restore the database frequently. For MySQL, the most convenient method is to use the Export and Import functions of phpMyAdmin. However, if your database is large, phpMyAdmin, a Web application, may encounter a "timeout" and fail to perform the operation. Therefore, it is necessary to back up and restore databases in command line mode.

1. Back up the database
In Linux Command Line Mode, the MySQL database is backed up using the mysqldump command:

Copy codeThe Code is as follows: mysqldump-u mysqluser-p test_db

The preceding commands are described as follows:

•-U means you need to specify a MySQL user name to connect to the database service. The above mysqluser is the MySQL user name.
•-P means you need a valid Password corresponding to the above user name.
• The last parameter is the name of the database to be backed up: test_db

If you directly execute the preceding command, you will be prompted to enter the MySQL password. After the data password is entered, it will directly display the backed up SQL script on the screen, this is certainly not the result we want. We need to back up the database into a file and use the following command:Copy codeThe Code is as follows: mysqldump-u mysqluser-p test_db> test_db. SQL

In this way, a file named test_db. SQL is backed up in the current directory.

Of course, if the database size is large, the files backed up are usually compressed. Backup and compression can be completed in the same line of command:Copy codeThe Code is as follows: mysqldump-u mysqluser-p test_db | gzip> test_db. SQL .gz

When the file is compressed, it is best to add the file name to the extension .gz, so that you can be aware of the next time you restore the database.

2. Restore the database
The command to restore the database is also very simple. If the file you back up is an uncompressed version, the command to restore the database is as follows:Copy codeThe Code is as follows: cat test_db. SQL | mysql-u mysqluser-p test_db

Run the cat command to output the SQL script content to the MySQL program for restoration. We can see that the parameters following MySQL are the same as those during Backup.

To restore a compressed backup file, run the following command:Copy codeThe Code is as follows: gunzip <test_db. SQL .gz | mysql-u mysqluser-p test_db

Similarly, use the gunzip command to decompress the script and output the script content to the MySQL program for restoration.

2012-10-08 update:

If you do not want to compress and package the. SQL file, you can use the following two statements to export and import the file separately.Copy codeThe Code is as follows: mysqldump-u root-p test_db> test_db. SQL
Mysql-u root-p test_db <test_db. SQL

Command for backing up MySQL databaseCopy codeThe Code is as follows: mysqldump-hhostname-uusername-ppassword databasename> backupfile. 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.Copy codeThe Code is as follows: mysqldump -- add-drop-table-uusername-ppassword databasename> backupfile. SQL

Directly compress and back up the MySQL databaseCopy codeThe Code is as follows: mysqldump-hhostname-uusername-ppassword databasename | gzip> backupfile. SQL .gz

Back up a MySQL database tableCopy codeThe Code is as follows: mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2> backupfile. SQL

Back up multiple MySQL databases at the same timeCopy codeThe Code is as follows: mysqldump-hhostname-uusername-ppassword-databases databasename1 databasename2 databasename3> multibackupfile. SQL

Back up database structures onlyCopy codeThe Code is as follows: mysqldump-no-data-databases databasename1 databasename2 databasename3> structurebackupfile. SQL

Back up all databases on the serverCopy codeThe Code is as follows: mysqldump-all-databases> allbackupfile. SQL

Command for restoring MySQL databaseCopy codeThe Code is as follows: mysql-hhostname-uusername-ppassword databasename <backupfile. SQL

Restore a compressed MySQL databaseCopy codeThe Code is as follows: gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename

Transfer database to new serverCopy codeThe Code is as follows: mysqldump-uusername-ppassword databasename | mysql-host = *. *-C databasename