Inception安装

最新推荐文章于 2024-08-12 08:46:29 发布

老王笔记

最新推荐文章于 2024-08-12 08:46:29 发布

阅读量710

点赞数

分类专栏： MYSQL 文章标签： Inception

本文链接：https://blog.csdn.net/JSWANGCHANG/article/details/84138941

版权

MYSQL 专栏收录该内容

79 篇文章 4 订阅

订阅专栏

Inception是去哪儿网开发，集审核、执行、回滚于一体的一个自动化运维系统，它是根据MySQL代码修改过来的，用它可以很明确的，详细的，准确的审核MySQL的SQL语句，它的工作模式和MySQL完全相同，可以直接使用MySQL客户端来连接。

Inception提供的功能很丰富，首先，它可以对提交的所有语句的语法分析，如果语法有问题，都会将相应的错误信息返回给审核者。还提供语义分析，当一个表，库，列等信息不正确或者不符合规范的时候报错，或者使用了一个不存在的对象时报错等等。还提供了很多针对SQL规范性约束的功能，这些DBA都是可以通过系统参数来配置的。

Inception 在执行 DML 时还提供生成回滚语句的功能，对应的操作记录及回滚语句会被存储在备份机器上面，备份机器通过配置Inception参数来指定。

Step1 环境准备：

inception-master.zip 压缩包

安装了bison 2.6之前版本，下载地址： http://ftp.gnu.org/gnu/bison/

cmake yum install cmake; 最好2.8.x系列

ncurses: yum install ncurses-devel.x86_64

openssl: yum install openssl-devel.x86_64

yum install MySQL-python

g++/gcc: yum install gcc-c+.x86_64 libgcc.x86_64 gcc.x86_64

Step 2:

解压inception包，创建子目录debug；进入debug目录开始编译：

[root@localhost inception-master]# ls
BUILD        cmake           config.h.cmake   dbug   Doxyfile-perfschema  include             libevent   man        packaging  scripts    sql-common     unittest  win
BUILD-CMAKE  CMakeLists.txt  configure.cmake  debug  extra                INSTALL-SOURCE      libmysql   mysys      README.md  sql        strings        VERSION   zlib
client       cmd-line-utils  COPYING          Docs   inception_build.sh   INSTALL-WIN-SOURCE  libmysqld  mysys_ssl  regex      sql-bench  support-files  vio

cmake .. -DWITH_DEBUG=OFF -DWITH_ZLIB=system -DCMAKE_INSTALL_PREFIX=/var/inception -DWITH_SSL=bundled -DCMAKE_BUILD_TYPE=RELEASE -DWITH_ZLIB=bundled

最后出现

-- Configuring done
-- Generating done
-- Build files have been written to: /mnt/inception-master/debug

则说明配置完成

Step 3:

配置完成后即可进行编译：在当前目录下(debug)直接执行make install即可；

[root@localhost debug]# ls
client          CMakeFiles           cmd-line-utils  extra    info_macros.cmake  make_dist.cmake  man    mysys_ssl  regex  sql-bench  support-files  vio
CMakeCache.txt  cmake_install.cmake  dbug            include  libmysql           Makefile         mysys  packaging  sql    strings    VERSION.dep    zlib
[root@localhost debug]# make install
Scanning dependencies of target abi_check
[  0%] Built target abi_check
Scanning dependencies of target zlib
[  0%] Building C object zlib/CMakeFiles/zlib.dir/adler32.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/compress.c.o
[  0%] Building C object zlib/CMakeFiles/zlib.dir/crc32.c.o
[  1%] Building C object zlib/CMakeFiles/zlib.dir/deflate.c.o
[  1%] Building C object zlib/CMakeFiles/zlib.dir/gzio.c.o
[  1%] Building C object zlib/CMakeFiles/zlib.dir/infback.c.o
[  1%] Building C object zlib/CMakeFiles/zlib.dir/inffast.c.o
[  2%] Building C object zlib/CMakeFiles/zlib.dir/inflate.c.o

install完毕后就可以看到/var/inception下面有如下内容：

[root@localhost debug]# ls /var/inception/
bin  data  include  lib  man  share  sql-bench  support-files
[root@localhost debug]# ls /var/inception/bin/
Inception  mysql  mysqld-debug
[root@localhost debug]#

添加配置文件：

[root@localhost debug]# cat /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=thunder

inception_remote_system_user=thunder

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

Step3: 启动Inception:

启动inception:

