Statement summary of backup and restore MySQL database in command line mode
- 2020-05-13 03:43:05
- OfStack
To be safe, you need to back up or restore the database frequently. For MySQL, the most convenient method is probably to use phpMyAdmin's export and import functions, but if you have a large database, phpMyAdmin, as an Web application, may experience a "timeout" and fail. Therefore, it is necessary to learn how to backup and restore the database in command line mode.
1. Back up the database
Backup the MySQL database in Linux command line mode with the mysqldump command:
A brief explanation of the above command:
The & # 8226; -u means that you specify an MySQL username to connect to the database service. mysqluser is the MySQL username as shown above.
The & # 8226; -p means you need to have a valid password corresponding to the above username.
The & # 8226; The last parameter is the name of the database to be backed up: test_db
If you execute the above command directly, you will be prompted to enter the MySQL password. After the data password, it will directly display the backup SQL script on the screen, which is certainly not what you want. We need to backup the database to a file using the following command:
This will back up a file named test_db.sql in the current directory.
Of course, if the database size is large, the backup file will usually be compressed, backup and compression can be done in the same line of command:
When compressing, it's a good idea to add the.gz extension to the file name so you know what to expect the next time you restore the database.
2. Restore the database
Restore the database command is also very simple, if you backup the file is an uncompressed version, restore the database command is as follows:
With the cat command, output the SQL script content to the MySQL program for restoration. As you can see, the following parameters for MySQL are the same as those for the backup.
If it is a compressed version of the backup file, the following command is required to restore:
Similarly, with the gunzip command, extract and then output the script content to the MySQL program for restoration.
The 2012-10-08 update:
If you don't want to compress and package the.sql file, you can export and import it with the following two statements.
The command to backup the MySQL database
Backup the MySQL database in a format with dropped tables
Backing up the MySQL database is in a format with deleted tables, enabling the backup to overwrite the existing database without manually deleting the original database.
Directly compress the MySQL database backup
Back up some tables in the MySQL database
Backup multiple MySQL databases simultaneously
Just back up the database structure
Backup all databases on the server
The command to restore the MySQL database
Restore the compressed MySQL database
Move the database to the new server
1. Back up the database
Backup the MySQL database in Linux command line mode with the mysqldump command:
mysqldump -u mysqluser -p test_db
A brief explanation of the above command:
The & # 8226; -u means that you specify an MySQL username to connect to the database service. mysqluser is the MySQL username as shown above.
The & # 8226; -p means you need to have a valid password corresponding to the above username.
The & # 8226; The last parameter is the name of the database to be backed up: test_db
If you execute the above command directly, you will be prompted to enter the MySQL password. After the data password, it will directly display the backup SQL script on the screen, which is certainly not what you want. We need to backup the database to a file using the following command:
mysqldump -u mysqluser -p test_db > test_db.sql
This will back up a file named test_db.sql in the current directory.
Of course, if the database size is large, the backup file will usually be compressed, backup and compression can be done in the same line of command:
mysqldump -u mysqluser -p test_db | gzip > test_db.sql.gz
When compressing, it's a good idea to add the.gz extension to the file name so you know what to expect the next time you restore the database.
2. Restore the database
Restore the database command is also very simple, if you backup the file is an uncompressed version, restore the database command is as follows:
cat test_db.sql | mysql -u mysqluser -p test_db
With the cat command, output the SQL script content to the MySQL program for restoration. As you can see, the following parameters for MySQL are the same as those for the backup.
If it is a compressed version of the backup file, the following command is required to restore:
gunzip < test_db.sql.gz | mysql -u mysqluser -p test_db
Similarly, with the gunzip command, extract and then output the script content to the MySQL program for restoration.
The 2012-10-08 update:
If you don't want to compress and package the.sql file, you can export and import it with the following two statements.
mysqldump -u root -p test_db > test_db.sql
mysql -u root -p test_db < test_db.sql
The command to backup the MySQL database
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
Backup the MySQL database in a format with dropped tables
Backing up the MySQL database is in a format with deleted tables, enabling the backup to overwrite the existing database without manually deleting the original database.
mysqldump - � add-drop-table -uusername -ppassword databasename > backupfile.sql
Directly compress the MySQL database backup
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
Back up some tables in the MySQL database
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
Backup multiple MySQL databases simultaneously
mysqldump -hhostname -uusername -ppassword � databases databasename1 databasename2 databasename3 > multibackupfile.sql
Just back up the database structure
mysqldump � no-data � databases databasename1 databasename2 databasename3 > structurebackupfile.sql
Backup all databases on the server
mysqldump � all-databases > allbackupfile.sql
The command to restore the MySQL database
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
Restore the compressed MySQL database
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
Move the database to the new server
mysqldump -uusername -ppassword databasename | mysql � host=*.*.*.* -C databasename