MySQL Periodic Backup and Synchronization to Remote Servers Using Crontab, Rsync, and Inotify

Data is a core asset for any company, and regular backups ensure that a database can be restored to the latest safe point, minimizing loss.

The article is divided into two parts: (1) MySQL periodic backup; (2) Synchronization to other servers.

MySQL Backup

Backup and Restore a Database

Export a database:

# 导出数据库

/usr/bin/mysqldump -u root -ppwd database > database20160929.sql

Import a database:

# 导入数据库

mysql -u root -p database < database20160929.sql

Backup to a compressed file and restore from it:

#备份到压缩文件

/usr/bin/mysqldump -u root -ppwd database | gzip > database20160929.sql.gz

#从压缩文件导入

gzip < database20160929.sql.gz | mysql -u root -p database

Crontab Scheduled Backup

1. Create backup directory:

# root 用户,创建备份目录

mkdir -p /bak/mysqlbak

cd /bak/mysqldata

2. Write the backup script (bakmysql.sh):

#!/bin/bash

# Name:bakmysql.sh

# This is a ShellScript For Auto DB Backup and Delete old Backup

backupdir=/bak/mysqlbak

time=` date +%Y%m%d%H `

mysqldump -u root -ppwd database | gzip > $backupdir/database$time.sql.gz

find $backupdir -name "*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1

3. Make the script executable:

# chmod +x /usr/sbin/bakmysql.sh

4. Add a crontab entry to run daily at 03:00:

vi /etc/crontab

#在最后一行中加入：

00 3 * * * root /usr/sbin/bakmysql.sh

5. Restart the cron service:

/etc/rc.d/init.d/crond restart

These steps complete automated backup and removal of backups older than seven days.

Synchronization to Other Servers

Use rsync together with inotify to keep files synchronized.

Rsync Basics

Rsync is a fast incremental backup tool for Unix-like systems.

Simple usage:

rsync src dest

Common options:

-a : archive mode (equivalent to -rlptgoD)

-r : recursive

-l : copy symlinks as symlinks

-p : preserve permissions

-t : preserve modification times

-g : preserve group

-o : preserve owner

-D : preserve device files

-z : compress during transfer

--delete : delete files in destination that no longer exist in source

Example: sync local backup directory to a remote server, excluding a subdirectory:

rsync -vzacu /bak/mysqlbak/ [email protected]:/bak/mysqlbak --exclude "mysqlbak/index" -e "ssh -p 22"

Example: pull remote backup to local:

rsync -vzrtopg --progress --delete [email protected]:/bak/mysqlbak /bak

Rsync Server Configuration

Create /etc/rsyncd.conf :

#以下是全局配置

log file = /var/log/rsyncd.log

pid file = /var/run/rsyncd.pid

lock file = /var/lock/rsyncd

[mysqlbak]

comment = sync rsync/home

path = /bak/mysqlbak

use chroot=no

read only = no

uid=root

gid=root

max connections=10

auth users = root

secrets file = /etc/rsyncd.pass

hosts allow=192.168.53.0/85

ignore errors = yes

timeout = 600

Create authentication file /etc/rsyncd.pass (format user:password ) and protect it:

vi /etc/rsyncd.pass

root:root

chmod 600 /etc/rsyncd.pass

Enable the rsync daemon via xinetd:

service rsync {

disable = no

socket_type = stream

wait = no

user = root

server = /usr/bin/rsync

server_args = --daemon

log_on_failure += USERID

}

Start the daemon:

rsync --daemon --config=/etc/rsyncd.conf

Inotify‑Driven Real‑Time Sync

Install inotify‑tools:

yum install inotify-tools

Define directories to watch in /etc/inotify_exclude.lst :

/bak/mysqlbak # monitor directory

@/bak/log # exclude

Create a sync script rsync.sh that triggers rsync on file events:

#rsync auto sync script with inotify

#variables

current_date=$(date +%Y%m%d_%H%M%S)

source_path=/bak/mysqlbak/

log_file=/var/log/rsync_client.log

rsync_server=192.168.53.86

rsync_user=root

rsync_pwd=/etc/rsync_client.pwd

rsync_module=mysqlbak

INOTIFY_EXCLUDE='(.*/*\.log|.*/*\.swp)$|^/tmp/src/mail/(2014|20.*/.*che.*)'

RSYNC_EXCLUDE='/bak/rsync_exclude.lst'

if [ ! -e ${rsync_pwd} ]; then

echo "rsync client password file ${rsync_pwd} does not exist!"

exit 0

fi

inotify_fun(){

/usr/bin/inotifywait -mrq --timefmt '%Y/%m/%d-%H:%M:%S' --format '%T %w %f' \

--exclude ${INOTIFY_EXCLUDE} -e modify,delete,create,move,attrib ${source_path} \

| while read file; do

/usr/bin/rsync -auvrtzopgP --exclude-from=${RSYNC_EXCLUDE} --progress --bwlimit=200 --password-file=${rsync_pwd} ${source_path} ${rsync_user}@${rsync_server}::${rsync_module}

done

}

inotify_fun >> ${log_file} 2>&1 &

Make the script executable and run it:

chmod 777 rsync.sh

./rsync.sh

With these configurations, MySQL dumps are created, compressed, rotated, and automatically synchronized to remote servers in near real‑time.