I created a database called dictionary as my master DB which has 2 tables(state and employees). In addition to the tables, I also added few functions, triggers, stored procedures, indexes. I am hoping to replicate everything to a slave. On debezium startup, it creates a snapshot of the 2 tables in the database. It copies both the tables and I can see any indexes created on these tables. But it excludes the triggers, stored procedures and functions. Surprisingly, any functions or triggers that get added after debezium start up I can see the change events captured and the data replicated. Any help on this is greatly appreciated.
The connector config is:
io.debezium.config.Configuration.create()
.with("name", "my-sql-connector")
.with("connector.class", "io.debezium.connector.mysql.MySqlConnector")
.with("offset.storage","com.cdc.util.CustomFileOffsetBackingStore")
.with("offset.storage.file.filename", offsetStorageFile.getAbsolutePath())
.with("time.precision.mode", "connect")
.with("offset.flush.interval.ms", 0)
.with("database.hostname", masterDbHost)
.with("database.port", masterDbPort)
.with("database.user", masterDbUsername)
.with("database.password", masterDbPassword)
.with("topic.prefix", "dictionary-db")
.with("include.schema.changes", "true")
.with("database.server.id", 2)
.with("database.connectionTimeZone", "America/New_York")
.with("server.id", 85744)
.with("server.name", "dictionary-db1")
.with("schema.history.internal", "io.debezium.storage.file.history.FileSchemaHistory")
.with("schema.history.internal.file.filename", schemaHistoryFile)
.build();
Connector logs:
2023-04-29 22:25:28.299 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 1 - Preparing
2023-04-29 22:25:28.300 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 2 - Determining captured tables
2023-04-29 22:25:28.300 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Read list of available databases
2023-04-29 22:25:28.302 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : list of available databases is: [dictionary, information_schema, mysql, performance_schema, sys]
2023-04-29 22:25:28.302 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Read list of available tables in each database
2023-04-29 22:25:28.313 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : snapshot continuing with database(s): [dictionary, information_schema, performance_schema, mysql, sys]
2023-04-29 22:25:28.314 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Adding table dictionary.employees to the list of capture schema tables
2023-04-29 22:25:28.314 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Adding table dictionary.state to the list of capture schema tables
2023-04-29 22:25:28.314 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 3 - Locking captured tables [dictionary.employees, dictionary.state]
2023-04-29 22:25:28.315 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Flush and obtain global read lock to prevent writes to database
2023-04-29 22:25:28.317 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 4 - Determining snapshot offset
2023-04-29 22:25:28.320 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Read binlog position of MySQL primary server
2023-04-29 22:25:28.321 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : using binlog 'binlog.000005' at position '167491' and gtid ''
2023-04-29 22:25:28.321 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 5 - Reading structure of captured tables
2023-04-29 22:25:28.321 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : All eligible tables schema should be captured, capturing: [dictionary.employees, dictionary.state]
2023-04-29 22:25:28.544 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Reading structure of database 'dictionary'
2023-04-29 22:25:28.583 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 6 - Persisting schema history
2023-04-29 22:25:28.600 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Releasing global read lock to enable MySQL writes
2023-04-29 22:25:28.600 INFO 68241 --- [rce-coordinator] i.d.c.m.MySqlSnapshotChangeEventSource : Writes to MySQL tables prevented for a total of 00:00:00.283
2023-04-29 22:25:28.600 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshot step 7 - Snapshotting data
2023-04-29 22:25:28.601 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Snapshotting contents of 2 tables while still in transaction
2023-04-29 22:25:28.601 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Exporting data from table 'dictionary.employees' (1 of 2 tables)
2023-04-29 22:25:28.601 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : For table 'dictionary.employees' using select statement: 'SELECT `employee_id`, `first_name`, `last_name`, `date_of_birth`, `phone_number`, `junk` FROM `dictionary`.`employees`'
2023-04-29 22:25:28.666 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Finished exporting 1004 records for table 'dictionary.employees'; total duration '00:00:00.065'
2023-04-29 22:25:28.666 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Exporting data from table 'dictionary.state' (2 of 2 tables)
2023-04-29 22:25:28.666 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : For table 'dictionary.state' using select statement: 'SELECT `id`, `name`, `description`, `capital`, `area_code` FROM `dictionary`.`state`'
2023-04-29 22:25:28.671 INFO 68241 --- [rce-coordinator] .d.r.RelationalSnapshotChangeEventSource : Finished exporting 3 records for table 'dictionary.state'; total duration '00:00:00.005'
2023-04-29 22:25:28.672 INFO 68241 --- [rce-coordinator] .d.p.s.AbstractSnapshotChangeEventSource : Snapshot - Final stage
2023-04-29 22:25:28.672 INFO 68241 --- [rce-coordinator] .d.p.s.AbstractSnapshotChangeEventSource : Snapshot completed