Real time sync

[hiepabh]

@bakwc it's me again

The yaml file

`mysql: host: '127.0.0.1' port: 3306 user: 'ch_rep' password: '123'

clickhouse: host: 'localhost' port: 8123 user: 'default' password: 'clickhouse'

binlog_replicator: data_dir: '/var/log/clickhouse-sync/' records_per_file: 100000

databases: 'regina' tables: ['employees', 'employee_positions', 'overtimes', 'employee_overtime']`

auto sync log

[dbrepl regina 2024-11-08 08:48:20,889 INFO] stats: {"last_transaction": ["mysql_bin.000105", 4364050], "events_count": 2, "insert_events_count": 1, "insert_records_count": 1, "erase_events_count": 0, "erase_records_count": 0, "no_events_count": 60, "cpu_load": 0.0008068166532034065} [binlogrepl 2024-11-08 08:49:00,839 INFO] last transaction id: ('mysql_bin.000105', 4369181), processed events: 67032 [dbrepl regina 2024-11-08 08:49:21,112 INFO] stats: {"last_transaction": ["mysql_bin.000105", 4372317], "events_count": 6, "insert_events_count": 3, "insert_records_count": 3, "erase_events_count": 0, "erase_records_count": 0, "no_events_count": 60, "cpu_load": 0.001337385818877352} [binlogrepl 2024-11-08 08:50:01,678 INFO] last transaction id: ('mysql_bin.000105', 4375515), processed events: 67160 [dbrepl regina 2024-11-08 08:50:21,383 INFO] stats: {"last_transaction": ["mysql_bin.000105", 4377415], "events_count": 4, "insert_events_count": 1, "insert_records_count": 1, "erase_events_count": 1, "erase_records_count": 1, "no_events_count": 60, "cpu_load": 0.0009487625410594661} [binlogrepl 2024-11-08 08:51:02,502 INFO] last transaction id: ('mysql_bin.000105', 4377446), processed events: 67284 [dbrepl regina 2024-11-08 08:51:21,464 INFO] stats: {"last_transaction": null, "events_count": 0, "insert_events_count": 0, "insert_records_count": 0, "erase_events_count": 0, "erase_records_count": 0, "no_events_count": 60, "cpu_load": 0.0004835192067362099} [binlogrepl 2024-11-08 08:52:03,334 INFO] last transaction id: ('mysql_bin.000105', 4381243), processed events: 67412 [dbrepl regina 2024-11-08 08:52:21,606 INFO] stats: {"last_transaction": ["mysql_bin.000105", 4381407], "events_count": 4, "insert_events_count": 1, "insert_records_count": 1, "erase_events_count": 0, "erase_records_count": 0, "no_events_count": 60, "cpu_load": 0.0008973392611787778} [binlogrepl 2024-11-08 08:53:04,167 INFO] last transaction id: ('mysql_bin.000105', 4381601), processed events: 67536

It doesnt sync

Am i missing something?

[bakwc]

According to logs you provided the data should be synced (insert_records_count is positive). I will prepare a verbose logs what's being inserted and we could check what's going on.

Also, could you please provide more information about your screenshot. 1) What is database is used for the first query? 2) What is regina_source? Is it a mysql based connection?

[bakwc]

I added debug logs in 0.0.29 - it will dump all binary log events and all recrods being inserted into clickhouse. Please:

• Update to 0.0.29
• Set log_level: 'debug' in config.yaml (see README.md for example)
• Run realtime replicator (you may continue from previous state)
• Insert record into mysql
• Wait a few minutes (just in case)
• Attach all logs from the moment you insert record into mysql

[hiepabh]

1. First data is what i sync from MySQL through the ur script
2. Correct

I will update and keep you post later on

[bakwc]

BTW logs are written to file on disk too. You could check binlog_directory/binlog_replicator.log and binlog_directory/DATABASE_NAME/db_replicator.log and attach this files too. We need to check following:

• event present / missing in binlog replicator log (that means it was extracted from mysql binary log)
• event present / missing in db replicator log (that means it should be inserted in clickhouse)

[hiepabh]

clickhouse-sync.zip See if it works for you

[bakwc]

Unfortunately it's missing debug logs.. could you double check that you updated to 0.0.29, enabled log_level: 'debug' restarted process and try to insert again. Also could you please check if it appears in a few minutes later? (may be there is some delay..)

[hiepabh]

I delete the log and start sync_all again, the attachment is newest log

See if it help

1.zip

[bakwc]

1. It seems like you need to configure MySQL server properly so it will write binary log in ROW format. Did you apply this settings to MySQL server?

[mysqld]
# ... other settings ...
gtid_mode = on
enforce_gtid_consistency = 1
default_authentication_plugin = mysql_native_password
binlog_format = ROW

This one is the most important: binlog_format = ROW

If you using RDS you should apply following:

binlog_format                       ROW
binlog_expire_logs_seconds          86400

2. Did you tried to insert or update anything after you enabled this debug logs?

[hiepabh]

The setting is run as default from when i test first sync with you

Not yet to insert or update anything after include debug on

[bakwc]

Settings seems correct. Could you please insert or update anything and attach logs after you do it with debug enabled.