Inception验证：

最新推荐文章于 2024-08-12 08:46:29 发布

老王笔记

最新推荐文章于 2024-08-12 08:46:29 发布

阅读量745

点赞数

分类专栏： MYSQL 文章标签： Inception mysql

本文链接：https://blog.csdn.net/JSWANGCHANG/article/details/84309750

版权

MYSQL 专栏收录该内容

79 篇文章 4 订阅

订阅专栏

审计

代码

/*--user=inception_rw;--password=inception_rw;--host=xx.xxx.xx.xx;--port=xxxx;--enable-check;*/
    inception_magic_start;
    use dbtest01;
    create table inception_test5(id int unsigned not null auto_increment comment 'main key',t_name varchar(10) default '' not null  comment 'name',primary key(id))engine=innodb character set utf8mb4 comment 'test table';
   alter table inception_test5 add KEY IDX_name(t_name);
    inception_magic_commit;
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#

结果：

[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# python check.py
['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1']
1 | CHECKED | 0 | Audit completed | None | use dbtest01 | 0 | '0_0_0' | None | 0 |
2 | CHECKED | 0 | Audit completed | None | create table inception_test5(id int unsigned not null auto_increment comment 'main key',t_name varchar(10) default '' not null  comment 'name',primary key(id))engine=innodb character set utf8mb4 comment 'test table' | 0 | '0_0_1' | 10_xx_xx_xx_3xx8_dbtest01 | 0 |
3 | CHECKED | 0 | Audit completed | None | alter table inception_test5 add KEY IDX_name(t_name) | 0 | '0_0_2' | 10_xxx_xx_xx_xxxx_dbtest01 | 0 |

Split

[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# cat split_sql
/*--user=inception_rw;--password=inception_rw;--host=xxx;--port=xxxx;--enable-split;*/
    inception_magic_start;
    use dbtest01;
    create table inception_test3(id int unsigned not null auto_increment comment 'main key',t_name varchar(10) default '' not null  comment 'name',primary key(id))engine=innodb character set utf8mb4 comment 'test table';
   insert into inception_test3(t_name) values('ouyangfeng');
   alter table inception_test3 add KEY IDX_name(t_name);
   alter table inception_test3 rename to inception_test4;
   truncate table inception_test4;
    inception_magic_commit;
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# python split.py
['ID', 'sql_statement', 'ddlflag']
1 | use dbtest01;
create table inception_test3(id int unsigned not null auto_increment comment 'main key',t_name varchar(10) default '' not null  comment 'name',primary key(id))engine=innodb character set utf8mb4 comment 'testtable';
| 0
2 | use dbtest01;
insert into inception_test3(t_name) values('ouyangfeng');
| 0
3 | use dbtest01;
alter table inception_test3 add KEY IDX_name(t_name);
alter table inception_test3 rename to inception_test4;
truncate table inception_test4;
| 1

执行

[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# cat sql
/*--user=inception_rw;--password=inception_rw;--host=xx.xx.xx.xx;--port=xxxx;--enable-execute;*/
    inception_magic_start;
    use dbtest01;
    insert into hero(hero) values('TeeMO'),('JAX');
    inception_magic_commit;
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]# python inc.py
['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1']
1 | RERUN | 0 | Execute Successfully | None | use dbtest01 | 0 | '1542719263_84861_0' | None | 0.000 |
2 | EXECUTED | 0 | Execute Successfully
Backup successfully | None | insert into hero(hero) values('TeeMO'),('JAX') | 2 | '1542719263_84861_1' | 10_xx_xx_xx_xxxx_dbtest01 | 0.000 |
[root@93a17e41-ee44-4c53-8432-b196632b8891 sql]#

备份与回滚

mysql> select * from wang_test limit 4;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| id | rollback_statement                   | opid_time          |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
|  1 | ALTER TABLE `test`.`wang_test` ADD INDEX `idx_end_date` (end_date),ADD INDEX `idx_start_date` (start_date),ADD INDEX `index_spu` (spu,status),ADD INDEX `INDEX_PROMOTIONID` (promotion_id),ADD INDEX `INDEX_SKUID` (sku_id); | 1542718047_84807_1 |
|  2 | ALTER TABLE `test`.`wang_test` ADD INDEX `index_classify` (classify_type,status);                   | 1542718417_84824_1 |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
2 rows in set (0.00 sec)

查看进度

必须在是ALTER TABLE的时候，且启用pt-online-schema-change功能，才可以查看进度：

查看执行进度

语法：inception get osc_percent 'HASH值’;

HASH值在审核的时候会返回；

mysql> inception get osc_percent '*FCE23EA0E70024BE7CDDDA1C9CD5B25EFEEC19C7'\G
*************************** 1. row ***************************
    DBNAME: test
 TABLENAME: wang_test
   SQLSHA1: *FCE23EA0E70024BE7CDDDA1C9CD5B25EFEEC19C7
   PERCENT: 72
REMAINTIME: 00:10
INFOMATION: Found 1 slaves:
7730b9fc-0066-4203-aeaf-fc95fb0f45b3 -> xxx.xxx.xxx.xxx:xxxx
Will check slave lag on:
7730b9fc-0066-4203-aeaf-fc95fb0f45b3 -> 1xxx.xxx.xxx.xxx:xxxx
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Not updating foreign keys because --alter-foreign-keys-method=none.  Foreign keys that reference the table will no longer work.
Altering `test`.`wang_test`...
Creating new table...
CREATE TABLE `test`.`_wang_test_new` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '??ID???',
  `brand_id` int(8) NOT NULL COMMENT '??ID',
  `sku_id` bigint(20) DEFAULT NULL,
  `spu` bigint(20) DEFAULT NULL,
  `sku_name` varchar(100) DEFAULT NULL COMMENT '???',
  `sku_img` varchar(100) DEFAULT NULL COMMENT '???',
  `sku_order` int(8) DEFAULT NULL COMMENT '????',
  `promotion_id` bigint(20) DEFAULT NULL,
  `tag_type` varchar(20) DEFAULT NULL COMMENT '????',
  `tag_text` varchar(20) DEFAULT NULL COMMENT '????',
  `jd_price` double DEFAULT '0' COMMENT '???',
  `promo_price` double DEFAULT '0' COMMENT '???',
  `thirty_lowest_price` double DEFAULT NULL COMMENT '30????',
  `seckill_num` int(8) DEFAULT '0' COMMENT '????',
  `status` tinyint(2) DEFAULT NULL COMMENT '????',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `operator` varchar(50) DEFAULT NULL,
  `brand_id_auto` int(8) DEFAULT NULL COMMENT '????ID???????',
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `top` int(4) DEFAULT '0' COMMENT '1??? 0????',
  `three_catagory_id` varchar(127) DEFAULT NULL COMMENT '????_????_????',
  `catagory_id` int(11) DEFAULT NULL COMMENT '??????',
  `start_date` datetime DEFAULT NULL COMMENT '??????',
  `end_date` datetime DEFAULT NULL COMMENT '??????',
  `classify_type` int(8) DEFAULT NULL COMMENT '??ID',
  `default_sku` tinyint(4) DEFAULT '1' COMMENT '???spu?????',
  `special_without_filter` tinyint(4) DEFAULT NULL COMMENT '???????1?????0??',
  `star_goods` tinyint(4) DEFAULT '0' COMMENT '??????? 0-? 1-?',
  `star_word` varchar(255) DEFAULT NULL COMMENT '???????',
  `plus_promotion_id` varchar(50) DEFAULT NULL COMMENT 'T+1 plus????id',
  `is_online` tinyint(4) DEFAULT '1' COMMENT '??????',
  `is_newgoods` tinyint(4) DEFAULT '0' COMMENT '???? 0:?  1??',
  `ever_pushDelivery` int(4) DEFAULT '0' COMMENT '????',
  PRIMARY KEY (`id`),
  KEY `INDEX_STATUS` (`status`),
  KEY `INDEX_BRAND` (`brand_id_auto`),
  KEY `index_start_time` (`start_time`),
  KEY `index_classify` (`classify_type`,`status`),
  KEY `idx_test` (`start_date`,`end_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1735494 DEFAULT CHARSET=utf8 COMMENT='??????????'
Created new table test._wang_test_new OK.
Waiting forever for new table `test`.`_wang_test_new` to replicate to 7730b9fc-0066-4203-aeaf-fc95fb0f45b3...
Altering new table...
ALTER TABLE `test`.`_wang_test_new` drop key  `index_classify`
Altered `test`.`_wang_test_new` OK.
2018-11-20T20:52:56 Creating triggers...
2018-11-20T20:52:56 Created triggers OK.
2018-11-20T20:52:56 Copying approximately 1687290 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_wang_test_new` (`id`, `brand_id`, `sku_id`, `spu`, `sku_name`, `sku_img`, `sku_order`, `promotion_id`, `tag_type`, `tag_text`, `jd_price`, `promo_price`, `thirty_lowest_price`, `seckill_num`, `status`, `created`, `modified`, `operator`, `brand_id_auto`, `start_time`, `end_time`, `top`, `three_catagory_id`, `catagory_id`, `start_date`, `end_date`, `classify_type`, `default_sku`, `special_without_filter`, `star_goods`, `star_word`, `plus_promotion_id`, `is_online`, `is_newgoods`, `ever_pushdelivery`) SELECT `id`, `brand_id`, `sku_id`, `spu`, `sku_name`, `sku_img`, `sku_order`, `promotion_id`, `tag_type`, `tag_text`, `jd_price`, `promo_price`, `thirty_lowest_price`, `seckill_num`, `status`, `created`, `modified`, `operator`, `brand_id_auto`, `start_time`, `end_time`, `top`, `three_catagory_id`, `catagory_id`, `start_date`, `end_date`, `classify_type`, `default_sku`, `special_without_filter`, `star_goods`, `star_word`, `plus_promotion_id`, `is_online`, `is_newgoods`, `eve
r_pushdelivery` FROM `test`.`wang_test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 572910 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`wang_test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

1 row in set (0.00 sec)
mysql> inception get osc_percent '*FCE23EA0E70024BE7CDDDA1C9CD5B25EFEEC19C7'\G
Query OK, 0 rows affected (0.00 sec)

PERCENT：显示执行的百分比，，0-99；

REMAINTIME：剩余执行时间，单位秒