Ready-to-Use Shell Scripts for Backing Up Oracle, MySQL, PostgreSQL & openGauss

Introduction

To prevent data loss caused by failures, viruses, or user errors, a reliable backup strategy is essential. The following sections provide ready‑to‑use shell scripts and detailed procedures for backing up and restoring the most common relational databases.

1. Oracle RMAN Backup

1.1 Create Directories

[oracle@OEL7 ~]$ mkdir -p /u01/dbbak/script
[oracle@OEL7 ~]$ cd /u01/dbbak
[oracle@OEL7 ~]$ chown -R oracle:oinstall script
[oracle@OEL7 ~]$ mkdir -p /u01/dbbak/db
[oracle@OEL7 ~]$ mkdir -p /u01/dbbak/arch
[oracle@OEL7 ~]$ cd /u01/dbbak
[oracle@OEL7 ~]$ chown -R oracle:oinstall db
[oracle@OEL7 ~]$ chown -R oracle:oinstall arch

1.2 Edit Scripts

# rman_full.sh
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=prod
dt=`date '+%Y%m%d_%H%M%S'`
/u01/app/oracle/product/19.3.0/dbhome_1/bin/rman target / \
  cmdfile=/u01/dbbak/script/backup.sh \
  log=/u01/dbbak/log/log_$dt.log append

# backup.sh
run{
  allocate channel c1 type disk;
  allocate channel c2 type disk;
  allocate channel c3 type disk;
  allocate channel c4 type disk;
  backup as compressed backupset database \
    format '/u01/dbbak/db/DB%U.bkp' plus archivelog \
    format '/u01/dbbak/arch/ARCH%U.bkp' delete all input;
  release channel c1;
  release channel c2;
  release channel c3;
  release channel c4;
  report obsolete;
  crosscheck copy;
  crosscheck archivelog all;
  delete noprompt obsolete;
  crosscheck backup;
  delete noprompt expired backup;
}
quit;

1.3 Authorize Scripts

[oracle@OEL7 ~]$ chmod 775 /u01/dbbak/script/backup.sh
[oracle@OEL7 ~]$ chmod 775 /u01/dbbak/script/rman_full.sh

1.4 Execute Script

/u01/dbbak/script/rman_full.sh

1.5 Schedule with Cron

0 2 * * * /u01/dbbak/script/rman_full.sh

1.6 Retention Policy

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

2. Oracle Logical Backup

2.1 Create Directories and Oracle Directory Object

[oracle@OEL7 ~]$ mkdir -p /home/oracle/backup/dump
[oracle@OEL7 ~]$ mkdir -p /home/oracle/backup/tars
SQL> CREATE OR REPLACE DIRECTORY scott_backup AS '/home/oracle/backup/dump';
SQL> GRANT read,write ON DIRECTORY scott_backup TO public;

2.2 Backup Script (exp_backup.sh)

#!/bin/bash
# Oracle environment variables
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

# Timestamp
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 backup..."
echo "Backup file path $DATA_DIR/scott_expdp_$BAKUPTIME.dmp"
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 "Backup job is done!"
# Delete dumps older than 7 days
find $DATA_DIR -type f -mtime +7 -exec rm -rf {} \;

2.3 Schedule with Cron

0 3 * * * /home/oracle/backup/exp_backup.sh

3. MySQL Logical Full & Incremental Backup

3.1 Enable Binary Logging

# Edit /etc/my.cnf
log_bin=ON
log_bin_basename=/home/mysql/mysql-bin
log_bin_index=/home/mysql/mysql-bin.index

3.2 Full Backup Script (Mysql-FullyBak.sh)

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 Incremental Backup Script (Mysql-DailyBak.sh)

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}'`
NextNum=0
for file in `cat $BinFile`; do
  base=`basename $file`
  NextNum=`expr $NextNum + 1`
  if [[ $NextNum -eq $Counter ]]; then
    echo "$base skip!" >> $LogFile
  else
    dest=$BakDir/$base
    if test -e $dest; then
      echo "$base exist!" >> $LogFile
    else
      cp $BinDir/$base $BakDir
      echo "$base copying" >> $LogFile
    fi
  fi
done

3.4 Schedule with Cron

# Full backup every Sunday at 03:00
0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh > /dev/null 2>&1
# Incremental backup Monday‑Saturday at 03:00
0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh > /dev/null 2>&1

3.5 Restore Steps

4. MySQL Xtrabackup (PXB) Full & Incremental Backup

Xtrabackup is an open‑source MySQL backup tool maintained by Percona.

4.1 Backup Script (backup.sh)

#!/bin/bash
echo ""
START_TIME=`date`
echo "############## backup start at $START_TIME ##############"

# Ensure xtrabackup is installed
source /home/mysql/.bash_profile
which xtrabackup

# Database connection 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

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 Cleanup Script (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 Schedule with Cron

# Cleanup at 04:10 daily (remove backups older than 30 days)
10 4 * * * /home/mysql/scripts/cleanup.sh >> /home/mysql/scripts/cleanup.log 2>&1
# Backup at 04:30 daily (full backup on Saturday, incremental otherwise)
30 4 * * * /home/mysql/scripts/backup.sh >> /home/mysql/scripts/backup.log 2>&1

5. PostgreSQL Logical Backup

5.1 Backup Script

#!/bin/bash
# PostgreSQL connection info
db_host="localhost"
db_port="5432"
db_name="database_name"
db_user="database_user"
db_password="database_password"

# Backup directory and retention
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 Schedule with Cron

0 2 * * * /data/script_name.sh

5.3 Restore Steps

-- Restore
DROP DATABASE jmedb;
CREATE DATABASE jmedb;
psql --file=jmedb.sql   # verify creation statements
psql --dbname=db2 --file=jmedb.sql

6. PostgreSQL Physical Backup with pg_rman

6.1 Backup Script

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

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 -gt 0 ]; then
  message="Postgres 数据库服务器${hostname}在${DATE}备份失败"
  echo $message
fi
pg_rman purge >> $BACK_LOG
echo "BACKUP  END" >> $BACK_LOG

6.2 Restore Procedure

# Stop PostgreSQL
pg_ctl stop
# Remove old data directory
rm -rf /postgresql/pgdata/
# Restore from backup
pg_rman restore -B /rmanbk
# Start PostgreSQL
pg_ctl start
# After start, the recovery.signal file is removed automatically

7. openGauss Backup

7.1 Backup Script

#!/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"`
# Load database list
gsql -p ${GAUSSPORT} postgres -c "select datname from pg_database where datname not in ('template1','template0','postgres')" -t | grep -v '^$' > db.txt
SAVE_BASE_DIR="/gaussdb/dump_dir"
DAT_FILE_DIR="${SAVE_BASE_DIR}/${CUR_DATE}"
if [ ! -d ${DAT_FILE_DIR} ]; then
  mkdir -p ${DAT_FILE_DIR}
fi

echo "`date "+%Y-%m-%d-%H%M"` begin backup db"
for dbname in $(cat db.txt); 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 Schedule with Cron

# Daily backup at 02:30
30 02 * * * sh /home/omm/backup.sh

7.3 Cleanup Strategy

# Remove tar.gz files older than 30 days
30 03 * * * find /gaussdb/dump_dir -not -path '*/\.*' -mtime +30 -type f -name *.tar.gz -exec rm -rf {} \;

These scripts together form a ready‑to‑use toolkit for automated backup, retention, and restoration of Oracle, MySQL, PostgreSQL, and openGauss databases.