mysql如何實現備份策略

這篇文章將為大家詳細講解有關mysql如何實現備份策略，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

設計場景

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

技術點

Mysqldump、mysqlbinlog、crontab

服務器信息

主機：centos7；數據庫：mysql5.7

準備工作

開啟binlog日志功能
（1）新建目錄，執行：

#mkdir /home/mysql
#cd /home/mysql
#mkdir mysql-bin.      #增量日志文件目錄

（2）修改所屬的用戶/組：（不修改，mysql無法重啟）

#chown -R mysql.mysql mysql-bin

（3）修改mysql配置文件，執行：

#vim /etc/my.cnf

其中，server-id表示單個結點的id，這里由于只有一個結點，所以可以把id隨機指定為一個數，這里將id設置成1。若集群中有多個結點，則id不能相同(對于5.7以下版本不需要指定server-id)；
log_bin指定binlog日志文件的存儲路徑，日志文件以mysql-bin開頭。

（4）重啟mysql，執行：

#systemctl restart mysqld.service

（5）查看日志文件：

#cd /home/mysql/mysql-bin

（6）進入數據庫，查看啟動效果:

#show variables like '%log_bin%';

編寫全量備份腳本(Mysql-FullyBak.sh)

進入/home/mysql目錄
新建目錄：mkdir backup
進入backup目錄，新建daily目錄：mkdir backup
切換到/home/mysql目錄，執行：

#vim Mysql-FullyBak.sh

參數說明：
–lock-tables
鎖定當前導出的數據表，而不是一下子鎖定全部庫下的表。本選項只適用于MySQL數據庫引擎為MyISAM 表，如果是 Innodb 表可以用 –single-transaction 選項。
–flush-logs
結束當前日志，生成新日志文件。
–delete-master-logs
清除以前的日志，以釋放空間。但是如果服務器配置為鏡像的復制主服務器，用–delete-master-logs刪掉MySQL二進制日志很危險，因為從服務器可能還沒有完全處理該二進制日志的內容。在這種情況下，使用 PURGE MASTER LOGS更為安全。
–quick
該選項在導出大表時很有用，它強制 MySQLdump 從服務器查詢取得記錄直接輸出而不是取得所有記錄后將它們緩存到內存中。
–single-transaction
該選項在導出數據之前提交一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用于事務表，例如 InnoDB 和 BDB。本選項和 –lock-tables 選項是互斥的，因為lock-tables會使任何掛起的事務隱含提交。要想導出大表的話，應結合使用 –quick 選項。
–events
導出事件
–master-data=2
其中參數–master-data=[0|1|2]
0: 不記錄
1：記錄為CHANGE MASTER語句
2：記錄為注釋的CHANGE MASTER語句
–master-data=2 選項將會在輸出SQL中記錄下完全備份后新日志文件的名稱，
用于日后恢復時參考，例如輸出的備份SQL文件中含有：
CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002′, MASTER_LOG_POS=106;

編寫增量備份腳本

切換到/home/mysql目錄，執行：

#vim Mysql-DailyBak.sh

設置定時任務crontab

（1）安裝crontab（centos7默認已經安裝）：

#yum install crontabs

服務操作說明：

#/bin/systemctl start crond.service //啟動服務
#/bin/systemctl stop crond.service //關閉服務
#/bin/systemctl restart crond.service //重啟服務
#/bin/systemctl reload crond.service //重新載入

配置:

#/bin/systemctl status crond.service //服務狀態

加入開機自動啟動：

#chkconfig –level 35 crond on

（2）在命令行輸入:

#crontab -e

添加相應的任務，wq存盤退出

#每個星期日凌晨3:00執行完全備份腳本
0 3 * * 0 /bin/bash -x /home/mysql/Mysql-FullyBak.sh >/dev/null 2>&1
#周一到周六凌晨3:00做增量備份
0 3 * * 1-6 /bin/bash -x /home/mysql/Mysql-DailyBak.sh >/dev/null 2>&1

說明：默認情況下，crontab執行一次任務后，會通過email通知用戶，為避免每次發信息，加入/dev/null 2>&1

（3）查看定時任務：#crontab -l

參數與說明:
crontab -u //設定某個用戶的cron服務，一般root用戶在執行這個命令的時候需要此參數 ;
　　crontab -l //列出某個用戶cron服務的詳細內容;
　　crontab -r //刪除所有用戶的cron服務;
　　crontab -e //編輯某個用戶的cron服務;
　　例如：root查看自己的cron設置:crontab -u root -l
　　例如：root刪除用戶fred的cron設置:crontab -u fred -r
補充：
（1）可直接編輯/etc/crontab 文件，即vi /etc/crontab，添加相應的任務（針對整個系統的crontab文件）;
（2）crontab執行定時任務的記錄會寫入到/var/log/cron這個文件中，該記錄以帳號為區分。

恢復操作

恢復過程亦會寫入日志文件，如果數據量很大，建議先關閉binlog日志功能
1、場景：假設早上9點的時候，數據庫被攻擊，drop了整個數據庫！
2、恢復思路：
利用全備的sql文件中記錄的CHANGE MASTER語句，binlog文件及其位置點信息，找出binlog文件中增量的那部分。
用mysqlbinlog命令將上述的binlog文件導出為sql文件，并剔除其中的drop語句。
通過全備文件和增量binlog文件導出的sql文件，就可以恢復到完整的數據。
3、恢復步驟：

（1）首先，解壓最新的全量備份文件，進入備份文件目錄，執行：

#tar -zxvf XXX.sql.tgz

(2)查看全備之后新增的binlog文件，執行：

#grep CHANGE XXX.sql

由圖可知，這是全備時刻的binlog文件位置，即mysql-bin.000003的154行，因此在該文件之前的binlog文件中的數據都已經包含在這個全備的sql文件中。

(3)恢復mysql-bin.000003文件的154行之后的信息

進入到mysql-bin.000003目錄，執行(sysecokit為數據庫名);

#mysqlbinlog --start-position=154 --database=sysecokit mysql-bin.000003 | mysql -uroot -p -v sysecokit

（4）將其他binlog文件（除去mysql-bin.000003）導出sql文件，執行(-d指定數據庫)：

#mysqlbinlog -d sysecokit mysql-bin.00000X >00Xbin.sql

(5) vim編輯最新的00Xbin.sql刪除其中的drop語句
(6)恢復全備數據，執行：

#mysql -uroot -p < XXX.sql

如：#mysql -uroot -p < 20180716.sql
(7)恢復增量數據，執行（syseco為數據庫名稱）：

#mysql -uroot -p syseco<00Xbin.sql

如：#mysql -uroot -p syseco<004bin.sql
自此，已經完成所有工作，讓我們查看一下運行一周后產生的文件：

關于“mysql如何實現備份策略”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。