pt-osc之工作流程: 1、检查更改表是否有主键或唯一索引,是否有触发器 2、检查修改表的表结构,创建一个临时表,在新表上执行ALTER TABLE语句 3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作 4、从源表拷贝数据到临时表,在拷贝过程中,对源表的更新操作会写入到新建表中 5、将临时表和源表rename(需要元数据修改锁,需要短时间锁表) 6、删除源表和触发器,完成表结构的修改。
1、源表必须有主键或唯一索引,如果没有工具将停止工作 2、如果线上的复制环境过滤器操作过于复杂,工具将无法工作 3、如果开启复制延迟检查,但主从延迟时,工具将暂停数据拷贝工作 4、如果开启主服务器负载检查,但主服务器负载较高时,工具将暂停操作 5、当表使用外键时,如果未使用--alter-foreign-keys-method参数,工具将无法执行 6、只支持Innodb存储引擎表,且要求服务器上有该表1倍以上的空闲空间。
简单的加索引操作
pt-online-schema-change h=127.0.0.1,P=3306,u=root,D=pt_test,t=pt_osc_1 --alter 'drop index idx_age' --ask-pass --print --execute打印出来的结果
# 尝试连接从数据库 Cannot connect to h=***.***.***.***,p=...,u=root Cannot connect to h=***.***.***.***,p=...,u=root No slaves found. See --recursion-method if host VM_0_14_centos has slaves. # 查看主从延迟配置项 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 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 Altering `pt_test`.`pt_osc_1`... # 建表 Creating new table... CREATE TABLE `pt_test`.`_pt_osc_1_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` tinyint(1) DEFAULT NULL, `sex` enum('man','women') DEFAULT 'man', `info` varchar(100) DEFAULT 'have a good day', PRIMARY KEY (`id`), KEY `idx_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=1270013 DEFAULT CHARSET=utf8 Created new table pt_test._pt_osc_1_new OK. # 修改表结构 Altering new table... ALTER TABLE `pt_test`.`_pt_osc_1_new` drop index idx_age Altered `pt_test`.`_pt_osc_1_new` OK. # 建触发器 2020-07-03T10:48:46 Creating triggers... 2020-07-03T10:48:46 Created triggers OK. # 插入数据 2020-07-03T10:48:46 Copying approximately 1266000 rows... INSERT LOW_PRIORITY IGNORE INTO `pt_test`.`_pt_osc_1_new` (`id`, `name`, `age`, `sex`, `info`) SELECT `id`, `name`, `age`, `sex`, `info` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 2459 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ 2020-07-03T10:49:08 Copied rows OK. # 修复索引 2020-07-03T10:49:08 Analyzing new table... # 切换表名 2020-07-03T10:49:08 Swapping tables... RENAME TABLE `pt_test`.`pt_osc_1` TO `pt_test`.`_pt_osc_1_old`, `pt_test`.`_pt_osc_1_new` TO `pt_test`.`pt_osc_1` 2020-07-03T10:49:08 Swapped original and new tables OK. # 删除旧表 2020-07-03T10:49:08 Dropping old table... DROP TABLE IF EXISTS `pt_test`.`_pt_osc_1_old` 2020-07-03T10:49:09 Dropped old table `pt_test`.`_pt_osc_1_old` OK. 2020-07-03T10:49:09 Dropping triggers... # 删除触发器 DROP TRIGGER IF EXISTS `pt_test`.`pt_osc_pt_test_pt_osc_1_del` DROP TRIGGER IF EXISTS `pt_test`.`pt_osc_pt_test_pt_osc_1_upd` DROP TRIGGER IF EXISTS `pt_test`.`pt_osc_pt_test_pt_osc_1_ins` 2020-07-03T10:49:09 Dropped triggers OK. Successfully altered `pt_test`.`pt_osc_1`.通过全局日志查看详细过程
# 链接 2020-07-03T10:48:46.466411+08:00 370 Connect root@localhost on pt_test using TCP/IP # 检查并设置锁超时时间和链接超时时间以及sql_mode以及版本等信息 2020-07-03T10:48:46.466838+08:00 370 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' 2020-07-03T10:48:46.469033+08:00 370 Query SET SESSION innodb_lock_wait_timeout=1 2020-07-03T10:48:46.469204+08:00 370 Query SHOW VARIABLES LIKE 'lock\_wait_timeout' 2020-07-03T10:48:46.470890+08:00 370 Query SET SESSION lock_wait_timeout=60 2020-07-03T10:48:46.471045+08:00 370 Query SHOW VARIABLES LIKE 'wait\_timeout' 2020-07-03T10:48:46.472562+08:00 370 Query SET SESSION wait_timeout=10000 2020-07-03T10:48:46.483177+08:00 371 Query SELECT @@server_id /*!50038 , @@hostname*/ 2020-07-03T10:48:46.483472+08:00 370 Query SHOW VARIABLES LIKE 'wsrep_on' 2020-07-03T10:48:46.485135+08:00 370 Query SHOW VARIABLES LIKE 'version%' 2020-07-03T10:48:46.486782+08:00 370 Query SHOW ENGINES 2020-07-03T10:48:46.487253+08:00 370 Query SHOW VARIABLES LIKE 'innodb_version' 2020-07-03T10:48:46.500382+08:00 370 Query SHOW VARIABLES LIKE 'innodb_stats_persistent' 2020-07-03T10:48:46.502663+08:00 370 Query SELECT @@SERVER_ID # 查看当前用户并链接从库 2020-07-03T10:48:46.502875+08:00 370 Query SHOW GRANTS FOR CURRENT_USER() 2020-07-03T10:48:46.503084+08:00 370 Query SHOW FULL PROCESSLIST 2020-07-03T10:48:46.504987+08:00 372 Connect root@***.***.***.*** on using TCP/IP 2020-07-03T10:48:46.505017+08:00 372 Connect Access denied for user 'root'@'152.136.115.236' (using password: YES) 2020-07-03T10:48:46.506965+08:00 373 Connect root@***.***.***.*** on using TCP/IP 2020-07-03T10:48:46.506991+08:00 373 Connect Access denied for user 'root'@'152.136.115.236' (using password: YES) 2020-07-03T10:48:46.508825+08:00 374 Connect root@***.***.***.*** on using TCP/IP 2020-07-03T10:48:46.508875+08:00 374 Connect Access denied for user 'root'@'152.136.115.236' (using password: YES) 2020-07-03T10:48:46.511866+08:00 375 Connect root@***.***.***.*** on using TCP/IP 2020-07-03T10:48:46.511894+08:00 375 Connect Access denied for user 'root'@'152.136.115.236' (using password: YES) # 查看当前线程数量,观测负载是否过高 2020-07-03T10:48:46.512786+08:00 370 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2020-07-03T10:48:46.514173+08:00 370 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2020-07-03T10:48:46.515510+08:00 370 Query SELECT CONCAT(@@hostname, @@port) 2020-07-03T10:48:46.515986+08:00 370 Query SHOW TABLES FROM `pt_test` LIKE 'pt\_osc\_1' 2020-07-03T10:48:46.516271+08:00 370 Query SELECT VERSION() # 查看当前表是否有触发器 2020-07-03T10:48:46.516482+08:00 370 Query SHOW TRIGGERS FROM `pt_test` LIKE 'pt\_osc\_1' # 查看是否存在外键 2020-07-03T10:48:46.518743+08:00 370 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='pt_test' AND referenced_table_name='pt_osc_1' # 创建表 2020-07-03T10:48:46.548541+08:00 370 Query CREATE TABLE `pt_test`.`_pt_osc_1_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` tinyint(1) DEFAULT NULL, `sex` enum('man','women') DEFAULT 'man', `info` varchar(100) DEFAULT 'have a good day', PRIMARY KEY (`id`), KEY `idx_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=1270013 DEFAULT CHARSET=utf8 # 修改表结构 2020-07-03T10:48:46.588347+08:00 370 Query ALTER TABLE `pt_test`.`_pt_osc_1_new` drop index idx_age # 检查是否有触发器(共6个,对于uodate,insert,delete的before,after的触发器) 2020-07-03T10:48:46.606298+08:00 370 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'pt_test' AND EVENT_OBJECT_TABLE = 'pt_osc_1' 2020-07-03T10:48:46.607263+08:00 370 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'pt_test' AND EVENT_OBJECT_TABLE = 'pt_osc_1' 2020-07-03T10:48:46.608132+08:00 370 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'pt_test' AND EVENT_OBJECT_TABLE = 'pt_osc_1' 2020-07-03T10:48:46.608877+08:00 370 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'pt_test' AND EVENT_OBJECT_TABLE = 'pt_osc_1' 2020-07-03T10:48:46.609648+08:00 370 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'pt_test' AND EVENT_OBJECT_TABLE = 'pt_osc_1' 2020-07-03T10:48:46.610366+08:00 370 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'pt_test' AND EVENT_OBJECT_TABLE = 'pt_osc_1' 创建触发器 2020-07-03T10:48:46.611152+08:00 370 Query CREATE TRIGGER `pt_osc_pt_test_pt_osc_1_del` AFTER DELETE ON `pt_test`.`pt_osc_1` FOR EACH ROW DELETE IGNORE FROM `pt_test`.`_pt_osc_1_new` WHERE `pt_test`.`_pt_osc_1_new`.`id` <=> OLD.`id` 2020-07-03T10:48:46.620854+08:00 370 Query CREATE TRIGGER `pt_osc_pt_test_pt_osc_1_upd` AFTER UPDATE ON `pt_test`.`pt_osc_1` FOR EACH ROW BEGIN DELETE IGNORE FROM `pt_test`.`_pt_osc_1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `pt_test`.`_pt_osc_1_new`.`id` <=> OLD.`id`;REPLACE INTO `pt_test`.`_pt_osc_1_new` (`id`, `name`, `age`, `sex`, `info`) VALUES (NEW.`id`, NEW.`name`, NEW.`age`, NEW.`sex`, NEW.`info`);END 2020-07-03T10:48:46.630283+08:00 370 Query CREATE TRIGGER `pt_osc_pt_test_pt_osc_1_ins` AFTER INSERT ON `pt_test`.`pt_osc_1` FOR EACH ROW REPLACE INTO `pt_test`.`_pt_osc_1_new` (`id`, `name`, `age`, `sex`, `info`) VALUES (NEW.`id`, NEW.`name`, NEW.`age`, NEW.`sex`, NEW.`info`) 检查每一个数据块的起始id 2020-07-03T10:48:46.638632+08:00 370 Query EXPLAIN SELECT * FROM `pt_test`.`pt_osc_1` WHERE 1=1 2020-07-03T10:48:46.640148+08:00 370 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/ 2020-07-03T10:48:46.640758+08:00 370 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/ 2020-07-03T10:48:46.641088+08:00 370 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `pt_test`.`pt_osc_1` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_len*/ 2020-07-03T10:48:46.641686+08:00 370 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/ 2020-07-03T10:48:46.642022+08:00 370 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/ # 按数据块插入数据 2020-07-03T10:48:46.642968+08:00 370 Query EXPLAIN SELECT `id`, `name`, `age`, `sex`, `info` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 2459 copy nibble*/ 2020-07-03T10:48:46.643469+08:00 370 Query INSERT LOW_PRIORITY IGNORE INTO `pt_test`.`_pt_osc_1_new` (`id`, `name`, `age`, `sex`, `info`) SELECT `id`, `name`, `age`, `sex`, `info` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE /*pt-online-schema-change 2459 copy nibble*/ 2020-07-03T10:48:46.666795+08:00 370 Query SHOW WARNINGS 2020-07-03T10:48:46.667272+08:00 370 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 2020-07-03T10:48:46.668973+08:00 370 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 21480, 2 /*next chunk boundary*/ 2020-07-03T10:48:46.670148+08:00 370 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 21480, 2 /*next chunk boundary*/ 2020-07-03T10:48:46.711830+08:00 370 Query EXPLAIN SELECT `id`, `name`, `age`, `sex`, `info` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '22481')) LOCK IN SHARE MODE /*explain pt-online-schema-change 2459 copy nibble*/ 2020-07-03T10:48:46.712513+08:00 370 Query INSERT LOW_PRIORITY IGNORE INTO `pt_test`.`_pt_osc_1_new` (`id`, `name`, `age`, `sex`, `info`) SELECT `id`, `name`, `age`, `sex`, `info` FROM `pt_test`.`pt_osc_1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '22481')) LOCK IN SHARE MODE /*pt-online-schema-change 2459 copy nibble*/ 2020-07-03T10:49:08.847235+08:00 370 Query SHOW WARNINGS 2020-07-03T10:49:08.847791+08:00 370 Query SHOW GLOBAL STATUS LIKE 'Threads_running' # 修复索引 2020-07-03T10:49:08.849748+08:00 370 Query ANALYZE TABLE `pt_test`.`_pt_osc_1_new` /* pt-online-schema-change */ # 重命名表 2020-07-03T10:49:08.864306+08:00 370 Query RENAME TABLE `pt_test`.`pt_osc_1` TO `pt_test`.`_pt_osc_1_old`, `pt_test`.`_pt_osc_1_new` TO `pt_test`.`pt_osc_1` # 删除表及触发器 2020-07-03T10:49:08.939689+08:00 370 Query DROP TABLE IF EXISTS `pt_test`.`_pt_osc_1_old` 2020-07-03T10:49:09.114312+08:00 370 Query DROP TRIGGER IF EXISTS `pt_test`.`pt_osc_pt_test_pt_osc_1_del` 2020-07-03T10:49:09.114661+08:00 370 Query DROP TRIGGER IF EXISTS `pt_test`.`pt_osc_pt_test_pt_osc_1_upd` 2020-07-03T10:49:09.114900+08:00 370 Query DROP TRIGGER IF EXISTS `pt_test`.`pt_osc_pt_test_pt_osc_1_ins` 2020-07-03T10:49:09.120227+08:00 370 Query SHOW TABLES FROM `pt_test` LIKE '\_pt\_osc\_1\_new' 2020-07-03T10:49:09.123379+08:00 371 Quit 2020-07-03T10:49:09.123761+08:00 370 Quit