Statement Summary for backing up and restoring a MySQL database in command line mode _ MySQL

Statement Summary of backing up and restoring the MySQL database in command line mode bitsCN.com for security reasons, 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:


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:

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:

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:

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:

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.

Mysqldump-u root-p test_db> test_db. SQL
Mysql-u root-p test_db <test_db. SQL


Command for backing up MySQL database

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.

Mysqldump-