Preface
 
    As we all know,it's really a troublesome thing to DBA in scenario of changing table structure online.Since MySQL 5.6,it support online ddl operations.On the other hand,we also use third party tool such as gh-ost.Let's learn another famous tool of Percona-Toolkit,that is pt-online-schema-change.
 
Introduce
 
    pt-osc is a tool which is commonly used in Percona-Toolkit suits which .It bases on triggers,by creating temporary middle table to dipose the table structure changing issue.MySQL 8.0.13 will support a new feature of instant adding columns(but only add,not change).Therefore,ps-osc still has its value in the "online ddl change" field.
 
Procedure
 
 ###Commonly used parameter introduce.###

 connect parameter:

 -h hostname -P port -u username -p password -S socket 

 output parameter:

 --print -- Show whole procedure of ddl operation on the screen.

 main parameter:

 --alter -- Specify the ddl statement using quotation mark behind.

 alternative parameter:

 --dry-run -- Only operate the new table instead of original one.

 --execute -- Directly do all opertion both new and original table.

 other parameter:

 --no-check-alter -- Don't parse your ddl statment which follows behind.

 --ask-pass -- You cannot use -p without a string to input password,so you need this parameter.

 ###Create test table.###

 (root@localhost mysql3306.sock)[zlm]::>create table test_ddl(

     -> id int unsigned auto_increment,

     -> name varchar() not null default '',

     -> tel tinyint unsigned not null default ,

     -> primary key(id)

     -> ) engine=innodb charset=utf8mb4;

 Query OK,  rows affected (0.01 sec)

 ###Insert records into table.###

 (root@localhost mysql3306.sock)[zlm]::>insert into test_ddl(name,tel) values('zlm',);

 ERROR  (): Out of range value for column 'tel' at row

 (root@localhost mysql3306.sock)[zlm]::>insert into test_ddl(name,tel) values('zlm',);

 ERROR  (): Out of range value for column 'tel' at row

 (root@localhost mysql3306.sock)[zlm]::>insert into test_ddl(name,tel) values('zlm',); -- Scope of unsigned tinyint is ~.

 Query OK,  row affected (0.00 sec)

 (root@localhost mysql3306.sock)[zlm]::>select * from test_ddl;

 +----+------+-----+

 | id | name | tel |

 +----+------+-----+

 |  | zlm  |  |

 +----+------+-----+

  row in set (0.00 sec)

 ###Change column "tel" from tinyint to bigint.###

 [root@zlm2 :: ~]

 #pt-online-schema-change -hzlm2 -P3306 -uroot --ask-pass --alter "modify tel bigint default 0" --print --execute --no-check-alter D=zlm,t=test_ddl

 Enter MySQL password: 

 ###Check slave information.###

 No slaves found.  See --recursion-method if host zlm2 has slaves.

 Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

 ###Summary of operations.###

 Operation, tries, wait:

   analyze_table, ,

   copy_rows, , 0.25

   create_triggers, ,

   drop_triggers, ,

   swap_tables, ,

   update_foreign_keys, ,

 Altering `zlm`.`test_ddl`...

 ###Create new table.###

 Creating new table...

 CREATE TABLE `zlm`.`_test_ddl_new` (

   `id` int() unsigned NOT NULL AUTO_INCREMENT,

   `name` varchar() NOT NULL DEFAULT '',

   `tel` tinyint() unsigned NOT NULL DEFAULT '',

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4

 Created new table zlm._test_ddl_new OK.

 ###Alter column in new table.###

 Altering new table...

 ALTER TABLE `zlm`.`_test_ddl_new` modify tel bigint default

 Altered `zlm`.`_test_ddl_new` OK. -- If you specify "dry-run",it will end here.

 ###Create Triggers.###

 --20T10:: Creating triggers...

 --20T10:: Created triggers OK.

 ###Copy rows into new table.###

 --20T10:: Copying approximately  rows...

 INSERT LOW_PRIORITY IGNORE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*pt-online-schema-change 4190 copy table*/

 --20T10:: Copied rows OK.

 ###Swap oraiginal table with new table.###

 --20T10:: Analyzing new table...

 --20T10:: Swapping tables...

 RENAME TABLE `zlm`.`test_ddl` TO `zlm`.`_test_ddl_old`, `zlm`.`_test_ddl_new` TO `zlm`.`test_ddl`

 --20T10:: Swapped original and new tables OK.

 ###Drop original table.###

 --20T10:: Dropping old table...

 DROP TABLE IF EXISTS `zlm`.`_test_ddl_old`

 --20T10:: Dropped old table `zlm`.`_test_ddl_old` OK.

 ###Drop triggers.###

 --20T10:: Dropping triggers...

 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_del`

 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_upd`

 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_ins`

 --20T10:: Dropped triggers OK.

 Successfully altered `zlm`.`test_ddl`.

 ###Check table ddl.###

 (root@localhost mysql3306.sock)[zlm]::>show create table test_ddl;

 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 | Table    | Create Table                                                                                                                                                                                                                        |

 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 | test_ddl | CREATE TABLE `test_ddl` (

   `id` int() unsigned NOT NULL AUTO_INCREMENT,

   `name` varchar() NOT NULL DEFAULT '',

   `tel` bigint() DEFAULT '', -- The column "tel" has be changed into bigint type now.

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4 |

 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

  row in set (0.01 sec)

 ###Check general log file for detail.###

 [root@zlm2 :: /data/mysql/mysql3306/data]

 #cat zlm2.log

 mysqld, Version: 5.7.-log (MySQL Community Server (GPL)). started with:

 Tcp port:   Unix socket: /tmp/mysql3306.sock

 Time                 Id Command    Argument

 --20T08::.476403Z        Query    show global variables like '%general_log%'

 --20T08::.828985Z        Connect    root@localhost on zlm using TCP/IP

 --20T08::.829297Z        Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

 --20T08::.832850Z        Query    SET SESSION innodb_lock_wait_timeout=

 --20T08::.833041Z        Query    SHOW VARIABLES LIKE 'lock\_wait_timeout'

 --20T08::.836907Z        Query    SET SESSION lock_wait_timeout=

 --20T08::.837752Z        Query    SHOW VARIABLES LIKE 'wait\_timeout'

 --20T08::.842090Z        Query    SET SESSION wait_timeout=

 --20T08::.842222Z        Query    SELECT @@SQL_MODE

 --20T08::.842350Z        Query    SET @@SQL_QUOTE_SHOW_CREATE = /*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/

 --20T08::.842453Z        Query    SELECT @@server_id /*!50038 , @@hostname*/

 --20T08::.843311Z        Connect    root@localhost on zlm using TCP/IP

 --20T08::.843620Z        Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

 --20T08::.847069Z        Query    SET SESSION innodb_lock_wait_timeout=

 --20T08::.847442Z        Query    SHOW VARIABLES LIKE 'lock\_wait_timeout'

 --20T08::.851645Z        Query    SET SESSION lock_wait_timeout=

 --20T08::.851904Z        Query    SHOW VARIABLES LIKE 'wait\_timeout'

 --20T08::.853942Z        Query    SET SESSION wait_timeout=

 --20T08::.854029Z        Query    SELECT @@SQL_MODE

 --20T08::.854055Z        Query    SET @@SQL_QUOTE_SHOW_CREATE = /*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/

 --20T08::.854152Z        Query    SELECT @@server_id /*!50038 , @@hostname*/

 --20T08::.854356Z        Query    SHOW VARIABLES LIKE 'wsrep_on'

 --20T08::.857148Z        Query    SHOW VARIABLES LIKE 'version%'

 --20T08::.860325Z        Query    SHOW ENGINES

 --20T08::.861002Z        Query    SHOW VARIABLES LIKE 'innodb_version'

 --20T08::.864957Z        Query    SHOW VARIABLES LIKE 'innodb_stats_persistent'

 --20T08::.867873Z        Query    SELECT @@SERVER_ID

 --20T08::.868114Z        Query    SHOW GRANTS FOR CURRENT_USER()

 --20T08::.868774Z        Query    SHOW FULL PROCESSLIST

 --20T08::.869245Z        Query    SHOW SLAVE HOSTS

 --20T08::.869849Z        Query    SHOW GLOBAL STATUS LIKE 'Threads_running'

 --20T08::.874152Z        Query    SHOW GLOBAL STATUS LIKE 'Threads_running'

 --20T08::.877509Z        Query    SELECT CONCAT(@@hostname, @@port)

 --20T08::.877973Z        Query    SHOW TABLES FROM `zlm` LIKE 'test\_ddl'

 --20T08::.878229Z        Query    SELECT VERSION()

 --20T08::.879011Z        Query    SHOW TRIGGERS FROM `zlm` LIKE 'test\_ddl'

 --20T08::.879506Z        Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

 --20T08::.879602Z        Query    USE `zlm`

 --20T08::.879706Z        Query    SHOW CREATE TABLE `zlm`.`test_ddl`

 --20T08::.880123Z        Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

 --20T08::.880979Z        Query    EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE =

 --20T08::.881846Z        Query    SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='zlm' AND referenced_table_name='test_ddl'

 --20T08::.893011Z        Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

 --20T08::.893622Z        Query    USE `zlm`

 --20T08::.893798Z        Query    SHOW CREATE TABLE `zlm`.`test_ddl`

 --20T08::.893954Z        Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

 --20T08::.894730Z        Query    CREATE TABLE `zlm`.`_test_ddl_new` (

   `id` int() unsigned NOT NULL AUTO_INCREMENT,

   `name` varchar() NOT NULL DEFAULT '',

   `tel` int() DEFAULT '',

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4

 --20T08::.914358Z        Query    ALTER TABLE `zlm`.`_test_ddl_new` modify tel bigint default

 --20T08::.934771Z        Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

 --20T08::.934930Z        Query    USE `zlm`

 --20T08::.935044Z        Query    SHOW CREATE TABLE `zlm`.`_test_ddl_new`

 --20T08::.936057Z        Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

 --20T08::.936926Z        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 = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'

 --20T08::.937687Z        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 = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'

 --20T08::.939328Z        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 = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'

 --20T08::.940991Z        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 = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'

 --20T08::.942390Z        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 = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'

 --20T08::.943844Z        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 = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'

 --20T08::.944624Z        Query    CREATE TRIGGER `pt_osc_zlm_test_ddl_del` AFTER DELETE ON `zlm`.`test_ddl` FOR EACH ROW DELETE IGNORE FROM `zlm`.`_test_ddl_new` WHERE `zlm`.`_test_ddl_new`.`id` <=> OLD.`id`

 --20T08::.949413Z        Query    CREATE TRIGGER `pt_osc_zlm_test_ddl_upd` AFTER UPDATE ON `zlm`.`test_ddl` FOR EACH ROW BEGIN DELETE IGNORE FROM `zlm`.`_test_ddl_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `zlm`.`_test_ddl_new`.`id` <=> OLD.`id`;REPLACE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) VALUES (NEW.`id`, NEW.`name`, NEW.`tel`);END

 --20T08::.954329Z        Query    CREATE TRIGGER `pt_osc_zlm_test_ddl_ins` AFTER INSERT ON `zlm`.`test_ddl` FOR EACH ROW REPLACE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) VALUES (NEW.`id`, NEW.`name`, NEW.`tel`)

 --20T08::.959241Z        Query    EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE =

 --20T08::.961358Z        Query    EXPLAIN SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*explain pt-online-schema-change 4190 copy table*/

 --20T08::.962004Z        Query    INSERT LOW_PRIORITY IGNORE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*pt-online-schema-change 4190 copy table*/

 --20T08::.962769Z        Query    SHOW WARNINGS

 --20T08::.963561Z        Query    SHOW GLOBAL STATUS LIKE 'Threads_running'

 --20T08::.966643Z        Query    ANALYZE TABLE `zlm`.`_test_ddl_new` /* pt-online-schema-change */

 --20T08::.968221Z        Query    RENAME TABLE `zlm`.`test_ddl` TO `zlm`.`_test_ddl_old`, `zlm`.`_test_ddl_new` TO `zlm`.`test_ddl`

 --20T08::.983005Z        Query    DROP TABLE IF EXISTS `zlm`.`_test_ddl_old`

 --20T08::.990393Z        Query    DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_del`

 --20T08::.991005Z        Query    DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_upd`

 --20T08::.991346Z        Query    DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_ins`

 --20T08::.991966Z        Query    SHOW TABLES FROM `zlm` LIKE '\_test\_ddl\_new'

 --20T08::.999016Z        Quit

 --20T08::.000302Z        Quit

Supplement

 ###Create a test table "test_ddl_no_pk" without primary key.###

 (root@localhost mysql3306.sock)[zlm]::>create table test_ddl_no_pk(

     -> id int unsigned,

     -> name varchar() not null default '',

     -> tel tinyint unsigned not null default

     -> ) engine=innodb charset=utf8mb4;

 Query OK,  rows affected (0.12 sec)

 (root@localhost mysql3306.sock)[zlm]::>insert into test_ddl_no_pk(id,name,tel) values(,'zlm',''),(,'aaron8219','');

 Query OK,  rows affected (0.00 sec)

 Records:   Duplicates:   Warnings: 

 ###Insert two records into above table.###

 (root@localhost mysql3306.sock)[zlm]::>select * from test_ddl_no_pk;

 +------+-----------+-----+

 | id   | name      | tel |

 +------+-----------+-----+

 |     | zlm       |  |

 |     | aaron8219 |  |

 +------+-----------+-----+

  rows in set (0.00 sec)

 ###Execute pt-osc again.###

 [root@zlm2 :: ~]

 #pt-online-schema-change -hzlm2 -P3306 -uroot --ask-pass --alter "modify tel bigint default 0" --print --execute --no-check-alter D=zlm,t=test_ddl_no_pk

 Enter MySQL password:

 No slaves found.  See --recursion-method if host zlm2 has slaves.

 Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

 Operation, tries, wait:

   analyze_table, ,

   copy_rows, , 0.25

   create_triggers, ,

   drop_triggers, ,

   swap_tables, ,

   update_foreign_keys, ,

 Altering `zlm`.`test_ddl_no_pk`...

 Creating new table...

 CREATE TABLE `zlm`.`_test_ddl_no_pk_new` (

   `id` int() unsigned DEFAULT NULL,

   `name` varchar() NOT NULL DEFAULT '',

   `tel` tinyint() unsigned NOT NULL DEFAULT ''

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

 Created new table zlm._test_ddl_no_pk_new OK.

 Altering new table...

 ALTER TABLE `zlm`.`_test_ddl_no_pk_new` modify tel bigint default

 Altered `zlm`.`_test_ddl_no_pk_new` OK.

 --21T04:: Dropping new table...

 DROP TABLE IF EXISTS `zlm`.`_test_ddl_no_pk_new`; -- There're no create triggers,copy rows and swap table operations any more but directly drop new table operation.

 --21T04:: Dropped new table OK.

 `zlm`.`test_ddl_no_pk` was not altered.

 The new table `zlm`.`_test_ddl_no_pk_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger. -- Indicate the reason why it stop working.
