linux定時(shí)備份mysql并同步到其它服務(wù)器

# 導(dǎo)出數(shù)據(jù)庫
/usr/bin/mysqldump -u root -ppwd database > database20160929.sql
# 導(dǎo)入數(shù)據(jù)庫
mysql -u root -p database  database20160929.sql

#備份到壓縮文件
/usr/bin/mysqldump -u root -ppwd database | gzip > database20160929.sql.gz
#從壓縮文件導(dǎo)入
gzip  database20160929.sql.gz | mysql -u root -p database
crontab定時(shí)備份

# root 用戶,創(chuàng)建備份目錄
mkdir -p /bak/mysqlbak
cd /bak/mysqldata

#!/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 `
mysql_bin_dir/mysqldump -u root -ppwd database | gzip > $backupdir/database$time.sql.gz
#
find $backupdir -name "name_*.sql.gz" -type f -mtime +7 -exec rm {} ; > /dev/null 2>1

#

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

vi /etc/crontab 
#在最后一行中加入： 
00 3 * * * root /usr/sbin/bakmysql.sh
#表示每天3點(diǎn)00分執(zhí)行備份
注：crontab配置文件格式如下：
分　時(shí)　日　月　周　 命令 

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

vi /etc/rsyncd.conf
#以下是全局配置
log file = /var/log/rsyncd.log
pid file = /var/run/rsyncd.pid
lock file = /var/lock/rsyncd
[mysqlbak]   #模塊名，在源服務(wù)器指定這個(gè)名字
  comment = sync rsync/home   #描述信息
  path = /bak/mysqlbak   #備份目錄
  use chroot=no      #不使用chroot，不用root權(quán)限
  read only = no     #設(shè)置本地備份目錄為讀寫權(quán)限
  uid=root     
  gid=root
  max connections=10    #客戶端最大連接數(shù)
  auth users = root   #指定數(shù)據(jù)同步用戶
  secrets file = /etc/rsyncd.pass     #指定數(shù)據(jù)同步用戶信息文件
  hosts allow=192.168.53.0/85   #允許連接的客戶端
  ignore errors = yes   #忽略出現(xiàn)I/O錯(cuò)誤
  timeout = 600

vi /etc/rsyncd.pass
 ##代碼
 root:root   #格式是用戶名：密碼
 #屬主要有權(quán)限讀這個(gè)文件，否則會(huì)報(bào)沒權(quán)限
 chmod 600 /etc/rsyncd.pass 

service rsync
{
    disable = no
    socket_type   = stream
    wait      = no
    user      = root
    server     = /usr/bin/rsync
    server_args   = --daemon
    log_on_failure += USERID
}

vi /etc/rsync_client.pwd
##代碼
root #只需要填寫rsync服務(wù)的密碼
#屬主要有權(quán)限讀這個(gè)文件，否則會(huì)報(bào)沒權(quán)限
chmod 600 /etc/rsync_client.pwd

/usr/bin/rsync -auvrtzopgP --progress --password-file=/etc/rsync_client.pwd /bak/mysqlbak/ root@192.168.53.86::mysqlbak

vi /etc/inotify_exclude.lst
#代碼
/bak/mysqlbak #監(jiān)控目錄
@/bak/log #排除監(jiān)控目錄
rsync排除監(jiān)控文件目錄

vi /etc/rsyncd.d/rsync_exclude.lst
#代碼
src/*.html*
src/js/
src/2014/20140[1-9]/

#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
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'
#rsync client pwd check
if [ ! -e ${rsync_pwd} ];then
  echo -e "rsync client passwod file ${rsync_pwd} does not exist!"
  exit 0
fi
#inotify_function
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 log
inotify_fun >> ${log_file} 2>1 

chmod 777 rsync.sh
./rsync.sh