Using Cron To Automatically Backup MySql Database
I run this family genealogy website using open source software called webtrees.
Webtrees recommends using MYSQL as its database.
I’ve spent so much time putting together the website that having to start all over again would be a total disaster.
Part of my backup routine is to backup the database every night. This is how I do it.
The assumption is that you’ve already setup a database with a user and password.
- Database name: mysql-database-name
- Database user: mysql-user-name
- Database user password: mysql-user-password
Switch linux user’s home directory
Create a file called *.my.cnf in the user’s home directory.*
sudo vim .my.cnf
Then type the following:
user = mysql-user-name
password = mysql-user-password
The benefit to creating .my.cnf is you don’t have to pass the user-name and user-password when executing a MYSQL command. Of course, there are security risks by using this method. But, since I’m the only one who has access to my server, I’m not that concerned unless my imaginary twin evil brother starts acting up again.
Since .my.cnf is a hidden file, you have to the following command to show it in the directory.
To do a manual mysql backup, here’s the command:
mysqldump database-name > /directory of backup/mysql-backup-filename.sql
The code is run at the general linux command line. You do NOT run it within the MYSQL command line client.
Remember, this command only works if you’ve already created the .my.cnf. with the credentials.
Also, by creating the .my.cnf file, you get to the MYSQL command line by simply typing mysql at the linux command line prompt.
If there’s a LOCK TABLES ERROR when
mysqldump is run, you need to add
--single-transaction to mysqldump.
mysqldump --single-transaction database-name > /directory of backup/mysql-backup-filename.sql
Webtrees usually gets installed with its data folder in this location:
To backup webtrees, you have to backup the data folder and database.
So, I backup the database to the webtrees data folder. Then backup the entire webtrees data folder using duplicity. You can read how I use duplicity here.
The code to manually backup the database is:
mysqldump database name > /var/www/html/webtrees/data/mysql-backup-filename.sql
I’m a pretty lazy person, so I automated the process by creating a script and running it in cron. Of course, you can use whatever location to backup the database.
Switch to the root user
Make a directory to store the jobs
Change to the directory you just created
Make the backup file that’ll hold the script
Add the following two lines to the backup script:
mysqldump database-name > /var/www/html/webtrees/data/mysql-backup-filename.sql
I then added this as a cronjob as follows:
$ crontab -e
This will open the crontab editor/scheduler.
Add the backup script that you saved to the scheduler by typing:
5 1 * * * sh /home/username/cronjobs/backup-mysql.sh
Then save and close.
This code performs a daily backup at 1:05 A.M. The date/time that’s used is the time that’s on the server. This is likely different than the current time where you live. To see the time on the server, the command is:
You can go to CronTab Guru to figure out the schedule expression you want.
By the way, I wrote this so in six months when I need to do it again on another server, I’d have a reference.
To see what cronjobs are scheduled:
To make sure that cron will run in the background:
sudo systemctl enable cron
Documentation is Everything.