MySQL / 数据库

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

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

一、环境描述

文章来源(Source):浅时光博客

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

  • M文章来源(Source):浅时光博客ysql  MyS文章来源(Source):https://www.dqzboy.comQL不用单独安装,安装Sqladvisor就会安装了
  • pt-online-schema-change
  • Inception
  • Sqladvisor
  • Redis5.0.6
  • Nginx1.16
  • See项目

二、安装pt工具

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

三、安装Ince文章来源(Source):https://www.dqzboy.comption

1、安装程序

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

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

2、修改配置

  • 创建文件 /etc/inc.cnf ,内容如下
[[email protected] 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、启动服务

[[email protected] inception-master]# nohup /usr/local/inception-master/builddir/mysql/bin/Inception --defaults-file=/etc/inc.cnf &
  • 默认文章来源(Source):浅时光博客文章来源(Source):浅时光博客监听端口6669
[[email protected] inception-master]# ss -tnlp|grep 6669
[[email protected] 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、克隆代码

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

2、安装依赖

[[email protected] src]# yum install -y cmake libaio-devel libffi-devel glib2 glib2-devel bison

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

#如果系统自带了MariaDB数据库,那么先进行卸载
[[email protected] src]# rpm -qa |grep mariadb|xargs rpm -e --nodeps
[[email protected] 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
[[email protected] src]# rpm -ivh /tmp/percona-release-0.1-3.noarch.rpm
[[email protected] src]# yum install -y Percona-Server-server-56 Percona-Server-client-56
文章来源(Source):浅时光博客
  • 设置软链
[[email protected] src]# cd /usr/lib64/
#先检查下该文件是否设置了软链,如没有则设置
[[email protected] src]# ls -l libperconaserverclient_r.so.18
 
[[email protected] lib64]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so 

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

3、编译安装

[[email protected] SQLAdvisor]# cd ./sqladvisor/
[[email protected] SQLAdvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
[[email protected] SQLAdvisor]# make
开源SQL审核平台SEE部署和使用教程-浅时光博客

4、进行测试

[[email protected] SQLAdvisor]# cp /usr/local/src/SQLAdvisor/sqladvisor/sqladvisor /usr/bin/sqladvisor
[[email protected] sqladvisor]# sqladvisor --help
开源SQL审核平台SEE部署和使用教程-浅时光博客

五、文章来源(Source):https://www.dqzboy.com安装Redis

1、下载源码包

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

2、安装升级组件

[[email protected] soft]# yum groupinstall 'Development Tools'
[[email protected] soft]# tar -xf redis-5.0.6.tar.gz -C /usr/local/
文章来源(Source):https://www.dqzboy.com

3、编译安装

[[email protected] soft]# yum -y install gcc make
[[email protected] soft]# cd /usr/local/
[[email protected] local]# mv redis-5.0.6 redis
[[email protected] local]# cd redis
  • 编译文章来源(Source):浅时光博客
[[email protected] redis]# make

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

4、修改配置文件

[[email protected] ~]# vim /usr/local/redis/redis.conf
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客

5、启动Redis

[[email protected] ~]# 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

[[email protected] ~]# systemctl daemon-reload
[[email protected] ~]# systemctl start redis
[[email protected] ~]# systemctl enable redis
[[email protected] ~]# systemctl status redis

六、安装Nginx

  • 说明:安装方式RPM包安装

1、下载RPM包

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

2、修改配置

[[email protected] ~]# 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";
    }
}

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

3、启动服务

[[email protected] ~]# systemctl start nginx
[[email protected] ~]# systemctl enable nginx

4、放通端口

[[email protected] ~]# firewall-cmd --permanent --zone=public --add-port=80/tcp
success
[[email protected] ~]# firewall-cmd --reload
success

七、安装SEE

1、安装依赖

[[email protected] ~]# 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

[[email protected] ~]# cd /opt/soft/
[[email protected] soft]# wget https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz

[[email protected] soft]# tar -xzf Python-3.6.6.tgz 
[[email protected] soft]# cd Python-3.6.6
[[email protected] Python-3.6.6]# ./configure --prefix=/usr/local/python3.6 --enable-shared
[[email protected] Python-3.6.6]# make && make install
[[email protected] Python-3.6.6]# ln -s /usr/local/python3.6/bin/python3.6 /usr/bin/python3
[[email protected] Python-3.6.6]# ln -s /usr/local/python3.6/bin/pip3 /usr/bin/pip3
[[email protected] Python-3.6.6]# ln -s /usr/local/python3.6/bin/pyvenv /usr/bin/pyvenv
  • 链接库文件
[[email protected] Python-3.6.6]# cp /usr/local/python3.6/lib/libpython3.6m.so.1.0 /usr/local/lib
[[email protected] Python-3.6.6]# cd /usr/local/lib
[[email protected] lib]# ln -s libpython3.6m.so.1.0 libpython3.6m.so
[[email protected] lib]# echo '/usr/local/lib' >> /etc/ld.so.conf
[[email protected] lib]# /sbin/ldconfig

3、安装Django及See后端

[[email protected] lib]# cd /usr/local/
[[email protected] local]# /usr/local/python3.6/bin/pyvenv seevenv
[[email protected] local]# cd seevenv
[[email protected] local]# source bin/activate
  • 如果通过wget下载很慢,就手动下载下来上传到服务器
(seevenv) [[email protected] seevenv]# wget https://github.com/myide/see/archive/master.zip
(seevenv) [[email protected] seevenv]# unzip master.zip
(seevenv) [[email protected] seevenv]# cd see-master/backend/
(seevenv) [[email protected] seevenv]# pip install -r requirements.txt --trusted-host mirrors.aliyun.com -i https://mirrors.aliyun.com/pypi/simple/

4、配置数据库

4.1:启动并配置密码

#启动后,无法密码即可登入到数据库
[[email protected] ~]# systemctl start mysqld

#解决root无需密码即可登入数据库问题
[[email protected] ~]# mysql
mysql> use mysql;
mysql> select u.`Host`,u.`User`,u.`Password` from `user` u;
  • 可以看到root用户并没有密码
开源SQL审核平台SEE部署和使用教程-浅时光博客
mysql> delete from user where user = "";
#设置root密码
mysql> update mysql.user set password=password('123456') where user='root';
#刷新
mysql> flush privileges;
  • 退出重新登入,检查是否需要密码才能登入
开源SQL审核平台SEE部署和使用教程-浅时光博客

4.2:创建SEE数据库

(seevenv) [[email protected] backend]# mysql -uroot -p123456 -e "create database sqlweb CHARACTER SET utf8;"
(seevenv) [[email protected] backend]# python manage.py makemigrations
(seevenv) [[email protected] backend]# python manage.py migrate
开源SQL审核平台SEE部署和使用教程-浅时光博客
  • 再执行一次migrate
文章来源(Source):浅时光博客
(seevenv) [[email protected] backend]# python manage.py migrate
文章来源(Source):浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客

5、创建inception库

5.1:创建测试库,测试表

(seevenv) [[email protected] 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
开源SQL审核平台SEE部署和使用教程-浅时光博客
  • 解决 Inceptio文章来源(Source):浅时光博客n始终反馈”Must start as begin statement”的语法错误
(seevenv) [[email protected] backend]# vim /usr/local/seevenv/lib/python3.6/site-packages/pymysql/cursors.py

    if not self._defer_warnings:
        pass
开源SQL审核平台SEE部署和使用教程-浅时光博客
(seevenv) [[email protected] backend]# python /usr/local/seevenv/see-master/backend/utils/inception_test.py
文章来源(Source):https://www.dqzboy.com
开源SQL审核平台SEE部署和使用教程-浅时光博客

6、创建管理员用户

  • 用于页面的用户登录
(seevenv) [[email protected] backend]# python manage.py createsuperuser --username admin --email [email protected]
开源SQL审核平台SEE部署和使用教程-浅时光博客

八、安装SOAR

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

九、启动SEE

  • 检查所有服务是否都启动成功,如果都启动正常则访问页面
#redis服务
[[email protected] ~]# lsof -i:6379

#nginx服务
[[email protected] ~]# lsof -i:80

#inception服务
[[email protected] ~]# lsof -i:6669

#mysql服务
[[email protected] ~]# lsof -i:3306

#see服务
[[email protected] ~]# lsof -i:8090

十、访问网站

输入ip即可直接访问
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客

十一、使用教程

1、平台使用步骤

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

2、创建目标库和集群

开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客

3、创建组和用户

3.1:创建组

开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客

3.2:创建用户

开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客
开源SQL审核平台SEE部署和使用教程-浅时光博客

4、平台流程设置

这里我们保持默认就好
文章来源(Source):https://www.dqzboy.com
开源SQL审核平台SEE部署和使用教程-浅时光博客

5、SQL优化使用

5.1:SQL工单添加数据库

文章来源(Source):https://www.dqzboy.com
开源SQL审核平台SEE部署和使用教程-浅时光博客
文章来源(Source):浅时光博客

5.2:SQL优化选择数据库

  • 添加好之后,即可在SQL优化栏选择我们需要进行优化的库了
开源SQL审核平台SEE部署和使用教程-浅时光博客

十二、问题总结

问题1:
开源SQL审核平台SEE部署和使用教程-浅时光博客
文章来源(Source):https://www.dqzboy.com
[[email protected] tmp]# yum update percona-release
  • 解决后再次进行安装,问题解决
问题2:
  • make时出现问题,问题如下:
开源SQL审核平台SEE部署和使用教程-浅时光博客
  • 文章来源(Source):https://www.dqzboy.com因是依赖libperconaserverclient_r,没有找到所以报错
  • 解决方案是进入/usr/lib64/设置软链
[[email protected] src]# cd /usr/lib64/
#先检查下该文件是否设置了软链,如没有则设置
[[email protected] lib64]# ls -l libperconaserverclient_r.so.18
[[email protected] lib64]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so 
开源SQL审核平台SEE部署和使用教程-浅时光博客
0 条回应
    本站已安全运行: | 耗时 0.798 秒 | 查询 110 次 | 内存 22.50 MB