Data is the core asset in any company, and regular backups are designed to ensure that when the database is in trouble, it can be rolled back to the nearest backup point in time, minimizing loss

This article will cover two parts: 1. periodic backup of mysql; 2. synchronization to other servers

mysql backup

Backup Restore a Database

Backup Restore

# Export Database
/usr/bin/mysqldump -u root -ppwd database > database20160929.sql
# Import Database
mysql -u root -p database < database20160929.sql

Backup to compressed file Import from compressed file

#Back up to a compressed file
/usr/bin/mysqldump -u root -ppwd database  | gzip > database20160929.sql.gz
#Import from a compressed file
gzip < database20160929.sql.gz | mysql -u root -p database

crontab Timed Backup

1. Create backup directory

# root user, create backup directory
mkdir -p /bak/mysqlbak
cd /bak/mysqldata

2. Write running scripts

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
The bin path of mysql_bin_dir mysql;
Time=`date +%Y%m%d%H ` can also be written as time='$(date +"%Y%m%d$H')', where the `symbol is the symbol above the TAB key, not the'symbol to the left of ENTER', and a space after date.
type f means to find a file of common type, and f means a file of common type.
mtime +7 looks for the file according to the time it was changed, +5 means the file was changed 7 days ago, and -mmin +5 means the file was changed 5 minutes ago.
exec rm {} \ means to execute a shell command followed by a command or script to execute, followed by a pair of {}, a space, a \, and a semicolon.
/dev/null 2>&1 redirects standard errors to standard output and throws them under/DEV/NULL.Common sense is to throw all standard outputs and standard errors into the dustbin; one of them is to have the command executed in the background.
3. Add Execution Rights to Scripts

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

4. Set crontab timer execution

vi /etc/crontab 
#Add in the last line:  
00 3 * * * root /usr/sbin/bakmysql.sh
#Represents backup performed at 3:00 a day

Note: The crontab configuration file format is as follows:
Hourly Date Month Week Command

 

5. Restart crontab

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

 

This completes the regular backup and cleans up the backup data from the previous 7 days

Synchronize to other servers
Use rsync+inotify, a Linux synchronization file tool, to synchronize files here

rsync
rsync is a data mirror backup tool for unix-like systems, remote sync.Remote Sync, a fast incremental backup tool that supports local replication or synchronization with other SSH, rsync hosts

usage

rsync src dest

This is the simplest use, meaning to synchronize src,dest files.(that is, after execution, the dest file is the same as the src, whichever is the src)

Common Options

-a: equivalent to -rlptgoD, Archive
-r:Recursive
-l: Copy software links
-p:Reserve permission information
-t: Synchronize src modification time 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 the SRC does not have this file, then dest cannot, that is, dest deletes files that are not in the src.(If you use this option, you must go with the -r option)

## Will be local/bak/mysqlbak/Synchronize files to remote server /bak/mysql/bak Exclude under Catalog mysqlbak/index Directory Passed ssh port
rsync -vzacu  /bak/mysqlbak/  root@192.168.53.86:/bak/mysqlbak   --exclude  "mysqlbak/index"   -e "ssh -p 22"
# Will remote directory /bak/mysqlbak Synchronize files under to local /bak/mysqlbak/Catalog
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 receiver side of the server, and the client side of rsycn is the file driver side of the server.

Server/File Receiver Configuration for rsycn

Server needs to start rsyncd service

Add configuration file rsyncd.conf

vi /etc/rsyncd.conf
#The following is the global configuration
log file = /var/log/rsyncd.log
pid file = /var/run/rsyncd.pid
lock file = /var/lock/rsyncd
[mysqlbak]     #Module name, specify this name on the source server
   comment = sync rsync/home      #Descriptive Information
   path = /bak/mysqlbak      #Backup directory
   use chroot=no           #Do not use chroot, do not use root privileges
   read only = no          #Set local backup directory to read and write permissions
   uid=root          
   gid=root
   max connections=10       #Maximum number of client connections
   auth users = root      #Specify data synchronization users
   secrets file = /etc/rsyncd.pass          #Specify data synchronization user information file
   hosts allow=192.168.53.0/85     #Clients Allowed to Connect
   ignore errors = yes     #Ignore Appearance I/O error
   timeout = 600

Create Authentication File

  vi /etc/rsyncd.pass
  ##Code
  root:root      #The format is user name: password
  #Subordinates have primary permissions to read this file, otherwise they will lose permissions
  chmod 600 /etc/rsyncd.pass  

Modify/etc/xinetd.d/rsync file, 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 Server

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

 

Client/file sender configuration for rsycn

Client configuration is simple and requires only a password to configure both

vi /etc/rsync_client.pwd
##Code
root #Just fill in the password for the rsync service
#Subordinates have primary permissions to read this file, otherwise they will lose permissions
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 synchronization is required another tool will need to be introduced

inotify
Inotify is a powerful, fine-grained, asynchronous file system event monitoring mechanism. The linux kernel has joined Inotify support since 2.6.13. Inotify can monitor all kinds of minor events in the file system, such as adding, deleting, modifying, moving, etc. With this kernel interface, third-party software can monitor files.Various changes to the files under the system, and inotify-tools is such a third-party software.

Inotify only needs to synchronize with the rsync script when the monitored file changes as deployed on the synchronized client

install

yum install inotify-tools

Configure the file path for monitoring

vi /etc/inotify_exclude.lst
#Code
/bak/mysqlbak #Monitoring Directory
@/bak/log #Exclude monitoring directories

rsync Excludes Monitoring File Directories

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

Client synchronization to remote script 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 &

Give the script permission to execute, then it's ready

chmod 777 rsync.sh
./rsync.sh 

Author: Pure smile
Source: http://www.ityouknow.com/
Copyright, please retain the original link for reproducing:)