Inception MySQL自动化运维工具

原创 赤水  2015年12月16日 03:06 阅读 803 次
    Inception —— 集审核、执行、备份及生成回滚语句于一身的MySQL自动化运维工具

    MySQL语句的审核,在业界都已经基本被认同了,实际上也是对MySQL语句写法的统一化,标准化,而之前的人工审核,针对标准这个问题其实是很 吃力的,标准越多,DBA越累,开发也越累。 那么在这个都追求自动化运维的时代,审核也必须要跟上步伐,因此Inception诞生了。而Inception可以做的工作远不止是一个自动化审核工 具,同时还具备执行,生成对影响数据的回滚语句(类似闪回的功能),这样一条龙服务的工具,将会给DBA的工作带来翻天覆地的变化,DBA从此就从繁重的 审核、登上去执行,出错了很难回滚(如果提前没有备份的话)的被动局面解放了出来,突然发现,做DBA原来可以这么轻松,工作可以不饱和了,那就有更多的 自由时间学习、进一步向自动化运维平台的实现等更智能化的方向去发展,是具有里程碑意义的。


安装篇:

系统:centos 6.5    

文档:http://mysql-inception.github.io/inception-document/

1. 到git平台下载inception:

cd /usr/src ; git  clone https://github.com/mysql-inception/inception.git

2.按照依赖包
yum -y install cmake  libncurses5-dev libssl-dev g++ bison openssl-devel.x86_64 
3.下载后解压编译:
cd inception/
cmake . && cmake -DWITH_DEBUG=OFF -DCMAKE_INSTALL_PREFIX=/usr/local/inception  -DMYSQL_DATADIR=/data/inception -DWITH_SSL=yes -DCMAKE_BUILD_TYPE=RELEASE-DWITH_ZLIB=bundled-DMY_MAINTAINER_CXX_WARNINGS="-Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing  -Wno-unused-parameter -Woverloaded-virtual" -DMY_MAINTAINER_C_WARNINGS="-Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Wdeclaration-after-statement" &&  make && make install ; echo $?
make -j6 && make install ; echo $?
sh -x  inception_build.sh debug

4.安装完毕后添加配置文件:

# cat /etc/inc.cnf 
[inception]
general_log=1
general_log_file=/usr/local/inception/data/inception.log
port=6669
socket=/data/workspace/inception_data/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       //远程备份库的port
inception_remote_backup_host=127.0.0.1  //远程备份库的host
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=/data/temp
inception_osc_chunk_time=0.1
inception_ddl_support=1
inception_enable_blob_type=1
inception_check_column_default_value=1

 

5.上面配置文件中用到的文件路径,没有的手动创建一下。 

 

mkdir -p /data/workspace/inception_data/
mkdir -p /data/temp
6.启动Inception,观察下日子吧,看看有没有什么报错,如果没有表示启动无误!
nohup  /usr/local/inception/bin/Inception --defaults-file=/etc/inc.cnf >> /var/log/inception.log &     

# cat /var/log/inception.log
2015-12-16 10:33:15 0 [Note] Welcome to use Inception2.1.17
2015-12-16 10:33:15 4886 [Note] Server hostname (bind-address): '*'; port: 6669
2015-12-16 10:33:15 4886 [Note] IPv6 is available.
2015-12-16 10:33:15 4886 [Note]   - '::' resolves to '::';
2015-12-16 10:33:15 4886 [Note] Server socket created on IP: '::'.
7.登录inception 执行一个命令:inception get variables;
输出了所有的变量,恭喜你,已经启动成功了,都说了非常简单。
# 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.17 1

Copyright (c) 2000, 2013, 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;
+------------------------------------------+-------------------------------------------+
| Variable_name                            | Value                                     |
+------------------------------------------+-------------------------------------------+
| autocommit                               | OFF                                       |
| bind_address                             | *                                         |
| character_set_system                     | utf8                                      |
| character_sets_dir                       | /root/inception/share/charsets/           |
| connect_timeout                          | 10                                        |
| date_format                              | %Y-%m-%d                                  |
8测试inception。
测试之前创建一个可以连接mysql的用户:user:lssin  pass:lssin  IP:127.0.0.1
mysql> grant all on history.* to  lssin@127.0.0.1 identified by "lssin"; 

