查看原文
其他

分享几个数据库备份脚本

JiekeXu JiekeXu DBA之路 2024-03-03

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路(ID: JiekeXu_IT)

如需转载请联系授权 | (个人微信 ID:JiekeXu_DBA)

大家好,我是 JiekeXu,很高兴又和大家见面了,今天给大家分享几个数据库备份脚本,欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号,标星或置顶,更多干货第一时间到达!

之前有朋友问有没有备份脚本,给分享一个,我就很是好奇,实在不会自己写,网上一搜一大堆的东西麽,拿过来改吧改吧就可以用了呀,实在没有啥可写的,但有人要求了,这里就水一篇吧。

备份脚本六个注意点

在写脚本前我这里按照个人习惯大概总结了以下六点,感兴趣的可以继续看看,不感兴趣的直接跳过就好。

  1. 首先要注意的一点就是 shell 环境是 bash 还是 sh ,需要在你的脚本开头中注明。

#!/bin/bash
  1. 其次就是数据库环境变量需要申明,例如直接加载  .bash_profile 或者单独申明 ORACLE_HOME、ORACLE_BASE 等。

source /home/oracle/.bash_profile
或者ORACLE_BASE=/u01/app/oracleORACLE_HOME=/u01/app/product/11.2.0/dbhome_1ORACLE_SID=JiekeXu1ORACLE_USER=oracleORACLE_GROUP=oinstall
  1. 接下来就是定义相关备份目录、日志目录等全局通用目录。

DICPATH=/db_backup/oracle/rman
LOGPATH=$DICPATH/logDATPATH=$DICPATH/data
  1. 到这里呢才算开始正式的备份任务,使用数据库备份工具编写备份脚本。

rman target /
mysqldump ......
  1. 备份完成需要考虑存放日期,存放一周,两周,或者 scp、ftp 等传到远程异地,然后考虑清理本地的磁盘空间。

--清理备份产生的日志find ${DIR}/log -name ${NAME}\* -mtime +90 | xargs rm -f--清理备份集--使用操作系统命令直接删除备份集find ${DIR}/data -name ${NAME}\* -mtime +30 | xargs rm -f
  1. 这一步就是部署备份了,一般借助操作系统的 crontab 定时任务来搞定。

crontab 命令的语法

crontab [-u username] [-l|-e|-r]选项与参数:-u :只有 root 才能进行这个任务,亦即帮其他使用者创建/移除 crontab 工作排程;-e :编辑 crontab 的工作内容-l :查阅 crontab 的工作内容-r :移除所有的 crontab 的工作内容,若仅要移除一项,请用 -e 去编辑
* * * * *- - - - -| | | | || | | | +----- 星期中的星期几 (0 - 6) (星期天为 0)| | | +---------- 月份 (1 - 12) | | +--------------- 一个月中的第几天 (1 - 31)| +-------------------- 小时 (0 - 23)+------------------------- 分钟 (0 - 59)

另外关于 crontab 还有几点要注意的:

  • 定时任务添加注释

  • >/dev/null 2>&1   ==>  &>/dev/null,别随意打印日志文件

  • 定时任务里面的程序脚本尽量用全路径

  • 避免不必要的程序以及命令输出

  • 定时任务之前添加注释



    下面看几个 crontab 的例子。

0 */2 * * * /sbin/service httpd restart 意思是每两个小时重启一次apache
50 7 * * * /sbin/service sshd start 意思是每天7:50开启ssh服务
50 22 * * * /sbin/service sshd stop 意思是每天22:50关闭ssh服务
0 0 1,15 * * fsck /home 每月1号和15号检查/home 磁盘
1 * * * * /home/jieke/backup 每小时的第一分执行 /home/jieke/backup这个文件
00 03 * * 1-5 find /home/oracle "*.xxx" -mtime +4 -exec rm {} \; 每周一至周五3点钟,在目录 /home/oracle 中,查找文件名为*.xxx的文件,并删除4天前的文件。
30 6 */10 * * ls  意思是每月的1、11、21、31日是的6:30执行一次ls命令
下面正式分享几个脚本。

