Centos7 implements MySQL LAN backup (including full backup and log incremental backup)
- 黄舟Original
- 2017-02-28 13:20:081571browse
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]
# 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
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 backupabove 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)!