#!/bin/bash #--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---# ## script was written with daily, dated, backups from holland backup # ## mysqldump per table in directories with date/time like ## /backups/daily/20221104_65127/backup_data/database-name.sql.gz ## ### path to archive directory. e.g., /home/backupusr/backups/daily/ ARCHPATH='/path/to/backup/daily/' ### sudirectory from date. e.g., /backup_data/ SUBDIR='backup_data' ### databases to skip. e.g., mysql SKIPDBS=('mysql') ### restore only changed databases or restore all FULLRESTORE=false ### database to use for master log position SETPOSDB=db_for_master_log_position ### config file for mysql credentials CFG='.my.cnf' ### dry-run will show actions for testing DRY_RUN=false ### noprompt for skipping user input NOPROMPT=false ### do not start will not restart replication when complete DONOTSTART=false ### file to log output LOG_FILE="_$(basename $0).$(date +%F_%T).log" ### values for configuring replication master ### empty if replication already configured MASTER_HOST='' MASTER_USER='' MASTER_PASSWORD='' # # #--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---# while [[ $# -gt 0 ]]; do case $1 in -d|--dry-run) DRY_RUN=true shift 1 ;; -a|--archive) ARCHPATH=$2 shift 2 ;; -sd|--subdir) SUBDIR=$2 shift 2 ;; -sk|--skip) SKIPDBS=($2) shift 2 ;; -f|--full) FULLRESTORE=true shift 1 ;; -p|--pos) SETPOSDB=$2 shift 2 ;; -c|--cfg) CFG=$2 shift 2 ;; -l|--log) LOG_FILE=$2 shift 2 ;; -nl|--nolog) LOG_FILE='' shift 2 ;; -ns|--nostart) DONOTSTART=true shift 1 ;; -np|--noprompt) NOPROMPT=true shift 1 ;; -mh|--masterhost) MASTER_HOST=$2 shift 2 ;; -mu|--masteruser) MASTER_USER=$2 shift 2 ;; -mp|--masterpass) MASTER_PASSWORD=$2 shift 2 ;; -h|--help) echo -e "\n\n\t _____________________________/‾‾‾ MySQL restoration script options ‾‾‾\____________________________________\n" echo -e "\t | arg | | type | default |\n" echo -e "\t ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾" echo -e "\t -h| --help display this menu [ flag ]" echo -e "\t -d| --dry-run no permanent changes, show plan [ flag ] $DRY_RUN" echo -e "\t -a| --archive directory containing archive directories [ string ] $ARCHPATH" echo -e "\t-sd| --subdir directory containing actual compressed sql files [ string ] $SUBDIR" echo -e "\t-sk| --skip databases to not restore (e.g. mysql would replace users) [ array ] $SKIPDBS" echo -e "\t -f| --full restore all tables not excluded (no comparison) [ array ] $FULLRESTORE" echo -e "\t -p| --pos datbase from which master log position will be used [ string ] $SETPOSFILE" echo -e "\t -c| --cfg path of config file for mysql credentials [ string ] $CFG" echo -e "\t -l| --log path of log file [ string ] $LOG_FILE" echo -e "\t-nl| --nolog disable logging [ string ] $([[ $LOG_FILE ]] && echo false || echo true)" echo -e "\t-ns| --nostart do not restart replication [ flag ] $DONOTSTART" echo -e "\t-np| --noprompt do not prompt user (Y/n) [ flag ] $NOPROMPT" echo -e "\t-mh| --masterhost replication master host address [ string ] $MASTER_HOST" echo -e "\t-mu| --masteruser user name for replication [ string ] $MASTER_USER" echo -e "\t-mp| --masterpass password for replication [ string ] $([[ $MASTER_PASSWORD ]] && echo true || echo false)" echo -e "\nEXAMPLE: $0 -p main_db -sd backup_data -a /home/backups/mysql/daily -sk 'mysql performance_stats' --dry-run -np" exit ;; *) echo 'error in command line parsing' >&2 echo "try: '$0 --help" exit 1 esac done function hecko() { if [[ $LOG_FILE ]]; then echo -e "$1" | tee -a $LOG_FILE else echo -e "$1"; fi } function xct() { if [[ $DRY_RUN == true ]]; then hecko "\t$(tput setaf 3)EXEC PLAN: $(tput sgr 0)$1" else hecko "\t$(tput setaf 5)EXECUTE: $(tput sgr 0)$1"; eval $1; fi } ARCHPATH="${ARCHPATH%/}/" SUBDIR="${SUBDIR%/}/" hecko "\n\n\t________/‾‾‾ MySQL restoration script options ‾‾‾\___________________\n" hecko "\tarchive path = $ARCHPATH" hecko "\tsubdirectory = $SUBDIR" hecko "\tDBs to skip = ${SKIPDBS[@]}" hecko "\tfull restore = $FULLRESTORE" hecko "\tDB for position = $SETPOSDB" hecko "\tconfig file = $CFG" hecko "\tdry run = $DRY_RUN" hecko "\n" hecko "\treplication master = $MASTER_HOST" hecko "\treplication user = $MASTER_USER" hecko "\treplication password = $([[ $MASTER_PASSWORD ]] && echo true || echo false)" hecko "\n" hecko "\tlog file = $([[ $LOG_FILE ]] && echo $LOG_FILE || echo false)" hecko "\t_____________________________________________________________________________\n" if [[ -f "$CFG" ]]; then if [[ ! $(grep user $CFG) ]]; then hecko "ERROR: 'user' field not found in '$CFG'" exit 1 fi if [[ ! $(grep password $CFG) ]]; then hecko "ERROR: 'password' field not found in '$CFG'" exit 1 fi else hecko "ERROR: configuration file '$CFG' not found" exit 1 fi if [[ $NOPROMPT == false ]]; then read -n 1 -p "restore? [Y/n] " reply; if [[ $reply == [Nn] ]]; then hecko "\naborting" exit 1 fi fi hecko "\nrestoring" START=$(date +%s) hecko "\n--START: $(date +%F\ %T)\nrestoring most recent backups from $ARCHPATH" hecko "\tstopping replication"; xct "mysql -e 'STOP SLAVE;'" NEWDATE=$(ls -1r "$ARCHPATH" | head -1) NEWPATH="$ARCHPATH$NEWDATE/$SUBDIR" if [[ $FULLRESTORE == false ]]; then OLDDATE=$(ls -1rt "$ARCHPATH" | head -1) OLDPATH="$ARCHPATH$OLDDATE/$SUBDIR" hecko "\n\ncompartive restoration selected:\n\tfiles from:\t'$NEWDATE'\n\tcompared to:\t'$OLDDATE'" else hecko "\n\nfull restorion selected: comparisons will not be made, all non-excluded databases will be restored" fi hecko "\nfinding files in '$NEWPATH*.gz'" for FILE in $NEWPATH*.gz; do STARTLOOP=$(date +%s) FILE=$(basename $FILE) hecko "\n$(date +%F\ %T) | $FILE:" if [[ "${SETPOSDB}" =~ ${FILE%%.*} ]]; then hecko "\t-- recognized '$FILE' for master position and log file" SETPOSFILE=$NEWPATH$FILE SETPOSSTMT=$(zcat $SETPOSFILE | head -40 | grep MASTER | sed 's/-- //') hecko "\t$SETPOSSTMT" fi if [[ "${SKIPDBS[*]}" =~ ${FILE%%.*} ]]; then hecko "\t-- skipping $FILE";continue; fi; if [[ FULLRESTORE == false ]]; then OLDSIZE=$(stat -c %s $OLDPATH$FILE) else OLDSIZE=0 fi NEWSIZE=$(stat -c %s $NEWPATH$FILE) if [[ $OLDSIZE != $NEWSIZE ]]; then hecko "\t$OLDDATE:$FILE:$OLDSIZE != $NEWDATE:$FILE:$NEWSIZE" DB=$(echo $FILE | cut -f1 -d'.') hecko "\tDROPPING/CREATING '$DB' pv $NEWPATH$FILE | gunzip | mysql $DB" xct "mysql -e 'DROP DATABASE IF EXISTS $DB'" xct "mysql -e 'CREATE DATABASE $DB'" xct "pv $NEWPATH$FILE | gunzip | mysql $DB;" fi ENDLOOP=$(date +%s) EXCTIME=$(expr $ENDLOOP - $STARTLOOP) hecko $(printf '\t%02dh:%02dm:%02ds\n' $((EXCTIME/3600)) $((EXCTIME%3600/60)) $((EXCTIME%60))) done if [[ $MASTER_HOST && $MASTER_USER && $MASTER_PASSWORD ]]; then hecko "\napplying configuration for replication\n\thost: $MASTER_HOST\n\tuser: $MASTER_USER" xct 'mysql -e "CHANGE MASTER TO master_host=$MASTER_HOST,master_user=$MASTER_USER,master_password=$MASTER_PASSWORD;"' else hecko "\nwarning: replication credentials not present; this might be bad if replication host is not already configured" fi if [[ $SETPOSSTMT ]]; then xct "mysql -e '$SETPOSSTMT'" else DONOTSTART=true hecko "\nmaster position database undefined, replication will not be started\n\tlisting suggestions:" for FILE in $NEWPATH*.gz; do hecko $(basename $FILE) hecko "\t$(zcat $FILE | head -40 | grep MASTER)" done fi if [[ $NOPROMPT == false ]]; then read -n1 -p -t600 "restart replication? [Y/n] " reply; if [[ $reply == [Nn] ]]; then DONOTSTART=true fi fi if [[ $DONOTSTART != true ]]; then hecko "\nrestarting replication" xct "mysql -e 'START SLAVE'" fi END=$(date +%s) EXCTIME=$(expr $END - $START) hecko $(printf '\t%02dh:%02dm:%02ds\n' $((EXCTIME/3600)) $((EXCTIME%3600/60)) $((EXCTIME%60))) hecko "\n--END: $(date +%F\ %T)\n\n" exit 0