Oracle rman 增备脚本

vim /home/oracle/scripts/oracle_rman.sh
#!/bin/bashsource /home/oracle/.bash_profile
DICPATH=/db_backup/oracle/rman
LOGPATH=$DICPATH/logDATPATH=$DICPATH/data
if [ ! -d $LOGPATH ]; then mkdir -p $LOGPATHfi
if [ ! -d $DATPATH ]; then mkdir -p $DATPATHfi
if [ "$1" = "full" ]; thenecho 'jxrt'rman target / nocatalog msglog=$LOGPATH/rman_db_`date '+%Y%m%d%H%M%S'`.log <<EOFrun{REPORT OBSOLETE;#crosscheck archivelog all;allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;allocate channel c4 type disk;allocate channel c5 type disk;allocate channel c6 type disk;allocate channel c7 type disk;allocate channel c8 type disk;sql 'alter system archive log current';backup as compressed backupset incremental level 0 cumulative database FILESPERSET 8 tag 'dbfull' format '$DATPATH/full_%d_%T_%s_%U.bak' ;sql 'alter system archive log current';backup as compressed backupset archivelog all tag 'arch' format '$DATPATH/arch_%d_%T_%s_%U.arc';DELETE force NOPROMPT OBSOLETE recovery window of 8 days device type disk;delete noprompt expired backup;delete noprompt expired archivelog all;backup current controlfile format '$DATPATH/ctl_%d_%T_%s_%U.bak'; release channel c1;release channel c2;release channel c3;release channel c4;release channel c5;release channel c6;release channel c7;release channel c8;}exit;EOF
elif [ "$1" = "diff" ]; thenrman target / nocatalog msglog=$LOGPATH/rman_db_`date '+%Y%m%d%H%M%S'`.log <<EOFrun{REPORT OBSOLETE;#crosscheck archivelog all;allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;allocate channel c4 type disk;allocate channel c5 type disk;allocate channel c6 type disk;allocate channel c7 type disk;allocate channel c8 type disk;sql 'alter system archive log current';backup as compressed backupset incremental level 1 cumulative database FILESPERSET 8 tag 'dbincrL1' format '$DATPATH/incr_L1_%d_%T_%s_%U.bak' ;sql 'alter system archive log current';backup as compressed backupset archivelog all tag 'arch' format '$DATPATH/arch_%d_%T_%s_%U.arc';DELETE force NOPROMPT OBSOLETE recovery window of 8 days device type disk;delete noprompt expired backup;delete noprompt expired archivelog all;backup current controlfile format '$DATPATH/ctl_%d_%T_%s_%U.bak';release channel c1;release channel c2;release channel c3;release channel c4;release channel c5;release channel c6;release channel c7;release channel c8;}exit;EOFfi
#oracle 用户下的定时备份任务,可加参数 full 或者 diff#30 0 * * * sh /home/oracle/db/scripts/oracle_rman.sh full

运行脚本
每周五凌晨 2:10 分运行 rman 脚本进行数据库全备,注意后面加参数 full;
其他时间每天 2:22 分运行 rman 脚本进行数据库增量备份,注意后面加参数 diff。

10 2 * * 5 /bin/bash /home/oracle/scripts/oracle_rman.sh full22 2 * * 6,0,1,2,3,4 /bin/bash /home/oracle/scripts/oracle_rman.sh diff
Oracle rman 全备脚本

下面的脚本是对数据库进行全备或者归档日志备份的,注意如果使用到了 catlog 的话,也可以使用我注释掉的 rman target catlog 连接串。

vim rman_nfs_bak.sh

#!/bin/bash
# ---------------------------------------------------------------------# hot_database_backup_proxy.sh# ---------------------------------------------------------------------# This script uses Recovery Manager to take a hot (inconsistent) database# backup. A hot backup is inconsistent because portions of the database are# being modified and written to the disk while the backup is progressing.# You must run your database in ARCHIVELOG mode to make hot backups. It is# assumed that this script will be executed by user root. In order for RMAN# to work properly we switch user (su -) to the oracle dba account before# execution. If this script runs under a user account that has Oracle dba# privilege, it will be executed using this user's account.# ---------------------------------------------------------------------
ORACLE_USER=oracleORACLE_GROUP=oinstallTARGET_CONNECT_STR=/ORACLE_SID=JIEKEDB2BAK_DIR=/backup/DB_Bak
source /home/${ORACLE_USER}/.bash_profile# ---------------------------------------------------------------------# Get the directory where the script locates and create log directory.# ---------------------------------------------------------------------
DIR=$(cd `dirname ${0}`; pwd)if [ ! -d ${DIR}/logs ]thenmkdir -p ${DIR}/logschown -R ${ORACLE_USER}:${ORACLE_GROUP} ${DIR}/logsfi
# ---------------------------------------------------------------------# Get the variables we need from the local instance.# ---------------------------------------------------------------------
SID=`ps -ef|grep pmon|grep ora_|awk -F'_' '{print $NF}'`case "$SID" in "$ORACLE_SID") RCVCAT_CONNECT_STR=jieke/jieke0jiekeR#@catalog ;; *) echo "Cannot get SID,database is not running" >> ${DIR}/logs/`basename ${0}`_error.outexit 1 ;;esac
# ---------------------------------------------------------------------# Determine the user which is executing this script.# ---------------------------------------------------------------------
CUSER=`whoami`if [ ${CUSER} != ${ORACLE_USER} ]thenecho "Please user oracle_user to run the script!" >> ${DIR}/logs/`basename ${0}`_error.outexit 1fi

# ---------------------------------------------------------------------# Put output in <this file name>.out. Change as desired.# Note: output directory requires write permission.# ---------------------------------------------------------------------
case "$1" in "arch") RMAN_LOG_FILE=${ORACLE_SID}_arch_`date +%Y%m%d-%H%M%S`.out ;; "full") RMAN_LOG_FILE=${ORACLE_SID}_full_`date +%Y%m%d-%H%M%S`.out ;; *) echo " The following word is needed as a script parameter: arch or full! ">> ${DIR}/logs/`basename ${0}`_error.outexit 1 ;;esac
NAME=`basename ${0}`RMAN_LOG_FILE=${DIR}/logs/`basename $RMAN_LOG_FILE`find ${DIR}/logs -name ${NAME}\* -mtime +30 | xargs rm -fif [ -h ${DIR}/logs/last ]thenrm ${DIR}/logs/lastfiln -s $RMAN_LOG_FILE ${DIR}/logs/last

