shell 定时备份mysql 上传七牛云

shell 定时备份mysql 上传七牛云

备份目录 /data/backup/mysql 脚本目录 /usr/local/mysql/script 脚本名称 mysql_data_backup.sh 这里是个人脚本路径,大家看着修改 注意: 数据库用户建议创建一个专门备份的用户和密码账号

​

创建七牛云 如果不想上传七牛云就把脚本里面的去掉就好了 登录账户->对象存储->新建空间 获取ak sk下面要用

安装 qshell

进入root 目录cd 空格新建文件夹qshellmkdir qshell然后-对应版本 qshell 我们Linux 是 64位的查看命令如下：uname -a显示如下，看到我是64位：Linux localhost.localdomain 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

然后我们-对应版本的 :qshellwget qshell-linux-x64-v2.4.0.zip重新命名mv qshell-linux-x64-v2.4.0 qshell给权限chmod +x qshell然后查看下目录，方便设置环境变量pwd/root/qshell设置环境变量，有三种方式，我们采用对所有系统用户生效，永久生效vi /etc/profile在末尾添加如下,注意自己 qshell 的路径export PATH=$PATH:/root/qshell执行生效source /etc/profile然后执行qshell会有很多帮助命令列表，这样就代表添加环境变量成功了

接下来我们开始配置 qshell

首先我们需要添加公钥密钥和账号：命令如下，请注意更换成自己的密钥ak 和 sk 查看， name 代表自己的七牛账号qshell account ak sk name添加完成后使用qshell user ls 来查看用户列表

测试脚本

cd /usr/local/mysql/script./mysql_data_backup.sh

定时任务 crontab -e 凌晨一点备份

01 00 * * * /usr/local/mysql/script/mysql_data_backup.sh >/tmp/log_backup_mysql_$(date +"\%Y\%m\%d\%H\%M\%S").log

下面是脚本

#!/usr/bin/env bash# Function description:# Backup MySQL databases for each, backup schema and schema with data in one action.# Usage:# bash mysql_bash_backup.sh# Every friday night execute# 58 23 * * 5 /usr/local/mysql/script/mysql_data_backup.sh >/tmp/log_backup_mysql_$(date +"\%Y\%m\%d\%H\%M\%S").logUSER="`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"fiold_PATH=$PATHdeclare -x PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games"mysql_host=127.0.0.1mysql_port=3306mysql_username=root #数据库用户mysql_password=root #数据库密码mysql_basedir=/usr/local/mysqlsave_old_backups_for_days=13mysql_bin_mysql=${mysql_basedir}/bin/mysqlmysql_bin_dump=${mysql_basedir}/bin/mysqldumpmysql_backup_dir=/data/backup/mysqldate_format_type_dir=$(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 'backup_user'@"%";# 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;" )fisaved_IFS=$IFSIFS=' '$'\t'$'\n'for mysql_database in ${mysql_databases_list};do if echo ${mysql_database} | grep -Eqvi "^database$|sys|information_schema|performance_schema|^mysql$" ; then ${mysql_bin_dump} -u${mysql_username} -p${mysql_password} --host=${mysql_host} --port=${mysql_port}\ --routines --events --triggers --single-transaction --flush-logs \ --ignore-table=mysql.event --databases ${mysql_database} |& \ gzip > ${mysql_backup_dir}/${date_format_type_dir}/${mysql_database}.sql.gz [ $? -eq 0 ] && echo "${mysql_database} backup successfully! " || \ echo "${mysql_database} backup failed! " /bin/sleep 2 ${mysql_bin_dump} -u${mysql_username} -p${mysql_password} --host=${mysql_host} --port=${mysql_port}\ --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}_schema.sql.gz [ $? -eq 0 ] && echo "${mysql_database} schema backup successfully! " || \ echo "${mysql_database} schema backup failed! " /bin/sleep 2 fidoneIFS=${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! " fiecho "=> do backup scheduler finished at $(date +%Y%m%d%H%M%S)"echo -e "\n\n\n"#上传七牛startecho ${mysql_backup_dir}/${date_format_type_dir} "\n\n\n" ccc "\n\n\n"if [ -d ${mysql_backup_dir}/${date_format_type_dir} ]; then bakFile=${mysql_backup_dir}/${date_format_type_dir} zipFile="${mysql_backup_dir}/mysql_${date_format_type_dir}.zip" zip -rP "123456" ${zipFile} ${bakFile} #zip 密码压缩 /root/qshell/qshell rput test "mysql_qn_${date_format_type_dir}.zip" ${zipFile} && rm -f ${zipFile} #上传七牛云 test 七牛云账号fi#上传七牛overdeclare -x PATH=${old_PATH}

好了就到这,有问题欢迎沟通

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。