Skip to content

Instantly share code, notes, and snippets.

@joshopkins787
Last active October 30, 2019 19:39
Show Gist options
  • Save joshopkins787/2faddae3c4730c116b8411403d5c8c67 to your computer and use it in GitHub Desktop.
Save joshopkins787/2faddae3c4730c116b8411403d5c8c67 to your computer and use it in GitHub Desktop.
Database Backup script for MariaDB/MySQL
# MySQL/mariaDB secure authorization file.
#
# To not have to put the password as an argument to the mysql commands, i.e. we don't want to
# use the -p option. Instead use a .my.cnf file in the home dir of the user you are running this
# script as, i.e. /root/.my.cnf if running as root. This way you can securely run mysqldump as
# root without the password being exposed in the process list. First create a .my.cnf in the home
# dir of root and make sure only root can read or change it by running the following three commands:
#
sudo touch /root/.my.cnf
sudo chown 0:0 /root/.my.cnf
sudo chmod 600 /root/.my.cnf
#
# It should contain something like mine:
# cat /root/.my.cnf
[mysql]
user=root
password=something
[mysqldump]
user=root
password=something
[client]
user=root
password=something
#!/bin/bash
#
#
# This script will backup all databases except for those excluded by egrep -v 'database1|database2'
#
# This script assumes that you have ssh-keys setup between the database server and the offsite
# storage server and a secure authorization file.
#
# To not have to put the password as an argument to the mysql commands, i.e. we don't want to
# use the -p option. Instead use a .my.cnf file in the home dir of the user you are running this
# script as, i.e. /root/.my.cnf if running as root. This way you can securely run mysqldump as
# root without the password being exposed in the process list. First create a .my.cnf in the home
# dir of root and make sure only root can read or change it by running the following
#
# three commands:
# 'sudo touch /root/.my.cnf'
# 'sudo chown 0:0 /root/.my.cnf'
# 'sudo chmod 600 /root/.my.cnf'
#
# then make sure it contains something like this:
# cat /root/.my.cnf
# [mysql]
# user=root
# password=something
#
# [mysqldump]
# user=root
# password=something
#
#[client]
# user=root
# password=something
# Database Server Name
dbsrv=$(hostname)
# Offsite storage location
offsite_srv=servername or IP# server FQDN or IP address to store a redundent of the backups
# Directory to which backup files will be written. Should end with slash ("/").
backups_dir="/data/bkup/db/" # temp or working director
backups_sql="/data/bkup/sql/" # directory where backups are stored
backups_archive="/data/bkup/archive/" # directory where archive copies are stored & offsite copies come from
offsite_dir="/data/bkup/" # directory on remote server where archive copies are uploaded to
# Number of days to retain copies on the local server
backup_retention=7
# Date/time included in the file names of the database backup files.
todaysDate=$(date +'%Y%m%d')
umask 007
renice 10 $$ >/dev/null
echo "########## Backing up MariaDB/MySQL databases"
echo " "
# The databases performance_schema and information_schema are not being backuped. You can exclude
# other databases by adding them in like 'performance_schema|information_schema|another_database'
cd $backups_db
for db in `mysql --batch --skip-column-names --execute="SHOW DATABASES" | egrep -v 'performance_schema|information_schema'`; do
/usr/bin/nice /usr/bin/mysqldump --events --ignore-table=mysql.event --single-transaction --quick --extended-insert "$db" > $backups_dir$db.sql | echo "########## Backup successfully compl
eted for " $db
done
echo " "
echo "########## All databases have been successfully copied"
echo " "
echo "########## Compressing files individually"
# Compresses all files that are not already compressed
for dbSQL in *.sql; do
bzip2 -v $backups_dir$dbSQL
done
echo " "
echo "########## Renaming files w/"$todaysDate"_ as prefix"
cd $backups_dir
for dsSQL in *.bz2; do
mv $dsSQL "$todaysDate""_"$dsSQL
done
echo " "
echo "########## All files have been compressed and renamed"
# Making date-stamped directory for storage and moving files
echo " "
echo "########## Creating" $backups_sql$todaysDate "folder"
mkdir $backups_sql$todaysDate
echo " "
echo "########## Moving files from" $backups_dir "to" $backups_dir$todaysDate
mv $backups_dir* $backups_sql$todaysDate
# Making single tar archive for offsite storage in archive directory
echo " "
echo "########## Archiving today's backup to" $backups_sql$todaysDate
tar -cvf $backups_archive"$todaysDate""_database.tar" $backups_sql$todaysDate
# Uses rsync via ssh to upload a copy of the database backups
echo " "
echo "########## Uploading to offsite storage on" $offsite_srv
ssh $offsite_srv "mkdir -p /data/bkup/$dbsrv"
rsync -avzPe 'ssh -p 22' $backups_archive"$todaysDate""_database.tar" $offsite_srv:$offsite_dir$dbsrv
echo "########## "$backups_archive"$todaysDate""_database.tar" "has been seccussfully uploaded"
# This will remove all local files that are older than 10 days
echo " "
echo "########## Removing local backups that more than " $backup_retention "days old"
/usr/bin/find $backups_sql* -maxdepth 0 -type d -mtime +$backup_retention -exec rm -rf {} \;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment