For security reasons, the database needs to be backed up frequently, or restored. For MySQL, the most convenient way may be to use the phpMyAdmin export, import functionality, but if your database volume is larger, as a WEB application phpMyAdmin may encounter "timeout" and the operation failed. Therefore, it is necessary to learn to back up and restore the database in command line mode.
1. Backup Database
Back up the MySQL database in Linux command line mode with the mysqldump command:
Copy Code code as follows:
Mysqldump-u mysqluser-p test_db
A little explanation of the above order:
-u means you have to specify a MySQL username to connect to the database service, as above mysqluser is the MySQL user name.
-p means you need to have a valid password that corresponds to the user name above.
• The last parameter is the name of the database that needs to be backed up: test_db
If you execute the above command directly, then you will be prompted to enter the MySQL password, the data password, it will directly back up the SQL script to display on the screen, which is certainly not the result we want. We need to back up the database into a single file, with the following commands:
Copy Code code 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 size of the database is large, the backed-up files are usually compressed, and backup and compression can be done within the same line of command:
Copy Code code as follows:
Mysqldump-u Mysqluser-p test_db | gzip > test_db.sql.gz
When compressing, it is best to add the extension. gz to the filename, so that you have a good idea of the next time you restore the database.
2, restore the database
The command to restore the database is also simple, if the file you backed up is an uncompressed version, the command to restore the database is as follows:
Copy Code code as follows:
Cat Test_db.sql | Mysql-u mysqluser-p test_db
With the Cat command, the SQL script content is exported to the MySQL program for restoration. As you can see, some of the parameters behind MySQL are the same as at backup time.
If it is a compressed version of the backup file, you need to use the command to restore:
Copy Code code as follows:
Gunzip < test_db.sql.gz | Mysql-u mysqluser-p test_db
Similarly, with the Gunzip command, unzip, and then output the script content to the MySQL program to restore.
2012-10-08 Update:
If you do not consider compressing the. sql files, you can also export and import them separately with the following two statements.
Copy Code code as follows:
Mysqldump-u root-p test_db > Test_db.sql
Mysql-u Root-p test_db < Test_db.sql
Commands for backing up the MySQL database
Copy Code code as follows:
Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql
Backing up the MySQL database for a deleted table format
Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.
Copy Code code as follows:
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql
Directly compress MySQL database to backup
Copy Code code as follows:
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz
Backing up a MySQL database (some) tables
Copy Code code as follows:
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql
Backup multiple MySQL databases at the same time
Copy Code code as follows:
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql
Just back up the database structure
Copy Code code as follows:
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
Back up all databases on the server
Copy Code code as follows:
Mysqldump–all-databases > Allbackupfile.sql
command to restore MySQL database
Copy Code code as follows:
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql
Restoring a compressed MySQL database
Copy Code code as follows:
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName
Transferring a database to a new server
Copy Code code as follows:
Mysqldump-uusername-ppassword DatabaseName | mysql–host=*.*.*.*-C databas ENAME
