前言：前段时间对pt-osc工具进行了原理性分析，但是pt-osc在生产上还是有一些不足的地方，如从库有触发器等就无法使用pt-osc，今天我们使用的是另外一个在线表结构变更工具gh-osc，体验一把新科技

一、安装

gh-osc的安装基本上就是开包即用，到https://github.com/github/gh-ost/releases上下载最新版本的gh-osc的二进制包，解压即可使用。

二、使用参数

  1. [root@localhost tmp]# ./gh-ost --help
  2. Usage of gh-ost:
  3.   -aliyun-rds
  4.         当在阿里云RDS执行时设置为true
  5.   -allow-master-master
  6.         允许在双主架构中使用
  7.   -allow-nullable-unique-key
  8.         允许gh-ost基于带空列的唯一键进行迁移。只要不存在空值，这应该是可以的。如果在选定的键中存在空值，数据可能会被破坏
  9.   -allow-on-master
  10.         允许直接在主库上迁移。最好是在从库上运行。 
  11.   -alter string
  12.         执行的具体命令
  13.   -ask-pass
  14.         询问输入密码
  15.   -chunk-size int
  16.         分批提交的最大值，默认1000
  17.   -conf string
  18.         指定配置文件名
  19.   -critical-load string
  20.         指定gh-osc退出的阈值，load超过这个值的话gh-osc自动退出
  21.   -cut-over-lock-timeout-seconds int
  22.         定义持有锁的最大时间，超时退出
  23.   -database string
  24.         dbname
  25.   -debug
  26.         debug模式
  27.   -default-retries int
  28.         重连次数
  29.   -discard-foreign-keys
  30.         去除外键
  31.   -dml-batch-size int
  32.         在单个事务中应用DML事件的批处理大小(范围为1-100)(默认为10) 
  33.   -execute
  34.         如果需要直接执行，打开此参数
  35.   -master-password string
  36.         master的密码
  37.   -master-user string
  38.         master的用户名
  39.   -max-lag-millis int
  40.         监控主从延迟，超过该阈值后停止迁移，等待延迟正常
  41.   -max-load string
  42.         定义数据库最大负载阈值
  43.   -migrate-on-replica
  44.         在从库上迁移
  45.   -nice-ratio float
  46.         force being 'nice', imply sleep time per chunk time; range: [0.0..100.0]. Example values: 0 is aggressive. 1: for every 1ms spent copying rows, sleep additional 1ms (effectively doubling runtime); 0.7: for every 10ms spend in a rowcopy chunk, spend 7ms sleeping immediately after
  47.   -ok-to-drop-table
  48.         迁移完成后删除旧表
  49.   -password string
  50.         askMySQL的密码
  51.   -port int
  52.         mysql的端口，默认3306
  53.   -user string
  54.         用户名

实际上，gh-osc的使用模式一共有三种:

  • 连接到主库，所有的操作都在主库上做变更，包括实时读取binlog，可能会造成主从复制延迟的问题

  • 连接从库，在主库上读写，读取从库的binlog，然后将变更应用到主

  • 在从库�迁移测试，即在迁移完成后，只在从库更改表结构，但是切换完成后，主库仍是旧表，从库是新表

三、测试

