自動化SQL操作平臺
系統環境要求：
python：3.6
django：1.8
mysql : 5.6及以上
linux : 64位linux操作系統均可
環境準備:
1、克隆代碼到本地: git clone 或 下載zip包
2、安裝mysql 5.6實例，請注意保證mysql數據庫默認字符集為utf8或utf8mb4
3、安裝inception
1、安裝 Python3
1.1、安裝依賴包
yum -y install wget sqlite-devel xz gcc automake zlib-devel openssl-devel epel-release git
1.2、編譯安裝python
<code>cd /opt/<code>
<code>wget https://www.python.org/ftp/python/3.6.1/Python-3.6.1.tar.xz/<code>
<code>tar xvf Python-3.6.1.tar.xz && cd Python-3.6.1/<code>
<code>./configure && make && make install/<code>
python3
2、安裝Django
pip3 install Django==1.8.17
或單獨安裝：
wget
tar -zxvf Django-1.8.17.tar.gz
cd Django-1.8.17
python3 setup.py install
python3
>>> import django
>>> django.VERSION
3、安裝Crypto和pymysql
pip3 install Crypto
pip3 install pycrypto
pip3 install pymysql
vi /usr/local/lib/python3.6/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'
修改後如下圖：
4、安裝mysql5.7
yum -y install yum-utils
配置yum源：
cat /etc/yum.repos.d/mysql.repo
# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
查看可安裝的mysql版本:
yum repolist all|grep mysql
安裝MySQL，默認最新版本：
# yum install mysql-community-server -y
啟動MySQL服務：
# systemctl start mysqld
# systemctl status mysqld
root賬戶默認密碼存儲在錯誤日誌中：
# grep 'temporary password' /var/log/mysqld.log
# mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '1qaz@WSX';
注意：密碼要求包含一個大寫字母，一個小寫字母，一位數字和一個特殊字符，並且密碼長度至少為8個字符。
# vi /etc/my.cnf
[mysql]
socket = /var/lib/mysql/mysql.sock
[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
bind-address = 0.0.0.0
pid-file = /var/run/mysqld/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error = /var/log/mysqld.log
max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_size = 3G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
# systemctl restart mysqld
5、配置archer
5.1、拉取代碼
git clone #或者在github直接下載zip包
cd /tmp
unzip archer-master.zip
mkdir -p /opt/archer
scp -r /tmp/archer-master/* /opt/archer/
cd /opt/archer
5.2、創建archer DB和inception DB
mysql -uroot -p
mysql> create database archer;
mysql> grant all on archer.* to archer_rw@'%' identified by '1Archer_rw';
mysql> flush privileges;
mysql> create database inception_db;
mysql> grant all on inception_db.* to incep_rw@'%' identified by '1Incep_rw';
mysql> flush privileges;
5.3、修改/opt/archer/archer/settings.py，如下內容：
cd /opt/archer/archer
vi settings.py
# 該項目本身的mysql數據庫地址
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'archer',
'USER': 'archer_rw',
'PASSWORD': '1Archer_rw',
'HOST': '192.168.0.13',
'PORT': '3306'
}
}
# inception組件所在的地址
INCEPTION_HOST = '192.168.0.13'
INCEPTION_PORT = '6669'
# 查看回滾SQL時候會用到，這裡要告訴archer去哪個mysql裡讀取inception備份的回滾信息和SQL.
# 注意這裡要和inception組件的inception.conf裡的inception_remote_XX部分保持一致.
INCEPTION_REMOTE_BACKUP_HOST = '192.168.0.13'
INCEPTION_REMOTE_BACKUP_PORT = 3306
INCEPTION_REMOTE_BACKUP_USER = 'incep_rw'
INCEPTION_REMOTE_BACKUP_PASSWORD = '1Incep_rw'
5.4、通過model創建archer本身的數據庫表
cd /opt/archer
cat requirements.txt
certifi==2017.7.27.1
chardet==3.0.4
#Django==1.8.17
idna==2.6
Naked==0.1.31
#pycrypto==2.6.1
#PyMySQL==0.7.11
PyYAML==3.12
requests==2.18.4
shellescape==3.4.1
simplejson==3.14.0
urllib3==1.22
django-admin-bootstrapped==2.5.7
django-apscheduler==0.2.8
pip3 install -r requirements.txt
python3 manage.py makemigrations
python3 manage.py makemigrations sql
python3 manage.py migrate
查看錶是否創建成功：
mysql -h192.168.0.13 -uarcher_rw -p'1Archer_rw' -P3306 -D archer -e "show tables;"
5.5、創建django admin管理員
python3 manage.py createsuperuser
[root@localhost archer]# python3 manage.py createsuperuser
Username: admin
Email address: [email protected]
Password: admin123
Password (again): admin123
Superuser created successfully.
6、啟動archer
cat debug.sh
#!/bin/bash
python3 manage.py runserver 0.0.0.0:9123 --insecure
sh debug.sh &
7、創建archer系統登錄用戶
7.1、登陸並添加用戶
通過瀏覽器訪問
可以看到django登錄界面：
使用上面第5步創建的用戶名密碼（admin/admin123）登錄：
點擊右側Add用戶配置，用戶名密碼自定義，至少創建一個工程師和一個審核人（用admin用戶可以登錄）後續新的工程師和審核人用戶請用LDAP導入sql_users表或django admin增加:
7.2、配置主庫地址
通過瀏覽器訪問http://192.168.0.13:9123/admin/sql/master_config
點擊右側Add master_config。這一步是為了告訴archer你要用inception去哪些mysql主庫裡執行SQL，所用到的用戶名密碼、端口等。
7.3、正式訪問主頁
http://192.168.0.13:9123/login/
mysql –uroot -p
mysql> grant select,create,insert on *.* to incep_rw@'%' identified by '1Incep_rw';
閱讀更多 愛踢人生 的文章
