mysql数据库脚本编写

概述

其实主要是为了偷懒，所以就搞了一个自动编写MySQL数据库备份脚本，每次写备份脚本传参就可以了，仅供参考。

1、MySQL备份模板（上传到下载平台）#!/bin/bash################################## copyright by hwb# DATE:2020-12-03# 用途：MYSQL备份模板##################################定义db_host=localhostdb_port=3306db_name=mysql_prod db_user=rootdb_pwd=passwordbackup_path="/data/backup"# view,function,procedure,event,triggeroutput_type=‘view,function,procedure,event,trigger‘ today=`date +"%Y%m%d-%H%M%S"`data_file=$backup_path/$db_name$today.sqlobject_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/functionsexport PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/binsource /etc/profile#Require root to run this ｓｃｒｉｐｔ.[ $(id -u) -gt 0 ] && echo "请用root用户执行此脚本！" && exit 1[ -d $backup_path ] || mkdir -p $backup_path#[ ! -n "$5" ] && echo -e "\033[31m Usage: $0 IP 端口 实例名 用户名 ‘密码‘\033[0m" && exit 1function mysql_backup() {echo ""echo -e "\033[33m***********************************************mysql数据库备份****************************************************\033[0m" echo -e "\033[36m**************备份数据库数据到$data_file**************\033[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_fileif [ $? -eq 0 ];thenaction "[$today]>>>完成数据库${db_name}数据备份" /bin/trueecho "[$today]>>>完成数据库${db_name}数据备份" >> ${log_file}else action "[$today]>>>数据库${db_name}备份失败，请检查相关配置！" /bin/falseecho "[$today]>>>数据库${db_name}备份失败，请检查相关配置！" >> ${log_file}exit 1fiecho -e "\033[36m*******备份${db_name}函数、视图等定义到$object_file***********\033[0m"cat > $object_file $object_fileecho "">> $object_file # 视图if [[ $output_type == *"view"* ]]thenecho "-- ------------------------------------------------------------" >> $object_fileecho "-- views" >> $object_fileecho "-- ------------------------------------------------------------" >> $object_file#让 MySQL不输出列名 可以用-N 或者--skip-column-names参数$mysql_cmd--skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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\n/g‘ | sed ‘s/[ ]*character_set_client:/;\nkk_end/g‘ |\sed -n ‘/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}‘>> $object_filefi # 函数if [[ $output_type == *"function"* ]]thenecho "-- ------------------------------------------------------------" >> $object_fileecho "-- function" >> $object_fileecho "-- ------------------------------------------------------------" >> $object_file$mysql_cmd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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\ndelimiter $\n/g‘ | sed ‘s/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g‘ |\sed -n ‘/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}‘ >> $object_filefi # 存储过程if [[ $output_type == *"procedure"* ]]thenecho "-- ------------------------------------------------------------" >> $object_fileecho "-- procedure" >> $object_fileecho "-- ------------------------------------------------------------" >> $object_file$mysql_cmd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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\ndelimiter $\n/g‘ | sed ‘s/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g‘ |\sed -n ‘/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}‘ >> $object_filefi # 事件if [[ $output_type == *"event"* ]]thenecho "-- ------------------------------------------------------------" >> $object_fileecho "-- event" >> $object_fileecho "-- ------------------------------------------------------------" >> $object_file$mysql_cmd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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\ndelimiter $\n/g‘ | sed ‘s/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g‘ |\sed -n ‘/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}‘ >> $object_filefi # 触发器if [[ $output_type == *"trigger"* ]]thenecho "-- ------------------------------------------------------------" >> $object_fileecho "-- trigger" >> $object_fileecho "-- ------------------------------------------------------------" >> $object_file$mysql_cmd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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\ndelimiter $\n/g‘ | sed ‘s/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g‘ |\sed -n ‘/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}‘ >> $object_filefi # ^M, you need to type CTRL-V and then CTRL-Msed -i "s/\^M//g" $object_file#清理过期备份find ${backup_path}-mtime +10-type f -name ‘*.sql‘ -ｅｘｅｃ rm -f {} \;if [ $? -eq 0 ];thenaction "[$today]>>>完成数据库${db_name}过期备份清理" /bin/trueecho "[$today]>>>完成数据库${db_name}过期备份清理" >> ${log_file}else action "[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！" /bin/falseecho "[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！" >> ${log_file}exit 1fiecho -e "\033[33m**********************************************完成${db_name}数据库备份**********************************************\033[0m"cat > /tmp/mysql_backup.log> /var/spool/cron/rootif [ $? -eq 0 ];thenecho ""action "[$mysql_date]>>>完成数据库备份定时任务配置" /bin/trueelse echo ""action "[$mysql_date]>>>定时任务配置失败，请检查相关配置！" /bin/falsefiecho ""echo"|------------------------------------定时任务内容------------------------------------|" crontab -lecho ""echo"|---------------mysql备份脚本[$mysql_path/$script_name]内容如下---------------|" cat$mysql_path/$script_nameecho -e "\033[33m************************************完成mysql数据库备份脚本配置*****************************************\033[0m"echo ""}bk_mysqlbackup

3、基于蓝鲸平台测试自动编写备份脚本

测试成功..

4、测试备份的脚本是否有效

成功完成备份..

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下..