mysql sql审核工具_centos 7 安装sql 审核工具 inception + archer

系统环境: Centos7 + python2.7 +python3 ....

下载

源码地址：https://github.com/mysql-inception/inception

Inception安装1、安装相关依赖包

yum install bison ncurses-libs libncurses5-dev ncurses-devel wget

git cmake openssl gcc-c++ -y

注意:centos7 系统自带的bison的版本过高，在后面测试的时候会报错！

下载地址: wget http://ftp.gnu.org/gnu/bison/bison-2.5.1.tar.gz

2、安装inception

git clone https://github.com/mysql-inception/inception

cd inception

sh inception_build.sh debug

debug就是编译目录，编译后所有的生成文件都在这个目录下面，包括可执行文件Inception。可执行文件在debug/sql目录下面：

[root@mysql sql]# pwd/root/inception/debug/sql

[root@mysql sql]# ls

CMakeFiles dummy.bak Inception libbinlog.a librpl.a libsql.a Makefile sql_builtin.cc sql_yacc.h

cmake_install.cmake gen_lex_hash lex_hash.h libmaster.a libslave.a libsqlgunitlib.a share sql_yacc.cc

[root@mysql3 sql]#3、创建配置目录，将可执行文件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]# cp Inception/data/inception/bin/编辑配置文件：

[root@mysql sql]# cat/etc/inc.cnf

[inception]

general_log=1general_log_file=/opt/inception/inception.log

port=6669socket=/opt/inception/inc.socket

character-set-client-handshake=0character-set-server=utf8

inception_remote_system_password=123456inception_remote_system_user=root

inception_remote_backup_port=3306inception_remote_backup_host=127.0.0.1inception_support_charset=utf8mb4

inception_enable_nullable=0inception_check_primary_key=1inception_check_column_comment=1inception_check_table_comment=1inception_osc_min_table_size=1inception_osc_bin_dir=/opt/inception/temp

inception_osc_chunk_time=0.1inception_ddl_support=1inception_enable_blob_type=1inception_check_column_default_value=1启动

[root@mysql sql]# nohup/data/inception/bin/Inception --defaults-file=/etc/inc.cnf &[1] 2398[root@mysql sql]# nohup: ignoring input and appending output to `nohup.out'[root@mysql sql]# ps -ef|grep Inception

root2414 23520 0 23:15 pts/0 00:00:00 /opt/inception/bin/Inception --defaults-file=/etc/inc.cnf

root2419 23520 0 23:22 pts/0 00:00:00grep Inception

登录验证

[root@mysql sql]# mysql-uroot -h127.0.0.1 -P6669

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 1Server version: Inception2.1.50 1Copyright (c)2009-2014 Percona LLC and/or its affiliates

Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracleis 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 getvariables;+------------------------------------------+-------------------------------------------------+

| Variable_name | Value |

+------------------------------------------+-------------------------------------------------+

| autocommit | OFF |

| bind_address | * |

| character_set_system | utf8 |

| character_sets_dir | /src/inception-master/debug/sql/share/charsets/ |

| connect_timeout | 10 |

| date_format | %Y-%m-%d |

| datetime_format | %Y-%m-%d %H:%i:%s |

| general_log | ON |

| general_log_file | /opt/inception/inception.log |

| inception_check_autoincrement_datatype | ON |

| inception_check_autoincrement_init_value | ON |

| inception_check_autoincrement_name | ON |

| inception_check_column_comment | ON |

| inception_check_column_default_value | ON |

| inception_check_dml_limit | ON |

| inception_check_dml_orderby | ON |

| inception_check_dml_where | ON |

| inception_check_identifier | ON |

| inception_check_index_prefix | ON |

| inception_check_insert_field | ON |

| inception_check_primary_key | ON |

| inception_check_table_comment | ON |

| inception_check_timestamp_default | ON |

| inception_ddl_support | ON |

| inception_enable_autoincrement_unsigned | ON |

| inception_enable_blob_type | ON |

| inception_enable_column_charset | OFF |

| inception_enable_enum_set_bit | OFF |

| inception_enable_foreign_key | OFF |

