mysql审计平台和sqladvisor-web SQL优化安装

Inception参考：http://mysql-inception.github.io/inception-document/
Inception参考：https://www.cnblogs.com/chenjiaxin–007/p/8432795.html
参考：美团sql优化建议web:https://github.com/zyw/sqladvisor-web
Yearning参考：https://supermancookie.com/Yearning-document/install/

系统环境: Centos7

Inception安装

1、安装相关依赖包

yum install bison ncurses-libs libncurses5-devel ncurses-devel wget  git cmake openssl gcc-c++ gcc  openssl-devel MySQL-python  -y

注意:centos7 系统自带的bison的版本过高，在后面测试的时候会报错！
查看版本： bison -V
下载地址: wget http://ftp.gnu.org/gnu/bison/bison-2.5.1.tar.gz

cd  bison-2.5.1
./configure
make && make install

安装完成后：bison -V
2、安装inception

cd /opt/
git clone https://github.com/mysql-inception/inception
注意：centos6.5可能git clone报错，升级git版本，升级：依赖yum update nss，就正常了：
升级参考：https://blog.csdn.net/zzpzheng/article/details/52132385
cd inception
sh inception_build.sh debug

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

/opt/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

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=1
general_log_file=/opt/inception/inception.log
port=6669
socket=/opt/inception/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=utf8mb4
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=/opt/inception/temp
inception_osc_chunk_time=0.1
inception_ddl_support=1
inception_enable_blob_type=1
inception_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
root      2414 23520  0 23:15 pts/0    00:00:00 /opt/inception/bin/Inception --defaults-file=/etc/inc.cnf
root      2419 23520  0 23:22 pts/0    00:00:00 grep Inception

登录验证
启动数据库，没有的可以自行安装。

[root@mysql sql]# mysql -uroot -h127.0.0.1 -P6669
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: Inception2.1.50 1
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is 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 get variables;
。。。。。
| version                                  | Inception2.1.50                                 |
| version_comment                          | Source distribution                             |
| version_compile_machine                  | x86_64                                          |
| version_compile_os                       | Linux                                           |
| wait_timeout                             | 28800                                           |
+------------------------------------------+-------------------------------------------------+
rows in set (0.00 sec)
mysql> exit
Bye
[root@mysql sql]#

可以看到所有的变量，安装成功！
命令行使用，另行研究

命令行不是很友好，所以追求web界面，接下来安装Yearning

安装Yearning

我的安装环境:
python3.6 + mysql5.6 +nginx

安装python3.6

 wget https://www.python.org/ftp/python/3.6.3/Python-3.6.3.tgz
tar xvf Python-3.6.3.tgz  
mkdir /usr/local/python3
cd Python-3.6.3/  
./configure --prefix=/usr/local/python3  --with-ssl  && make && make install 

ln -s /usr/local/python3/bin/python3.6 /usr/bin/python3

安装mysql:

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-server
systemctl start mysql

进入mysql
创建数据库并授权

CREATE DATABASE IF NOT EXISTS Yearning default charset utf8 COLLATE utf8_general_ci;
grant all on *.* to 'yearning'@'%' identified by 'yearning';

下载Yearning源码：

git clone https://github.com/cookieY/Yearning.git

cd /opt/Yearning/src
cp deploy.conf.template deploy.conf

更改配置文件：

[root@localhost src]# cat deploy.conf
[mysql]
db = Yearning
address = 127.0.0.1
port = 3306
username =yearning
password =yearning

[host]
ipaddress = 192.168.1.39:8080

[Inception]

ip = 127.0.0.1
port = 6669
user = root
password  = x
backupdb = 127.0.0.1
backupport = 3306
backupuser = root
backuppassword =

[LDAP]
LDAP_SERVER =
LDAP_SCBASE =
LDAP_DOMAIN =
LDAP_TYPE = 1

[email]

username =

password =

smtp_server =


smtp_port = 25

[sql]

limit = 1000

[webhook]

dingding =

注意：deploy.conf中 ipaddress = 192.168.1.39:8080 这个8080端口是对外访问端口，也就是需跟Nginx的端口一至，nginx也需要填8008端口，django python3 manage.py runserver 0.0.0.0:8000，这个8000端口是yearning后端服务端口，一个是前端端口。
安装相应python依赖库

/usr/local/python3/bin/pip3 install -r requirements.txt

初始化数据库

python3 manage.py makemigrations core && python3 manage.py migrate core 

添加初始化用户

echo "from core.models import Account;Account.objects.create_user(username='admin', password='Jlkj#123', group='admin',is_staff=1)" | python3 manage.py shell

初始化权限

echo "from core.models import grained;grained.objects.get_or_create(username='admin', permissions={'person': [], 'ddl': '1', 'ddlcon': [], 'dml': '1', 'dmlcon': [], 'dic': '1', 'diccon': [], 'dicedit': '0', 'user': '1', 'base': '1', 'dicexport': '0'})" | python3 manage.py shell

复制编译好的静态文件到nginx html目录下(如自行更改Nginx静态路径地址则将静态文件复制到对应静态文件目录下)
安装ningx

yum install -y nginx

启动nginx

systemctl start nginx  
#此处$NGINX_HOME为你nginx的路径
cp -rf Yearning/webpage/dist/* $NGINX_HOME/html/

启动django

python3 manage.py runserver 0.0.0.0:8000 

登录：ip:8080
此时使用我们初始化的账号密码：admin ‘Jlkj#123 登录即可

安装sqladvisor-web

下载依赖：

yum install python-devel mysql-community-devel -y

下载源码：

git clone https://github.com/zyw/sqladvisor-web.git

安装依赖：

cd sqladvisor-web
pip install -r requirements.txt

修改数据库配置

vim config.py
SQLALCHEMY_DATABASE_URI = os.environ.get('DEV_DATABASE_URL') or 'mysql://root:root@localhost/sqladvisor'

其中把第二个root修改成你安装Mysql时设置的密码
初始化数据库：

python manage.py db init
python manage.py db migrate
python manage.py db upgrade

启动系统：
解压sqlparser.tar.gz到/usr/local

python manage.py runserver --host 0.0.0.0 &

默认监听5000端口，此时安装完成