欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

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

程序员文章站 2022-03-06 21:24:46
...

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

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

[[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

#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日志

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

[email protected] 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)

[email protected] 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)

[email protected] 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)

[email protected] 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)

[email protected] 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执行

[email protected] 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)

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

TIDB查看

[email protected] 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)

[email protected] 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)

如果只是配置

[[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"

会报错

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'
相关标签： TiDB

上一篇： 深度学习lstm防止过拟合之dropout

下一篇： TiDB 学习第 3 天：TIDB 正式环境 TiDB-Ansible 部署

推荐阅读