MySQL backup and restore commands with gzip and bzip2

We need to backup MySQL database regularly and for that mysqldump is a excellent tool provided for the specific purpose. It creates a .sql file for the database backup file. This tool creates SQL statements of DROP & CREATE TABLES, INSERT INTO statement, DROP & CREATE statements for PROCEDURE, TRIGGERS and FUNCTIONS.

Some times database(s) grows rapidly thus we need to compress while backing up the data to reduce the size of the backup file. We can use gzip or bzip2 for compression. gzip is installed by default in the operating system.

If bzip2 is not present in the system we can issue following commands to install it.

for Debian-based operating systems [with APT]
$ apt-get update
$ apt-get install bzip2

for CentOS, Fedora, RedHat Operating systems
$ yum update
$ yum install bzip2

for custom installation from source file
-download bzip2 source file [currently it is http://www.bzip.org/1.0.6/bzip2-1.0.6.tar.gz]
$ wget http://www.bzip.org/1.0.6/bzip2-1.0.6.tar.gz
$ tar zxvf bzip2-1.0.6.tar.gz
$ cd bzip2-1.0.6
$ make$ make install

 

Backup of single database only:

mysqldump -u dbuser -p --databases dbname1 > dump.sql  [no compression]
mysqldump -u dbuser -p --databases dbname1 | gzip > dump.sql.gz  [gzip]
mysqldump -u dbuser -p --databases dbname1 | bzip2 > dump.sql.bz2  [bzip2]

Backup of all databases:

mysqldump -u dbuser -p --all-databases > dump.sql [no compression]
mysqldump -u dbuser -p --all-databases | gzip > dump.sql.gz  [gzip]
mysqldump -u dbuser -p --all-databases | bzip2 > dump.sql.bz2  [bzip2]

Backup of multiple databases [not all]:

mysqldump -u dbuser -p --databases dbname1 dbname2 > dump.sql
mysqldump -u dbuser -p --databases dbname1 dbname2 | gzip > dump.sql.gz  [gzip]
mysqldump -u dbuser -p --databases dbname1 dbname2 | bzip2 > dump.sql.bz2  [bzip2]

Backup of all databases with Triggers:

mysqldump -u dbuser -p --triggers --all-databases > dump.sql
mysqldump -u dbuser -p --triggers --all-databases | gzip > dump.sql.gz  [gzip]
mysqldump -u dbuser -p --triggers --all-databases | bzip2 > dump.sql.bz2  [bzip2]

Backup of all databases with  Procedures and Functions:

mysqldump -u dbuser -p --routines --all-databases > dump.sql
mysqldump -u dbuser -p --routines --all-databases | gzip > dump.sql.gz  [gzip]
mysqldump -u dbuser -p --routines --all-databases | bzip2 > dump.sql.bz2  [bzip2]

 

We need to create appropriately named database on the target machine that needs to be restored from the remote backup.

Restore all database from the dump file:

mysql -u dbuser -p < dump.sql  [no compression]
gunzip < dump.sql.gz | mysql -u dbuser -p  [gzip]
bunzip2 < dump.sql.bz2 | mysql -u dbuser -p   [bzip2]

Restore single database from the dump file:

mysql -u dbuser -p dbname1 < dump.sql  [no compression]
gunzip < dump.sql.gz | mysql -u dbuser -p dbname1 [gzip]
bunzip2 < dump.sql.bz2 | mysql -u dbuser -p dbname1  [bzip2]

3 Comments

    • Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

Leave a Comment.