Kitz ADSL Broadband Information
adsl spacer  
Support this site
Home Broadband ISPs Tech Routers Wiki Forum
 
     
   Compare ISP   Rate your ISP
   Glossary   Glossary
 
Please login or register.

Login with username, password and session length
Advanced search  

News:

Author Topic: MySQL backup  (Read 329 times)

jelv

  • Helpful
  • Kitizen
  • *
  • Posts: 1344
MySQL backup
« on: February 22, 2019, 10:53:47 AM »

I have developed a script which will do a daily/weekly/monthly rotation of SQL backups which I am offering here if anyone finds it useful. The number of each type of backup to be kept is configurable as is which day of the week the weekly backup is to be run on. The monthly backup replaces the weekly backup on the first weekly backup day each month.

There are two files the script itself and a configuration file which is to be placed in the same directory as the script. The script should be set to run once a day at a suitable time by cron. It creates a separate backup for each database that the configured database user has permissions to. It is  recommended that a specific backup user is created and given permissions on the databases to be backed up.

Configuration file: save as my "mysqlbackup.conf"
Code: [Select]
# mysqlbackup parameters - save this file in the same directory as the script

# Day of week to create weekly backup 0 = Sunday, 1 = Monday etc.
weeklydayofweek=0

# keep_days     Number of daily backups to keep - usually 6
# keep_weeks    Number of weekly backups to keep
#               4 works well if monthly backs are used
# keep_months   Number of monthly backups to keep
#               Set to 0 to inhibit monthly backup
# Monthly backups are taken in place of the first weekly backup each month
daystokeep=6
weekstokeep=4
monthstokeep=12

# mySQL DB user and password with select, views and lock permission on all databases
dbuser=xxxxxxx
dbpw=ppppppppp

# Logging level
#   0 Nothing
#   1 Errors
#   2 Warnings
#   3 Informationals
log_level=3

# Base directory for backups (optional) - must give (or expand to) full path
# If not set defaults to directory containing the script
#backupdir=~/backupsxxx

Script mysqlbackup.sh:
Code: [Select]
#!/bin/bash
# mysqlbackup v1.0
#
# Make automatic backups of mysql databases
#
# No changes are to be made to this script
# Parameters are set in the configuration file


thisdir="$( cd "$( dirname "${BASH_SOURCE[0]}" )" >/dev/null && pwd )"

log=$thisdir/mysqlbackup.log

# Load user parameters
source $thisdir/mysqlbackup.conf

# Force Sunday to 0 if user set weeklydayofweek as 7 (to match date %w)
weeklydayofweek=$(($weeklydayofweek%7))

# Internal variable setup
ymd=$(date +%Y%m%d)
dest=
cur_month_day=$(date --date=$ymd +"%d")
cur_week_day=$(date --date=$ymd +"%w")
error=
databases=
mysqld_c=

# ==============================================================================
# Define error and logging functions

function error ()
{ echo -e "ERROR: $1" ; if (( $log_level > 0 )) ; then echo $(date +"%Y-%m-%d %T") " ERROR $1" >> $log ; fi ; exit 1 ; }

function warn ()
{ echo -e "WARNING: $1" ; if (( $log_level > 1 )) ; then echo $(date +"%Y-%m-%d %T") " WARNING $1" >> $log ; fi ;  }

function info ()
{ echo -e "INFO: $1" ; if (( $log_level > 2 )) ; then echo $(date +"%Y-%m-%d %T") " INFO $1" >> $log ; fi ; }

function infolog ()
{ echo -e "INFO: $1" ; echo $(date +"%Y-%m-%d %T") " INFO $1" >> $log ; }
# ==============================================================================

# Start script
# Checking if prerequisites are met
if [[ -z "$log_level" ]] ; then log_level="0" ; warnlater="Errorlevel not set in config, setting to 0 (nothing)" ; fi
infolog "MySQL Backup STARTED"

# Checking variables from config file
if [[ -n "$warnlater" ]] ; then warn "$warnlater" ; fi
if [[ -z "$daystokeep" ]] ; then daystokeep="6" ; warn "days to keep backup not set in config, setting to 6" ; fi
if [[ -z "$weekstokeep" ]] ; then weekstokeep="5" ; warn "weeks to keep backup not set in config, setting to 5" ; fi
if [[ -z "$monthstokeep" ]] ; then monthstokeep="3" ; warn "months to keep backup not set in config, setting to 3" ; fi
if [[ -z "$weeklydayofweek" ]] ; then weeklydayofweek="0" ; warn "weekly rotate day not set in config, setting to Sunday" ; fi
if [[ -z "$dbuser" ]] ; then error "MySQL user for backup not set in config" ; fi
if [[ -z "$dbpw" ]] ; then error "MySQL password for backup not set in config" ; fi

# Base directory for backups
if [[ -z "$backupdir" ]] ; then backupdir="$thisdir" ; fi
if ! [ -d "$backupdir" ] ; then error "Backup directory $backupdir does not exist" ; fi
info "Backing up databases to $backupdir"

# Check if mysqldump supports routines switch

