MySQL 的 Binlog 記錄著 MySQL 數(shù)據(jù)庫的所有變更信息，了解 Binlog 的結(jié)構(gòu)可以幫助我們解析Binlog，甚至對 Binlog 進(jìn)行一些修改，或者說是“篡改”，例如實(shí)現(xiàn)類似于 Oracle 的 flashback 的功能，恢復(fù)誤刪除的記錄，把 update 的記錄再還原回去等。本文將帶您探討一下這些神奇功能的實(shí)現(xiàn)，您會(huì)發(fā)現(xiàn)比您想象地要簡單得多。本文指的 Binlog 是 ROW 模式的 Binlog，這也是 MySQL 8 里的默認(rèn)模式，STATEMENT 模式因?yàn)槭褂弥杏泻芏嘞拗?，現(xiàn)在用得越來越少了。
創(chuàng)新互聯(lián)主營龍子湖網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,重慶App定制開發(fā),龍子湖h5成都微信小程序搭建,龍子湖網(wǎng)站營銷推廣歡迎龍子湖等地區(qū)企業(yè)咨詢
Binlog 由事件（event）組成，請注意是事件（event）不是事務(wù)（transaction），一個(gè)事務(wù)可以包含多個(gè)事件。事件描述對數(shù)據(jù)庫的修改內(nèi)容。
現(xiàn)在我們已經(jīng)了解了 Binlog 的結(jié)構(gòu)，我們可以試著修改 Binlog 里的數(shù)據(jù)。例如前面舉例的 Binlog 刪除了一條記錄，我們可以試著把這條記錄恢復(fù)，Binlog 里面有個(gè)刪除行（DELETE_ROWS_EVENT）的事件，就是這個(gè)事件刪除了記錄，這個(gè)事件和寫行（WRITE_ROWS_EVENT）的事件的數(shù)據(jù)結(jié)構(gòu)是完全一樣的，只是刪除行事件的類型是 32，寫行事件的類型是 30，我們把對應(yīng)的 Binlog 位置的 32 改成 30 即可把已經(jīng)刪除的記錄再插入回去。從前面的 “show binlog events” 里面可看到這個(gè) DELETE_ROWS_EVENT 是從位置 378 開始的，這里的位置就是 Binlog 文件的實(shí)際位置（以字節(jié)為單位）。從事件（event）的結(jié)構(gòu)里面可以看到 type_code 是在 event 的第 5 個(gè)字節(jié)，我們寫個(gè) Python 小程序把把第383（378+5=383）字節(jié)改成 30 即可。當(dāng)然您也可以用二進(jìn)制編輯工具來改。
找出 Binlog 中的大事務(wù)
由于 ROW 模式的 Binlog 是每一個(gè)變更都記錄一條日志，因此一個(gè)簡單的 SQL，在 Binlog 里可能會(huì)產(chǎn)生一個(gè)巨無霸的事務(wù)，例如一個(gè)不帶 where 的 update 或 delete 語句，修改了全表里面的所有記錄，每條記錄都在 Binlog 里面記錄一次，結(jié)果是一個(gè)巨大的事務(wù)記錄。這樣的大事務(wù)經(jīng)常是產(chǎn)生麻煩的根源。我的一個(gè)客戶有一次向我抱怨，一個(gè) Binlog 前滾，滾了兩天也沒有動(dòng)靜，我把那個(gè) Binlog 解析了一下，發(fā)現(xiàn)里面有個(gè)事務(wù)產(chǎn)生了 1.4G 的記錄，修改了 66 萬條記錄！下面是一個(gè)簡單的找出 Binlog 中大事務(wù)的 Python 小程序，我們知道用 mysqlbinlog 解析的 Binlog，每個(gè)事務(wù)都是以 BEGIN 開頭，以 COMMIT 結(jié)束。我們找出 BENGIN 前面的 “# at” 的位置，檢查 COMMIT 后面的 “# at” 位置，這兩個(gè)位置相減即可計(jì)算出這個(gè)事務(wù)的大小，下面是這個(gè) Python 程序的例子。
切割 Binlog 中的大事務(wù)
對于大的事務(wù)，MySQL 會(huì)把它分解成多個(gè)事件（注意一個(gè)是事務(wù) TRANSACTION，另一個(gè)是事件 EVENT），事件的大小由參數(shù) binlog-row-event-max-size 決定，這個(gè)參數(shù)默認(rèn)是 8K。因此我們可以把若干個(gè)事件切割成一個(gè)單獨(dú)的略小的事務(wù)
ROW 模式下，即使我們只更新了一條記錄的其中某個(gè)字段，也會(huì)記錄每個(gè)字段變更前后的值，這個(gè)行為是 binlog_row_image 參數(shù)控制的，這個(gè)參數(shù)有 3 個(gè)值，默認(rèn)為 FULL，也就是記錄列的所有修改，即使字段沒有發(fā)生變更也會(huì)記錄。這樣我們就可以實(shí)現(xiàn)類似 Oracle 的 flashback 的功能，我個(gè)人估計(jì) MySQL 未來的版本從可能會(huì)基于 Binlog 推出這樣的功能。
了解了 Binlog 的結(jié)構(gòu)，再加上 Python 這把瑞士軍刀，我們還可以實(shí)現(xiàn)很多功能，例如我們可以統(tǒng)計(jì)哪個(gè)表被修改地最多？我們還可以把 Binlog 切割成一段一段的，然后再重組，可以靈活地進(jìn)行 MySQL 數(shù)據(jù)庫的修改和遷移等工作。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
通過 AUTO_INCREMENT設(shè)置
SQL INSERT語句的時(shí)候，要避免 指定那個(gè)自增的字段.否則會(huì)發(fā)生主鍵的沖突。
通過 ALTER TABLE語句 可以修改 自增的數(shù)值， 但是只能增加，不能減少。
TRUNCATE TABLE 語句，會(huì)將自增ID重置為零。
mysql CREATE TABLE test_create_tab2 (
- id INT AUTO_INCREMENT,
- val VARCHAR(10),
- PRIMARY KEY (id)
- );
Query OK, 0 rows affected (0.09 sec)
mysql INSERT INTO test_create_tab2(val) VALUES ('NO id');
Query OK, 1 row affected (0.03 sec)
mysql select last_insert_id() as id;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql INSERT INTO test_create_tab2(val) VALUES ('NO id 2');
Query OK, 1 row affected (0.03 sec)
mysql select last_insert_id() as id;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
mysql select * from test_create_tab2;
+----+---------+
| id | val |
+----+---------+
| 1 | NO id |
| 2 | NO id 2 |
+----+---------+
2 rows in set (0.00 sec)
utf8編碼可以支持一到4字節(jié)的字符編碼，在mysql用我們一般使用utf8編碼來處理字符類型，通常情況下都沒有問題，但遇到4字節(jié)編碼的字符，在數(shù)據(jù)存取的時(shí)候就會(huì)有問題了。
通常我們可能會(huì)得到一個(gè)錯(cuò)誤或者警告:Incorrect string value: '/xF0/x9D/x8C/x86' for column ...
Mysql 從5.5.3版本開始支持4字節(jié)的utf8編碼，如果你的Mysql數(shù)據(jù)庫是5.5.3+，按照以下步驟就能解決這個(gè)問題，如果版本低于5.5.3，是不是可以考慮升級數(shù)據(jù)庫版本呢？
1、在修改數(shù)據(jù)庫編碼前先對數(shù)據(jù)庫備份（雖然utf8mb4兼容utf8，但有備無患）
2、修改數(shù)據(jù)庫的編碼、表的編碼、列的編碼為utf8mb4
3、在Mysql數(shù)據(jù)庫配置文件(my.ini)中加入如下設(shè)置
[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ci
重新啟動(dòng)Mysql數(shù)據(jù)庫，確認(rèn)設(shè)置生效
mysql show VARIABLES like '%char%'；+--------------------------+----------------------------------------+| Variable_name | Value |+--------------------------+----------------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8mb4 || character_set_system | utf8 || character_sets_dir | /home/app/mysql-5.5.33/share/charsets/ |+--------------------------+----------------------------------------+8 rows in set
4、在獲取數(shù)據(jù)庫連接的時(shí)候執(zhí)行sql:set names utf8mb4；我使用的是alibaba的開源數(shù)據(jù)庫連接池程序，在配置文件中增加一行如下配置
property name="connectionInitSqls" value="set names utf8mb4；" /
重新啟動(dòng)應(yīng)用程序，問題解決。
整理 MySQL 8.0 文檔時(shí)發(fā)現(xiàn)一個(gè)變更：
默認(rèn)字符集由 latin1 變?yōu)?utf8mb4。想起以前整理過字符集轉(zhuǎn)換文檔，升級到 MySQL 8.0 后大概率會(huì)有字符集轉(zhuǎn)換的需求，在此正好分享一下。
當(dāng)時(shí)的需求背景是：
部分系統(tǒng)使用的字符集是 utf8，但 utf8 最多只能存 3 字節(jié)長度的字符，不能存放 4 字節(jié)的生僻字或者表情符號(hào)，因此打算遷移到 utf8mb4。
遷移方案一1. 準(zhǔn)備新的數(shù)據(jù)庫實(shí)例，修改以下參數(shù)：[mysqld]## Character Settingsinit_connect='SET NAMES utf8mb4'#連接建立時(shí)執(zhí)行設(shè)置的語句，對super權(quán)限用戶無效character-set-server = utf8mb4collation-server = utf8mb4_general_ci#設(shè)置服務(wù)端校驗(yàn)規(guī)則，如果字符串需要區(qū)分大小寫，設(shè)置為utf8mb4_binskip-character-set-client-handshake#忽略應(yīng)用連接自己設(shè)置的字符編碼，保持與全局設(shè)置一致## Innodb Settingsinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_file_per_table = 1innodb_large_prefix = ON#允許索引的最大字節(jié)數(shù)為3072（不開啟則最大為767字節(jié)，對于類似varchar(255)字段的索引會(huì)有問題，因?yàn)?55*4大于767）
2. 停止應(yīng)用，觀察，確認(rèn)不再有數(shù)據(jù)寫入
可通過 show master status 觀察 GTID 或者 binlog position，沒有變化則沒有寫入。
3. 導(dǎo)出數(shù)據(jù)
先導(dǎo)出表結(jié)構(gòu)：mysqldump -u -p --no-data --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases testdb /backup/testdb.sql
后導(dǎo)出數(shù)據(jù)：mysqldump -u -p --no-create-info --master-data=2 --flush-logs --routines --events --triggers --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --database testdb /backup/testdata.sql
4. 修改建表語句
修改導(dǎo)出的表結(jié)構(gòu)文件，將表、列定義中的 utf8 改為 utf8mb4
5. 導(dǎo)入數(shù)據(jù)
先導(dǎo)入表結(jié)構(gòu)：mysql -u -p testdb /backup/testdb.sql
后導(dǎo)入數(shù)據(jù)：mysql -u -p testdb /backup/testdata.sql
6. 建用戶
查出舊環(huán)境的數(shù)據(jù)庫用戶，在新數(shù)據(jù)庫中創(chuàng)建
7. 修改新數(shù)據(jù)庫端口，啟動(dòng)應(yīng)用進(jìn)行測試
關(guān)閉舊數(shù)據(jù)庫，修改新數(shù)據(jù)庫端口重啟，啟動(dòng)應(yīng)用
1、授權(quán)mysql在遠(yuǎn)程機(jī)器上以登錄。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'host' IDENTIFIED BY 'pwd' WITH GRANT OPTION。
2、修改字段名稱和類型。
--alter table upload change column old name new name new datatype
alter table filetable change column fdata fdata MediumBlob
3、MySQL BLOB。
MySQL中，BLOB是一個(gè)二進(jìn)制大型對象，是一個(gè)可以存儲(chǔ)大量數(shù)據(jù)的容器，它能容納不同大小的數(shù)據(jù)。BLOB類型實(shí)際是個(gè)類型系列（TinyBlob、Blob、MediumBlob、LongBlob），除在存儲(chǔ)的最大信息量上不同，基本是等同的。
MySQL的四種BLOB類型；類型、大小(單位：字節(jié))：
1、TinyBlob 最大 255 。
2、Blob 最大 65K。
3、MediumBlob 最大 16M。
4、LongBlob 最大 4G。
實(shí)際使用中根據(jù)需要存入的數(shù)據(jù)大小定義不同的BLOB類型。注意：如存儲(chǔ)的文件過大，數(shù)據(jù)庫的性能會(huì)下降。
重慶分公司
重慶分公司
