The breadcrumbs filter records
regular 'breadcrumb' points into a MySQL table for systems that do not
have global transaction IDs. This can be useful if recovery needs to be
made to a specific point. The example also shows how metadata information
for a given event can be updated based on the information from a table.
| Pre-configured filter name |
ansiquotes
| ||
| JavaScript Filter File |
tungsten-replicator/support/filters-javascript/breadcrumbs.js
| ||
| Property prefix |
replicator.filter.breadcrumbs
| ||
| Stage compatibility |
binlog-to-q
| ||
| tpm Option compatibility |
--svc-extractor-filters
| ||
| Data compatibility | Any event | ||
| Parameters | |||
| Parameter | Type | Default | Description |
server_id
|
numeric
| (none) | MySQL server ID of the current host |
To use the filter:
A table is created and populated with one more rows on the Target server. For example:
CREATE TABLE `tungsten_svc1`.`breadcrumbs` ( `id` int(11) NOT NULL PRIMARY KEY, `counter` int(11) DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB; INSERT INTO tungsten_svc1.breadcrumbs(id, counter) values(@@server_id, 1);
Now set an event to update the table regularly. For example, within MySQL an event can be created for this purpose:
CREATE EVENT breadcrumbs_refresh
ON SCHEDULE EVERY 5 SECOND
DO
UPDATE tungsten_svc1.breadcrumbs SET counter=counter+1;
SET GLOBAL event_scheduler = ON;The filter will extract the value of the counter each time it sees to the table, and then mark each transaction with a particular server ID with the counter value plus an offset. For convenience we assume row replication is enabled.
If you need to failover to another server that has different logs, you can figure out the restart point by looking in the THL for the breadcrumb metadata on the last transaction. Use this to search the binary logs on the new server for the correct restart point.
The filter itself work in two stages, and operates because the JavaScript instance is persistent as long as the Replicator is running. This means that data extracted during replication stays in memory and can be applied to later transactions. Hence the breadcrumb ID and offset information can be identified and used on each call to the filter function.
The first part of the filter event identifies the breadcrumb table and extracts the identified breadcrumb counter:
if (table.compareToIgnoreCase("breadcrumbs") == 0)
{
columnValues = oneRowChange.getColumnValues();
for (row = 0; row < columnValues.size(); row++)
{
values = columnValues.get(row);
server_id_value = values.get(0);
if (server_id == null || server_id == server_id_value.getValue())
{
counter_value = values.get(1);
breadcrumb_counter = counter_value.getValue();
breadcrumb_offset = 0;
}
}
}The second part updates the event metadata using the extracted breadcrumb information:
topLevelEvent = event.getDBMSEvent();
if (topLevelEvent != null)
{
xact_server_id = topLevelEvent.getMetadataOptionValue("mysql_server_id");
if (server_id == xact_server_id)
{
topLevelEvent.setMetaDataOption("breadcrumb_counter", breadcrumb_counter);
topLevelEvent.setMetaDataOption("breadcrumb_offset", breadcrumb_offset);
}
}To calculate the offset (i.e. the number of events since the last breadcrumb value was extracted), the filter determines if the event was the last fragment processed, and updates the offset counter:
if (event.getLastFrag())
{
breadcrumb_offset = breadcrumb_offset + 1;
}