Linux regularly backs up mysql and synchronizes it to other servers

Data is the core asset of any company. Regular backup is to ensure that when there is a problem with the database, it can be rolled back to the nearest backup point in time to minimize the loss.

This article will be divided into two parts. Description: 1. Regular backup of mysql; 2. Synchronize to other servers

mysql backup

Backup and restore a database

Backup and restore

# 导出数据库
/usr/bin/mysqldump -u root -ppwd database > database20160929.sql
# 导入数据库
mysql -u root -p database < database20160929.sql

Backup to compressed file Import from compressed file

#备份到压缩文件
/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 a backup directory

# root 用户,创建备份目录
mkdir -p /bak/mysqlbak
cd /bak/mysqldata

2. Write a running script

vi  /usr/sbin/bakmysql.sh

Script code:

#!/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

#

Script description:

backupdir mysql backup address

root mysql user name

pwd mysql password

database database name

mysql_bin_dir mysql bin path;

time=` date +%Y%m%d%H ` can also be written as time="$(date +"%Y%m%d$H")" where the ` symbol is above the TAB key symbol, not the ' symbol to the left of ENTER, and there must be a space after the date.

type f means finding files of common types, f means ordinary files.

mtime +7 searches for files based on their change time. +5 means that the file was changed 7 days ago; if it is -mmin +5, it means the file was changed 5 minutes ago.

exec rm {} means executing a shell command. The exec option is followed by the command or script to be executed, then a pair of {}, a space and a, and finally a semicolon.

/dev/null 2>&1 Redirect standard error to standard output, and then throw it under /DEV/NULL. In layman's terms, it means throwing all standard output and standard error into the trash can; the & in it means to let the command be executed in the background.

3. Add execution permissions to the script

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

4. Set up crontab for scheduled execution

vi /etc/crontab 
#在最后一行中加入：  
00 3 * * * root /usr/sbin/bakmysql.sh
#表示每天3点00分执行备份

Note: The crontab configuration file format is as follows:
Time-sharing day-month-week command

5. Restart crontab

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

This completes the scheduled backup and cleans up the backup data of the previous 7 days

Synchronize to other servers

Here we use the Linux file synchronization tool rsync+inotify to synchronize files

rsync

rsync is Data mirroring backup tool under Unix-like systems - remote sync. A fast incremental backup tool Remote Sync, remote synchronization supports local replication, or synchronization with other SSH and rsync hosts

Usage

rsync src dest

This is the simplest usage, which means synchronizing src and dest files. (That is, after execution, the file of dest is the same as that of src, which shall prevail)

Common options

-a: Equivalent to -rlptgoD, archive style

-r: Recursive

-l: Copy software Link

-p: retain permission information

-t: synchronize the modification time of src to dest

-g: synchronize group information (group)

-o: synchronize owner information (own)

-D : Keep character and block device files

-z: Enable compressed transmission

---delete: If src does not have this file, then dest cannot have it either, that is, delete files that are not in src in dest. (If you use this option, you must use it with the -r option)

## 将本地/bak/mysqlbak/文件同步到 远程服务器 /bak/mysql/bak 目录下面 排除 mysqlbak/index目录 通过ssh端口
rsync -vzacu  /bak/mysqlbak/  root@192.168.53.86:/bak/mysqlbak   --exclude  "mysqlbak/index"   -e "ssh -p 22"
# 将远程目录 /bak/mysqlbak下的文件同步到本地 /bak/mysqlbak/目录下
rsync -vzrtopg --progress --delete root@192.168.53.85:/bak/mysqlbak  /bak

Enable rsync server-side synchronization of remote files

The server side of rsycn is the file receiving end of the server, and the client side of rsycn is the file pushing end of the server.

rsycn server/file receiver configuration

The server needs to enable the rsyncd service

Add the configuration file rsyncd.conf

vi /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           #不使用chroot，不用root权限
   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     #忽略出现I/O错误
   timeout = 600

Create the authentication file

 vi /etc/rsyncd.pass
  ##代码
  root:root      #格式是用户名：密码
  #属主要有权限读这个文件，否则会报没权限
  chmod 600 /etc/rsyncd.pass

Modify the /etc/xinetd.d/rsync file and change disable to no

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

Start the server

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

rsycn client/file sender configuration

The client configuration is simple and you only need to configure the password

  vi /etc/rsync_client.pwd  ##代码
  root    #只需要填写rsync服务的密码  
  #属主要有权限读这个文件，否则会报没权限
  chmod 600 /etc/rsync_client.pwd

Client synchronization test

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

rsync is only a one-time synchronization, if real-time is required Synchronization requires the introduction of another tool

inotify

Inotify 是一种强大的、细粒度的、异步的文件系统事件监控机制，linux内核从2.6.13起，加入了Inotify支持，通过Inotify可以监控文件系统中添加、删除，修改、移动等各种细微事件，利用这个内核接口，第三方软件就可以监控文件系统下文件的各种变化情况，而inotify-tools就是这样的一个第三方软件。

Inotify只需要要按照部署在同步的客户端，当监控的文件有变化触动 rsync脚本来同步

安装

yum install inotify-tools

配置监控的文件路径

vi /etc/inotify_exclude.lst
#代码
/bak/mysqlbak #监控目录
@/bak/log #排除监控目录

rsync排除监控文件目录

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

客户端同步到远程的脚本rsync.sh

#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