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.
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.