MySQL Binlog Events Use case and Examples

This is in continuation to the previous post, if you have not read that, I would advise you to go through that before continue reading.
I discussed about three use cases in my last post, here I will explain them in detail.

  1.  Extracting data from a binary log file:
    The binary log file is full of information, but what if you want selected info, for example:

    • printing the timestamp for every event in the binary log file.
    • extracting the event types of all the events occurring in the binary log file.

    And any other such data from an event in real time. Below is some sample code which shows how to do that step by step.

    • Connect to the available transport
      // the base class for both types of transport, the term driver and transport are
      // used interchangeably in code.
      binary_log::system::Binary_log_driver *drv;

      • For tcp transport
        // An example of the uri can be mysql://neha@localhost:3306  drv=binary_log::system::create_transport("user@host:port_no");
      • For file transport
        // The path should be absolute like
        // “/home/neha/binlog/masterbin.000001”
        drv=binary_log::system::create_transport("path of the binary log file");
      • Common code for both transports
        binary_log::Binary_log binlog(drv);
        // This internally calls methods from libmysqlclient library
        binlog.connect();
    • Fetch the stream in a buffer
      // In this pair object the first value will store stream buffer and second value
      // will store length buffer, only one event is received at a time.
      std::pair buffer_buflen;
      // This internally calls methods from libmysqlclient library.
      drv= get_next_event(&buffer_buflen);
    • Decode the buffer and get an event object
      binary_log::Binary_log_event *event; // To store the event object
      // This class contains method to decode all the binary log events
      Decoder decode;

      event= decode.decode_event((char *) buffer_buflen.first, buffer_buflen.second,
      NULL/* char array to store the error message*/, 1)
    • Now that the event object is created, it can be used to extract all the information about that particular event, for example
      std::cout<<”Event Data”<<std::endl;
      std::cout<<”Event type ”<<event->get_event_type()<<std::endl;
      std::cout<<”Data written ”<<event->header()->data_written<<std::endl;
      std::cout<<”Binlog Position ”<<event->header()->log_pos<<std::endl;

    You can see the complete code below.

    Screenshot from 2015-04-21 13:25:28

    The above example will print just one event, if you want to continue reading events, place the code for decoding and printing in a while loop, with breaking condition as per your requirement. If there are no more events left to read, an EOF message will appear in case of file transport.

  2. Filtering events from a Binary log file:
    Imagine a case where you want to have all the queries executed on your server in a
    printable format at one place may be in some file, or you want to be notified whenever a binary log file is rotated(this can be identified by Rotate event). Similar to these there can be many other use cases where you are only interested in some events and don’t want to load yourself with extra information.In the last use case we saw how we can get an event object, now to filter event we can do this:
    The steps for getting the event object will be same as last example/*
    Filtering query_event and printing the query for each Query_event
    The type of event object is Binary_log_event which is the abstract class for all events
    to extract any data which is specific to that event We will need to have the appropriate
    cast.
    */
    if (event->get_event_type() == QUERY_EVENT)
    std::cout<<” The Query is “<<static_cast<Query_event*>(event)->query<<std::endl;
    And as this is real time, you can just run your application and all the queries will be printed as soon as they executed on the MySQL Server in case of a tcp transport.
  3. Building application to support heterogeneous replication in real time. Heterogeneous replication is where the two datastore participating in the replication process are different. There are multiple scenarios where this is beneficial, for example: Apache Kafka captures thedata from different streams and processes them, and imagine a scenario where we want to establish a replication system between MySQL server and Apache Kafka, so we can create an application using MySQL Binlog Events which will enable you to create a pipeline between MySQL Server and Apache Kafka, and replicate the data from MySQL to Apache Kafka in real time.

These are just some of the many places where MySQL Binlog Events can be used. This is a very generic framework so there will be many other use-cases. Let us know how you are using this library. Its always exciting to hear about the real use cases. If you have any comments or questions, please get in touch.
If you happen to find a bug, please file the bug at bugs.mysql.com.
This library is availabe at labs.mysql.com, choose MySQL Binlog Events 1.0.0 from the drop down menu.

Looking forward to the feedback.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.