IT邦德

IT邦德

关注
发信
关注(28)粉丝(399)

数据库备份脚本嘎嘎香，被秀到了！

前言

在日常运行过程中，为防止出现数据库故障、病毒或者用户操作不当而导致数据丢失，必须要有良好的预备方案，特分享主流数据库备份的Shell脚本给大家。

1.Oracle RMAN备份

1.1 创建目录

1.2 编辑脚本

1.3 脚本授权

1.4 执行脚本

1.5 定时任务

1.6 备份删除

2.Oracle逻辑备份

2.1 生成目录

2.2 备份脚本

[oracle@OEL7 ~]$ vi /home/oracle/backup/exp_backup.sh
#!/bin/bash
#Oracle 环境变量
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
ORACLE_SID=prod
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/11g
PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export ORACLE_SID ORACLE_BASE ORACLE_HOME NLS_LANG PATH

#获取时间戳
#dump生成时间
export BAKUPTIME=`date +%Y%m%d%H%M%S`
#压缩文件存放目录
export DATA_DIR=/home/oracle/backup/dump
export TAR_DIR=/home/oracle/backup/tars
cd $DATA_DIR     
echo "Starting bakup..."
echo "Bakup file path $DATA_DIR/scott_expdp_$BAKUPTIME.dmp"
#parallel=4 为并行度，对于备份大数据库有助于减少备份时间，但会增加CPU负载。
expdp scott/tiger directory=scott_backup dumpfile=scott_expdp_$BAKUPTIME.dump logfile=scott_expdp_$BAKUPTIME.log parallel=4
echo "Starting tar..."
echo "Tar file path $TAR_DIR/SCOTT_$BAKUPTIME.tar.gz"
tar -zcvf $TAR_DIR/scott_expdp_$BAKUPTIME.tar.gz scott_expdp*
echo "Bakup job is done!"
#历史dump文件保留7天
find  $DATA_DIR -type f -mtime +7 -exec rm -rf {} \;

2.3 定时任务

3.MySQL逻辑全｜增备份

3.1 开启binlog日志

3.2 全备脚本

# mkdir -p /home/mysql/daily
# mkdir -p /home/mysql/backup
# vi /home/mysql/Mysql-FullyBak.sh

#mysqldump to Fully backup mysql data per week!
source /etc/profile
BakDir=/home/mysql/backup
LogFile=/home/mysql/backup/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
/usr/bin/mysqldump -uroot -proot --quick --events --databases wmp --flush-logs --delete-master-logs --single-transaction >$DumpFile
/bin/tar -zvcf $GZDumpFile $DumpFile
/bin/rm $DumpFile
oldDate=`date -d '7 days ago' +%Y%m%d`
oldBakFile=${oldDate}".sql.tgz"
/bin/rm $oldBakFile
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFile
cd $BakDir/daily
/bin/rm -f *

3.3 增备脚本

# vi /home/mysql/Mysql-DailyBak.sh
#use cp to bakup mysql data everyday!
source /etc/profile
BakDir=/home/mysql/backup/daily
BinDir=/home/mysql/mysql-bin
LogFile=/home/mysql/backup/bak.log
BinFile=/home/mysql/mysql-bin/mysql-bin.index
/usr/bin/mysqladmin -uroot -proot flush-logs
Counter=`wc -l $BinFile |awk '{print $1}'`  
#产生新的mysql-bin.00000*文件
NextNum=0
#比对$Counter和￥NextNum这两个值来确定文件是不是最新的
for file in `cat $BinFile`
do
     base=`basename $file`
     #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
     NextNum=`expr $NextNum + 1`
     if [[ $NextNum -eq $Counter ]]
     then
        echo $base skip! >> $LogFile
      else
        dest=$BakDir/$base
         if(test -e $dest)
        #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去
         then
               echo $base exist! >> $LogFile
         else
                cp $BinDir/$base $BakDir
                echo $base copying >> $LogFile
          fi
fi
done

3.4 定时任务

3.5 恢复步骤

数据库备份脚本嘎嘎香，被秀到了！-LMLPHP

4.MySQL PXB全/增备份

4.1 备份脚本

vi /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="jeames@123"
CONF="/data/mysqldb/conf/mysql.conf"
SOCKET="/data/mysqldb/socket/mysql.sock"
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
echo ""
END_TIME=`date`
echo "############## backup end at $END_TIME ##############"
echo ""

4.2 备份删除

vi  /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 ""

4.3 定时任务

5.PostgreSQL逻辑备份

5.1 备份脚本

