MySQL Dump
This is a short example as to how I back up and restore my entire MySQL database. I used root to dump my databases in my examples, but this would be insecure if you where to implement it as a cron job. I would suggest creating an account with global SELECT privileges. It’s also important not to use –password= as you do not want your root password to appear in your .bash_history or with ps -ex. Instead use –password, this way you will be prompted for it. And if your database contains sensitive information such as unhashed passwords be sure use a tool such as GnuPG to encrypt it after it has been compressed.
$ mysqldump –all-databases –opt –user=’root’ –password | gzip > mysqldump-`date +%Y%m%d-%T`.sql.gz
This snippet of code will dump all databases into a compressed GnuZIP file with a unique name ending with the current date and time. –all-databases will dump all tables in all databases. To keep the command short you can use -A. –opt means optimize and is a shortcut for –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset. As of 4.1 –opt is enabled by default.
Always remember, computer users fall into two groups: Those that do backups and those that have never had a hard drive fail. You did store that MySQL dump in a secure off-shore location right? Good, then mount that tape drive into your server and copy the compressed file into your newly restored server. Uncompress the file with GnuZIP and import it into your database. Your file name will differ.
$ gzip -d “mysqldump-20060710-03:00:01.sql.gz”
$ mysql –user=’root’ –password < “mysqldump-20060710-03:00:01.sql”