下面用脚本测试相关的审核语句:
1.  审核表创建语句测试:

 

cat createsql.py 
#!/usr/bin/python
#-\*-coding: utf-8-\*-
import MySQLdb
sql='/*--user=lssin;--password=lssin;--host=127.0.0.1;--execute=1;--port=3306;*/\
    inception_magic_start;\
    use test;\
    CREATE TABLE `alifeba_user` (\
                    `ID` int(11) unsigned NOT NULL auto_increment comment"aaa",\
                    `username` varchar(50) NOT NULL Default "" comment"aaa",\
                    `realName` varchar(50) NOT NULL Default "" comment"aaa",\
                    `age` int(11) NOT NULL Default 0 comment"aaa",\
                    PRIMARY KEY (`ID`)\
                    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT="AAAA";\
    inception_magic_commit;'
try:
        conn=MySQLdb.connect(host='127.0.0.1',user='lssin',passwd='lssin',db='history',port=6669)
        cur=conn.cursor()
        ret=cur.execute(sql)
        result=cur.fetchall()
        num_fields = len(cur.description)
        field_names = [i[0] for i in cur.description]
        print field_names
        for row in result:
                print row[0], "¦",row[1],"¦",row[2],"¦",row[3],"¦",row[4],"¦",
                row[5],"¦",row[6],"¦",row[7],"¦",row[8],"¦",row[9],"¦",row[10]
        cur.close()
        conn.close()
except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
2. 审核增删改语句
cat updatesql.py 
#!/usr/bin/python
#-\*-coding: utf-8-\*-
import MySQLdb
sql='/*--user=lssin;--password=lssin;--host=127.0.0.1;--execute=1;--enable-remote-backup;--port=3306;*/\
    inception_magic_start;\
    use wwn;\
    INSERT INTO v9_wap(siteid,sitename,logo,domain,setting) VALUES ("12","aa","bb","cc","dd");\
    delete from   v9_wap where siteid=11;\
    update  v9_wap set sitename="haha" where siteid=10;\
    inception_magic_commit;'
     
try:
        conn=MySQLdb.connect(host='127.0.0.1',user='lssin',passwd='lssin',db='history',port=6669)
        cur=conn.cursor()
        ret=cur.execute(sql)
        result=cur.fetchall()
        num_fields = len(cur.description)
        field_names = [i[0] for i in cur.description]
        print field_names
        for row in result:
                print row[0], "¦",row[1],"¦",row[2],"¦",row[3],"¦",row[4],"¦",
                row[5],"¦",row[6],"¦",row[7],"¦",row[8],"¦",row[9],"¦",row[10]
        cur.close()
        conn.close()
except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

3. inception web封装的界面安装过程如下:
下载地址:https://github.com/dbalihui/inception_web
cd /usr/src/ ; git clone https://github.com/dbalihui/inception_web.git
环境配置:python2.7 + flask  #centos 升级python 见我另一篇博客 /blog/read.html?id=4

 

安装依赖模块:

pip install flask_wtf 
pip install flask-script 
pip install flask-debugtoolbar
pip install MySQL-python
pip install argparse
配置:
修改app/inception.py 中的登录数据库帐号,密码,数据库,记得use sql_check这里要改成自己的数据库名字
cat app/inception.py
def table_structure(mysql_structure):
    sql1='/*--user=lssin;--password=lssin;--host=127.0.0.1;--execute=1;--port=3306;*/\
            inception_magic_start;\
            use history;'
启动:在inception_web目录下运行
./run.py runserver --host 0.0.0.0
然后访问 http://172.16.1.137:5000

 

 

本文地址: https://blog.lssin.com/readblog/18.html
版权声明:本文为原创文章,版权归  赤水 所有,欢迎分享本文,转载请保留出处!

发表评论


表情