MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.
The mysqlbackup command is an easy-to-use tool for all backup and restore operations. Following script creates scheduled incremental backup. MySQL Enterprise Backup is available on enterprise edition of mysql database only.
Create a user for backup specific task.
create user 'bckadmin'@'127.0.0.1' identified by 'hahaha_password';
Login to mysql as user root and grant following privileges to backup admin user;
GRANT RELOAD ON *.* TO 'bckadmin'@'127.0.0.1';GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'bckadmin'@'127.0.0.1';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'bckadmin'@'127.0.0.1';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'bckadmin'@'127.0.0.1';
GRANT REPLICATION CLIENT ON *.* TO 'bckadmin'@'127.0.0.1';
GRANT SUPER ON *.* TO 'bckadmin'@'127.0.0.1';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bckadmin'@'localhost';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bckadmin'@'127.0.0.1';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bckadmin'@'localhost';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bckadmin'@'127.0.0.1';
flush privileges;
Create a directory where executable, configuration, include and exclude files can be placed. Following files should be created under newly create directory.
mkdir /opt/mysqlbckCreate blank file with the name of include.database that contains list of the database to be backup. The content of this file will be generated at run time. So better create blank file for it.
# include.database
cd /opt/mysqlbck
touch include.database
touch include.database
Create file, exclude.database that contains list of the database to be exclude from the backup.
# exclude.database
cd /opt/mysqlbck
echo "test" >> exclude.database
cd /opt/mysqlbck
echo "test" >> exclude.database
echo "project_test" >> exclude.database
Create executable file, online_backup.sh that contains the code that will backup the database online.
# online_backup.sh
#!/bin/bash
# Author : Dilli Maharjan(dilliraj.maharjan@gmail.com)
# Date : 2015-07-25
# Description : Script to backup mysql database Online
# Variable Declaration
DATE_N_TIME=$(date +%Y-%m-%d_%H%M%S)
DATE=$(date +%Y_%m_%d)
BACKUP_TYPE=''
BACKUP_OPT=''
CMD=''
COMMENT=''
ADDITIONAL_OPT=''
WORKING_DIR="$(dirname $0)"
. ${WORKING_DIR}/online_backup.conf
EXCLUDE_FILE="${WORKING_DIR}/exclude.database"
INCLUDE_FILE="${WORKING_DIR}/include.database"
FULLBCK_EXISTS=0
declare -a last_backup
# Find either backup history table exists or not.
BHTBL_EXISTS=$(/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "select count(*) from information_schema.tables where upper(table_name)='BACKUP_HISTORY' and upper(table_schema)='MYSQL';" 2>/dev/null | grep -v "+--")
FULLBCK_EXISTS=$(/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "select count(*) from mysql.backup_history where backup_type='FULL';" 2>/dev/null | grep -v "+--")
[ ${DEBUG} -eq "1" ] && echo "INFO: Backup table exists ${BHTBL_EXISTS}"
# Checking whether first day of the month or backup information table exists or not.
if [ $(date +%d) == "01" ] || [ "${BHTBL_EXISTS}" -eq 0 ] || [ "${FULLBCK_EXISTS}" -eq 0 ] ; then
BACKUP_TYPE="Full_backup"
BACKUP_OPT="backup-and-apply-log --backup-dir="
COMMENT="Full Backup of ${DB}"
ADDITIONAL_OPT="--connect_timeout=7200"
else
last_backup=( $(/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "select binlog_pos, binlog_file, start_lsn, end_lsn from mysql.backup_history where backup_type='FULL' order by end_lsn desc limit 1;" 2>/dev/null | grep -v "+--"))
BACKUP_TYPE="Diff_backup"
BACKUP_OPT="backup --incremental-backup-dir="
COMMENT="Differential Backup of ${DB}"
ADDITIONAL_OPT="--incremental --start-lsn=${last_backup[3]} --connect_timeout=7200 "
fi
[ ${DEBUG} -eq "1" ] && echo "INFO: BACKUP_TYPE ${BACKUP_TYPE}, BACKUP_OPT ${BACKUP_OPT}, COMMENT ${COMMENT}, ADDITIONAL_OPT ${ADDITIONAL_OPT}"
# Declare the log dir, logfile, and backup dir
LOG_FILE="${LOG_DIR}/${BACKUP_TYPE}_${DATE_N_TIME}.log"
BACKUP_DIR1="${BACKUP_DIR}/${BACKUP_TYPE}_${DATE_N_TIME}"
# Creating backup directory and logdirectory if doesnot exists
[ ! -e ${LOG_DIR} ] && mkdir -p ${LOG_DIR}
[ ! -e $BACKUP_DIR1} ] && mkdir -p ${BACKUP_DIR1}
# Increasing wait timeout to 2 hrs
/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "set global wait_timeout=7200;" 2>/dev/null
# Backup recent include database file and generate new include file
/bin/mv -f ${INCLUDE_FILE} ${INCLUDE_FILE}_001 && /usr/bin/script -c "/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e \"SHOW GLOBAL STATUS LIKE 'Uptime';\"" -a -f ${LOG_FILE}
# Generate fresh new include.database file which contains the all the database that need to be included in the backup
/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "show databases;" | sed s/\+\-//g | grep -v -f ${WORKING_DIR}/exclude.database > ${WORKING_DIR}/include.database
# Start backup of the database.
/usr/bin/script -c "/usr/local/bin/mysqlbackup --slave-info ${ADDITIONAL_OPT} ${BACKUP_OPT}${BACKUP_DIR1} --databases-list-file=${INCLUDE_FILE} --comments=\"${COMMENT}\" --user=${USER_BCKADMIN} --password=${PASS_BCKADMIN} --host=127.0.0.1 --read-threads=1 --write-threads=1 --process-threads=1" -a -f ${LOG_FILE}
sleep 7200
# Reverting wait timeout to 1min
/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "set global wait_timeout=60;" 2>/dev/null
# logging the uptime in the logfile
/usr/bin/script -c "/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e \"SHOW GLOBAL STATUS LIKE 'Uptime';\"" -a -f ${LOG_FILE}
# Include latest copy of my.cnf in the backup
cp /etc/my.cnf ${BACKUP_DIR1}/
# Compress the recently created backup and delete the source.
/usr/bin/script -c "/bin/tar --remove-files -czvf ${BACKUP_DIR1}.tar.gz ${BACKUP_DIR1}" -a -f ${LOG_FILE}
sleep 1200
# Replace password with ******** in logfile
/bin/sed -i 's/${PASS_BCKADMIN}/********/g' ${LOG_FILE}
# Purge backup on the first day of the month
[ $(date +%d) == "01" ] && ${WORKING_DIR}/purge_backup.sh ${BACKUP_DIR}>> ${LOG_FILE}
# Mail detail log to the admins.
/bin/mail -r "${SENDER}" -s "${SUBJECT}" ${RECEPTIANTS} < ${LOG_FILE}
Create configuration file online_backup.conf. This file includes the credential to be used while backup, backup location and mail configurations.
#online_backup.conf
# Either to enable debug mode or not
DEBUG=1
# Force to consider Fullbackup exists Do not change the value of this variable
FULLBCK_EXISTS=0
# Creadential for generating backup
PASS_BCKADMIN="hahaha_password"
USER_BCKADMIN="bckadmin"
# Declaring variables for the mail
DB="dillidb"
SENDER="database@dilli.com.np"
SUBJECT="Backup Report ${DB} Database ${DATE}"
RECEPTIANTS="_dba@dilli.com.np"
# Backup and log directory
LOG_DIR="/bck/OnlineBackup/logs"
BACKUP_DIR="/bck/OnlineBackup/mysqlbackup"
Create file purge_backup.sh This file cleanup the old backup files created on basic of retention period in months.
# purge_backup.sh
#!/bin/bash
if [ "$#" -lt 1 ]; then
echo "USAGE: $0: <Backup_dir> [Retention month: default 1]"
exit 1
fi
# Variables declaration
RETENTION_MNTH=1
[ "$2" != "" ] && RETENTION_MNTH=$1
PURGE_MONTHS=$(expr ${RETENTION_MNTH} + 1)
#echo $RETENTION_MNTH
#echo $PURGE_MONTHS
PURGE_FILE_WC=$(date +%Y_%m -d "-${PURGE_MONTHS} months")
BACKUP_DEST=$1
/bin/echo "Current date $(date +%Y_%m)"
/bin/echo "Purge date ${PURGE_FILE_WC}"
/bin/echo "Purging Following files from backup destination"
/bin/ls -1 ${BACKUP_DEST}/*${PURGE_FILE_WC}*
/bin/rm -f ${BACKUP_DEST}/*${PURGE_FILE_WC}*
if [ $? -eq 0 ]; then
/bin/echo "Backup Purged Successfully"
else
/bin/echo "Backup Purged Failed"
fi
Make the script file executable.
chmod 755 online_backup.*
chmod 755 purge_backup.sh
Backup can be scheduled and executed via cron job.s We can set the cron job as below.
# cron setting
05 00 * * * /home/dilli/bin/online_backup.sh > /dev/null
