#!/usr/bin/env bash
# Function description:
# Backup MySQL databases for each, backup schema and schema with data in one action.
# Usage:
# bash BackupMysqlByDate.sh
# Birth Time:
# 2016-06-24 17:44:43.895515929 +0800
# Author:
# Open Source Software written by 'Guodong Ding '
# Blog: http://dgd2010.blog.51cto.com/
# Github: https://github.com/DingGuodong
# Others:
# crontabs -- configuration and scripts for running periodical jobs
# SHELL=/bin/bash
# PATH=/sbin:/bin:/usr/sbin:/usr/bin
# MAILTO=root
# HOME=/
# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
# m h dom mon dow command
# execute on 11:59 per sunday
# 59 11 * * */0 /path/to/BackupMysqlByDate.sh >/tmp/log_backup_mysql_$(date +"\%Y\%m\%d\%H\%M\%S").log
# or
# execute on 23:59 per day
# 59 23 * * * /path/to/BackupMysqlByDate.sh >/tmp/log_backup_mysql_$(date +"\%Y\%m\%d\%H\%M\%S").log
USER="`id -un`"
LOGNAME="$USER"
if [ $UID -ne 0 ]; then
echo "WARNING: Running as a non-root user, \"$LOGNAME\". Functionality may be unavailable. Only root can use some commands or options"
fi
old_PATH=$PATH
declare -x PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games"
mysql_host=127.0.0.1
mysql_port=3306
mysql_username=dev
mysql_password=dev
mysql_basedir=/usr/local/mysql
save_old_backups_for_days=5
mysql_bin_mysql=${mysql_basedir}/bin/mysql
mysql_bin_dump=${mysql_basedir}/bin/mysqldump
mysql_backup_dir=/data/backup/db/mysql
date_format_type_dir=$(date +%Y-%m-%d)
date_format_type_file=$(date +%Y%m%d%H%M%S)
echo "--------------------------------"
echo "=> do backup scheduler start at $(date +%Y%m%d%H%M%S)"
# TODO, check user privileges
# check user if have 'RELOAD,EVENT' privileges,etc
# backup role
# GRANT ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EXECUTE,INDEX,INSERT,LOCK TABLES,SELECT,UPDATE,SHOW VIEW,RELOAD,EVENT ON *.* TO 'dev'@"%";
# FLUSH PRIVILEGES;
[ -d ${mysql_basedir} ] && mysql_datadir=${mysql_basedir}/data || mysql_datadir=/var/lib/mysql
[ -x ${mysql_bin_mysql} ] || mysql_bin_mysql=mysql
[ -x ${mysql_bin_dump} ] || mysql_bin_dump=mysqldump
[ -d ${mysql_backup_dir}/${date_format_type_dir} ] || mkdir -p ${mysql_backup_dir}/${date_format_type_dir}
mysql_databases_list=""
if [ -d ${mysql_datadir} ]; then
mysql_databases_list=`ls -p ${mysql_datadir} | grep / |tr -d /`
else
mysql_databases_list=$(${mysql_bin_mysql} -h${mysql_host} -P${mysql_port} -u${mysql_username} -p${mysql_password}\
-e "show databases;" |& grep -Eiv '(^database$|information_schema|performance_schema|^mysql$)')
fi
saved_IFS=$IFS
IFS=' '$'\t'$'\n'
for mysql_database in ${mysql_databases_list};do
${mysql_bin_dump} --host=${mysql_host} --port=${mysql_port} --user=${mysql_username} --password=${mysql_password}\
--routines --events --triggers --single-transaction --flush-logs \
--ignore-table=mysql.event --databases ${mysql_database} |& \
gzip > ${mysql_backup_dir}/${date_format_type_dir}/${mysql_database}-backup-${date_format_type_file}.sql.gz
[ $? -eq 0 ] && echo "${mysql_database} backup successfully! " || \
echo "${mysql_database} backup failed! "
/bin/sleep 2
${mysql_bin_dump} --host=${mysql_host} --port=${mysql_port} --user=${mysql_username} --password=${mysql_password}\
--routines --events --triggers --single-transaction --flush-logs \
--ignore-table=mysql.event --databases ${mysql_database} --no-data |& \
gzip > ${mysql_backup_dir}/${date_format_type_dir}/${mysql_database}-backup-${date_format_type_file}_schema.sql.gz
[ $? -eq 0 ] && echo "${mysql_database} schema backup successfully! " || \
echo "${mysql_database} schema backup failed! "
/bin/sleep 2
done
IFS=${saved_IFS}
save_days=${save_old_backups_for_days:-10}
need_clean=$(find ${mysql_backup_dir} -mtime +${save_days} -exec ls '{}' \;)
if [ ! -z ${need_clean} ]; then
find ${mysql_backup_dir} -mtime +${save_days} -exec rm -rf '{}' \;
echo "$need_clean have been cleaned! "
else
echo "nothing can be cleaned, skipped! "
fi
echo "=> do backup scheduler finished at $(date +%Y%m%d%H%M%S)"
echo -e "\n\n\n"
declare -x PATH=${old_PATH}
运维网声明
1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网 享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com