MySQL Inception安装使用

比眉伴天荒 2024-04-18 00:44 43阅读 0赞

Inception 是一个集审核、执行、备份及生成回滚语句于一身的MySQL自动化运维工具，支持MySQL 5.5、5.6以及Percona等主流版本。  
  
测试环境：CentOS 6.9  
下载源码  
\[root@MySQL01 ~\]\# cd /fire/  
\[root@MySQL01 fire\]\# mkdir inception  
\[root@MySQL01 fire\]\# cd inception/  
\[root@MySQL01 inception\]\# git clone https://github.com/mysql-inception/inception.git  
Initialized empty Git repository in /fire/inception/inception/.git/  
remote: Counting objects: 2018, done.  
remote: Total 2018 (delta 0), reused 0 (delta 0), pack-reused 2018  
Receiving objects: 100% (2018/2018), 11.80 MiB | 37 KiB/s, done.  
Resolving deltas: 100% (522/522), done.  
  
安装依赖包  
yum install bison  
yum install cmake  
yum install ncurses-devel  
yum install openssl-devel  
yum install gcc-c++ libgcc gcc  
  
通过sh inception\_build.sh debug脚本方式安装  
\[root@MySQL01 inception\]\# sh inception\_build.sh debug  
...  
\-- Installing: /fire/inception/inception/debug/mysql/man/man1/mysql.1  
\-- Installing: /fire/inception/inception/debug/mysql/man/man1/mysql\_fix\_extensions.1  
\-- Installing: /fire/inception/inception/debug/mysql/man/man1/mysqltest.1  
\-- Installing: /fire/inception/inception/debug/mysql/man/man1/mysql\_client\_test.1  
\-- Installing: /fire/inception/inception/debug/mysql/man/man1/mysql\_waitpid.1  
\-- Installing: /fire/inception/inception/debug/mysql/man/man1/mysql.server.1  
\-- Installing: /fire/inception/inception/debug/mysql/man/man8/mysqld.8  
\-- Installing: /fire/inception/inception/debug/mysql/support-files/solaris/postinstall-solaris  
  
默认会安装在/fire/inception/inception/debug/mysql下面  
  
编辑配置文件

1.  \[root@MySQL01 script\]\# vim /etc/inc.cnf  
    
2.  \[inception\]  
    
3.  general\_log=1  
    
4.  general\_log\_file=/var/inception/log/inception.log  
    
5.  port=6690  
    
6.  socket=/fire/inception/inception/debug/mysql/inc.socket  
    
7.  character-set\-client-handshake=0  
    
8.  character-set\-server=utf8  
    
9.    
    
10. \# MySQL支持字符集  
    
11. inception\_support\_charset=utf8  
    
12.   
    
13. inception\_remote\_backup\_host=192.168.56.102  
    
14. inception\_remote\_backup\_port=3306  
    
15. inception\_remote\_system\_user=inception  
    
16. inception\_remote\_system\_password=inception  
    
17.   
    
18. \#inception\_remote\_charset=utf8mb4  
    
19. inception\_enable\_nullable=0  
    
20. inception\_check\_primary\_key=1  
    
21. inception\_check\_column\_comment=1  
    
22. inception\_check\_table\_comment=1  
    
23. inception\_enable\_blob\_type=1  
    
24. inception\_check\_column\_default\_value=1  
    
25.   
    
26. \# OSC  
    
27. inception\_osc\_on=1  
    
28. inception\_osc\_min\_table\_size=1  
    
29. inception\_osc\_bin\_dir=/script/percona-toolkit-3.0.2/bin  
    
30. inception\_osc\_check\_interval=5  
    
31. inception\_osc\_chunk\_time=0.1

启动  
\# /fire/inception/inception/debug/mysql/bin/Inception --defaults-file=/etc/inc.cnf &  
  
执行SQL  
编辑python 2脚本，为一张100万行的大表添加字段

1.  \[root@MySQL01 script\]\# vim inception.py  
    
2.  \#!/usr/bin/python2.6  
    
3.  \#\-\\\*\-coding: utf\-8\-\\\*\-  
    
4.  import MySQLdb  
    
5.  sql='/\*--user=neo;--password=neo;--host=192.168.56.101;--execute=1;--port=3306;\*/\\  
    
