Yearning做SQL审核

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/eagle89/article/details/80695233

 系统环境: 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

git clone https://github.com/cookieY/Yearning.git
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


登录验证

启动数据库,没有的可以自行安装。

# mysql -uroot -h127.0.0.1 -P6669
-bash: mysql: 未找到命令

注:没有安装MYSQL 数据库如下:

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

[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

安装环境:
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

将/opt/python3/bin加入环境变量

 

##########将/opt/python3/bin加入环境变量#########

 

echo 'export PATH=$PATH:/opt/python3.2.3/bin/' >> ~/.bashrc
.  ~/.bashrc
加入我们需要用它来代替系统的默认的 python,即运行 python就是运行 python3,而且要是 yum可以正常使用,我们可以执行如下步骤:

mv /usr/bin/python /usr/bin/python-old-backup

ln -s /opt/python3.2.3/bin/python3 /usr/bin/python

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

vim /usr/bin/yum
#将文件开头的
#!/usr/bin/python 修改为 #/usr/bin//usr/bin/python-old-backup

###############################

$$$$yum 出现 except OSError, e: ^ SyntaxError: invalid syntax$$$$

错误原因:

       系统中装有多个版本的Python,Python脚本运行的时候版本冲突。

     解决办法:(以下两步都要执行)

    1.  vim /usr/libexec/urlgrabber-ext-down
        将/usr/bin/python改为/usr/bin/python2.7。
    2. vim /usr/bin/yum-config-manager
      解决办法同上: #!/usr/bin/python换成 #!/usr/bin/python2.7 

 

 

安装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 =

 

 

 

 

#####编辑 Yearning/src/deploy.conf##########

 

[mysql]
db = 所创建的库名
address = 数据库地址
port = 数据库端口
password = 数据库密码
username = 数据库用户

[host]
ipaddress = 服务器ip地址:端口 (涉及跨域十分重要!!设置不正确将无法登陆!!)
            例如 本机地址为192.168.1.2 nginx端口为80(端口可自己定义,定义什么端口就填写什么端口)
            则应填写为 192.168.1.2:80 之后只能通过该地址访问平台其他地址一概无效!
            注意!访问的是nginx的端口不是后端8000端口!
            (仅对于v1.2版本以下版本,对于1.2以上版本此配置信息只用来消息推送时显示平台地址)


特别注意! 以下配置信息为v1.2版本以下配置信息。v1.2版本及以上版本不存在以下配置信息

[Inception]
ip = Inception地址
port = Inception端口
user = Inception用户名
password  = Inception密码
backupdb = 备份数据库地址
backupport = 备份数据库端口
backupuser = 备份数据库用户名
backuppassword = 备份数据库密码

[LDAP] LDAP相关设置
LDAP_SERVER = LDAP服务地址
LDAP_SCBASE = LDAP dc 设置 如 dc=xxx,dc=com
LDAP_DOMAIN = LDAP域名 如 xxx.com
LDAP_TYPE = 1  1 通过域名进行ldap认证  2 通过uid进行ldap认证 3 通过cn进行ldap认证

[email] 邮箱推送相关设置
username = 邮箱发件账号 如 xxxx@163.com
password = 邮箱发件账号密码
smtp_server = 邮箱stmp地址, 具体地址请咨询对应邮箱提供者

[sql]

limit = 100 查询最高的Limit数量

[webhook]

dingding =  权限提交,查询工单等其他非sql工单提交消息推送钉钉webhook

 

###################

 

 

安装相应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
######################

1.添加Nginx到YUM源

添加CentOS 7 Nginx yum资源库,打开终端,使用以下命令:

sudo rpm -Uvh http://nginx.org/packages/centos/7/noarch/RPMS/nginx-release-centos-7-0.el7.ngx.noarch.rpm

2.安装Nginx

在你的CentOS 7 服务器中使用yum命令从Nginx源服务器中获取来安装Nginx:

sudo yum install -y nginx

Nginx将完成安装在你的CentOS 7 服务器中。

3.启动Nginx

刚安装的Nginx不会自行启动。运行Nginx:

sudo systemctl start nginx.service

如果一切进展顺利的话,现在你可以通过你的域名或IP来访问你的Web页面来预览一下Nginx的默认页面;

nginx_default

如果看到这个页面,那么说明你的CentOS 7 中 web服务器已经正确安装。

CentOS 7 开机启动Nginx

sudo systemctl enable nginx.service

更多systemctl命令可查看《systemctl命令用法

Nginx配置信息

网站文件存放默认目录

/usr/share/nginx/html

网站默认站点配置

/etc/nginx/conf.d/default.conf

自定义Nginx站点配置文件存放目录

/etc/nginx/conf.d/

Nginx全局配置

/etc/nginx/nginx.conf

 

Nginx启动

nginx -c nginx.conf

 

在这里你可以改变设置用户运行Nginx守护程序进程一样,和工作进程的数量得到了Nginx正在运行,等等。

Linux查看公网IP

您可以运行以下命令来显示你的服务器的公共IP地址:

ip addr show eth0 | grep inet | awk '{ print $2; }' | sed 's/\/.*$//'

 

或者

最后安装nginx
下载Nginx: wget http://nginx.org/download/nginx-1.3.11.tar.gz /opt/
  ./configure && make && make install
 启动nginx
启动:/usr/local/nginx/sbin/nginx
重启:kill -HUP `cat /usr/local/nginx/logs/nginx.pid`
停止nginx:pkill -9 nginx

##########################


启动nginx

systemctl start nginx  
cp -rf Yearning/webpage/dist/* $NGINX_HOME/html/

启动django
python3 manage.py runserver 0.0.0.0:8000
注:

是否受防火墙安全控制等。

将iptables和selinux关闭
以下4条命令清除iptables的配置

iptables -F
iptables -F -t nat
iptables -X
iptables -X -t nat

setenforce 0 #关闭selinux

访问首页http://IP

 

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

 

注:添加初始化用户,登录后如下图所示(如果登录不了,注意看#2.9是否增加了8000的安全组策略)

2.9、修改阿里云的安全组策略(解决不能用公网IP访问的问题,请一定注意增加80和8000两个端口,否则还是登录不了),参考以下文章
http://blog.csdn.net/LJFPHP/article/details/78670459

 

 

 

 

三、archer安装

开发语言和推荐环境

python:3.4

 

wget https://www.python.org/ftp/python/3.4.1/Python-3.4.1.tgz

django:1.8
mysql : 5.6及以上
linux : 64位linux操作系统均可

环境准备

1、克隆代码到本地: git clone https://github.com/jly8866/archer.git 或 下载zip包

git clone -b archer-2.0 https://github.com/jly8866/archer.git

详细说明文档:https://gitee.com/ilanni/archer

2、安装mysql 5.6实例,请注意保证mysql数据库默认字符集为utf8或utf8mb4
3、安装inception

1、安装所需软件和模块

1)安装python3 (前面有)

2)安装setuptools


 

[root@mysql3 ~]# wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-19.6.tar.gz#md5=c607dd118eae682c44ed146367a17e26

[root@mysql3 ~]# tar -zxvf setuptools-19.6.tar.gz

[root@mysql3 ~]# cd setuptools-19.6

[root@mysql3 setuptools-19.6]# python3 setup.py build

[root@mysql3 setuptools-19.6]# python3 setup.py install

... ...

creating 'dist/setuptools-19.6-py3.4.egg' and adding 'build/bdist.linux-x86_64/egg' to it

removing 'build/bdist.linux-x86_64/egg' (and everything under it)

Processing setuptools-19.6-py3.4.egg

Copying setuptools-19.6-py3.4.egg to /opt/python3/lib/python3.4/site-packages

Adding setuptools 19.6 to easy-install.pth file

Installing easy_install script to /opt/python3/bin

Installing easy_install-3.4 script to /opt/python3/bin

Installed /opt/python3/lib/python3.4/site-packages/setuptools-19.6-py3.4.egg

Processing dependencies for setuptools==19.6

Finished processing dependencies for setuptools==19.6

可以看到安装到python3下面了

3)安装pip


 

[root@mysql3 ~]# wget --no-check-certificate https://pypi.python.org/packages/source/p/pip/pip-8.0.2.tar.gz#md5=3a73c4188f8dbad6a1e6f6d44d117eeb

[root@mysql3 ~]# tar -zxvf pip-8.0.2.tar.gz

[root@mysql3 ~]# cd pip-8.0.2

[root@mysql3 pip-8.0.2]# python3 setup.py build

[root@mysql3 pip-8.0.2]# python3 setup.py install

... ...

creating 'dist/pip-8.0.2-py3.4.egg' and adding 'build/bdist.linux-x86_64/egg' to it

removing 'build/bdist.linux-x86_64/egg' (and everything under it)

Processing pip-8.0.2-py3.4.egg

creating /opt/python3/lib/python3.4/site-packages/pip-8.0.2-py3.4.egg

Extracting pip-8.0.2-py3.4.egg to /opt/python3/lib/python3.4/site-packages

Adding pip 8.0.2 to easy-install.pth file

Installing pip3 script to /opt/python3/bin

Installing pip3.4 script to /opt/python3/bin

Installing pip script to /opt/python3/bin

Installed /opt/python3/lib/python3.4/site-packages/pip-8.0.2-py3.4.egg

Processing dependencies for pip==8.0.2

Finished processing dependencies for pip==8.0.2

4)安装Django

使用pip3安装:pip3 install Django==1.8.17

wget  http://mirrors.aliyun.com/pypi/packages/58/4a/26f99e2b094a2edefb2cffbcdbaca9207835d4b2765dd8afa553a1714ea6/Django-2.0.7.tar.gz

 

或者单独下载安装:


 

[root@mysql3 ~]# wget https://pypi.python.org/packages/44/41/bf93934082e9897a56a591a67bacbd9fb74e71244f3f42253432a9e627e6/Django-1.8.17.tar.gz#md5=e76842cdfbcb31286bd44f51e087a04c

[root@mysql3 ~]# tar -zxvf Django-1.8.17.tar.gz

[root@mysql3 ~]# cd Django-1.8.17

[root@mysql3 Django-1.8.17]# python3 setup.py install

[root@mysql3 Django-1.8.17]# python3

Python 3.4.1 (default, Apr 15 2017, 14:11:11)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-18)] on linux

Type "help", "copyright", "credits" or "license" for more information.

>>> django.VERSION

(1, 8, 17, 'final', 0)

>>> exit()

注1:Django支持python版本对照表

Django版本

Python版本

1.5.x

2.6.5

2.7

3.2

3.3

1.6.x

2.6

2.7

3.2

3.3

1.7.x

2.7

3.2

3.3

3.4

1.8.x

2.7

3.2

3.3

3.4

3.5

LTS

1.9.x

2.7

3.4

3.5

1.10.x

2.7

3.4

3.5

1.11.x

开发推荐

2.7

3.4

3.5

3.6

LTS

2.0.x

3.4

3.5

3.6

注1:查看python版本和django版本

python --version

在python shell中:

  import sys

  sys.version

  import django

  django.VERSION

 

5)安装GIT

yum -y install git

6)安装Crypto和pymysql

[root@mysql3 ~]# pip3 install Crypto
[root@mysql3 ~]# pip3 install pycrypto

[root@mysql3 ~]# pip3 install pymysql

 

[root@mysql3 ~]# vi /opt/python3/lib/python3.4/site-packages/pymysql/connections.py
在if int(self.server_version.split(‘.’, 1)[0]) >= 5: 这一行之前加上以下这一句并保存,记得别用tab键用4个空格缩进:
self.server_version = ‘5.6.24-72.2-log’
修改后如下图:
图1

2、配置archer

1、将archer-master源码解压到/opt/archer目录


 

[root@mysql3 archer-master]# mkdir -p /opt/archer

[root@mysql3 archer-master]# scp -r /src/archer-master /opt/archer/

[root@mysql3 archer-master]# cd /opt/archer/

[root@mysql3 archer]# ls

archer debug.sh docs manage.py README.md requirements.txt screenshots sql startup.sh stop.sh

2、创建archer DB和inception DB


 

create database archer;(CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;)

grant all on archer.* to archer_rw@'%' identified by 'archer_rw';
 create database inception_db;
 grant all on inception_db.* to incep_rw@'%' identified by 'incep_rw';
flush privileges;

注:设置数据库字符集

1.alter database test default character set = utf8;

2.create database mydb character set utf8;

修改单个表的字符集

1.alter table test1 default character set = utf8;

2.alter table test2 convert to character set utf8;

使用MySQL字符集的建议:建立数据库/表和进行数据库操作时尽量显式指出使用的字符集,而不是依赖于MySQL的默认设置;

或者https://blog.csdn.net/eagle89/article/details/80776741

 

3、修改/opt/archer/archer/settings.py,如下内容:


 

#该项目本身的mysql数据库地址

DATABASES = {

'default': {

'ENGINE': 'django.db.backends.mysql',

'NAME': 'archer',

'USER': 'archer_rw',

'PASSWORD': 'archer_rw',

'HOST': '192.168.1.3',

'PORT': '3306'

}

}

#inception组件所在的地址

INCEPTION_HOST = '192.168.1.123'

INCEPTION_PORT = '6669'

#查看回滚SQL时候会用到,这里要告诉archer去哪个mysql里读取inception备份的回滚信息和SQL.

#注意这里要和inception组件的inception.conf里的inception_remote_XX部分保持一致.

INCEPTION_REMOTE_BACKUP_HOST='192.168.1.3'

INCEPTION_REMOTE_BACKUP_PORT=3306

INCEPTION_REMOTE_BACKUP_USER='incep_rw'

INCEPTION_REMOTE_BACKUP_PASSWORD='incep_rw'

4、通过model创建archer本身的数据库表

总结:

 

pip3 install django_admin_bootstrapped
python3 manage.py makemigrations
python3 manage.py makemigrations sql
python3 manage.py migrate

######django models error#######

 

问题1.将主键id改成自动生成的python3 manage.py migrate时报下面的错误

django.db.utils.InternalError: (1091, "Can‘t DROP ‘id‘; check that column/key exists")

 

我猜测是因为我改了两个表的主键,其中A表主键是B表的外键,删除会有问题

 

解决方法:

1.删除app下migrations文件夹中000开头的记录文件

2.删掉app对应的数据库drop database 数据库名;

4.重新建立刚刚删除的数据库create database 数据库名;

3.然后重新执行修改数据库的命令:

  python3 manage.py makemigrations

  python3 manage.py migrate

 

问题2:django向数据库中添加中文时报错

django.db.utils.InternalError: (1366, "Incorrect string value: ‘\\xE7\\x99\\xBB\\xE5\\xBD\\x95...‘ for column ‘chinessname‘ at row 1")

 

解决方法:创建数据库的时候设置编码格式

 CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 

问题3:增加主键没生效,每次都只是执行了update

django的models通过AutoField设置自增主键后,没有递增,代码如下

class elements(models.Model):
    elementid=models.AutoField(primary_key=True,default=1)

    pageid=models.ForeignKey(page)

 

解决方法:去掉default=1

如果有default,它实现的是update,只有去掉default,才执行insert

 

问题4:models存储包含外键的数据时,报错

ValueError: Cannot assign "‘12‘": "elements.pageid" must be a "page" instance.

 

models的数据结构:

class page(models.Model):
    pageid=models.AutoField(primary_key=True)

    

class elements(models.Model):
    elementid=models.AutoField(primary_key=True)

    pageid=models.ForeignKey(page)

 

解决方法:

page对象的paged是elements的外键,在使用save时,需要传递一个page对象实例

elements(pageid=page.objects.get(pageid=xxx).save()

 

###############

 

 


 

[root@mysql3]# cd /opt/archer

[root@mysql3 archer]# python3 manage.py makemigrations

No changes detected

[root@mysql3 archer]# python3 manage.py makemigrations sql

Migrations for 'sql':

0001_initial.py:

- Create model users

- Create model master_config

- Create model workflow

[root@mysql3 archer]# python3 manage.py migrate

Operations to perform:

Synchronize unmigrated apps: messages, staticfiles

Apply all migrations: sessions, auth, contenttypes, sql, admin

Synchronizing apps without migrations:

Creating tables...

Running deferred SQL...

Installing custom SQL...

Running migrations:

Rendering model states... DONE

Applying contenttypes.0001_initial... OK

Applying contenttypes.0002_remove_content_type_name... OK

Applying auth.0001_initial... OK

Applying auth.0002_alter_permission_name_max_length... OK

Applying auth.0003_alter_user_email_max_length... OK

Applying auth.0004_alter_user_username_opts... OK

Applying auth.0005_alter_user_last_login_null... OK

Applying auth.0006_require_contenttypes_0002... OK

Applying sql.0001_initial... OK

Applying admin.0001_initial... OK

Applying sessions.0001_initial... OK

查看表是否创建成功:

[root@mysql3 archer]# mysql -uarcher_rw -parcher_rw -h192.168.1.3 -P3306 -Darcher -e "show tables;"

Warning: Using a password on the command line interface can be insecure.

+----------------------------+

| Tables_in_archer |

+----------------------------+

| auth_group |

| auth_group_permissions |

| auth_permission |

| django_admin_log |

| django_content_type |

| django_migrations |

| django_session |

| sql_master_config |

| sql_users |

| sql_users_groups |

| sql_users_user_permissions |

| sql_workflow |

+----------------------------+

5、创建django admin管理员


 

[root@mysql3 archer]# python3 manage.py createsuperuser

Username: admin

Email address: river_dba@foxmail.com

Password: admin123

Password (again): admin123

Superuser created successfully.

该用户可以登录django admin来管理model。

启动acher


 

[root@mysql3 archer]# vi debug.sh

#!/bin/bash

python3 manage.py runserver 0.0.0.0:9123

[root@mysql3 archer]# sh debug.sh

Performing system checks...

System check identified no issues (0 silenced).

April 15, 2017 - 16:39:11

Django version 1.8.17, using settings 'archer.settings'

Starting development server at http://0.0.0.0:9123/

Quit the server with CONTROL-C.

创建archer系统登录用户

通过浏览器访问http://192.168.1.123:9123/admin/sql/users/可以看到django登录界面:图2使用上面第5步创建的用户名密码(admin/admin123)登录:图3点击右侧Add用户配置,用户名密码自定义,至少创建一个工程师和一个审核人(用admin用户可以登录)后续新的工程师和审核人用户请用LDAP导入sql_users表或django admin增加图4

配置主库地址

通过浏览器访问http://192.168.1.123:9123/admin/sql/master_config点击右侧Add master_config。这一步是为了告诉archer你要用inception去哪些mysql主库里执行SQL,所用到的用户名密码、端口等。图5图6

正式访问主页

http://192.168.1.123:9123/login/图7登录成功后:图8

遇到问题及解决

查看回滚SQL时报错

图9debug日志报错:


 

[18/Apr/2017 11:49:50] "GET /detail/2/ HTTP/1.1" 200 7474

Mysql Error 1142: SELECT command denied to user 'incep_rw'@'192.168.1.123' for table '$_$inception_backup_information$_$'

Error: returned listTables more than 1.

[18/Apr/2017 11:50:01] "GET /rollback/?csrfmiddlewaretoken=8s6iaPsMtgX1Qvd0lf8vDOXe0K9hPecp&workflowid=2 HTTP/1.1" 500 73965

解决办法:给incep_rw用户赋予select、create、insert权限


 

root@mysql 04:42:04>grant select,create,insert on *.* to incep_rw@'%' identified by 'incep_rw';

Query OK, 0 rows affected (0.00 sec)

root@mysql 04:54:23>flush privileges;

Query OK, 0 rows affected (0.00 sec)

CREATE权限用于创建表或者库的,INSERT权限用于插入备份数据的,SELECT权限用于查询回滚SQL。再次执行update语句,查看回滚SQL为空,审核结果显示:图9解决办法:为mysql添加server_id

 

在线查询&脱敏查询

  1. settings中QUERY改为True
  2. 到【后台数据管理】-【从库地址配置】页面添加从库信息
  3. 到【后台数据管理】-【工作流配置】页面配置审核流程
  4. 用户申请权限、审核通过后即可进行在线查询
  5. 如需要使用动态脱敏,请将settings中DATA_MASKING_ON_OFF改为True,并且到【后台数据管理】-【脱敏配置】页面配置脱敏规则和字段

慢日志管理

  1. settings中SLOWQUERY改为True

  2. 安装percona-toolkit(版本>3.0),以centos为例

    yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm 
    yum -y install percona-toolkit.x86_64 
    install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm 
    yum -y install percona-toolkit.x86_64 
    
  3. 使用src/script/mysql_slow_query_review.sql创建慢日志收集表到archer数据库

  4. 将src/script/analysis_slow_query.sh部署到各个监控机器,注意修改脚本里面的 hostname="${mysql_host}:${mysql_port}"与archer主库配置信息一致,否则将无法筛选到相关记录

 

阿里云rds管理

  1. settings修改配置文件ALIYUN_RDS_MANAGE=True
  2. 安装模块
pip3 install aliyun-python-sdk-core==2.3.5
pip3 install aliyun-python-sdk-core-v3==2.5.3
pip3 install aliyun-python-sdk-rds==2.1.1
install aliyun-python-sdk-core==2.3.5
pip3 install aliyun-python-sdk-core-v3==2.5.3
pip3 install aliyun-python-sdk-rds==2.1.1
  1. 在【后台数据管理】-【阿里云认证信息】页面,添加阿里云账号的accesskey信息,重新启动服务
  2. 在【后台数据管理】-【阿里云rds配置】页面,添加实例信息,即可实现对阿里云rds的进程管理、慢日志管理

admin后台加固,防暴力破解

  1. patch目录下,名称为:django_1.8.17_admin_secure_archer.patch
  2. 使用命令:
patch  python/site-packages/django/contrib/auth/views.py django_1.8.17_admin_secure_archer.patch
.py django_1.8.17_admin_secure_archer.patch

集成ldap

  1. 修改配置文件ENABLE_LDAP=True,安装相关模块,可以启用ldap账号登陆,以centos为例
yum install openldap-devel
pip install django-auth-ldap==1.3.0
install openldap-devel
pip install django-auth-ldap==1.3.0
  1. 如果使用了ldaps,并且是自签名证书,需要打开settings中AUTH_LDAP_GLOBAL_OPTIONS的注释
  2. settings中以AUTH_LDAP开头的配置,需要根据自己的ldap对应修改

部分功能使用说明

  1. 用户角色配置
    在【后台数据管理】-【用户配置】页面管理用户,或者使用LADP导入,至少拥有一个工程师角色(engineer)、一个DBA角色才可以进行SQL上线工程师可以发起SQL上线,审核人/DBA进行审核,DBA进行执行,超级管理员可以登录admin界面进行管理
  2. 配置主库地址
    在【后台数据管理】-【主库地址配置】页面管理主库
    主库地址用于SQL上线,DDL、DML、慢日志查看、SQL优化等功能
  3. 配置从库地址
    在【后台数据管理】-【从库地址配置】页面管理从库
    从库地址用于SQL查询功能
  4. 配置查询权限审核流程
    在【后台数据管理】-【工作流配置】页面管理审核流程

 

archer安装部署
***************************************************************************
--版本
python:3.4
django:1.8
mysql : 5.6以上
linux : 64位linux

--安装archer
mkdir -p /opt/archer_web/
cd /opt/archer_web
yum -y install git
git clone https://github.com/jly8866/archer.git
 
--安装python3
cd /opt 
wget https://www.python.org/ftp/python/3.4.1/Python-3.4.1.tgz
tar -zxvf Python-3.4.1.tgz
cd Python-3.4.1
./configure --prefix=/opt/python3 && make && make install
vim /etc/profile
添加:export  PATH="/opt/python3/bin:$PATH"
source /etc/profile
python3
 
--安装setuptools
cd /opt
wget --no-check-certificate  https://pypi.python.org/packages/source/s/setuptools/setuptools-19.6.tar.gz#md5=c607dd118eae682c44ed146367a17e26
tar -zxvf setuptools-19.6.tar.gz
cd setuptools-19.6
python3 setup.py build
python3 setup.py install
可以看到安装到python3下面了

--安装pip
cd /opt
wget --no-check-certificate  https://pypi.python.org/packages/source/p/pip/pip-8.0.2.tar.gz#md5=3a73c4188f8dbad6a1e6f6d44d117eeb
tar -zxvf pip-8.0.2.tar.gz
cd pip-8.0.2
python3 setup.py build
python3 setup.py install
 
--安装Django 
cd /opt
wget https://pypi.python.org/packages/44/41/bf93934082e9897a56a591a67bacbd9fb74e71244f3f42253432a9e627e6/Django-1.8.17.tar.gz#md5=e76842cdfbcb31286bd44f51e087a04c
tar -zxvf Django-1.8.17.tar.gz
cd Django-1.8.17
python3 setup.py install
python3
>>> import django
>>> django.VERSION
(1, 8, 17, 'final', 0)
>>> exit()
 
--安装Crypto和pymysql
pip3 install Crypto
pip3 install pycrypto
pip3 install pymysql
这里遇到了错误
# pip3 install Crypto
-bash: ./pip3: /usr/bin/python3: bad interpreter: No such file or directory
解决方法:
python3.4 -m pip install Crypto
或者
vim /opt/python3/bin/pip3
将#!/usr/bin/python3改为#!/opt/python3/bin/python3.4
# vim /opt/python3/lib/python3.4/site-packages/pymysql/connections.py
在if int(self.server_version.split(‘.’, 1)[0]) >= 5: 这一行之前加上以下这一句并保存,记得别用tab键用4个空格缩进:
self.server_version = '5.6.24-72.2-log'
 
--配置archer
cd /opt/archer_web/archer

--创建archer DB和inception DB
--进入安装的mysql5.6
mysql -uroot -p'P@ssw0rd' -S /mydata/mysql/mysql3306/mysql.sock
--grant all privileges on *.* to root@'%' identified by 'P@ssw0rd';
create database archer default character set utf8;
grant all on archer.* to archer_rw@'%' identified by 'P@ssw0rd';
create database inception_db default character set utf8;
grant all on inception_db.* to incep_rw@'%' identified by 'P@ssw0rd';
grant select,create,insert on *.* to incep_rw@'%' identified by 'P@ssw0rd';
create database test default character set utf8;    #测试库 建在审核库里了 仅做测试
grant all on test.* to test@'%' identified by 'P@ssw0rd';
flush privileges;
########创建archer DB和inception DB#############
 create database archer;
grant all on archer.* to archer_rw@'%' identified by 'archer_rw';
create database inception_db;
grant all on inception_db.* to incep_rw@'%' identified by 'incep_rw';
flush privileges;
########################
 --修改/opt/archer_web/archer/archer/settings.py,如下内容:
vi /opt/archer_web/archer/archer/settings.py
#该项目本身的mysql数据库地址
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'archer',
        'USER': 'archer_rw',
        'PASSWORD': 'P@ssw0rd',
        'HOST': '127.0.0.1',
        'PORT': '3306'
    }
}
#inception组件所在的地址
INCEPTION_HOST = '10.10.3.70'
INCEPTION_PORT = '6669'
#查看回滚SQL时候会用到,这里要告诉archer去哪个mysql里读取inception备份的回滚信息和SQL.
#注意这里要和inception组件的inception.conf里的inception_remote_XX部分保持一致.
INCEPTION_REMOTE_BACKUP_HOST='10.10.3.70'
INCEPTION_REMOTE_BACKUP_PORT=3306
INCEPTION_REMOTE_BACKUP_USER='incep_rw'
INCEPTION_REMOTE_BACKUP_PASSWORD='P@ssw0rd'      这个用户就是在部署inception使用的用户。(这用户应该是和上面创建的incep_rw是一个用户,可以不用创建incep_rw)

--通过model创建archer本身的数据库表
cd /opt/archer_web/archer
pip3 install django_admin_bootstrapped
python3 manage.py makemigrations
python3 manage.py makemigrations sql
python3 manage.py migrate
进入数据库查看archer库下的表是否存在
##########创建archer本身的数据库表#####################
[root@192-168-1-34 archer]# python3 manage.py makemigrations
No changes detected
[root@192-168-1-34 archer]# python3 manage.py makemigrations sql
No changes detected in app 'sql'
[root@192-168-1-34 archer]# python3 manage.py migrate
Operations to perform:
  Synchronize unmigrated apps: messages, django_admin_bootstrapped, staticfiles
  Apply all migrations: admin, sessions, auth, sql, django_apscheduler, contenttypes
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0001_initial... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying sql.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying django_apscheduler.0001_initial... OK
  Applying django_apscheduler.0002_auto_20180412_0758... OK
  Applying sessions.0001_initial... OK
[root@192-168-1-34 archer]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| archer             |
| inception_db       |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use archer;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_archer                      |
+---------------------------------------+
| aliyun_access_key                     |
| aliyun_rds_config                     |
| auth_group                            |
| auth_group_permissions                |
| auth_permission                       |
| data_masking_columns                  |
| data_masking_rules                    |
| django_admin_log                      |
| django_apscheduler_djangojob          |
| django_apscheduler_djangojobexecution |
| django_content_type                   |
| django_migrations                     |
| django_session                        |
| query_log                             |
| query_privileges                      |
| query_privileges_apply                |
| sql_master_config                     |
| sql_slave_config                      |
| sql_users                             |
| sql_users_groups                      |
| sql_users_user_permissions            |
| sql_workflow                          |
| workflow_audit                        |
| workflow_audit_detail                 |
| workflow_audit_setting                |
+---------------------------------------+
25 rows in set (0.00 sec)

mysql> show create table aliyun_rds_config;
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                          |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aliyun_rds_config | CREATE TABLE `aliyun_rds_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rds_dbinstanceid` varchar(100) NOT NULL,
  `cluster_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cluster_name` (`cluster_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@192-168-1-34 archer]# python3 manage.py createsuperuser
Username: admin
Email address: ddd@sina.com
Password: 
Password (again): 
Superuser created successfully.
[root@192-168-1-34 archer]# ls
archer  debug.bat  debug.sh  LICENSE  manage.py  patch  README.md  requirements.txt  sql  src  startup.sh  stop.sh
[root@192-168-1-34 archer]# sh debug.sh 
Performing system checks...

System check identified no issues (0 silenced).
June 21, 2018 - 09:49:24
Django version 1.8.17, using settings 'archer.settings'
Starting development server at http://192.168.1.34:9123/
Quit the server with CONTROL-C.
#######################################

 --创建django admin管理员
python3 manage.py createsuperuser
Username: admin   自己填写用户名
Email address:     邮箱
Password:          密码
Password (again): 确认密码
Superuser created successfully.
该用户可以登录django admin来管理model。
 
--启动acher
vi debug.sh
#!/bin/bash
python3 manage.py runserver ip:9123    端口为9123  
nohup sh debug.sh 2&>1 &
[root@192-168-1-34 archer]# sh debug.sh 
Performing system checks...
System check identified no issues (0 silenced).
June 21, 2018 - 09:49:24
Django version 1.8.17, using settings 'archer.settings'
Starting development server at http://192.168.1.34:9123/
Quit the server with CONTROL-C.
注:WEB访问时报错

--创建archer系统登录用户
pip3 install simplejson

 通过浏览器访问http://10.10.3.70:8080/admin/sql/users/
可以看到django登录界面:
使用上面创建的用户名密码(admin/和密码)登录:
点击右侧Add用户配置,用户名密码自定义,至少创建一个工程师和一个审核人(用admin用户可以登录)后续新的工程师和审核人用户请用LDAP导入sql_users表或django admin增加

--配置主库地址
通过浏览器访问http://10.10.3.70:8080/admin/sql/master_config
点击右侧Add master_config。这一步是为了告诉archer你要用inception去哪些mysql主库里执行SQL,所用到的用户名密码、端口等。


--正式访问主页
http://10.10.3.70:8080/login/
发起脚本如:
use mysql;
create table t (id int(10));
insert into t values(1);
insert into t values(2);
commit;
select * from t;
如报错:/opt/python3/lib/python3.4/site-packages/pymysql/connections.py in _request_authentication, line 1113
解决办法:
vi /opt/python3/lib/python3.4/site-packages/pymysql/connections.py
... ...
    def _request_authentication(self):
        # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
        self.server_version = '5.6.24-72.2-log'  #添加此行
        if int(self.server_version.split('.', 1)[0]) >= 5:
... ...

如果遇到rollback的问题,就是给权限的问题,inception配置的连接的数据库的用户的权限,在
grant select,create,insert on *.* to incep_rw@'%' identified by 'P@ssw0rd';
也就是我配置的admin的用户。


inception源码地址:https://github.com/mysql-inception/inception
archer 源码地址:https://github.com/jly8866/archer

 

展开阅读全文

没有更多推荐了,返回首页