MySQL DBA Training - How to restore database from zipped backups in MySQL Server by using mysqldump

Scenario: 

You or other MySQL DBAs have created zipped backups of databases by using mysqldump. You need to restore the zipped backups to MySQL Server, Which command you will use to restore zipped backup to MySQL Server?

Solution:

The below command can be used to generate zipped backups for databases by using mysqldump if you have generate before.

shell > mysqldump --user=[uname] --password=[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

For example if you have testdb and you would like to run the compress backup, your command will look like below.

shell >  mysqldump --user=UserName --password=ProvidePassword testdb | gzip -9 > /mysqlbackup/testdb.sql.gz

Restore Zipped Backups to MySQL Server:

Below command can be used to restore zipped backup to MySQL Server.

shell > gunzip < backupfile.sql.gz | mysql --user=[uname] --password=[pass] [dbname]

Let's say you are restoring the testdb that we took above. Your command will look like below.

shell > gunzip < /mysqlbackup/testdb.sql.gz | mysql --user=backupuser  --password=ProvidePassword testdb

In case when you have taken backup of all databases to single backup file and you would like to restore all of them, you can use below command. 

shell > gunzip < /mysqlbackup/testdb.sql.gz | mysql --user=backupuser  --password=ProvidePassword 

Notice that, you don't have to provide the database name in above command if you want to restore all databases from file with all databases backup.

MySQL DBA Tutorial - Restore Zipped backup in MySQL Server