Let’s consider a case where you have limited disk space available in your database server. In addition, you need a database backup quickly. However, you estimate that, the space available in the server for the backup is not enough.

One option would be to make the backup directly to a SAN or a NAS location. This requires a faster network and any delay might cause data loss due to database table locks. However, we can use MySQL options such as “--single-transaction” to overcome this. This means, any changes during the backup is skipped.

Best Solution

Therefore, the best option would be to save the backup as a compressed file. For instance, the one liner command shown below will backup the database as a compressed file. However, this is good for systems with higher memory.

# mysqldump -u user -p db_name | gzip > ~/db.sql.gz

Similarly, we can restore a database from a compressed backup too. For example, the command shown below will restore a compressed backup directly to the database server.

# gunzip < ~/db.sql.gz | mysql -u user -p db_name