How to Prevent Catastrophic Data Loss with Automated MySQL and File Backups

Introduction

The term 删库跑路 ("delete‑database‑run‑away") humorously describes a situation where production data is accidentally erased, causing severe business disruption. The most reliable defense against this scenario is a robust backup strategy combined with strict permission control, audit logging, and monitoring.

Key Prevention Measures

Permission control : Limit direct access to production environments to trusted personnel only.

Audit logging : Record who performed which operation and when.

Backup strategy : Perform regular backups and store them securely.

Geographic redundancy : Keep copies in multiple locations.

Principle of least privilege : Grant only the minimum permissions required.

Monitoring and alerts : Deploy systems to detect abnormal operations.

Training : Educate staff about the risks of accidental deletions.

The single most important practice is to back up data—multiple times.

Environment Overview

The tutorial assumes the following environment:

Ubuntu operating system

Docker containers

MySQL running inside a Docker container

FastDFS distributed file system (or Redis data files) for file storage

Expect tool for automating interactive commands

Backup Plan

3.1 Backing Up All MySQL Data

Use mysqldump to export the entire database to an SQL file.

Compress the SQL file with tar.

Encrypt the compressed archive using openssl.

Delete expired backup files according to a retention policy.

Transfer the encrypted archive to remote servers with scp or rsync for off‑site storage.

3.2 Backing Up File Data

Compress and encrypt the target directory.

Split the archive into volume files (e.g., 200 MB each).

Merge volumes when needed.

Upload the encrypted parts to remote storage.

Delete expired backup files.

Automated Periodic Backup

After creating the backup scripts, schedule them with a cron job. The example runs daily at 02:10 and appends output to /home/passjava/backup/cron_log.txt:

crontab -u root -e
10 2 * * * bash /home/passjava/backup/your_script.sh >> /home/passjava/backup/cron_log.txt

Backup Scripts

5.1 Database Backup Script

#!/bin/bash
# Set MySQL credentials (adjust as needed)
mysql_user="root"
mysql_password="xxx"
mysql_host="database_server_ip"
mysql_port="3306"

# Backup destination
backup_location=/home/passjava/backup/mysql/passjava_web

# Retention policy
expire_backup_delete="ON"
expire_days=7
backup_time=$(date +%Y-%m-%d-%H-%M-%S)

# Get MySQL container ID
mysqlContainerName=$(sudo docker ps -q --filter "name=mysql")

# Dump the database inside the container
sudo docker exec $mysqlContainerName mysqldump passjava_web -u$mysql_user -p$mysql_password > $backup_location/$backup_time-backup-mysql-passjava_web.sql

# Compress and encrypt
tar -czvf - $backup_location/$backup_time-backup-mysql-passjava_web.sql | \
  openssl des3 -salt -k passjava123456 -out $backup_location/$backup_time-backup-mysql-passjava_web.sql.tar.gz

# Delete expired backups
if [ "$expire_backup_delete" == "ON" ] && [ -n "$backup_location" ]; then
  find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf
  echo "Expired backup data delete complete!"
fi

# Remote copy using expect for password automation
expect -c "
  spawn scp -r $backup_location/$backup_time-backup-mysql-passjava_web.sql.tar.gz passjava@remote1:/home/passjava/backup/mysql/passjava_web
  expect {\"*assword\" {send \"passjava\r\"; exp_continue} \"yes/no\" {send \"yes\r\"}}
  spawn scp -r $backup_location/$backup_time-backup-mysql-passjava_web.sql.tar.gz passjava@remote2:/home/passjava/backup/mysql/passjava_web
  expect {\"*assword\" {send \"passjava\r\"; exp_continue} \"yes/no\" {send \"yes\r\"}}
  expect eof
"

# Clean up local SQL file
rm -f $backup_location/$backup_time-backup-mysql-passjava_web.sql

5.2 File Backup Script

This script backs up FastDFS files (or Redis data) and follows the same retention and remote copy logic.

#!/bin/bash
# Backup destination
backup_location=/home/passjava/backup/fdfs/data

# Retention policy
expire_backup_delete="ON"
expire_days=7
backup_time=$(date +%Y-%m-%d-%H-%M-%S)

# Compress, encrypt, and split into 200 MB volumes
tar -czvf - /home/passjava/fdfs | \
  openssl des3 -salt -k passjava123456 | \
  split -b 200m -d - $backup_location/$backup_time-fdfs-data.tar.gz

# Delete expired backups
if [ "$expire_backup_delete" == "ON" ] && [ -n "$backup_location" ]; then
  find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf
  echo "Expired backup data delete complete!"
fi

# Reassemble volumes when needed
cat $backup_location/$backup_time-fdfs-data.tar.gz* > $backup_location/$backup_time-fdfs-data-all.tar.gz

# Remote copy using expect
expect -c "
  spawn scp -r $backup_location/$backup_time-fdfs-data-all.tar.gz [email protected]:/home/passjava/backup/fdfs/data
  expect {\"*assword\" {send \"passjava\r\"; exp_continue} \"yes/no\" {send \"yes\r\"}}
  expect eof
"

# Clean up local split files
rm -f $backup_location/$backup_time-fdfs-data.tar.gz*

Conclusion

Accidental deletion of production data can cause massive financial loss and security risks. Implementing strict permission management, regular automated backups, and reliable restoration scripts is essential for data safety and system stability.