MySQL Heartbeats

Setup MySQL heartbeats for low and intermittent traffic databases

In low-traffic or intermittent databases, the log can become stale due to inactivity, which may cause the Connector to miss change events. Heartbeats (enabled by default) help mitigate this.

To mitigate this further, a heartbeat table can be created in the source database which the heartbeat process — running within the Connector — periodically updates. This generates a change event in MySQL preventing the logs from becoming stale.

Setup Heartbeats

-- Create the streamkap schema
CREATE SCHEMA IF NOT EXISTS streamkap;

-- Switch to the streamkap schema
USE streamkap;

-- Create the heartbeat table with id, text, and last_update fields
CREATE TABLE streamkap_heartbeat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    text TEXT,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grant permission to the Streamkap user
GRANT SELECT, INSERT, UPDATE ON streamkap.streamkap_heartbeat TO STREAMKAP_USER;

-- Insert the first row into the heartbeat table
INSERT INTO streamkap_heartbeat (text) VALUES ('test_heartbeat');

Verify

  • After a few minutes, look for entries with a recent timestamp in the heartbeat table to verify it's working