淺談使用Binlog實現MySQL增量備份

這裡也只是深入淺出的描述一下增備的流程，實際生活中往往要比這個案例殘酷的多。那麼我們又該如何選擇備份方案呢？

1， 按天備份

週一 00:00 全備數據

週二 00:00 全備數據

26_01.sql.gz

26_02.sql.gz週一增備

週二增備

binlog.000022

binlog.000023

binlog.000024

......

binlog.000033

binlog.000034

binlog.000035

......

這樣做的好處，顯然是恢復時間短，維護成本低，同樣缺點也很明顯，就是佔用資源多，而且需要頻繁鎖表，影響用戶的使用體驗

2， 按周備份

週六00:00 全備

26_01.sql.gz

週一增備

週二增備週三增備...binlog.000022

binlog.000023

binlog.000024

......

binlog.000032

binlog.000033

binlog.000034

......

binlog.000042

binlog.000043

binlog.000044

......

......

這麼做的優缺點則剛好和上面案例相反，優點是佔用資源少，不頻繁鎖表，用戶體驗相對好一些，不過代價就是維護成本較高，如果數據出現問題，恢復時間較長。

綜上所述，這是一個非常尷尬的問題，造成這個問題的原因即複雜又簡單。說簡單，是因為可以高度概括為資源不足，四個字。但是細扣下來，就變成時間、空間、成本、智力投入等諸多因素的博弈問題了

全備份

mysqldump -B test -lF -uroot-pdafei1288 > test.sql

參數 --lock-all-tables

對於InnoDB將替換為 --single-transaction。

該選項在導出數據之前提交一個 BEGIN SQL語句，BEGIN不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用於事務表，例如 InnoDB 和 BDB。本選項和 --lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何掛起的事務隱含提交。要想導出大表的話，應結合使用--quick 選項。

參數 --flush-logs，結束當前日誌，生成並使用新日誌文件

參數 --master-data=2，該選項將會在輸出SQL中記錄下完全備份後新日誌文件的名稱，用於日後恢復時參考，例如輸出的備份SQL文件中含有：CHANGE MASTER TOMASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=106;

參數 test，該處的test表示數據庫test，如果想要將所有的數據庫備份，可以換成參數 --all-databases

參數 --databases 指定多個數據庫

參數 --quick或-q，該選項在導出大表時很有用，它強制 MySQLdump 從服務器查詢取得記錄直接輸出而不是取得所有記錄後將它們緩存到內存中。

參數 --ignore-table，忽略某個數據表，如 --ignore-tabletest.user 忽略數據庫test裡的user表

-lF，注意必須大寫F,當備份工作剛開始時系統會刷新log日誌，產生新的binlog日誌來記錄備份之後的數據庫“增刪改”操作。

全恢復

mysql -uroot -pdafei1288 <test.sql>

恢復指定庫

mysql -uroot -pdafei1288 test1< test1.sql

增備

環境配置

檢查是否開始 binlog

show variables like'%log_bin%';

表示沒開啟，在my.inf主配置文件中直接添加三行

log_bin=ON

log_bin_basename=

log_bin_index=

重啟mysql，

表示已開啟。

show binary logs;

查看binlog文件列表

增備

show master status;

當前正在記錄日誌的文件名是 binlog.000003

mysqladmin -uroot -pdafei1288flush-logs

生成並使用新的日誌文件，再重新查看日誌文件列表

恢復

mysqlbinlog --no-defaults--start-position=1082 binlog.000001 | mysql -uroot -pdafei1288

命令列表

mysqldump -B test -lF -uroot-pdafei1288 > test.sql

mysql -uroot -pdafei1288 <test.sql>

show variables like'%log_bin%';

show binary logs;

show master status;

mysqladmin -uroot -pdafei1288 flush-logs

mysqlbinlog --no-defaults--start-position=1082 binlog.000001 | mysql -uroot -pdafei1288

https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html

https://www.cnblogs.com/wangke2017/p/9745183.html

---

分享到:

---

閱讀更多 麒思秒想 的文章

關鍵字: SQL 增量 數據庫

---