一、开源SQL项目简介

Inception：一个集审核、执行、备份及生成回滚语句于一身的MySQL自动化运维工具。

SQLAdvisor：基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。
关于inception的意义，inception作者的一段话讲的很棒：

DBA从此就从繁重的审核、登上去执行，出错了很难回滚（如果提前没有备份的话）的被动局面解放了出来，突然发现，做DBA原来可以这么轻松，工作可以不饱和了，那就有更多的自由时间学习、进一步向自动化运维平台的实现等更智能化的方向去发展，是具有里程碑意义的。

二、See项目介绍

以一张简图开始，图片中django web即是See项目，它接收用户处理SQL的请求，交给inception或者sqladvisor后请求到数据库，得到数据库的结果做进一步的处理。

这个是权限逻辑部分，开发、经理，DBA会略有不同， 都是傻瓜式的操作，点点点即可完成。inception审核过的工单，经理确认即为审批，审批后DBA可以在生产上执行。另外还提供了一个测试环境，此环境无需审批流程，发起工单的人即可执行，用于在上线前可以先在测试环境跑一遍sql，之后再生产环境操作。逻辑图如下：

三、环境描述

项目地址：https://github.com/myide/see

Mysql  MySQL不用单独安装，安装Sqladvisor就会安装了
pt-online-schema-change
Inception
Sqladvisor
Redis5.0.6
Nginx1.16
See项目

四、安装pt工具

[root@localhost ~]# yum install -y perl-DBI perl-DBD-mysql perl-Time-HiRes perl-ExtUtils-MakeMaker
[root@localhost ~]# wget https://www.percona.com/get/percona-toolkit.tar.gz
[root@localhost ~]# tar -zxvf percona-toolkit.tar.gz
[root@localhost ~]# cd percona-toolkit-3.0.13
[root@localhost percona-toolkit-3.1.0]# perl Makefile.PL
[root@localhost percona-toolkit-3.1.0]# make
[root@localhost percona-toolkit-3.1.0]# make install
[root@localhost percona-toolkit-3.1.0]# ln -s /usr/local/bin/pt-online-schema-change /usr/bin/

五、安装Inception

1、安装程序

[root@localhost ~]# yum -y install cmake libncurses5-dev libssl-dev g++ bison gcc gcc-c++ openssl-devel ncurses-devel mysql MySQL-python
[root@localhost ~]# wget http://ftp.gnu.org/gnu/bison/bison-2.5.1.tar.gz
[root@localhost ~]# tar -zxvf bison-2.5.1.tar.gz
[root@localhost ~]# cd bison-2.5.1/
[root@localhost bison-2.5.1]# ./configure
[root@localhost bison-2.5.1]# make
[root@localhost bison-2.5.1]# make install

[root@localhost bison-2.5.1]# cd /usr/local/
[root@localhost local]# wget https://github.com/myide/inception/archive/master.zip
[root@localhost local]# unzip master.zip
[root@localhost local]# cd inception-master/
[root@localhost inception-master]# sh inception_build.sh builddir linux

2、修改配置

创建文件 /etc/inc.cnf ,内容如下
[root@localhost inception-master]# vim /etc/inc.cnf
[inception]
general_log=1
general_log_file=inc.log
port=6669
socket=/tmp/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
inception_support_charset=utf8
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=/usr/bin
inception_osc_chunk_time=0.1
inception_ddl_support=1
inception_enable_blob_type=1
inception_check_column_default_value=1

3、启动服务

[root@localhost inception-master]# nohup /usr/local/inception-master/builddir/mysql/bin/Inception –defaults-file=/etc/inc.cnf &

默认监听端口6669
[root@localhost inception-master]# ss -tnlp|grep 6669
[root@localhost inception-master]# lsof -i:6669
COMMAND     PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
Inception 15988 root    4u  IPv6  70067      0t0  TCP *:ircu-5 (LISTEN)

六、安装Sqladvisor

