mysql binlog相關知識點有哪些

本篇內容主要講解“mysql binlog相關知識點有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“mysql binlog相關知識點有哪些”吧!

1.
- 邏輯日志,記錄的是數據庫內部的所有變動（sql語句  行的改變）
- server層日志，binlog不僅僅記錄innodb的變動，也記錄myisam存儲引擎的變動。
- innodb redo 是存儲引擎層，和binlog不是一層，redo只記錄innodb的變化。
- binlog不記錄 select show 等查詢語句,記錄dml ddl等數據庫變動的語句.

2.binlog的作用
- 可以查看mysql變更
- mysql的復制架構（主從）
- mysql備份恢復（可以通過binlog進行數據的補齊或者回滾）

3.binlog的位置
log_bin = /home/mysql3306/mysql3306/mysql-bin
#binlog路徑+binlog前綴名 如：mysql-bin.000001

4.相關參數簡介
(1).log-bin
決定了msyql 的binlog的名字，生成的binlog名字為mysql-bin.000001

(2).binlog_format
規定binlog的格式，binlog有三種格式statement，row以及mixed，默認使用默認使用statement，建議使用row格式

(3).expire_logs_days
過期時間 建議大家根據業務設置7D-30D

(4).binlog_do_db
此參數表示只記錄指定數據庫的二進制日志

(5).binlog_ignore_db
此參數表示不記錄指定的數據庫的二進制日志

(6).sync_binlog(binlog落盤策略)
0---每一秒刷一次磁盤
1---每次事務提交都刷一次磁盤
n---（100 200 500）每n次提交落盤一次
innodb_flush_log_at_trx_commit =1 
sync_binlog=1
雙1配置，數據庫的安全性是最高的，不會丟事務。

(7).binlog_checksum 為 {CRC32|NONE}
寫binlog時，會將內容生成校驗位，之后存儲在binlog中。
默認情況下，服務器記錄事件的長度以及事件本身，并使用它來驗證事件是否正確寫入。 也可以通過設置 binlog_checksum系統變量來使服務器為事件寫入校驗和。

(8).max_binlog_size
binlog文件的最大值，默認和最大是1GB，并不能嚴格限定二進制文件的大小

(9).max_binlog_cache_size
表示的是binlog 能夠使用的最大cache 內存大小
當我們執行多語句事務的時候 所有session的使用的內存超過max_binlog_cache_size的值時
就會報錯：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”

(10).binlog_cache_size = 4M
線程級參數，不能設置太大

5.binlog格式
(1).statement
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

執行語句:
insert into wwj.t1 values(2,'mxt2');
insert into wwj.t1 values(3,'mxt3');
insert into wwj.t1 values(4,'mxt4');

查看binlog

點擊(此處)折疊或打開

1. mysql> show binlog events;

2. +------------------+-----+----------------+-----------+-------------+---------------------------------------+

3. | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |

4. +------------------+-----+----------------+-----------+-------------+---------------------------------------+

5. | mysql-bin.000001 |   4 | Format_desc    |  12023307 |         123 | Server ver: 5.7.21-log, Binlog ver: 4 |

6. | mysql-bin.000001 | 123 | Previous_gtids |  12023307 |         154 |                                       |

7. | mysql-bin.000001 | 154 | Anonymous_Gtid |  12023307 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |

8. | mysql-bin.000001 | 219 | Query          |  12023307 |         293 | BEGIN                                 |

9. | mysql-bin.000001 | 293 | Query          |  12023307 |         398 | insert into wwj.t1 values(13,'mxt3')  |

10. | mysql-bin.000001 | 398 | Xid            |  12023307 |         429 | COMMIT /* xid=275 */                  |

11. | mysql-bin.000001 | 429 | Anonymous_Gtid |  12023307 |         494 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |

12. | mysql-bin.000001 | 494 | Query          |  12023307 |         568 | BEGIN                                 |

13. | mysql-bin.000001 | 568 | Query          |  12023307 |         673 | insert into wwj.t1 values(14,'mxt4')  |

14. | mysql-bin.000001 | 673 | Xid            |  12023307 |         704 | COMMIT /* xid=276 */                  |

15. +------------------+-----+----------------+-----------+-------------+---------------------------------------+

Log_name： binlog文件名
Pos：binlog的position點，寫入binlog的日志的字節數，就是pos的大小，在每次新生成binlog的時候，pos點會重置，但是在一個binlog內部，這個pos是單調遞增的。
xid：分布式事務ID，Innodb支持分布式事務，Innodb支持的單實例內部的，server層和存儲引擎層的分布式事務，還有多節點分布式事務（ndb）
Server_id：實例server_id

解析binlog工具
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > /tmp/all.bin

點擊(此處)折疊或打開

1. # at 293

2. #180325  4:14:20 server id 12023307  end_log_pos 398 CRC32 0xb9d2f949   Query   thread_id=14    exec_time=0     error_code=0

3. SET TIMESTAMP=1521922460/*!*/;

4. insert into wwj.t1 values(13,'mxt3')

5. /*!*/;

6. # at 398

查看當前的binlog pos點的位置
mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    15
Current database: *** NONE ***
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      704 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+


