Linux regularly backs up MySQL and synchronizes to other servers

Data is the most core asset in any company, and regular backups are done to ensure that the database is in a timely fashion to roll back to the nearest backup point, minimizing the loss

This article will be two parts to explain: 1, regular backup of MySQL, 2, sync to other servers

MySQL Backup

Backup Restore a Database

Backup 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 backups

1. Create a backup directory

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

2. Write Run 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/mysqlbaktime=` 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:

• backupdirMySQL Backup address
• rootMySQL User name
• pwdMySQL Password
• databaseDatabase name
• mysql_bin_dirThe bin path of MySQL;
• time=` date +%Y%m%d%H `It can also be written as the symbol is the sign time="$(date +"%Y%m%d$H")" ` above the TAB key, not the ' symbol on the left of enter ', and a space after date.
• type fRepresents a common type of file, and F represents a normal file.
• mtime +7The file changes time to find the file, +5 means that the file change time is 7 days ago; if it is-mmin +5, the file change time is now 5 minutes ago.
• exec rm {} \Represents executing a shell command, followed by the EXEC option with the command or script to execute, followed by a pair of {}, a space and one, and finally a semicolon.
• /dev/null 2>&1REDIRECT the standard error to standard output, and then throw it under the/dev/null. In layman's words, all standard output and standard errors are thrown into the trash, where the & means that the command executes in the background.

3. Add Execute permissions for scripts

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

4, set crontab timing execution

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

Note: The crontab configuration file format is as follows:
Time-sharing Weekly command

5. Restart Crontab

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

That's it. Scheduled backups and cleanup of backup data for the first 7 days

Synchronizing to other servers

This uses the Linux Sync File tool rsync+inotify to synchronize the files.

Rsync

Rsync is a data mirroring Backup tool--remote Sync under Unix-like systems. A fast incremental backup tool remote sync that supports local replication or synchronizes with other SSH and rsync hosts

Usage

rsync src dest

This is the simplest usage, which means synchronizing the Src,dest file. (i.e., after execution, the Dest file is the same as SRC, whichever is src)

Common options

• -A: Equivalent to-rlptgod, archive
• -R: Recursive
• -L: Copy software link
• -P: Retain permission information
• -T: Synchronize the modified time of SRC to dest
• -G: Sync Group info (group)
• -O: Synchronization owner Information (OWN)
• -D: Keep characters with block device files
• -Z: Enable compressed transport
• -–delete: If SRC does not have this file, then dest also cannot have, that is, in the dest delete the files that are not in SRC. (If you use this option, it must be paired with the-R option)

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

Enable Rsync server-side synchronization remote files

RSYCN is the server's file receiving end, RSYCN client is the server's file push end.

RSYCN Server/File Receive-side configuration

Server needs to open RSYNCD service

Add configuration file rsyncd.conf

vi/etc/rsyncd.conf# The following is the global configuration log file =/var/log/rsyncd.logpid File =/var/run/rsyncd.pidLockFILE =/var/Lock/rsyncd[mysqlbak] #模块名, specify this name on the source serverComment =Sync Rsync/home #描述信息Path =/bak/mysqlbak #备份目录use chroot=no #不使用chroot without root access Read Only = no #设置本地备份目录为读写权限 uid=root gid=root max connections =Ten #客户端最大连接数 auth users = root #指定数据同步用户 secrets file =/etc/rsyncd.pass #指定数据同步用户信息文件hosts allow=< c11>192.168.0/#允许连接的客户端ignore errors = yes #忽略出现I/O error timeout = 600 

Create an authentication file

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

Modify/etc/xinetd.d/rsync file, disable change 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 service side

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

RSYCN client/File send-side configuration

Simple client configuration requires only a configuration password to

  /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/ [email protected]192.168.53.86::mysqlbak

Rsync is just a one-time sync, and if you need to sync it in real, you need to introduce another tool

INotify

Inotify is a powerful, fine-grained, asynchronous file system event monitoring mechanism, the Linux kernel from 2.6.13, joined the Inotify support, through Inotify can monitor the file system to add, delete, modify, move and other subtle events, using this kernel interface, Third-party software can monitor the file system under the various changes, and inotify-tools is such a third-party software.

INotify only need to follow the deployment in sync with the client, when the monitored files have changed touch rsync script Sync

Installation

install inotify-tools

Configuring the file path for monitoring

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

rsync Exclusion Monitoring Files directory

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

The client synchronizes to the remote script rsync.sh

#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.log#rsyncrsync_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 '#rsync client pwd Checkif [!-E${RSYNC_PWD}];ThenEcho-EThe Rsync client Passwod file${RSYNC_PWD} does not exist! "Exit 0Fi#inotify_functionInotify_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 loginotify_fun >> ${log_file} 2>&1 &    

Execute permissions on the script, and then you can do it.

chmod 777 rsync.sh./rsync.sh 

Reference:

Linux under the Sync Tool inotify+rsync use detailed

Http://www.cnblogs.com/ityouknow/p/5923489.html

Linux regularly backs up MySQL and synchronizes to other servers