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.