The idea and method of Mysql database Incremental backup, mysql incremental

The idea and method of Mysql database Incremental backup, mysql incremental

MySQL database Incremental backup. Before that, modify our database configuration file/etc/my. cnf to enable the bin-log function. Next, I have referenced some methods on the Internet and wrote them myself. I still want to learn some of his ideas and methods.

# Function: MYSQL Incremental Backup # version: 1.0.0 # author: wangyanlin # date: Latest /08/02 # latest #-----------------------------------------------------------------------------------------------#! /Bin/shexport LANG = en_US.UTF-8 # Set Time DATE = 'date + % Y % m % d' # Set information USER = rootPASSWORD = withub # Set path cd // usr/bin/ mkdir-p daily/Daily/usr/bin/mkdir-p mysql_bak/logsBakDir =/mysql_bak/dailyBinDir =/var/lib/mysqlLogFile =/mysql_bak/logs/daily _ $ DATE. logBinlogFile =/var/lib/mysql/mysql-bin.index/usr/bin/mysqladmin-u $ USER-p $ PASSWORD flush-logs # refresh log Counter = 'wc-l $ BinlogFile | awk '{print $1}' NextNum = 0start_ti Me = 'date + '% Y-% m-% d % H: % M: % s' 'echo 'date + "% Y % m month % d % H: % M: % S "'$ Next1 Bakup start! >>$ LogFile # This for loop is used to compare the values of $ Counter and $ NextNum to determine whether the file exists or is up-to-date. For file in 'cat $ binlogfile' do base = 'basename $ file' # basename is used to intercept the mysql-bin.00000 * file name, remove. /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 check whether the target file exists. if yes, write exist! Go to $ LogFile. Then echo $ base exist! >>$ LogFile else cp $ BinDir/$ base $ BakDir echo $ base copying >>$ LogFile fi fidoneecho 'date + "% Y % m % d % H: % M: % S "'$ Next2 Bakup succ! >>$ LogFileend_time = 'date + '% Y-% m-% d % H: % M: % S 'start _ seconds =$ (date -- date = "$ start_time" + % s); end_seconds =$ (date -- date = "$ end_time" + % s ); echo "This backup run time:" $ (end_seconds-start_seconds) "s" >>$ LogFile

Add a scheduled task:

Crontab-e

00 03 ***/root/MySQL_incrementbak.sh # Incremental Backup day starts at every day

Effect of logs log printing:

PS: Let's take a look at the mysql full and Incremental Backup scripts.

Full:

[Root @ master leo] # cat DBfullBak. sh #! /Bin/bash # use mysqldump to fully backup mysql dataBakDir =/root/leo/fullLogFile =/root/leo/full/bak. logDate = 'date + % Y % m % d' Begin = 'date + "% Y % m month % dday % H: % M: % S "'CD $ BakDirDumpFile = $ Date. sqlGZDumpFile = $ Date. SQL. tests-uroot-p123456 -- all-databases -- lock-all-tables -- routines -- triggers -- events -- master-data = 2 -- flush-logs> $ DumpFiletar zcvf $ GZDumpFile $ DumpFilerm $ dumpFileLast = 'date + "% Y % m % d % H: % M: % S "'echo start: $ Begin end: $ Last $ GZDumpFile succ >>$ LogFile

Parameter notes:

-- All-databases # Back up all databases -- lock-all-tables # apply a read lock to all tables -- routinge # stored procedures and functions -- triggers # Trigger -- events # record events -- master-data = 2 # record the location of the current binary log in the backup file, and it is annotated. 1 is meaningful only when the comment is not commented out in master-slave replication -- flush-logs # the log is rolled once.

The result is as follows:

[Root @ master full] # ls20140728. SQL. tgz bak. log [root @ master full] # cat bak. log start: July 28, 2014 19:02:59 end: July 28, 2014 19:02:59 20140728. SQL. tgz succ start: July 28, 2014 19:12:01 end: July 28, 2014 19:12:01 20140728. SQL. tgz succ [root @ master full] #

Incremental Backup:

[Root @ master leo] # cat DBDailyBak. sh #! /Bin/basw.kdir =/root/leo/binlog/BinDir =/var/lib/mysqlLogFile =/root/leo/binlog/bak. logBinFile =/var/lib/mysql/mysql-bin.indexmysqladmin-uroot-p123456 flush-logsCounter = 'wc-l $ BinFile | awk '{print $1} ''NextNum = 0for file in' cat $ BinFile 'do base = 'basename $ file' NextNum = 'expr $ NextNum + 1' if [$ NextNum-eq $ Counter] then echo $ base skip! >>$ LogFile else dest = $ BakDir/$ base if (test-e $ dest) then echo $ base exist! >>$ LogFile else cp $ BinDir/$ base $ BakDir/echo $ base copying >>$ LogFile fi fidoneecho 'date + "% Y % m % d % H: % M: % S "'$ Next Bakup succ ~ >>$ LogFile

Summary

The above section describes the ideas and methods for Incremental backup of Mysql databases. I hope this will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!