Monday, March 16, 2015

Connecting to MySQL as a slave ( Open Replicator )

Open Replicator is a open source library provided here.

It is a very useful and easy to use tool to get the bin log events fired when the MySQL masters writes into the bin log file.

Usage as per the Open Replicator wiki:
final OpenReplicator or = new OpenReplicator();
or.setUser("root");
or.setPassword("123456");
or.setHost("localhost");
or.setPort(3306);
or.setServerId(6789);
or.setBinlogPosition(4);
or.setBinlogFileName("mysql_bin.000001");
or.setBinlogEventListener(new BinlogEventListener() {
    public void onEvents(BinlogEventV4 event) {
        // your code goes here
    }
});
or.start();

One thing to note is that the open replicator works in two ways that can be set via the my.cnf file in MySQL. They are;

1. Statement based replication: In this case only QueryEvents are fired and the data is sent in the form of sql query statements. For this the binlog-format needs to be set to "STATEMENT"

2. Row based replication: In this case different events are fired for different statements used i.e. an UpdateEvent is fired for update statements, WriteEvent is fired for Insert statements and DeleteEvent is fired for Delete statements.

Services like facebook, linkedin-databus, flipkart, to name a few, have used this powerful library to manage mysql data.

No comments :