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

备份脚本六个注意点

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

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

#!/bin/bash

2、其次就是数据库环境变量需要申明，例如直接加载 .bash_profile 或者单独申明 ORACLE_HOME、ORACLE_BASE 等。

source /home/oracle/.bash_profile
`或者
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/product/11.2.0/dbhome_1
ORACLE_SID=JiekeXu1
ORACLE_USER=oracle
ORACLE_GROUP=oinstall`

3、接下来就是定义相关备份目录、日志目录等全局通用目录。

DICPATH=/db_backup/oracle/rman
`LOGPATH=$DICPATH/log
DATPATH=$DICPATH/data`

4、到这里呢才算开始正式的备份任务，使用数据库备份工具编写备份脚本。

rman target / 
`mysqldump ......`

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

--清理备份产生的日志
find ${DIR}/log -name ${NAME}\* -mtime +90 | xargs rm -f
--清理备份集
--使用操作系统命令直接删除备份集
find ${DIR}/data -name ${NAME}\* -mtime +30 | xargs rm -f

6. 这一步就是部署备份了，一般借助操作系统的 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/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。

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 连接串。

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}archdate +%Y%m%d-%H%M%S.out
;;
"full") RMAN_LOG_FILE=${ORACLE_SID}fulldate +%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。

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

Oracle 归档删除脚本

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

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

crontab -e
0 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 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 进行备份，注意只有周六是全备，其他时间均是增备。

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

#!/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

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

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}'