Easy site/database backup with mysqldump and crontab
For sites with databases, frequent backups are necessary. Anyone who has ever accidentally dropped a crucial database table will know this lesson very well. Here’s a quick an easy system for backups:
First, the Mysql backup.
Create a file in /home/user called .my.cnf
It’s format should be as follows:
[client]
user = db_user
password = "password"
host = localhost
Be sure to make the .my.cnf file limited readable (chmod 700 .my.cnf).
Then, crontab -e, and enter the following at the end of the file:
0 1 * * * /usr/bin/mysqldump mybb > /var/www/backup/`date +"%m-%d-%y"`.mysql
This will create daily backups of the Mysql database.
The database can be easily restored with the command:
mysql -u root -p root_password database_name < dumpfile.mysql
To copy your site weekly, add this line to crontab:
0 3 * * 1 /bin/tar -cvpzf /var/www/backup/`date +"%m-%d-%y"`.tar.gz /var/www/public
You can also copy your data on a weekly basis to a remote location by first running this command:
ssh-copy-id user@something.com
then adding this line to crontab -e
0 5 * * 1 /usr/bin/scp /var/www/backup/* user@remotehost.com:/var/www/backups