# ---------------------------------------------------------------------# You may want to delete the output file so that backup information does# not accumulate. If not, delete the following lines.# ---------------------------------------------------------------------
# if [ -f "$RMAN_LOG_FILE" ]# then# rm -f "$RMAN_LOG_FILE"# fi
# -----------------------------------------------------------------# Initialize the log file.# -----------------------------------------------------------------
echo>> $RMAN_LOG_FILEchmod 666 $RMAN_LOG_FILE
# ---------------------------------------------------------------------# Log the start of this script.# ---------------------------------------------------------------------
echo Script $0 >> $RMAN_LOG_FILEecho ==== started on `date '+%a,%Y%m%d-%H:%M:%S'` ==== >> $RMAN_LOG_FILEecho>> $RMAN_LOG_FILE
# ---------------------------------------------------------------------# Print out the value of the variables set by this script.# ---------------------------------------------------------------------
echo>> $RMAN_LOG_FILEecho "RMAN: $RMAN" >> $RMAN_LOG_FILEecho "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILEecho "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILEecho "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
# Convert requests for incremental into archive log only backupsif [ "$1" = "arch" ]; thenecho "Archive log only backup requested" >> $RMAN_LOG_FILECMD_STR="#rman target $TARGET_CONNECT_STR catalog $RCVCAT_CONNECT_STR log $RMAN_LOG_FILE append << EOFrman target / log $RMAN_LOG_FILE append << EOFRUN {sql 'alter system archive log current';ALLOCATE CHANNEL ch00 device type DISK;BACKUP FILESPERSET 5 FORMAT '${BAK_DIR}/arch_%d_%s_%p_%t' ARCHIVELOG ALL;RELEASE CHANNEL ch00;}EOF"elif [ "$1" = "full" ]; thenecho "Full database backup requested" >> $RMAN_LOG_FILECMD_STR="rman target $TARGET_CONNECT_STR catalog $RCVCAT_CONNECT_STR log $RMAN_LOG_FILE append << EOFRUN {ALLOCATE CHANNEL ch00 device type DISK;ALLOCATE CHANNEL ch01 device type DISK;BACKUP TAG ${ORACLE_SID}_hot_backup FILESPERSET 5 FORMAT '${BAK_DIR}/database_%d_%s_%p_%t' DATABASE;sql 'alter system archive log current';RELEASE CHANNEL ch00;RELEASE CHANNEL ch01;}EOF"fi
# Initiate the command stringsh -c "$CMD_STR"#sh -c "$CMD_STR" >> $RMAN_LOG_FILERSTAT=$?# ---------------------------------------------------------------------# Log the completion of this script.# ---------------------------------------------------------------------
if [ "$RSTAT" = "0" ]then LOGMSG="ended successfully"else LOGMSG="ended in error"fi
echo>> $RMAN_LOG_FILEecho Script $0 >> $RMAN_LOG_FILEecho ==== $LOGMSG on `date '+%a,%Y%m%d-%H:%M:%S'` ==== >> $RMAN_LOG_FILEecho>> $RMAN_LOG_FILE
exit $RSTAT

