MySQL Inception的安装和使用

本篇内容主要讲解“Mysql Inception的安装和使用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql Inception的安装和使用”吧!

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='\
   

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
    

48. SELECT `order_id` FROM `test`.`item_order` FORCE INDEX(`PRIMARY`) WHERE ((`order_id` >= ?)) ORDER BY `order_id` LIMIT ?, 2
    

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)

到此，相信大家对“MySQL Inception的安装和使用”有了更深的了解，不妨来实际操作一番吧！这里是编程网网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！