Automate MySQL Backups with Bash: Script, Rsync, and Cron Guide

Knowledge Base

Linux find command (reference: https://mp.weixin.qq.com/s?__biz=MzI3NzQ4MTE4Mw==∣=2247484146&idx=2&sn=689c3c657f57f809196ab46296a7e364)

MySQL export syntax using mysqldump Remote backup with rsync (reference: https://mp.weixin.qq.com/s?__biz=MzI3NzQ4MTE4Mw==∣=2247483788&idx=1&sn=cde6f3645c10885944c5826d67e7d0a3)

SSH password‑less login (reference: https://mp.weixin.qq.com/s?__biz=MzI3NzQ4MTE4Mw==∣=2247483675&idx=1&sn=f00a26df3f77477e2aa88cf0f0c690c3)

Usage Scenario

Run backup daily at 03:00, covering databases db1, db2, db3.

Store backup files locally under /data/mysql.

Compress backups with gzip.

Name compressed files like db1-2020-02-24-20:57.sql.gz.

Synchronize backups to a remote server using rsync.

Retain local backups for two months.

MySQL Backup Script (mysql-backup.sh)

#! /bin/bash
# Save as /etc/mysql-backup.sh
BAKDIR="/data/mysql"
MYSQLUSR="root"
MYSQLPW="123123"
LOG=/var/log/mysql-backup.log
NOW=`date +%Y-%m-%d-%H:%M`
[ ! -d $BAKDIR ] && mkdir -p $BAKDIR

echo "##########today##########" >> $LOG
echo "mysql backup begin at `date`" >> $LOG

for db in db1 db2 db3
do
  mysqldump -u$MYSQLUSR -p$MYSQLPW $db | gzip > $BAKDIR/$db-$NOW.sql.gz
  if [ $? == 0 ]; then
    echo "$NOW--$db backup succeeded!" >> $LOG
  else
    echo "$db backup failed!" >> $LOG
  fi
done

rsync -az --delete /data/mysql/* [email protected]:/data/backup >/dev/null 2>&1
if [ $? == 0 ]; then
  echo "$NOW Remote backup succeeded!" >> $LOG
else
  echo "$NOW Remote backup failed!" >> $LOG
fi

find $BAKDIR -type f -mtime +60 -name "*.gz" | xargs rm -rf

echo "mysql backup end at `date`" >> $LOG

Make Script Executable

chmod +x /etc/mysql-backup.sh

Schedule with Cron

Open the crontab editor and add the following line to run the script every day at 03:00:

0 3 * * * /etc/mysql-backup.sh

Test Execution

Run the script manually to verify it works. The following screenshot shows the expected log output:

Importing a Database

mysql -u用户名 -p密码 数据库名 < 数据库名.sql