引子
最近打算做一個(gè) MySQL 的數(shù)據(jù)庫運(yùn)維平臺(tái)秀仲。這里面有一個(gè)非常重要的功能就是 SQL 的審核，如果完全靠人工去實(shí)現(xiàn)就沒必要做成一個(gè)平臺(tái)了。正沒頭緒如何去實(shí)現(xiàn)的時(shí)候菇曲，google 了一下，看下有沒有現(xiàn)成的開源方案抚吠。果不其然常潮，github 上發(fā)現(xiàn)一個(gè) 【去哪兒網(wǎng)】開源的一個(gè)數(shù)據(jù)庫運(yùn)維工具 Inception, 它是一個(gè)集審核、執(zhí)行楷力、備份及生成回滾語句于一身的MySQL自動(dòng)化運(yùn)維工具喊式。
Inception 介紹
Inception 的架構(gòu)圖如下圖所示孵户，簡單來說，Inception 就是一個(gè) MySQL 的代理岔留，能夠幫助你審核 SQL夏哭，執(zhí)行 SQL，備份 SQL 影響的記錄献联。Inception 是一個(gè) C/S 的軟件架構(gòu)竖配。我們可以通過原生的 MySQL 客戶端 去連接，也可以通過遠(yuǎn)程的接口去連接酱固，目前執(zhí)行只支持通過C/C++接口械念、Python接口來對(duì)Inception訪問。
執(zhí)行流程圖如下：
安裝 Inception
我安裝的環(huán)境
OS: Ubuntu 16.04.2 LTS
安裝依賴
- 下載bison： 版本最好是2.6之前的（Ubuntu 16.04.2 LTS 版本下安裝的是 bison-2.5.1）运悲，最新的可能會(huì)有問題龄减，下載之后，需要自己編譯源碼來安裝班眯，具體安裝方法希停，可以參數(shù)網(wǎng)上的一些說明。
- cmake安裝：apt-get install cmake
- ncurses安裝：apt-get install libncurses5-dev
- 安裝openssl：apt-get install libssl-dev
- 安裝g++：sudo apt-get install g++
- 安裝m4: apt-get install m4
編譯安裝 Inception
git clone https://github.com/mysql-inception/inception.git
sh inception_build.sh debug [linux] (如果不指定就是linux平臺(tái)署隘，而如果要指定是Xcode宠能，就后面指定Xcode)
可執(zhí)行文件在 debug/sql/Debug/ 目錄下面（不同平臺(tái)有可能不相同）。
啟動(dòng) Inception
創(chuàng)建一個(gè)配置文件 inc.cnf, 里面主要是配置 Inception 啟動(dòng)的端口磁餐，SQL 審核的策略违崇，備份數(shù)據(jù)庫的配置等等，更多可參考官方文檔诊霹。
[inception]
general_log=1
general_log_file=inception.log
port=6669 # Inception 的監(jiān)聽的端口
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_remote_system_password=root # 備份數(shù)據(jù)庫密碼
inception_remote_system_user=wzf1 # 備份數(shù)據(jù)庫用戶名
inception_remote_backup_port=3306 # 備份數(shù)據(jù)庫端口
inception_remote_backup_host=127.0.0.1 # 備份數(shù)據(jù)庫地址
inception_support_charset=utf8mb4
inception_enable_nullable=0
inception_check_primary_key=1
inception_check_column_comment=1
inception_check_table_comment=1
inception_osc_min_table_size=1
inception_osc_bin_dir=/data/temp
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1
啟動(dòng)
./Inception --defaults-file=inc.cnf
訪問
- 通過原生的 MySQL 客戶端的方式羞延。主要注意的是，請(qǐng)不要將的 SQL 語句塊脾还，放到 MySQL 客戶端中執(zhí)行伴箩，因?yàn)檫@是一個(gè)自動(dòng)化運(yùn)維工具，如果使用交互式的命令行來使用的話沒有意義鄙漏，所有的 SQL 執(zhí)行應(yīng)該都通過接口的方式嗤谚，這個(gè)方式僅僅可用來查看和設(shè)置上訴配置文件里的配置，如
inception get variables;可查看所有的變量怔蚌，更多請(qǐng)參考官方文檔巩步。
mysql -uroot -h127.0.0.1 -P6669
- 通過接口的方式。下面是官方示例中的 Python 代碼媚创，需要注意的是如果使用 Python3 的
pymsql去連接會(huì)有異常渗钉，目前的解決方案是需要修改pymysql的源碼，具體 issue：
#!/usr/bin/python
#-\*-coding: utf-8-\*-
import MySQLdb
sql='/*--user=username;--password=password;--host=127.0.0.1;--execute=1;--port=3306;*/\
inception_magic_start;\
use mysql;\
CREATE TABLE adaptive_office(id int);\
inception_magic_commit;'
try:
conn=MySQLdb.connect(host='127.0.0.1',user='',passwd='',db='',port=9998)
cur=conn.cursor()
ret=cur.execute(sql)
result=cur.fetchall()
num_fields = len(cur.description)
field_names = [i[0] for i in cur.description]
print field_names
for row in result:
print row[0], "|",row[1],"|",row[2],"|",row[3],"|",row[4],"|",
row[5],"|",row[6],"|",row[7],"|",row[8],"|",row[9],"|",row[10]
cur.close()
conn.close()
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
SQL 審核 & 執(zhí)行
通過 Inception 對(duì)語句進(jìn)行審核時(shí)，必須要告訴 Inception 這些語句對(duì)應(yīng)的數(shù)據(jù)庫地址鳄橘、數(shù)據(jù)庫端口以及Inception連接數(shù)據(jù)庫時(shí)使用的用戶名声离、密碼等信息，而不能簡單的只是執(zhí)行一條sql語句瘫怜，所以必須要通過某種方式將這些信息傳達(dá)給 Inception术徊。
連接信息放在 /* ... */ 的注釋中，真正的 SQL 語句則包括在 inception_magic_start 與 inception_magic_commit:
/*--user=zhufeng;--password=xxxxxxxxxxx;--host=xxxxxxxxxx;
--enable-check;--port=3456;*/
inception_magic_start;
use mysql;
CREATE TABLE adaptive_office(id int);
inception_magic_commit;
連接的信息里可以配置更多的信息鲸湃，比如關(guān)閉備份等等赠涮，具體請(qǐng)參考官方文檔。
審核
審核的規(guī)范見官方文檔暗挑，有些規(guī)范是可配置的笋除，可根據(jù)自己公司的規(guī)范在 Inception 的配置文件中配置。
執(zhí)行
注意下炸裆，官方說是支持 DDL垃它，DML 語句的，但是并不支持 SELECT 查詢烹看。
比如通過 Inception 執(zhí)行一個(gè)建表語句：
...
inception_magic_start;
use mysql;
CREATE TABLE adaptive_office(id int);
inception_magic_commit;
...
返回結(jié)果, 可見是每一條 SQL 就會(huì)返回一個(gè)可執(zhí)行的結(jié)果国拇，errlevel 非 0 時(shí)表示執(zhí)行失敗，下面所示中的第二條 SQL 語句 Audit completed(審核完成) 但是不符合建表的規(guī)范惯殊，更多關(guān)于返回結(jié)果的說明可見官方文檔：
'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
1 | CHECKED | 0 | Audit completed | None | use inception_test | 0 | '0_0_0' | None | 0 |
2 | CHECKED | 1 | Audit completed | Set engine to innodb for table 'adaptive_office'.
Set charset to one of 'utf8mb4' for table 'adaptive_office'.
Set comments for table 'adaptive_office'.
Column 'id' in table 'adaptive_office' have no comments.
Column 'id' in table 'adaptive_office' is not allowed to been nullable.
Set Default value for column 'id' in table 'adaptive_office'
Set a primary key for table 'adaptive_office'. | CREATE TABLE adaptive_office(id int) | 0 | '0_0_1' | 10_10_1_67_1028_inception_test | 0 |
備份功能
前提條件
- 線上服務(wù)器必須要打開
binlog酱吝，不然不會(huì)備份及生成回滾語句。 - 參數(shù)
binlog_format必須要設(shè)置為mixed或者row模式土思，通過語句：set global binlog_format=mixed/row來設(shè)置务热，如果是statement模式，則不做備份及回滾語句的生成己儒。
注意點(diǎn)
- 在備份是陕习，執(zhí)行時(shí)被影響的表如果沒有主鍵的話，就不會(huì)做備份了址愿，這樣更簡單并且備份時(shí)間及數(shù)據(jù)都會(huì)少一點(diǎn)，不然再生成的回滾語句的 WHERE 條件就會(huì)將所有列寫進(jìn)去冻璃，這樣會(huì)影響性能且沒有太大意義响谓，所以在 WHERE 條件中，只需要主鍵即可省艳。
Inception 在做 DML 操作時(shí)具有備份功能（默認(rèn)開啟娘纷，可通過在執(zhí)行 SQL 中注釋文件中指定 --disable-remote-backup），它會(huì)將所有當(dāng)前語句修改的行備份下來跋炕，存儲(chǔ)到一個(gè)指定的備份庫中赖晶， 備份庫通過配置 Inception 參數(shù)來指定。
關(guān)于備份數(shù)據(jù)庫的命名方式，備份機(jī)器的庫名組成是由線上機(jī)器的 IP 地址的點(diǎn)換成下劃線遏插，再加上端口號(hào)捂贿，再加上庫名三部分，這三部分也是通過下劃線連接起來的胳嘲。例如：我執(zhí)行 DML 操作的數(shù)據(jù)庫地址是 192.168.1.1, 端口是 3306, 庫名是 inceptiondb, 則在備份數(shù)據(jù)庫中表名為：192_168_1_1_3306_inceptiondb厂僧。
比如，我有一個(gè) inception_test 庫了牛，其中有一張 userinfo 表颜屠，就兩個(gè)字段：
我通過 Inception 去執(zhí)行一個(gè) innert 操作：
/*--user=root;--password=xxx;--host=1.1.1.1;--execute=1;--port=3306;--sleep=0;--enable-remote-backup;*/\
inception_magic_start;\
use inception_test; \
insert into userinfo(`username`) values("test");\
inception_magic_commit;
返回的結(jié)果如下, 可以看到 Execute Successfully Backup successfully：
2 | EXECUTED | 0 | Execute Successfully
Backup successfully | None | insert into userinfo(`username`) values("test") | 1 | '1533716166_25519001_1' | 1_1_1_1_3306_inception_test | 0.060 |
查看下備份數(shù)據(jù)庫中的 1_1_1_1_3306_inception_test 庫 userinfo 表的結(jié)果, 根據(jù) innert 的語句生成了一條 delete 回滾語句：
3 DELETE FROM `inception_test`.`userinfo` WHERE id=4; 1533716166_25519001_1
那么，我需要如果正確地找到回滾的語句呢鹰祸？
可以查看下備份庫 1_1_1_1_3306_inception_test 中 userinfo 的表結(jié)構(gòu)：
主要有兩個(gè)字段：
-
rollback_statement text: 生成修改的回滾語句甫窟。 -
opid_time varchar(50): 這個(gè)列存儲(chǔ)的是的被執(zhí)行的 SQL 語句在執(zhí)行時(shí)的一個(gè)序列號(hào)，這個(gè)序列號(hào)由三部分組成：timestamp(int 值蛙婴，是語句被執(zhí)行的時(shí)間點(diǎn))+線上服務(wù)器執(zhí)行時(shí)所產(chǎn)生的 thread_id+當(dāng)前這條語句在所有被執(zhí)行的語句塊中的一個(gè)序號(hào)組成粗井。可見上面的結(jié)果：1533716166_25519001_1, 這個(gè)序列號(hào)會(huì)出現(xiàn)在執(zhí)行返回的結(jié)果中敬锐，所有需要回滾就是根據(jù)這個(gè)序列號(hào)去備份表中查詢回滾的 SQL 語句背传。
更多說明，請(qǐng)參考官方文檔中的備份功能說明台夺。
最后
有了這么好用的工具径玖，基于這個(gè)為基礎(chǔ)，我們?cè)趯懸粋€(gè) WEB 頁面做一個(gè)權(quán)限審批管理等功能颤介，一個(gè)數(shù)據(jù)庫運(yùn)維平臺(tái)就可以實(shí)現(xiàn)了梳星，真的需要自己去寫嗎？我有發(fā)現(xiàn)了一個(gè)基于 Inception 實(shí)現(xiàn)的一個(gè)數(shù)據(jù)庫運(yùn)維平臺(tái) Yearning滚朵。
感謝開源冤灾！
