mysql binlog 簡單介紹與基于binlog數(shù)據(jù)恢復

通過備份文件恢復

binlog(本節(jié)重點)

binlog 二進制日志文件

show variables like 'log_bin';

二進制日志文件(binary log) 記錄了對mysql 數(shù)據(jù)庫執(zhí)行更改的所有操作, 但是不包括 select 和 show 這類操作,

因為這類操作對數(shù)據(jù)本身沒有修改. 然而 操作本身沒有對數(shù)據(jù)進行修改也可能會記錄二進制日志,

binlog文件位置可以通過查詢得到

mysql> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

其產(chǎn)生的binlog 文江將在 該目錄下存儲

通過查詢可得到當前mysql服務(wù)正在使用的 是哪個文件

mysql> show master status\G

*************************** 1. row ***************************

File: binlog.000055

Position: 1065

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

我們來看一下該路徑下都有啥

# pwd

/var/lib/mysql

# ls

'#innodb_temp' binlog.000055 client-key.pem ib_logfile0 mysql.ibd server-cert.pem undo_002

auto.cnf binlog.index credit_txp ib_logfile1 nh_merchant server-key.pem vcc

binlog.000051 bootdo demo ibdata1 performance_schema springbootv2

binlog.000052 ca-key.pem ec_common ibtmp1 private_key.pem sys

binlog.000053 ca.pem foo internationalization public_key.pem test

binlog.000054 client-cert.pem ib_buffer_pool mysql renren@002dfast undo_001

#

binlog文件一般是在mysql重啟時切換binlog文件, 也可以手動切換當前使用的binlog文件,例如我們當前使用的binlog文件是 "binlog.000055", 可以通過 flush logs 手動切換 binlog 文件

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: binlog.000056

Position: 155

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

可以看到mysql服務(wù)使用的binlog文件改為了 binlog.000056

看一下 binlog文件的配置參數(shù)

max_binlog_size

binlog_cache_size

sync_binlog

binlog-do-db

binlog-ignore-db

log-save-update

binlog_format

說明:

max_binlog_size 指定二進制文件最大值, 如果超過該值將會產(chǎn)生一個新的二進制文件,后綴名+1, 默認大小為1G

當使用innodb 存儲引擎時, 所有未提交的二進制日志將會記錄到一個緩存中, 等事務(wù)提價時將從緩存中數(shù)據(jù)寫入二進制文件, 而該緩存的大小有 binlog_cache_size 指定, 默認大小為32KB, 此外, 該緩沖是 基于session 的 , 即一個會話就會分配 一個指定大小的緩存, 因此不能設(shè)置過大, 但是當一個事務(wù)的記錄大于指定的緩存時, mysql會把緩存中的日志寫入一個臨時文件中, 因此該值還不能設(shè)置過小. 可以通過 show global status 查看binlog_cache_use(記錄使用緩沖寫入二進制文件次數(shù)) 、binlog_cache_disk_use(記錄使用臨時文件寫入二進制文件測試) 的狀態(tài) 來判斷是 binlog_cache_size 大小設(shè)置是否合適,

mysql> show variables like 'binlog_cache_size'; 大小默認32k

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| binlog_cache_size | 32768 |

+-------------------+-------+

1 row in set (0.01 sec)

mysql> show global status like 'binlog_cache%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 0 | 未使用過臨時文件寫入二進制文件

| Binlog_cache_use | 3 |

+-----------------------+-------+

2 rows in set (0.00 sec)

sync_binlog, 使用緩沖的來寫入二進制日志的時候, sync_binlog=N 即表示寫到緩沖N次即同步磁盤

binlog-do-db 與 binlog-ignore-db 表示需要寫入或者忽略那些庫的日志寫入二進制日志文件,默認空

log-save-update 是當前mysql服務(wù)作為slave 節(jié)點, 則當前節(jié)點是不會從master節(jié)點獲取二進制日志文件在寫入自己的二進制文件的, 如果要開啟就是要在從節(jié)點再次寫入的話需要配置 log-save-update. 另外,如果mysql主從配置如果為 master=>slave=>slave 的話,則該參數(shù)必須設(shè)置

binlog_format 二進制日志文件格式

Row

日志中會記錄成每一行數(shù)據(jù)被修改的形式，然后在 slave 端再對相同的數(shù)據(jù)進行修改。

優(yōu)點： 在 row 模式下，bin-log 中可以不記錄執(zhí)行的 SQL 語句的上下文相關(guān)的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了。所以 row 的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)，非常容易理解。而且不會出現(xiàn)某些特定情況下的存儲過程或 function ，以及 trigger 的調(diào)用和觸發(fā)無法被正確復制的問題。

缺點： 在 row 模式下，所有的執(zhí)行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產(chǎn)生大量的日志內(nèi)容，比如有這樣一條 update 語句：

UPDATE product SET owner_member_id = 'b' WHERE owner_member_id = 'a'

執(zhí)行之后，日志中記錄的不是這條 update 語句所對應(yīng)的事件 (MySQL 以事件的形式來記錄 bin-log 日志) ，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個事件。自然，bin-log 日志的量就會很大。尤其是當執(zhí)行 alter table 之類的語句的時候，產(chǎn)生的日志量是驚人的。因為 MySQL 對于 alter table 之類的表結(jié)構(gòu)變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表。那么該表的每一條記錄都會被記錄到日志中。

Statement

