久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

mysql binlog相關知識點有哪些

160次閱讀
沒有評論

共計 7370 個字符,預計需要花費 19 分鐘才能閱讀完成。

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

點擊 (此處) 折疊或打開

mysql show binlog events;

+——————+—–+—————-+———–+————-+—————————————+

| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |

+——————+—–+—————-+———–+————-+—————————————+

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

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

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

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

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

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

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

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

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

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

+——————+—–+—————-+———–+————-+—————————————+

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

點擊 (此處) 折疊或打開

# at 293

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

SET TIMESTAMP=1521922460/*!*/;

insert into wwj.t1 values(13, mxt3)

/*!*/;

# 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   增加數據類型

點擊 (此處) 折疊或打開

# at 334

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

BINLOG

SMi2WhMLdrcALwAAAE4BAAAAAGwAAAAAAAEAA3d3agACdDEAAgMPArQAAsKvIYM=

SMi2WiALdrcAfAAAAMoBAAAAAGwAAAAAAAEAAgAC//wBAAAAA3d3avwCAAAABG14dDL8AwAAAARt

eHQz/AQAAAAEbXh0NPwFAAAABG14dDL8BgAAAARteHQz/AcAAAAEbXh0NPwNAAAABG14dDP8DgAA

AARteHQ0dpPlGw==

/*!*/;

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

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

### WHERE

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

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

# 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 相關知識點有哪些”有了更深的了解,不妨來實際操作一番吧!這里是丸趣 TV 網站,更多相關內容可以進入相關頻道進行查詢,關注我們,繼續學習!

正文完
 
丸趣
版權聲明:本站原創文章,由 丸趣 2023-07-20發表,共計7370字。
轉載說明:除特殊說明外本站除技術相關以外文章皆由網絡搜集發布,轉載請注明出處。
評論(沒有評論)
主站蜘蛛池模板: 洛隆县| 洪湖市| 东方市| 南乐县| 宣威市| 遵义市| 宁明县| 毕节市| 芒康县| 封开县| 仙游县| 巍山| 鹤岗市| 昌图县| 杭锦后旗| 常宁市| 阿拉尔市| 四子王旗| 宁城县| 镇沅| 奉贤区| 新龙县| 波密县| 金昌市| 崇文区| 和田市| 长武县| 福州市| 平潭县| 楚雄市| 竹溪县| 秀山| 阿城市| 北海市| 岑溪市| 茶陵县| 鹤山市| 澄江县| 叶城县| 浦北县| 吴桥县|