数据库 / MySQL

开源SQL审核平台SEE部署和使用教程

浅时光 · 5月1日 · 2020年 · · 68743次已读

一、环境文章来源(Source):https://www.dqzboy.com描述

项目地址: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
1605952764 8d79a5148cff6d8

4、进行测试

[root@localhost SQLAdvisor]# cp /usr/local/src/SQLAdvisor/sqladvisor/sqladvisor /usr/bin/sqladvisor
[root@localhost sqladvisor]# sqladvisor --help
1605952929 0c340cb1f0f9bf1

五、安装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
1605953026 2d4115f1d493584
1605953027 0a9022e30b1a2bd
1605953028 f5460e95bde8005

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

[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文章来源(Source):浅时光博客后端

[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用户并没有密码
1605953133 30a7d12715e680f
mysql> delete from user where user = "";
#设置root密码
mysql> update mysql.user set password=password('123456') where user='root';
#刷新
mysql> flush privileges;
  • 退出重新登入,检查是否需要密码才能登入
1605953171 7d97b4867b10dea

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
1605953211 325a826f4d0c2df
  • 再执行一次migrate
(seevenv) [root@localhost backend]# python manage.py migrate
1605953250 6f95a6b58bee85c

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
1605953290 57cc19a884c6772
  • 解决 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
1605953327 953e37a4528568c
(seevenv) [root@localhost backend]# python /usr/local/seevenv/see-master/backend/utils/inception_test.py
1605953360 90bcc454fb6f461

6、创建管理员用户

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

八、安装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

十、访文章来源(Source):https://www.dqzboy.com问网站

输入ip即可直接访问
1605953483 2549b11485ae0f9
1605953492 af51bb141db3cda

十一、使用教程

1、平台使用步骤

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

2、创建目标库和集群

1605953666 a2a33ee10de5d54
1605953667 e60eeeb7142c69d
1605953668 40c5db451ed2821
1605953669 40878cf2a26c3e6
1605953671 8b44c469085d3b6
1605953673 7d6bcc55645a058

3、创建组文章来源(Source):https://www.dqzboy.com和用户

3.1:创建组

1605953929 20daa36f7480a33
1605953930 07f2bdb1c79bc78

3.2:创建用户

1605954143 01caf5dfb9ab5ca
1605954144 5ef061bf5dba6e6
1605954146 5ee325bddb52071

4、平台流程设置

这里我们保持默认就好
1605954285 7c3a0985590b355

5、SQL优化使用

5.1:SQL工单添加数据库

1605954341 38208db995afc03

5.2:SQL优化选择数据库

  • 添加好之后,即可在SQL优化栏选择我们需要进行优化的库了
1605954395 c6fca4b954edabd
文章来源(Source):浅时光博客

十二、问题总结

问题1:
1605954440 808a7648bbd2c87
[root@localhost tmp]# yum update percona-release
  • 解决后再次进行安装,问题解决
问题2:
  • make时出现问题,问题如下:
1605954506 6b85ffb1ec86608
  • 原因是依赖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 
1599059641 ae7af2e544b8e09


0 条回应

必须 注册 为本站用户, 登录 后才可以发表评论!