Cross‑Database Backup & Restore Scripts for MySQL, Oracle, and PostgreSQL

Overview

Production‑environment database backups are critical for data safety and business continuity. This summary presents Bash scripts that perform remote backup and restore for MySQL, Oracle, and PostgreSQL, together with cleanup utilities and cron‑job examples.

MySQL

Remote backup script

The script runs on a Linux host, creates a date‑named directory, dumps all databases with mysqldump, compresses the dump to .tar.gz, removes backups older than 7 days, and transfers the archive to a remote backup server via scp. Password‑less SSH between the MySQL server and the backup host is required.

#!/bin/bash
DB_USER="root"
DB_PASS="1Q!2W@3E#"
DB_HOST="192.168.1.100"
DBBACK_IP="192.168.1.200"
BCK_DIR="/bigdata/mysql"
DBBACK_PATH="/bigdata/mysqlbackup"
DATE=$(date +%F)
YESTERDAY=$(date -d '-7 day' +%Y-%m-%d)
mkdir -p $BCK_DIR/$DATE
/usr/local/mysql/bin/mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST --all-databases > $BCK_DIR/$DATE/mysql_backup_$DATE.sql
cd $BCK_DIR/$DATE && tar -zcvf mysql_backup_$DATE.sql.tar.gz mysql_backup_$DATE.sql && rm -f mysql_backup_$DATE.sql
echo "$DATE db backup success!" >> $BCK_DIR/$DATE/$DATE.log
rm -rf $BCK_DIR/$YESTERDAY
scp -r $BCK_DIR/$DATE/mysql_backup_$DATE.sql.tar.gz root@$DBBACK_IP:$DBBACK_PATH

Restore command

After installing MySQL, restore a dump with:

mysql -u<username> -p<password> <database_name> < backup_file.sql

Cron scheduling

Make the script executable ( chmod +x mysql_backup.sh) and add a cron entry, for example:

0 3 * * 6 root sh /bigdata/mysql_backup.sh >> /bigdata/mysql_backup.log 2>&1

Oracle

Remote backup script

The script uses exp to create a full export (.dmp), compresses the file, transfers it via scp, and removes local and remote files older than two weeks. It also sends email notifications on success or failure.

#!/bin/bash
echo "Starting Oracle backup..."
CURRENT_DATE=$(date +%Y-%m-%d)
DMP_DIR=/data/originbackup
DEST_DIR=/opt/backup
LOCAL_IP=192.168.1.111
REMOTE_IP=192.168.1.110
ORACLE_USER=oracle_db
ORACLE_PASS=4r$5t%6y^
MAIL="[email protected],[email protected]"

mkdir -p $DMP_DIR/$CURRENT_DATE
exp $ORACLE_USER/$ORACLE_PASS@$LOCAL_IP:1521/orcl file=$DMP_DIR/$CURRENT_DATE.dmp full=y > /dev/null 2>&1

# Compress
gzip -f $DMP_DIR/$CURRENT_DATE.dmp

# Ensure remote directory exists
ssh root@$REMOTE_IP "mkdir -p $DEST_DIR"

# Transfer
scp $DMP_DIR/$CURRENT_DATE.dmp.gz root@$REMOTE_IP:$DEST_DIR

# Cleanup local files older than 14 days
find $DMP_DIR -type f -name "*.dmp.gz" -mtime +14 -exec rm -f {} \;
# Cleanup remote files older than 14 days
ssh root@$REMOTE_IP "find $DEST_DIR -type f -name \"*.dmp.gz\" -mtime +14 -exec rm -f {} \\""

# Notification (example using mail)
if [ $? -eq 0 ]; then
  echo "Oracle backup succeeded" | mail -s "Oracle backup success" $MAIL
else
  echo "Oracle backup failed" | mail -s "Oracle backup failure" $MAIL
fi

Restore procedure

After installing Oracle, create a directory for the dump, copy the oracle_db.dmp file there, grant read/write permissions, and run the Data Pump import:

su - oracle
impdp oracle_db/oracle_db@orcl SCHEMAS=oracle_db DUMPFILE=oracle_db.dmp LOGFILE=oracle_db_imp.log

Cron scheduling

Make the script executable and add a line to /etc/crontab, e.g.:

0 3 * * 6 root sh /opt/oracle_backup.sh >> /opt/oraclebackup.log 2>&1

PostgreSQL

Remote backup script

The script records the current date, uses pg_dumpall to export all databases, compresses the result, transfers it via scp, and deletes backups older than three days.

#!/bin/bash
echo "Starting PostgreSQL backup..."
TODAY=$(date +%Y-%m-%d)
HOST_IP=127.0.0.1
REMOTE_IP=192.168.1.111
PORT=5432
USER=postgres
export PGPASSWORD='7u&8i*9o('
DMP_DIR=/opt/pgbak
DEST_DIR=/bigdata/pg_backup

# Dump all databases
/monchickey/bin/pg_dumpall --file "$DMP_DIR/pg_backup_$TODAY.sql" --host "$HOST_IP" --port "$PORT" --username "$USER"

gzip "$DMP_DIR/pg_backup_$TODAY.sql"

# Transfer
scp -r "$DMP_DIR/pg_backup_$TODAY.sql.gz" root@$REMOTE_IP:$DEST_DIR

# Delete local files older than 3 days
find $DMP_DIR -name "*.sql.gz" -mtime +3 -exec rm -f {} \;

Restore command

Create the target database, then restore the backup with pg_restore:

pg_restore -U <username> -d <database_name> <backup_file_path>

Cron scheduling

Make the script executable and add a cron entry, for example:

0 3 * * 6 root sh /home/postgres/pg_dump_backup.sh >> /home/postgres/postgres_backup.log 2>&1

Cleanup script for old backups

A generic script that deletes files older than seven days in a specified backup directory can be used for any of the three databases:

#!/bin/bash
echo "Deleting backup files older than 7 days..."
find /path/to/backup/ -type f -mtime +7 -exec rm -f {} \;
echo "Old backup files removed."

Key considerations

All scripts assume password‑less SSH is configured between the database host and the remote backup server.

Adjust paths to mysqldump, pg_dumpall, and exp according to the actual installation locations (use find / -name mysqldump etc.).

Update variables such as IP addresses, directories, usernames, and passwords to match your environment.

Ensure the cron daemon is running and that the specified user (often root) has permission to execute the scripts.