Master Multi‑DB Backup: Oracle, MySQL, PostgreSQL & openGauss Shell Scripts

1. Oracle RMAN Backup

Creates a structured directory hierarchy, prepares backup scripts, grants execution rights, runs the RMAN script, schedules a daily cron job at 02:00, and configures a 7‑day retention policy.

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 backup script

[oracle@OEL7 ~]$ vi /u01/dbbak/script/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
[oracle@OEL7 ~]$ vi /u01/dbbak/script/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;
EOF

1.3 Script permissions

[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 cron job

crontab -e   # edit
[oracle@OEL7 ~]$ crontab -l
0 2 * * * /u01/dbbak/script/rman_full.sh

1.6 Backup deletion policy

[oracle@OEL7 ~]$ rman target /
RMAN> show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

2. Oracle Logical Backup

Creates export directories, defines a DIRECTORY object, grants privileges, and runs an expdp‑based script with hourly cron scheduling.

2.1 Create directories and 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

[oracle@OEL7 ~]$ vi /home/oracle/backup/exp_backup.sh
#!/bin/bash
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
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..."
expdp scott/tiger directory=scott_backup dumpfile=scott_expdp_${BAKUPTIME}.dump logfile=scott_expdp_${BAKUPTIME}.log parallel=4
echo "Starting tar..."
tar -zcvf $TAR_DIR/SCOTT_${BAKUPTIME}.tar.gz scott_expdp*
echo "Backup job is done!"
find $DATA_DIR -type f -mtime +7 -exec rm -rf {} \;

2.3 Schedule cron job

crontab -e   # edit
[oracle@OEL7 ~]$ crontab -l
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

# vi /home/mysql/Mysql-FullyBak.sh
#!/bin/bash
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

# vi /home/mysql/Mysql-DailyBak.sh
#!/bin/bash
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=$((NextNum+1))
  if [ $NextNum -eq $Counter ]; then
    echo "$base skip!" >> $LogFile
  else
    dest=$BakDir/$base
    if [ -e $dest ]; then
      echo "$base exist!" >> $LogFile
    else
      cp $BinDir/$base $BakDir
      echo "$base copying" >> $LogFile
    fi
  fi
done

3.4 Schedule cron jobs

# 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

Use the generated .sql.tgz files, extract them, and import with mysql or mysqldump as needed.

4. MySQL Percona XtraBackup (PXB) Full & Incremental

4.1 Backup script

# vi /home/mysql/scripts/backup.sh
#!/bin/bash
START_TIME=`date`
echo "############## backup start at $START_TIME ##############"
# set environment
source /home/mysql/.bash_profile
which xtrabackup
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`
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/$(date -d "-1 days" +%F)-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/$(date -d "-1 days" +%F)-$((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
END_TIME=`date`
echo "############## backup end at $END_TIME ##############"

4.2 Cleanup script

# vi /home/mysql/scripts/cleanup.sh
#!/bin/bash
START_TIME=`date`
echo "############## clean up start at $START_TIME ##############"
find /db_bak/mysql_bak/mysql -maxdepth 1 -type d -mtime +30 -exec rm -rf {} \;
END_TIME=`date`
echo "############## clean up end at $END_TIME ##############"

4.3 Schedule cron jobs

# Clean up at 04:10 daily
10 4 * * * /home/mysql/scripts/cleanup.sh >> /home/mysql/scripts/cleanup.log 2>&1
# Backup at 04:30 daily (full 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

# vi /data/script_name.sh
#!/bin/bash
# PostgreSQL connection info
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 Schedule cron job

# Daily at 02:00
0 2 * * * /data/script_name.sh

5.3 Restore steps

drop database jmedb;
create database jmedb;
psql --file=jmedb.sql   # or specify the target DB
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
  echo "Postgres 数据库服务器 ${hostname} 在 ${DATE} 备份失败" >> $BACK_LOG
fi
pg_rman purge >> $BACK_LOG
echo "BACKUP END" >> $BACK_LOG

6.2 Restore procedure

# Stop the server
pg_ctl stop
# Remove old data directory
rm -rf /postgresql/pgdata/
# Restore from backup
pg_rman restore -B /rmanbk
# Start the server
pg_ctl start

7. openGauss Backup

7.1 Backup script

# vi /home/omm/backup.sh
#!/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=$GAUSSHOME/bin:$PATH
DUMP_USER=ysla
DUMP_PASSWORD='jeames007@HW'
CUR_DATE=`date "+%Y-%m-%d-%H%M"`
# Get list of databases
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"
mkdir -p $DAT_FILE_DIR
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 cron job

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

7.3 Cleanup policy

# 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 {} \;