下面我们结合general log在主库上测试一把

  1. [root@localhost tmp]# ./gh-ost -user="root" -password="xcl582388" -host="localhost" -port=3306 -allow-on-master -database="xucl" -table="t" -debug -alter "add column test varchar(10)" -assume-rbr --execute --initially-drop-old-table --ok-to-drop-table
  2. 2018-05-22 11:56:08 INFO starting gh-ost 1.0.45
  3. 2018-05-22 11:56:08 INFO Migrating `xucl`.`t`
  4. 2018-05-22 11:56:08 INFO connection validated on localhost:3306
  5. 2018-05-22 11:56:08 INFO User has ALL privileges
  6. 2018-05-22 11:56:08 INFO binary logs validated on localhost:3306
  7. 2018-05-22 11:56:08 INFO Inspector initiated on localhost.localdomain:3306, version 5.7.20-log
  8. 2018-05-22 11:56:08 INFO Table found. Engine=InnoDB
  9. 2018-05-22 11:56:08 DEBUG Estimated number of rows via STATUS: 0
  10. 2018-05-22 11:56:08 DEBUG Validated no foreign keys exist on table
  11. 2018-05-22 11:56:08 DEBUG Validated no triggers exist on table
  12. 2018-05-22 11:56:08 INFO Estimated number of rows via EXPLAIN: 1
  13. 2018-05-22 11:56:08 DEBUG Potential unique keys in t: [PRIMARY: [user flag id]; has nullable: false]
  14. 2018-05-22 11:56:08 INFO Recursively searching for replication master
  15. 2018-05-22 11:56:08 DEBUG Looking for master on localhost:3306
  16. 2018-05-22 11:56:08 INFO Master found to be localhost.localdomain:3306
  17. 2018-05-22 11:56:08 INFO log_slave_updates validated on localhost:3306
  18. 2018-05-22 11:56:08 INFO connection validated on localhost:3306
  19. 2018-05-22 11:56:08 DEBUG Streamer binlog coordinates: mysql-bin.000001:15862
  20. 2018/05/22 11:56:08 binlogsyncer.go:79: [info] create BinlogSyncer with config {99999 mysql localhost 3306 root   false false <nil>}
  21. 2018-05-22 11:56:08 INFO Connecting binlog streamer at mysql-bin.000001:15862
  22. 2018/05/22 11:56:08 binlogsyncer.go:246: [info] begin to sync binlog from position (mysql-bin.000001, 15862)
  23. 2018/05/22 11:56:08 binlogsyncer.go:139: [info] register slave for master server localhost:3306
  24. 2018-05-22 11:56:08 DEBUG Beginning streaming
  25. 2018/05/22 11:56:08 binlogsyncer.go:573: [info] rotate to (mysql-bin.000001, 15862)
  26. 2018-05-22 11:56:08 INFO rotate to next log name: mysql-bin.000001
  27. 2018-05-22 11:56:08 INFO connection validated on localhost:3306
  28. 2018-05-22 11:56:08 INFO connection validated on localhost:3306
  29. 2018-05-22 11:56:08 INFO will use time_zone='SYSTEM' on applier
  30. 2018-05-22 11:56:08 INFO Examining table structure on applier
  31. 2018-05-22 11:56:08 INFO Applier initiated on localhost.localdomain:3306, version 5.7.20-log
  32. 2018-05-22 11:56:08 INFO Dropping table `xucl`.`_t_del`
  33. 2018-05-22 11:56:08 INFO Table dropped
  34. 2018-05-22 11:56:08 INFO Dropping table `xucl`.`_t_ghc`
  35. 2018-05-22 11:56:08 INFO Table dropped
  36. 2018-05-22 11:56:08 INFO Creating changelog table `xucl`.`_t_ghc`
  37. 2018-05-22 11:56:08 INFO Changelog table created
  38. 2018-05-22 11:56:08 INFO Creating ghost table `xucl`.`_t_gho`
  39. 2018-05-22 11:56:08 INFO Ghost table created
  40. 2018-05-22 11:56:08 INFO Altering ghost table `xucl`.`_t_gho`
  41. 2018-05-22 11:56:08 DEBUG ALTER statement: alter /* gh-ost */ table `xucl`.`_t_gho` add column test varchar(10)
  42. 2018-05-22 11:56:08 INFO Ghost table altered
  43. 2018-05-22 11:56:08 INFO Intercepted changelog state GhostTableMigrated
  44. 2018-05-22 11:56:08 INFO Waiting for ghost table to be migrated. Current lag is 0s
  45. 2018-05-22 11:56:08 DEBUG ghost table migrated
  46. 2018-05-22 11:56:08 INFO Handled changelog state GhostTableMigrated
  47. 2018-05-22 11:56:08 DEBUG Potential unique keys in _t_gho: [PRIMARY: [user flag id]; has nullable: false]
  48. 2018-05-22 11:56:08 INFO Chosen shared unique key is PRIMARY
  49. 2018-05-22 11:56:08 INFO Shared columns are user,flag,id,tttt
  50. 2018-05-22 11:56:08 INFO Listening on unix socket file: /tmp/gh-ost.xucl.t.sock
  51. 2018-05-22 11:56:08 DEBUG Reading migration range according to key: PRIMARY
  52. 2018-05-22 11:56:08 INFO Migration min values: [<nil>]
  53. 2018-05-22 11:56:08 DEBUG Reading migration range according to key: PRIMARY
  54. 2018-05-22 11:56:08 INFO Migration max values: [<nil>]
  55. 2018-05-22 11:56:08 INFO Waiting for first throttle metrics to be collected
  56. 2018-05-22 11:56:08 INFO First throttle metrics collected
  57. 2018-05-22 11:56:08 DEBUG Operating until row copy is complete
  58. # Migrating `xucl`.`t`; Ghost table is `xucl`.`_t_gho`
  59. # Migrating localhost.localdomain:3306; inspecting localhost.localdomain:3306; executing on localhost.localdomain
  60. # Migration started at Tue May 22 11:56:08 +0200 2018
  61. # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
  62. # throttle-additional-flag-file: /tmp/gh-ost.throttle 
  63. # Serving on unix socket: /tmp/gh-ost.xucl.t.sock
  64. 2018-05-22 11:56:08 DEBUG Getting nothing in the write queue. Sleeping...
  65. 2018-05-22 11:56:08 DEBUG No rows found in table. Rowcopy will be implicitly empty
  66. 2018-05-22 11:56:08 INFO Row copy complete
  67. # Migrating `xucl`.`t`; Ghost table is `xucl`.`_t_gho`
  68. # Migrating localhost.localdomain:3306; inspecting localhost.localdomain:3306; executing on localhost.localdomain
  69. # Migration started at Tue May 22 11:56:08 +0200 2018
  70. # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
  71. # throttle-additional-flag-file: /tmp/gh-ost.throttle 
  72. # Serving on unix socket: /tmp/gh-ost.xucl.t.sock
  73. Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000001:18076; State: migrating; ETA: due
  74. 2018-05-22 11:56:08 DEBUG checking for cut-over postpone
  75. 2018-05-22 11:56:08 DEBUG checking for cut-over postpone: complete
  76. 2018-05-22 11:56:08 INFO Grabbing voluntary lock: gh-ost.41.lock
  77. Copy: 0/1 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000001:18076; State: migrating; ETA: N/A
  78. 2018-05-22 11:56:08 INFO Setting LOCK timeout as 6 seconds
  79. 2018-05-22 11:56:08 INFO Looking for magic cut-over table
  80. 2018-05-22 11:56:08 INFO Creating magic cut-over table `xucl`.`_t_del`
  81. 2018-05-22 11:56:08 INFO Magic cut-over table created
  82. 2018-05-22 11:56:08 INFO Locking `xucl`.`t`, `xucl`.`_t_del`
  83. 2018-05-22 11:56:08 INFO Tables locked
  84. 2018-05-22 11:56:08 INFO Session locking original & magic tables is 41
  85. 2018-05-22 11:56:08 INFO Writing changelog state: AllEventsUpToLockProcessed:1526982968208522643
  86. 2018-05-22 11:56:08 INFO Intercepted changelog state AllEventsUpToLockProcessed
  87. 2018-05-22 11:56:08 INFO Handled changelog state AllEventsUpToLockProcessed
  88. 2018-05-22 11:56:08 INFO Waiting for events up to lock
  89. 2018-05-22 11:56:09 DEBUG Getting nothing in the write queue. Sleeping...
  90. 2018-05-22 11:56:09 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1526982968208522643
  91. 2018-05-22 11:56:09 INFO Done waiting for events up to lock; duration=968.589035ms
  92. # Migrating `xucl`.`t`; Ghost table is `xucl`.`_t_gho`
  93. # Migrating localhost.localdomain:3306; inspecting localhost.localdomain:3306; executing on localhost.localdomain
  94. # Migration started at Tue May 22 11:56:08 +0200 2018
  95. # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
  96. # throttle-additional-flag-file: /tmp/gh-ost.throttle 
  97. # Serving on unix socket: /tmp/gh-ost.xucl.t.sock
  98. Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: mysql-bin.000001:24040; State: migrating; ETA: due
  99. 2018-05-22 11:56:09 INFO Setting RENAME timeout as 3 seconds
  100. 2018-05-22 11:56:09 INFO Session renaming tables is 36
  101. 2018-05-22 11:56:09 INFO Issuing and expecting this to block: rename /* gh-ost */ table `xucl`.`t` to `xucl`.`_t_del`, `xucl`.`_t_gho` to `xucl`.`t`
  102. Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: mysql-bin.000001:24488; State: migrating; ETA: due
  103. 2018-05-22 11:56:10 DEBUG Getting nothing in the write queue. Sleeping...
  104. Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: mysql-bin.000001:29007; State: migrating; ETA: due
  105. 2018-05-22 11:56:10 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
  106. 2018-05-22 11:56:10 INFO Checking session lock: gh-ost.41.lock
  107. 2018-05-22 11:56:10 INFO Connection holding lock on original table still exists
  108. 2018-05-22 11:56:10 INFO Will now proceed to drop magic table and unlock tables
  109. 2018-05-22 11:56:10 INFO Dropping magic cut-over table
  110. 2018-05-22 11:56:10 INFO Releasing lock from `xucl`.`t`, `xucl`.`_t_del`
  111. 2018-05-22 11:56:10 INFO Tables unlocked
  112. 2018-05-22 11:56:10 INFO Tables renamed
  113. 2018-05-22 11:56:10 INFO Lock & rename duration: 2.004491174s. During this time, queries on `t` were blocked
  114. 2018-05-22 11:56:10 INFO Looking for magic cut-over table
  115. 2018/05/22 11:56:10 binlogsyncer.go:107: [info] syncer is closing... 
  116. 2018/05/22 11:56:10 binlogstreamer.go:47: [error] close sync with err: sync is been closing...
  117. 2018/05/22 11:56:10 binlogsyncer.go:122: [info] syncer is closed 
  118. 2018-05-22 11:56:10 INFO Closed streamer connection. err=<nil>
  119. 2018-05-22 11:56:10 INFO Dropping table `xucl`.`_t_ghc`
  120. 2018-05-22 11:56:10 DEBUG done streaming events
  121. 2018-05-22 11:56:10 DEBUG Done streaming
  122. 2018-05-22 11:56:10 INFO Table dropped
  123. 2018-05-22 11:56:10 INFO Dropping table `xucl`.`_t_del`
  124. 2018-05-22 11:56:10 INFO Table dropped
  125. 2018-05-22 11:56:10 INFO Done migrating `xucl`.`t`
  126. 2018-05-22 11:56:10 INFO Removing socket file: /tmp/gh-ost.xucl.t.sock
  127. 2018-05-22 11:56:10 INFO Tearing down inspector
  128. 2018-05-22 11:56:10 INFO Tearing down applier
  129. 2018-05-22 11:56:10 DEBUG Tearing down...
  130. 2018-05-22 11:56:10 INFO Tearing down streamer
  131. 2018-05-22 11:56:10 INFO Tearing down throttler
  132. 2018-05-22 11:56:10 DEBUG Tearing down...
  133. # Done

