TiDB Syncer不同表名库名同步且支持pt-osc改表

阅读量

TiDB Syncer不同表名库名同步且支持pt-osc改表

mysql端库名叫sysbench, 表名sbtest11
tidb端库名ptosc_sysbench,表名ptosc_sbtest11

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[[replicate-do-table]]
db-name = "ptosc_sysbench"
tbl-name = "ptosc_sbtest11"


[[replicate-do-table]]
db-name = "ptosc_sysbench"
tbl-name = "~.*_sbtest11_new"


[[replicate-do-table]]
db-name = "ptosc_sysbench"
tbl-name = "~.*_sbtest11_old"


[[route-rules]]
pattern-schema = "sysbench"
pattern-table = "sbtest11"
target-schema = "ptosc_sysbench"
target-table = "ptosc_sbtest11"

MySQL执行pt-osc

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
#pt-online-schema-change --ask-pass --check-interval=1 --no-check-replication-filters --no-check-alter --no-version-check --chunk-size 3 --recursion-method=none --max-load='Threads_running=200' --critical-load='Threads_running=500' --recurse=0 --no-drop-old-table --alter="drop index c" --print h=10.133.x.52,P=3308,u=fanboshi,D=sysbench,t=sbtest11,A=utf8 --execute
Enter MySQL password: 
No slaves found.  See --recursion-method if host node10-133-1-52 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `sysbench`.`sbtest11`...
Creating new table...
CREATE TABLE `sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
Created new table sysbench._sbtest11_new OK.
Altering new table...
ALTER TABLE `sysbench`.`_sbtest11_new` drop index c
Altered `sysbench`.`_sbtest11_new` OK.
2019-12-04T22:32:44 Creating triggers...
2019-12-04T22:32:44 Created triggers OK.
2019-12-04T22:32:44 Copying approximately 3 rows...
INSERT LOW_PRIORITY IGNORE INTO `sysbench`.`_sbtest11_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sysbench`.`sbtest11` LOCK IN SHARE MODE /*pt-online-schema-change 11937 copy table*/
2019-12-04T22:32:44 Copied rows OK.
2019-12-04T22:32:44 Analyzing new table...
2019-12-04T22:32:44 Swapping tables...
RENAME TABLE `sysbench`.`sbtest11` TO `sysbench`.`__sbtest11_old`, `sysbench`.`_sbtest11_new` TO `sysbench`.`sbtest11`
2019-12-04T22:32:44 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2019-12-04T22:32:44 Dropping triggers...
DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_del`
DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_upd`
DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_ins`
2019-12-04T22:32:44 Dropped triggers OK.
Successfully altered `sysbench`.`sbtest11`.

