MySQL自动化审核平台部署说明

背景：

关于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

各个参数的意义：

[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('
','
'.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

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）

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可以直接看它们的手册说明。

参考文档：

Inception使用规范及说明文档

SQLAdvisor详细说明

美团点评SQL优化工具SQLAdvisor

Tornado介绍说明