本文共 16208 字,大约阅读时间需要 54 分钟。
一个集审核、执行、备份及生成回滚语句于一身的MySQL自动化运维工具,由去哪网开源
yum -y install cmake libncurses5-dev libssl-dev g++ bison gcc gcc-c++ openssl-devel ncurses-devel mysql pymysql
注意bison版本,centos7如果yum安装bison,会因为版本过高导致编译报错
wget http://ftp.gnu.org/gnu/bison/bison-2.5.1.tar.gztar -zxvf bison-2.5.1.tar.gzcd bison-2.5.1./configuremakemake install
cd /usr/local/wget https://github.com/mysql-inception/inception/archive/master.zipunzip master.zipcd inception-master/sh inception_build.sh builddir linux
cat /etc/inc.cnf###################[inception]general_log=1general_log_file=inc.logport=6669socket=/tmp/inc.socketcharacter-set-client-handshake=0character-set-server=utf8# 这里的数据库是填inception的库,也用作备份库inception_remote_system_password=123456inception_remote_system_user=rootinception_remote_backup_port=3306inception_remote_backup_host=127.0.0.1inception_support_charset=utf8inception_enable_nullable=0inception_check_primary_key=1inception_check_column_comment=1inception_check_table_comment=1inception_osc_min_table_size=1inception_osc_bin_dir=/usr/bininception_osc_chunk_time=0.1inception_ddl_support=1inception_enable_blob_type=1inception_check_column_default_value=1###################
nohup /usr/local/inception-master/builddir/mysql/bin/Inception --defaults-file=/etc/inc.cnf &
(inception_env) [root@localhost inception-master]# mysql -uroot -h127.0.0.1 -P 6669Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: Inception2.1.50 1Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.# 有如下输出即可MySQL [(none)]> inception get variables;+------------------------------------------+---------------------------------------------+| Variable_name | Value |+------------------------------------------+---------------------------------------------+| autocommit | OFF || bind_address | * || character_set_system | utf8 || character_sets_dir | /usr/local/inception-master/share/charsets/ || connect_timeout | 10 || date_format | %Y-%m-%d || datetime_format | %Y-%m-%d %H:%i:%s || general_log | ON || general_log_file | inc.log || inception_check_autoincrement_datatype | ON || inception_check_autoincrement_init_value | ON || inception_check_autoincrement_name | ON || inception_check_column_comment | ON || inception_check_column_default_value | ON || inception_check_dml_limit | ON || inception_check_dml_orderby | ON || inception_check_dml_where | ON || inception_check_identifier | ON || inception_check_index_prefix | ON || inception_check_insert_field | ON || inception_check_primary_key | ON || inception_check_table_comment | ON || inception_check_timestamp_default | ON || inception_ddl_support | ON || inception_enable_autoincrement_unsigned | ON || inception_enable_blob_type | ON || inception_enable_column_charset | OFF || inception_enable_enum_set_bit | OFF || inception_enable_foreign_key | OFF || inception_enable_identifer_keyword | OFF || inception_enable_not_innodb | OFF || inception_enable_nullable | OFF || inception_enable_orderby_rand | OFF || inception_enable_partition_table | OFF || inception_enable_pk_columns_only_int | OFF || inception_enable_select_star | OFF || inception_enable_sql_statistic | ON || inception_max_char_length | 16 || inception_max_key_parts | 5 || inception_max_keys | 16 || inception_max_primary_key_parts | 5 || inception_max_update_rows | 10000 || inception_merge_alter_table | ON || inception_osc_alter_foreign_keys_method | none || inception_osc_bin_dir | /usr/bin || inception_osc_check_alter | ON || inception_osc_check_interval | 5.000000 || inception_osc_check_replication_filters | ON || inception_osc_chunk_size | 1000 || inception_osc_chunk_size_limit | 4.000000 || inception_osc_chunk_time | 0.100000 || inception_osc_critical_thread_connected | 1000 || inception_osc_critical_thread_running | 80 || inception_osc_drop_new_table | ON || inception_osc_drop_old_table | ON || inception_osc_max_lag | 3.000000 || inception_osc_max_thread_connected | 1000 || inception_osc_max_thread_running | 80 || inception_osc_min_table_size | 1 || inception_osc_on | ON || inception_osc_print_none | ON || inception_osc_print_sql | ON || inception_osc_recursion_method | processlist || inception_password | || inception_read_only | OFF || inception_remote_backup_host | 127.0.0.1 || inception_remote_backup_port | 3306 || inception_remote_system_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || inception_remote_system_user | root || inception_support_charset | utf8 || inception_user | || interactive_timeout | 28800 || max_allowed_packet | 1073741824 || max_connect_errors | 100 || max_connections | 151 || net_buffer_length | 16384 || net_read_timeout | 30 || net_write_timeout | 60 || port | 6669 || query_alloc_block_size | 8192 || query_prealloc_size | 8192 || socket | /tmp/inc.socket || thread_handling | one-thread-per-connection || thread_stack | 262144 || time_format | %H:%i:%s || version | Inception2.1.50 || version_comment | Source distribution || version_compile_machine | x86_64 || version_compile_os | Linux || wait_timeout | 28800 |+------------------------------------------+---------------------------------------------+90 rows in set (0.00 sec)
对sql的审核都是在脚本中调用inception去实现的,下面就简单使用下
(inception_env) [vagrant@localhost ~]$ cat test.py #!/usr/bin/python#-\*-coding: utf-8-\*-''' 建库建表语句CREATE DATABASE test;CREATE TABLE IF NOT EXISTS `mytable1`( `id` INT UNSIGNED AUTO_INCREMENT, `myname` VARCHAR(10) NOT NULL, PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;'''import pymysql# 待审核/执行的sql语句(需包含目标数据库的地址、端口 等参数)sql='/* --user=root;--password=123456;--host=127.0.0.1;--port=3306;--enable-check; */\inception_magic_start;\use test;\insert into mytable1 (myname) values ("xianyu1"),("xianyu2");\insert into mytable1 (myname) values ("xianyu1"),("xianyu2");\inception_magic_commit;'try: # inception的地址、端口等 conn=pymysql.connect(host='127.0.0.1',user='root',passwd='123456',db='',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 (result) ''' 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 Exception as e: print (e)
注意:
- –enable-check是审核sql - –enable-execute是执行sql具体指令参考官方文档
如果就这样直接执行是会报如下错误的
invalid literal for int() with base 10: 'Inception2'
因为inception是不支持python3的pymysql库的,所以得修改下pymysql的源码才行
查找pymysql源码修改connections.py文件 找到_request_authentication方法def _request_authentication(self): # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse if int(self.server_version.split('.', 1)[0]) >= 5: self.client_flag |= CLIENT.MULTI_RESULTS
修改为
def _request_authentication(self): # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse try: if int(self.server_version.split('.', 1)[0]) >= 5: self.client_flag |= CLIENT.MULTI_RESULTS except: if self.server_version.split('.', 1)[0] == 'Inception2': self.client_flag |= CLIENT.MULTI_RESULTS
现在我们来执行下脚本看看
(inception_env) [vagrant@localhost ~]$ python test.py ((1, 'CHECKED', 0, 'Audit completed', 'None', 'use test', 0, "'0_0_0'", 'None', '0', ''), (2, 'CHECKED', 0, 'Audit completed', 'None', 'insert into mytable1 (myname) values ("xianyu1"),("xianyu2")', 2, "'0_0_1'", '127_0_0_1_3306_test', '0', ''), (3, 'CHECKED', 0, 'Audit completed', 'None', 'insert into mytable1 (myname) values ("xianyu1"),("xianyu2")', 2, "'0_0_2'", '127_0_0_1_3306_test', '0', ''))
可以看到sql审核过了(Audit completed),没有问题,那来执行sql看看(把脚本里的–enable-check改成–enable-execute)
(inception_env) [vagrant@localhost ~]$ python test.py ((1, 'RERUN', 0, 'Execute Successfully', 'None', 'use test', 0, "'1533472093_33_0'", 'None', '0.000', ''), (2, 'EXECUTED', 0, 'Execute Successfully\nBackup successfully', 'None', 'insert into mytable1 (myname) values ("xianyu1"),("xianyu2")', 2, "'1533472093_33_1'", '127_0_0_1_3306_test', '0.010', ''), (3, 'EXECUTED', 0, 'Execute Successfully\nBackup successfully', 'None', 'insert into mytable1 (myname) values ("xianyu1"),("xianyu2")', 2, "'1533472093_33_2'", '127_0_0_1_3306_test', '0.000', ''))
可以看到(Execute Successfully\nBackup successfully)sql执行成功并且被备份了回滚语句
开启数据库binlog选项(不开启的话是不会生成备份库的)
cat /etc/my.cnf[mysqld]log-bin = mysql-binbinlog_format = ROWserver-id = 1
我们去看看(备份的数据在配置文件指定的库里)
(inception_env) [root@localhost bin]# mysql -uroot -h127.0.0.1 -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 35Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;+---------------------+| Database |+---------------------+| information_schema |# 备份库| 127_0_0_1_3306_test |# inception的库 不用管它| inception || performance_schema || test |+---------------------+13 rows in set (0.00 sec)MariaDB [127_0_0_1_3306_test]> select * from $_$Inception_backup_information$_$;+-----------------+-------------------+------------------+------------------+----------------+--------------------------------------------------------------+-----------+--------+-----------+------+---------------------+--------+| opid_time | start_binlog_file | start_binlog_pos | end_binlog_file | end_binlog_pos | sql_statement | host | dbname | tablename | port | time | type |+-----------------+-------------------+------------------+------------------+----------------+--------------------------------------------------------------+-----------+--------+-----------+------+---------------------+--------+| 1533448913_2_1 | mysql-bin.000001 | 245 | mysql-bin.000001 | 445 | insert into mytable1 (myname) values ("xianyu1"),("xianyu2") | 127.0.0.1 | test | mytable1 | 3306 | 2018-08-05 07:01:53 | INSERT || 1533448913_2_2 | mysql-bin.000001 | 445 | mysql-bin.000001 | 645 | insert into mytable1 (myname) values ("xianyu1"),("xianyu2") | 127.0.0.1 | test | mytable1 | 3306 | 2018-08-05 07:01:53 | INSERT |2 rows in set (0.00 sec)MariaDB [127_0_0_1_3306_test]> select * from mytable1;+----+--------------------------------------------+-----------------+| id | rollback_statement | opid_time |+----+--------------------------------------------+-----------------+| 1 | DELETE FROM `test`.`mytable1` WHERE id=13; | 1533448913_2_1 || 2 | DELETE FROM `test`.`mytable1` WHERE id=14; | 1533448913_2_1 || 3 | DELETE FROM `test`.`mytable1` WHERE id=15; | 1533448913_2_2 || 4 | DELETE FROM `test`.`mytable1` WHERE id=16; | 1533448913_2_2 |4 rows in set (0.00 sec)
注意:
- $_$Inception_backup_information$_$表记录的是执行sql记录- mytable1表记录的是sql回滚语句
用户在页面上点击通过django对inception进行调用进行sql的审核、执行、备份及生成回滚
转载地址:http://lkksi.baihongyu.com/