205 lines
12 KiB
Bash
Executable File
205 lines
12 KiB
Bash
Executable File
#!/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
|