MySQL Heartbeats

Setup MySQL heartbeats for low volume databases

For low volume MySQL sources, it is possible that the log becomes stale due to a lack of change events. As a result, events may not be captured by the Connector.

To address this, the heartbeat process involves writing an insert or update to a heartbeat table every 1 second to generate a change event.

📘

Table must exist in a schema called streamkap

Setup MySQL 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 ON UPDATE CURRENT_TIMESTAMP
);

-- Grant permission to the Streamkap user
GRANT 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

  • Look for entries in the heartbeat table to verify this is working with an updated timestamp

Updated about 2 months ago