Summary
  • pt-ocs depends on tirggers,there mustn't be triggers on original table.
  • Primary key or unique key is indispensable when use it.Otherwise,it won't work normally.
  • Although pt-ocs won't block online transactions but still recommend do online ddl operations in off-peak period.

最新文章

  1. C语言指针转换为intptr_t类型
  2. Only Link: Inheritance and the prototype chain
  3. LL谱面分析和难度标定
  4. 与子域名共用session信息
  5. iOS开发--项目内存优化
  6. SQL事物用法【转】
  7. C#指定某用户对某文夹件的访问权限
  8. MySQL慢日志查询全解析：从参数、配置到分析工具【转】
  9. Javascript基本语句
  10. Centos 7系统启动修复
  11. 05 Android强制设置横屏或竖屏/全屏
  12. 使用PHP操作ElasticSearch
  13. 游记-WC2019
  14. Complete Binary Search Tree
  15. Java核心技术及面试指南面试题，基本数据类型、封装类和运算操作的面试题
  16. WPF:自定义Metro样式文件夹选择对话框FolderBrowserDialog
  17. Spring Boot 启动：No active profile set, falling back to default profiles: default
  18. Openstack1 云计算与虚拟化概念
  19. unity 动画无法正常播放Animation的动画问题
  20. 【Java】类加载过程

热门文章

  1. 关于js 中函数的参数
  2. 针对 IE的 的优化
  3. SpagoBI 和 开源ERP(iDempiere)整合入门
  4. Shader之性能优化
  5. import模块后查找模块的绝对路径
  6. input file 类型为excel表格
  7. Hadoop -&gt;&gt; HDFS(Hadoop Distributed File System)
  8. ubuntu 18 下配置 WebStorm 编译 sass
  9. oracle_union_operator
  10. vue v-on:事件