| inception_enable_identifer_keyword | OFF |

| inception_enable_not_innodb | OFF |

| inception_enable_nullable | OFF |

| inception_enable_orderby_rand | OFF |

| inception_enable_partition_table | OFF |

| inception_enable_pk_columns_only_int | OFF |

| inception_enable_select_star | OFF |

| inception_enable_sql_statistic | ON |

| inception_max_char_length | 16 |

| inception_max_key_parts | 5 |

| inception_max_keys | 16 |

| inception_max_primary_key_parts | 5 |

| inception_max_update_rows | 10000 |

| inception_merge_alter_table | ON |

| inception_osc_alter_foreign_keys_method | none |

| inception_osc_bin_dir | /opt/inception/temp |

| inception_osc_check_alter | ON |

| inception_osc_check_interval | 5.000000 |

| inception_osc_check_replication_filters | ON |

| inception_osc_chunk_size | 1000 |

| inception_osc_chunk_size_limit | 4.000000 |

| inception_osc_chunk_time | 0.100000 |

| inception_osc_critical_thread_connected | 1000 |

| inception_osc_critical_thread_running | 80 |

| inception_osc_drop_new_table | ON |

| inception_osc_drop_old_table | ON |

| inception_osc_max_lag | 3.000000 |

| inception_osc_max_thread_connected | 1000 |

| inception_osc_max_thread_running | 80 |

| inception_osc_min_table_size | 1 |

| inception_osc_on | ON |

| inception_osc_print_none | ON |

| inception_osc_print_sql | ON |

| inception_osc_recursion_method | processlist |

| inception_password | |

| inception_read_only | OFF |

| inception_remote_backup_host | 127.0.0.1 |

| inception_remote_backup_port | 3306 |

| inception_remote_system_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| inception_remote_system_user | root |

| inception_support_charset | utf8mb4 |

| inception_user | |

| interactive_timeout | 28800 |

| max_allowed_packet | 1073741824 |

| max_connect_errors | 100 |

| max_connections | 151 |

| net_buffer_length | 16384 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| port | 6669 |

| query_alloc_block_size | 8192 |

| query_prealloc_size | 8192 |

| socket | /opt/inception/inc.socket |

| thread_handling | one-thread-per-connection |

| thread_stack | 262144 |

| time_format | %H:%i:%s |

| version | Inception2.1.50 |

| version_comment | Source distribution |

| version_compile_machine | x86_64 |

| version_compile_os | Linux |

| wait_timeout | 28800 |

+------------------------------------------+-------------------------------------------------+

90 rows in set (0.00sec)

mysql>exit

Bye

[root@mysql sql]#

可以看到所有的变量，安装成功！

使用Inception

DDL审核

[root@mysql~]# cat inception.py

#!/usr/bin/python

#-\*-coding: utf-8-\*-import pymysql

sql='/*--user=testdev;--password=testdev;--host=127.0.0.1;--execute=1;--port=3306;*/\

inception_magic_start;\

use testdb;\

CREATE TABLE adaptive_office(idint);\

inception_magic_commit;'try:

conn=pymysql.connect(host='127.0.0.1',user='root',password='123456',db='',port=6669)

cur=conn.cursor()

ret=cur.execute(sql)

result=cur.fetchall()

num_fields=len(cur.description)

field_names= [i[0] for i incur.description]

print field_namesfor row inresult:

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])

[root@mysql~]#

运行结果：

[root@mysql~]# python inception.py

[u'ID', u'stage', u'errlevel', u'stagestatus', u'errormessage', u'SQL', u'Affected_rows', u'sequence', u'backup_dbname', u'execute_time', u'sqlsha1']1 | CHECKED | 0 | Audit completed | None | 2 | CHECKED | 1 | Audit completed | Set engine to innodb for table 'adaptive_office'.

Set charset to one of'utf8mb4' for table 'adaptive_office'.

Set commentsfor table 'adaptive_office'.

Column'id' in table 'adaptive_office'have no comments.

Column'id' in table 'adaptive_office' isnot allowed to been nullable.

