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:
|
1
2
3
4
5
6
|
mysql> CREATE TABLE employees (id INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16)); Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO employees VALUES (1, 2), (3, 7), (4, 12); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 |
The mysqlbinlog output for the INSERT command is:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
### Extra row info for partitioning: partition: 0 ### INSERT INTO `test`.`employees` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=0 is_null=0 */ ### Extra row info for partitioning: partition: 1 ### INSERT INTO `test`.`employees` ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=7 /* INT meta=0 nullable=0 is_null=0 */ ### Extra row info for partitioning: partition: 2 ### INSERT INTO `test`.`employees` ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=12 /* INT meta=0 nullable=0 is_null=0 */ |
In case of an UPDATE Event:
|
1
2
3
|
mysql> UPDATE employees SET store_id = 8 WHERE store_id > 11; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
The mysqlbinlog output for the UPDATE command is:
|
1
2
3
4
5
6
7
8
|
### Extra row info for partitioning: source_partition: 2 target_partition: 1 ### UPDATE `test`.`employees` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=12 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=8 /* INT meta=0 nullable=0 is_null=0 */ |
This summarizes the introduction of the partition information into the binary log . We look forward to hearing from you, leave us your feedback! !