After the server crashed, how did I use a backup script to recover all the data?

MySQL database automatic backup | mysqldump full backup | crontab scheduled task | disk fuse | recovery verification

A server hard drive failure nearly cost me all my customer data. Since then, I wrote this script and it has been running as smoothly as an old dog for more than half a year. Today I will share the complete plan, including the pitfalls I have stepped on.

Background

My server runs more than a dozen business systems, and MySQL stores contract data, approval records, and monitoring logs. Once the disk was full and MySQL crashed. Fortunately, there was a cold backup so no data was lost.

After learning from the experience, I did something wrongFully automatic backup solution, added disk fusing, expiration cleanup, and binlog refresh. Even if it collapses in the middle of the night, the data will still be there when I wake up the next day.

1. Backup script

1.1 Configure MySQL password-free login

Writing it directly in the command line will cause security risks (history can check the password), it is recommended to use the configuration file:

cat > ~/.my.cnf <<EOF
[client]
user=你的用户名
password=你的密码
EOF
chmod 600 ~/.my.cnf

⚠️ Note: The user must have read permissions to the database. It is recommended to use root directly. Insufficient permissions will cause the backup to fail silently. This is the first pitfall I have stepped on.

1.2 Core backup script

Script path:/usr/local/bin/mysql_backup.sh

#!/bin/bash

BACKUP_DIR="/home/storehouse/DBbackup"
RETAIN_DAYS=7
MAX_DISK_USAGE=80

# 磁盘熔断：使用率超过 80% 直接跳过，防止备份把磁盘写满
CURRENT_USAGE=$(df -h /home | awk 'NR==2 {print $5}' | sed 's/%//')

if [ $CURRENT_USAGE -gt $MAX_DISK_USAGE ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') 警告: 磁盘使用率 ${CURRENT_USAGE}%，超过阈值，跳过本次备份！"
    exit 1
fi

mkdir -p $BACKUP_DIR
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/full_backup_${DATE}.sql.gz"

echo "$(date '+%Y-%m-%d %H:%M:%S') 开始备份..."
mysqldump --defaults-extra-file=~/.my.cnf \
  --all-databases \
  --single-transaction \
  --routines \
  --events \
  | gzip -9 > $BACKUP_FILE

if [ $? -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') 备份成功: $BACKUP_FILE (大小: $(du -h $BACKUP_FILE | cut -f1))"
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') 备份失败！"
    exit 1
fi

# 清理 7 天前的旧备份
find $BACKUP_DIR -name "full_backup_*.sql.gz" -mtime +$RETAIN_DAYS -delete

# 刷新 binlog，配合备份做时间点恢复
mysql --defaults-extra-file=~/.my.cnf -e "FLUSH LOGS;"

echo "$(date '+%Y-%m-%d %H:%M:%S') 备份流程结束，当前磁盘使用率: $(df -h /home | awk 'NR==2 {print $5}')"

chmod +x /usr/local/bin/mysql_backup.sh

1.3 Configure scheduled tasks

crontab -e

Add to:

0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

Automatically runs at 2 a.m. every day, and the logs are thrown to /var/log/mysql_backup.log。

2. Daily inspection

2.1 Check whether the backup is successful every day

tail -20 /var/log/mysql_backup.log
ls -lh /home/storehouse/DBbackup/

2.2 Check disk space weekly

df -h

make sure /home Partition usage does not exceed 80%.

2.3 Do a recovery test once a month (This is the most important）

Backup without recovery testing = no backup. Verify in the test environment once a month:

gunzip < /home/storehouse/DBbackup/full_backup_xxxxxx.sql.gz | mysql -u root -p

3. Why not just use the cloud vendor’s snapshot/Percona?

  • cloud snapshot: The recovery granularity is too coarse and cannot be used to restore a single table or a single database. Moreover, it is not your own thing. If the cloud provider collapses, you will collapse.
  • Percona XtraBackup: More suitable for physical backup of large databases and heavy deployment. Small and medium-sized libraries directly use mysqldump + gzip, which is sufficient and easy to migrate.
  • No third-party monitoring/alerting is used: Keep zero dependencies. If you have any questions, just check the logs. No additional Grafana or DingTalk robots are needed. Of course, if needed later, you can just add a line of webhook to the script.

4. The pits I have stepped on

  1. Insufficient permissions cause backup to fail silently：.my.cnf Users in must be able to read all libraries. For the first time, I used an account with only partial library permissions. No error was reported during the backup but data was missing. I only discovered it when I restored it.
  2. MySQL crashes when the disk is full: This is why I added disk fusing. If the backup script ignores the disk, it will make things worse.
  3. crontab environment variables are inconsistent with manual execution：cron When running a script PATH Very simple, mysqldump and gzip must use absolute paths or specify them in the script export PATH. I installed the full MySQL client directly, so I didn't encounter it, but remember to check if you use a minimized system.

Conclusion

I have been running this solution stably in the production environment for more than half a year. It's not perfect, but it works. The key isSimple, no dependencies, you can fix it yourself if there is a problem。

Too lazy to configure manually? I have packaged the complete one-click deployment script and just copy it to the server and run it directly.Reply to the private message "Backup" and send it directly to you.

If you have any questions, please feel free to communicate in the comment area or private message. If you are also doing server operation and maintenance, you can also pay attention to it. Later I will share monitoring, log cutting, and Docker practice.