运行脚本

每周一凌晨 1:10 分运行 rman 脚本进行数据库全备,注意后面加参数 full;
其他时间每天 0:10 分运行 rman 脚本进行数据库归档日志备份,注意后面加参数 arch。

10 1 * * 1 /home/oracle/db/scripts/rman_nfs_bak.sh full10 0 * * * /home/oracle/db/scripts/rman_nfs_bak.sh arch

Oracle 归档删除脚本

vi /home/oracle/clear_arch.sh
#!/bin/bashORACLE_BASE=/u01/app/oracleORACLE_HOME=/u01/app/product/19.0.0/dbhome_1ORACLE_SID=jiekexu
PATH=$PATH:$ORACLE_HOME/bin/source /home/oracle/.bash_profile
rman target / log=/home/oracle/clear_arch.log<<EOFdelete force noprompt archivelog all completed before 'sysdate-5';exitEOF

运行脚本
chmod +x /home/oracle/clear_arch.sh
每隔六个小时运行一次,清理五天之前的归档日志。注意有备库或者 ogg 的需要格外注意,避免被过早的删除。

crontab -e0 0,6,12,18 * * * /home/oracle/clear_arch.sh

MySQL xtrabackup 全备/增备脚本

more /home/mysql/scripts/backup.sh
#!/bin/bash
echo ""START_TIME=`date`echo "############## backup start at $START_TIME ##############"echo ""###you need install xtrabackup!#### Set envsource /home/mysql/.bash_profilewhich xtrabackup# Database InfoDB_USER="root"DB_PASS="R00t@123"#HOST="127.0.0.1"#PORT="24801"CONF="/data/mysqldb/conf/mysql.conf"SOCKET="/data/mysqldb/socket/mysql.sock"# Databases to backup# DB_NAME=("db1" "db2" "db3")# OthersBAK_BASE="/db_bak/mysql_bak/mysql"DATE=`date +%F`YESTERDAY=`date +%F -d "-1 days"`WEEK_DAY=`date +%w`BAK_DIR=$BAK_BASE/$DATE-$WEEK_DAY# Create Directory and backupif [ "$WEEK_DAY" == "6" ]; then xtrabackup --defaults-file=$CONF --socket=$SOCKET --backup --user=$DB_USER --password=$DB_PASS --target-dir=$BAK_DIR --compresselif [ "$WEEK_DAY" == "0" ]; then INCRE_BASE=$BAK_BASE/$YESTERDAY-6 xtrabackup --defaults-file=$CONF --socket=$SOCKET --backup --user=$DB_USER --password=$DB_PASS --target-dir=$BAK_DIR --incremental-basedir=$INCRE_BASE --compresselse INCRE_BASE=$BAK_BASE/$YESTERDAY-$[WEEK_DAY-1] xtrabackup --defaults-file=$CONF --socket=$SOCKET --backup --user=$DB_USER --password=$DB_PASS --target-dir=$BAK_DIR --incremental-basedir=$INCRE_BASE --compressfi# TODO#for var in ${DB_NAME[@]};#do# xtrabackup --defaults-file=$CONF --socket=$SOCKET --backup --databases $var --user=$DB_USER --password=$DB_PASS --target-dir=$BAK_DIR --compress#doneecho ""END_TIME=`date`echo "############## backup end at $END_TIME ##############"echo ""
vim /home/mysql/scripts/cleanup.sh
#!/bin/bash
echo ""START_TIME=`date`echo "############## clean up start at $START_TIME ##############"echo ""
find /db_bak/mysql_bak/mysql -maxdepth 1 -type d -mtime +30find /db_bak/mysql_bak/mysql -maxdepth 1 -type d -mtime +30 -exec rm -rf {} \;
echo ""END_TIME=`date`echo "############## clean up end at $END_TIME ##############"echo ""
运行脚本

