Oracle rman备份 与 mysqldump 备份
1|0分享几个数据库备份脚本
备份脚本六个注意点
首先要注意的一点就是 shell 环境是 bash 还是 sh ,需要在你的脚本开头中注明。
1 | #!/bin/bash |
其次就是数据库环境变量需要申明,例如直接加载 .bash_profile 或者单独申明 ORACLE_HOME、ORACLE_BASE 等。
1 | source /home/oracle/.bash_profile |
或者
1 2 3 4 5 | ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/product/11.2.0/dbhome_1 ORACLE_SID=(操作系统的环境变量) ORACLE_USER=oracle ORACLE_GROUP=oinstall |
接下来就是定义相关备份目录、日志目录等全局通用目录。
1 2 3 4 | DICPATH=/db_backup/oracle/rman LOGPATH=$DICPATH/log DATPATH=$DICPATH/data |
到这里呢才算开始正式的备份任务,使用数据库备份工具编写备份脚本。
1 2 3 | rman target / mysqldump ...... |
备份完成需要考虑存放日期,存放一周,两周,或者 scp、ftp 等传到远程异地,然后考虑清理本地的磁盘空间。
1 2 3 4 | --清理备份产生的日志find ${DIR}/log -name ${NAME}\* -mtime +90 | xargs rm -f --清理备份集 --使用操作系统命令直接删除备份集 find ${DIR}/data - name ${ NAME }\* -mtime +30 | xargs rm -f |
这一步就是部署备份了,一般借助操作系统的 crontab 定时任务来搞定。
crontab 命令的语法
crontab [-u username] [-l|-e|-r]
- 定时任务添加注释
- >/dev/null 2>&1 ==> &>/dev/null,别随意打印日志文件
- 定时任务里面的程序脚本尽量用全路径
- 避免不必要的程序以及命令输出
- 定时任务之前添加注释
下面看几个 crontab 的例子。
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 增备脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | vim /home/oracle/scripts/oracle_rman.sh #!/bin/bash source /home/oracle/.bash_profile DICPATH=/db_backup/oracle/rman LOGPATH=$DICPATH/log DATPATH=$DICPATH/data if [ ! -d $LOGPATH ]; then mkdir -p $LOGPATH fi if [ ! -d $DATPATH ]; then mkdir -p $DATPATH fi if [ "$1" = "full" ]; then echo 'jxrt' rman target / nocatalog msglog=$LOGPATH/rman_db_` date '+%Y%m%d%H%M%S' `.log <<EOF run{ 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" ]; then rman target / nocatalog msglog=$LOGPATH/rman_db_` date '+%Y%m%d%H%M%S' `.log <<EOF run{ 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; EOF fi #oracle 用户下的定时备份任务,可加参数 full 或者 diff #30 0 * * * sh /home/oracle/db/scripts/oracle_rman.sh full |
运行脚本
每周五凌晨 2:10 分运行 rman 脚本进行数据库全备,注意后面加参数 full;
其他时间每天 2:22 分运行 rman 脚本进行数据库增量备份,注意后面加参数 diff。
1 2 | 10 2 * * 5 /bin/bash /home/oracle/scripts/oracle_rman.sh full 22 2 * * 6,0,1,2,3,4 /bin/bash /home/oracle/scripts/oracle_rman.sh diff |
Oracle rman 全备脚本
下面的脚本是对数据库进行全备或者归档日志备份的,注意如果使用到了 catlog 的话,也可以使用我注释掉的 rman target catlog 连接串。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | 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=oracle ORACLE_GROUP=oinstall TARGET_CONNECT_STR=/ ORACLE_SID=JIEKEDB2 BAK_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 ] then mkdir -p ${DIR}/logs chown -R ${ORACLE_USER}:${ORACLE_GROUP} ${DIR}/logs fi # --------------------------------------------------------------------- # 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.out exit 1 ;; esac # --------------------------------------------------------------------- # Determine the user which is executing this script. # --------------------------------------------------------------------- CUSER=`whoami` if [ ${CUSER} != ${ORACLE_USER} ] then echo "Please user oracle_user to run the script!" >> ${DIR}/logs/`basename ${0}`_error.out exit 1 fi # --------------------------------------------------------------------- # 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.out exit 1 ;; esac NAME=`basename ${0}` RMAN_LOG_FILE=${DIR}/logs/`basename $RMAN_LOG_FILE` find ${DIR}/logs -name ${NAME}\* -mtime +30 | xargs rm -f if [ -h ${DIR}/logs/last ] then rm ${DIR}/logs/last fi ln -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_FILE chmod 666 $RMAN_LOG_FILE # --------------------------------------------------------------------- # Log the start of this script. # --------------------------------------------------------------------- echo Script $0 >> $RMAN_LOG_FILE echo ==== started on `date ' +%a,%Y%m%d-%H:%M:%S '` ==== >> $RMAN_LOG_FILE echo>> $RMAN_LOG_FILE # --------------------------------------------------------------------- # Print out the value of the variables set by this script. # --------------------------------------------------------------------- echo>> $RMAN_LOG_FILE echo "RMAN: $RMAN" >> $RMAN_LOG_FILE echo "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE echo "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE echo "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE # Convert requests for incremental into archive log only backups if [ "$1" = "arch" ]; then echo "Archive log only backup requested" >> $RMAN_LOG_FILE CMD_STR=" #rman target $TARGET_CONNECT_STR catalog $RCVCAT_CONNECT_STR log $RMAN_LOG_FILE append << EOF rman target / log $RMAN_LOG_FILE append << EOF RUN { 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" ]; then echo "Full database backup requested" >> $RMAN_LOG_FILE CMD_STR=" rman target $TARGET_CONNECT_STR catalog $RCVCAT_CONNECT_STR log $RMAN_LOG_FILE append << EOF RUN { 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 string sh -c "$CMD_STR" #sh -c "$CMD_STR" >> $RMAN_LOG_FILE RSTAT=$? # --------------------------------------------------------------------- # Log the completion of this script. # --------------------------------------------------------------------- if [ "$RSTAT" = "0" ] then LOGMSG="ended successfully" else LOGMSG="ended in error" fi echo>> $RMAN_LOG_FILE echo Script $0 >> $RMAN_LOG_FILE echo ==== $LOGMSG on `date ' +%a,%Y%m%d-%H:%M:%S'` ==== >> $RMAN_LOG_FILE echo>> $RMAN_LOG_FILE exit $RSTAT |
运行脚本
每周一凌晨 1:10 分运行 rman 脚本进行数据库全备,注意后面加参数 full;
其他时间每天 0:10 分运行 rman 脚本进行数据库归档日志备份,注意后面加参数 arch。
1 2 | 10 1 * * 1 /home/oracle/db/scripts/rman_nfs_bak.sh full 10 0 * * * /home/oracle/db/scripts/rman_nfs_bak.sh arch |
Oracle 归档删除脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | vi /home/oracle/clear_arch.sh #!/bin/bash ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/product/19.0.0/dbhome_1 ORACLE_SID=jiekexu PATH=$PATH:$ORACLE_HOME/bin/ source /home/oracle/.bash_profile rman target / log=/home/oracle/clear_arch.log<<EOF delete force noprompt archivelog all completed before 'sysdate-5' ; exit EOF |
运行脚本
1 | chmod +x /home/oracle/clear_arch.sh |
每隔六个小时运行一次,清理五天之前的归档日志。注意有备库或者 ogg 的需要格外注意,避免被过早的删除。
1 2 | crontab -e 0 0,6,12,18 * * * /home/oracle/clear_arch.sh |
MySQL xtrabackup 全备/增备脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | more /home/mysql/scripts/backup.sh #!/bin/bash echo "" START_TIME=` date ` echo "############## backup start at $START_TIME ##############" echo "" ###you need install xtrabackup!### # Set env source /home/mysql/.bash_profile which xtrabackup # Database Info DB_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" ) # Others BAK_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 backup if [ "$WEEK_DAY" == "6" ]; then xtrabackup --defaults-file=$CONF --socket=$SOCKET --backup --user=$DB_USER --password=$DB_PASS --target-dir=$BAK_DIR --compress elif [ "$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 --compress else 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 --compress fi # 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 #done echo "" 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 +30 find /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 进行备份,注意只有周六是全备,其他时间均是增备。
1 2 | 10 0 * * * /home/mysql/scripts/cleanup.sh >> /home/mysql/scripts/cleanup.log 2>&1 30 0 * * * /home/mysql/scripts/backup.sh >> /home/mysql/scripts/backup.log 2>&1 |
MySQL mysqldump 备份脚本 1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | #!/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_DIR fi # 备份数据库 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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 $BACKUP tar -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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | 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-transaction lock= "--single-transaction" mysql_path= "/usr/local/mysql" backup_path= "${mysql_path}/backup" date =$( date +%Y-%m-%d_%H-%M-%S) day =15 backup_log= "${mysql_path}/backup.log" #建立备份目录 if [ ! -e $backup_path ]; then mkdir -p $backup_path fi #删除以前备份 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>&1 done echo "备份结束,结果查看 $backup_log" du $backup_path/*$ date * -sh | awk '{print "文件:" $2 ",大小:" $1}' |
__EOF__
作 者:Aaron
出 处:https://www.cnblogs.com/Williamls/p/17283576.html
关于博主: 谦谦君子 卑以自牧
版权声明:署名 - 非商业性使用 - 禁止演绎,协议普通文本 | 协议法律文本。
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 无需6万激活码!GitHub神秘组织3小时极速复刻Manus,手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火,是硬核还是营销?
· 终于写完轮子一部分:tcp代理 了,记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛(1)
2019-04-03 在线激活win10、win8/8.1和office2019、2016、2013等的kms激活工具
2019-04-03 MySQL数据优化
2019-04-03 帆软报表(finereport)决策平台笔记