Database backup like a boss

22nd Oct, 2014 | mysql

We often need to create and restore database backups to migrate data between servers or run backups. The mysqldump command creates a series of mysql insert commands that is good for readability but not so good for file size. It's recommended to compress before transferring across a network but that means another command, and then further commands to cleanup afterwards. Compressing can often reduce file sizes to a tenth of the original. Sometimes however the uncompressed file simply won't fit in the file system, likely for large data stored when the system is running low on disk space.

In the situations it's best to pipe the mysqldump straight into a compression utility so there is no intermediate file. On the other end likewise the compressed file can be uncompressed and the output piped to mysql.

It's simple to backup like a boss:

mysqldump -p -u   | gzip > backup.sql.gz

and then

gunzip < backup.sql.gz | mysql -u -p