MySQL full backup and Incremental Backup

Reference blog: http://lhflinux.blog.51cto.com/1961662/506467

Thank you very much for sharing this information!

The following is the script I modified based on the above blog!

1. Complete backup
VI full_mysqldump.sh
#! /Bin/sh
# Test by XXXXX
# Time info
Time = 'date + % Y % m % d % H'
Time1 = 'date + % Y % m % d \ % H: % m: % s'
# Dir_info
Back_dir = "/backup_db/MySQL"
File_dir = "/backup_db/MySQL"
# Mysql_back
Log =/backup_db/mysqlback. Log
Echo "$ time1 start to full backup"> $ log
Mysqldump-uroot -- flush-logs -- master-Data = 2 zabbix | gzip> $ back_dir/mysqlfull1_time. SQL .gz
Time2 = 'date + % Y % m % d \ % H: % m: % s'
Echo "$ time2 full backup successfully" >>$ log
Spendtime = $ (date + % s-d "$ time2")-$ (date + % s-d "$ time1 ")))
Echo "backup spend total time: $ spendtime seconds" >>$ log
# Get postion
Sleep 5S
Gunzip <$ back_dir/mysqlfull1_time. SQL .gz | sed-n "22 p" | awk-f '= |, |; ''{print $2, $4}'> $ file_dir/position
# Remove 7 days ago backup
Find $ back_dir-name "MySQL *. SQL .gz"-mtime + 7-exec RM {}\;
Echo "remove 7 days ago backup! ">>> $ Log

2. Perform Incremental backup. Perform Incremental Backup Based on the position obtained from the complete backup and the current position and binlog of the binlog server,
After the backup, save the current position and BINLOG to the position file, and retain the starting point of the next Incremental backup.
 
VI zl_backup.sh
#! /Bin/bash
#
# Test by xxxxxx
#
Date = 'date + % Y % m % d'
Olddate = 'date -- date = '7 Days ago '+ % Y % m % d'
Data_dir =/var/lib/MySQL
File_dir =/backup_db/MySQL
Backup_dir =/backup_db/mysqlincr
Backupsql = $ backup_dir/mysql_zlback _ $ date. SQL
Log =/backup_db/mysqlback. Log
Startbinlog = 'awk' {print $1} '$ file_dir/position'
Startposition = 'awk' {print $2} '$ file_dir/position'
Echo "Start BINLOG: $ startbinlog, start position: $ startposition" >>$ log
CD $ file_dir
Mysql-uroot-e "show Master Status \ G;" | awk '{print $2}'> nowposition
Stopbinlog = 'sed-n' 2P '$ file_dir/nowposition'
Stopposition = 'sed-n' 3p '$ file_dir/nowposition'
Echo "Stop BINLOG: $ stopbinlog, stop position: $ stopposition" >>$ log
Time1 = 'date + % Y % m % d \ % H: % m: % s'
Echo "$ time1 start to Incremental Backup"> $ log
If ["$ startbinlog" = "$ stopbinlog"]; then
Mysqlbinlog -- start-position = $ startposition -- stop-position = $ stopposition $ data_dir/$ startbinlog> $ backupsql
Else
Startline = 'awk "/$ startbinlog/{print Nr}" $ data_dir/mysqld-bin.index'
Stopline = 'wc-L $ data_dir/mysqld-bin.index | awk '{print $1 }''
For I in 'seq $ Startline $ stopline'
Do
BINLOG = 'sed-n "$ I" p $ data_dir/mysqld-bin.index | SED's/. \ // g''
Case "$ BINLOG" in
"$ Startbinlog ")
Mysqlbinlog -- start-position = $ startposition $ data_dir/$ BINLOG >>$ backupsql
;;
"$ Stopbinlog ")
Mysqlbinlog -- stop-position = $ stopposition $ data_dir/$ BINLOG> $ backupsql
;;
*)
Mysqlbinlog $ data_dir/$ BINLOG >>$ backupsql
Esac
Done
Fi

If [-F $ backupsql]; then
Gzip $ backupsql
Echo "$ stopbinlog $ stopposition"> $ file_dir/position
Rm-RF $ backup_dir/mysql_zlback_1_olddate.tar.gz
Time2 = 'date + % Y % m % d \ % H: % m: % s'
Spendtime = $ (date + % s-d "$ time2")-$ (date + % s-d "$ time1 ")))
Echo "$ time2 Incremental Backup finished! ">>> $ Log
Echo "Incremental Backup spend total time: $ spendtime seconds" >>$ log
Else
Echo "$ date Incremental Backup fail !!!! ">>> $ Log

3. Plan tasks well
Write the Incremental backup script and full backup script in the scheduled task according to the desired time, and perform regular Incremental backup.
Of course, you need to restore the data in sequence during restoration.