Create a compressed MySQL database dump/backup (Linux)

This may go without saying, but backing up your database regularly is important. Hard disks can fail at anytime causing you to lose all your data (We recommend using a RAID configuration when possible for data redundancy!) Often times, we may overlook the database and simply make backups of our /www or /public_html folder that contains our HTML, PHP, CSS, etc. Below is a command that even database newbies can run to backup their data:

Compressed MySQL Database Dump

mysqldump -u <DB USERNAME> --password=<DB PASSWORD> --all-databases | gzip > mysql_alldb_$(date -d "today" +"%Y-%m-%d").sql.gz

This command will dump the SQL of ALL of your databases on the server and then compress it with GZip. The file it creates will contain the datestamp (i.e.  mysql_alldb_2013-02-22.sql.gz) so that you can easily tell when it was created. Also, this command will work well in a nightly CRON job. If you have any questions, don’t hesitate to ask!

See also  SQL ERROR -532 THE RELATIONSHIP CONSTRAINT-NAME RESTRICTS THE DELETION OF ROW WITH RID X'RID-NUMBER'

Support us & keep this site free of annoying ads.
Shop Amazon.com or Donate with Paypal

2 thoughts on “Create a compressed MySQL database dump/backup (Linux)”

  1. This will only single thread gzip (which limits to about 30MB/s max).

    Use pigz for multithreaded compression of large databases.

Leave a Comment