MySQL Periodic Backup and Synchronization to Remote Servers Using Crontab, Rsync, and Inotify
This guide explains how to perform regular MySQL backups, automate them with crontab, compress and rotate old backups, and keep multiple servers in sync using rsync and inotify, providing complete command examples and configuration files for both server and client sides.
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.sqlImport a database:
# 导入数据库mysql -u root -p database < database20160929.sqlBackup 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 databaseCrontab Scheduled Backup
1. Create backup directory:
# root 用户,创建备份目录mkdir -p /bak/mysqlbakcd /bak/mysqldata2. Write the backup script (bakmysql.sh):
#!/bin/bash# Name:bakmysql.sh# This is a ShellScript For Auto DB Backup and Delete old Backupbackupdir=/bak/mysqlbaktime=` date +%Y%m%d%H `mysqldump -u root -ppwd database | gzip > $backupdir/database$time.sql.gzfind $backupdir -name "*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&13. 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.sh5. 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 sourceExample: 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 /bakRsync Server Configuration
Create /etc/rsyncd.conf:
#以下是全局配置log file = /var/log/rsyncd.logpid file = /var/run/rsyncd.pidlock file = /var/lock/rsyncd[mysqlbak]comment = sync rsync/homepath = /bak/mysqlbakuse chroot=noread only = nouid=rootgid=rootmax connections=10auth users = rootsecrets file = /etc/rsyncd.passhosts allow=192.168.53.0/85ignore errors = yestimeout = 600Create authentication file /etc/rsyncd.pass (format user:password) and protect it:
vi /etc/rsyncd.passroot:rootchmod 600 /etc/rsyncd.passEnable the rsync daemon via xinetd:
service rsync {disable = nosocket_type = streamwait = nouser = rootserver = /usr/bin/rsyncserver_args = --daemonlog_on_failure += USERID}Start the daemon:
rsync --daemon --config=/etc/rsyncd.confInotify‑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 # excludeCreate a sync script rsync.sh that triggers rsync on file events:
#rsync auto sync script with inotify#variablescurrent_date=$(date +%Y%m%d_%H%M%S)source_path=/bak/mysqlbak/log_file=/var/log/rsync_client.logrsync_server=192.168.53.86rsync_user=rootrsync_pwd=/etc/rsync_client.pwdrsync_module=mysqlbakINOTIFY_EXCLUDE='(.*/*\.log|.*/*\.swp)$|^/tmp/src/mail/(2014|20.*/.*che.*)'RSYNC_EXCLUDE='/bak/rsync_exclude.lst'if [ ! -e ${rsync_pwd} ]; thenecho "rsync client password file ${rsync_pwd} does not exist!"exit 0fiinotify_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.shWith these configurations, MySQL dumps are created, compressed, rotated, and automatically synchronized to remote servers in near real‑time.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
