1

I am using shyiko connector to stream bin log changes from a mysql cluster to downstream database systems.

A cluster = MySQL master + Primary slave + Secondary slave

When the listening MySQL system goes down for whatever reason, the mechanism is to promote the slave as master and then continue on as usual. But the problem is the bin log files and positions are completely different from the failed machine to the newly promoted slave mysql.

The only common thing that I can think of between the commit logs of both machines is the timestamp.Even mysqlbinlog utility has the feature to set timestamp using the --start-datetime option.

Is there a way to find out the position in mysql bin log using a given timestamp? Because the library that I mentioned above can use only specific positions and not timestamps. If no then how should one go by to achieve it.

Greedy Coder
  • 1,256
  • 1
  • 15
  • 36
  • We should probably remove the phrase "MySQL cluster" since that implies a different product than what you are describing. The implementation of `--start-datetime` is actually pretty primitive -- it just scans the log until it finds a record at that timestamp. To clarify, your machines are replicating A > B > C and you want to switch from listening to A and start listening to B, which is now master because A has failed? What is the MySQL Server version involved? – Michael - sqlbot Sep 08 '16 at 12:39
  • "My SQL cluster" - Thats why I have mentioned what a cluster is MySQL master + Primary slave + Secondary slave. I am currently using 5.5.21 (yes its old). But the idea is common irrespective of the version. – Greedy Coder Sep 08 '16 at 12:44
  • Yes, of course, the general idea is common across 5.x but each successive version has additional features. The obvious solution is GTIDs -- that's one of the main value propositions that feature offers -- but that requires 5.6+, can be a fair amount of hassle to enable, and would require your "connector" to support it as well. Another theoretical workaround requires a copy of mysqlbinlog that shipped with server 5.6.12 or newer, yet would work if run against servers as old as 5.1 because the feature is in the client, not the server, and the client is backward-compatible. – Michael - sqlbot Sep 08 '16 at 19:16

1 Answers1

3

The best approach would be to use GTIDs (which mysql-binlog-connector-java supports, btw) but just like Michael said it requires 5.6+. If there is absolutely no way for you to upgrade and you understand the risks involved then getting you own --start-datetime is as simple as: 

BinaryLogClient binaryLogClient = new BinaryLogClient(...);
binaryLogClient.setBinlogFilename(""); // instructs server to stream events 
// starting from the oldest known binlog
final long start = ...
binaryLogClient.registerEventListener(new BinaryLogClient.EventListener() {
    @Override
    public void onEvent(Event event) {
        if (event.getHeader().getTimestamp() < start) {
            return;
        }
        // process the event here
    }
});
binaryLogClient.connect();

NOTE: binlog event's timestamp has seconds precision.

Stanley Shyiko
  • 561
  • 1
  • 5
  • 10