查看下general log如下：

  1. 2018-05-22T09:56:08.032569Z       36 Connect    root@localhost on xucl using TCP/IP
  2. 2018-05-22T09:56:08.032680Z       36 Query    SELECT @@max_allowed_packet
  3. 2018-05-22T09:56:08.032732Z       36 Query    SET autocommit=true
  4. 2018-05-22T09:56:08.032768Z       36 Query    SET NAMES utf8mb4
  5. 2018-05-22T09:56:08.032835Z       36 Query    select @@global.version
  6. 2018-05-22T09:56:08.032947Z       36 Query    select @@global.port
  7. 2018-05-22T09:56:08.033060Z       36 Query    select @@global.hostname, @@global.port
  8. 2018-05-22T09:56:08.033136Z       36 Query    show /* gh-ost */ grants for current_user()
  9. 2018-05-22T09:56:08.033853Z       36 Query    select @@global.log_bin, @@global.binlog_format
  10. 2018-05-22T09:56:08.033991Z       36 Query    select @@global.binlog_row_image
  11. 2018-05-22T09:56:08.034103Z       36 Query    show /* gh-ost */ table status from `xucl` like 't'
  12. 2018-05-22T09:56:08.034859Z       36 Query    SELECT
  13.             SUM(REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA='xucl' AND TABLE_NAME='t') as num_child_side_fk,
  14.             SUM(REFERENCED_TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_SCHEMA='xucl' AND REFERENCED_TABLE_NAME='t') as num_parent_side_fk
  15.         FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  16.         WHERE
  17.                 REFERENCED_TABLE_NAME IS NOT NULL
  18.                 AND ((TABLE_SCHEMA='xucl' AND TABLE_NAME='t')
  19.                     OR (REFERENCED_TABLE_SCHEMA='xucl' AND REFERENCED_TABLE_NAME='t')
  20.                 )
  21. 2018-05-22T09:56:08.044897Z       36 Query    SELECT COUNT(*) AS num_triggers
  22.             FROM INFORMATION_SCHEMA.TRIGGERS
  23.             WHERE
  24.                 TRIGGER_SCHEMA='xucl'
  25.                 AND EVENT_OBJECT_TABLE='t'
  26. 2018-05-22T09:56:08.052367Z       36 Query    explain select /* gh-ost */ * from `xucl`.`t` where 1=1
  27. 2018-05-22T09:56:08.052842Z       36 Query    SELECT
  28.       COLUMNS.TABLE_SCHEMA,
  29.       COLUMNS.TABLE_NAME,
  30.       COLUMNS.COLUMN_NAME,
  31.       UNIQUES.INDEX_NAME,
  32.       UNIQUES.COLUMN_NAMES,
  33.       UNIQUES.COUNT_COLUMN_IN_INDEX,
  34.       COLUMNS.DATA_TYPE,
  35.       COLUMNS.CHARACTER_SET_NAME,
  36.             LOCATE('auto_increment', EXTRA) > 0 as is_auto_increment,
  37.       has_nullable
  38.     FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN (
  39.       SELECT
  40.         TABLE_SCHEMA,
  41.         TABLE_NAME,
  42.         INDEX_NAME,
  43.         COUNT(*) AS COUNT_COLUMN_IN_INDEX,
  44.         GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES,
  45.         SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME,
  46.         SUM(NULLABLE='YES') > 0 AS has_nullable
  47.       FROM INFORMATION_SCHEMA.STATISTICS
  48.       WHERE
  49.                 NON_UNIQUE=0
  50.                 AND TABLE_SCHEMA = 'xucl'
  51.           AND TABLE_NAME = 't'
  52.       GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
  53.     ) AS UNIQUES
  54.     ON (
  55.       COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA AND
  56.       COLUMNS.TABLE_NAME = UNIQUES.TABLE_NAME AND
  57.       COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME
  58.     )
  59.     WHERE
  60.       COLUMNS.TABLE_SCHEMA = 'xucl'
  61.       AND COLUMNS.TABLE_NAME = 't'
  62.     ORDER BY
  63.       COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME,
  64.       CASE UNIQUES.INDEX_NAME
  65.         WHEN 'PRIMARY' THEN 0
  66.         ELSE 1
  67.       END,
  68.       CASE has_nullable
  69.         WHEN 0 THEN 0
  70.         ELSE 1
  71.       END,
  72.       CASE IFNULL(CHARACTER_SET_NAME, '')
  73.           WHEN '' THEN 0
  74.           ELSE 1
  75.       END,
  76.       CASE DATA_TYPE
  77.         WHEN 'tinyint' THEN 0
  78.         WHEN 'smallint' THEN 1
  79.         WHEN 'int' THEN 2
  80.         WHEN 'bigint' THEN 3
  81.         ELSE 100
  82.       END,
  83.       COUNT_COLUMN_IN_INDEX
  84. 2018-05-22T09:56:08.053794Z       36 Query    show columns from `xucl`.`t`
  85. 2018-05-22T09:56:08.054658Z       37 Connect    root@localhost on information_schema using TCP/IP
  86. 2018-05-22T09:56:08.054753Z       37 Query    SELECT @@max_allowed_packet
  87. 2018-05-22T09:56:08.054836Z       37 Query    SET NAMES utf8mb4
  88. 2018-05-22T09:56:08.054862Z       37 Query    SET autocommit=true
  89. 2018-05-22T09:56:08.054953Z       37 Query    show slave status
  90. 2018-05-22T09:56:08.055113Z       37 Quit    
  91. 2018-05-22T09:56:08.055181Z       36 Query    select @@global.log_slave_updates
  92. 2018-05-22T09:56:08.055274Z       36 Query    select @@global.version
  93. 2018-05-22T09:56:08.055973Z       36 Query    select @@global.port
  94. 2018-05-22T09:56:08.056058Z       36 Query    show /* gh-ost readCurrentBinlogCoordinates */ master status
  95. 2018-05-22T09:56:08.063389Z       38 Connect    root@localhost on  using TCP/IP
  96. 2018-05-22T09:56:08.064119Z       38 Query    SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'
  97. 2018-05-22T09:56:08.066197Z       38 Query    SET @master_binlog_checksum='NONE'
  98. 2018-05-22T09:56:08.066478Z       36 Query    select @@global.version
  99. 2018-05-22T09:56:08.067845Z       38 Binlog Dump    Log: 'mysql-bin.000001'  Pos: 15862
  100. 2018-05-22T09:56:08.072505Z       36 Query    select @@global.port
  101. 2018-05-22T09:56:08.072916Z       39 Connect    root@localhost on xucl using TCP/IP
  102. 2018-05-22T09:56:08.072999Z       39 Query    SELECT @@max_allowed_packet
  103. 2018-05-22T09:56:08.073068Z       39 Query    SET NAMES utf8mb4
  104. 2018-05-22T09:56:08.073116Z       39 Query    SET autocommit=true
  105. 2018-05-22T09:56:08.073167Z       39 Query    select @@global.version
  106. 2018-05-22T09:56:08.073272Z       39 Query    select @@global.port
  107. 2018-05-22T09:56:08.073354Z       36 Query    select @@global.time_zone
  108. 2018-05-22T09:56:08.073430Z       36 Query    select @@global.hostname, @@global.port
  109. 2018-05-22T09:56:08.078324Z       36 Query    show columns from `xucl`.`t`
  110. 2018-05-22T09:56:08.079002Z       36 Query    show /* gh-ost */ table status from `xucl` like '_t_gho'
  111. 2018-05-22T09:56:08.086250Z       36 Query    drop /* gh-ost */ table if exists `xucl`.`_t_del`
  112. 2018-05-22T09:56:08.086565Z       36 Query    show /* gh-ost */ table status from `xucl` like '_t_del'
  113. 2018-05-22T09:56:08.091585Z       36 Query    drop /* gh-ost */ table if exists `xucl`.`_t_ghc`
  114. 2018-05-22T09:56:08.091843Z       36 Query    create /* gh-ost */ table `xucl`.`_t_ghc` (
  115.             id bigint auto_increment,
  116.             last_update timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  117.             hint varchar(64) charset ascii not null,
  118.             value varchar(4096) charset ascii not null,
  119.             primary key(id),
  120.             unique key hint_uidx(hint)
  121.         ) auto_increment=256
  122. 2018-05-22T09:56:08.105332Z       36 Query    create /* gh-ost */ table `xucl`.`_t_gho` like `xucl`.`t`
  123. 2018-05-22T09:56:08.118897Z       36 Query    alter /* gh-ost */ table `xucl`.`_t_gho` add column test varchar(10)
  124. 2018-05-22T09:56:08.136898Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  125.                 (id, hint, value)
  126.             values
  127.                 (NULLIF(?, 0), ?, ?)
  128.             on duplicate key update
  129.                 last_update=NOW(),
  130.                 value=VALUES(value)
  131. 2018-05-22T09:56:08.136973Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  132.                 (id, hint, value)
  133.             values
  134.                 (NULLIF(2, 0), 'state', 'GhostTableMigrated')
  135.             on duplicate key update
  136.                 last_update=NOW(),
  137.                 value=VALUES(value)
  138. 2018-05-22T09:56:08.137162Z       36 Close stmt    
  139. 2018-05-22T09:56:08.137316Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  140.                 (id, hint, value)
  141.             values
  142.                 (NULLIF(?, 0), ?, ?)
  143.             on duplicate key update
  144.                 last_update=NOW(),
  145.                 value=VALUES(value)
  146. 2018-05-22T09:56:08.137935Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  147.                 (id, hint, value)
  148.             values
  149.                 (NULLIF(0, 0), 'state at 1526982968137068881', 'GhostTableMigrated')
  150.             on duplicate key update
  151.                 last_update=NOW(),
  152.                 value=VALUES(value)
  153. 2018-05-22T09:56:08.138853Z       40 Connect    root@localhost on information_schema using TCP/IP
  154. 2018-05-22T09:56:08.138921Z       40 Query    SELECT @@max_allowed_packet
  155. 2018-05-22T09:56:08.142418Z       40 Query    SET NAMES utf8mb4
  156. 2018-05-22T09:56:08.147283Z       36 Close stmt    
  157. 2018-05-22T09:56:08.147431Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  158.                 (id, hint, value)
  159.             values
  160.                 (NULLIF(?, 0), ?, ?)
  161.             on duplicate key update
  162.                 last_update=NOW(),
  163.                 value=VALUES(value)
  164. 2018-05-22T09:56:08.147507Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  165.                 (id, hint, value)
  166.             values
  167.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.13866344+02:00')
  168.             on duplicate key update
  169.                 last_update=NOW(),
  170.                 value=VALUES(value)
  171. 2018-05-22T09:56:08.147809Z       36 Close stmt    
  172. 2018-05-22T09:56:08.147837Z       40 Query    SET autocommit=true
  173. 2018-05-22T09:56:08.147901Z       40 Query    show slave status
  174. 2018-05-22T09:56:08.154605Z       36 Query    SELECT
  175.       COLUMNS.TABLE_SCHEMA,
  176.       COLUMNS.TABLE_NAME,
  177.       COLUMNS.COLUMN_NAME,
  178.       UNIQUES.INDEX_NAME,
  179.       UNIQUES.COLUMN_NAMES,
  180.       UNIQUES.COUNT_COLUMN_IN_INDEX,
  181.       COLUMNS.DATA_TYPE,
  182.       COLUMNS.CHARACTER_SET_NAME,
  183.             LOCATE('auto_increment', EXTRA) > 0 as is_auto_increment,
  184.       has_nullable
  185.     FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN (
  186.       SELECT
  187.         TABLE_SCHEMA,
  188.         TABLE_NAME,
  189.         INDEX_NAME,
  190.         COUNT(*) AS COUNT_COLUMN_IN_INDEX,
  191.         GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES,
  192.         SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME,
  193.         SUM(NULLABLE='YES') > 0 AS has_nullable
  194.       FROM INFORMATION_SCHEMA.STATISTICS
  195.       WHERE
  196.                 NON_UNIQUE=0
  197.                 AND TABLE_SCHEMA = 'xucl'
  198.           AND TABLE_NAME = '_t_gho'
  199.       GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
  200.     ) AS UNIQUES
  201.     ON (
  202.       COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA AND
  203.       COLUMNS.TABLE_NAME = UNIQUES.TABLE_NAME AND
  204.       COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME
  205.     )
  206.     WHERE
  207.       COLUMNS.TABLE_SCHEMA = 'xucl'
  208.       AND COLUMNS.TABLE_NAME = '_t_gho'
  209.     ORDER BY
  210.       COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME,
  211.       CASE UNIQUES.INDEX_NAME
  212.         WHEN 'PRIMARY' THEN 0
  213.         ELSE 1
  214.       END,
  215.       CASE has_nullable
  216.         WHEN 0 THEN 0
  217.         ELSE 1
  218.       END,
  219.       CASE IFNULL(CHARACTER_SET_NAME, '')
  220.           WHEN '' THEN 0
  221.           ELSE 1
  222.       END,
  223.       CASE DATA_TYPE
  224.         WHEN 'tinyint' THEN 0
  225.         WHEN 'smallint' THEN 1
  226.         WHEN 'int' THEN 2
  227.         WHEN 'bigint' THEN 3
  228.         ELSE 100
  229.       END,
  230.       COUNT_COLUMN_IN_INDEX
  231. 2018-05-22T09:56:08.155931Z       36 Query    show columns from `xucl`.`_t_gho`
  232. 2018-05-22T09:56:08.161827Z       36 Query    select
  233.                 *
  234.             from
  235.                 information_schema.columns
  236.             where
  237.                 table_schema='xucl'
  238.                 and table_name='t'
  239. 2018-05-22T09:56:08.164528Z       36 Query    select
  240.                 *
  241.             from
  242.                 information_schema.columns
  243.             where
  244.                 table_schema='xucl'
  245.                 and table_name='t'
  246. 2018-05-22T09:56:08.165032Z       36 Query    select
  247.                 *
  248.             from
  249.                 information_schema.columns
  250.             where
  251.                 table_schema='xucl'
  252.                 and table_name='t'
  253. 2018-05-22T09:56:08.168654Z       36 Query    select
  254.                 *
  255.             from
  256.                 information_schema.columns
  257.             where
  258.                 table_schema='xucl'
  259.                 and table_name='_t_gho'
  260. 2018-05-22T09:56:08.174110Z       36 Query    select /* gh-ost `xucl`.`t` */ `user`, `flag`, `id`
  261.                 from
  262.                     `xucl`.`t`
  263.                 order by
  264.                     `user` asc, `flag` asc, `id` asc
  265.                 limit 1
  266. 2018-05-22T09:56:08.174861Z       36 Query    select /* gh-ost `xucl`.`t` */ `user`, `flag`, `id`
  267.                 from
  268.                     `xucl`.`t`
  269.                 order by
  270.                     `user` desc, `flag` desc, `id` desc
  271.                 limit 1
  272. 2018-05-22T09:56:08.175660Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  273. 2018-05-22T09:56:08.177282Z       41 Connect    root@localhost on xucl using TCP/IP
  274. 2018-05-22T09:56:08.177373Z       41 Query    SELECT @@max_allowed_packet
  275. 2018-05-22T09:56:08.177445Z       41 Query    SET autocommit=true
  276. 2018-05-22T09:56:08.177492Z       41 Query    SET NAMES utf8mb4
  277. 2018-05-22T09:56:08.177598Z       41 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  278.                 (id, hint, value)
  279.             values
  280.                 (NULLIF(?, 0), ?, ?)
  281.             on duplicate key update
  282.                 last_update=NOW(),
  283.                 value=VALUES(value)
  284. 2018-05-22T09:56:08.177694Z       41 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  285.                 (id, hint, value)
  286.             values
  287.                 (NULLIF(0, 0), 'copy iteration 0 at 1526982968', 'Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000001:18076; State: migrating; ETA: due')
  288.             on duplicate key update
  289.                 last_update=NOW(),
  290.                 value=VALUES(value)
  291. 2018-05-22T09:56:08.182899Z       41 Close stmt    
  292. 2018-05-22T09:56:08.182955Z       41 Query    START TRANSACTION
  293. 2018-05-22T09:56:08.183200Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  294.                 (id, hint, value)
  295.             values
  296.                 (NULLIF(?, 0), ?, ?)
  297.             on duplicate key update
  298.                 last_update=NOW(),
  299.                 value=VALUES(value)
  300. 2018-05-22T09:56:08.188933Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  301.                 (id, hint, value)
  302.             values
  303.                 (NULLIF(0, 0), 'copy iteration 0 at 1526982968', 'Copy: 0/1 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000001:18076; State: migrating; ETA: N/A')
  304.             on duplicate key update
  305.                 last_update=NOW(),
  306.                 value=VALUES(value)
  307. 2018-05-22T09:56:08.189266Z       41 Query    select connection_id()
  308. 2018-05-22T09:56:08.189618Z       41 Query    select get_lock('gh-ost.41.lock', 0)
  309. 2018-05-22T09:56:08.189677Z       36 Close stmt    
  310. 2018-05-22T09:56:08.195756Z       41 Query    set session lock_wait_timeout:=6
  311. 2018-05-22T09:56:08.196910Z       36 Query    show /* gh-ost */ table status from `xucl` like '_t_del'
  312. 2018-05-22T09:56:08.197392Z       36 Query    create /* gh-ost */ table `xucl`.`_t_del` (
  313.             id int auto_increment primary key
  314.         ) engine=InnoDB comment='ghost-cut-over-sentry'
  315. 2018-05-22T09:56:08.208183Z       41 Query    lock /* gh-ost */ tables `xucl`.`t` write, `xucl`.`_t_del` write
  316. 2018-05-22T09:56:08.212669Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  317.                 (id, hint, value)
  318.             values
  319.                 (NULLIF(?, 0), ?, ?)
  320.             on duplicate key update
  321.                 last_update=NOW(),
  322.                 value=VALUES(value)
  323. 2018-05-22T09:56:08.213499Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  324.                 (id, hint, value)
  325.             values
  326.                 (NULLIF(2, 0), 'state', 'AllEventsUpToLockProcessed:1526982968208522643')
  327.             on duplicate key update
  328.                 last_update=NOW(),
  329.                 value=VALUES(value)
  330. 2018-05-22T09:56:08.215217Z       36 Close stmt    
  331. 2018-05-22T09:56:08.215437Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  332.                 (id, hint, value)
  333.             values
  334.                 (NULLIF(?, 0), ?, ?)
  335.             on duplicate key update
  336.                 last_update=NOW(),
  337.                 value=VALUES(value)
  338. 2018-05-22T09:56:08.215664Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  339.                 (id, hint, value)
  340.             values
  341.                 (NULLIF(0, 0), 'state at 1526982968215071085', 'AllEventsUpToLockProcessed:1526982968208522643')
  342.             on duplicate key update
  343.                 last_update=NOW(),
  344.                 value=VALUES(value)
  345. 2018-05-22T09:56:08.216611Z       36 Close stmt    
  346. 2018-05-22T09:56:08.248739Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  347.                 (id, hint, value)
  348.             values
  349.                 (NULLIF(?, 0), ?, ?)
  350.             on duplicate key update
  351.                 last_update=NOW(),
  352.                 value=VALUES(value)
  353. 2018-05-22T09:56:08.248821Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  354.                 (id, hint, value)
  355.             values
  356.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.248488653+02:00')
  357.             on duplicate key update
  358.                 last_update=NOW(),
  359.                 value=VALUES(value)
  360. 2018-05-22T09:56:08.249143Z       36 Close stmt    
  361. 2018-05-22T09:56:08.277018Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  362. 2018-05-22T09:56:08.348104Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  363.                 (id, hint, value)
  364.             values
  365.                 (NULLIF(?, 0), ?, ?)
  366.             on duplicate key update
  367.                 last_update=NOW(),
  368.                 value=VALUES(value)
  369. 2018-05-22T09:56:08.348176Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  370.                 (id, hint, value)
  371.             values
  372.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.347919396+02:00')
  373.             on duplicate key update
  374.                 last_update=NOW(),
  375.                 value=VALUES(value)
  376. 2018-05-22T09:56:08.348452Z       36 Close stmt    
  377. 2018-05-22T09:56:08.376954Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  378. 2018-05-22T09:56:08.448067Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  379.                 (id, hint, value)
  380.             values
  381.                 (NULLIF(?, 0), ?, ?)
  382.             on duplicate key update
  383.                 last_update=NOW(),
  384.                 value=VALUES(value)
  385. 2018-05-22T09:56:08.448141Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  386.                 (id, hint, value)
  387.             values
  388.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.447880905+02:00')
  389.             on duplicate key update
  390.                 last_update=NOW(),
  391.                 value=VALUES(value)
  392. 2018-05-22T09:56:08.448416Z       36 Close stmt    
  393. 2018-05-22T09:56:08.478341Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  394. 2018-05-22T09:56:08.549633Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  395.                 (id, hint, value)
  396.             values
  397.                 (NULLIF(?, 0), ?, ?)
  398.             on duplicate key update
  399.                 last_update=NOW(),
  400.                 value=VALUES(value)
  401. 2018-05-22T09:56:08.549706Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  402.                 (id, hint, value)
  403.             values
  404.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.549450894+02:00')
  405.             on duplicate key update
  406.                 last_update=NOW(),
  407.                 value=VALUES(value)
  408. 2018-05-22T09:56:08.549982Z       36 Close stmt    
  409. 2018-05-22T09:56:08.577941Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  410. 2018-05-22T09:56:08.648776Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  411.                 (id, hint, value)
  412.             values
  413.                 (NULLIF(?, 0), ?, ?)
  414.             on duplicate key update
  415.                 last_update=NOW(),
  416.                 value=VALUES(value)
  417. 2018-05-22T09:56:08.648848Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  418.                 (id, hint, value)
  419.             values
  420.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.648594596+02:00')
  421.             on duplicate key update
  422.                 last_update=NOW(),
  423.                 value=VALUES(value)
  424. 2018-05-22T09:56:08.649123Z       36 Close stmt    
  425. 2018-05-22T09:56:08.677923Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  426. 2018-05-22T09:56:08.749249Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  427.                 (id, hint, value)
  428.             values
  429.                 (NULLIF(?, 0), ?, ?)
  430.             on duplicate key update
  431.                 last_update=NOW(),
  432.                 value=VALUES(value)
  433. 2018-05-22T09:56:08.749321Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  434.                 (id, hint, value)
  435.             values
  436.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.74906528+02:00')
  437.             on duplicate key update
  438.                 last_update=NOW(),
  439.                 value=VALUES(value)
  440. 2018-05-22T09:56:08.749610Z       36 Close stmt    
  441. 2018-05-22T09:56:08.777033Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  442. 2018-05-22T09:56:08.849435Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  443.                 (id, hint, value)
  444.             values
  445.                 (NULLIF(?, 0), ?, ?)
  446.             on duplicate key update
  447.                 last_update=NOW(),
  448.                 value=VALUES(value)
  449. 2018-05-22T09:56:08.849550Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  450.                 (id, hint, value)
  451.             values
  452.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.849202925+02:00')
  453.             on duplicate key update
  454.                 last_update=NOW(),
  455.                 value=VALUES(value)
  456. 2018-05-22T09:56:08.849856Z       36 Close stmt    
  457. 2018-05-22T09:56:08.877064Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  458. 2018-05-22T09:56:08.948094Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  459.                 (id, hint, value)
  460.             values
  461.                 (NULLIF(?, 0), ?, ?)
  462.             on duplicate key update
  463.                 last_update=NOW(),
  464.                 value=VALUES(value)
  465. 2018-05-22T09:56:08.948174Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  466.                 (id, hint, value)
  467.             values
  468.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:08.947907753+02:00')
  469.             on duplicate key update
  470.                 last_update=NOW(),
  471.                 value=VALUES(value)
  472. 2018-05-22T09:56:08.948446Z       36 Close stmt    
  473. 2018-05-22T09:56:08.977623Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  474. 2018-05-22T09:56:09.049287Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  475.                 (id, hint, value)
  476.             values
  477.                 (NULLIF(?, 0), ?, ?)
  478.             on duplicate key update
  479.                 last_update=NOW(),
  480.                 value=VALUES(value)
  481. 2018-05-22T09:56:09.049373Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  482.                 (id, hint, value)
  483.             values
  484.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.049052703+02:00')
  485.             on duplicate key update
  486.                 last_update=NOW(),
  487.                 value=VALUES(value)
  488. 2018-05-22T09:56:09.049652Z       36 Close stmt    
  489. 2018-05-22T09:56:09.078456Z       36 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  490. 2018-05-22T09:56:09.149183Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  491.                 (id, hint, value)
  492.             values
  493.                 (NULLIF(?, 0), ?, ?)
  494.             on duplicate key update
  495.                 last_update=NOW(),
  496.                 value=VALUES(value)
  497. 2018-05-22T09:56:09.149280Z       36 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  498.                 (id, hint, value)
  499.             values
  500.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.148887407+02:00')
  501.             on duplicate key update
  502.                 last_update=NOW(),
  503.                 value=VALUES(value)
  504. 2018-05-22T09:56:09.149589Z       36 Close stmt    
  505. 2018-05-22T09:56:09.177707Z       42 Connect    root@localhost on xucl using TCP/IP
  506. 2018-05-22T09:56:09.177800Z       42 Query    SELECT @@max_allowed_packet
  507. 2018-05-22T09:56:09.178241Z       42 Query    SET autocommit=true
  508. 2018-05-22T09:56:09.178702Z       42 Query    SET NAMES utf8mb4
  509. 2018-05-22T09:56:09.178841Z       42 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  510. 2018-05-22T09:56:09.179146Z       36 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  511.                 (id, hint, value)
  512.             values
  513.                 (NULLIF(?, 0), ?, ?)
  514.             on duplicate key update
  515.                 last_update=NOW(),
  516.                 value=VALUES(value)
  517. 2018-05-22T09:56:09.179293Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  518.                 (id, hint, value)
  519.             values
  520.                 (NULLIF(?, 0), ?, ?)
  521.             on duplicate key update
  522.                 last_update=NOW(),
  523.                 value=VALUES(value)
  524. 2018-05-22T09:56:09.179377Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  525.                 (id, hint, value)
  526.             values
  527.                 (NULLIF(0, 0), 'copy iteration 0 at 1526982969', 'Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: mysql-bin.000001:24040; State: migrating; ETA: due')
  528.             on duplicate key update
  529.                 last_update=NOW(),
  530.                 value=VALUES(value)
  531. 2018-05-22T09:56:09.183848Z       42 Close stmt    
  532. 2018-05-22T09:56:09.183884Z       36 Close stmt    
  533. 2018-05-22T09:56:09.183924Z       36 Query    START TRANSACTION
  534. 2018-05-22T09:56:09.184149Z       36 Query    select connection_id()
  535. 2018-05-22T09:56:09.189229Z       42 Query    select id
  536.             from information_schema.processlist
  537.             where
  538.                 id != connection_id()
  539.                 and 36 in (0, id)
  540.                 and state like concat('%', 'metadata lock', '%')
  541.                 and info  like concat('%', 'rename', '%')
  542. 2018-05-22T09:56:09.189557Z       36 Query    set session lock_wait_timeout:=3
  543. 2018-05-22T09:56:09.191289Z       43 Connect    root@localhost on xucl using TCP/IP
  544. 2018-05-22T09:56:09.191372Z       36 Query    rename /* gh-ost */ table `xucl`.`t` to `xucl`.`_t_del`, `xucl`.`_t_gho` to `xucl`.`t`
  545. 2018-05-22T09:56:09.199314Z       43 Query    SELECT @@max_allowed_packet
  546. 2018-05-22T09:56:09.200038Z       43 Query    SET autocommit=true
  547. 2018-05-22T09:56:09.200105Z       43 Query    SET NAMES utf8mb4
  548. 2018-05-22T09:56:09.200261Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  549.                 (id, hint, value)
  550.             values
  551.                 (NULLIF(?, 0), ?, ?)
  552.             on duplicate key update
  553.                 last_update=NOW(),
  554.                 value=VALUES(value)
  555. 2018-05-22T09:56:09.200404Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  556.                 (id, hint, value)
  557.             values
  558.                 (NULLIF(?, 0), ?, ?)
  559.             on duplicate key update
  560.                 last_update=NOW(),
  561.                 value=VALUES(value)
  562. 2018-05-22T09:56:09.200448Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  563.                 (id, hint, value)
  564.             values
  565.                 (NULLIF(0, 0), 'copy iteration 0 at 1526982969', 'Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: mysql-bin.000001:24488; State: migrating; ETA: due')
  566.             on duplicate key update
  567.                 last_update=NOW(),
  568.                 value=VALUES(value)
  569. 2018-05-22T09:56:09.201584Z       42 Close stmt    
  570. 2018-05-22T09:56:09.201644Z       43 Close stmt    
  571. 2018-05-22T09:56:09.248258Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  572.                 (id, hint, value)
  573.             values
  574.                 (NULLIF(?, 0), ?, ?)
  575.             on duplicate key update
  576.                 last_update=NOW(),
  577.                 value=VALUES(value)
  578. 2018-05-22T09:56:09.248366Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  579.                 (id, hint, value)
  580.             values
  581.                 (NULLIF(?, 0), ?, ?)
  582.             on duplicate key update
  583.                 last_update=NOW(),
  584.                 value=VALUES(value)
  585. 2018-05-22T09:56:09.248411Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  586.                 (id, hint, value)
  587.             values
  588.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.248052978+02:00')
  589.             on duplicate key update
  590.                 last_update=NOW(),
  591.                 value=VALUES(value)
  592. 2018-05-22T09:56:09.248704Z       43 Close stmt    
  593. 2018-05-22T09:56:09.248716Z       42 Close stmt    
  594. 2018-05-22T09:56:09.277169Z       43 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  595. 2018-05-22T09:56:09.348123Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  596.                 (id, hint, value)
  597.             values
  598.                 (NULLIF(?, 0), ?, ?)
  599.             on duplicate key update
  600.                 last_update=NOW(),
  601.                 value=VALUES(value)
  602. 2018-05-22T09:56:09.348254Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  603.                 (id, hint, value)
  604.             values
  605.                 (NULLIF(?, 0), ?, ?)
  606.             on duplicate key update
  607.                 last_update=NOW(),
  608.                 value=VALUES(value)
  609. 2018-05-22T09:56:09.348304Z       43 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  610.                 (id, hint, value)
  611.             values
  612.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.347931458+02:00')
  613.             on duplicate key update
  614.                 last_update=NOW(),
  615.                 value=VALUES(value)
  616. 2018-05-22T09:56:09.348585Z       42 Close stmt    
  617. 2018-05-22T09:56:09.348597Z       43 Close stmt    
  618. 2018-05-22T09:56:09.376970Z       42 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  619. 2018-05-22T09:56:09.448083Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  620.                 (id, hint, value)
  621.             values
  622.                 (NULLIF(?, 0), ?, ?)
  623.             on duplicate key update
  624.                 last_update=NOW(),
  625.                 value=VALUES(value)
  626. 2018-05-22T09:56:09.448188Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  627.                 (id, hint, value)
  628.             values
  629.                 (NULLIF(?, 0), ?, ?)
  630.             on duplicate key update
  631.                 last_update=NOW(),
  632.                 value=VALUES(value)
  633. 2018-05-22T09:56:09.448233Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  634.                 (id, hint, value)
  635.             values
  636.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.447894653+02:00')
  637.             on duplicate key update
  638.                 last_update=NOW(),
  639.                 value=VALUES(value)
  640. 2018-05-22T09:56:09.448513Z       42 Close stmt    
  641. 2018-05-22T09:56:09.448524Z       43 Close stmt    
  642. 2018-05-22T09:56:09.477078Z       43 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  643. 2018-05-22T09:56:09.548943Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  644.                 (id, hint, value)
  645.             values
  646.                 (NULLIF(?, 0), ?, ?)
  647.             on duplicate key update
  648.                 last_update=NOW(),
  649.                 value=VALUES(value)
  650. 2018-05-22T09:56:09.549054Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  651.                 (id, hint, value)
  652.             values
  653.                 (NULLIF(?, 0), ?, ?)
  654.             on duplicate key update
  655.                 last_update=NOW(),
  656.                 value=VALUES(value)
  657. 2018-05-22T09:56:09.549102Z       43 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  658.                 (id, hint, value)
  659.             values
  660.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.548760771+02:00')
  661.             on duplicate key update
  662.                 last_update=NOW(),
  663.                 value=VALUES(value)
  664. 2018-05-22T09:56:09.549382Z       42 Close stmt    
  665. 2018-05-22T09:56:09.549393Z       43 Close stmt    
  666. 2018-05-22T09:56:09.578227Z       42 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  667. 2018-05-22T09:56:09.648137Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  668.                 (id, hint, value)
  669.             values
  670.                 (NULLIF(?, 0), ?, ?)
  671.             on duplicate key update
  672.                 last_update=NOW(),
  673.                 value=VALUES(value)
  674. 2018-05-22T09:56:09.648241Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  675.                 (id, hint, value)
  676.             values
  677.                 (NULLIF(?, 0), ?, ?)
  678.             on duplicate key update
  679.                 last_update=NOW(),
  680.                 value=VALUES(value)
  681. 2018-05-22T09:56:09.648286Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  682.                 (id, hint, value)
  683.             values
  684.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.647954397+02:00')
  685.             on duplicate key update
  686.                 last_update=NOW(),
  687.                 value=VALUES(value)
  688. 2018-05-22T09:56:09.648568Z       43 Close stmt    
  689. 2018-05-22T09:56:09.648579Z       42 Close stmt    
  690. 2018-05-22T09:56:09.677111Z       43 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  691. 2018-05-22T09:56:09.748068Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  692.                 (id, hint, value)
  693.             values
  694.                 (NULLIF(?, 0), ?, ?)
  695.             on duplicate key update
  696.                 last_update=NOW(),
  697.                 value=VALUES(value)
  698. 2018-05-22T09:56:09.748171Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  699.                 (id, hint, value)
  700.             values
  701.                 (NULLIF(?, 0), ?, ?)
  702.             on duplicate key update
  703.                 last_update=NOW(),
  704.                 value=VALUES(value)
  705. 2018-05-22T09:56:09.748216Z       43 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  706.                 (id, hint, value)
  707.             values
  708.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.747886726+02:00')
  709.             on duplicate key update
  710.                 last_update=NOW(),
  711.                 value=VALUES(value)
  712. 2018-05-22T09:56:09.748497Z       42 Close stmt    
  713. 2018-05-22T09:56:09.748508Z       43 Close stmt    
  714. 2018-05-22T09:56:09.777156Z       42 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  715. 2018-05-22T09:56:09.848840Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  716.                 (id, hint, value)
  717.             values
  718.                 (NULLIF(?, 0), ?, ?)
  719.             on duplicate key update
  720.                 last_update=NOW(),
  721.                 value=VALUES(value)
  722. 2018-05-22T09:56:09.850017Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  723.                 (id, hint, value)
  724.             values
  725.                 (NULLIF(?, 0), ?, ?)
  726.             on duplicate key update
  727.                 last_update=NOW(),
  728.                 value=VALUES(value)
  729. 2018-05-22T09:56:09.850104Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  730.                 (id, hint, value)
  731.             values
  732.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.848555183+02:00')
  733.             on duplicate key update
  734.                 last_update=NOW(),
  735.                 value=VALUES(value)
  736. 2018-05-22T09:56:09.850528Z       43 Close stmt    
  737. 2018-05-22T09:56:09.850542Z       42 Close stmt    
  738. 2018-05-22T09:56:09.877177Z       43 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  739. 2018-05-22T09:56:09.949419Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  740.                 (id, hint, value)
  741.             values
  742.                 (NULLIF(?, 0), ?, ?)
  743.             on duplicate key update
  744.                 last_update=NOW(),
  745.                 value=VALUES(value)
  746. 2018-05-22T09:56:09.949524Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  747.                 (id, hint, value)
  748.             values
  749.                 (NULLIF(?, 0), ?, ?)
  750.             on duplicate key update
  751.                 last_update=NOW(),
  752.                 value=VALUES(value)
  753. 2018-05-22T09:56:09.949569Z       43 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  754.                 (id, hint, value)
  755.             values
  756.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:09.949216417+02:00')
  757.             on duplicate key update
  758.                 last_update=NOW(),
  759.                 value=VALUES(value)
  760. 2018-05-22T09:56:09.949850Z       42 Close stmt    
  761. 2018-05-22T09:56:09.949862Z       43 Close stmt    
  762. 2018-05-22T09:56:09.977901Z       42 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  763. 2018-05-22T09:56:10.049520Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  764.                 (id, hint, value)
  765.             values
  766.                 (NULLIF(?, 0), ?, ?)
  767.             on duplicate key update
  768.                 last_update=NOW(),
  769.                 value=VALUES(value)
  770. 2018-05-22T09:56:10.049637Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  771.                 (id, hint, value)
  772.             values
  773.                 (NULLIF(?, 0), ?, ?)
  774.             on duplicate key update
  775.                 last_update=NOW(),
  776.                 value=VALUES(value)
  777. 2018-05-22T09:56:10.049684Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  778.                 (id, hint, value)
  779.             values
  780.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:10.049317922+02:00')
  781.             on duplicate key update
  782.                 last_update=NOW(),
  783.                 value=VALUES(value)
  784. 2018-05-22T09:56:10.049970Z       43 Close stmt    
  785. 2018-05-22T09:56:10.049981Z       42 Close stmt    
  786. 2018-05-22T09:56:10.077077Z       43 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  787. 2018-05-22T09:56:10.148090Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  788.                 (id, hint, value)
  789.             values
  790.                 (NULLIF(?, 0), ?, ?)
  791.             on duplicate key update
  792.                 last_update=NOW(),
  793.                 value=VALUES(value)
  794. 2018-05-22T09:56:10.148195Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  795.                 (id, hint, value)
  796.             values
  797.                 (NULLIF(?, 0), ?, ?)
  798.             on duplicate key update
  799.                 last_update=NOW(),
  800.                 value=VALUES(value)
  801. 2018-05-22T09:56:10.148240Z       43 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  802.                 (id, hint, value)
  803.             values
  804.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:10.147897501+02:00')
  805.             on duplicate key update
  806.                 last_update=NOW(),
  807.                 value=VALUES(value)
  808. 2018-05-22T09:56:10.148527Z       42 Close stmt    
  809. 2018-05-22T09:56:10.148538Z       43 Close stmt    
  810. 2018-05-22T09:56:10.177953Z       42 Query    select hint, value from `xucl`.`_t_ghc` where hint = 'heartbeat' and id <= 255
  811. 2018-05-22T09:56:10.189863Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  812.                 (id, hint, value)
  813.             values
  814.                 (NULLIF(?, 0), ?, ?)
  815.             on duplicate key update
  816.                 last_update=NOW(),
  817.                 value=VALUES(value)
  818. 2018-05-22T09:56:10.189967Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  819.                 (id, hint, value)
  820.             values
  821.                 (NULLIF(?, 0), ?, ?)
  822.             on duplicate key update
  823.                 last_update=NOW(),
  824.                 value=VALUES(value)
  825. 2018-05-22T09:56:10.190014Z       42 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  826.                 (id, hint, value)
  827.             values
  828.                 (NULLIF(0, 0), 'copy iteration 0 at 1526982970', 'Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: mysql-bin.000001:29007; State: migrating; ETA: due')
  829.             on duplicate key update
  830.                 last_update=NOW(),
  831.                 value=VALUES(value)
  832. 2018-05-22T09:56:10.190280Z       43 Close stmt    
  833. 2018-05-22T09:56:10.190292Z       42 Close stmt    
  834. 2018-05-22T09:56:10.192997Z       43 Query    select id
  835.             from information_schema.processlist
  836.             where
  837.                 id != connection_id()
  838.                 and 36 in (0, id)
  839.                 and state like concat('%', 'metadata lock', '%')
  840.                 and info  like concat('%', 'rename', '%')
  841. 2018-05-22T09:56:10.194342Z       42 Query    select is_used_lock('gh-ost.41.lock')
  842. 2018-05-22T09:56:10.194469Z       41 Query    drop /* gh-ost */ table if exists `xucl`.`_t_del`
  843. 2018-05-22T09:56:10.204711Z       41 Query    unlock tables
  844. 2018-05-22T09:56:10.205209Z       41 Query    ROLLBACK
  845. 2018-05-22T09:56:10.205210Z       41 Quit    
  846. 2018-05-22T09:56:10.212698Z       43 Query    show /* gh-ost */ table status from `xucl` like '_t_del'
  847. 2018-05-22T09:56:10.214087Z       36 Query    ROLLBACK
  848. 2018-05-22T09:56:10.214211Z       36 Quit    
  849. 2018-05-22T09:56:10.249618Z       42 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  850.                 (id, hint, value)
  851.             values
  852.                 (NULLIF(?, 0), ?, ?)
  853.             on duplicate key update
  854.                 last_update=NOW(),
  855.                 value=VALUES(value)
  856. 2018-05-22T09:56:10.249924Z       43 Prepare    insert /* gh-ost */ into `xucl`.`_t_ghc`
  857.                 (id, hint, value)
  858.             values
  859.                 (NULLIF(?, 0), ?, ?)
  860.             on duplicate key update
  861.                 last_update=NOW(),
  862.                 value=VALUES(value)
  863. 2018-05-22T09:56:10.249992Z       43 Execute    insert /* gh-ost */ into `xucl`.`_t_ghc`
  864.                 (id, hint, value)
  865.             values
  866.                 (NULLIF(1, 0), 'heartbeat', '2018-05-22T11:56:10.248964488+02:00')
  867.             on duplicate key update
  868.                 last_update=NOW(),
  869.                 value=VALUES(value)
  870. 2018-05-22T09:56:10.250372Z       42 Query    drop /* gh-ost */ table if exists `xucl`.`_t_ghc`
  871. 2018-05-22T09:56:10.257636Z       42 Close stmt    
  872. 2018-05-22T09:56:10.257674Z       43 Query    drop /* gh-ost */ table if exists `xucl`.`_t_del`
  873. 2018-05-22T09:56:10.266537Z       40 Quit    
  874. 2018-05-22T09:56:10.266589Z       39 Quit    
  875. 2018-05-22T09:56:10.267238Z       42 Quit    
  876. 2018-05-22T09:56:10.267326Z       43 Close stmt    
  877. 2018-05-22T09:56:10.267333Z       43 Quit

以上log相对来说比较多，大致过程可以整理为以下几步：

  • 直连主库

  • 主库上创建ghost表

  • 新表(ghost表)上直接alter修改表结构

  • 迁移原表数据到新表

  • 拉取解析binlog事件,应用到新表

  • cut-over阶段,用新表替换掉原表