[root@localhost inception-master]# /mnt/inception-master/debug/sql/Inception --defaults-file=/etc/inc.cnf
2018-11-15 18:14:17 0 [Note] Welcome to use Inception2.1.23
/mnt/inception-master/debug/sql/Inception: File '/var/inception/log/inception.log' not found (Errcode: 2 - No such file or directory)
2018-11-15 18:14:17 29781 [ERROR] Could not use /var/inception/log/inception.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
2018-11-15 18:14:17 29781 [Note] Server hostname (bind-address): '*'; port: 6669
2018-11-15 18:14:17 29781 [Note] IPv6 is available.
2018-11-15 18:14:17 29781 [Note]   - '::' resolves to '::';
2018-11-15 18:14:17 29781 [Note] Server socket created on IP: '::'.

监控端口确认6669端口已listing

[root@localhost ~]# netstat -anp | grep 6669
tcp        0      0 :::6669                     :::*                        LISTEN      29781/Inception
[root@localhost ~]#

如果遇到下面报错，则将/root/.mylogin.cnf 文件删除即可；

[root@localhost inception-master]# /mnt/inception-master/debug/sql/Inception --port=6669
error: Found option without preceding group in config file: /root/.mylogin.cnf at line: 2
Fatal error in defaults handling. Program aborted

Step4: 登陆：

[root@localhost ~]# mysql -h127.0.0.1 -uroot -P6669 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: Inception2.1.23 1

Copyright (c) 2000, 2017, 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>
mysql>
mysql> inception get variables;
+------------------------------------------+-------------------------------------------+
| Variable_name                            | Value                                     |
+------------------------------------------+-------------------------------------------+
| autocommit                               | OFF                                       |
| bind_address                             | *                                         |
| character_set_system                     | utf8                                      |
| character_sets_dir                       | /mnt/inception-master/share/charsets/     |
..............
| version_compile_machine                  | x86_64                                    |
| version_compile_os                       | Linux                                     |
| wait_timeout                             | 28800                                     |
+------------------------------------------+-------------------------------------------+
88 rows in set (0.01 sec)

注意：

线上服务器必须打开binlog;

binlog_format必须是mixed或者row模式

server_id必须非0或者非1

最好建2个账号供Inception使用，一个只读，用于审核，一个读写，用于执行；读写账户官方建议授权：

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for inception_rw@10.%              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, ALTER, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, TRIGGER ON *.* TO 'inception_rw'@'10.%' IDENTIFIED BY PASSWORD '*E3F7198900FABBABDD4F0694970063845EBEBAFA' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

涉及的表必须要有主键

binlog参数：binlog_row_image 必须是full，因为备份生成回滚语句的时候需要拿到完整binlog，否则备份不成功；

Step 5:测试：

[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# ls
inc.py  sql
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# cat sql
/*--user=inception_rw;--password=inception_password;--host=dbaddr;--port=3358;--enable-execute;--force*/
    inception_magic_start;
    create database  dbtest01 DEFAULT CHARACTER SET utf8mb4;
    use dbtest01;
    create table inception_test2(id int unsigned not null auto_increment comment 'main key',t_name varchar(10) default '' not null  comment 'name',primary key(id))engine=innodb character set utf8mb4 comment 'test table';
    inception_magic_commit;
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# cat inc.py
#!/usr/bin/env python
#--*--coding=utf8--*--

import MySQLdb
import os
import json

file_object=open('sql')
try:
	all_the_text = file_object.read()
finally:
	file_object.close()


try:
       conn=MySQLdb.connect(host='address',user='root',passwd='password',db='test',port=6669)
       cur=conn.cursor()
       ret=cur.execute(all_the_text)
       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 as e:
       print "Mysql Error %d: %s" % (e.args[0], e.args[1])
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#

执行结果如下：

[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# python inc.py
['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1']
1 | EXECUTED | 2 | Execute failed | Execute: Can't create database 'dbtest01'; database exists | create database  dbtest01 DEFAULT CHARACTER SET utf8mb4 | 0 | '0_0_0' | None | 0.000 |
2 | RERUN | 0 | Execute Successfully | None | use dbtest01 | 0 | '1542616607_81374_1' | None | 0.000 |
3 | EXECUTED | 0 | Execute Successfully
Backup successfully | None | create table inception_test2(id int unsigned not null auto_increment comment 'main key',t_name varchar(10) default '' not null  comment 'name',primary key(id))engine=innodb character set utf8mb4 comment 'test table' | 0 | '1542616607_81374_2' | 10_173_74_87_3358_dbtest01 | 0.000 |