mysql 运维方案 mysql自动化运维平台

转载

网络安全守护先锋 2024-03-02 10:51:31

文章标签 mysql 运维方案运维数据库 python SQL 文章分类 MySQL 数据库

作者：黄超
杏仁运维工程师，关注容器技术和自动化运维。

引子

最近打算做一个 MySQL 的数据库运维平台。这里面有一个非常重要的功能就是 SQL 的审核，如果完全靠人工去实现就没必要做成一个平台了。正没头绪如何去实现的时候，google 了一下，看下有没有现成的开源方案。果不其然，github 上发现一个『去哪儿网』开源的一个数据库运维工具 Inception, 它是一个集审核、执行、备份及生成回滚语句于一身的 MySQL 自动化运维工具。

Inception 介绍

Inception 的架构图如下图所示，简单来说，Inception 就是一个 MySQL 的代理，能够帮助你审核 SQL，执行 SQL，备份 SQL 影响的记录。Inception 是一个 C/S 的软件架构。我们可以通过原生的 MySQL 客户端去连接，也可以通过远程的接口去连接，目前执行只支持通过C/C++接口、Python接口来对Inception访问。

执行流程图如下：

安装 Inception

我安装的环境
OS: Ubuntu 16.04.2 LTS

安装依赖

下载 bison：版本最好是2.6之前的（Ubuntu 16.04.2 LTS 版本下安装的是 bison-2.5.1），最新的可能会有问题，下载之后，需要自己编译源码来安装，具体安装方法，可以参数网上的一些说明。
cmake 安装：``apt-get install cmake`
ncurses 安装：apt-get install libncurses5-dev
安装openssl：apt-get install libssl-dev
安装 g++：sudo apt-get install g++
安装 m4: apt-get install m4

编译安装 Inception

git clone https://github.com/mysql-inception/inception.git
sh inception_build.sh debug [linux]  (如果不指定就是linux平台，而如果要指定是Xcode，就后面指定Xcode)
复制代码

可执行文件在 debug/sql/Debug/ 目录下面（不同平台有可能不相同）。

启动 Inception

创建一个配置文件 inc.cnf, 里面主要是配置 Inception 启动的端口，SQL 审核的策略，备份数据库的配置等等，更多可参考官方文档。

[inception]
general_log=1
general_log_file=inception.log
port=6669   # Inception 的监听的端口
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_remote_system_password=root  # 备份数据库密码
inception_remote_system_user=wzf1      # 备份数据库用户名
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=/data/temp
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1
复制代码

启动

./Inception --defaults-file=inc.cnf
复制代码

访问

通过原生的 MySQL 客户端的方式。主要注意的是，请不要将的 SQL 语句块，放到 MySQL 客户端中执行，因为这是一个自动化运维工具，如果使用交互式的命令行来使用的话没有意义，所有的 SQL 执行应该都通过接口的方式，这个方式仅仅可用来查看和设置上诉配置文件里的配置，如 inception get variables;可查看所有的变量，更多请参考官方文档。

mysql -uroot -h127.0.0.1 -P6669
复制代码

通过接口的方式。下面是官方示例中的 Python 代码，需要注意的是如果使用 Python3 的 pymsql 去连接会有异常，目前的解决方案是需要修改pymysql 的源码，具体 issue：

#!/usr/bin/python
#-\*-coding: utf-8-\*-
import MySQLdb
sql='/*--user=username;--password=password;--host=127.0.0.1;--execute=1;--port=3306;*/\
inception_magic_start;\
use mysql;\
CREATE TABLE adaptive_office(id int);\
inception_magic_commit;'
try:
    conn=MySQLdb.connect(host='127.0.0.1',user='',passwd='',db='',port=9998)
    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])
复制代码

SQL 审核 & 执行

通过 Inception 对语句进行审核时，必须要告诉 Inception 这些语句对应的数据库地址、数据库端口以及

Inception 连接数据库时使用的用户名、密码等信息，而不能简单的只是执行一条 sql 语句，所以必须要通过某种方式将这些信息传达给 Inception。

连接信息放在 /* ... */ 的注释中，真正的 SQL 语句则包括在 inception_magic_start 与 inception_magic_commit:

审核

审核的规范见官方文档，有些规范是可配置的，可根据自己公司的规范在 Inception 的配置文件中配置。

执行

注意下，官方说是支持 DDL，DML 语句的，但是并不支持 SELECT 查询。

比如通过 Inception 执行一个建表语句：

...
inception_magic_start;  
use mysql;  
CREATE TABLE adaptive_office(id int);  
inception_magic_commit;
...
复制代码

返回结果, 可见是每一条 SQL 就会返回一个可执行的结果，errlevel 非 0 时表示执行失败，下面所示中的第二条 SQL 语句 Audit completed(审核完成) 但是不符合建表的规范，更多关于返回结果的说明可见官方文档：

'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
1 | CHECKED | 0 | Audit completed | None | use inception_test | 0 | '0_0_0' | None | 0 | 
2 | CHECKED | 1 | Audit completed | Set engine to innodb for table 'adaptive_office'.
Set charset to one of 'utf8mb4' for table 'adaptive_office'.
Set comments for table 'adaptive_office'.
Column 'id' in table 'adaptive_office' have no comments.
Column 'id' in table 'adaptive_office' is not allowed to been nullable.
Set Default value for column 'id' in table 'adaptive_office'
Set a primary key for table 'adaptive_office'. | CREATE TABLE adaptive_office(id int) | 0 | '0_0_1' | 10_10_1_67_1028_inception_test | 0 | 
复制代码

备份功能

前提条件

线上服务器必须要打开 binlog，不然不会备份及生成回滚语句。
参数 binlog_format 必须要设置为 mixed 或者 row 模式，通过语句：set global binlog_format=mixed/row 来设置，如果是 statement 模式，则不做备份及回滚语句的生成。
被影响的行中必须存在主键，因为回滚语句的 WHERE 条件就是主键。比如，我插入一条数据并返回主键 id=1, 那么相应的它就会反向生成一个删除语句 (WHERE 的条件就是主键) DELETE FROM xx WHERE id = 1。

Inception 在做 DML 操作时具有备份功能（默认开启，可通过在执行 SQL 中注释文件中指定 --disable-remote-backup），它会将所有当前语句修改的行备份下来，存储到一个指定的备份库中，备份库通过配置 Inception 参数来指定。

关于备份数据库的命名方式，备份机器的库名组成是由线上机器的 IP 地址的点换成下划线，再加上端口号，再加上库名三部分，这三部分也是通过下划线连接起来的。例如：我执行 DML 操作的数据库地址是 192.168.1.1, 端口是 3306, 库名是 inceptiondb, 则在备份数据库中表名为：192_168_1_1_3306_inceptiondb。

比如，我有一个 inception_test 库，其中有一张 userinfo 表，就两个字段：

我通过 Inception 去执行一个 INSERT 一条记录：

/*--user=root;--password=xxx;--host=1.1.1.1;--execute=1;--port=3306;--sleep=0;--enable-remote-backup;*/\
inception_magic_start;\
use inception_test; \
insert into userinfo(`username`) values("test");\
inception_magic_commit;
复制代码

返回的结果如下, 可以看到已经执行成功并且备份成功了：

2 | EXECUTED | 0 | Execute Successfully
Backup successfully | None | insert into userinfo(`username`) values("test") | 1 | '1533716166_25519001_1' | 1_1_1_1_3306_inception_test | 0.060 | 
复制代码

查看下备份数据库中的 1_1_1_1_3306_inception_test 库 userinfo 表的结果, 根据 INSERT 的语句相应地生成了一条 DELETE 语句：

DELETE FROM `inception_test`.`userinfo` WHERE id=4;
复制代码

那么，我需要如果正确地找到回滚的语句呢？可以查看下备份库 1_1_1_1_3306_inception_test 中 userinfo 的表结构：

主要有两个字段：

rollback_statement text: 生成修改的回滚语句。
opid_time varchar(50): 这个列存储的是的被执行的 SQL 语句在执行时的一个序列号，这个序列号由三部分组成：timestamp(int 值，是语句被执行的时间点) + 线上服务器执行时所产生的 thread_id + 当前这条语句在所有被执行的语句块中的一个序号组成。可见上面的结果：1533716166_25519001_1 , 这个序列号同时也会出现在执行返回的结果中，所有需要回滚就是根据这个序列号去备份表中查询回滚的 SQL 语句。

更多说明，请参考官方文档中的备份功能说明。