千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

MySQL Inception的安装和使用

发表于：2025-11-13 作者：千家信息网编辑
千家信息网最后更新 2025年11月13日，本篇内容主要讲解"MySQL Inception的安装和使用"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"MySQL Inception的安装和使用"吧!
千家信息网最后更新 2025年11月13日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


  10. # MySQL支持字符集

  11. inception_support_charset=utf8


  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


  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


  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`.


  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)


  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)


  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)


  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


  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)


  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的安装和使用"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

很赞哦！
订单 参数 备份 语句 生成 脚本 内容 商品 日期 交易 学习 支持 实用 更深 成功 一身 主流 兴趣 地区 字段 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 谷歌地图高清卫星地图数据库 思杰网络技术怎么样 网络安全法留存日志不超过 梦幻西游2021哪个服务器好 本地代理服务器软件 苏州橡胶零件加工管理软件开发 sqlite 数据库版本 苹果网页找不到服务器 软件开发企业如何记账 win10英文版服务器修改语言 内江安卓软件开发公司 小白菜数据库是什么 论文收录中国主流数据库 北京文档软件开发常用解决方案 可可软件开发者账号注册 温州建设智慧消防软件开发 vps数据库地址 怎么查看服务器的内存 从百度搜索结果中爬取数据库 招聘福州居家办公软件开发 软件开发的支持活动 企业网络安全分析及解决方案研究 专业上门回收服务器 湖北分布式存储服务器 软件开发的行业发展 网络安全手抄报写是什么 t6期初余额数据库表 互联网科技基金还可以持有吗 王牌竞速最新服务器叫什么名儿 多媒体数据被数据库保存多久

相关文章

0