基于Inception搭建MySQL SQL审核平台Yearing
Inception
1. Inceptionj简介
Inception是一款针对MySQL的SQL语句审核自动化运维工具。使用Inception，将会给DBA带来更大的便利性，将DBA从繁冗的工作中解放出来，做更多的自动化工作，或者从架构方面研究如何更大程度地保证数据库的高可用等。
Inception的文档地址：https://github.com/cookieY/inception-document/tree/master/docs
2. Inception安装
2.1 下载和编译
Inception的源代码可以在Github上直接下载，开源GitHub地址为：
https://github.com/mysql-inception/inception.git
获取Inception源代码：
git clone https://github.com/mysql-inception/inception.git
依赖包安装
编译Inception过程中依赖一些包才可以成功，依赖的包有如下5个：
bison：用来编译语法文件(.yy)。yum收录的是3.0.4，源码编译2.5，官方推荐2.6之前的，所以采用源码编译。 # wget http://ftp.gnu.org/gnu/bison/bison-2.5.tar.bz2 # tar xf bison-2.5.tar.bz2 # cd bison-2.5 # ./configure && make && make install cmake:版本最好用2.8.x # yum -y install cmake ncurses安装 # yum -y install ncurses-devel 安装g++ # yum -y install gcc gcc-c++ 安装openssl # yum -y installopenssl-devel
编译Inception
# cd /opt # mkdir -p /inception/{data,logs} # unzip inception-master.zip -d /inception # cd /inception/inception-master # cmake -DWITH_DEBUG=OFF -DCMAKE_INSTALL_PREFIX=/usr/local/inception -DMYSQL_DATADIR=/inception/data -DWITH_SSL=bundled -DCMAKE_BUILD_TYPE=RELEASE -DMY_MAINTAINER_CXX_WARNINGS="-Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Wno-unused-parameter -Woverloaded-virtual" -DMY_MAINTAINER_C_WARNINGS="-Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Wdeclaration-after-statement" # make && make install
2.2 启动配置
拷贝启动文件
# cp /inception/inception-master/sql/Inception /usr/local/bin/ # cp /inception/inception-master/sql/gen_lex_hash /usr/local/bin/
配置参数文件/etc/inc.cnf
# vi /etc/inc.cnf [inception] general_log=1 general_log_file=inception.log port=6669 socket=/tmp/inc.socket character-set-client-handshake=0 character-set-server=utf8 inception_remote_system_password=mysql inception_remote_system_user=wanbin inception_remote_backup_port=3307 inception_remote_backup_host=127.0.0.1 inception_support_charset=utf8mb4 inception_enable_nullable=1 inception_check_primary_key=1 inception_check_column_comment=0 inception_check_table_comment=1 inception_check_column_default_value=0 inception_max_char_length=30 inception_osc_min_table_size=1 inception_osc_bin_dir=/usr/local/toolkit/bin inception_osc_chunk_time=0.1 inception_enable_blob_type=1
详细参数介绍
port=6669：Inception的服务端口 socket=/tmp/inc.socket：Inception的套接字文件存放位置 character-set-server=utf8：mysql原生参数 #Inception 审核规则 inception_check_insert_field：参数可选范围为ON/OFF，参数默认值为ON，功能是在插入语句中， 用来控制是否指定插入列列表，如果没有指定，并且参数值为ON，则会报错。 inception_check_dml_where：参数可选范围为ON/OFF，参数默认值为ON，功能是在审核DML语句时， 如果发现没有WHERE条件，并且此参数设置为ON，就会报错，否则被忽略 inception_check_dml_limit：参数可选范围为ON/OFF，参数默认值为ON，功能说明是在DML语句中，如果使用了LIMIT表达式， 并且此参数设置为ON，就会报错。这一般用来防止STATEMENT语句主从复制时导致主从不一致的问题。 inception_check_dml_orderby：参数可选范围为ON/OFF，参数默认值为ON，功能是在DML语句中，如果使用了OrderBy表达式， 并且此参数设置为ON，就会报错。这一般用来防止STATEMENT语句主从复制时导致主从不一致的问题。 inception_enable_select_star：参数可选范围为ON/OFF，参数默认值为ON，功能是在遇到查询语句为“select*from”， 并且此参数设置为ON时，不会报错，否则会报错。 inception_enable_orderby_rand：参数可选范围为ON/OFF，参数默认值为ON，功能是语句中出现orderbyrand()时， 用来控制是否报错，设置为ON表示不报错，否则会报错。 inception_enable_nullable：参数可选范围为ON/OFF，参数默认值为ON，功能是在创建或者新增列时，如果列为NULL， 用来控制是否报错，如果设置为ON，表示不报错，否则会报错。 inception_enable_foreign_key：参数可选范围为ON/OFF，参数默认值为ON，功能是在创建表或增加索引时，如果存在外键， 用来控制是否报错，如果设置为ON，则不报错，否则会报错。 inception_max_key_parts：参数可选范围为1～64，参数默认值为5，功能是在一个索引中，用来控制列的最大个数， 如果超过这个数目则报错。在增加索引或新建表时，都会生效。 inception_max_update_rows：参数可选范围为1～MAX，参数默认值为10000，功能是在一个修改语句中，用来控制预计影响的最大行数， 如果超过这个数就报错。这个参数的获取方法是explain，对于有一些语句或在MySQL5.5版本中获取不到相应语句时，预计行数都会是0， 这时这个参数就失效了。 inception_max_keys：参数可选范围为1～1024，参数默认值为16，功能在一个表中，用来控制支持的最大索引数目， 如果超过这个数则报错，不管在新增表，还是新增索引时，都有效。 inception_enable_not_innodb：参数可选范围为ON/OFF，参数默认值为OFF，功能是在新建表指定的存储引擎不是Innodb时， 用来控制是否报错，如果设置为ON，则不报错，否则会报错。 inception_support_charset：参数可选范围为MySQL支持字符集，参数默认值为“utf8mb4”，功能是表示在建表或建库时支持的字符集， 如果需要多个，则用逗号分隔，影响的范围是建表、设置会话字符集、修改表字符集属性等。 inception_check_table_comment：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表及没有设置表注释时，用来控制是否报错，如果设置为ON，则会报错。 inception_check_column_comment：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表或改表加列，并且没有设置列注释时，用来控制是否报错，如果设置为ON，则会报错。 inception_check_primary_key：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表时，如果没有创建主键， 用来控制是否报错，如果设置为ON，就会报错。 inception_enable_partition_table：参数可选范围为ON/OFF，参数默认值为OFF，功能是在建表时， 如果创建了分区表，用来控制是否报错，如果设置为ON，不会报错，否则会报错。 inception_enable_enum_set_bit：参数可选范围为ON/OFF，参数默认值为OFF，功能是在建表或加列时， 如果列对应的数据类型指定的是enum、set、bit数据类型，用来控制是否报错，如果设置为ON，则不报错，否则会报错。 inception_check_index_prefix：参数可选范围为ON/OFF，参数默认值为ON，功能是用来检查新建或建表时的索引前缀， 普通索引的前缀为“idx_”，唯一索引的前缀为“uniq_”，如果设置为ON，并且索引前缀不符合规则，则会报错。 inception_enable_autoincrement_unsigned：参数可选范围为ON/OFF，参数默认值为ON，功能是在新建表时， 如果自增列不是无符号整型的数据类型，用来控制是否报错，如果设置为ON，就报错，否则不报错。 inception_max_char_length：参数可选范围为1～MAX，参数默认值为16，功能是用来控制当char类型的长度大于多少时， 就提示将其转换为VARCHAR。 inception_check_autoincrement_init_value：参数可选范围为ON/OFF，参数默认值为ON，功能是当建表时自增列的值指定不为1时， 用来控制是否报错，如果设置为ON，则报错。 inception_check_autoincrement_datatype：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表时自增列的类型不为int或bigint时， 用来控制是否报错，如果设置为ON，则会报错。 inception_check_timestamp_default：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表时，如果没有为timestamp类型指定默认值， 用来控制是否报错，如果设置为ON，则会报错。 inception_enable_column_charset：参数可选范围为ON/OFF，参数默认值为OFF，功能是在新建表或修改表加列改列时， 用来控制是否能单独指定列的字符集，如果设置为ON，则表示可以设置，不报错。 inception_check_autoincrement_name：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表时，如果指定的自增列名字不为ID， 用来控制是否报错，如果设置为ON，则报错，表示这个列可能存在业务意义，起到提示的作用。 inception_merge_alter_table：参数可选范围为ON/OFF，参数默认值为ON，功能是在同一个Inception任务中，多个语句修改同一个表的语句出现时， 用来控制是否报错，如果设置为ON，则报错，并提示合成一个。 inception_check_column_default_value：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表、修改列、新增列时， 用来控制新的列属性是否要有默认值，如果设置为ON，则说明必须要有默认值，否则会报错。 inception_enable_blob_type：参数可选范围为ON/OFF，参数默认值为ON，功能是在建表、修改列、新增列操作时，如果存在BLOB类型的列， 用来控制是否报错，如果设置为ON，说明支持BLOB类型，则不会报错。 inception_enable_identifer_keyword：参数可选范围为ON/OFF，参数默认值为OFF，功能是在所有审核的SQL语句中， 如果有标识符被写成MySQL的关键字，用来控制是否报错。如果设置为ON，说明支持标识符为关键字，就不会报错，否则会报错。 由于历史原因，这里的identifer写错了，正确写法是identifier，但Inception发布已久，只能将错就错。 auto_commit：参数可选范围为ON/OFF，参数默认值为OFF，功能是为了匹配Python客户端每次自动设置auto_commit=0的， 如果取消则会报错，针对Inception本身没有实际意义。 bind_address：参数可选范围为string，参数默认值为*。这个参数实际上就是MySQL数据库原来的参数， 因为Incpetion没有权限验证过程，那么为了实现更安全的访问，可以给Inception服务器的这个参数设置某些机器（Inception上层的应用程序）的地址， 这样其他非法程序就是不可访问的了，再加上Inception执行选项中的用户名密码，对于后端MySQL就更加安全了。 general_log：参数可选范围为ON/OFF，参数默认值为ON。 这个参数就是原生的MySQL参数，用来记录在Inception服务上执行过哪些语句，定位一些问题等。 general_log_file：参数可选范围为string，参数默认值为inception.log，功能是设置generallog写入的文件路径。 inception_user：参数可选范围为string，参数默认值为empty。这个用户名在配置之后，在连接Inception的选项中可以不指定user， 这样就可以不暴露线上数据库的用户名及密码了，可以作为临时使用的一种方式。 但这个用户现在只能用来审核，也就是说，即使在选项中指定--enable-execute，也不能执行，是只能用来审核的账号。 inception_password：参数可选范围为string，参数默认值为empty。这个参数与上面的参数是一对的，对应的是选项中的password，设置这个参数之后，可以在选项中不指定password。 inception_enable_sql_statistic：参数可选范围为ON/OFF，参数默认值为ON。 用来设置是否支持在统计Inception执行过的语句中，记录各种语句分别占多大比例。 如果参数值为ON，则每次执行的情况都会在备份数据库实例中inception库的statistic表中，以一条记录的形式存储这次操作的统计情况， 每次操作对应一条记录，这条记录中含有的信息是各种类型的语句执行次数情况， 具体的信息请参照52章中“Inception对SQL执行情况的统计”一节。 inception_read_only：参数可选范围为ON/OFF，参数默认值为OFF。 设置当前Inception服务器是否为只读，这是为了防止一些人在具有修改权限的账号时，通过Inception误修改一些数据。 如果inception_read_only设置为ON，则即使打开了enable-execute，同时又有执行权限，也不会去执行，审核完成即返回。 inception_check_identifier：参数可选范围为ON/OFF，参数默认值为ON，功能是打开与关闭Inception对SQL语句中各种名字的检查。 如果设置为ON，则发现名字中存在除数字、字母、下划线之外的字符时，会报Identifier“invalidname”isinvalid,validoptions:[a-z,A-Z,0-9,_]。 inception_max_primary_key_parts：参数可选范围为1～64，参数默认值为5，功能是在创建表时，如果主键所包含的列个数超过这个设置的值，则会报警告。 inception_enable_pk_columns_only_int：参数可选范围为ON/OFF，参数默认值为OFF，功能是如果参数设置为ON，则在创建表或创建主键索引时，会判断包含的列的类型是否只有INT类型的，如果不是则报警告。 #备份服务器信息，注意改成你的机器.用于回滚。 inception_remote_backup_host：指定远程备份MySQL实例的地址。 inception_remote_backup_port：指定远程备份MySQL实例的端口。 inception_remote_system_user：备份时，连接上面指定的MySQL实例时所需要的用户名，这个用户需要有相应的权限，一般包括CREATE、INSERT及SELECT权限。 inception_remote_system_password：备份时，连接备份库时所需要的用户对应的密码。 #inception 支持 OSC 参数 inception_osc_bin_dir=/user/bin：用于指定pt-online-schema-change脚本的位置，不可修改，在配置文件中设置 inception_osc_check_interval=5 #对应OSC参数--check-interval，意义是Sleep time between checks for --max-lag. inception_osc_chunk_size=1000 #对应OSC参数--chunk-size inception_osc_chunk_size_limit=4 #对应OSC参数--chunk-size-limit inception_osc_chunk_time=0.1 #对应OSC参数--chunk-time inception_osc_critical_thread_connected=1000 #对应参数--critical-load中的thread_connected部分 inception_osc_critical_thread_running=80 #对应参数--critical-load中的thread_running部分 inception_osc_drop_new_table=1 #对应参数--[no]drop-new-table inception_osc_drop_old_table=1 #对应参数--[no]drop-old-table inception_osc_max_lag=3 #对应参数--max-lag inception_osc_max_thread_connected=1000 #对应参数--max-load中的thread_connected部分 inception_osc_max_thread_running=80 #对应参数--max-load中的thread_running部分 inception_osc_min_table_size=0 # 这个参数实际上是一个OSC的开关，如果设置为0，则全部ALTER语句都走OSC，如果设置为非0，则当这个表占用空间大小大于这个值时才使用OSC方式。单位为M，这个表大小的计算方式是通过语句："select (DATA_LENGTH + INDEX_LENGTH)/1024/1024 from information_schema.tables where table_schema = 'dbname' and table_name = 'tablename'"来实现的 inception_osc_on=0 #一个全局的OSC开关，默认是打开的，如果想要关闭则设置为OFF，这样就会直接修改 inception_osc_print_none=1 #用来设置在Inception返回结果集中，对于原来OSC在执行过程的标准输出信息是不是要打印到结果集对应的错误信息列中，如果设置为1，就不打印，如果设置为0，就打印。而如果出现错误了，则都会打印 inception_osc_print_sql=1 #对应参数--print
启动Inception
# Inception --defaults-file=/etc/inc.cnf & # mysql -h127.0.0.1 -uroot -P6669 1 [(none)] 10:53:26>inception get variables; +------------------------------------------+---------------------------------------------+ | Variable_name | Value | +------------------------------------------+---------------------------------------------+ | autocommit | OFF | | bind_address | * | | character_set_system | utf8 | | character_sets_dir | /inception/inception-master/share/charsets/ | | connect_timeout | 10 | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | general_log | ON | | general_log_file | inception.log | | inception_check_autoincrement_datatype | ON | | inception_check_autoincrement_init_value | ON | | inception_check_autoincrement_name | ON | | inception_check_column_comment | OFF | | inception_check_column_default_value | OFF | | 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 | OFF | | 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 | ON | | 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 | 30 | | 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 | /usr/local/toolkit/bin | | 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 | 3307 | | inception_remote_system_password | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | | inception_remote_system_user | wanbin | | 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 | /tmp/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.00 sec)
Yearning
1. Yearning简介
Yearning 开源的MYSQL SQL语句审核平台，提供数据库字典查询，查询审计，SQL审核等多种功能。
Yearning 的文档地址：https://supermancookie.com/Yearning-document/
Yearning 是基于Inception的web可视化SQL审核平台,其本身只提供可视化交互页面并不具备sql审核的能力。
2. Yearning安装
从v1.3.0版本开始将采取docker运行的方式，不再提供普通安装教程
2.1 Yearning v1.3.0及以上版本安装
docker-compose安装
# sudo curl -L "https://github.com/docker/compose/releases/download/1.22.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose # sudo chmod +x /usr/local/bin/docker-compose # docker-compose --version docker-compose version 1.22.0, build f46880fe
docker安装
参考CentOS Docker 安装
# service docker start
Yearning安装
# git clone https://github.com/cookieY/Yearning.git # cd /opt/Yearning-master/install/yearning-docker-compose # vim docker-compose.yml 把8080:80 改成 80:80 # docker-compose up -d
请注意本地不要占用8080和8000端口 如需要更改端口可再docker-compose.yml文件中更改，3306和8000端口不可更改！docker-compose并不能确定容器的依赖关系，所以如果执行后无法登陆，请使用docker-compose restart yearning重启容器
cd /opt/Yearning-master/install/yearning-docker-compose docker-compose down 或者 docker-compose stop yearning #停止yearning服务 docker-compose stop db #停止mysql服务 cd /opt/Yearning-master/install/yearning-docker-compose docker-compose up -d 或者 docker-compose start yearning #启动yearning服务 docker-compose start db #启动mysql服务
默认用户：admin
默认密码: Yearning_admin
使用外部mysql
docker-compose中已经包含了mysql且挂载本地目录，不存在容器销毁数据消失的问题，但如果你还是想使用外部mysql,可使用以下步骤：
#新建Yearning库，设置字符集为UTF-8， 然后在Yearning库中导入sql文件 # cd /opt/Yearning-master/install/yearning-docker-compose/init-sql mysql> create database Yearning DEFAULT CHARACTER SET utf8mb4; mysql>use Yearning mysql>source install.sql # docker run -d -e HOST=192.168.56.210 -e MYSQL_ADDR=192.168.56.210 -e MYSQL_USER=wanbin -e MYSQL_PASSWORD=mysql -p80:80 -p8000:8000 registry.cn-hangzhou.aliyuncs.com/cookie/yearning:v1.3.2
登陆后请通过设置页面设置inception及其他配置信息
2.2 Yearning v1.2.0版本安装
参考官档：https://supermancookie.com/Yearning-document/install/
启动
#启动nginx systemctl start nginx #启动Inception Inception --defaults-file=/etc/inc.cnf & #启动yearning cd /opt/Yearning-1.2.0/src/ gunicorn settingConf.wsgi:application -c gunicorn.conf # ps -ef|grep gun root 26174 1 2 13:56 ? 00:00:00 /usr/local/bin/python3.6 /usr/local/bin/gunicorn settingConf.wsgi:application -c gunicorn.conf root 26181 26174 17 13:56 ? 00:00:00 /usr/local/bin/python3.6 /usr/local/bin/gunicorn settingConf.wsgi:application -c gunicorn.conf root 26182 26174 15 13:56 ? 00:00:00 /usr/local/bin/python3.6 /usr/local/bin/gunicorn settingConf.wsgi:application -c gunicorn.conf root 26189 1925 0 13:57 pts/0 00:00:00 grep --color=auto gun
代表启动成功