應用binlog
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > /tmp/all.bin
mysql -uroot -proot -S /tmp/mysql3306.sock < /tmp/all.bin
mysql > source /tmp/all.bin

應用binlog需要注意的事項
如果應用多個binlog
錯誤做法
mysqlbinlog mysql-bin.000001 | mysql -S -u -p
mysqlbinlog mysql-bin.000002 | mysql -S -u -p
正確做法
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -S -u -p
或者
mysqlbinlog mysql-bin.000001 mysql-bin.000002 > /tmp/all.bin
mysql -S -u -p < /tmp/all.bin


(2).row
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

row格式binlog：row格式binlog記錄的是mysql的行的改變前后的數據
比如在stament格式中，update數據，記錄的是update語句
而且row格式中，記錄的行的改變的前后，如果update1000數據，記錄一千行的數據改變

mysqlbinlog -v  mysql-bin.000001 > /tmp/all.bin  增加偽sql
mysqlbinlog -vv  mysql-bin.000001 > /tmp/all.bin  增加數據類型

點擊(此處)折疊或打開

1. # at 334

2. #180325  5:51:04 server id 12023307  end_log_pos 458 CRC32 0x1be59376   Delete_rows: table id 108 flags: STMT_END_F

3. 
   

4. BINLOG '

5. SMi2WhMLdrcALwAAAE4BAAAAAGwAAAAAAAEAA3d3agACdDEAAgMPArQAAsKvIYM=

6. SMi2WiALdrcAfAAAAMoBAAAAAGwAAAAAAAEAAgAC//wBAAAAA3d3avwCAAAABG14dDL8AwAAAARt

7. eHQz/AQAAAAEbXh0NPwFAAAABG14dDL8BgAAAARteHQz/AcAAAAEbXh0NPwNAAAABG14dDP8DgAA

8. AARteHQ0dpPlGw==

9. '/*!*/;

10. ### DELETE FROM `wwj`.`t1`

11. ### WHERE

12. ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

13. ###   @2='wwj' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

14. ### DELETE FROM `wwj`.`t1`

15. ### WHERE

16. ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

17. ###   @2='mxt2' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

18. ### DELETE FROM `wwj`.`t1`

19. ### WHERE

20. ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

21. ###   @2='mxt3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

22. ### DELETE FROM `wwj`.`t1`

23. ### WHERE

24. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */

25. ###   @2='mxt4' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

26. ### DELETE FROM `wwj`.`t1`

27. ### WHERE

28. ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */

29. ###   @2='mxt2' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

30. ### DELETE FROM `wwj`.`t1`

31. ### WHERE

32. ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */

33. ###   @2='mxt3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

34. ### DELETE FROM `wwj`.`t1`

35. ### WHERE

36. ###   @1=7 /* INT meta=0 nullable=0 is_null=0 */

37. ###   @2='mxt4' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

38. ### DELETE FROM `wwj`.`t1`

39. ### WHERE

40. ###   @1=13 /* INT meta=0 nullable=0 is_null=0 */

41. ###   @2='mxt3' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

42. ### DELETE FROM `wwj`.`t1`

43. ### WHERE

44. ###   @1=14 /* INT meta=0 nullable=0 is_null=0 */

45. ###   @2='mxt4' /* VARSTRING(180) meta=180 nullable=1 is_null=0 */

46. # at 458

其他解析方法
解析不帶行數據
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000001
如果binlog解析出來需要恢復到數據庫，那么需要注意不要加--base64-output=DECODE-ROWS。
/usr/local/mysql/bin/mysqlbinlog
start-datetime  開始時間
stop-datetime   停止時間
/usr/local/mysql/bin/mysqlbinlog --start-datetime="2017-04-20 9:00:00" --stop-datetime="2017-07-20 18:00:00"/home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql
start-position  開始時間的position
stop-position   停止時間的position

row格式優點：
-記錄行改變，而不是SQL，能最大限度保證數據安全性
-row格式可以用于閃回（恢復數據）
在線上的環境中，推薦使用row格式的binlog
-row格式update delete會對磁盤 網絡造成一個比較大的壓力


(3).mixed
一般情況下使用statment格式，在遇到特殊情況（造成主從不一致）改成使用row格式記錄。
mixed格式在舊版本bug比較多
不推薦使用

6.清理binlog
 reset master; //刪除master的binlog，線上不要使用
 purge master logs before '2012-03-30 17:20:00'; //刪除指定日期以前的日志索引中binlog日志文件
 purge master logs to 'binlog.000002'; //刪除指定日志文件的日志索引中binlog日志文件

如果想直接刪除binlog
rm binlog.000002 
(不要刪除當前正在使用的binlog，也就是編號最大的binlog)
[root@namenode mysql3306]# cat mysql-bin.index    不會自動更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002
主從復制如果刪掉了，容易從庫報錯（保證從庫不再同步該binlog的數據）

7.binlog和redo的區別
- redo 物理邏輯日志 物理：數據頁 邏輯： 數據頁的改變
  binlog 是邏輯日志 記錄的SQL或者是行改變
- redo是innodb存儲引擎層
   binlog是server層
- redo時時刻刻都在寫入文件
   binlog是 提交事務的寫入（具體寫入策略跟具體參數有關）

到此，相信大家對“mysql binlog相關知識點有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！