Setting up auto backup of your MySQL database
Backing up your data is one of the most important things to do. Here is a guide on how to set up auto backup of your MySQL database.
First thing to consider when setting up automated backups of your database is how often it should run. If you are running something like a Webshop, it is important to have more regular backups than if you run a site where you just fetch static data. Either way, backups are not something you want to do manually. You can easily forget to do it or prioritise other tasks above it. Since you are reading this guide, you are probably in the process of finding ways to make backups automatically, so you will never have to do it yourself again. Good news: Keep on reading and I will shop you how it is done!
In this guide I will use MySQL on a linux-server.
I assume you have some knowledge about how to access to your server using SSH. You can start with that.
Once you are logged in, make a folder where you want to keep your backups.
Now make a new file. Name it something like backup.sh
touch backup.sh
Open the file using your favourite editor:
vi backup.sh
Copy / paste this script:
#!/bin/bash
BackupPath="{PathToYourChosenBackupFolder}"
Date=$( date +"%Y-%m-%d-%T" )
FileName=db_$date.sql
umask 177
mysqldump -u {USERNAME} -p{PASSWORD} {YOURDB} > $BackupPath/$FileName
find $BackupPath/* -mtime +5 -exec rm {} \;
Now change the following variables to fit your server and database:
{PathToYourChosenBackupFolder}
Your chosen backup directory. Example: /home/user/backup
{USERNAME}
The MySQL username
{PASSWORD}
The MySQL password
{YOURDB}
The database you want to backup
Save it and exit the editor. Now you can test the script to see if it has the desired result. If it does, all you need to do to finish is to set up a cronjob to make it run automatically.
The script keeps backups for 5 days. You can easily change that by changing the number in the last line just after the ‘+’.
As a last tip, it is not advisable to keep the backups on the same server as you database runs on. Move them to another server to be safe.
1 Response