vi /data/script_name.sh 

#!/bin/bash
 
# PostgreSQL数据库相关信息
db_host="localhost"
db_port="5432"
db_name="database_name"
db_user="database_user"
db_password="database_password"
 
# 备份存储目录
backup_dir="/data/backup/folder"
 
# 保留备份的天数
retention_days=7
 
# 创建备份目录
mkdir -p $backup_dir
 
# 备份文件名
backup_file="$backup_dir/backup_$(date +'%Y%m%d%H%M%S').sql"
 
# 执行备份
PGPASSWORD=$db_password 
pg_dump -h $db_host -p $db_port -U $db_user -F c -b -v -f "$backup_file" $db_name
 
if [ $? -eq 0 ]; then
    echo "数据库备份成功: $backup_file"
 
    # 删除旧的备份文件
    find $backup_dir -name "backup_*.sql" -type f -mtime +$retention_days -exec rm -f {} \;
else
    echo "数据库备份失败."
fi

5.2 定时任务

5.3 备份恢复

--恢复
drop database jmedb;
create database jmedb;;
psql --file=jmedb.sql   --先查看可否有创建数据库的语句 
psql --dbname=db2 --file=jmedb.sql   --先查看可否有创建数据库的语句

6.PostgreSQL物理备份

6.1 备份脚本

#!/bin/bash

source /home/postgres/.bash_profile

DATE=`date +%Y%m%d`;
PG_HOME=/home/postgres
BACK_LOG=/home/postgres/log/pg_rman_${DATE}.log

#START BACKUP
echo "START BACKUP" > $BACK_LOG
#执行备份命令
pg_rman backup --backup-mode=full -B /rmanbk >> $BACK_LOG
#备份集校验
pg_rman validate >> $BACK_LOG
#检查备份是否成功
error_num=`pg_rman show | awk 'BEGIN{n=0}{if(NR > 3 && $8 != "OK")n++}END{print n}'`
if [ $error_num > 0 ];then
    message="Postgres 数据库服务器${hostname}在${DATE}备份失败" 
    echo $message
fi
#清理无效备份集
pg_rman purge >> $BACK_LOG
echo "BACKUP  END" >> $BACK_LOG

6.2 备份恢复

数据库备份脚本嘎嘎香，被秀到了！-LMLPHP

7.openGauss备份

7.1 备份脚本

vi /home/omm/backup.sh

# database dump shell
# you should change the GAUSSHOME GAUSSPORT GAUSSDATA DUMP_USER DUMP_PASSWORD
#!/bin/bash
source /etc/profile
source /home/omm/.bash_profile
export GAUSSHOME=/opt/gaussdb/app
export GAUSSPORT=26000
export GAUSSDATA=/gaussdb/data/dn1
export PATH=$PGHOME/bin:$PATH
DUMP_USER=ysla
DUMP_PASSWORD='jeames007@HW'
CUR_DATE=`date "+%Y-%m-%d-%H%M"`
dbnamelist=`cat db.txt`
 
#Loading DBLIST
gsql -p ${GAUSSPORT} postgres -c "select datname from pg_database where datname not in ('template1','template0','postgres')" -t | grep -v '^$' >db.txt
 
#save directory
SAVE_BASE_DIR="/gaussdb/dump_dir"
DAT_FILE_DIR="${SAVE_BASE_DIR}/${CUR_DATE}"
if [ -d ${DAT_FILE_DIR} ]
 then :
 else
 mkdir -p ${DAT_FILE_DIR}
fi
# The real backup step!
echo "`date "+%Y-%m-%d-%H%M"` begin backup db "
for dbname in ${dbnamelist}
do
gs_dump -E UTF8 ${dbname} -U ${DUMP_USER} -W ${DUMP_PASSWORD} -p ${GAUSSPORT} -F p -f ${DAT_FILE_DIR}/${dbname}_${CUR_DATE}.sql
gs_dumpall -l ${dbname} -U ${DUMP_USER} -W ${DUMP_PASSWORD} -p ${GAUSSPORT} -g -f ${DAT_FILE_DIR}/global_data_${dbname}_${CUR_DATE}.sql
done
tar -cjvf ${DAT_FILE_DIR}.tar.gz /${DAT_FILE_DIR} --remove-files
echo "`date "+%Y-%m-%d-%H%M"` end backup db "

7.2 定时任务

在命令行输入:
#crontab -e
30 02 * * * sh /home/omm/backup.sh

7.3 备份清理

01-14 17:41
Powered by LMLPHP ©2024 IT邦德 0.006606