mysql的全量備份與增量備份

全量備份：可以使用mysqldump直接備份整個庫或者是備份其中某一個庫或者一個庫中的某個表。

增量備份：增量備份是針對于數據庫的bin-log日志進行備份的，需要開始數據庫的bin-log日志。增量備份是在全量的基礎上進行操作的。增量備份主要是靠mysql記錄的bin-log日志。（可以把二進制日志保存成每天的一個文件）。

生產環境：
建議一周或者三天進行一次全量備份，一天一次增量備份。

?

首先配置下mysql增量備份：

server-id ? ? ? ? ? ? ?= 1
log_bin ? ? ? ? ? ? ? ?= /var/log/mysql/mysql-bin.log
binlog-ignore-db ? ? ? = sys, mysql, information_schema, performance_schema
#設置日志格式
binlog_format = mixed

#設置日志路徑，注意路經需要mysql用戶有權限寫
log-bin = /data/mysql/logs/mysql-bin.log

#設置binlog清理時間
expire_logs_days = 7
#binlog每個日志文件大小
max_binlog_size = 100m

#binlog緩存大小
binlog_cache_size = 4m

#最大binlog緩存大小
max_binlog_cache_size = 512m

innodb_buffer_pool_size＝64m
innodb_thread_concurrency = 8

?

?

1.MySQLdump增量備份
假定星期日下午1點執行全量備份，適用于MyISAM存儲引擎。
[root@test-huanqiu ~]# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup_sunday_1_PM.sql

對于InnoDB將--lock-all-tables替換為--single-transaction
--flush-logs為結束當前日志，生成新日志文件；
--master-data=2 選項將會在輸出SQL中記錄下完全備份后新日志文件的名稱，

用于日后恢復時參考，例如輸出的備份SQL文件中含有：
CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;

2.MySQLdump增量備份其他說明：
如果MySQLdump加上–delete-master-logs 則清除以前的日志，以釋放空間。但是如果服務器配置為鏡像的復制主服務器，用MySQLdump –delete-master-logs刪掉MySQL二進制日志很危險，因為從服務器可能還沒有完全處理該二進制日志的內容。在這種情況下，使用 PURGE MASTER LOGS更為安全。

每日定時使用 MySQLadmin flush-logs來創建新日志，并結束前一日志寫入過程。并把前一日志備份，例如上例中開始保存數據目錄下的日志文件 MySQL-bin.000002 , ...

1.恢復完全備份
mysql -u root -p < backup_sunday_1_PM.sql

2.恢復增量備份
mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p注意此次恢復過程亦會寫入日志文件，如果數據量很大，建議先關閉日志功能

結合Linux的cron命令實現定時備份
比如需要在每天凌晨1:30備份某個主機上的所有數據庫并壓縮dump文件為gz格式
30 1 * * * mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

mysqldump全量備份+mysqlbinlog二進制日志增量備份
1）從mysqldump備份文件恢復數據會丟失掉從備份點開始的更新數據，所以還需要結合mysqlbinlog二進制日志增量備份。
首先確保已開啟binlog日志功能。在my.cnf中包含下面的配置以啟用二進制日志：
[mysqld]
log-bin=mysql-bin

2）mysqldump命令必須帶上--flush-logs選項以生成新的二進制日志文件：
mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql
其中參數--master-data=[0|1|2]
0: 不記錄
1：記錄為CHANGE MASTER語句
2：記錄為注釋的CHANGE MASTER語句

?

下面分享一下自己用過的mysqldump全量和增量備份腳本

應用場景：
1）增量備份在周一到周六凌晨3點，會復制mysql-bin.00000*到指定目錄；
2）全量備份則使用mysqldump將所有的數據庫導出，每周日凌晨3點執行，并會刪除上周留下的mysq-bin.00000*，然后對mysql的備份操作會保留在bak.log文件中。

腳本實現：
1）全量備份腳本（假設mysql登錄密碼為123456；注意腳本中的命令路徑）：