每天凌晨 0:10 分清理 30 天之前的备份,每天 0:30 分使用 xtrabackup 进行备份,注意只有周六是全备,其他时间均是增备。

10 0 * * * /home/mysql/scripts/cleanup.sh >> /home/mysql/scripts/cleanup.log 2>&130 0 * * * /home/mysql/scripts/backup.sh >> /home/mysql/scripts/backup.log 2>&1

MySQL mysqldump 备份脚本 1

#!/bin/bash
# 定义变量DB_USER="root"DB_PASS="password"DB_NAME="database_name"BACKUP_DIR="/db_backup/mysql"REMOTE_SERVER="remote_server_address"REMOTE_DIR="/db_backup/mysql"
# 创建备份目录if [ ! -d $BACKUP_DIR ]; then mkdir -p $BACKUP_DIRfi
# 备份数据库mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).sql
# 压缩备份文件tar -czvf $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).tar.gz $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).sql
# 上传备份文件到远程服务器scp $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).tar.gz $REMOTE_SERVER:$REMOTE_DIR
# 删除过期备份文件find $BACKUP_DIR -type f -name "*.tar.gz" -mtime +15 -delete
MySQL mysqldump 备份脚本 2
vim mysql_backup.sh
#!/bin/bash#完成数据库的定时备份#备份的路径BACKUP=/db_backup/backup/db#当前的时间作为文件名DATETIME=$(date +%Y_%m_%d_%H%M%S)#可以输出变量调试#echo ${DATETIME}echo "==========开始备份==========="echo "备份的路径是 $BACKUP/$DATETIME.tar.gz"
#主机HOST=localhost#用户名DB_USER=root#密码DB_PWD=root#备份数据库名DATABASE=mysql#创建备份的路径#如果备份的路径文件夹存在就使用,否则创建[ ! -d "$BACKUP/$DATETIME" ] && mkdir -p "$BACKUP/$DATETIME" #执行mysql的备份数据库的指令mysqldump -u${DB_USER} -p${DB_PWD} --host=$HOST $DATABASE | gzip > $BACKUP/$DATETIME/$DATETIME.sql.gz#打包备份文件cd $BACKUPtar -zcvf $DATETIME.tar.gz $DATETIME#删除临时目录rm -rf $BACKUP/$DATETIME
#删除15天前的备份文件(-exec rm -rf {} \是固定写法,删除查询出来的数据)find $BACKUP -mtime +15 -name "*.tar.gz" -exec rm -rf {} \;echo "==========备份完成==========="

