引言
gh-ost 是github开源的在线DDL工具，从发布之后就受到了同行的广泛关注，由于其采用的伪装从库的方式同步增量DML，比pt-online-schema-change采用的触发器更友好，且生产开销较小，是一款很优秀的在线DDL工具。
原理
gh-ost 介绍
gh-ost 作为一个伪装的备库，可以从主库/备库上拉取 binlog，过滤之后重新应用到主库上去，相当于主库上的增量操作通过 binlog 又应用回主库本身，不过是应用在幽灵表上。
大致工作过程：1
2
31.gh-ost 首先连接到主库上，根据 alter 语句创建幽灵表
2.然后作为一个备库连接到其中一个真正的备库或者主库上(根据具体的参数来定)，一边在主库上拷贝已有的数据到幽灵表，一边从备库上拉取增量数据的 binlog，然后不断的把 binlog 应用回主库
3.等待全部数据同步完成，进行 cut-over 幽灵表和原表切换
general_log
通过打开mysql的general_log，查看gh-ost的具体执行过程：
mysql: 5.7.22
gh-ost：1.0.48
此案例使用了–allow-on-master 参数，DDL在主库上操作
执行语句：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=512 \
--chunk-size=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--conf="/etc/my.cnf" \
--host="localhost" \
--port=3306 \
--user="root" \
--password="xxx" \
--database="test" \
--table="employee1" \
--verbose \
--alter="engine=innodb" \
--switch-to-rbr \
--allow-on-master \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--execute
执行过程：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
1102018-10-11 15:38:27 INFO starting gh-ost 1.0.48
2018-10-11 15:38:27 INFO Migrating `test`.`employee1`
2018-10-11 15:38:27 INFO connection validated on localhost:3306
2018-10-11 15:38:27 INFO User has ALL privileges
2018-10-11 15:38:27 INFO binary logs validated on localhost:3306
2018-10-11 15:38:27 INFO Restarting replication on localhost:3306 to make sure binlog settings apply to replication thread
2018-10-11 15:38:27 INFO Inspector initiated on VM_24_101_centos:3306, version 5.7.22-log
2018-10-11 15:38:27 INFO Table found. Engine=InnoDB
2018-10-11 15:38:28 INFO Estimated number of rows via EXPLAIN: 10028
2018-10-11 15:38:28 INFO Recursively searching for replication master
2018-10-11 15:38:28 INFO Master found to be VM_24_101_centos:3306
2018-10-11 15:38:28 INFO log_slave_updates validated on localhost:3306
2018-10-11 15:38:28 INFO connection validated on localhost:3306
2018-10-11 15:38:28 INFO Connecting binlog streamer at bin.000070:320607755
[2020/05/29 15:38:28] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql localhost 3306 root false false <nil> false UTC true 0 0s 0s 0 false}
[2020/05/29 15:38:28] [info] binlogsyncer.go:354 begin to sync binlog from position (bin.000070, 320607755)
[2020/05/29 15:38:28] [info] binlogsyncer.go:203 register slave for master server localhost:3306
2018-10-11 15:38:28 INFO rotate to next log from bin.000070:0 to bin.000070
[2020/05/29 15:38:28] [info] binlogsyncer.go:723 rotate to (bin.000070, 320607755)
2018-10-11 15:38:28 INFO connection validated on localhost:3306
2018-10-11 15:38:28 INFO connection validated on localhost:3306
2018-10-11 15:38:28 INFO will use time_zone='SYSTEM' on applier
2018-10-11 15:38:28 INFO Examining table structure on applier
2018-10-11 15:38:28 INFO Applier initiated on VM_24_101_centos:3306, version 5.7.22-log
2018-10-11 15:38:28 INFO Dropping table `test`.`_employee1_gho`
2018-10-11 15:38:28 INFO Table dropped
2018-10-11 15:38:28 INFO Dropping table `test`.`_employee1_del`
2018-10-11 15:38:28 INFO Table dropped
2018-10-11 15:38:28 INFO Dropping table `test`.`_employee1_ghc`
2018-10-11 15:38:28 INFO Table dropped
2018-10-11 15:38:28 INFO Creating changelog table `test`.`_employee1_ghc`
2018-10-11 15:38:28 INFO Changelog table created
2018-10-11 15:38:28 INFO Creating ghost table `test`.`_employee1_gho`
2018-10-11 15:38:28 INFO Ghost table created
2018-10-11 15:38:28 INFO Altering ghost table `test`.`_employee1_gho`
2018-10-11 15:38:28 INFO Ghost table altered
2018-10-11 15:38:28 INFO Intercepted changelog state GhostTableMigrated
2018-10-11 15:38:28 INFO Waiting for ghost table to be migrated. Current lag is 0s
2018-10-11 15:38:28 INFO Handled changelog state GhostTableMigrated
2018-10-11 15:38:28 INFO Chosen shared unique key is PRIMARY
2018-10-11 15:38:28 INFO Shared columns are id,employeeid,employeename
2018-10-11 15:38:28 INFO Listening on unix socket file: /tmp/gh-ost.test.employee1.sock
2018-10-11 15:38:28 INFO Migration min values: [1]
2018-10-11 15:38:28 INFO Migration max values: [10000]
2018-10-11 15:38:28 INFO Waiting for first throttle metrics to be collected
2018-10-11 15:38:28 INFO First throttle metrics collected
# Migrating `test`.`employee1`; Ghost table is `test`.`_employee1_gho`
# Migrating VM_24_101_centos:3306; inspecting VM_24_101_centos:3306; executing on VM_24_101_centos
# Migration started at Fri May 29 15:38:27 +0800 2020
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=512; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.employee1.sock
Copy: 0/10028 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: bin.000070:320610958; State: migrating; ETA: N/A
Copy: 0/10028 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: bin.000070:320618234; State: migrating; ETA: N/A
2018-10-11 15:38:29 INFO Row copy complete
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: bin.000070:320813452; State: migrating; ETA: due
2018-10-11 15:38:29 INFO Grabbing voluntary lock: gh-ost.40312.lock
2018-10-11 15:38:29 INFO Setting LOCK timeout as 6 seconds
2018-10-11 15:38:29 INFO Looking for magic cut-over table
2018-10-11 15:38:29 INFO Creating magic cut-over table `test`.`_employee1_del`
2018-10-11 15:38:29 INFO Magic cut-over table created
2018-10-11 15:38:29 INFO Locking `test`.`employee1`, `test`.`_employee1_del`
2018-10-11 15:38:29 INFO Tables locked
2018-10-11 15:38:29 INFO Session locking original & magic tables is 40312
2018-10-11 15:38:29 INFO Writing changelog state: AllEventsUpToLockProcessed:1590737909807671836
2018-10-11 15:38:29 INFO Intercepted changelog state AllEventsUpToLockProcessed
2018-10-11 15:38:29 INFO Handled changelog state AllEventsUpToLockProcessed
2018-10-11 15:38:29 INFO Waiting for events up to lock
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: bin.000070:320823747; State: migrating; ETA: due
2018-10-11 15:38:30 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1590737909807671836
2018-10-11 15:38:30 INFO Done waiting for events up to lock; duration=974.564957ms
# Migrating `test`.`employee1`; Ghost table is `test`.`_employee1_gho`
# Migrating VM_24_101_centos:3306; inspecting VM_24_101_centos:3306; executing on VM_24_101_centos
# Migration started at Fri May 29 15:38:27 +0800 2020
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=512; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.employee1.sock
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: bin.000070:320825878; State: migrating; ETA: due
2018-10-11 15:38:30 INFO Setting RENAME timeout as 3 seconds
2018-10-11 15:38:30 INFO Session renaming tables is 40314
2018-10-11 15:38:30 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`employee1` to `test`.`_employee1_del`, `test`.`_employee1_gho` to `test`.`employee1`
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: bin.000070:320832073; State: migrating; ETA: due
2018-10-11 15:38:31 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don t strictly have to)
2018-10-11 15:38:31 INFO Checking session lock: gh-ost.40312.lock
2018-10-11 15:38:31 INFO Connection holding lock on original table still exists
2018-10-11 15:38:31 INFO Will now proceed to drop magic table and unlock tables
2018-10-11 15:38:31 INFO Dropping magic cut-over table
2018-10-11 15:38:31 INFO Releasing lock from `test`.`employee1`, `test`.`_employee1_del`
2018-10-11 15:38:31 INFO Tables unlocked
2018-10-11 15:38:31 INFO Tables renamed
2018-10-11 15:38:31 INFO Lock & rename duration: 2.021372845s. During this time, queries on `employee1` were blocked
2018-10-11 15:38:31 INFO Looking for magic cut-over table
[2020/05/29 15:38:31] [info] binlogsyncer.go:164 syncer is closing...
[2020/05/29 15:38:31] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
2018-10-11 15:38:31 INFO Closed streamer connection. err=<nil>
[2020/05/29 15:38:31] [info] binlogsyncer.go:179 syncer is closed
2018-10-11 15:38:31 INFO Dropping table `test`.`_employee1_ghc`
2018-10-11 15:38:31 INFO Table dropped
2018-10-11 15:38:31 INFO Dropping table `test`.`_employee1_del`
2018-10-11 15:38:31 INFO Table dropped
2018-10-11 15:38:31 INFO Done migrating `test`.`employee1`
2018-10-11 15:38:31 INFO Removing socket file: /tmp/gh-ost.test.employee1.sock
2018-10-11 15:38:31 INFO Tearing down inspector
2018-10-11 15:38:31 INFO Tearing down applier
2018-10-11 15:38:31 INFO Tearing down streamer
2018-10-11 15:38:31 INFO Tearing down throttler
# Done
[mysql@VM_24_101_centos ~]$
打印的general_log：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
8702018-10-11T15:38:27.796759+08:00 40303 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:27.796967+08:00 40303 Query SELECT @@max_allowed_packet
2018-10-11T15:38:27.797151+08:00 40303 Query SET autocommit=true
2018-10-11T15:38:27.797283+08:00 40303 Query SET NAMES utf8mb4
2018-10-11T15:38:27.797446+08:00 40303 Query select @@global.version
2018-10-11T15:38:27.797751+08:00 40303 Query select @@global.port
2018-10-11T15:38:27.797976+08:00 40303 Query select @@global.hostname, @@global.port
2018-10-11T15:38:27.798176+08:00 40303 Query show /* gh-ost */ grants for current_user()
2018-10-11T15:38:27.798307+08:00 40303 Query select @@global.log_bin, @@global.binlog_format
2018-10-11T15:38:27.798396+08:00 40303 Query select @@global.binlog_row_image
2018-10-11T15:38:27.799003+08:00 40304 Connect root@127.0.0.1 on information_schema using TCP/IP
2018-10-11T15:38:27.799177+08:00 40304 Query SELECT @@max_allowed_packet
2018-10-11T15:38:27.799343+08:00 40304 Query SET autocommit=true
2018-10-11T15:38:27.799460+08:00 40304 Query SET NAMES utf8mb4
2018-10-11T15:38:27.799571+08:00 40304 Query show slave status
2018-10-11T15:38:27.799829+08:00 40304 Quit
2018-10-11T15:38:27.799880+08:00 40303 Query show /* gh-ost */ table status from `test` like 'employee1'
2018-10-11T15:38:27.800450+08:00 40303 Query SELECT
SUM(REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA='test' AND TABLE_NAME='employee1') as num_child_side_fk,
SUM(REFERENCED_TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_SCHEMA='test' AND REFERENCED_TABLE_NAME='employee1') as num_parent_side_fk
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME IS NOT NULL
AND ((TABLE_SCHEMA='test' AND TABLE_NAME='employee1')
OR (REFERENCED_TABLE_SCHEMA='test' AND REFERENCED_TABLE_NAME='employee1')
)
2018-10-11T15:38:28.328510+08:00 40303 Query SELECT COUNT(*) AS num_triggers
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE
TRIGGER_SCHEMA='test'
AND EVENT_OBJECT_TABLE='employee1'
2018-10-11T15:38:28.334174+08:00 40303 Query explain select /* gh-ost */ * from `test`.`employee1` where 1=1
2018-10-11T15:38:28.359593+08:00 40303 Query SELECT
COLUMNS.TABLE_SCHEMA,
COLUMNS.TABLE_NAME,
COLUMNS.COLUMN_NAME,
UNIQUES.INDEX_NAME,
UNIQUES.COLUMN_NAMES,
UNIQUES.COUNT_COLUMN_IN_INDEX,
COLUMNS.DATA_TYPE,
COLUMNS.CHARACTER_SET_NAME,
LOCATE('auto_increment', EXTRA) > 0 as is_auto_increment,
has_nullable
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COUNT(*) AS COUNT_COLUMN_IN_INDEX,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES,
SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME,
SUM(NULLABLE='YES') > 0 AS has_nullable
FROM INFORMATION_SCHEMA.STATISTICS
WHERE
NON_UNIQUE=0
AND TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'employee1'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS UNIQUES
ON (
COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME
)
WHERE
COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = 'employee1'
ORDER BY
COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME,
CASE UNIQUES.INDEX_NAME
WHEN 'PRIMARY' THEN 0
ELSE 1
END,
CASE has_nullable
WHEN 0 THEN 0
ELSE 1
END,
CASE IFNULL(CHARACTER_SET_NAME, '')
WHEN '' THEN 0
ELSE 1
END,
CASE DATA_TYPE
WHEN 'tinyint' THEN 0
WHEN 'smallint' THEN 1
WHEN 'int' THEN 2
WHEN 'bigint' THEN 3
ELSE 100
END,
COUNT_COLUMN_IN_INDEX
2018-10-11T15:38:28.361667+08:00 40303 Query show columns from `test`.`employee1`
2018-10-11T15:38:28.362720+08:00 40305 Connect root@127.0.0.1 on information_schema using TCP/IP
2018-10-11T15:38:28.362951+08:00 40305 Query SELECT @@max_allowed_packet
2018-10-11T15:38:28.363171+08:00 40305 Query SET autocommit=true
2018-10-11T15:38:28.363300+08:00 40305 Query SET NAMES utf8mb4
2018-10-11T15:38:28.363417+08:00 40305 Query show slave status
2018-10-11T15:38:28.363647+08:00 40305 Quit
2018-10-11T15:38:28.363739+08:00 40303 Query select @@global.log_slave_updates
2018-10-11T15:38:28.364010+08:00 40303 Query select @@global.version
2018-10-11T15:38:28.364218+08:00 40303 Query select @@global.port
2018-10-11T15:38:28.364408+08:00 40303 Query show /* gh-ost readCurrentBinlogCoordinates */ master status
2018-10-11T15:38:28.365328+08:00 40306 Connect root@127.0.0.1 on using TCP/IP
2018-10-11T15:38:28.365632+08:00 40306 Query SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'
2018-10-11T15:38:28.366882+08:00 40306 Query SET @master_binlog_checksum='NONE'
2018-10-11T15:38:28.367450+08:00 40306 Binlog Dump Log: 'bin.000070' Pos: 320607755
2018-10-11T15:38:28.367748+08:00 40303 Query select @@global.version
2018-10-11T15:38:28.368343+08:00 40303 Query select @@global.port
2018-10-11T15:38:28.368961+08:00 40307 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:28.369102+08:00 40307 Query SELECT @@max_allowed_packet
2018-10-11T15:38:28.369202+08:00 40307 Query SET autocommit=true
2018-10-11T15:38:28.369283+08:00 40307 Query SET NAMES utf8mb4
2018-10-11T15:38:28.369362+08:00 40307 Query select @@global.version
2018-10-11T15:38:28.369513+08:00 40307 Query select @@global.port
2018-10-11T15:38:28.369636+08:00 40303 Query select @@global.time_zone
2018-10-11T15:38:28.369918+08:00 40303 Query select @@global.hostname, @@global.port
2018-10-11T15:38:28.370060+08:00 40303 Query show columns from `test`.`employee1`
2018-10-11T15:38:28.370612+08:00 40303 Query drop /* gh-ost */ table if exists `test`.`_employee1_gho`
2018-10-11T15:38:28.377122+08:00 40303 Query show /* gh-ost */ table status from `test` like '_employee1_gho'
2018-10-11T15:38:28.377393+08:00 40303 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
2018-10-11T15:38:28.378699+08:00 40303 Query show /* gh-ost */ table status from `test` like '_employee1_del'
2018-10-11T15:38:28.378938+08:00 40303 Query drop /* gh-ost */ table if exists `test`.`_employee1_ghc`
2018-10-11T15:38:28.385496+08:00 40303 Query create /* gh-ost */ table `test`.`_employee1_ghc` (
id bigint auto_increment,
last_update timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
hint varchar(64) charset ascii not null,
value varchar(4096) charset ascii not null,
primary key(id),
unique key hint_uidx(hint)
) auto_increment=256
2018-10-11T15:38:28.419446+08:00 40303 Query create /* gh-ost */ table `test`.`_employee1_gho` like `test`.`employee1`
2018-10-11T15:38:28.445917+08:00 40303 Query alter /* gh-ost */ table `test`.`_employee1_gho` engine=innodb
2018-10-11T15:38:28.480994+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(2, 0), 'state', 'GhostTableMigrated')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.483727+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'state at 1590737908483609931', 'GhostTableMigrated')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.487362+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.487157014+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.487438+08:00 40308 Connect root@127.0.0.1 on information_schema using TCP/IP
2018-10-11T15:38:28.487582+08:00 40308 Query SELECT @@max_allowed_packet
2018-10-11T15:38:28.487684+08:00 40308 Query SET autocommit=true
2018-10-11T15:38:28.487744+08:00 40308 Query SET NAMES utf8mb4
2018-10-11T15:38:28.487897+08:00 40308 Query show slave status
2018-10-11T15:38:28.488401+08:00 40309 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:28.488525+08:00 40309 Query SELECT @@max_allowed_packet
2018-10-11T15:38:28.488614+08:00 40309 Query SET autocommit=true
2018-10-11T15:38:28.488679+08:00 40309 Query SET NAMES utf8mb4
2018-10-11T15:38:28.488893+08:00 40309 Query SELECT
COLUMNS.TABLE_SCHEMA,
COLUMNS.TABLE_NAME,
COLUMNS.COLUMN_NAME,
UNIQUES.INDEX_NAME,
UNIQUES.COLUMN_NAMES,
UNIQUES.COUNT_COLUMN_IN_INDEX,
COLUMNS.DATA_TYPE,
COLUMNS.CHARACTER_SET_NAME,
LOCATE('auto_increment', EXTRA) > 0 as is_auto_increment,
has_nullable
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COUNT(*) AS COUNT_COLUMN_IN_INDEX,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES,
SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME,
SUM(NULLABLE='YES') > 0 AS has_nullable
FROM INFORMATION_SCHEMA.STATISTICS
WHERE
NON_UNIQUE=0
AND TABLE_SCHEMA = 'test'
AND TABLE_NAME = '_employee1_gho'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS UNIQUES
ON (
COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME
)
WHERE
COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = '_employee1_gho'
ORDER BY
COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME,
CASE UNIQUES.INDEX_NAME
WHEN 'PRIMARY' THEN 0
ELSE 1
END,
CASE has_nullable
WHEN 0 THEN 0
ELSE 1
END,
CASE IFNULL(CHARACTER_SET_NAME, '')
WHEN '' THEN 0
ELSE 1
END,
CASE DATA_TYPE
WHEN 'tinyint' THEN 0
WHEN 'smallint' THEN 1
WHEN 'int' THEN 2
WHEN 'bigint' THEN 3
ELSE 100
END,
COUNT_COLUMN_IN_INDEX
2018-10-11T15:38:28.490030+08:00 40309 Query show columns from `test`.`_employee1_gho`
2018-10-11T15:38:28.490435+08:00 40309 Query select
*
from
information_schema.columns
where
table_schema='test'
and table_name='employee1'
2018-10-11T15:38:28.491220+08:00 40309 Query select
*
from
information_schema.columns
where
table_schema='test'
and table_name='employee1'
2018-10-11T15:38:28.491648+08:00 40309 Query select
*
from
information_schema.columns
where
table_schema='test'
and table_name='_employee1_gho'
2018-10-11T15:38:28.492166+08:00 40303 Query select /* gh-ost `test`.`employee1` */ `id`
from
`test`.`employee1`
order by
`id` asc
limit 1
2018-10-11T15:38:28.492369+08:00 40309 Query select /* gh-ost `test`.`employee1` */ `id`
from
`test`.`employee1`
order by
`id` desc
limit 1
2018-10-11T15:38:28.492686+08:00 40309 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:28.492707+08:00 40303 Query show global status like 'Threads_running'
2018-10-11T15:38:28.493066+08:00 40309 Query show global status like 'Threads_running'
2018-10-11T15:38:28.493758+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'copy iteration 0 at 1590737908', 'Copy: 0/10028 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: bin.000070:320610958; State: migrating; ETA: N/A')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.592131+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.591944857+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.593200+08:00 40303 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:28.692156+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.691988298+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.693217+08:00 40309 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:28.792164+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.791963767+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.793212+08:00 40303 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:28.892118+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.891946977+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.893150+08:00 40309 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:28.992167+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.991978+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.993216+08:00 40303 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.105879+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.105683131+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.105878+08:00 40303 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.192162+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.191915709+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.193158+08:00 40309 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.292151+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.291919747+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.293179+08:00 40303 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.392095+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.391930285+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.393205+08:00 40309 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.492159+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.491902668+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.493237+08:00 40303 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.494187+08:00 40310 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:29.494198+08:00 40311 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:29.494760+08:00 40310 Query SELECT @@max_allowed_packet
2018-10-11T15:38:29.494868+08:00 40311 Query SELECT @@max_allowed_packet
2018-10-11T15:38:29.495139+08:00 40311 Query SET autocommit=true
2018-10-11T15:38:29.495258+08:00 40311 Query SET NAMES utf8mb4
2018-10-11T15:38:29.495265+08:00 40310 Query SET autocommit=true
2018-10-11T15:38:29.495466+08:00 40310 Query SET NAMES utf8mb4
2018-10-11T15:38:29.495702+08:00 40310 Query show global status like 'Threads_running'
2018-10-11T15:38:29.496312+08:00 40311 Query select /* gh-ost `test`.`employee1` iteration:0 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'1') or ((`id` = _binary'1'))) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.496521+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'copy iteration 0 at 1590737909', 'Copy: 0/10028 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: bin.000070:320618234; State: migrating; ETA: N/A')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.496554+08:00 40309 Query show global status like 'Threads_running'
2018-10-11T15:38:29.506595+08:00 40303 Quit
2018-10-11T15:38:29.507086+08:00 40311 Quit
2018-10-11T15:38:29.507153+08:00 40310 Query START TRANSACTION
2018-10-11T15:38:29.507400+08:00 40310 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.507853+08:00 40310 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'1') or ((`id` = _binary'1'))) and ((`id` < _binary'1000') or ((`id` = _binary'1000')))) lock in share mode
)
2018-10-11T15:38:29.523454+08:00 40310 Query COMMIT
2018-10-11T15:38:29.533501+08:00 40309 Query select /* gh-ost `test`.`employee1` iteration:1 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'1000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.534075+08:00 40310 Query START TRANSACTION
2018-10-11T15:38:29.534227+08:00 40310 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.534369+08:00 40310 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'1000')) and ((`id` < _binary'2000') or ((`id` = _binary'2000')))) lock in share mode
)
2018-10-11T15:38:29.543074+08:00 40310 Query COMMIT
2018-10-11T15:38:29.586554+08:00 40309 Query select /* gh-ost `test`.`employee1` iteration:2 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'2000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.587919+08:00 40310 Query START TRANSACTION
2018-10-11T15:38:29.588183+08:00 40310 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.588485+08:00 40310 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'2000')) and ((`id` < _binary'3000') or ((`id` = _binary'3000')))) lock in share mode
)
2018-10-11T15:38:29.591994+08:00 40309 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.591866842+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.593465+08:00 40312 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:29.593671+08:00 40312 Query SELECT @@max_allowed_packet
2018-10-11T15:38:29.593802+08:00 40312 Query SET NAMES utf8mb4
2018-10-11T15:38:29.593936+08:00 40312 Query SET autocommit=true
2018-10-11T15:38:29.594077+08:00 40312 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.602754+08:00 40310 Query COMMIT
2018-10-11T15:38:29.611800+08:00 40310 Quit
2018-10-11T15:38:29.612020+08:00 40312 Query select /* gh-ost `test`.`employee1` iteration:3 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'3000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.612725+08:00 40309 Query START TRANSACTION
2018-10-11T15:38:29.612883+08:00 40309 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.613169+08:00 40309 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'3000')) and ((`id` < _binary'4000') or ((`id` = _binary'4000')))) lock in share mode
)
2018-10-11T15:38:29.629626+08:00 40309 Query COMMIT
2018-10-11T15:38:29.672734+08:00 40312 Query select /* gh-ost `test`.`employee1` iteration:4 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'4000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.674106+08:00 40309 Query START TRANSACTION
2018-10-11T15:38:29.674348+08:00 40309 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.674589+08:00 40309 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'4000')) and ((`id` < _binary'5000') or ((`id` = _binary'5000')))) lock in share mode
)
2018-10-11T15:38:29.683215+08:00 40309 Query COMMIT
2018-10-11T15:38:29.688292+08:00 40312 Query select /* gh-ost `test`.`employee1` iteration:5 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'5000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.688930+08:00 40309 Query START TRANSACTION
2018-10-11T15:38:29.689193+08:00 40309 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.689449+08:00 40309 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'5000')) and ((`id` < _binary'6000') or ((`id` = _binary'6000')))) lock in share mode
)
2018-10-11T15:38:29.692033+08:00 40312 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.691873562+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.693417+08:00 40313 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:29.693606+08:00 40313 Query SELECT @@max_allowed_packet
2018-10-11T15:38:29.693715+08:00 40313 Query SET autocommit=true
2018-10-11T15:38:29.693881+08:00 40313 Query SET NAMES utf8mb4
2018-10-11T15:38:29.694047+08:00 40313 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.706414+08:00 40309 Query COMMIT
2018-10-11T15:38:29.710002+08:00 40309 Quit
2018-10-11T15:38:29.710036+08:00 40313 Query select /* gh-ost `test`.`employee1` iteration:6 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'6000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.710568+08:00 40312 Query START TRANSACTION
2018-10-11T15:38:29.710719+08:00 40312 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.710962+08:00 40312 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'6000')) and ((`id` < _binary'7000') or ((`id` = _binary'7000')))) lock in share mode
)
2018-10-11T15:38:29.720031+08:00 40312 Query COMMIT
2018-10-11T15:38:29.724876+08:00 40313 Query select /* gh-ost `test`.`employee1` iteration:7 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'7000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.725421+08:00 40312 Query START TRANSACTION
2018-10-11T15:38:29.725689+08:00 40312 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.725942+08:00 40312 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'7000')) and ((`id` < _binary'8000') or ((`id` = _binary'8000')))) lock in share mode
)
2018-10-11T15:38:29.748448+08:00 40312 Query COMMIT
2018-10-11T15:38:29.754426+08:00 40313 Query select /* gh-ost `test`.`employee1` iteration:8 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'8000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.755265+08:00 40312 Query START TRANSACTION
2018-10-11T15:38:29.755448+08:00 40312 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.755605+08:00 40312 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'8000')) and ((`id` < _binary'9000') or ((`id` = _binary'9000')))) lock in share mode
)
2018-10-11T15:38:29.766785+08:00 40312 Query COMMIT
2018-10-11T15:38:29.770199+08:00 40313 Query select /* gh-ost `test`.`employee1` iteration:9 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'9000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.770741+08:00 40312 Query START TRANSACTION
2018-10-11T15:38:29.770860+08:00 40312 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.771014+08:00 40312 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'9000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))) lock in share mode
)
2018-10-11T15:38:29.778317+08:00 40312 Query COMMIT
2018-10-11T15:38:29.781554+08:00 40313 Query select /* gh-ost `test`.`employee1` iteration:10 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'10000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
2018-10-11T15:38:29.781810+08:00 40312 Query select /* gh-ost `test`.`employee1` iteration:10 */ `id`
from (
select
`id`
from
`test`.`employee1`
where ((`id` > _binary'10000')) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1000
) select_osc_chunk
order by
`id` desc
limit 1
2018-10-11T15:38:29.782135+08:00 40313 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'copy iteration 10 at 1590737909', 'Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: bin.000070:320813452; State: migrating; ETA: due')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.784806+08:00 40312 Query START TRANSACTION
2018-10-11T15:38:29.785567+08:00 40312 Query select connection_id()
2018-10-11T15:38:29.785720+08:00 40312 Query select get_lock('gh-ost.40312.lock', 0)
2018-10-11T15:38:29.785846+08:00 40312 Query set session lock_wait_timeout:=6
2018-10-11T15:38:29.791062+08:00 40313 Query show /* gh-ost */ table status from `test` like '_employee1_del'
2018-10-11T15:38:29.791374+08:00 40313 Query create /* gh-ost */ table `test`.`_employee1_del` (
id int auto_increment primary key
) engine=InnoDB comment='ghost-cut-over-sentry'
2018-10-11T15:38:29.792202+08:00 40314 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:29.793355+08:00 40314 Query SELECT @@max_allowed_packet
2018-10-11T15:38:29.793370+08:00 40315 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:29.793452+08:00 40314 Query SET autocommit=true
2018-10-11T15:38:29.793460+08:00 40315 Query SELECT @@max_allowed_packet
2018-10-11T15:38:29.793515+08:00 40314 Query SET NAMES utf8mb4
2018-10-11T15:38:29.793610+08:00 40315 Query SET autocommit=true
2018-10-11T15:38:29.793646+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.791855122+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.793942+08:00 40315 Query SET NAMES utf8mb4
2018-10-11T15:38:29.794103+08:00 40315 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.807138+08:00 40313 Quit
2018-10-11T15:38:29.807226+08:00 40312 Query lock /* gh-ost */ tables `test`.`employee1` write, `test`.`_employee1_del` write
2018-10-11T15:38:29.807813+08:00 40315 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(2, 0), 'state', 'AllEventsUpToLockProcessed:1590737909807671836')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.810430+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'state at 1590737909810266435', 'AllEventsUpToLockProcessed:1590737909807671836')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.892168+08:00 40315 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.891967268+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.893179+08:00 40314 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:29.992165+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:29.991965407+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:29.993199+08:00 40315 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.092194+08:00 40315 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.091928438+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.093227+08:00 40314 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.192141+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.191949581+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.193143+08:00 40315 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.292137+08:00 40315 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.291941416+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.293149+08:00 40314 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.392240+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.392035339+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.393275+08:00 40315 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.492165+08:00 40315 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.491972791+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.493176+08:00 40314 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.493939+08:00 40316 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:30.494139+08:00 40316 Query SELECT @@max_allowed_packet
2018-10-11T15:38:30.494249+08:00 40316 Query SET autocommit=true
2018-10-11T15:38:30.494318+08:00 40316 Query SET NAMES utf8mb4
2018-10-11T15:38:30.494385+08:00 40316 Query show global status like 'Threads_running'
2018-10-11T15:38:30.494988+08:00 40314 Query show global status like 'Threads_running'
2018-10-11T15:38:30.495802+08:00 40315 Quit
2018-10-11T15:38:30.496482+08:00 40316 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'copy iteration 10 at 1590737910', 'Copy: 10000/10000 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: bin.000070:320823747; State: migrating; ETA: due')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.592238+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.591999354+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.593255+08:00 40316 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.692187+08:00 40316 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.691992755+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.693206+08:00 40314 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.782521+08:00 40314 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'copy iteration 10 at 1590737910', 'Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: bin.000070:320825878; State: migrating; ETA: due')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.792059+08:00 40316 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.791873789+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.792295+08:00 40314 Query START TRANSACTION
2018-10-11T15:38:30.792439+08:00 40314 Query select connection_id()
2018-10-11T15:38:30.792794+08:00 40314 Query set session lock_wait_timeout:=3
2018-10-11T15:38:30.793033+08:00 40317 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:30.793157+08:00 40317 Query SELECT @@max_allowed_packet
2018-10-11T15:38:30.793316+08:00 40317 Query SET autocommit=true
2018-10-11T15:38:30.793423+08:00 40317 Query SET NAMES utf8mb4
2018-10-11T15:38:30.793585+08:00 40317 Query select id
from information_schema.processlist
where
id != connection_id()
and 40314 in (0, id)
and state like concat('%', 'metadata lock', '%')
and info like concat('%', 'rename', '%')
2018-10-11T15:38:30.793827+08:00 40314 Query rename /* gh-ost */ table `test`.`employee1` to `test`.`_employee1_del`, `test`.`_employee1_gho` to `test`.`employee1`
2018-10-11T15:38:30.794321+08:00 40318 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:30.794490+08:00 40318 Query SELECT @@max_allowed_packet
2018-10-11T15:38:30.794641+08:00 40318 Query SET autocommit=true
2018-10-11T15:38:30.795166+08:00 40318 Query SET NAMES utf8mb4
2018-10-11T15:38:30.795347+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.796006+08:00 40316 Quit
2018-10-11T15:38:30.892133+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.891962913+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.893297+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:30.992155+08:00 40318 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:30.991958953+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:30.993278+08:00 40317 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.092115+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.091945166+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.093206+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.192121+08:00 40318 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.191943104+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.193195+08:00 40317 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.292113+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.291920755+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.293131+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.392166+08:00 40318 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.391927846+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.393211+08:00 40317 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.492258+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.491963265+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.493225+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.493639+08:00 40318 Query show global status like 'Threads_running'
2018-10-11T15:38:31.494432+08:00 40318 Query show global status like 'Threads_running'
2018-10-11T15:38:31.496602+08:00 40318 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'copy iteration 10 at 1590737911', 'Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: bin.000070:320832073; State: migrating; ETA: due')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.592213+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.592016295+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.593217+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.692115+08:00 40318 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.691941596+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.693157+08:00 40317 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.792078+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.791911518+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.793129+08:00 40318 Query select hint, value from `test`.`_employee1_ghc` where hint = 'heartbeat' and id <= 255
2018-10-11T15:38:31.794412+08:00 40318 Query select id
from information_schema.processlist
where
id != connection_id()
and 40314 in (0, id)
and state like concat('%', 'metadata lock', '%')
and info like concat('%', 'rename', '%')
2018-10-11T15:38:31.794952+08:00 40318 Query select is_used_lock('gh-ost.40312.lock')
2018-10-11T15:38:31.795148+08:00 40312 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
2018-10-11T15:38:31.804223+08:00 40312 Query unlock tables
2018-10-11T15:38:31.804366+08:00 40312 Query ROLLBACK
2018-10-11T15:38:31.804450+08:00 40312 Quit
2018-10-11T15:38:31.828578+08:00 40314 Query ROLLBACK
2018-10-11T15:38:31.828661+08:00 40318 Query show /* gh-ost */ table status from `test` like '_employee1_del'
2018-10-11T15:38:31.829299+08:00 40318 Quit
2018-10-11T15:38:31.892061+08:00 40317 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:31.891916935+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:31.895474+08:00 40314 Query drop /* gh-ost */ table if exists `test`.`_employee1_ghc`
2018-10-11T15:38:31.903826+08:00 40317 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
2018-10-11T15:38:31.927905+08:00 40314 Quit
2018-10-11T15:38:31.927992+08:00 40307 Quit
2018-10-11T15:38:31.928011+08:00 40308 Quit
2018-10-11T15:38:31.928082+08:00 40317 Quit
执行过程解析
检查
检查用户、权限、binlog信息、字符集、复制状态、表状态等1
2
3
4
5
6
7
8
9
10
11
12
13
14
152018-10-11T15:38:27.796759+08:00 40303 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:27.796967+08:00 40303 Query SELECT @@max_allowed_packet
2018-10-11T15:38:27.797151+08:00 40303 Query SET autocommit=true
2018-10-11T15:38:27.797283+08:00 40303 Query SET NAMES utf8mb4
2018-10-11T15:38:27.797446+08:00 40303 Query select @@global.version
2018-10-11T15:38:27.797751+08:00 40303 Query select @@global.port
2018-10-11T15:38:27.797976+08:00 40303 Query select @@global.hostname, @@global.port
2018-10-11T15:38:27.798176+08:00 40303 Query show /* gh-ost */ grants for current_user()
2018-10-11T15:38:27.798307+08:00 40303 Query select @@global.log_bin, @@global.binlog_format
2018-10-11T15:38:27.798396+08:00 40303 Query select @@global.binlog_row_image
2018-10-11T15:38:27.799003+08:00 40304 Connect root@127.0.0.1 on information_schema using TCP/IP
2018-10-11T15:38:27.799177+08:00 40304 Query SELECT @@max_allowed_packet
2018-10-11T15:38:27.799343+08:00 40304 Query SET autocommit=true
2018-10-11T15:38:27.799460+08:00 40304 Query SET NAMES utf8mb4
2018-10-11T15:38:27.799571+08:00 40304 Query show slave status
检查外键、触发器、行数预估、索引、字段等信息1
2
3
4
5
62018-10-11T15:38:27.800450+08:00 40303 Query SELECT
SUM(REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA='test' AND TABLE_NAME='employee1') as num_child_side_fk,......
2018-10-11T15:38:28.328510+08:00 40303 Query SELECT COUNT(*) AS num_triggers......
2018-10-11T15:38:28.334174+08:00 40303 Query explain select /* gh-ost */ * from `test`.`employee1` where 1=1......
2018-10-11T15:38:28.359593+08:00 40303 Query SELECT
COLUMNS.TABLE_SCHEMA,......
伪装slave
模拟 slave，获取当前的位点信息，创建 binlog streamer 监听 binlog1
2
3
4
52018-10-11T15:38:28.364408+08:00 40303 Query show /* gh-ost readCurrentBinlogCoordinates */ master status
2018-10-11T15:38:28.365328+08:00 40306 Connect root@127.0.0.1 on using TCP/IP
2018-10-11T15:38:28.365632+08:00 40306 Query SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'
2018-10-11T15:38:28.366882+08:00 40306 Query SET @master_binlog_checksum='NONE'
2018-10-11T15:38:28.367450+08:00 40306 Binlog Dump Log: 'bin.000070' Pos: 320607755
创建日志记录表 xx_ghc 和影子表 xx_gho 并且执行 alter 语句将影子表变更为目标表结构
1 | 2018-10-11T15:38:28.385496+08:00 40303 Query create /* gh-ost */ table `test`.`_employee1_ghc` ( |
insert into xx_gho select * from xx 拷贝数据
同时从general_log中可以相关的步骤会记录到xx_ghc表中，其中包括GhostTableMigrated，heartbeat等信息，并且此类信息会贯穿DDL整个周期内1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
222018-10-11T15:38:28.480994+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(2, 0), 'state', 'GhostTableMigrated')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.483727+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(0, 0), 'state at 1590737908483609931', 'GhostTableMigrated')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
2018-10-11T15:38:28.487362+08:00 40303 Query insert /* gh-ost */ into `test`.`_employee1_ghc`
(id, hint, value)
values
(NULLIF(1, 0), 'heartbeat', '2018-10-11T15:38:28.487157014+08:00')
on duplicate key update
last_update=NOW(),
value=VALUES(value)
......
紧接着获取获取当前的最大主键和最小主键，然后根据命令行传参 chunk 获取数据 ，开启事务，insert 到影子表里面，循环此步骤，直到数据copy完成
rowcopy 过程中是对原表加上 lock in share mode,防止数据在 copy 的过程中被修改1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
202018-10-11T15:38:29.496312+08:00 40311 Query select /* gh-ost `test`.`employee1` iteration:0 */
`id`
from
`test`.`employee1`
where ((`id` > _binary'1') or ((`id` = _binary'1'))) and ((`id` < _binary'10000') or ((`id` = _binary'10000')))
order by
`id` asc
limit 1
offset 999
......
2018-10-11T15:38:29.507153+08:00 40310 Query START TRANSACTION
2018-10-11T15:38:29.507400+08:00 40310 Query SET
SESSION time_zone = 'SYSTEM',
sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')
2018-10-11T15:38:29.507853+08:00 40310 Query insert /* gh-ost `test`.`employee1` */ ignore into `test`.`_employee1_gho` (`id`, `employeeid`, `employeename`)
(select `id`, `employeeid`, `employeename` from `test`.`employee1` force index (`PRIMARY`)
where (((`id` > _binary'1') or ((`id` = _binary'1'))) and ((`id` < _binary'1000') or ((`id` = _binary'1000')))) lock in share mode
)
2018-10-11T15:38:29.523454+08:00 40310 Query COMMIT
......
应用增量数据
由于测试环境在做DDL期间，没有增量数据写入，general_log并没有读取binlog相关的信息
- row copy 转化为insert ignore into 到影子表
- apply binlog 转化为replace into 应用到影子表
创建xx_del表
防止 cut-over 提前执行，导致数据丢失。最后做影子表和原表的交换及收尾工作1
2
3
4
5
6
7
8
92018-10-11T15:38:29.791374+08:00 40313 Query create /* gh-ost */ table `test`.`_employee1_del` (
id int auto_increment primary key
) engine=InnoDB comment='ghost-cut-over-sentry'......
2018-10-11T15:38:29.807226+08:00 40312 Query lock /* gh-ost */ tables `test`.`employee1` write, `test`.`_employee1_del` write......
2018-10-11T15:38:30.793827+08:00 40314 Query rename /* gh-ost */ table `test`.`employee1` to `test`.`_employee1_del`, `test`.`_employee1_gho` to `test`.`employee1`......
2018-10-11T15:38:31.795148+08:00 40312 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
2018-10-11T15:38:31.804223+08:00 40312 Query unlock tables......
2018-10-11T15:38:31.895474+08:00 40314 Query drop /* gh-ost */ table if exists `test`.`_employee1_ghc`
2018-10-11T15:38:31.903826+08:00 40317 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
执行过程总结
- ① 检查有没有外键和触发器
- ② 检查表的主键信息
- ③ 检查是否主库或从库，是否开启log_slave_updates，以及binlog信息
- ④ 检查gho和del结尾的临时表是否存在
- ⑤ 创建ghc结尾的表，存数据迁移的信息，以及binlog信息等
- ⑥ 初始化stream的连接,添加binlog的监听
- ⑥ 创建gho结尾的临时表，执行DDL在gho结尾的临时表上
- ⑦ 开启事务，按照主键id把源表数据写入到gho结尾的表上，再提交，以及binlog apply
- ⑧ lock源表，rename 表：rename 源表 to 源_del表，gho表 to 源表
- ⑨ 清理ghc表
gh-ost 操作模式
gh-ost 可以同时连接多个服务器，为了获取二进制的数据流，它会作为一个从库，将数据从一个库复制到另外一个。它有各种不同的操作模式，这取决于你的设置，配置，和要运行迁移环境。
连接到从库，在主库做迁移
这是 gh-ost 默认的工作方式。gh-ost 将会检查从库状态，找到集群结构中的主库并连接，接下来进行迁移操作：
- 行数据在主库上读写
- 读取从库的二进制日志，将变更应用到主库
- 在从库收集表格式，字段&索引，行数等信息
- 在从库上读取内部的变更事件（如心跳事件）
- 在主库切换表
如果你的主库的日志格式是 SBR，工具也可以正常工作。但从库必须启用二级制日志(log_bin, log_slave_updates) 并且设置 binlog_format=ROW ( gh-ost 是读取从库的二级制文件)。
如果直接在主库上操作，当然也需要二进制日志格式是RBR。
连接到主库
如果你没有从库，或者不想使用从库，你可以直接在主库上操作。gh-ost 将会直接在主库上进行所有操作。你需要持续关注复制延迟问题。
- 主库的二进制日志必须是 RBR 格式
- 必须指定 –allow-on-master 参数
在从库迁移/测试
该模式会在从库执行迁移操作。gh-ost 会简单的连接到主库，此后所有的操作都在从库执行，不会对主库进行任何的改动。整个操作过程中，gh-ost 将控制速度保证从库可以及时的进行数据同步
- –migrate-on-replica 表示 gh-ost 会直接在从库上进行迁移操作。即使在复制运行阶段也可以进行表的切换操作
- –test-on-replica 表示 迁移操作只是为了测试在切换之前复制会停止，然后会进行切换操作，然后在切换回来，你的原始表最终还是原始表。两个表都会保存下来，复制操作是停止的。你可以对这两个表进行一致性检查等测试操作
时序问题
gh-ost 做 DDL 变更期间对原表和影子表的操作有三种:
对原表的 row copy (我们用 A 操作代替),业务对原表的 DML 操作(B),对影子表的 apply binlog(C)。而且 binlog 是基于 DML 操作产生的，因此对影子表的 apply binlog 一定在 对原表的 DML 之后，共有ABC/BCA/BAC/三种
| 类别 | row copy(A) | DML(B) | apply binlog(C) |
|---|---|---|---|
| A-B-C，数据先被复制到影子表 | insert ignore into b select id>0 and id<3; 将数据从原表同步到影子表 |
insert into b(id,val) values(2,’b’); update b set val=’b’ where id = 1; delete from b where id = 1; |
replace into b(id,val) values(2,’b’); update b set val=’b’ where id = 1; delete from b where id = 1; |
| B-C-A，先应用dml的binlog，数据后被复制到影子表 | insert into b(id,val) values(2,’a’); update b set val=’b’ where id = 1; delete from b where id = 1; |
replace into b(id,val) values(2,’a’); update b set val=’b’ where id = 1;影子表不存在此记录，update空; delete from b where id = 1;影子表不存在此记录，delete空; |
因为id=2的记录binlog比row copy先到达，row copy执行insert ignore忽略插入; row copy时新记录已经在原表，直接复制到影子表即可; row copy时原表已无此记录，不会同步到影子表 |
| B-A-C，先插入dml，数据再被复制到影子表，最后应用dml | insert into b(id,val) values(2,’a’); update b set val=’b’ where id =1; delete from b where id = 1; |
insert ignore into b(id,val) values(2,’a’); insert ignore into b(id,val) values(1,’b’); 因为id=1的记录已经被删除，row copy空行; |
因为id=2的记录row copy比binlog先到达，影子表已经有id=2记录，apply binlog会使用replace into; 因为id=2的记录row copy比binlog先到达，binlog apply执行update… binlog apply空操作 |
通过上面的几种组合操作的分析，我们可以看到 数据最终是一致的。尤其是当copy 结束之后，只剩下apply binlog，情况更简单。
cut-over原理
general_log
从上面的general_log，截取后面的cut-over：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
482018-10-11T15:38:29.791374+08:00 40313 Query create /* gh-ost */ table `test`.`_employee1_del` (
id int auto_increment primary key
) engine=InnoDB comment='ghost-cut-over-sentry'
2018-10-11T15:38:29.807226+08:00 40312 Query lock /* gh-ost */ tables `test`.`employee1` write, `test`.`_employee1_del` write
2018-10-11T15:38:30.493939+08:00 40316 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:30.494139+08:00 40316 Query SELECT @@max_allowed_packet
2018-10-11T15:38:30.494249+08:00 40316 Query SET autocommit=true
2018-10-11T15:38:30.494318+08:00 40316 Query SET NAMES utf8mb4
2018-10-11T15:38:30.494385+08:00 40316 Query show global status like 'Threads_running'
2018-10-11T15:38:30.494988+08:00 40314 Query show global status like 'Threads_running'
2018-10-11T15:38:30.792295+08:00 40314 Query START TRANSACTION
2018-10-11T15:38:30.792439+08:00 40314 Query select connection_id()
2018-10-11T15:38:30.792794+08:00 40314 Query set session lock_wait_timeout:=3
2018-10-11T15:38:30.793033+08:00 40317 Connect root@127.0.0.1 on test using TCP/IP
2018-10-11T15:38:30.793157+08:00 40317 Query SELECT @@max_allowed_packet
2018-10-11T15:38:30.793316+08:00 40317 Query SET autocommit=true
2018-10-11T15:38:30.793423+08:00 40317 Query SET NAMES utf8mb4
2018-10-11T15:38:30.793585+08:00 40317 Query select id
from information_schema.processlist
where
id != connection_id()
and 40314 in (0, id)
and state like concat('%', 'metadata lock', '%')
and info like concat('%', 'rename', '%')
2018-10-11T15:38:30.793827+08:00 40314 Query rename /* gh-ost */ table `test`.`employee1` to `test`.`_employee1_del`, `test`.`_employee1_gho` to `test`.`employee1`
2018-10-11T15:38:31.493639+08:00 40318 Query show global status like 'Threads_running'
2018-10-11T15:38:31.494432+08:00 40318 Query show global status like 'Threads_running'
2018-10-11T15:38:31.794412+08:00 40318 Query select id
from information_schema.processlist
where
id != connection_id()
and 40314 in (0, id)
and state like concat('%', 'metadata lock', '%')
and info like concat('%', 'rename', '%')
2018-10-11T15:38:31.794952+08:00 40318 Query select is_used_lock('gh-ost.40312.lock')
2018-10-11T15:38:31.795148+08:00 40312 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
2018-10-11T15:38:31.804223+08:00 40312 Query unlock tables
2018-10-11T15:38:31.804366+08:00 40312 Query ROLLBACK
2018-10-11T15:38:31.804450+08:00 40312 Quit
2018-10-11T15:38:31.828578+08:00 40314 Query ROLLBACK
2018-10-11T15:38:31.828661+08:00 40318 Query show /* gh-ost */ table status from `test` like '_employee1_del'
2018-10-11T15:38:31.829299+08:00 40318 Quit
2018-10-11T15:38:31.895474+08:00 40314 Query drop /* gh-ost */ table if exists `test`.`_employee1_ghc`
2018-10-11T15:38:31.903826+08:00 40317 Query drop /* gh-ost */ table if exists `test`.`_employee1_del`
2018-10-11T15:38:31.927905+08:00 40314 Quit
2018-10-11T15:38:31.927992+08:00 40307 Quit
2018-10-11T15:38:31.928011+08:00 40308 Quit
2018-10-11T15:38:31.928082+08:00 40317 Quit
日志说明
其中，包括session:
- T1-40313:create table _employee1_del
- T2-40312:lock tables employee1 write, _employee1_del write
- T3-40316:show global status like ‘Threads_running’
- T4-40317:select id from information_schema.processlist where id != connection_id() and 40314 in (0, id) and state like concat(‘%’, ‘metadata lock’, ‘%’) and info like concat(‘%’, ‘rename’, ‘%’)
- T5-40314:rename table employee1 to _employee1_del, _employee1_gho to employee1
- T6-40318:select id from information_schema.processlist where id != connection_id() and 40314 in (0, id) and state like concat(‘%’, ‘metadata lock’, ‘%’) and info like concat(‘%’, ‘rename’, ‘%’)
- T7-40318:select is_used_lock(‘gh-ost.40312.lock’)
- T8-40312:drop table if exists _employee1_del
- T9-40312:unlock tables
- T10-40314:drop table if exists _employee1_ghc
- T11-40317:drop table if exists _employee1_del
日志解析
- T1 创建临时表_employee1_del，防止提前rename表，导致数据丢失。(如果创建失败，则gh-ost程序退出，此时创建语句并非create table if exists)
- T2 原表，影子表加写锁，此时原表的DML会阻塞。(如果加锁失败，则gh-ost程序退出)
- T3 获取实例状态，确定是否进行下一步。(查询状态,不影响上下文)
- T4 查看当前是否有进程有rename在运行，查询不到的话，可以进行rename操作。(如果查询到rename，下文确定是否获取T2时刻的锁)
- T5 进行rename交换表，由于在T2时刻表上有写锁，rename会阻塞。(如果此时T2意外中止，rename会退出，因为临时表_employee1_del已经存在)
- T6 再次查询是否有进程有rename在运行。(如果gh-ost捕捉不到rename，则T2会继续进行下一步T9，所有请求恢复正常)
- T7 查询到rename操作，确定T2的上锁生效。(如果T2,T5全部失败,T2会释放锁,T5会被清除)
- T8 删除临时表_employee1_del。(由于T2对_employee1_del加锁,在未unlock tables前,同一session是可以drop操作的。如果drop失败，rename也会失败，因为表对_employee1_del已存在，会继续T9，所有请求恢复正常)
- T9 释放unlock tables
- T10-T11 释放其他临时表
官方参考
作者写了三篇文章解释cut-over操作的思路和切换算法，链接1，链接2，链接3，可参考，这里仅列出官方示例说明:
The solution we offer is now based on two connections only (as opposed to three, in the optimistic approach). “Our” connections will be C10, C20. The “normal” app connections are C1..C9, C11..C19, C21..C29.
- Connections C1..C9 operate on tbl with normal DML: INSERT, UPDATE, DELETE
- Connection C10: CREATE TABLE tbl_old (id int primary key) COMMENT=’magic-be-here’
- Connection C10: LOCK TABLES tbl WRITE, tbl_old WRITE
- Connections C11..C19, newly incoming, issue queries on tbl but are blocked due to the LOCK
- Connection C20: RENAME TABLE tbl TO tbl_old, ghost TO tbl. This is blocked due to the LOCK, but gets prioritized on top connections C11..C19 and on top C1..C9 or any other connection that attempts DML on tbl
- Connections C21..C29, newly incoming, issue queries on tbl but are blocked due to the LOCK and due to the RENAME, waiting in queue
- Connection C10: checks that C20’s RENAME is applied (looks for the blocked RENAME in processlist)
- Connection 10: DROP TABLE tbl_old. Nothing happens yet; tbl is still locked. All other connections still blocked.
- Connection 10: UNLOCK TABLES. BAM! The RENAME is first to execute, ghost table is swapped in place of tbl, then C1..C9, C11..C19, C21..C29 all get to operate on the new and shiny tbl
Some notes
- We create tbl_old as a blocker for a premature swap
- It is allowed for a connection to DROP a table it has under a WRITE LOCK
- A blocked RENAME is always prioritized over a blocked INSERT/UPDATE/DELETE, no matter who came first
What happens on failures?
Much fun. Just works; no rollback required.
- If C10 errors on the CREATE we do not proceed.
- If C10 errors on the LOCK statement, we do not proceed. The table is not locked. App continues to operate as normal.
If C10 dies just as C20 is about to issue the RENAME:
1
2
3The lock is released, the queries C1..C9, C11..C19 immediately operate on tbl.
C20’s RENAME immediately fails because tbl_old exists.
The entire operation is failed, but nothing terrible happens; some queries were blocked for some time is all. We will need to retry everythingIf C10 dies while C20 is blocked on RENAME: Mostly similar to the above. Lock released, then C20 fails the RENAME (because tbl_old exists), then all queries resume normal operation
- If C20 dies before C10 drops the table, we catch the error and let C10 proceed as planned: DROP, UNLOCK. Nothing terrible happens, some queries were blocked for some time. We will need to retry
- If C20 dies just after C10 DROPs the table but before the unlock, same as above.
- If both C10 and C20 die, no problem: LOCK is cleared; RENAME lock is cleared. C1..C9, C11..C19, C21..C29 are free to operate on tbl.
No matter what happens, at the end of operation we look for the ghost table. Is it still there? Then we know the operation failed, “atomically”. Is it not there? Then it has been renamed to tbl, and the operation worked atomically.
A side note on failure is the matter of cleaning up the magic tbl_old. Here this is a matter of taste. Maybe just let it live and avoid recreating it, or you can drop it if you like.
对应用的影响
应用程序连接保证被阻止，直到交换 ghost 表或直到操作失败。在前者中，他们继续在新表上进行操作。在后者中，他们继续在原表上进行操作。
参数
以gh-ost 1.0.48版本为准
参数 |
描述 |
|---|---|
| -aliyun-rds | set ‘true’,允许在阿里云数据库上执行 |
| -allow-master-master | 是否允许gh-ost运行在双主复制架构中，一般与-assume-master-host参数一起使用 |
| -allow-nullable-unique-key | 允许gh-ost在数据迁移依赖的唯一键可以为NULL，默认为不允许为NULL的唯一键。如果数据迁移(migrate)依赖的唯一键允许NULL值，则可能造成数据不正确，请谨慎使用。 |
| -allow-on-master | 允许gh-ost直接运行在主库上，默认gh-ost连接的从库。 |
| -alter string | string:DDL语句 |
| -approve-renamed-columns | ALTER:如果你修改一个列的名字，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的，除非提供-approve-renamed-columns ALTER。 |
| -ask-pass | MySQL密码 |
| -assume-master-host | string:为gh-ost指定一个主库，格式为”ip:port”或者”hostname:port”。在这主主架构里比较有用，或则在gh-ost发现不到主的时候有用。 |
| -assume-rbr | 确认gh-ost连接的数据库实例的binlog_format=ROW的情况下，可以指定-assume-rbr，这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。 |
| -check-flag | 检查是否存在/支持另一个标志。 这允许跨版本脚本编写。 当所有其他提供的标志都存在时，以0退出，否则返回非零。 您必须为需要值的标志提供（虚拟）值。 示例：gh-ost –check-flag –cut-over-lock-timeout-seconds –nice-ratio 0 |
| -chunk-size | int:在每次迭代中处理的行数量(允许范围：100-100000)，默认值为1000。 |
| -concurrent-rowcount | 该参数如果为True(默认值)，则进行row-copy之后，估算统计行数(使用explain select count(*)方式)，并调整ETA时间，否则，gh-ost首先预估统计行数，然后开始row-copy。 |
| -conf | gh-ost的配置文件路径。 |
| -critical-load | string:一系列逗号分隔的status-name=values组成，当MySQL中status超过对应的values，gh-ost将会退出。-critical-load Threads_connected=20,Connections=1500，指的是当MySQL中的状态值Threads_connected>20,Connections>1500的时候，gh-ost将会由于该数据库严重负载而停止并退出。 |
| -critical-load-hibernate-seconds | int:负载达到critical-load时，gh-ost在指定的时间内进入休眠状态。 它不会读/写任何来自任何服务器的任何内容。 |
| -critical-load-interval-millis | int:当值为0时，当达到-critical-load，gh-ost立即退出。当值不为0时，当达到-critical-load，gh-ost会在-critical-load-interval-millis秒数后，再次进行检查，再次检查依旧达到-critical-load，gh-ost将会退出。 |
| -cut-over | string:选择cut-over类型:atomic/two-step，atomic(默认)类型的cut-over是github的算法，two-step采用的是facebook-OSC的算法。 |
| -cut-over-exponential-backoff | 两次失败的切换尝试之间的等待时间间隔成指数增长。 等待间隔服从”exponential-backoff-max-interval”可配置的最大值。 |
| -cut-over-lock-timeout-seconds | int:gh-ost在cut-over阶段最大的锁等待时间，当锁超时时，gh-ost的cut-over将重试。(默认值：3) |
| -database | string:数据库名称。 |
| -debug | debug模式。 |
| -default-retries | int:各种操作在panick前重试次数。(默认为60) |
| -discard-foreign-keys | 该参数针对一个有外键的表，在gh-ost创建ghost表时，并不会为ghost表创建外键。该参数很适合用于删除外键，除此之外，请谨慎使用。 |
| -dml-batch-size | int:在单个事务中应用DML事件的批量大小（范围1-100）（默认值为10） |
| -exact-rowcount | 准确统计表行数(使用select count(*)的方式)，得到更准确的预估时间。 |
| -execute | 实际执行alter&migrate表，默认为noop，不执行，仅仅做测试并退出，如果想要ALTER TABLE语句真正落实到数据库中去，需要明确指定-execute |
| -exponential-backoff-max-interval | 在执行具有指数补偿的各种操作时，两次尝试之间等待的最大秒数。 （默认为64） |
| -force-named-cut-over | 如果为true，则’unpostpone cut-over’交互式命令必须命名迁移的表 |
| -force-table-names | string:在临时表上使用的表名前缀 |
| -gcp | 在第一代Google Cloud Platform（GCP）上执行时，请将其设置为”true”。 |
| -heartbeat-interval-millis | gh-ost心跳频率值，默认为500 |
| -help | 帮助 |
| -hooks-hint | string:任意消息通过GH_OST_HOOKS_HINT注入到钩子 |
| -hooks-hint-owner | 通过GH_OST_HOOKS_HINT_OWNER将所有者的任意名称注入到钩子中 |
| -hooks-hint-token | 通过GH_OST_HOOKS_HINT_TOKEN将任意令牌注入到挂钩中 |
| -hooks-path | string:hook文件存放目录(默认为empty，即禁用hook)。hook会在这个目录下寻找符合约定命名的hook文件来执行。 |
| -host | string :MySQL IP/hostname |
| -initially-drop-ghost-table | gh-ost操作之前，检查并删除已经存在的ghost表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。 |
| -initially-drop-old-table | gh-ost操作之前，检查并删除已经存在的旧表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。 |
| -initially-drop-socket-file | gh-ost强制删除已经存在的socket文件。该参数不建议使用，可能会删除一个正在运行的gh-ost程序，导致DDL失败。 |
| -master-password | string:MySQL主密码 |
| -master-user | string:MysQL主账号 |
| -max-lag-millis | 主从复制最大延迟时间，当主从复制延迟时间超过该值后，gh-ost将采取节流(throttle)措施，默认值：1500s。 |
| -max-load | string:逗号分隔状态名称=阈值，如：’Threads_running=100,Threads_connected=500’. When status exceeds threshold, app throttles writes |
| -migrate-on-replica | gh-ost的数据迁移(migrate)运行在从库上，而不是主库上。 |
| -nice-ratio | float:每次chunk时间段的休眠时间，范围[0.0…100.0]。0：每个chunk时间段不休眠，即一个chunk接着一个chunk执行；1：每row-copy 1毫秒，则另外休眠1毫秒；0.7：每row-copy 10毫秒，则另外休眠7毫秒。 |
| -ok-to-drop-table | gh-ost操作结束后，删除旧表，默认状态是不删除旧表，会存在_tablename_del表。 |
| -panic-flag-file | string:当这个文件被创建，gh-ost将会立即退出。 |
| -password | string:MySQL密码 |
| -port | int:MySQL端口 |
| -postpone-cut-over-flag-file | string：当这个文件存在的时候，gh-ost的cut-over阶段将会被推迟，数据仍然在复制，直到该文件被删除。 |
| -quiet | 静默模式 |
| -replica-server-id | uint:gh-ost的server_id |
| -replication-lag-query | string:弃用 |
| -serve-socket-file | string：gh-ost的socket文件绝对路径。 |
| -serve-tcp-port | int:gh-ost使用端口，默认为关闭端口。 |
| -skip-foreign-key-checks | 确定你的表上没有外键时，设置为’true’，并且希望跳过gh-ost验证的时间-skip-renamed-columns ALTER |
| -skip-renamed-columns | ALTER：如果你修改一个列的名字(如change column)，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的。该参数告诉gh-ost跳该列的数据迁移，让gh-ost把重命名列作为无关紧要的列。该操作很危险，你会损失该列的所有值。 |
| -ssl | ssl连接MySQL |
| -ssl-allow-insecure | Skips verification of MySQL hosts’ certificate chain and host name. Requires –ssl |
| -ssl-ca | CA certificate in PEM format for TLS connections to MySQL hosts. Requires –ssl |
| -stack | 添加错误堆栈追踪。 |
| -switch-to-rbr | 让gh-ost自动将从库的binlog_format转换为ROW格式。 |
| -table | string:表名 |
| -test-on-replica | 在从库上测试gh-ost，包括在从库上数据迁移(migration)，数据迁移完成后stop slave，原表和ghost表立刻交换而后立刻交换回来。继续保持stop slave，使你可以对比两张表。 |
| -test-on-replica-skip-replica-stop | 当-test-on-replica执行时，该参数表示该过程中不用stop slave。 |
| -throttle-additional-flag-file | string:当该文件被创建后，gh-ost操作立即停止。该参数可以用在多个gh-ost同时操作的时候，创建一个文件，让所有的gh-ost操作停止，或者删除这个文件，让所有的gh-ost操作恢复。 |
| -throttle-control-replicas | string:列出所有需要被检查主从复制延迟的从库。 |
| -throttle-flag-file | string:当该文件被创建后，gh-ost操作立即停止。该参数适合控制单个gh-ost操作。-throttle-additional-flag-file string适合控制多个gh-ost操作。 |
| -throttle-http | when given, gh-ost checks given URL via HEAD request; any response code other than 200 (OK) causes throttling; make sure it has low latency response -throttle-query string |
| -throttle-query | string:节流查询。每秒钟执行一次。当返回值=0时不需要节流，当返回值>0时，需要执行节流操作。该查询会在数据迁移(migrated)服务器上操作，所以请确保该查询是轻量级的。 |
| -timestamp-old-table | 在旧表名中使用时间戳。 这会使旧表名称具有唯一且无冲突的交叉迁移 |
| -tungsten | 告诉gh-ost你正在运行的是一个tungsten-replication拓扑结构。 |
| -user | string:MYSQL用户 |
| -verbose | verbose |
| -version | 版本 |
案例
单实例DDL
相当于主库，需要开启–allow-on-master参数和ROW模式1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=512 \
--chunk-size=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--conf="/etc/my.cnf" \
--host="master:ip" \
--port=3306 \
--user="root" \
--password="xxx" \
--database="sysbench" \
--table="sbtest1" \
--verbose \
--alter="engine=innodb" \
--switch-to-rbr \
--allow-on-master \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--serve-socket-file=/tmp/gh-ost.socket.sock \
--execute
主从上DDL
有2个选择，一是按照单实例DDL直接在主上执行同步到从上，另一个连接到从库，在主库做迁移（只要保证从库的binlog为ROW即可，主库不需要保证）,以下去除了参数–allow-on-master1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=512 \
--chunk-size=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--conf="/etc/my.cnf" \
--host="slave:ip" \
--port=3306 \
--user="root" \
--password="xxx" \
--database="sysbench" \
--table="sbtest1" \
--verbose \
--alter="engine=innodb" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--serve-socket-file=/tmp/gh-ost.socket.sock \
--execute
注意：
- 在执行DDL中，从库会执行一次stop/start slave，要是确定从的binlog是ROW的话可以添加参数：–assume-rbr。如果从库的binlog不是ROW，可以用参数–switch-to-rbr来转换成ROW，此时需要注意的是执行完毕之后，binlog模式不会被转换成原来的值。–assume-rbr和–switch-to-rbr参数不能一起使用。
- –host需要连接从库的ip
非分区表改为分区表
1.将原表的id自增改为非自增，使用mysql online ddl，不锁表的方式1
alter table employee1 modify id int(11) NOT NULL,algorithm=inplace,lock=none;
2.删除分区字段内的空值，省略
3.删除原主键，添加联合主键1
alter table employee1 drop primary key,ADD PRIMARY KEY (create_time,id),algorithm=inplace,lock=none;
4.gh-ost重建分区1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=512 \
--chunk-size=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--conf="/etc/my.cnf" \
--host="localhost" \
--port=3306 \
--user="root" \
--password="xxx" \
--database="sysbench" \
--table="employee1" \
--verbose \
--alter=" partition by RANGE COLUMNS (create_time)(PARTITION P202003 VALUES LESS THAN ('2020-04-01'),PARTITION P202004 VALUES LESS THAN ('2020-05-01'),PARTITION P202005 VALUES LESS THAN ('2020-06-01'), PARTITION PMAX VALUES LESS THAN MAXVALUE ) " \
--switch-to-rbr \
--allow-on-master \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--serve-socket-file=/tmp/gh-ost.socket.sock \
--execute
终止、暂停、限速
- ① 表示文件终止运行：–panic-flag-file。
- 创建文件终止运行，例子中创建/tmp/ghost.panic.flag文件，终止正在运行的gh-ost，临时文件清理需要手动进行。
- ② 表示文件禁止cut-over进行，即禁止表名切换，数据复制正常进行。–postpone-cut-over-flag-file
- 创建文件延迟cut-over进行，即推迟切换操作。例子中创建/tmp/ghost.postpone.flag文件，gh-ost 会完成行复制，但并不会切换表，它会持续的将原表的数据更新操作同步到临时表中。(适用于夜里变更时间较长,担心自动cut-over失败,延迟cut-over,早上上班后再做切换)
- ③ 使用socket监听请求，操作者可以在命令运行后更改相应的参数。–serve-socket-file，–serve-tcp-port（默认关闭）
- 创建socket文件进行监听，通过接口进行参数调整，当执行操作的过程中发现负载、延迟上升了，不得不终止操作，重新配置参数，如 chunk-size，然后重新执行操作命令，可以通过scoket接口进行动态调整。如：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33暂停操作：
#暂停
echo throttle | socat - /tmp/gh-ost.socket.sock
#恢复
echo no-throttle | socat - /tmp/gh-ost.socket.sock
修改限速参数：
echo chunk-size=100 | socat - /tmp/gh-ost.socket.sock
echo max-lag-millis=200 | socat - /tmp/gh-ost.socket.sock
echo max-load=Thread_running=3 | socat - /tmp/gh-ost.socket.sock
以下为可操作的参数:
[root@VM_24_101_centos ~]# echo help | nc -U /tmp/gh-ost.socket.sock
available commands:
status # Print a detailed status message
sup # Print a short status message
coordinates # Print the currently inspected coordinates
chunk-size=<newsize> # Set a new chunk-size
dml-batch-size=<newsize> # Set a new dml-batch-size
nice-ratio=<ratio> # Set a new nice-ratio, immediate sleep after each row-copy operation, float (examples: 0 is aggressive, 0.7 adds 70% runtime, 1.0 doubles runtime, 2.0 triples runtime, ...)
critical-load=<load> # Set a new set of max-load thresholds
max-lag-millis=<max-lag> # Set a new replication lag threshold
replication-lag-query=<query> # Set a new query that determines replication lag (no quotes)
max-load=<load> # Set a new set of max-load thresholds
throttle-query=<query> # Set a new throttle-query (no quotes)
throttle-http=<URL> # Set a new throttle URL
throttle-control-replicas=<replicas> # Set a new comma delimited list of throttle control replicas
throttle # Force throttling
no-throttle # End forced throttling (other throttling may still apply)
unpostpone # Bail out a cut-over postpone; proceed to cut-over
panic # panic and quit without cleanup
help # This message
- use '?' (question mark) as argument to get info rather than set. e.g. "max-load=?" will just print out current max-load.
注意：1
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag #如果指定该参数，此文件执行时就会创建，默认也就延迟做cut-over，直到文件删除，执行cut-over
总结
gh-ost问世之后，由于其创建的设计模式，对生产的影响较小，已经成为了DDL的标准工具，广泛应用于生产。特别是公司内部有自建的运维平台，gh-ost多数会集成进平台，成为标配工具。即使在MySQL 8.0.12后官方的MySQL Online DDL支持秒级加字段，加索引，但是因为其限制，仅支持部分的场景可以做到秒级。并且，多数公司使用的MySQL版本还停留在5.7版本，目前来看，gh-ost是最优秀的MySQL DDL工具。
参考
https://segmentfault.com/a/1190000006158503
https://segmentfault.com/a/1190000020409138
https://segmentfault.com/a/1190000020417715
https://www.cnblogs.com/zping/p/8876148.html
https://www.cnblogs.com/zhoujinyi/p/9187421.html
https://www.cnblogs.com/mysql-dba/p/9901589.html
https://segmentfault.com/a/1190000007729135
https://blog.csdn.net/poxiaonie/article/details/75331916