if [[ "$(mysqldump --routines)" = *"unknown"* ]]
 then
   mysqld_c=mysqldump" -eqQ --single-transaction"
 else
   mysqld_c=mysqldump" -eqQR --single-transaction"
fi

# Set sub-directory name depending on whether today is a daily, weekly or monthly backup
# The first weekly in a calenday month is the monthly backup (if required)
 if (( $cur_week_day == $weeklydayofweek )) ; then
if (( $cur_month_day <= 7 )) && (( $monthstokeep != 0 )); then
{ # First weekly of month
dest=monthly;
  backupstokeep=$monthstokeep;
  infolog "Creating monthly backup(s)";
}
else
{
dest=weekly;
backupstokeep=$weekstokeep;
    infolog "Creating weekly backup(s)";
}
fi
else
    {
    dest=daily;
    backupstokeep=$daystokeep;
    infolog "Creating daily backup(s)";
    }
fi

# Check for temp folder
if ! [ -d "$backupdir/temp" ] ; then info "temp folder has been created" ; mkdir "$backupdir/temp" ; if [ $? != 0 ] ; then error "the folder temp could not be created" ; fi ; fi

# Get list of all the databases
databases=$(mysql -u $dbuser -p$dbpw -Bse 'show databases' | sed "/\b\(information_schema\|performance_schema\)\b/d")
if [ $? != 0 ] ; then error "cannot list databases, is password correct?" ; fi

# ===============================================================================
# Process the databases

while read line
do
set $line
echo
infolog "Database $line"

# Check for database folder
full="$backupdir/$line"
if ! [ -d "$full" ] ; then info "Creating folder for database $line" ; mkdir "$full" ; if [ $? != 0 ] ; then error "Create folder $full failed" ; fi ; fi

# Check for appropriate daily/weekly/monthly folder
full="$backupdir/$line/$dest"
if ! [ -d "$full" ] ; then info "Creating folder $dest for database $line" ; mkdir "$full" ; if [ $? != 0 ] ; then error "Create folder $full failed" ; fi ; fi

# Create backup
info "Backing up database to ${line}_$ymd.sql"
$mysqld_c -u $dbuser -B $line --password=$dbpw > "$backupdir/temp/${line}_$ymd.sql"
if [ $? != 0 ]; then error "creating new backup when trying to access the database $line" ; fi

info "Compressing backup to $full/${line}_${ymd}.tar.gz"
tar 2> /dev/null -czvf "$full/${line}_${ymd}.tar.gz" -C "$backupdir/temp" "${line}_$ymd.sql" --remove-files &>/dev/null
if [ $? != 0 ] ; then error "compressing backup" ; else info "Done compressing backup" ; fi

# Remove old backups
info "Deleting old backups"
for target in $(ls -t "$full" | tail -n +$(($backupstokeep + 1 ))) ; do rm -f "$full/$target"; done
if [ $? != 0 ] ; then error "erasing old daily backups" ; fi

done<<<"$databases"

echo

infolog "MySQL Backup COMPLETED"
Logged
Line rental: Pulse8, Broadband: AAISP Home::1 FTTC 80/20, Mobile: id Mobile

tiffy

  • Reg Member
  • ***
  • Posts: 549
Re: MySQL backup
« Reply #1 on: February 22, 2019, 11:43:26 AM »

@jelv:

Many thanks for making your script available to forum members.
I would like to copy for future reference, can you possibly advise ?

Can copy & paste the script's into "Notepad++" or "Sublime Text" editors but not sure which format to store as to ensure functionality within a suitable environment, both editors offer many formats.
Logged
Plusnet 40/10 - DSLStats/RPi - ZyXEL VMG1312-B10A - Huawei Cab - MDWS (sadly gone 12/03/18)

j0hn

  • Kitizen
  • ****
  • Posts: 2517
Re: MySQL backup
« Reply #2 on: February 22, 2019, 12:13:09 PM »

.conf

If that isn't offered then save as .txt and rename to .conf

.sh for the 2nd part
Logged
Plusnet FTTC 80/20 -  ECI now Huawei cab
retx low @ 3dB target SNRM
Zyxel VMG1312-B10A bridged with 1508 MTU + Asus RT-AC68U running Asuswrt-Merlin

tiffy

  • Reg Member
  • ***
  • Posts: 549
Re: MySQL backup
« Reply #3 on: February 22, 2019, 12:36:35 PM »

Thanks j0hn,

Yes, *.txt is the default save option on both editors but due to my lack of knowledge on SQL scripting was not sure if it was that simple as just saving as .txt and re-naming.
 
Logged
Plusnet 40/10 - DSLStats/RPi - ZyXEL VMG1312-B10A - Huawei Cab - MDWS (sadly gone 12/03/18)

jelv

  • Helpful
  • Kitizen
  • *
  • Posts: 1344
Re: MySQL backup
« Reply #4 on: February 22, 2019, 01:57:53 PM »

As j0hn answered - just save as text files and rename if needed.
Logged
Line rental: Pulse8, Broadband: AAISP Home::1 FTTC 80/20, Mobile: id Mobile
 

anything