Prerequisite Setup

First, create a dedicated backup user on the source MySQL server (192.168.1.1) with the minimum required permissions for consistent backup:

mysql> GRANT SELECT, LOCK TABLES ON *.* TO 'backup_operator'@'192.168.%.%' IDENTIFIED BY 'BackupPass123456';
FLUSH PRIVILEGES;

Next, verify the connnection and backup functionality manually from your backup server (192.168.1.2):

[root@backup-server ~]$ mysqldump -u backup_operator -pBackupPass123456 -h 192.168.1.1 --databases test1 > test1_backup.sql

Create the Automated Backup Script

First, create a dedicated directory to store backup archives:

[root@backup-server ~]$ mkdir -p /opt/db-backups
[root@backup-server ~]$ vim automated-mysql-backup.sh

Add the following script content, adjusting configuration values to match your environment:

#!/bin/bash
# Configuration for remote MySQL backup
db_user="backup_operator"
db_pass="BackupPass123456"
source_host="192.168.1.1"
db_conn=" -u $db_user -p$db_pass -h $source_host"
target_dbs=("test1" "test2")
backup_dir="/opt/db-backups"
mysqldump_bin="/usr/local/mysql/bin/mysqldump"
current_timestamp=$(date +%Y%m%d-%H%M)

# Enter backup working directory
cd $backup_dir

# Process each target database
for db in "${target_dbs[@]}"; do
    backup_name="${db}-${current_timestamp}"
    # Dump database to raw SQL
    $mysqldump_bin $db_conn --databases $db > ${backup_name}.sql
    # Compress backup and remove uncompressed file
    /bin/tar zcf ${backup_name}.tar.gz ${backup_name}.sql --remove-files > /dev/null
done

Test the Backup Script

Make the script executable and run a manual test:

[root@backup-server ~]$ chmod +x automated-mysql-backup.sh
[root@backup-server ~]$ ./automated-mysql-backup.sh

Password warnings are expected for this basic setup. Verify that backup archives were generated correctly:

[root@backup-server ~]$ ls /opt/db-backups/
test1-20240520-2215.tar.gz  test2-20240520-2215.tar.gz

Schedule Recuring Backups with Cron

Move the finished script to the backup directory and add a cron job for automated daily backup:

[root@backup-server ~]$ mv automated-mysql-backup.sh /opt/db-backups/
[root@backup-server ~]$ crontab -e

Add this line to run a new backup every day at 10:00 PM:

0 22 * * * /opt/db-backups/automated-mysql-backup.sh

Confirm the cron scheduler is running to execute the job:

[root@backup-server ~]$ systemctl status crond
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2024-05-20 10:15:30 CST; 12h ago
 Main PID: 1493 (crond)
    Tasks: 1
   CGroup: /system.slice/crond.service
           └─1493 /usr/sbin/crond -n