It is important to backup your database. I case of an error, you can easily restore all data. You do not have to do the backup process manually.
I wanted to automate my MySQL database backup workflow. To do this, I created a shell script, which collects all database data and stores it into a file. After creation, the script compresses the file and uploads it to another server. I use another server to decrease the risk of data loss. If my primary production server fails, I still have all backups on another physical system.
Create a new database user
It is highly recommended creating a new database user with limited permissions.
You can restrict the new user to the following permissions:
- LOCK TABLES
- SHOW VIEW
You can set these permissions global or for every needed database/table.
To use this user during crontab backup, you have to create a file in your home directory called .my.cnf and insert the user credentials. The file should look like this:
[mysqldump] user= password=
What does the shell script look like?
Create a new file sqlbackup.sh and paste the following code:
#!/bin/bash today=`date '+%Y_%m_%d'`; mysqldump DATABASE_NAME > /PATH_TO_BACKUP_FOLDER/dump-$today.sql tar -cPf /PATH_TO_BACKUP_FOLDER/dump-$today.tar /PATH_TO_BACKUP_FOLDER/dump-$today.sql rm /PATH_TO_BACKUP_FOLDER/dump-$today.sql backupdir='/PATH_TO_BACKUP_FOLDER' DAYS_OLD=30 for d in $backupdir ; do if [ -d "$d" ] ; then FILE="$d/"$(ls "$d" -t1 | head -n 1) if test $(find "$FILE" -mtime +$DAYS_OLD) ; then rm $FILE fi fi done
To get this code running, you need to replace 2 placeholders: DATABASE_NAME, PATH_TO_BACKUP_FOLDER!
The code will also delete local backup files older than 30 days. If you want to increase or decrease this value, change DAYS_OLD.
You can also add the server upload algorithm in this file.
How to configure the CRON?
On an Ubuntu system execute crontab -e. This will open an editor. At the end of the file insert:
0 1 * * * /PATH_TO_BACKUP_FOLDER/sqlbackup.sh
This command will then execute every day at 01:00 AM.
Please comment below, if you have any questions.