#!/bin/bash # Program # use mysqldump to Fully backup mysql data per week! # History # Path today=`date +%Y%m%d` whichday=`date -d $today +%w` monday=`date -d "$today -$[${whichday}-1] days" +%Y%m%d` Date=$monday BakDir=/home/mysql/backup/$Date LogFile=/home/mysql/backup/bak.log Begin=`date +"%Y年%m月%d日 %H:%M:%S"` if [ ! -d $BakDir ];thenmkdir -p $BakDir && mkdir -p $BakDir/daily fi cd $BakDir DumpFile=$Date.sql GZDumpFile=$Date.sql.tgz mysqldump -uroot -phello123456 --quick --events --databases gaoke --flush-logs --delete-master-logs --single-transaction > $DumpFile /bin/tar -zvcf $GZDumpFile $DumpFile /bin/rm $DumpFile Last=`date +"%Y年%m月%d日 %H:%M:%S"` echo 開始全量備份:$Begin 結束:$Last $GZDumpFile SUCC >> $LogFile cd $BakDir/daily /bin/rm -f *

如果備份多個數據庫那么：

mysqldump -u root -p --databases mysql stady > /opt/mysql-stady.sql

2）增量備份腳本（腳本中mysql的數據存放路徑是/home/mysql/data，具體根據自己的實際情況進行調整）

#!/bin/bash # Program # use cp to backup mysql data everyday! # History # Path today=`date +%Y%m%d` whichday=`date -d $today +%w` monday=`date -d "$today -$[${whichday}-1] days" +%Y%m%d` Date=$monday BakDir=/home/mysql/backup/$Date/daily # //增量備份時復制mysql-bin.00000*的目標目錄，提前手動創建這個目錄 BinDir=/var/lib/mysql # //mysql的數據目錄 LogFile=/home/mysql/backup/bak.log BinFile=$BinDir/mysql-bin.index # //mysql的index文件路徑，放在數據目錄下的 mysqladmin -uroot -phello123456 flush-logs #這個是用于產生新的mysql-bin.00000*文件 Counter=`wc -l $BinFile |awk '{print $1}'` NextNum=0 #這個for循環用于比對$Counter,$NextNum這兩個值來確定文件是不是存在或最新的 for file in `cat $BinFile` dobase=`basename $file`#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./NextNum=`expr $NextNum + 1`if [ $NextNum -eq $Counter ]thenecho $base skip! >> $LogFileelsedest=$BakDir/$baseif(test -e $dest)#test -e用于檢測目標文件是否存在，存在就寫exist!到$LogFile去thenecho $base exist! >> $LogFileelseecho $base copying >> $LogFilecp $BinDir/$base $BakDirecho `date +"%Y年%m月%d日 %H:%M:%S"` $base 新的增量備份 Bakup succ! >> $LogFilefifi done

3）設置crontab任務，執行備份腳本。先執行的是增量備份腳本，然后執行的是全量備份腳本：

[root@test-huanqiu ~]# crontab -e #每個星期1凌晨3:00執行完全備份腳本 0 3 * * 1 /bin/bash -x /root/mysqlbackup/full_backup.sh >/dev/null 2>&1 #周2-7凌晨3:00做增量備份 0 3 * * 2-7 /bin/bash -x /root/mysqlbackup/binlog_backup.sh >/dev/null 2>&1

4）手動執行上面兩個腳本，測試下備份效果

[root@test-huanqiu backup]# pwd /home/mysql/backup [root@test-huanqiu backup]# mkdir daily [root@test-huanqiu backup]# ll total 4 drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily [root@test-huanqiu backup]# ll daily/ total 0 先執行增量備份腳本 [root@test-huanqiu backup]# sh /root/Mysql-DailyBak.sh [root@test-huanqiu backup]# ll total 8 -rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily [root@test-huanqiu backup]# ll daily/ total 8 -rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030 -rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031 [root@test-huanqiu backup]# cat bak.log mysql-binlog.000030 copying mysql-binlog.000031 copying mysql-binlog.000032 skip! 2016年11月29日 11:29:32 Bakup succ! 然后執行全量備份腳本 [root@test-huanqiu backup]# sh /root/Mysql-FullyBak.sh 20161129.sql [root@test-huanqiu backup]# ll total 152 -rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz -rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily [root@test-huanqiu backup]# ll daily/ total 0 [root@test-huanqiu backup]# cat bak.log mysql-binlog.000030 copying mysql-binlog.000031 copying mysql-binlog.000032 skip! 2016年11月29日 11:29:32 Bakup succ! 開始:2016年11月29日 11:30:38 結束:2016年11月29日 11:30:38 20161129.sql.tgz succ

5.增量備份恢復：

mysqlbinlog --no-defaults --start-datetime='18-07-03 21:56:11' --stop-datetime='18-07-03 21:56:04' mysql-bin.000003 | mysql -u root -p #結束節點

?

總結

以上是生活随笔為你收集整理的MYSQL增量备份和全量备份脚本分享的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。