Mysqlmysqldump data backup and incremental backup mysqldump
BitsCN.com this article mainly describes how to use shell to implement full mysql and incremental backup. Incremental Backup copies the mysql-bin.00000 * to the specified directory at Monday-sat, while full backup uses mysqldump to export all the databases at every Sunday, and will delete the last week left by the mysq-bin.00000 *. Then, the mysql backup operations will be kept in the bak. log file. For example:
Start: May 02, 2013 15:10:57 end: May 02, 2013 15:12:16 20130502. SQL. tgz succ is generated by DBFullyBak. sh, backed up once a week; mysql-bin.000001 copying; mysql-bin.000002 skip !; May 02, 2013 16:53:15 Bakup succ! It is generated by DBDailyBak. sh once a day.
Implementation:
1. write a full backup script
# Vim/root/DBFullyBak. sh // add the following content
#! /Bin/bash
# Program
# Use mysqldump to Fully backup mysql data per week!
# History
#2013-04-27 guo first
# Path
#....
BakDir =/home/mysql/backup
LogFile =/home/mysql/backup/bak. log
Date = 'date + % Y % m % d'
Begin = 'date + "% Y % m month % d % H: % M: % S "'
Cd $ BakDir
DumpFile = $ Date. SQL
GZDumpFile = $ Date. SQL. tgz
/Usr/local/mysql/bin/mysqldump-uroot-p123456 -- quick -- all-databases -- flush-logs -- delete-master-logs -- single-transaction> $ DumpFile
/Bin/tar czvf $ GZDumpFile $ DumpFile
/Bin/rm $ DumpFile
Last = 'date + "% Y % m month % d % H: % M: % S "'
Echo start: $ Begin end: $ Last $ GZDumpFile succ >>$ LogFile
Cd $ BakDir/daily
Rm-f *
2. write an incremental backup script
# Cat/root/DBDailyBak. sh // The content is as follows:
#! /Bin/bash
# Program
# Use cp to backup mysql data everyday!
# History
#2013-05-02 guo first
# Path
#....
BakDir =/home/mysql/backup/daily
BinDir =/home/mysql/data
LogFile =/home/mysql/backup/bak. log
BinFile =/home/mysql/data/mysql-bin.index
/Usr/local/mysql/bin/mysqladmin-uroot-p123456 flush-logs
# This is used to generate new mysql-bin.00000 * files
Counter = 'WC-l $ BinFile | awk '{print $1 }''
NextNum = 0
# This for loop is used to compare the values $ Counter and $ NextNum to determine whether the file exists or is up-to-date.
For file in 'cat $ binfile'
Do
Base = 'basename $ file'
# Basename is used to capture the mysql-bin.00000 * file name, remove the./mysql-bin.000005 before ./
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
Fi
Done
Echo 'date + "% Y % m month % d % H: % M: % S" '$ Next Bakup succ! >>$ LogFile
3. set the crontab task and execute the backup script every day.
# Crontab-l // The content is
# Execute the full backup script at every Sunday.
0 3 ** 0/root/DBFullyBak. sh>/dev/null 2> & 1
# Perform incremental backup at from Monday to Saturday
0 3 ** 1-6/root/DBDailyBak. sh>/dev/null 2> & 1
Appendix:
Sh-n/root/DBFullyBak. sh can be used to check whether the shell syntax is correct.
BitsCN.com