MySQL mysqldump 备份脚本 3

vim mysql_backup.sh
#!/bin/bash#全备方式,一般在从机上执行,适用于小中型mysql数据库#删除15天以前备份
source /etc/profile #加载系统环境变量source ~/.bash_profile #加载用户环境变量set -o nounset #引用未初始化变量时退出#set -o errexit #执行shell命令遇到错误时退出
user="root"password="root"host="localhost"port="13306"#需备份的数据库,数组db=("test")#备份时加锁方式,#MyISAM为锁表--lock-all-tables,#InnoDB为锁行--single-transactionlock="--single-transaction"mysql_path="/usr/local/mysql"backup_path="${mysql_path}/backup"date=$(date +%Y-%m-%d_%H-%M-%S)day=15backup_log="${mysql_path}/backup.log"
#建立备份目录if [ ! -e $backup_path ];then mkdir -p $backup_pathfi
#删除以前备份find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1
echo "开始备份数据库:${db[*]}"
#备份并压缩backup_sql(){ dbname=$1 backup_name="${dbname}_${date}.sql" #-R备份存储过程,函数,触发器 mysqldump -h $host -P $port -u $user -p$password $lock --default-character-set=utf8 --flush-logs -R $dbname > $backup_path/$backup_name if [[ $? == 0 ]];then cd $backup_path tar zcpvf $backup_name.tar.gz $backup_name size=$(du $backup_name.tar.gz -sh | awk '{print $1}') rm -rf $backup_name echo "$date 备份 $dbname($size) 成功 " else cd $backup_path rm -rf $backup_name echo "$date 备份 $dbname 失败 " fi}
#循环备份length=${#db[@]}for (( i = 0; i < $length; i++ )); do backup_sql ${db[$i]} >> $backup_log 2>&1done
echo "备份结束,结果查看 $backup_log"du $backup_path/*$date* -sh | awk '{print "文件:" $2 ",大小:" $1}'

全文完,希望可以帮到正在阅读的你,如果觉得此文对你有帮助,可以分享给你身边的朋友,同事,你关心谁就分享给谁,一起学习共同进步~~~

欢迎关注我的公众号【JiekeXu DBA之路】,第一时间一起学习新知识!

————————————————————————————
公众号:JiekeXu DBA之路
CSDN :https://blog.csdn.net/JiekeXu
墨天轮:https://www.modb.pro/u/4347
腾讯云:https://cloud.tencent.com/developer/user/5645107
————————————————————————————



Oracle 表碎片检查及整理方案

OGG|Oracle GoldenGate 基础

2022 年公众号历史文章合集整理

2021 年公众号历史文章合集整理

2020 年公众号历史文章合集整理

Oracle 19c RAC 遇到的几个问题

OGG|Oracle 数据迁移后比对一致性

利用 OGG 迁移 Oracle11g 到 19C

OGG|Oracle GoldenGate 微服务架构

Oracle 查询表空间使用率超慢问题一则

国产数据库|TiDB 5.4 单机快速安装初体验

Oracle ADG 备库停启维护流程及增量恢复

Linux 环境搭建 MySQL8.0.28 主从同步环境

继续滑动看下一个

分享几个数据库备份脚本

JiekeXu JiekeXu DBA之路
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存