背景:
关于MySQL的审核的重要性就不说明了,本文的自动化审核是通过Inception和SQLAdvisor实现的,具体的使用可以看它们各自的说明文档。这里大致介绍下如何部署和使用它们,其实该文章也可以说成是如何在ubuntu下安装Inception和SQLAdivsor。本文的web平台是通过python的tornado来实现的,详细信息可以看下面的介绍说明。
环境:
系统:Ubuntu 14.04.5
安装部署:
1)安装Inception:
① 下载inception
git clone https://github.com/mysql-inception/inception.git
② 安装依赖包
1,cmake: sudo apt-get install cmake: 2,libncurses5-dev: sudo apt-get install libncurses5-dev 3,libssl-dev: sudo apt-get install libssl-dev 4,g++ sudo apt-get install g++ 5,m4 sudo apt-get install m4 6,bison:版本最好是2.6之前的,最新的可能会有问题 wget http://ftp.gnu.org/gnu/bison/bison-2.5.1.tar.gz ./configure make make install
做个软连接:
ln -s /usr/local/inception-master_2.1.50/sql/Inception /usr/bin/Inception
③ 安装inception
1 安装,进入clone的目录执行: sh inception_build.sh debug 上面执行完毕之后再执行,可执行文件在debug下面的sql目录中,执行: mv debug/ /usr/local/inception-master_2.1.50 2 查看版本: cd /usr/local/inception-master_2.1.50/sql ./Inception -V ./Inception Ver Inception2.1.50 for Linux on x86_64 (Source distribution)
④ 安装percona-toolkit
wget https://www.percona.com/downloads/percona-toolkit/2.2.20/tarball/percona-toolkit-2.2.20.tar.gz tar zxvf percona-toolkit-2.2.20.tar.gz cd percona-toolkit-2.2.20/ perl Makefile.PL make install
⑤ 测试Inception:
命令行启动:
Inception --port=6669 2017-03-27 00:45:31 0 [Note] Welcome to use Inception2.1.50 2017-03-27 00:45:31 26811 [Note] Server hostname (bind-address): '*'; port: 6669 2017-03-27 00:45:31 26811 [Note] IPv6 is available. 2017-03-27 00:45:31 26811 [Note] - '::' resolves to '::'; 2017-03-27 00:45:31 26811 [Note] Server socket created on IP: '::'.
参数启动:可以根据需要来调整参数
Inception --defaults-file=/etc/inception/inc.cnf
各个参数的意义:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
[inception] general_log=1 general_log_file=/var/log/inception.log port=6669 socket=/tmp/inc.socket character-set-client-handshake=0 character-set-server=utf8 #备份相关 #需要开启binlog inception_remote_system_password=cVQ9FLoiTbED2R3ycvnJ inception_remote_system_user=audit_user inception_remote_backup_port=3306 inception_remote_backup_host=192.168.200.49 #在DML语句中没有WHERE条件时,是不是要报错 inception_check_dml_where=1 #在DML语句中使用了LIMIT时,是不是要报错 inception_check_dml_limit=1 #在DML语句中使用了Order By时,是不是要报错 inception_check_dml_orderby #Select*时是不是要报错 inception_enable_select_star=1 #order by rand时是不是报错 inception_enable_orderby_rand=1 #创建或者新增列时如果列为NULL,是不是报错 inception_enable_nullable=1 #是不是支持外键 inception_enable_foreign_key=1 #一个索引中,列的最大个数,超过这个数目则报错(1-64) inception_max_key_parts=5 #在一个修改语句中,预计影响的最大行数,超过这个数就报错(1-max) inception_max_update_rows=10000 #一个表中,最大的索引数目,超过这个数则报错(1-1024) inception_max_keys=16 #建表指定的存储引擎不为Innodb,不报错 inception_enable_not_innodb=0 #表示在建表或者建库时支持的字符集,如果需要多个,则用逗号分隔,影响的范围是建表、设置会话字符集、修改表字符集属性等 inception_support_charset=utf8mb4 #建表时,表没有注释时报错 inception_check_table_comment=1 #建表时,列没有注释时报错 inception_check_column_comment=1 #建表时,如果没有主键,则报错 inception_check_primary_key=1 #是不是支持分区表 inception_enable_partition_table=0 #是不是支持enum,set,bit数据类型 inception_enable_enum_set_bit=0 #是不是要检查索引名字前缀为"idx_",检查唯一索引前缀是不是"uniq_" inception_check_index_prefix=1 #自增列是不是要为无符号型 inception_enable_autoincrement_unsigned=1 #当char类型的长度大于这个值时,就提示将其转换为VARCHAR(1-max) inception_max_char_length=16 #当建表时自增列的值指定的不为1,则报错 inception_check_autoincrement_init_value=1 #当建表时自增列的类型不为int或者bigint时报错 inception_check_autoincrement_datatype=1 #建表时,如果没有为timestamp类型指定默认值,则报错 inception_check_timestamp_default=0 #允许列自己设置字符集 inception_enable_column_charset=0 #建表时,如果指定的自增列的名字不为ID,则报错,说明是有意义的,给提示 inception_check_autoincrement_name=1 #在多个改同一个表的语句出现时,报错,提示合成一个 inception_merge_alter_table=1 #检查在建表、修改列、新增列时,新的列属性是不是要有默认值 inception_check_column_default_value=1 #检查是不是支持BLOB字段,包括建表、修改列、新增列操作 inception_enable_blob_type=1 #检查在SQL语句中,是不是有标识符被写成MySQL的关键字,默认值为报警。 inception_enable_identifer_keyword=1 #这个参数的作用是为了匹配Python客户端每次自动设置auto_commit=0的,如果取消则会报错,针对Inception本身没有实际意义 #auto_commit=0 #这个参数实际上就是MySQL数据库原来的参数,因为Incpetion没有权限验证过程,那么为了实现更安全的访问,可以给Inception服务器的这个参数设置某台机器(Inception上层的应用程序)不地址,这样 #其它非法程序是不可访问的,那么再加上Inception执行的选项中的用户名密码,对MySQL就更加安全 bind_address=127.0.0.1 #inception_user #inception_password #inception_enable_sql_statistic #inception_read_only #打开与关闭Inception对SQL语句中各种名字的检查,如果设置为ON,则如果发现名字中存在除数字字母下划线之外的字符时,报Identifier "invalidname" is invalid, valid options: [a-z,A-Z,0-9,_]. inception_check_identifier=1 #inception_osc_min_table_size=1 #inception_osc_bin_dir=/data/temp #inception_osc_chunk_time=0.1
Inception需要用MySQL客户端连接,需要安装mysql-client,本文测试用Percona MySQL 5.6。因为后面需要用到MySQL,就直接安装客户端和服务端:
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-server-server-5.6
连接Inception(6669),用inception get variables;测试是否安装成功。
mysql -uroot -h127.0.0.1 -P6669 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: Inception2.1.50 1 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> inception get variables; ...
⑥ 接口:python接口
#!/usr/bin/python #-*- coding: utf-8 -*- import MySQLdb sql='/*--user=root;--password=123456;--host=127.0.0.1;--port=3306;--execute=1;*/\ inception_magic_start;\ use test;\ CREATE TABLE xxx(id int unsigned not null auto_increment comment "aaaa",username varchar(10) not null default 0 comment "xxx",primary key(id))engine = innodb default charset utf8mb4 comment "dddd";\ inception_magic_commit;' #insert into adaptive_office(username) values("ASD"),("ZXC"),("EST"); try: conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='',port=6669) cursor=conn.cursor() cursor.execute(sql) results = cursor.fetchall() column_name_max_size=max(len(i[0]) for i in cursor.description) row_num=0 for result in results: row_num=row_num+1 print '*'.ljust(27,'*'),row_num,'.row', '*'.ljust(27,'*') row = map(lambda x, y: (x,y), (i[0] for i in cursor.description), result) for each_column in row: if each_column[0] != 'errormessage': print each_column[0].rjust(column_name_max_size),":",each_column[1] else: print each_column[0].rjust(column_name_max_size),':',each_column[1].replace('\n','\n'.ljust(column_name_max_size+4)) cursor.close() conn.close() except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
效果:
python inc.py *************************** 1 .row *************************** ID : 1 stage : CHECKED errlevel : 0 stagestatus : Audit completed errormessage : None SQL : use test Affected_rows : 0 sequence : '0_0_0' backup_dbname : None execute_time : 0 sqlsha1 : *************************** 2 .row *************************** ID : 2 stage : CHECKED errlevel : 1 stagestatus : Audit completed errormessage : Set unsigned attribute on auto increment column in table 'xx'. SQL : CREATE TABLE xx(id int not null auto_increment comment "aaaa",username varchar(10) not null default 0 comment "xxx",primary key(id))engine = innodb default charset utf8mb4 comment "dddd" Affected_rows : 0 sequence : '0_0_1' backup_dbname : 127_0_0_1_3306_test execute_time : 0 sqlsha1 :
到此,Inception安装已完成。更多的使用说明,可以从文档说明里查找,文档是最好的手册指南。
2)安装SQLAdvisor,架构和原理说明可以看美团点评SQL优化工具SQLAdvisor开源
① 下载
git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
② 安装依赖包
apt-get install cmake apt-get install libffi-dev apt-get install libaio-dev apt-get install glib2.0-dev apt-get install glib2.0 apt-get install percona-server-client-5.6 apt-get install percona-server-server-5.6 apt-get install percona-server-common-5.6 apt-get install libmysqlclient-dev apt-get install build-essential apt-get install g++ apt-get install bison
③ 相关修改
1:建立安装目录 mkdir -p /usr/local/sqlparser 2:增加软连接 cd /usr/lib/x86_64-linux-gnu/ ls -lh /usr/lib/x86_64-linux-gnu/libperconaserverclient* ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so 3:修改配置 vi sqladvisor/CMakeLists.txt cmake_minimum_required (VERSION 2.6) project(sqladvisor) # /usr/local/sqlparser为sqlparser库安装目录,必要时进行修改 include_directories("/usr/local/sqlparser/include") include_directories("/usr/local/sqlparser/include/regex") #include_directories("/usr/lib64/glib-2.0/include") include_directories("/usr/lib/x86_64-linux-gnu/glib-2.0/include") include_directories("/usr/include/glib-2.0") link_directories("/usr/local/sqlparser/lib") link_directories("/usr/lib/x86_64-linux-gnu") set(TEST_SRC main.cc) add_executable(sqladvisor ${TEST_SRC}) #如果是sqlparser debug库,则应连接的库为sqlparser-debug target_link_libraries(sqladvisor sqlparser-debug) target_link_libraries(sqladvisor perconaserverclient_r) target_link_libraries(sqladvisor glib-2.0)
④ 编译依赖项sqlparser
1: cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./ 2: make 3: make install
⑤ 安装SQLAdvisor源码
1: cd sqladvisor/ 2: cmake -DCMAKE_BUILD_TYPE=debug ./ 3: make
⑥ 测试:
sqladvisor --help Usage: sqladvisor [OPTION...] sqladvisor SQL Advisor Summary Help Options: -?, --help Show help options Application Options: -f, --defaults-file sqls file -u, --username username -p, --password password -P, --port port -h, --host host -d, --dbname database name -q, --sqls sqls -v, --verbose 1:output logs 0:output nothing
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
sqladvisor -h 127.0.0.1 -P 3306 -u root -p 123456 -d test -q "select * from xxx order by username " -v 1 2017-03-27 02:08:43 18111 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `test`.`xxx` order by `username` 2017-03-27 02:08:43 18111 [Note] 第2步:开始解析order by 条件 2017-03-27 02:08:43 18111 [Note] 第3步:开始验证 字段username是不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] show index from xxx where Key_name = 'PRIMARY' and Column_name ='username' and Seq_in_index = 1 2017-03-27 02:08:43 18111 [Note] 第4步:字段username不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] 第5步:开始添加order by 字段 2017-03-27 02:08:43 18111 [Note] 第6步:开始验证 字段username是不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] show index from xxx where Key_name = 'PRIMARY' and Column_name ='username' and Seq_in_index = 1 2017-03-27 02:08:43 18111 [Note] 第7步:字段username不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] 第8步:开始验证 字段username是不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] show index from xxx where Key_name = 'PRIMARY' and Column_name ='username' and Seq_in_index = 1 2017-03-27 02:08:43 18111 [Note] 第9步:字段username不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] 第10步:开始验证 字段username是不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] show index from xxx where Key_name = 'PRIMARY' and Column_name ='username' and Seq_in_index = 1 2017-03-27 02:08:43 18111 [Note] 第11步:字段username不是主键。表名:xxx 2017-03-27 02:08:43 18111 [Note] 第12步:开始验证表中是否已存在相关索引。表名:xxx, 字段名:username, 在索引中的位置:1 2017-03-27 02:08:43 18111 [Note] show index from xxx where Column_name ='username' and Seq_in_index =1 2017-03-27 02:08:43 18111 [Note] 第13步:开始输出表xxx索引优化建议: 2017-03-27 02:08:43 18111 [Note] Create_Index_SQL:alter table xxx add index idx_username(username) 2017-03-27 02:08:43 18111 [Note] 第14步: SQLAdvisor结束!
到此,SQLAdvisor安装已完成。更多的使用说明,可以从文档说明里查找,文档是最好的手册指南。
1)和2)即Inception和SQLAdvisor已经解决了大部分场景的审核要求,通过他们各自的说明手册了解其使用方法就可以直接通过web来操作实现了。
3)Tornado实现web
① 安装
1: apt-get install python-setuptools 2: easy_install tornado 3: apt-get install python-mysqldb
4:
easy_install futures
② 初始化MySQL(上面已经装好了mysql server)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE DATABASE `sql_audit` USE `sql_audit`; #SQLAdvisor使用 DROP TABLE IF EXISTS `slave_dbnames`; CREATE TABLE `slave_dbnames` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dbname` varchar(30) NOT NULL, `createTime` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_dbname` (`dbname`) ) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8; #下面2张Inception使用 DROP TABLE IF EXISTS `sysuser`; CREATE TABLE `sysuser` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `username` varchar(30) NOT NULL COMMENT '用户名', `password` varchar(100) NOT NULL COMMENT '密码', `email` varchar(100) NOT NULL COMMENT '邮箱', `createTime` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_username` (`username`), UNIQUE KEY `uk_email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COMMENT='用户信息表'; DROP TABLE IF EXISTS `user_dbnames`; CREATE TABLE `user_dbnames` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `username` varchar(30) NOT NULL COMMENT '用户名', `dbname` varchar(30) NOT NULL COMMENT '数据库名', `createTime` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_username_dbname` (`username`,`dbname`) ) ENGINE=InnoDB AUTO_INCREMENT=368 DEFAULT CHARSET=utf8 COMMENT='用户操作数据库对应表';
③ 测试效果
web部分的代码就不说明了,主要就是传参数使用Inception和SQLAdvisor。我使用的模板来自:http://ace.jeka.by/index.html。
运行:
python audit_server.py Inception --port=6669
最终实现的效果如下:
4)Supervisor来实现后台进程启动
具体的可以看进程管理supervisor的简单说明,这里需要注意的是上面的安装都是通过root的,而Inception和SQLAdivsor以及Tornado的使用都不需要用root进行启动,为了安全,安装完所有之后,专门创建启动上面进程都用户,如:
ps -ef| grep incept #inception用户启动inception incepti+ 3021 3015 0 Mar14 ? 00:00:00 /usr/bin/Inception --defaults-file=/etc/inception/inc.cnf ps -ef| grep tornado | grep -v "color=auto" #tornado用户启动tornado tornado 26529 3015 0 Mar22 ? 00:00:06 /usr/bin/python /home/jyzhou/audit_platform/audit_server.py SQLAdvisor 是一个执行文件,直接使用就可以了。
总结:
通过上面大致就完成了一个web审核管理平台,本文主要是介绍ubuntu下如何安装Inception和SQLAdvisor,Centos可以直接看它们的手册说明。
参考文档: