MySQL incremental backup with fully-prepared script

MySQL full-scale backup, incremental backup. Turn on MySQL's logbin log feature. Add the following code to the/etc/my.cnf file:

[Mysqld]
Log-bin = "/home/mysql/logbin.log"
Binlog-format = ROW
Log-bin-index = "/home/mysql/logindex"
binlog_cache_size=32m
max_binlog_cache_size=512m
max_binlog_size=512m

Restart MySQL. Users and groups with path/home/mysql are changed to MySQL.

2. Incremental backup
Create the following directory under the/home/mysql/directory:
Mkdir-p/home/mysql/backup/daily

Incremental backup Script
Cd/home/mysql

VI binlogbak.sh

#!/bin/bash
Export Lang=en_us. UTF-8
Bakdir=/home/mysql/backup/daily
Bindir=/home/mysql
Logfile=/home/mysql/backup/binlog.log
Binfile=/home/mysql/logindex.index
Mysqladmin-uroot-proot123 Flush-logs
#这个是用于产生新的mysql-bin.00000* File
Counter= ' wc-l $BinFile |awk ' {print '} '
Nextnum=0
#这个for循环用于比对 $Counter, $NextNum These two values to determine whether the file is present or up to date.
For file in ' Cat $BinFile '
Do
Base= ' basename $file '
#basename用于截取mysql-bin.00000* file name, remove the./mysql-bin.000005 front.
nextnum= ' expr $NextNum + 1 '
If [$NextNum-eq $Counter]
Then
Echo $base skip! >> $LogFile
Else
dest= $BakDir/$base
if (test-e $dest)
#test-E is used to detect if the target file exists, and it writes exist! to $logfile.
Then
Echo $base exist! >> $LogFile
Else
CP $BinDir/$base $BakDir
echo $base copying >> $LogFile
Fi
Fi
Done
Echo ' date + '%y year%M month%d day%h:%m:%s "' Bakup succ! >> $LogFile

Give binlogbak.sh Execute Permissions
chmod a+x/home/mysql/binlogbak.sh

3, full-scale backup
VI databak.sh

#!/bin/bash
Export Lang=en_us. UTF-8
Bakdir=/home/mysql/backup
Logfile=/home/mysql/backup/bak.log
Date= ' Date +%y%m%d '
begin= ' date + '%y year%M month%d day%h:%m:%s "'
CD $BakDir
dumpfile= $Date. sql
gzdumpfile= $Date. sql.tgz
mysqldump-uroot-proot123--all-databases--flush-logs--delete-master-logs--single-transaction > $DumpFile
TAR-CZVF $GZDumpFile $DumpFile
RM $DumpFile

count=$ (ls-l *.tgz |wc-l)
If [$count-ge 5]
Then
file=$ (ls-l *.tgz |awk ' {print $9} ' |awk ' Nr==1 ')
Rm-f $file
Fi
#只保留过去四周的数据库内容

last= ' date + '%y year%M month%d day%h:%m:%s "'
echo Start: $Begin end: $Last $GZDumpFile succ >> $LogFile
CD $BakDir/daily
Rm-f *

Give databak.sh Execute Permissions
chmod a+x/home/mysql/databak.sh

4. Start the scheduled task
Vi/etc/crontab

#每个星期日凌晨3:00 Perform a full backup script
0 3 * * 0/home/mysql/databak.sh >/dev/null 2>&1
#周一到周六凌晨3:00 do an incremental backup
0 3 * * 1-6/home/mysql/binlogbak.sh >/dev/null 2>&1

Make the above scheduled tasks effective
Crontab/etc/crontab

View Scheduled Tasks
Crontab-l

MySQL incremental backup with fully-prepared script