Mysql Backup Script

Index

• Mysql dump command
• Upload file to server
• Delete older file
• Add script to crontab
• Restore the backup

Mysql Dump Command

The command to create a mysql backup is mysqldump. The follwing script executes the backup and save it to a specified local folder. Edit the config variables to generate the backup with your DB configuration.

#! /bin/bash
### MySQL Server Login Info ###
MUSER="[your_mysql_user]"
MPASS="[your_mysql_passwor]"
MHOST="[your_mysql_host]"
MPORT="[your_mysql_port]"
DBNAME="[your_mysql_database]"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
### File Info ###
# Save backup in temp directory before upload to server
TEMPDIR="[your_bkp_temp_dir]" #Exp: ./bkp_temp
# Name of bkp file (before the date)
FILENAME="[your_bkp_file_name_root]" #Exp: my_prj_daily_bkp_
NOW=$(date +%F)
#Create and clean temp directory
[ ! -d "$TEMPDIR" ] && mkdir -p "$TEMPDIR"
rm -f "$TEMPDIR"/*.*
# Create bkp
TFILENAME="$FILENAME""$NOW".sql
FILE=$TEMPDIR/$TFILENAME
$MYSQLDUMP -u $MUSER -h $MHOST -P $MPORT  -p$MPASS $DBNAME > $FILE

This is the script to perform the backup:

$MYSQLDUMP -u $MUSER -h $MHOST -P $MPORT  -p$MPASS $DBNAME > $FILE

And this is an example with clear variables:

mysqldump -u root -h localhost -P 3306  -proot testdb > bkp_daily_2018-10-10.sql

Upload file to server FTP

You have multiple solutions to upload a file with ftpin a server.
In this case (for our ftp server connection) we use this solution:

### FTP SERVER Login info ###
FTPU="[your_ftp_username]"
FTPP="[your_ftp_password]"
FTPS="[your_ftp_server]"
DESTDIR="[your_ftp_destination_dir]"
ftp -n $FTPS <<END_SCRIPT
quote USER $FTPU
quote PASS $FTPP
binary
put $FILE $DESTDIR/$TFILENAME
quit
END_SCRIPT

($FILE and $TFILENAME are generated in the step before)
This open a connection with the FTP server and put the file in the directory you specify.

Delete older file

Sometimes it is not necessary to store all the daily backups, the disk space would finish in a short time. One solution is to keep all backups, for example, from the last month (perhaps by doing a separate monthly backup).
This is a solution, using the date in the name of the backup file, to delete files in the FTP server.

listing=`ftp -i -n $FTPS <<EOMYF
quote USER $FTPU
quote PASS $FTPP
binary
cd $DESTDIR
ls $FILENAME*
quit
EOMYF
`

This script opens the connection to FTP server and returns the list of backup names with our base name.

lista=( $listing )
# loop over our files
for ((FNO=0; FNO<${#lista[@]}; FNO+=9));do
    # month (element 5), day (element 6) and filename (element 8)
    # filename
    f=${lista[`expr $FNO+8`]}
    IFS='_' read -r -a arrayF <<< "$f"
    IFS='.' read -r -a arrayD <<< "${arrayF[2]}"
    # Split name and get date
    d=${arrayD[0]}
    DAY=$(date -d "$d" +%Y-%m-%d)
    diff=$(( ($(date -d $NOW +%s) - $(date -d $DAY +%s) )/(60*60*24) ))
    # Remove older file
    if [[ $NDAYS -lt $diff ]];
    then
        ftp -n $FTPS <<END_SCRIPT
        quote USER $FTPU
        quote PASS $FTPP
        binary
        cd $DESTDIR
        delete $f
        quit
END_SCRIPT
fi
done

For each file, the script get the date from the name and if it's older than $NDAYS, it connects to FTP server and delete the file.

Add script to crontab

crontab is a Linux command used to schedule operations (jobs) using the cron daemon. The cron daemon reads the crontab file and follows the operations set there (cronjob) at the specified time and completely automatically.

Basic commands

List

crontab -l

Show the content of the crontab.

Remove

crontab -r

Remove all crontab jobs.

Edit

crontab -e

Open a vim editor and let you to modify your crontab config.

Schedule a job with crontab

In our crontab we can insert all jobs we want. We have to specify only the execution date and the job to execute.

This is the format to insert a new activities

[minutes] [hour] [month day] [month] [week day] [path to script]

For example:

00 00 * * * /usr/script/mysql-backup.sh

This line execute our mysql backup script every day at 00:00 (midnight).

All

00 00 * * * /usr/script/mysql-backup.sh

For example this is every day at 00:00.

Repeater

*/5 00 * * * /usr/script/mysql-backup.sh

This example runs the script every 5 minutes

Range

00 9 1-4 * * /usr/script/mysql-backup.sh

This example runs the script at 9:00 on the first 4 days at every month.

List

00 9 * 6,12 * /usr/script/mysql-backup.sh

This example runs the script every day at 9:00 on the month of june (6) and december (12).

Shortcut

• @reboot Run job at system reboot
• @yearly Run job once a year [0 0 1 1 *]
• @annually Run job once a year [0 0 1 1 *]
• @monthly Run job every month [0 0 1 * *]
• @weekly Run job every week [0 0 * * 0]
• @daily Run job every day [0 0 * * *]
• @midnight Run job every day [0 0 * * *]
• @hourly Run job every hour [0 * * * *]

Schedule our job

Finaly we can schedule our mysql script in crontab.
We decide to run every day. This is the final command:

@daily /usr/script/mysql-backup.sh

Restore the backup

Finally if you need to restore a mysql backup (hope for test, not for revert production stuff) we need to identify our correct backup and retrive from FTP server.
Once you have your file you can run:

mysql -u [username] -p [DB name] < [file.sql]

To different host:

mysql -h [host] -u [username] -p [DB name] < [file.sql]

You can find full code on this Github repository.