How to create zipped Backup and Restored zipped Backup in MySQL or MariaDB

Scenario:

You are working as MySQL or MariaDB Admin, You have some big databases those you would like to take backup. It is good idea to take compressed backups for large databases. How you would perform compress backup and restore them on another server?

Solution:

To take the compressed backup, you will use below command.

shell > mysqldump -u [uname] -p[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 -u root  -pDBA123 testdb | gzip -9 > /mysqlbackup/testdb.sql.gz

To RESTORE the compressed backup to a database, you will use below syntax, Make sure to create the empty database first.

shell > gunzip < backupfile.sql.gz | mysql -u [uname] -p[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 -u root  -pDBA123 testdb

To see the demo, please watch video : How to take compress backup and restore in MySQL or MariaDB