syncer日志

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
2019/12/04 22:32:05 syncer.go:754: [info] [query]CREATE TABLE `sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin [current pos](10523308-mysql-bin.000023, 557640598) [next pos](10523308-mysql-bin.000023, 557641108) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755555 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755556
2019/12/04 22:32:05 syncer.go:795: [info] [ddl][schema]sysbench [start]USE `ptosc_sysbench`; CREATE TABLE `ptosc_sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
2019/12/04 22:32:05 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557641108 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755556
2019/12/04 22:32:05 syncer.go:803: [info] [ddl][end]USE `ptosc_sysbench`; CREATE TABLE `ptosc_sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
2019/12/04 22:32:05 ddl.go:110: [warning] will split alter table statement: ALTER TABLE `sysbench`.`_sbtest11_new` drop index c
2019/12/04 22:32:05 ast.go:521: [info] spec &{node:{text:} IfExists:false IfNotExists:false NoWriteToBinlog:false OnAllPartitions:false Tp:6 Name:c Constraint:<nil> Options:[] OrderByList:[] NewTable:<nil> NewColumns:[] NewConstraints:[] OldColumnName:<nil> NewColumnName:<nil> Position:<nil> LockType: Algorithm:DEFAULT Comment: FromKey: ToKey: Partition:<nil> PartitionNames:[] PartDefinitions:[] WithValidation:false Num:0 Visibility:0}
2019/12/04 22:32:05 ddl.go:114: [warning] splitted alter table statement: [ALTER TABLE `sysbench`.`_sbtest11_new` DROP INDEX `c`]
2019/12/04 22:32:05 syncer.go:754: [info] [query]ALTER TABLE `sysbench`.`_sbtest11_new` drop index c [current pos](10523308-mysql-bin.000023, 557641108) [next pos](10523308-mysql-bin.000023, 557641303) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755556 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755557
2019/12/04 22:32:05 syncer.go:795: [info] [ddl][schema]sysbench [start]USE `ptosc_sysbench`; ALTER TABLE `ptosc_sysbench`.`_sbtest11_new` DROP INDEX `c`;
2019/12/04 22:32:06 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557641303 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755557
2019/12/04 22:32:06 syncer.go:803: [info] [ddl][end]USE `ptosc_sysbench`; ALTER TABLE `ptosc_sysbench`.`_sbtest11_new` DROP INDEX `c`;
2019/12/04 22:32:06 syncer.go:754: [info] [query]DROP TABLE IF EXISTS `_sbtest11_new` /* generated by server */ [current pos](10523308-mysql-bin.000023, 557641303) [next pos](10523308-mysql-bin.000023, 557641509) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755557 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755558
2019/12/04 22:32:06 syncer.go:795: [info] [ddl][schema]sysbench [start]USE `ptosc_sysbench`; DROP TABLE IF EXISTS `ptosc_sysbench`.`_sbtest11_new`;
2019/12/04 22:32:07 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557641509 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755558
2019/12/04 22:32:07 syncer.go:803: [info] [ddl][end]USE `ptosc_sysbench`; DROP TABLE IF EXISTS `ptosc_sysbench`.`_sbtest11_new`;
2019/12/04 22:32:30 syncer.go:955: [info] [syncer]total events = 4,  tps = 0.100000, master-binlog = (10523308-mysql-bin.000023, 557641509), master-binlog-gtid=a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755558, syncer-binlog = (10523308-mysql-bin.000023, 557641509), syncer-binlog-gtid = a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755558
2019/12/04 22:32:44 syncer.go:754: [info] [query]CREATE TABLE `sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin [current pos](10523308-mysql-bin.000023, 557641509) [next pos](10523308-mysql-bin.000023, 557642019) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755558 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755559
2019/12/04 22:32:44 syncer.go:795: [info] [ddl][schema]sysbench [start]USE `ptosc_sysbench`; CREATE TABLE `ptosc_sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
2019/12/04 22:32:46 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557642019 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755559
2019/12/04 22:32:46 syncer.go:803: [info] [ddl][end]USE `ptosc_sysbench`; CREATE TABLE `ptosc_sysbench`.`_sbtest11_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
2019/12/04 22:32:46 ddl.go:110: [warning] will split alter table statement: ALTER TABLE `sysbench`.`_sbtest11_new` drop index c
2019/12/04 22:32:46 ast.go:521: [info] spec &{node:{text:} IfExists:false IfNotExists:false NoWriteToBinlog:false OnAllPartitions:false Tp:6 Name:c Constraint:<nil> Options:[] OrderByList:[] NewTable:<nil> NewColumns:[] NewConstraints:[] OldColumnName:<nil> NewColumnName:<nil> Position:<nil> LockType: Algorithm:DEFAULT Comment: FromKey: ToKey: Partition:<nil> PartitionNames:[] PartDefinitions:[] WithValidation:false Num:0 Visibility:0}
2019/12/04 22:32:46 ddl.go:114: [warning] splitted alter table statement: [ALTER TABLE `sysbench`.`_sbtest11_new` DROP INDEX `c`]
2019/12/04 22:32:46 syncer.go:754: [info] [query]ALTER TABLE `sysbench`.`_sbtest11_new` drop index c [current pos](10523308-mysql-bin.000023, 557642019) [next pos](10523308-mysql-bin.000023, 557642214) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755559 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755560
2019/12/04 22:32:46 syncer.go:795: [info] [ddl][schema]sysbench [start]USE `ptosc_sysbench`; ALTER TABLE `ptosc_sysbench`.`_sbtest11_new` DROP INDEX `c`;
2019/12/04 22:32:47 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557642214 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755560
2019/12/04 22:32:47 syncer.go:803: [info] [ddl][end]USE `ptosc_sysbench`; ALTER TABLE `ptosc_sysbench`.`_sbtest11_new` DROP INDEX `c`;
2019/12/04 22:32:47 ddl.go:64: [error] 
encountered incompatible DDL in TiDB: CREATE DEFINER=`fanboshi`@`%` TRIGGER `pt_osc_sysbench_sbtest11_del` AFTER DELETE ON `sysbench`.`sbtest11` FOR EACH ROW DELETE IGNORE FROM `sysbench`.`_sbtest11_new` WHERE `sysbench`.`_sbtest11_new`.`id` <=> OLD.`id`
please confirm your DDL statement is correct and needed.
for TiDB compatible DDL, please see the docs:
  English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md
  Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md
