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!
Hey thanks, was desperately looking forward for this 🙂 Wanted to move my database as zipped format.
This will only single thread gzip (which limits to about 30MB/s max).
Use pigz for multithreaded compression of large databases.