Partition information into binary log

Partitioning enables you to distribute portions of individual tables across a filesystem, according to rules you can set as needed.
The user-selected rule by which the partitioning of data is accomplished is known as a partitioning function. In MySQL this function can be the modulus, simple matching against a set of ranges or value lists, an internal hashing function, or a linear hashing function.

Every partition in a table has an identifier in the form  of a number, the partition_id. It also has a textual form  associated with it, the partition_name.  The partition_id  part  is sufficient to propagate the changes to the target table partition.  The partition information logged into the binary log can be used while mining the log for changes and thus handle changes to partitions in a more optimal way. For instance, one can build some parallelization scheme to handle data on different partitions, effectively speeding up the data capture and transformation..

Now if a Row event modifies the data spanning across ‘N’ partitions then that particular Row event will be split into ‘N’ Row events, and in turn logged as ‘N’ Row events into the binary log. In case of an Update event, the partition_id for both the source partition (where the column data was stored before) and the target partition (where the  column data will be stored after) will be written to the binary log.

Let’s understand this better with an example:

The mysqlbinlog output for the INSERT command is:

In case of an UPDATE Event:

The mysqlbinlog output for the UPDATE command is:

This summarizes the introduction of the partition information into the binary log . We look forward to hearing  from you, leave us your feedback! !

215 total views, 12 views today

Leave a Reply