6.  inception\_magic\_start;\\  
    
7.  use test;\\  
    
8.  alter table item\_order add loc5 varchar(30) not null default \\'xxx\\' comment \\'efg\\';\\  
    
9.  inception\_magic\_commit;'  
    
10. try:  
    
11.     conn=MySQLdb.connect(host='127.0.0.1',user='',passwd='',db='',port=6690)  
    
12.     cur=conn.cursor()  
    
13.     ret=cur.execute(sql)  
    
14.     result=cur.fetchall()  
    
15.     num\_fields = len(cur.description)  
    
16.     field\_names = \[i\[0\] for i in cur.description\]  
    
17.     print field\_names  
    
18.     for row in result:  
    
19.         print row\[0\], "|",row\[1\],"|",row\[2\],"|",row\[3\],"|",row\[4\],"|",  
    
20.         row\[5\],"|",row\[6\],"|",row\[7\],"|",row\[8\],"|",row\[9\],"|",row\[10\]  
    
21.     cur.close()  
    
22.     conn.close()  
    
23. except MySQLdb.Error,e:  
    
24.      print "Mysql Error %d: %s" % (e.args\[0\], e.args\[1\])

执行脚本  
\[root@MySQL01 script\]\# python2.6 inception.py  
  
登录Inception查看OSC的执行过程

1.  \# /mysql\_software\_56/bin/mysql -uroot -h 127.0.0.1 -P 6690  
    
2.  mysql> inception get osc processlist\\G  
    
3.  \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
    
4.      DBNAME: test  
    
5.   TABLENAME: item\_order  
    
6.     COMMAND: alter table item\_order add loc5 varchar(30) not null default 'xxx' comment 'efg'  
    
7.     SQLSHA1: \*862B1979B3751217FE56799A0216A2629F2FFD4C  
    
8.     PERCENT: 100  
    
9.  REMAINTIME: 00:00  
    
10. INFOMATION: No slaves found. See --recursion-method if host MySQL01 has slaves.  
    
11. Not checking slave lag because no slaves were found and \--check-slave-lag was not specified.  
    
12. Operation, tries, wait:  
    
13.   analyze\_table, 10, 1  
    
14.   copy\_rows, 10, 0.25  
    
15.   create\_triggers, 10, 1  
    
16.   drop\_triggers, 10, 1  
    
17.   swap\_tables, 10, 1  
    
18.   update\_foreign\_keys, 10, 1  
    
19. Not updating foreign keys because --alter-foreign-keys-method=none. Foreign keys that reference the table will no longer work.  
    
