Automatic database backups can be set up very easily using a simple shell script and a cron job. However it may not be a practical solution for huge databases and the restoration process can also be difficult. There are various tools available to make this process simple. Sypex Dumper is just one of them.
What is Sypex Dumper
Sypex Dumper is a software product (PHP-script), which can help you create a backup copy (dump, export) of a MySQL database, and also restore the database from the backup file if needed. Read more and download
With this tool huge databases can be backed up and restored with very high speed using least server resources and greatly reducing the size of the database dumps.
Free version of the tool is enough to create the automatic backups. The Paid version allows to selectively restore a particular table from the entire database.
Steps to set up automatic Database Backups
Let’s assume you want to create backup of your database every day and keep the recent 30 backups on your server
Create the required job in SXD
1. Login to Sypex Dumper with your database user credentials
2. Click on the Export option
3. Select the database from the Database drop down
4. Since we want to keep only the last 30 database backups add 30 in the Autodelete If number of files more than box.
5. Add some comments e.g. Last 30 backups
6. Clicking the Save button will create the backup job with the name specified
Create the shell script to execute the job
Now that the job is created we want to execute it using a shell script. Here is a sample script
# Syntax : <php path> <sxd path> -j=<job_name>
php $sxd_path -j=DATABASE_JOB_NAME
echo "Database Backup taken successfully at $(date)..." > $log_path
Automate the script
Finally just add the above script to a cron job so that it runs once daily