1、克隆代码

[root@localhost ~]# cd /usr/local/src/
[root@localhost src]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

2、安装依赖

[root@localhost src]# yum install -y cmake libaio-devel libffi-devel glib2 glib2-devel bison

# 移除mysql-community库(无用途且和Percona-Server有冲突)
[root@localhost src]# yum remove -y mysql-community-client mysql-community-server mysql-community-common mysql-community-libs

#如果系统自带了MariaDB数据库，那么先进行卸载
[root@localhost src]# rpm -qa |grep mariadb|xargs rpm -e –nodeps
[root@localhost src]# wget http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm -O /tmp/percona-release-0.1-3.noarch.rpm
[root@localhost src]# rpm -ivh /tmp/percona-release-0.1-3.noarch.rpm
[root@localhost src]# yum install -y Percona-Server-server-56 Percona-Server-client-56

设置软链
[root@localhost src]# cd /usr/lib64/
#先检查下该文件是否设置了软链，如没有则设置
[root@localhost src]# ls -l libperconaserverclient_r.so.18

[root@localhost lib64]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

[root@localhost lib64]# cd /usr/local/src/SQLAdvisor/
[root@localhost SQLAdvisor]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
[root@localhost SQLAdvisor]# make && make install

3、编译安装

[root@localhost SQLAdvisor]# cd ./sqladvisor/
[root@localhost SQLAdvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
[root@localhost SQLAdvisor]# make

4、进行测试

[root@localhost SQLAdvisor]# cp /usr/local/src/SQLAdvisor/sqladvisor/sqladvisor /usr/bin/sqladvisor
[root@localhost sqladvisor]# sqladvisor –help

七、安装Redis

1、下载源码包

#将下载的包存储在/opt/soft目录下
[root@localhost ~]# mkdir /opt/soft
[root@localhost ~]# cd /opt/soft
[root@localhost soft]# wget http://download.redis.io/releases/redis-5.0.6.tar.gz

2、安装升级组件

[root@localhost soft]# yum groupinstall ‘Development Tools’
[root@localhost soft]# tar -xf redis-5.0.6.tar.gz -C /usr/local/

3、编译安装

[root@localhost soft]# yum -y install gcc make
[root@localhost soft]# cd /usr/local/
[root@localhost local]# mv redis-5.0.6 redis
[root@localhost local]# cd redis

编译
[root@localhost redis]# make

安装
[root@localhost redis]# make install PREFIX=/usr/local/redis
拷贝执行文件到/usr/local/bin下
[root@localhost ~]# cd /usr/local/redis/bin
[root@localhost bin]# cp * /usr/local/bin/

4、修改配置文件

[root@localhost ~]# vim /usr/local/redis/redis.conf

5、启动Redis

[root@localhost ~]# vim /usr/lib/systemd/system/redis.service

[Unit]
Description=Redis persistent key-value database
After=network.target

[Service]
ExecStart=/usr/local/redis/bin/redis-server /usr/local/redis/redis.conf –supervised systemd
ExecStop=/bin/kill -s QUIT $MAINPID
ExecRepload=/bin/kill -s HUP $MAINPID
Type=notify
User=appadmin
Group=appadmin
RuntimeDirectory=redis
RuntimeDirectoryMode=0755

[Install]
WantedBy=multi-user.target

[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start redis
[root@localhost ~]# systemctl enable redis
[root@localhost ~]# systemctl status redis

八、安装Nginx

说明：安装方式RPM包安装

1、下载RPM包

[root@localhost ~]# cd /opt/soft/
[root@localhost soft]# wget http://nginx.org/packages/centos/7/x86_64/RPMS/nginx-1.16.1-1.el7.ngx.x86_64.rpm
[root@localhost soft]# rpm -ivh nginx-1.16.1-1.el7.ngx.x86_64.rpm
[root@localhost soft]# nginx -v
nginx version: nginx/1.16.1

2、修改配置

[root@localhost ~]# vim /etc/nginx/conf.d/default.conf
server {
listen 80;  # 用户访问端口
access_log    /var/log/access.log;
error_log    /var/log/error.log;

location / {
root /usr/local/seevenv/see-master/frontend/dist/;  # 前端项目文件
try_files $uri $uri/ /index.html =404;
index  index.html;
}

location /static/rest_framework_swagger {  #  前端API静态文件
root /usr/local/seevenv/lib/python3.6/site-packages/rest_framework_swagger/;
}

location /static/rest_framework {  #  前端rest_framework静态文件
root /usr/local/seevenv/lib/python3.6/site-packages/rest_framework/;
}

location /api {
proxy_pass http://127.0.0.1:8090;  # 后端端口
add_header Access-Control-Allow-Origin *;
add_header Access-Control-Allow-Headers Content-Type;
add_header Access-Control-Allow-Headers “Origin, X-Requested-With, Content-Type, Accept”;
add_header Access-Control-Allow-Methods “GET, POST, OPTIONS, PUT, DELETE, PATCH”;
}
}

[root@localhost ~]# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

3、启动服务

[root@localhost ~]# systemctl start nginx
[root@localhost ~]# systemctl enable nginx

4、放通端口

[root@localhost ~]# firewall-cmd –permanent –zone=public –add-port=80/tcp
success
[root@localhost ~]# firewall-cmd –reload
success

九、安装SEE

1、安装依赖

[root@localhost ~]# yum install -y readline readline-devel gcc gcc-c++ zlib zlib-devel openssl openssl-devel sqlite-devel python-devel openldap-clients openldap-devel openssl-devel

2、安装python3

将软件包上传至服务器或者直接wget下载 https://www.python.org/ftp/python/3.6.6/
[root@localhost ~]# cd /opt/soft/
[root@localhost soft]# wget https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz

[root@localhost soft]# tar -xzf Python-3.6.6.tgz
[root@localhost soft]# cd Python-3.6.6
[root@localhost Python-3.6.6]# ./configure –prefix=/usr/local/python3.6 –enable-shared
[root@localhost Python-3.6.6]# make && make install
[root@localhost Python-3.6.6]# ln -s /usr/local/python3.6/bin/python3.6 /usr/bin/python3
[root@localhost Python-3.6.6]# ln -s /usr/local/python3.6/bin/pip3 /usr/bin/pip3
[root@localhost Python-3.6.6]# ln -s /usr/local/python3.6/bin/pyvenv /usr/bin/pyvenv

链接库文件
[root@localhost Python-3.6.6]# cp /usr/local/python3.6/lib/libpython3.6m.so.1.0 /usr/local/lib
[root@localhost Python-3.6.6]# cd /usr/local/lib
[root@localhost lib]# ln -s libpython3.6m.so.1.0 libpython3.6m.so
[root@localhost lib]# echo ‘/usr/local/lib’ >> /etc/ld.so.conf
[root@localhost lib]# /sbin/ldconfig

3、安装Django及See后端

[root@localhost lib]# cd /usr/local/
[root@localhost local]# /usr/local/python3.6/bin/pyvenv seevenv
[root@localhost local]# cd seevenv
[root@localhost local]# source bin/activate

如果通过wget下载很慢，就手动下载下来上传到服务器
(seevenv) [root@localhost seevenv]# wget https://github.com/myide/see/archive/master.zip
(seevenv) [root@localhost seevenv]# unzip master.zip
(seevenv) [root@localhost seevenv]# cd see-master/backend/
(seevenv) [root@localhost seevenv]# pip install -r requirements.txt –trusted-host mirrors.aliyun.com -i https://mirrors.aliyun.com/pypi/simple/

4、配置数据库

4.1：启动并配置密码

#启动后，无法密码即可登入到数据库
[root@localhost ~]# systemctl start mysqld

#解决root无需密码即可登入数据库问题
[root@localhost ~]# mysql
mysql> use mysql;
mysql> select u.`Host`,u.`User`,u.`Password` from `user` u;

可以看到root用户并没有密码

mysql> delete from user where user = “”;
#设置root密码
mysql> update mysql.user set password=password(‘123456′) where user=’root’;
#刷新
mysql> flush privileges;

退出重新登入，检查是否需要密码才能登入

4.2：创建SEE数据库

(seevenv) [root@localhost backend]# mysql -uroot -p123456 -e “create database sqlweb CHARACTER SET utf8;”
(seevenv) [root@localhost backend]# python manage.py makemigrations
(seevenv) [root@localhost backend]# python manage.py migrate

再执行一次migrate
(seevenv) [root@localhost backend]# python manage.py migrate

5、创建inception库

5.1：创建测试库，测试表

(seevenv) [root@localhost backend]# mysql -uroot -p123456  # 登录数据库
mysql> CREATE DATABASE pro1;
mysql> CREATE TABLE IF NOT EXISTS pro1.mytable1 (
`id` INT UNSIGNED AUTO_INCREMENT,
`myname` VARCHAR(10) NOT NULL,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.2：执行测试脚本

先解决报错 ValueError: invalid literal for int() with base 10: ‘Inception2’
def _request_authentication(self):
# https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
if self.server_version.split(‘.’, 1)[0] == ‘Inception2’:
self.client_flag |= CLIENT.MULTI_RESULTS
elif int(self.server_version.split(‘.’, 1)[0]) >= 5:
self.client_flag |= CLIENT.MULTI_RESULTS

解决 Inception始终反馈”Must start as begin statement”的语法错误
(seevenv) [root@localhost backend]# vim /usr/local/seevenv/lib/python3.6/site-packages/pymysql/cursors.py

if not self._defer_warnings:
pass

(seevenv) [root@localhost backend]# python /usr/local/seevenv/see-master/backend/utils/inception_test.py

6、创建管理员用户

用于页面的用户登录
(seevenv) [root@localhost backend]# python manage.py createsuperuser –username admin –email admin@domain.com

十、安装SOAR

[root@localhost ~]# mkdir -p /usr/local/SOAR/bin/
[root@localhost ~]# cp /usr/local/seevenv/see-master/frontend/src/files/soar /usr/local/SOAR/bin
[root@localhost ~]# chmod +x /usr/local/SOAR/bin/soar

十一、启动SEE

检查所有服务是否都启动成功，如果都启动正常则访问页面

#redis服务
[root@localhost ~]# lsof -i:6379

#nginx服务
[root@localhost ~]# lsof -i:80

#inception服务
[root@localhost ~]# lsof -i:6669

#mysql服务
[root@localhost ~]# lsof -i:3306

#see服务
[root@localhost ~]# lsof -i:8090

十二、访问网站

输入ip即可直接访问

十三、使用教程

1、平台使用步骤
1. 创建目标数据库/集群（申请 功能一般是研发人员使用，用来向管理员提交建库申请）
2. 创建组/用户
3. 平台流程设置
4. SQL工单设置
5. 提交SQL工单及后续处理

2、创建目标库和集群

3、创建组和用户

3.1：创建组

3.2：创建用户

4、平台流程设置
这里我们保持默认就好

5、SQL优化使用

5.1：SQL工单添加数据库

5.2：SQL优化选择数据库

添加好之后，即可在SQL优化栏选择我们需要进行优化的库了

十四、问题总结

问题1：

[root@localhost tmp]# yum update percona-release
解决后再次进行安装，问题解决

问题2：
make时出现问题，问题如下：

原因是依赖libperconaserverclient_r，没有找到所以报错
解决方案是进入/usr/lib64/设置软链

[root@localhost src]# cd /usr/lib64/
#先检查下该文件是否设置了软链，如没有则设置
[root@localhost lib64]# ls -l libperconaserverclient_r.so.18
[root@localhost lib64]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so