分享一个自动编写MySQL数据库备份脚本
其实主要是为了偷懒，所以就搞了一个自动编写MySQL数据库备份脚本，每次写备份脚本传参就可以了，仅供参考。其实主要是为了偷懒，所以就搞了一个自动编写MySQL数据库备份脚本，每次写备份脚本传参就可以了，仅供参考。
1. MySQL备份模板(上传到下载平台)
[*]#!/bin/bash
[*]#################################
[*]# copyright by hwb
[*]# DATE:2020-12-03
[*]# 用途：MYSQL备份模板
[*]#################################
[*]
[*]#定义
[*]db_host=localhost
[*]db_port=3306
[*]db_name=mysql_prod
[*]db_user=root
[*]db_pwd=password
[*]backup_path="/data/backup"
[*]
[*]# view,function,procedure,event,trigger
[*]output_type='view,function,procedure,event,trigger'
[*]today=`date +"%Y%m%d-%H%M%S"`
[*]data_file=$backup_path/$db_name$today.sql
[*]object_file="${backup_path}/obj_${db_name}$today.sql"
[*]log_file="/home/scripts/mysql_backup.log"
[*]mysql_cmd="mysql -u${db_user} -p${db_pwd} -h${db_host} -P${db_port} "
[*]mysqldump_cmd="mysqldump -u${db_user} -p${db_pwd} -h${db_host} -P${db_port} $db_name "
[*]
[*]
[*]#调用函数库
[*][ -f /etc/init.d/functions ] && source /etc/init.d/functions
[*]export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[*]source /etc/profile
[*]
[*]#Require root to run this script.
[*][ $(id -u) -gt 0 ] && echo "请用root用户执行此脚本！" && exit 1
[*]
[*]
[*][ -d $backup_path ] || mkdir -p $backup_path
[*]
[*]
[*]#[ ! -n "$5" ] && echo -e "[31m Usage: $0 IP 端口 实例名 用户名 '密码' [0m" && exit 1
[*]
[*]
[*]function mysql_backup()
[*]{
[*]echo ""
[*]echo -e "[33m***********************************************mysql数据库备份****************************************************[0m"
[*]
[*]echo -e "[36m**************备份数据库数据到$data_file**************[0m"
[*]#A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events
[*]$mysqldump_cmd --single_transaction -R -E --flush-logs --master-data=2 --set-gtid-purged=OFF > $data_file
[*]
[*]if [ $? -eq 0 ];then
[*]action "[$today]>>>完成数据库${db_name}数据备份" /bin/true
[*]echo "[$today]>>>完成数据库${db_name}数据备份" >> ${log_file}
[*]else
[*]action "[$today]>>>数据库${db_name}备份失败，请检查相关配置！" /bin/false
[*]echo "[$today]>>>数据库${db_name}备份失败，请检查相关配置！" >> ${log_file}
[*]exit 1
[*]fi
[*]
[*]
[*]echo -e "[36m*******备份${db_name}函数、视图等定义到$object_file***********[0m"
[*]cat > $object_file<<EOF
[*]ouput object‘s definition for database "$db_name"
[*]ouput time: $(date "+%Y-%m-%d %H:%M:%S")
[*]ouput object type: $output_type
[*]EOF
[*]echo "">> $object_file
[*]echo "">> $object_file
[*]
[*]# 视图
[*]if [[ $output_type == *"view"* ]]
[*]then
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]echo "-- views" >> $object_file
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]#让 MySQL不输出列名 可以用-N 或者--skip-column-names参数
[*]$mysql_cmd --skip-column-names
[*]-e "select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views where table_schema='$db_name'" |
[*]sed 's/;/\G/g' | $mysql_cmd $db_name |
[*]sed 's/Create View: /kk_begin /g' | sed 's/[ ]*character_set_client:/; kk_end/g' |
[*]sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin|kk_end.*//g;p}' >> $object_file
[*]fi
[*]
[*]# 函数
[*]if [[ $output_type == *"function"* ]]
[*]then
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]echo "-- function" >> $object_file
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]$mysql_cmd --skip-column-names
[*]-e "select concat('SHOW CREATE FUNCTION ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='FUNCTION'" |
[*]sed 's/;/\G/g' | $mysql_cmd $db_name |
[*]sed 's/Create Function: /kk_begin delimiter $$ /g' | sed 's/[ ]*character_set_client:/$$ delimiter ; kk_end/g' |
[*]sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin|kk_end.*//g;p}' >> $object_file
[*]fi
[*]
[*]# 存储过程
[*]if [[ $output_type == *"procedure"* ]]
[*]then
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]echo "-- procedure" >> $object_file
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]$mysql_cmd --skip-column-names
[*]-e "select concat('SHOW CREATE PROCEDURE ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='PROCEDURE'" |
[*]sed 's/;/\G/g' | $mysql_cmd $db_name |
[*]sed 's/Create Procedure: /kk_begin delimiter $$ /g' | sed 's/[ ]*character_set_client:/$$ delimiter ; kk_end/g' |
[*]sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin|kk_end.*//g;p}' >> $object_file
[*]fi
[*]
[*]# 事件
[*]if [[ $output_type == *"event"* ]]
[*]then
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]echo "-- event" >> $object_file
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]$mysql_cmd --skip-column-names
[*]-e "select concat('SHOW CREATE EVENT ',EVENT_SCHEMA,'.',EVENT_NAME,';') from information_schema.events where EVENT_SCHEMA='$db_name'" |
[*]sed 's/;/\G/g' | $mysql_cmd |
[*]sed 's/Create Event: /kk_begin delimiter $$ /g' | sed 's/[ ]*character_set_client:/$$ delimiter ; kk_end/g' |
[*]sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin|kk_end.*//g;p}' >> $object_file
[*]fi
[*]
[*]# 触发器
[*]if [[ $output_type == *"trigger"* ]]
[*]then
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]echo "-- trigger" >> $object_file
[*]echo "-- ------------------------------------------------------------" >> $object_file
[*]$mysql_cmd --skip-column-names
[*]-e "select concat('SHOW CREATE TRIGGER ',TRIGGER_SCHEMA,'.',TRIGGER_NAME,';') from information_schema.triggers where TRIGGER_SCHEMA='$db_name';" |
[*]sed 's/;/\G/g' | $mysql_cmd $db_name|
[*]sed 's/SQL Original Statement: /kk_begin delimiter $$ /g' | sed 's/[ ]*character_set_client:/$$ delimiter ; kk_end/g' |
[*]sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin|kk_end.*//g;p}' >> $object_file
[*]fi
[*]
[*]# ^M, you need to type CTRL-V and then CTRL-M
[*]sed -i "s/^M//g" $object_file
[*]
[*]#清理过期备份
[*]find ${backup_path} -mtime +10 -type f -name '*.sql' -exec rm -f {} ;
[*]
[*]if [ $? -eq 0 ];then
[*]action "[$today]>>>完成数据库${db_name}过期备份清理" /bin/true
[*]echo "[$today]>>>完成数据库${db_name}过期备份清理" >> ${log_file}
[*]else
[*]action "[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！" /bin/false
[*]echo "[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！" >> ${log_file}
[*]exit 1
[*]fi
[*]
[*]echo -e "[33m**********************************************完成${db_name}数据库备份**********************************************[0m"
[*]cat > /tmp/mysql_backup.log << EOF
[*]mysql地址：${db_host}
[*]mysql端口：${db_port}
[*]mysql实例名：${db_name}
[*]数据备份文件：${data_file}
[*]定义备份文件：${object_file}
[*]EOF
[*]cat /tmp/mysql_backup.log
[*]echo -e "e[1;31m 以上信息保存在/tmp/mysql_backup.log文件下 e[0m"
[*]echo -e "[33m*******************************************************************************************************************[0m"
[*]echo ""
[*]}
[*]
[*]
[*]mysql_backup
2. 自动编写MySQL数据库备份脚本
[*]#!/bin/bash
[*]###################################################################
[*]# copyright by hwb
[*]# DATE: 2020-12-04
[*]# 用途：自动编写MySQL备份脚本
[*]###################################################################
[*]
[*]#脚本外变量
[*]mysql_path="/home/scripts"
[*]script_name="mysql_backup.sh"
[*]mysql_date=`date +"%Y-%m-%d-%H:%M:%S"`
[*]#脚本内变量
[*]db_host="localhost"
[*]db_port="53306"
[*]db_name="dbname"
[*]db_user="root"
[*]db_pwd="xxxx"
[*]backup_path="/data/backup"
[*]bk_backup_path=`echo $backup_path | sed 's:/:\/:g'`
[*]bk_db_pwd=`echo $db_pwd | sed 's:/:\/:g'`
[*]
[*]#调用函数库
[*][ -f /etc/init.d/functions ] && source /etc/init.d/functions
[*]export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[*]source /etc/profile
[*]
[*]
[*]#root用户执行脚本
[*][ $(id -u) -gt 0 ] && echo "请用root用户执行此脚本！" && exit 1
[*]
[*]
[*]#判断目录是否存在
[*][ -d $mysql_path ] || mkdir -p $mysql_path
[*][ -d $backup_path ] || echo "mysql数据库备份目录[$backup_path]不存在，请确认参数是否正确！"
[*][ -d $backup_path ] || exit 1
[*]
[*]
[*]function bk_mysqlbackup(){
[*]echo ""
[*]echo -e "**************************[0m"
[*]echo ""
[*]echo -e "[36m Usage: $0 [0m"
[*]echo ""
[*]
[*][ -f $mysql_path/$script_name ] && echo "${mysql_path}已存在脚本[${script_name}],请检查相关配置！" && exit 1
[*]
[*]wget https://app.fslgz.com/portal/api/public/fs/association/file/downLoad?uploadId=784001405093478400 -O /opt/mysql_backup_template.sh
[*]mv /opt/mysql_backup_template.sh $mysql_path/$script_name
[*]
[*]#处理windows传linux的脚本格式问题(注意空格位置不能多不能少)
[*]vi +':w ++ff=unix' +':q' ${mysql_path}/${script_name}
[*]
[*]sed -i "s/localhost/${db_host}/g" $mysql_path/$script_name
[*]sed -i "s/3306/${db_port}/g" $mysql_path/$script_name
[*]sed -i "s/mysql_prod/${db_name}/g" $mysql_path/$script_name
[*]sed -i "s/root/${db_user}/g" $mysql_path/$script_name
[*]sed -i "s/password/${bk_db_pwd}/g" $mysql_path/$script_name
[*]sed -i "s#/data/backup#${bk_backup_path}#g" $mysql_path/$script_name
[*]
[*]action "完成mysql备份脚本[$mysql_path/$script_name]编写！" /bin/true
[*]chmod u+x $mysql_path/$script_name
[*]
[*]#配置定时任务
[*]echo "30 11 * * * /bin/bash ${mysql_path}/${script_name} " >> /var/spool/cron/root
[*]
[*]if [ $? -eq 0 ];then
[*]echo ""
[*]action "[$mysql_date]>>>完成数据库备份定时任务配置" /bin/true
[*]else
[*]echo ""
[*]action "[$mysql_date]>>>定时任务配置失败，请检查相关配置！" /bin/false
[*]fi
[*]
[*]echo ""
[*]echo "|------------------------------------定时任务内容------------------------------------|"
[*]crontab -l
[*]echo ""
[*]echo "|---------------mysql备份脚本[$mysql_path/$script_name]内容如下---------------|"
[*]cat $mysql_path/$script_name
[*]echo -e "[33m************************************完成mysql数据库备份脚本配置*****************************************[0m"
[*]echo ""
[*]}
[*]
[*]
[*]bk_mysqlbackup
3. 基于蓝鲸平台测试自动编写备份脚本
测试成功..
4. 测试备份的脚本是否有效
成功完成备份..
原文地址：https://www.toutiao.com/i6902767275703845379/
http://www.zzvips.com/article/225402.html
页:
[1]