Automate your database backups using cron jobs in cpanel

I’ve been trying to do this off and on for years. Finally, finally I found the answer, and it is actually working! This is for people hosting with a service that uses Cpanel. Quick step by step:

1. Log into cpanel and choose the MySQL Database link
2. Create a database user named “backup” and add that user with full permissions to each database you have
3. Go back to the cpanel home and choose the Cron jobs link
4. If you want to run your backup at midnight every night, then choose the Advanced option
5. Advanced: Leave all of the “Minute Hour Day Month Weekday” options at their default
6. Standard: Use the form fields to choose how often you would like to run your backups, and at what time.
7. BOTH: Paste the following line into the Command field (There are only two changes that need to be made to the line below: change DBPASSWORD to the database password you created for your MySQL user “backup” and change YOURACCOUNT to your account username)
   date=`date -I` ; mysqldump -ubackup -pPASSWORD -A | gzip > /home/YOURACCOUNT/xbackup_$date.sql.gz
8. Click Commit changes and you are all done.

This should create a gzipped file in the root directory of your account named something like xbackup_2008-02-21.sql.gz. Now you can use something cool like FTP Synchronizer to create your own backups. More on that later.

Filed under Website development |