Summary of Mysql backup compression and database restoration methods

In general, we use mysqldump to back up the MySQL database and upload it to other backup machines. If the database is large, it may be slow during Backup transmission, so we try to make the backup file smaller.

When writing an automatic backup script, it is best to compress the backup result directly, and the backup can be recovered directly from the compressed backup. The following describes how to use bzip2 and gzip to compress mysql backup files.

Back up and Compress With bzip:

The Code is as follows:
Mysqldump | bzip2> outputfile. SQL .bz2

Recover from bzip2 backup:

The Code is as follows:
Bunzip2 <outputfile. SQL .bz2 | mysql <mysql options>

Back up and Compress With gzip:

The Code is as follows:
Mysqldump | gzip> outputfile. SQL .gz

Restore from gzip backup:

Gunzip <outputfile. SQL .gz | mysql <mysql options>

Supplement this article

Back up a specified database

The Code is as follows:
Mysqldump-h hostname-u username-p databasename> If db. SQL does not specify a path, it is assigned to the user's working directory: C: Documents and SettingsAdministrator.

You can specify the Backup Directory explicitly:

The Code is as follows:
Mysqldump-u root-p mydb-h 192.168.14.204> D: mydb. SQL

Note:

-P: you do not need to enter the password. Click the Enter key to enter the password.

Directly compress and back up the MySQL database

The Code is as follows:
Mysqldump-h hostname-u username-p databasename | gzip> db. SQL .gz

Note:

Gzip is a compression tool in linux, so it cannot be used in windows.

Back up a MySQL database table

The Code is as follows:
Mysqldump-h hostname-u username-p databasename table1 table2> db. SQL

Back up multiple MySQL databases at the same time

The Code is as follows:
Mysqldump-h hostname-u username-p-databases db1 db2 db3> dbs. SQL

Back up all databases on the server

The Code is as follows:
Mysqldump -- all-databases> allbackupfile. SQL

Test:

The Code is as follows:
Mysqldump -- all-databases-u root-p> allbackupfile. SQL

Enter password: ****** in windows, you can use the File-> Open Script of Mysql Query Browser to execute the backup Script. You can also use the following command to directly restore the backup:

The Code is as follows:
Mysql-h hostname-u username-p databasename <backupfile. SQL