20. Altering \`test\`.\`item\_order\`...  
    
21. Creating new table...  
    
22. CREATE TABLE \`test\`.\`\_item\_order\_new\` (  
    
23.   \`order\_id\` bigint(20) unsigned NOT NULL AUTO\_INCREMENT COMMENT '订单编号',  
    
24.   \`loc\_id\` tinyint(3) unsigned NOT NULL COMMENT '地区编号',  
    
25.   \`order\_create\_date\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP COMMENT '订单生成日期',  
    
26.   \`order\_expire\_date\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单过期日期',  
    
27.   \`item\_id\` int(10) unsigned NOT NULL COMMENT '商品编号',  
    
28.   \`item\_cnt\` int(10) unsigned NOT NULL COMMENT '商品数量',  
    
29.   \`order\_status\` tinyint(3) unsigned NOT NULL COMMENT '订单状态,0-失效,1-交易成功',  
    
30.   \`tran\_amount\` bigint(20) unsigned NOT NULL COMMENT '交易金额',  
    
31.   PRIMARY KEY (\`order\_id\`),  
    
32.   KEY \`idx\_order\_loc\_status\` (\`loc\_id\`,\`order\_status\`,\`order\_expire\_date\`),  
    
33.   KEY \`idx\_order\_loc\_exp\` (\`loc\_id\`,\`order\_expire\_date\`),  
    
34.   KEY \`idx\_order\_stat\_loc\_item\` (\`order\_status\`,\`loc\_id\`,\`item\_id\`,\`tran\_amount\`),  
    
35.   KEY \`idx\_item\_id\` (\`item\_id\`)  
    
36. ) ENGINE=InnoDB AUTO\_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT='订单表'  
    
37. Created new table test.\_item\_order\_new OK.  
    
38. Altering new table...  
    
39. ALTER TABLE \`test\`.\`\_item\_order\_new\` add loc5 varchar(30) not null default 'xxx' comment 'efg'  
    
40. Altered \`test\`.\`\_item\_order\_new\` OK.  
    
41. 2018-04-07T02:03:33 Creating triggers...  
    
42. CREATE TRIGGER \`pt\_osc\_test\_item\_order\_del\` AFTER DELETE ON \`test\`.\`item\_order\` FOR EACH ROW DELETE IGNORE FROM \`test\`.\`\_item\_order\_new\` WHERE \`test\`.\`\_item\_order\_new\`.\`order\_id\` <=> OLD.\`order\_id\`  
    
43. CREATE TRIGGER \`pt\_osc\_test\_item\_order\_upd\` AFTER UPDATE ON \`test\`.\`item\_order\` FOR EACH ROW BEGIN DELETE IGNORE FROM \`test\`.\`\_item\_order\_new\` WHERE !(OLD.\`order\_id\` <=> NEW.\`order\_id\`) AND \`test\`.\`\_item\_order\_new\`.\`order\_id\` <=> OLD.\`order\_id\`;REPLACE INTO \`test\`.\`\_item\_order\_new\` (\`order\_id\`, \`loc\_id\`, \`order\_create\_date\`, \`order\_expire\_date\`, \`item\_id\`, \`item\_cnt\`, \`order\_status\`, \`tran\_amount\`) VALUES (NEW.\`order\_id\`, NEW.\`loc\_id\`, NEW.\`order\_create\_date\`, NEW.\`order\_expire\_date\`, NEW.\`item\_id\`, NEW.\`item\_cnt\`, NEW.\`order\_status\`, NEW.\`tran\_amount\`);END  
    
44. CREATE TRIGGER \`pt\_osc\_test\_item\_order\_ins\` AFTER INSERT ON \`test\`.\`item\_order\` FOR EACH ROW REPLACE INTO \`test\`.\`\_item\_order\_new\` (\`order\_id\`, \`loc\_id\`, \`order\_create\_date\`, \`order\_expire\_date\`, \`item\_id\`, \`item\_cnt\`, \`order\_status\`, \`tran\_amount\`) VALUES (NEW.\`order\_id\`, NEW.\`loc\_id\`, NEW.\`order\_create\_date\`, NEW.\`order\_expire\_date\`, NEW.\`item\_id\`, NEW.\`item\_cnt\`, NEW.\`order\_status\`, NEW.\`tran\_amount\`)  
    
45. 2018-04-07T02:03:33 Created triggers OK.  
    
46. 2018-04-07T02:03:33 Copying approximately 1000219 rows...  
    
47. INSERT LOW\_PRIORITY IGNORE INTO \`test\`.\`\_item\_order\_new\` (\`order\_id\`, \`loc\_id\`, \`order\_create\_date\`, \`order\_expire\_date\`, \`item\_id\`, \`item\_cnt\`, \`order\_status\`, \`tran\_amount\`) SELECT \`order\_id\`, \`loc\_id\`, \`order\_create\_date\`, \`order\_expire\_date\`, \`item\_id\`, \`item\_cnt\`, \`order\_status\`, \`tran\_amount\` FROM \`test\`.\`item\_order\` FORCE INDEX(\`PRIMARY\`) WHERE ((\`order\_id\` >= ?)) AND ((\`order\_id\` <= ?)) LOCK IN SHARE MODE /\*pt-online-schema-change 5178 copy nibble\*/  
    
48. SELECT /\*!40001 SQL\_NO\_CACHE \*/ \`order\_id\` FROM \`test\`.\`item\_order\` FORCE INDEX(\`PRIMARY\`) WHERE ((\`order\_id\` >= ?)) ORDER BY \`order\_id\` LIMIT ?, 2 /\*next chunk boundary\*/  
    
49. 2018-04-07T02:05:07 Copied rows OK.  
    
50. 2018-04-07T02:05:07 Swapping tables...  
    
51. RENAME TABLE \`test\`.\`item\_order\` TO \`test\`.\`\_item\_order\_old\`, \`test\`.\`\_item\_order\_new\` TO \`test\`.\`item\_order\`  
    
52. 2018-04-07T02:05:10 Swapped original and new tables OK.  
    
53. 2018-04-07T02:05:10 Dropping old table...  
    
54. SET foreign\_key\_checks=0  
    
55. DROP TABLE IF EXISTS \`test\`.\`\_item\_order\_old\`  
    
56. 2018-04-07T02:05:11 Dropped old table \`test\`.\`\_item\_order\_old\` OK.  
    
57. 2018-04-07T02:05:11 Dropping triggers...  
    
58. DROP TRIGGER IF EXISTS \`test\`.\`pt\_osc\_test\_item\_order\_del\`;  
    
59. DROP TRIGGER IF EXISTS \`test\`.\`pt\_osc\_test\_item\_order\_upd\`;  
    
60. DROP TRIGGER IF EXISTS \`test\`.\`pt\_osc\_test\_item\_order\_ins\`;  
    
61. 2018-04-07T02:05:11 Dropped triggers OK.  
    
62. \# Event Count  
    
63. \# ====== =====  
    
64. \# INSERT 727  
    
65. Successfully altered \`test\`.\`item\_order\`.  
    
66.   
    
67. 1 row in set (0.00 sec)

语句执行完成  
\[root@MySQL01 script\]\# python2.6 inception.py   
\['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected\_rows', 'sequence', 'backup\_dbname', 'execute\_time', 'sqlsha1'\]  
1 | RERUN | 0 | Execute Successfully | None | 2 | EXECUTED | 0 | Execute Successfully  
Backup successfully | None |  
  
备份及回滚语句的生成条件：  
线上服务器必须要打开 binlog，在启动时需要设置参数log\_bin、log\_bin\_index等关于 binlog 的参数。不然不会备份及生成回滚语句。  
参数binlog\_format必须要设置为 mixed 或者 row 模式。  
参数 server\_id 必须要设置为非0及非1。

1.  mysql> show global variables like '%log%bin%';  
    
2.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
3.  | Variable\_name | Value |  
    
4.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
5.  | log\_bin | ON |  
    
6.  | log\_bin\_basename | /mysql\_56\_3306/log/mysql-bin |  
    
7.  | log\_bin\_index | /mysql\_56\_3306/log/mysql-bin.index |  
    
8.  | log\_bin\_trust\_function\_creators | ON |  
    
9.  | log\_bin\_use\_v1\_row\_events | OFF |  
    
10. | sql\_log\_bin | ON |  
    
11. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
12. 6 rows in set (0.00 sec)  
    
13.   
    
14. mysql> show global variables like 'binlog\_format';  
    
15. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
16. | Variable\_name | Value |  
    
17. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
18. | binlog\_format | ROW |  
    
19. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
20. 1 row in set (0.00 sec)  
    
21.   
    
22. mysql> show global variables like 'server\_id';  
    
23. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
24. | Variable\_name | Value |  
    
25. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
26. | server\_id | 102 |  
    
27. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
28. 1 row in set (0.00 sec)

查看备份表

1.  mysql> show databases;  
    
2.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
3.  | Database |  
    
4.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
5.  | information\_schema |  
    
6.  | 192\_168\_56\_101\_3306\_test |  
    
7.  | inception |  
    
8.  | mysql |  
    
9.  | performance\_schema |  
    
10. | sale |  
    
11. | test |  
    
12. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
13. 8 rows in set (0.00 sec)  
    
14.   
    
15. mysql> use 192\_168\_56\_101\_3306\_test  
    
16. Reading table information for completion of table and column names  
    
17. You can turn off this feature to get a quicker startup with -A  
    
18.   
    
19. Database changed  
    
20. mysql> show tables;  
    
21. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
22. | Tables\_in\_192\_168\_56\_101\_3306\_test |  
    
23. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
24. | $\_$inception\_backup\_information$\_$ |  
    
25. | item\_order |  
    
26. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
27. 2 rows in set (0.00 sec)  
    
28.   
    
29. mysql> select \* from item\_order;  
    
30. \+\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
31. | id | rollback\_statement | opid\_time |  
    
32. \+\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
33. | 1 | ALTER TABLE \`test\`.\`item\_order\` DROP COLUMN \`loc5\`; | 1523037912\_28\_1 |  
    
34. \+\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
35. 1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2152755/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2152755/