Set Default valuefor column 'id' in table 'adaptive_office'Set a primary keyfor table 'adaptive_office'. |常见错误及解决

[root@mysql~]# python inception.py

Traceback (most recent call last):

File"inception.py", line 10, in conn=pymysql.connect(host='192.168.1.123',user='root',password='123456',db='',port=6669)

File"/usr/local/lib/python2.7/site-packages/pymysql/__init__.py", line 88, inConnectreturn Connection(*args, **kwargs)

File"/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 679, in__init__

self.connect()

File"/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 891, inconnect

self._request_authentication()

File"/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1054, in_request_authenticationif int(self.server_version.split('.', 1)[0]) >= 5:

ValueError: invalid literalfor int() with base 10: 'Inception2'解决办法:

[root@mysql~]# vi /usr/local/lib/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/#

命令行不是很友好，追求web界面的请继续，接下来安装archer

我的安装环境:

python3+ mysql5.7 + django1.8安装python3

wget https://www.python.org/ftp/python/3.4.1/Python-3.4.1.tgz

tar -zxvf Python-3.4.1.tgz

cd Python-3.4.1./configure && make &&make install

安装setuptools

[root@mysql~]# wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-19.6.tar.gz#md5=c607dd118eae682c44ed146367a17e26

[root@mysql ~]# tar -zxvf setuptools-19.6.tar.gz

[root@mysql~]# cd setuptools-19.6[root@mysql setuptools-19.6]# python3 setup.py build

[root@mysql setuptools-19.6]# python3 setup.py install

... ...

creating'dist/setuptools-19.6-py3.4.egg' and adding 'build/bdist.linux-x86_64/egg'to it

removing'build/bdist.linux-x86_64/egg'(and everything under it)

Processing setuptools-19.6-py3.4.egg

Copying setuptools-19.6-py3.4.egg to /opt/python3/lib/python3.4/site-packages

Adding setuptools19.6 to easy-install.pth file

Installing easy_install script to/opt/python3/bin

Installing easy_install-3.4 script to /opt/python3/bin

Installed/opt/python3/lib/python3.4/site-packages/setuptools-19.6-py3.4.egg

Processing dependenciesfor setuptools==19.6Finished processing dependenciesfor setuptools==19.6安装pip

[root@mysql~]# wget --no-check-certificate https://pypi.python.org/packages/source/p/pip/pip-8.0.2.tar.gz#md5=3a73c4188f8dbad6a1e6f6d44d117eeb

[root@mysql ~]# tar -zxvf pip-8.0.2.tar.gz

[root@mysql~]# cd pip-8.0.2[root@mysql pip-8.0.2]# python3 setup.py build

[root@mysql pip-8.0.2]# python3 setup.py install

... ...

creating'dist/pip-8.0.2-py3.4.egg' and adding 'build/bdist.linux-x86_64/egg'to it

removing'build/bdist.linux-x86_64/egg'(and everything under it)

Processing pip-8.0.2-py3.4.egg

creating/opt/python3/lib/python3.4/site-packages/pip-8.0.2-py3.4.egg

Extracting pip-8.0.2-py3.4.egg to /opt/python3/lib/python3.4/site-packages

Adding pip8.0.2 to easy-install.pth file

Installing pip3 script to/opt/python3/bin

Installing pip3.4 script to /opt/python3/bin

Installing pip script to/opt/python3/bin

Installed/opt/python3/lib/python3.4/site-packages/pip-8.0.2-py3.4.egg

Processing dependenciesfor pip==8.0.2Finished processing dependenciesfor pip==8.0.2安装Django

使用pip3安装：pip3 install Django==1.8.17安装Crypto和pymysql

[root@mysql3~]# pip3 install Crypto

[root@mysql3~]# pip3 install pycrypto

[root@mysql3~]# pip3 install pymysql

[root@mysql3~]# vi /opt/python3/lib/python3.4/site-packages/pymysql/connections.py

在ifint(self.server_version.split(‘.’, 1)[0]) >= 5: 这一行之前加上以下这一句并保存，记得别用tab键用4个空格缩进：

self.server_version= '5.6.24-72.2-log'修改后如下图：