Centos7 implements MySQL LAN backup (including full backup and log incremental backup)

            Database backup operation is a very important step. For online systems, a database without backup is not safe. By consulting online information, this blog post realizes the full backup, incremental backup and scheduled backup operations in the LAN of the MySQL database through shell script files.

Server introduction: centos 7.0

[Local full backup]

1. Single database backup script file

#vi mysql-backup.sh

##

db_user="root"     #本服务器用户名密码
db_passwd="root"  
db_host="192.168.64.137"   #本服务器地址
db_name="whp"  #需要备份数据库名称
# the directory for story your backup file.  
backup_dir="/backup"    #备份以后放入的文件路径
# date format for backup file (dd-mm-yyyy)  
time="$(date +"%Y-%m-%d-%H-%M-%S")"  
# mysql, mysqldump and some other bin's path  
MYSQL="/application/mysql/bin/mysql"  
MYSQLDUMP="/application/mysql/bin/mysqldump"  
GZIP="/bin/gzip"  
  
$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db_name | $GZIP -9 > "$backup_dir/$db_name"_"$time.gz"

2. Keep 7 days of historical data Full backup

db_user="root"  
db_passwd="root"  
db_host="localhost"   
# the directory for story your backup file.  
backup_dir="/application/backup/"  
# date format for backup file (dd-mm-yyyy)  
time="$(date +"%H-%M-%S-%m-%d-%y")"  
# mysql, mysqldump and some other bin's path  
MYSQL="/application/mysql/bin/mysql"  
MYSQLDUMP="/application/mysql/bin/mysqldump"  
MKDIR="/bin/mkdir"  
RM="/bin/rm"  
MV="/bin/mv"  
GZIP="/bin/gzip"  
# check the directory for store backup is writeable   
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 
# the directory for story the newest backup  
test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"  
echo "Start to Backup...";  
# get all databases  
# don't backup information_schema、performance_schema  
all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"  
all_db=${all_db//information_schema/};  
all_db=${all_db//performance_schema/};  
for db in $all_db  
do  
$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"  
done  
# delete the oldest backup  
test -d "$backup_dir/backup.7/" && $RM -rf "$backup_dir/backup.7"  
# rotate backup directory  
for int in 6 5 4 3 2 1 0  
do  
if(test -d "$backup_dir"/backup."$int")  
then  
next_int=`expr $int + 1`  
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"  
fi  
done  
echo "BackUp Success!"  
exit 0;

3. Modify the shell script permissions and execute

       # chmod 700 mysql-backup.sh  // 只允许管理员运行此脚本
           #./mysql-backup.sh                //执行脚本，测试一次

【Incremental backup of log files】

1. Create script file:

#vi incre-backup.sh

#执行mysqladmin执行刷新日志文件         
/application/mysql/bin/mysqladmin -uroot -proot flush-logs   
         
#DATADIR=/var/lib/mysql   
DATADIR=/application/data  #获取数据文件路径 
BAKDIR=/backup    #获取要备份的数据目标文件路径
         
###如果mysql bin log你做了特殊设置,请修改此处或者修改应用此变量的行:缺省取机器名,mysql缺省也是取机器名   
#HOSTNAME=`uname -n`   
cd $DATADIR   #转入到/application/data 查询mysql-bin.index文件
#FILELIST=`cat $HOSTNAME-bin.index`   
 FILELIST=`cat mysql-bin.index`   
 
## COUNTER number   
COUNTER=0   
for file in $FILELIST   
do   
COUNTER=`expr $COUNTER + 1 `   
done   
         
NextNum=0   
for file in  $FILELIST   
do   
base=`basename $file`   
NextNum=`expr $NextNum + 1`   
if [ $NextNum -eq $COUNTER ]   
then
echo "skip lastest"
else
dest=$BAKDIR/$base   
if(test -e $dest)   
then
echo "skip exist $base"
else
echo "copying $base"
cp $base $BAKDIR   
fi   
fi   
done   
         
echo "backup mysql binlog ok"

2. Modify file permissions and execute

# chmod 700 incre-backup.sh  // 只允许管理员运行此脚本
  #./mysql-backup.sh                //执行脚本，测试一次

[Automatic backup]

Automatic backup is based on the above scripting, using the Linux system

crontab command, automatically execute the shell file regularly:

 # crontab –e
添加：
00 01 * * */mysql-backup.sh    // 每天凌晨1点执行

[Remote LAN backup]

LAN backup uses the NFS server to realize data sharing on the LAN to achieve backup. The NFS server mounting method is implemented just like we usually share files by configuring the network connection to allow our files to be shared. The implementation method is as follows:

1. NFS server-side installation

a) Install the software packages required for the NFS server:

  # yuminstall -y nfs-utils
 
           b)编辑exports文件
           #vim/etc/exports
           #/home/nfs/192.168.248.0/24(rw,sync)

A host with the same network number as 192.168.248.0/24 can mount the /home/nfs/ directory on the NFS server to its own file system

rw means readable and writable; sync means synchronization Write

c) Start nfs service

Confirm that the NFS server starts successfully:

#rpcinfo –p

e) If it does not start successfully, restart the rpcbind and nfs-server services:

                                                                                                                                                                                             ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ​​,,

A) Install NFS and start the RPCBIND service

##yuminstall -y nfs -Utils

B) #systemctlenable rpcbind.service

c) Then start the rpcbind service:

systemctlstart rpcbind.service

d) Check NFS Whether the server side has a shared directory:

#Showmount -E 192.168.64.138

## E) Use Mount Mount the server to the client on the machine to a directory of the client on the machine Next (here the file path of

local backup

above is mounted in the server):

        #mount -tnfs -o nolock,nfsvers=1,vers=3 192.168.64.138:/home /nfs /backup

Problem

: mount.nfs: Stale NFS file handle

Solution: Remove the mount file on the client side and re- Mount:

# umount –a

[Backup and restore]

a. Restore the gzip database to Mysq

#gunzip

b. Binary file recovery database:

#/mysql/bin/mysqlbinlog--database=fox --start-date="2013-01-22 5:00:00" --stop-date="2013-01-22 9:00:00 "/mysql/data/mysql-bin.000001 | /mysql/bin/mysql -u root -p123456 –f

database: Specify the specific database

start-date: start time

Stop-date: end time

/mysql/data/mysql-bin.000001: Specify binary file

[Summary]

The basic backup method has been Complete, but we need to adopt different backup strategies according to specific application scenarios to ensure security without consuming too much memory.

The above is the content of Centos7 implementing MySQL LAN backup (including full backup, log incremental backup). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!