There are various reasons we probably don't have to explain, but backups are important. While your hosting company usually takes care of the backups, sometimes they require you to restore a whole server in order to get access to a single missing table row. Having your own backup of the database can be useful and doesn't cost much if you have a small database.
We are going to create a simple shell script we can run using cron to create hourly backups.
First we need to make sure that
mysqldump can access our database without asking for a password. In order to do that, we create a new file called
.my.cnf in the home of the user which will run the cronjob. Put the following content in it, but change the user and password to match yours:
[mysqldump] user=YOUR_USER password=YOUR_PASSWORD
Next we are going to create a directory called
db-backups in our home directory, then we create a shell script called
db-backup.sh in our home directory with the following content:
#!/bin/sh find ~/db-backups/* -mtime +30 -type f -delete mysqldump YOUR_DATABASE | bzip2 -c > ~/db-backups/YOUR_DATABASE-$(date +%Y-%m-%d-%H.%M.%S).sql.bz2
The find command removes all files older than a month to keep the necessary storage limited.
mysqldump command creates a dump file, compresses it using
bzip2and keeps it in our
Our shell script needs to be executable, let's make that happen:
chmod +x db-backup.sh
You can now test your script by running
If everything is running fine you can type
crontab -e and paste the following snippet in it to run the cronjob every hour:
Save the cronjob and wait for the magic to happen, one new file every hour!