每一條會修改數(shù)據(jù)的 SQL 都會記錄到 master 的 bin-log 中。slave 在復制的時候 SQL 進程會解析成和原來 master 端執(zhí)行過的相同的 SQL 再次執(zhí)行。

優(yōu)點： 在 statement 模式下，首先就是解決了 row 模式的缺點，不需要記錄每一行數(shù)據(jù)的變化，減少了 bin-log 日志量，節(jié)省 I/O 以及存儲資源，提高性能。因為他只需要記錄在 master 上所執(zhí)行的語句的細節(jié)，以及執(zhí)行語句時候的上下文的信息。

缺點： 在 statement 模式下，由于他是記錄的執(zhí)行語句，所以，為了讓這些語句在 slave 端也能正確執(zhí)行，那么他還必須記錄每條語句在執(zhí)行的時候的一些相關(guān)信息，也就是上下文信息，以保證所有語句在 slave 端杯執(zhí)行的時候能夠得到和在 master 端執(zhí)行時候相同的結(jié)果。另外就是，由于 MySQL 現(xiàn)在發(fā)展比較快，很多的新功能不斷的加入，使 MySQL 的復制遇到了不小的挑戰(zhàn)，自然復制的時候涉及到越復雜的內(nèi)容，bug 也就越容易出現(xiàn)。在 statement 中，目前已經(jīng)發(fā)現(xiàn)的就有不少情況會造成 MySQL 的復制出現(xiàn)問題，主要是修改數(shù)據(jù)的時候使用了某些特定的函數(shù)或者功能的時候會出現(xiàn)，比如：sleep() 函數(shù)在有些版本中就不能被正確復制，在存儲過程中使用了 last_insert_id() 函數(shù)，可能會使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行來記錄的變化，所以不會出現(xiàn)類似的問題。

Mixed

從 5.1.8 版本開始，MySQL 提供了除 Statement 和 Row 之外的第三種復制模式：Mixed，實際上就是前兩種模式的結(jié)合。

在 Mixed 模式下，MySQL 會根據(jù)執(zhí)行的每一條具體的 SQL 語句來區(qū)分對待記錄的日志形式，也就是在 statement 和 row 之間選擇一種。

新版本中的 statment 還是和以前一樣，僅僅記錄執(zhí)行的語句。而新版本的 MySQL 中對 row 模式也被做了優(yōu)化，并不是所有的修改都會以 row 模式來記錄，比如遇到表結(jié)構(gòu)變更的時候就會以 statement 模式來記錄，如果 SQL 語句確實就是 update 或者 delete 等修改數(shù)據(jù)的語句，那么還是會記錄所有行的變更。

以上是對binlog 基本參數(shù)的配置做一個簡要說明

下面將通過開源binlog2sql 來嘗試恢復數(shù)據(jù)

binlog2sql 是大眾點評開源快速回滾的工具, 其原理如名字是通過mysql的binlog對數(shù)據(jù)進行恢復

下面走一個案例來測試恢復數(shù)據(jù)

mysql版本5.7

python 版本 2.7.16

安裝

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

shell> pip install -r requirements.txt

注意: shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql binlog2sql路徑下的requirements.txt文件需要調(diào)整 cat requirements.txt PyMySQL0.7.11 wheel0.29.0 mysql-replication==0.13 需要將PyMySQL由0.7.11調(diào)整為0.9.3 shell> pip install -r requirements.txt

測試

準備數(shù)據(jù)庫 test, 表user, 確認當前使用的binlog文件是什么

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

當前使用的binlog 文件是 mysql-bin.000002

手動向user 中插入幾條數(shù)據(jù)

INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '老趙');

INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '老高');

INSERT INTO `test`.`user`(`id`, `name`) VALUES (3, '你妹的');

此時數(shù)據(jù)庫中的數(shù)據(jù)是


?

此時我們咔全刪了 DELETE TABLE user;

使用binlog2sql 對案發(fā)時間的日志做篩查

刪除數(shù)據(jù)

DELETE FROM `user`; # 案發(fā)時間大約在 2021-02-20 16:27:00 - 2021-02-20 16:29:00

使用binlog2sql 生成的回滾數(shù)據(jù)恢復數(shù)據(jù)

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -tuser --start-file='mysql-bin.000002' --start-datetime='2021-02-20 16:27:00' --stop-datetime='2021-02-20 16:29:00' -B > rollback.sql | cat

結(jié)果:

jack@JackdeMacBook-Pro binlog2sql % python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -tuser --start-file='mysql-bin.000002' --start-datetime='2021-02-20 16:27:00' --stop-datetime='2021-02-20 16:29:00' -B > rollback.sql | cat

INSERT INTO `test`.`user`(`id`, `name`) VALUES (3, '你妹的'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '老高'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '老趙'); #start 4911 end 5175 time 2021-02-20 16:27:08

jack@JackdeMacBook-Pro binlog2sql %

jack@JackdeMacBook-Pro binlog2sql % cat rollback.sql

INSERT INTO `test`.`user`(`id`, `name`) VALUES (3, '你妹的'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '老高'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '老趙'); #start 4911 end 5175 time 2021-02-20 16:27:08

jack@JackdeMacBook-Pro binlog2sql %

經(jīng)過排查然后執(zhí)行恢復數(shù)據(jù) 👌

參考

MySQL技術(shù)內(nèi)幕++InnoDB存儲引擎

記錄報錯

總結(jié)

以上是生活随笔為你收集整理的mysql binlog update_mysql binlog 简单介绍与基于binlog数据恢复的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。