Summary of MySQL backup compression and Recovery database method

Generally 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 when the backup is transmitted, so we try to make the backup file smaller.

When writing an automatic backup script, it is best to compress the results of the backup directly and restore them directly from the compressed backup. The following describes how to use BZIP2 and gzip to compress MySQL backup files.

Backup and compress using bZIP:

The code is as follows
mysqldump | bzip2 > outputfile.sql.bz2

Restore from bzip2 Backup:

The code is as follows
Bunzip2 < OUTPUTFILE.SQL.BZ2 | MySQL < MySQL options>

Backup and use gzip compression:

The code is as follows
mysqldump | gzip > outputfile.sql.gz

Restore from gzip Backup:

Gunzip < outputfile.sql.gz | MySQL < MySQL options>

Supplement this article

Backing up the specified database

The code is as follows
Mysqldump-h hostname-u username-p databasename > Db.sql are assigned to the user's working directory if the path is not explicitly specified: C:documents and Settingsadministrator

You can explicitly specify a backup directory:

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

Description

-p After you do not enter the password, click the ENTER key before you need to enter the password.

Directly compress MySQL database to backup

The code is as follows
Mysqldump-h hostname-u username-p DatabaseName | gzip > db.sql.gz

Description

Gzip is the compression tool under Linux, so it is not available in Windows environment.

Backing up a MySQL database (some) tables

The code is as follows
Mysqldump-h hostname-u username-p databasename table1 table2 > Db.sql

Backup 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: ******windows can use MySQL Query browser's file– Open script to perform backup scripts, but also use the command to restore directly:

The code is as follows
Mysql-h hostname-u username-p DatabaseName < Backupfile.sql