if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/04 22:32:47 syncer.go:745: [warning] [skip query event][schema]sysbench [sql]CREATE DEFINER=`fanboshi`@`%` TRIGGER `pt_osc_sysbench_sbtest11_del` AFTER DELETE ON `sysbench`.`sbtest11` FOR EACH ROW DELETE IGNORE FROM `sysbench`.`_sbtest11_new` WHERE `sysbench`.`_sbtest11_new`.`id` <=> OLD.`id` [current pos](10523308-mysql-bin.000023, 557642214) [next pos](10523308-mysql-bin.000023, 557642590) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755560 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755561
2019/12/04 22:32:47 ddl.go:64: [error] 
encountered incompatible DDL in TiDB: CREATE DEFINER=`fanboshi`@`%` TRIGGER `pt_osc_sysbench_sbtest11_upd` AFTER UPDATE ON `sysbench`.`sbtest11` FOR EACH ROW BEGIN DELETE IGNORE FROM `sysbench`.`_sbtest11_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `sysbench`.`_sbtest11_new`.`id` <=> OLD.`id`;REPLACE INTO `sysbench`.`_sbtest11_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END
please confirm your DDL statement is correct and needed.
for TiDB compatible DDL, please see the docs:
  English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md
  Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md
if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/04 22:32:47 syncer.go:745: [warning] [skip query event][schema]sysbench [sql]CREATE DEFINER=`fanboshi`@`%` TRIGGER `pt_osc_sysbench_sbtest11_upd` AFTER UPDATE ON `sysbench`.`sbtest11` FOR EACH ROW BEGIN DELETE IGNORE FROM `sysbench`.`_sbtest11_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `sysbench`.`_sbtest11_new`.`id` <=> OLD.`id`;REPLACE INTO `sysbench`.`_sbtest11_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END [current pos](10523308-mysql-bin.000023, 557642214) [next pos](10523308-mysql-bin.000023, 557643116) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755560 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755562
2019/12/04 22:32:47 ddl.go:64: [error] 
encountered incompatible DDL in TiDB: CREATE DEFINER=`fanboshi`@`%` TRIGGER `pt_osc_sysbench_sbtest11_ins` AFTER INSERT ON `sysbench`.`sbtest11` FOR EACH ROW REPLACE INTO `sysbench`.`_sbtest11_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
please confirm your DDL statement is correct and needed.
for TiDB compatible DDL, please see the docs:
  English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md
  Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md
if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/04 22:32:47 syncer.go:745: [warning] [skip query event][schema]sysbench [sql]CREATE DEFINER=`fanboshi`@`%` TRIGGER `pt_osc_sysbench_sbtest11_ins` AFTER INSERT ON `sysbench`.`sbtest11` FOR EACH ROW REPLACE INTO `sysbench`.`_sbtest11_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) [current pos](10523308-mysql-bin.000023, 557642214) [next pos](10523308-mysql-bin.000023, 557643506) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755560 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755563
2019/12/04 22:32:47 syncer.go:754: [info] [query]RENAME TABLE `sysbench`.`sbtest11` TO `sysbench`.`__sbtest11_old`, `sysbench`.`_sbtest11_new` TO `sysbench`.`sbtest11` [current pos](10523308-mysql-bin.000023, 557644557) [next pos](10523308-mysql-bin.000023, 557645022) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755564 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566
2019/12/04 22:32:47 syncer.go:795: [info] [ddl][schema]sysbench [start]RENAME TABLE `ptosc_sysbench`.`ptosc_sbtest11` TO `ptosc_sysbench`.`__sbtest11_old`
2019/12/04 22:32:48 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557644557 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755564
2019/12/04 22:32:48 syncer.go:803: [info] [ddl][end]RENAME TABLE `ptosc_sysbench`.`ptosc_sbtest11` TO `ptosc_sysbench`.`__sbtest11_old`
2019/12/04 22:32:48 syncer.go:795: [info] [ddl][schema]sysbench [start]RENAME TABLE `ptosc_sysbench`.`_sbtest11_new` TO `ptosc_sysbench`.`ptosc_sbtest11`
2019/12/04 22:32:49 meta.go:135: [info] save position to file, binlog-name:10523308-mysql-bin.000023 binlog-pos:557645022 binlog-gtid:a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566
2019/12/04 22:32:49 syncer.go:803: [info] [ddl][end]RENAME TABLE `ptosc_sysbench`.`_sbtest11_new` TO `ptosc_sysbench`.`ptosc_sbtest11`
2019/12/04 22:32:49 ddl.go:64: [error] 
encountered incompatible DDL in TiDB: DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_del`
please confirm your DDL statement is correct and needed.
for TiDB compatible DDL, please see the docs:
  English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md
  Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md
if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/04 22:32:49 syncer.go:745: [warning] [skip query event][schema]sysbench [sql]DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_del` [current pos](10523308-mysql-bin.000023, 557645022) [next pos](10523308-mysql-bin.000023, 557645230) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755567
2019/12/04 22:32:49 ddl.go:64: [error] 
encountered incompatible DDL in TiDB: DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_upd`
please confirm your DDL statement is correct and needed.
for TiDB compatible DDL, please see the docs:
  English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md
  Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md
if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/04 22:32:49 syncer.go:745: [warning] [skip query event][schema]sysbench [sql]DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_upd` [current pos](10523308-mysql-bin.000023, 557645022) [next pos](10523308-mysql-bin.000023, 557645438) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755568
2019/12/04 22:32:49 ddl.go:64: [error] 
encountered incompatible DDL in TiDB: DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_ins`
please confirm your DDL statement is correct and needed.
for TiDB compatible DDL, please see the docs:
  English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md
  Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md
if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/04 22:32:49 syncer.go:745: [warning] [skip query event][schema]sysbench [sql]DROP TRIGGER IF EXISTS `sysbench`.`pt_osc_sysbench_sbtest11_ins` [current pos](10523308-mysql-bin.000023, 557645022) [next pos](10523308-mysql-bin.000023, 557645646) [current gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566 [next gtid set]a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755569
2019/12/04 22:33:00 syncer.go:955: [info] [syncer]total events = 7,  tps = 0.233333, master-binlog = (10523308-mysql-bin.000023, 557645646), master-binlog-gtid=a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755569, syncer-binlog = (10523308-mysql-bin.000023, 557645022), syncer-binlog-gtid = a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566
2019/12/04 22:33:30 syncer.go:955: [info] [syncer]total events = 7,  tps = 0.000000, master-binlog = (10523308-mysql-bin.000023, 557645646), master-binlog-gtid=a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755569, syncer-binlog = (10523308-mysql-bin.000023, 557645022), syncer-binlog-gtid = a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566
2019/12/04 22:34:00 syncer.go:955: [info] [syncer]total events = 7,  tps = 0.000000, master-binlog = (10523308-mysql-bin.000023, 557645646), master-binlog-gtid=a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755569, syncer-binlog = (10523308-mysql-bin.000023, 557645022), syncer-binlog-gtid = a5c0e0de-19bb-5df7-8be2-29248264cc14:1-755566
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
root@10.132.2.164 22:28:34 [ptosc_sysbench]> show tables;
+--------------------------+
| Tables_in_ptosc_sysbench |
+--------------------------+
| __sbtest11_old           |
| _sbtest11_old            |
| ptosc_sbtest1            |
| ptosc_sbtest10           |
| ptosc_sbtest11           |
| ptosc_sbtest12           |
| ptosc_sbtest13           |
| ptosc_sbtest2            |
| ptosc_sbtest3            |
| ptosc_sbtest4            |
| ptosc_sbtest5            |
| ptosc_sbtest6            |
| ptosc_sbtest7            |
| ptosc_sbtest8            |
| ptosc_sbtest9            |
+--------------------------+
15 rows in set (0.01 sec)

root@10.132.2.164 22:32:58 [ptosc_sysbench]> show create table ptosc_sbtest11\G
*************************** 1. row ***************************
       Table: ptosc_sbtest11
Create Table: CREATE TABLE `ptosc_sbtest11` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30004
1 row in set (0.00 sec)

root@10.132.2.164 22:33:07 [ptosc_sysbench]> show create table __sbtest11_old\G
*************************** 1. row ***************************
       Table: __sbtest11_old
Create Table: CREATE TABLE `__sbtest11_old` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30004
1 row in set (0.00 sec)

root@10.132.2.164 22:33:12 [ptosc_sysbench]> select  * from ptosc_sbtest11;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 250731 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
|  2 | 251240 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 |
|  3 | 249472 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

root@10.132.2.164 22:33:19 [ptosc_sysbench]> select * from __sbtest11_old;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 250731 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
|  2 | 251240 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 |
|  3 | 249472 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL执行

1
2
3
4
5
6
7
8
9
10
11
12
root@localhost 22:31:10 [sysbench]> select * from sbtest11;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 250731 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
|  2 | 251240 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 |
|  3 | 249472 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

root@localhost 22:31:14 [sysbench]> delete from sbtest11 where id=2;
Query OK, 1 row affected (0.00 sec)

TIDB查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@10.132.2.164 22:33:19 [ptosc_sysbench]> select * from __sbtest11_old;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 250731 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
|  2 | 251240 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 |
|  3 | 249472 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

root@10.132.2.164 22:33:24 [ptosc_sysbench]> select  * from ptosc_sbtest11;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 250731 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
|  3 | 249472 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

如果只是配置

1
2
3
4
5
6
7
8
9
10
[[replicate-do-table]]
db-name = "ptosc_sysbench"
tbl-name = "ptosc_sbtest11"


[[route-rules]]
pattern-schema = "sysbench"
pattern-table = "sbtest11"
target-schema = "ptosc_sysbench"
target-table = "ptosc_sbtest11"

会报错

1
2
3
4
5
6
7
8
9
10
11
2019/12/03 11:39:13 syncer.go:754: [info] [query]RENAME TABLE `sale_risk_log`.`t_risk_third_request_log` TO `sale_risk_log`.`_t_risk_third_request_log_old`, `sale_risk_log`.`_t_risk_third_request_log_new` TO `sale_risk_log`.`t_risk_third_request_log` [current pos](0022263310-mysql-bin.003273, 481842042) [next pos](0022263310-mysql-bin.003273, 481842397) [current gtid set]2fb23bcc-e5b3-5f84-88ce-15271019b97d:1-2550946489,38f8425e-9182-5934-b32a-7e4317fe4a04:270797037-273174391:5392264317-5392487512,ce9be252-2b71-11e6-b8f4-00212844f856:909572082-909633988:913857848-913889001 [next gtid set]ce9be252-2b71-11e6-b8f4-00212844f856:909572082-909633988:913857848-913889001,2fb23bcc-e5b3-5f84-88ce-15271019b97d:1-2550946490,38f8425e-9182-5934-b32a-7e4317fe4a04:270797037-273174391:5392264317-5392487512
2019/12/03 11:39:13 syncer.go:795: [info] [ddl][schema]sale_risk_log [start]RENAME TABLE `tidb_sale_risk_log`.`tidb_t_risk_third_request_log` TO `tidb_sale_risk_log`.`_t_risk_third_request_log_old`
2019/12/03 11:39:13 db.go:143: [warning] [exec][sql]RENAME TABLE `tidb_sale_risk_log`.`tidb_t_risk_third_request_log` TO `tidb_sale_risk_log`.`_t_risk_third_request_log_old`[args][][error]Error 1050: Table 'tidb_sale_risk_log._t_risk_third_request_log_old' already exists
2019/12/03 11:39:13 db.go:115: [error] [exec][sql][RENAME TABLE `tidb_sale_risk_log`.`tidb_t_risk_third_request_log` TO `tidb_sale_risk_log`.`_t_risk_third_request_log_old`][args][[]][error]Error 1050: Table 'tidb_sale_risk_log._t_risk_third_request_log_old' already exists
2019/12/03 11:39:13 syncer.go:491: [warning] [ignore ddl error][sql]RENAME TABLE `tidb_sale_risk_log`.`tidb_t_risk_third_request_log` TO `tidb_sale_risk_log`.`_t_risk_third_request_log_old`[args][][error]Error 1050: Table 'tidb_sale_risk_log._t_risk_third_request_log_old' already exists
2019/12/03 11:39:13 meta.go:135: [info] save position to file, binlog-name:0022263310-mysql-bin.003273 binlog-pos:481842042 binlog-gtid:ce9be252-2b71-11e6-b8f4-00212844f856:909572082-909633988:913857848-913889001,2fb23bcc-e5b3-5f84-88ce-15271019b97d:1-2550946489,38f8425e-9182-5934-b32a-7e4317fe4a04:270797037-273174391:5392264317-5392487512
2019/12/03 11:39:13 syncer.go:803: [info] [ddl][end]RENAME TABLE `tidb_sale_risk_log`.`tidb_t_risk_third_request_log` TO `tidb_sale_risk_log`.`_t_risk_third_request_log_old`
2019/12/03 11:39:13 syncer.go:795: [info] [ddl][schema]sale_risk_log [start]RENAME TABLE `tidb_sale_risk_log`.`_t_risk_third_request_log_new` TO `tidb_sale_risk_log`.`tidb_t_risk_third_request_log`
2019/12/03 11:39:13 db.go:143: [warning] [exec][sql]RENAME TABLE `tidb_sale_risk_log`.`_t_risk_third_request_log_new` TO `tidb_sale_risk_log`.`tidb_t_risk_third_request_log`[args][][error]Error 1017: Can't find file: './tidb_sale_risk_log/_t_risk_third_request_log_new.frm'
2019/12/03 11:39:13 db.go:115: [error] [exec][sql][RENAME TABLE `tidb_sale_risk_log`.`_t_risk_third_request_log_new` TO `tidb_sale_risk_log`.`tidb_t_risk_third_request_log`][args][[]][error]Error 1017: Can't find file: './tidb_sale_risk_log/_t_risk_third_request_log_new.frm'
2019/12/03 11:39:13 syncer.go:489: [fatal] [error query event][error type]execution error [sql]RENAME TABLE `tidb_sale_risk_log`.`_t_risk_third_request_log_new` TO `tidb_sale_risk_log`.`tidb_t_risk_third_request_log` [schema]sale_risk_log [table]_t_risk_third_request_log_new [current pos](0022263310-mysql-bin.003273, 481842042) [next pos](0022263310-mysql-bin.003273, 481842397) [current gtid set]2fb23bcc-e5b3-5f84-88ce-15271019b97d:1-2550946489,38f8425e-9182-5934-b32a-7e4317fe4a04:270797037-273174391:5392264317-5392487512,ce9be252-2b71-11e6-b8f4-00212844f856:909572082-909633988:913857848-913889001 [next gtid set]ce9be252-2b71-11e6-b8f4-00212844f856:909572082-909633988:913857848-913889001,2fb23bcc-e5b3-5f84-88ce-15271019b97d:1-2550946490,38f8425e-9182-5934-b32a-7e4317fe4a04:270797037-273174391:5392264317-5392487512 [error message]Error 1017: Can't find file: './tidb_sale_risk_log/_t_risk_third_request_log_new.frm'

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :