1.Inception简介
Inception是集审核、执行、回滚于一体的一个自动化运维系统,它可以对提交的所有语句的语法分析,如果语法有问题,都会将相应的错误信息返回给审核者。 还提供SQL语句的执行功能,可执行的语句类型包括常用的DML及DDL语句及truncate table等操作。 Inception在执行 DML时还提供生成回滚语句的功能,对应的操作记录及回滚语句会被存储在备份机器上面,备份机器通过配置Inception参数来指定。
2.先决条件
1.安装基础软件及工具
yum install cmake ncurses-devel gcc gcc-c++ openssl-devel
2.卸载已安装(yum安装)或系统自带的bison,因为centos7yum安装bison会因为版本过高导致编译报错。
yum remove bison
3.编译安装m4
cd /usr/local/
wget http://ftp.gnu.org/gnu/m4/m4-1.4.18.tar.gz
tar -zxvf m4-1.4.18.tar.gz
cd m4-1.4.18
./configure && make && make install
4.编译安装bison
wget http://ftp.gnu.org/gnu/bison/bison-2.4.tar.gz
tar -zxvf bison-2.4.tar.gz
cd bison-2.4/
./configure
make
make install
5.下载、编译及安装Inception
命令行不是很友好,现在已经有开源的基于Inception比较好用的web界面操作平台,分别是archer和yearning,下面附上这2个的github地址,读者可以自己选择使用哪个平台。
https://github.com/jly8866/archer
https://github.com/cookieY/Yearning
archer的安装可参考:https://www.cnblogs.com/chenjiaxin--007/p/8432795.html
1)下载Inception安装包
Inception源地址已没有源码,可以打开源地址,然后在右上角上面的fork处选择新的下载地址。
源码地址:https://github.com/mysql-inception/inception
下载方法如下图:
将下载的Inception-master.zip源码包上传到服务器(CentOS /usr/local/目录下),开始安装,步骤如下:
cd /usr/local/
unzip Inception-master.zip
mv inception-master/ inception
sh inception_build.sh builddir linux
builddir 就是编译目录(自定义),编译后所有的生成文件都在这个目录下面,包括可执行文件Inception。可执行文件在builddir /sql目录下面:
创建配置目录,将可执行文件Inception拷贝到/opt/inception/bin目录下
[root@mysql sql]# mkdir -p /data/inception
[root@mysql sql]# mkdir -p /data/inception/bin
[root@mysql sql]# mkdir -p /data/inception/temp
[root@mysql sql]# mkdir -p /var/log/inception
[root@mysql sql]# cp Inception /data/inception/bin/
新建及编辑Inception配置文件
[inception]
general_log=1
general_log_file=/var/log/inception/inception.log #日志保存路径,必须得先创建/var/log/inception/目录
port=6669
socket=/opt/inception/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_remote_system_password=123456 # 数据库登录密码
inception_remote_system_user=root #数据库用户名
inception_remote_backup_port=3306
inception_remote_backup_host=127.0.0.1 #数据库host
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=/opt/inception/temp
inception_osc_chunk_time=0.1
inception_ddl_support=1
inception_enable_blob_type=1
inception_check_column_default_value=1
启动Inception服务
nohup /data/inception/bin/Inception --defaults-file=/etc/inc.cnf &
检查服务启动是否成功:
ps -ef|grep Inception
正确结果如下所示:
登录验证
mysql -uroot -h127.0.0.1 -P6669
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: Inception2.0.18-beta 1
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> inception get variables;
正确结果如下所示:
可以看到所有的变量,安装成功!
使用Inception DDL审核
编写python 程序测试SQL DDL语句审核,创建一个python脚本程序文件inception.py,内容如下:
#!/usr/bin/python
#-\*-coding: utf-8-\*-
import pymysql
sql='/*--user=root;--password='';--host=192.168.11.95;--execute=1;--port=3306;*/\
inception_magic_start;\
use testdb;\
CREATE TABLE adaptive_office(id int);\
inception_magic_commit;'
try:
conn=pymysql.connect(host='192.168.11.95',user='root',password='',db='',port=6669)
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 pymysql.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
该程序的主要功能是连接Inception执行一个DDL SQL语句应用于testdb数据库,所以我们在执行该程序之前,需要先创建该数据库。
接下来通过如下命令运行该程序:
python inception.py
正确输出结果应如下所示:
在运行该程序时会遇到如下常见错误:
[root@Ansiblectl ~]# python inception.py
Traceback (most recent call last):
File "inception.py", line 10, in <module>
conn=pymysql.connect(host='127.0.0.1',user='root',password='123456',db='',port=6669)
File "/usr/lib64/python2.7/site-packages/pymysql/__init__.py", line 94, in Connect
return Connection(*args, **kwargs)
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 325, in __init__
self.connect()
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 599, in connect
self._request_authentication()
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 788, in _request_authentication
if int(self.server_version.split('.', 1)[0]) >= 5:
ValueError: invalid literal for int() with base 10: 'Inception2'
[root@Ansiblectl ~]# vim inception.py
[root@Ansiblectl ~]# python inception.py
Traceback (most recent call last):
File "inception.py", line 10, in <module>
conn=pymysql.connect(host='192.168.11.95',user='root',password='123456',db='',port=6669)
File "/usr/lib64/python2.7/site-packages/pymysql/__init__.py", line 94, in Connect
return Connection(*args, **kwargs)
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 325, in __init__
self.connect()
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 599, in connect
self._request_authentication()
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 788, in _request_authentication
if int(self.server_version.split('.', 1)[0]) >= 5:
ValueError: invalid literal for int() with base 10: 'Inception2'
解决办法:
在/usr/lib64/python2.7/site-packages/pymysql/connections.py文件中添加一行内容:self.server_version = '5.6.24-72.2-log',如下所示:
[root@Ansiblectl ~]# vim /usr/lib64/python2.7/site-packages/pymysql/connections.py
... ...
def _request_authentication(self):
# https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
self.server_version = '5.6.24-72.2-log' #添加此行
if int(self.server_version.split('.', 1)[0]) >= 5:
... ...
使用中的其他错误见: https://riverdba.github.io/2017/04/13/inception-install/#
至此,Inception mysql SQL审核工具安装及测试验证完成。
相关参考文档:
https://blog.51cto.com/hequan/2052602