#!/bin/bash ############################################################################################## # # # 88 # # 88 # # 88 # # ,adPPYba, 8b,dPPYba, ,adPPYba, 88 ,adPPYba, ,adPPYba, 88,dPYba,,adPYba, # # I8[ "" 88P' `"8a a8P_____88 88 a8" "" a8" "8a 88P' "88" "8a # # `"Y8ba, 88 88 8PP""""""" 88 8b 8b d8 88 88 88 # # aa ]8I 88 88 "8b, ,aa 88 888 "8a, ,aa "8a, ,a8" 88 88 88 # # `"YbbdP"' 88 88 `"Ybbd8"' 88 888 `"Ybbd8"' `"YbbdP"' 88 88 88 # # # # # ############################################################################################## # # # Author: Snel.com - Yavuz Aydin # # E-mail: support@snel.com # # (c) Snel.com - all rights reserved # # # ############################################################################################## # # # Script: backup_mysql.sh # # License: This file is licensed under the GPLv3 License # # Purpose: Create local MySQL backups # # # ############################################################################################## # # # Changelog: # # # ############################################################################################## # # # 2021/11/16 1.12 Changes to triggers and procedures # # 2021/10/01 1.11 Show error message if mysql binaries are not found # # 2021/07/27 1.10 Added PATH to include /usr/local/sbin and /usr/local/bin # # 2021/07/26 1.9 Added MYSQL_BIN, MYSQLADMIN_BIN and MYSQLADMIN_BIN variables # # 2020/12/30 1.8 Added DBS_SKIP to prevent dumping certain databases # # 2020/02/27 1.7 Added username and password and set default retention to 1 day # # 2019/01/28 1.6 Fixed backups of databases with dash (-) in name # # 2019/01/21 1.5 Fixed creation of databases with dot (.) in name # # 2018/02/11 1.4 Fixed detection of mydumper # # 2017/11/09 1.3 Fixed multiple backups on a day support # # 2017/07/23 1.2 Added mydumper / myloader support # # 2016/02/19 1.1 Added --events to suppress warning # # 2016/02/06 1.0 First release # # # ############################################################################################## # # # Restore # # # ############################################################################################## # # # Assuming you want to restore mytestdb from your backups made on 20190121-1540: # # # # mysqldump: # # # # DB='mytestdb' # # BACKUPDIR='/var/backup/mysql/20190121-1540' # # mysql -e "CREATE DATABASE IF NOT EXISTS ${DB}" # # for table in ${BACKUPDIR}/${DB}/*; do gunzip -c $table | mysql ${DB}; done # # # # With myloader (backups should have been created with mydumper): # # # # DB='mytestdb' # # BACKUPDIR='/var/backup/mysql/20190121-1540' # # myloader --directory=${BACKUPDIR}/ --overwrite-tables -s ${DB} # # # ############################################################################################## # # # CONFIGURATION # # # ############################################################################################## # MySQL host, uncomment if you want to use this instead of the default localhost #MYSQL_HOST='localhost' # MySQL administrative user, uncomment if you want to use this instead of # the value in /root/.my.cnf #MYSQL_USER='root' # MySQL administrative user password, uncomment if you want to use this instead of # the value in /root/.my.cnf #MYSQL_PW='ThisIsMyMySQLRootPassword' # Set PATH to include /usr/loca/ PATH="/usr/local/sbin:/usr/local/bin:${PATH}" # Log file BAKUP_LOG=/var/backup/log/mysql-backup.log # Backup Base directory BASE_BAK_FLDR=/var/backup/mysql # Backup rotation period. RM_FLDR_DAYS="+1" ############################################################################################## # # # DO NOT EDIT BELOW THIS # # # ############################################################################################## # Check whether mydumper is installed and whether we need to use it USE_MYDUMPER=0 which mydumper &> /dev/null if [ "$?" == "0" ]; then MYDUMPER=$(which mydumper) USE_MYDUMPER=1 fi # Set paths MYSQL_BIN=$(which mysql) MYSQLADMIN_BIN=$(which mysqladmin) MYSQLDUMP_BIN=$(which mysqldump) if [[ -z ${MYSQL_BIN} ]]; then echo "Couldn't find mysql binary, exiting"; exit 1; fi if [[ -z ${MYSQLADMIN_BIN} ]]; then echo "Couldn't find mysqladmin binary, exiting"; exit 1; fi if [[ -z ${MYSQLDUMP_BIN} ]]; then echo "Couldn't find mysqldump binary, exiting"; exit 1; fi # Check if MYSQL_USER or MYSQL_PW is set and set options accordingly MYSQL_OPTS="" if [[ -n ${MYSQL_HOST} && "${MYSQL_HOST}" != "" ]]; then MYSQL_OPTS="${MYSQL_OPTS} -h ${MYSQL_HOST}" fi if [[ -n ${MYSQL_USER} && "${MYSQL_USER}" != "" ]]; then MYSQL_OPTS="${MYSQL_OPTS} -u ${MYSQL_USER}" fi if [[ -n ${MYSQL_PW} && "${MYSQL_PW}" != "" ]]; then MYSQL_OPTS="${MYSQL_OPTS} -p${MYSQL_PW}" fi # Get list of Databases except the pid file DBS_SKIP="'performance_schema','information_schema'" DBS_LIST=$(${MYSQL_BIN} ${MYSQL_OPTS} -s -N -e "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN (${DBS_SKIP});") index=0 # Check if we can connect to the mysql server; otherwise die if [ ! "$(id -u -n)" = "root" ]; then echo -e "Error: $0 : Only user 'root' can run this script" exit 100 fi ${MYSQLADMIN_BIN} ${MYSQL_OPTS} status >> /dev/null 2>&1 if [ $? -ne 0 ]; then echo "Error: Unable to connect to MySQL Server, exiting!" exit 101 fi # Backup process starts here. START_DATETIME=$(date +%Y%m%d-%H%M) # Flush logs prior to the backup. ${MYSQL_BIN} ${MYSQL_OPTS} -e "FLUSH LOGS" if [ $USE_MYDUMPER -eq 1 ]; then # Use mydumper DB_BKP_FLDR=${BASE_BAK_FLDR}/${START_DATETIME} [ ! -d ${DB_BKP_FLDR} ] && mkdir -p ${DB_BKP_FLDR} && chmod 700 ${DB_BKP_FLDR} ${MYDUMPER} -o ${DB_BKP_FLDR} -G -E -R --less-locking -r 500000 -c -e -L ${DB_BKP_FLDR}/mydumper.log -v 3 else # Use traditional mysqldump # Loop through the DB list and create table level backup, # applying appropriate option for MyISAM and InnoDB tables. for DB in ${DBS_LIST}; do DB_BKP_FLDR=${BASE_BAK_FLDR}/${START_DATETIME}/${DB} [ ! -d ${DB_BKP_FLDR} ] && mkdir -p ${DB_BKP_FLDR} && chmod 700 ${DB_BKP_FLDR} # Get the schema of database with the stored procedures. # This will be the first file in the database backup folder ${MYSQLDUMP_BIN} ${MYSQL_OPTS} -R -d --add-drop-trigger --single-transaction ${DB} | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip -c > ${DB_BKP_FLDR}/000-DB_SCHEMA.sql.gz index=0 #Get the tables and its type. Store it in an array. table_types=($(${MYSQL_BIN} ${MYSQL_OPTS} -e "show table status from \`${DB}\`" | awk '{ if ($2 == "MyISAM" || $2 == "InnoDB") print $1,$2}')) table_type_count=${#table_types[@]} # Loop through the tables and apply the mysqldump option according to the table type # The table specific SQL files will not contain any create info for the table schema. # It will be available in SCHEMA file while [ "${index}" -lt "${table_type_count}" ]; do START=$(date +%s) TYPE=${table_types[${index} + 1]} table=${table_types[${index}]} #echo -en "$(date) : backup ${DB} : ${table} : ${TYPE} " if [ "${TYPE}" = "MyISAM" ]; then DUMP_OPT="${DB} --skip-triggers --no-create-info --tables --events --quote-names " else DUMP_OPT="${DB} --skip-triggers --no-create-info --single-transaction --tables --events --quote-names " fi ${MYSQLDUMP_BIN} ${MYSQL_OPTS} ${DUMP_OPT} ${table} |gzip -c > ${DB_BKP_FLDR}/${table}.sql.gz index=$((${index} + 2)) #echo -e " - Total time : $(($(date +%s) - ${START}))\n" done done fi # Rotating old backup. according to the 'RM_FLDR_DAYS' if [ ! -z "${RM_FLDR_DAYS}" ]; then #echo -en "$(date) : removing folder : " find ${BASE_BAK_FLDR}/ -maxdepth 1 -mtime ${RM_FLDR_DAYS} -type d -exec rm -rf {} \; #echo fi