Backup Shell Script

Updated on 03 Oct 2020

Writing a bash script for MySQL backups

We would want daily and monthly backups plus automatically purge backups after a certain time frame.

Backup script

set up some date variables

Here I am using Python to format a couple of date variables.

date1 will be used to append to the filename
date2 is a day of month, and is used to determine if we are running the monthly backup.

backup MySQL databases

Here I am using mysqldump to backup the database(s) to a file. $date1 variable is appended to the filename. I am also using --no-data flag to get a separate structure backup.

monthly backup

I am using bash conditional statement to work out if the day of month falls on the 3rd. If it does, then I perform a database backup and the filename is appended with the value of $date1 plus monthly.

I also call a separate PHP script to backup the user accounts. I probably should look at rewriting it in python.

remove old copies (2 months for daily, 2 years for monthly)

I am using the find command with a few flags to delete old backup files. I am keeping daily backups for 2 months, and monthly backups for 2 years.

#!/bin/bash

#-----
#--set up some date variables
#-----
date1=`/usr/bin/python3 -c 'import datetime; today = datetime.date.today(); mama = today.strftime("%Y%m%d"); print(mama)'`
date2=`/usr/bin/python3 -c 'import datetime; today = datetime.date.today(); mama = today.strftime("%d"); print(mama)'`
backup_dir="/home/brent/mysql_backup/databases"
#-----


#-----
#--backup MySQL databases
#-----
mysqldump --databases yii_yii1 > $backup_dir"/yii_yii1_"$date1".sql"
mysqldump --no-data --databases yii_yii1 > /home/brent/mysql_backup/databases/yii_yii1-tools-dump-structure_$date1".sql"

mysqldump --databases demoapp > $backup_dir"/demoapp_"$date1".sql"
mysqldump --no-data --databases demoapp > $backup_dir"/demoapp-dump-structure_"$date1".sql"
#-----


#-----
#--monthly backup
#-----
if test $date2 == '12' ; then
    mysqldump --databases yii_yii1 > $backup_dir"/yii_yii1_"$date1".sql.monthly"
    mysqldump --no-data --databases yii_yii1 > $backup_dir"/yii_yii1-dump-structure_"$date1".sql.monthly"

    mysqldump --databases demoapp > $backup_dir"/demoapp_"$date1".sql.monthly"
    mysqldump --no-data --databases demoapp > $backup_dir"/demoapp-dump-structure_"$date1".sql.monthly"

   #--backup the users.
   /usr/bin/php /home/brent/mysql_backup/mysql_user_backup.php > $backup_dir"/users_"$date1".sql.monthly"

fi
#-----


#-----
#--remove old copies (2 months for daily, 2 years for monthly)
#-----
find /home/brent/mysql_backup/databases -type f -name *.sql -mtime +60 -delete 
find /home/brent/mysql_backup/databases -type f -name *.monthly -mtime +735 -delete
#-----

Script to backup MySQL user accounts

The following script does the following:

gets the users from the user table
iterates over the users feeding the user into show grants for ...
output is SQL that can be re-run to create and load user accounts

$db = new PDO('mysql:host=localhost;dbname=mysql;charset=utf8',
              'backup_user',
              'myPassword');

$query = "SELECT CONCAT('\'',user,'\'@\'',host,'\''), authentication_string
          FROM   mysql.user
          WHERE  user != 'root'";

$result = $db->query($query);

while($row = $result->fetch(PDO::FETCH_NUM))
{
    $query = "show grants for $row[0]";
    $stmt  = $db->query($query);

        echo "\n\n-- Create User Account for $row[0]";
        echo "\nCREATE USER $row[0] IDENTIFIED BY PASSWORD '$row[1]';";

        echo "\n\n-- Privileges for $row[0]";
        while($grant = $stmt->fetch(PDO::FETCH_NUM)) {
                echo "\n$grant[0];";
        }


        echo "\n\n-- ---------------------------------------------";
}

echo "\n\nflush privileges;\n";