Skip to content

# Pseudo GTID, Row Based Replication

MySQL GTID orchestrator Replication October 23, 2014

This post continues Pseudo GTID, in a series of posts describing an alternative to using MySQL GTIDs.

The solution offered in the last post does not work too well for row based replication. The binary log entries for the INSERT statement look like this:

>     # at 1020
>     # at 1074
>     #141020 12:36:21 server id 1  end_log_pos 1074  Table_map: `test`.`pseudo_gtid` mapped to number 33
>     #141020 12:36:21 server id 1  end_log_pos 1196  Update_rows: table id 33 flags: STMT_END_F

>     BINLOG '
>     lddEVBMBAAAANgAAADIEAAAAACEAAAAAAAEABHRlc3QAC3BzZXVkb19ndGlkAAMDBw8CQAAE
>     lddEVBgBAAAAegAAAKwEAAAAACEAAAAAAAEAA///+AEAAACL10RUJDg2ZmRhMDk1LTU4M2MtMTFl
>     NC05NzYyLTNjOTcwZWEzMWVhOPgBAAAAlddEVCQ4Y2YzOWMyYy01ODNjLTExZTQtOTc2Mi0zYzk3
>     MGVhMzFlYTg=
>     '/*!*/;

Where’s our unique value? Encoded within something that cannot be trusted to be unique. Issuing mysqlbinlog –verbose helps out:

>     BEGIN
>     /*!*/;
>     # at 183
>     # at 237
>     #141020 12:35:51 server id 1  end_log_pos 237   Table_map: `test`.`pseudo_gtid` mapped to number 33
>     #141020 12:35:51 server id 1  end_log_pos 359   Update_rows: table id 33 flags: STMT_END_F

>     BINLOG '
>     d9dEVBMBAAAANgAAAO0AAAAAACEAAAAAAAEABHRlc3QAC3BzZXVkb19ndGlkAAMDBw8CQAAE
>     d9dEVBgBAAAAegAAAGcBAAAAACEAAAAAAAEAA///+AEAAABt10RUJDc1MWJkYzEwLTU4M2MtMTFl
>     NC05NzYyLTNjOTcwZWEzMWVhOPgBAAAAd9dEVCQ3YjExZDQzYy01ODNjLTExZTQtOTc2Mi0zYzk3
>     MGVhMzFlYTg=
>     '/*!*/;
>     ### UPDATE `test`.`pseudo_gtid`
>     ### WHERE
>     ###   @1=1
>     ###   @2=1413797741
>     ###   @3='751bdc10-583c-11e4-9762-3c970ea31ea8'
>     ### SET
>     ###   @1=1
>     ###   @2=1413797751
>     ###   @3='7b11d43c-583c-11e4-9762-3c970ea31ea8'

and that’s something we can work with. However, I like to do stuff from within MySQL, and rely as little as possible on external tools. How do the binary log entries look via SHOW BINLOG EVENTS? Not good.

>     master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000058' limit 20;
>     +------------------+------+-------------+-----------+-------------+---------------------------------------+
>     | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                  |
>     +------------------+------+-------------+-----------+-------------+---------------------------------------+
>     | mysql-bin.000058 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4 |
>     | mysql-bin.000058 |  107 | Query       |         1 |         183 | BEGIN                                 |
>     | mysql-bin.000058 |  183 | Table_map   |         1 |         237 | table_id: 33 (test.pseudo_gtid)       |
>     | mysql-bin.000058 |  237 | Update_rows |         1 |         359 | table_id: 33 flags: STMT_END_F        |
>     | mysql-bin.000058 |  359 | Xid         |         1 |         386 | COMMIT /* xid=5460 */                 |
>     | mysql-bin.000058 |  386 | Query       |         1 |         462 | BEGIN                                 |
>     | mysql-bin.000058 |  462 | Table_map   |         1 |         516 | table_id: 33 (test.pseudo_gtid)       |
>     | mysql-bin.000058 |  516 | Update_rows |         1 |         638 | table_id: 33 flags: STMT_END_F        |
>     | mysql-bin.000058 |  638 | Xid         |         1 |         665 | COMMIT /* xid=5471 */                 |
>     | mysql-bin.000058 |  665 | Query       |         1 |         741 | BEGIN                                 |
>     | mysql-bin.000058 |  741 | Table_map   |         1 |         795 | table_id: 33 (test.pseudo_gtid)       |
>     | mysql-bin.000058 |  795 | Update_rows |         1 |         917 | table_id: 33 flags: STMT_END_F        |
>     | mysql-bin.000058 |  917 | Xid         |         1 |         944 | COMMIT /* xid=5474 */                 |
>     | mysql-bin.000058 |  944 | Query       |         1 |        1020 | BEGIN                                 |
>     | mysql-bin.000058 | 1020 | Table_map   |         1 |        1074 | table_id: 33 (test.pseudo_gtid)       |
>     | mysql-bin.000058 | 1074 | Update_rows |         1 |        1196 | table_id: 33 flags: STMT_END_F        |
>     | mysql-bin.000058 | 1196 | Xid         |         1 |        1223 | COMMIT /* xid=5476 */                 |
>     | mysql-bin.000058 | 1223 | Query       |         1 |        1299 | BEGIN                                 |
>     | mysql-bin.000058 | 1299 | Table_map   |         1 |        1353 | table_id: 33 (test.pseudo_gtid)       |
>     | mysql-bin.000058 | 1353 | Update_rows |         1 |        1475 | table_id: 33 flags: STMT_END_F        |
>     +------------------+------+-------------+-----------+-------------+---------------------------------------+

The representation of row-format entries in the SHOW BINLOG EVENTS output is really poor. Why, there’s nothing to tell me at all about what’s been done, except that this is some operation on test.pseudo_gtid. Obviously I cannot find anything unique over here.

Not all is lost. How about DDL statements? Those are still written in SBR format (there’s no rows to log upon creating a table). A solution could be somehow manipulating a unique value in a DDL statement. There could be various such solutions, and I chose to use a CREATE VIEW statement, dynamically composed of a UUID():

>     drop event if exists test.update_pseudo_gtid_rbr_event;

>     delimiter ;;
>     create event if not exists
>       test.update_pseudo_gtid_rbr_event
>       on schedule every 10 second starts current_timestamp
>       on completion preserve
>       enable
>       do
>         begin
>           set @pseudo_gtid := uuid();
>           set @_create_statement := concat('create or replace view test.pseudo_gtid_v as select \'', @pseudo_gtid, '\' from dual');
>           PREPARE st FROM @_create_statement;
>           EXECUTE st;
>           DEALLOCATE PREPARE st;
>         end
>     ;;

>     delimiter ;

And this is how it looks on runtime (running this new event along with the old one):

>     master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000060' limit 20;
>     +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>     | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                  |
>     +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>     | mysql-bin.000060 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                                                                 |
>     | mysql-bin.000060 |  107 | Query       |         1 |         183 | BEGIN                                                                                                                                                                                 |
>     | mysql-bin.000060 |  183 | Table_map   |         1 |         237 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
>     | mysql-bin.000060 |  237 | Update_rows |         1 |         359 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
>     | mysql-bin.000060 |  359 | Xid         |         1 |         386 | COMMIT /* xid=5802 */                                                                                                                                                                 |
>     | mysql-bin.000060 |  386 | Query       |         1 |         638 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '7d2d44ca-583e-11e4-9762-3c970ea31ea8' from dual |
>     | mysql-bin.000060 |  638 | Query       |         1 |         714 | BEGIN                                                                                                                                                                                 |
>     | mysql-bin.000060 |  714 | Table_map   |         1 |         768 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
>     | mysql-bin.000060 |  768 | Update_rows |         1 |         890 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
>     | mysql-bin.000060 |  890 | Xid         |         1 |         917 | COMMIT /* xid=5811 */                                                                                                                                                                 |
>     | mysql-bin.000060 |  917 | Query       |         1 |        1169 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '83234b13-583e-11e4-9762-3c970ea31ea8' from dual |
>     | mysql-bin.000060 | 1169 | Query       |         1 |        1245 | BEGIN                                                                                                                                                                                 |
>     | mysql-bin.000060 | 1245 | Table_map   |         1 |        1299 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
>     | mysql-bin.000060 | 1299 | Update_rows |         1 |        1421 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
>     | mysql-bin.000060 | 1421 | Xid         |         1 |        1448 | COMMIT /* xid=5819 */                                                                                                                                                                 |
>     | mysql-bin.000060 | 1448 | Query       |         1 |        1700 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '89193a09-583e-11e4-9762-3c970ea31ea8' from dual |
>     +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Scroll to the right to find the unique value injected into the view’s creation statement.

Does it replicate well? Looking at a slave’s binary logs:

>     slave3 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000064';
>     +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>     | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                  |
>     +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>     | mysql-bin.000064 |    4 | Format_desc |       103 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                                                                 |
>     | mysql-bin.000064 |  107 | Query       |         1 |         166 | BEGIN                                                                                                                                                                                 |
>     | mysql-bin.000064 |  166 | Table_map   |         1 |         220 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
>     | mysql-bin.000064 |  220 | Update_rows |         1 |         342 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
>     | mysql-bin.000064 |  342 | Xid         |         1 |         369 | COMMIT /* xid=3184 */                                                                                                                                                                 |
>     | mysql-bin.000064 |  369 | Query       |         1 |         601 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '7d2d44ca-583e-11e4-9762-3c970ea31ea8' from dual |
>     | mysql-bin.000064 |  601 | Query       |         1 |         660 | BEGIN                                                                                                                                                                                 |
>     | mysql-bin.000064 |  660 | Table_map   |         1 |         714 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
>     | mysql-bin.000064 |  714 | Update_rows |         1 |         836 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
>     | mysql-bin.000064 |  836 | Xid         |         1 |         863 | COMMIT /* xid=3194 */                                                                                                                                                                 |
>     | mysql-bin.000064 |  863 | Query       |         1 |        1095 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '83234b13-583e-11e4-9762-3c970ea31ea8' from dual |
>     | mysql-bin.000064 | 1095 | Query       |         1 |        1154 | BEGIN                                                                                                                                                                                 |
>     | mysql-bin.000064 | 1154 | Table_map   |         1 |        1208 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
>     | mysql-bin.000064 | 1208 | Update_rows |         1 |        1330 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
>     | mysql-bin.000064 | 1330 | Xid         |         1 |        1357 | COMMIT /* xid=3198 */                                                                                                                                                                 |
>     | mysql-bin.000064 | 1357 | Query       |         1 |        1589 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '89193a09-583e-11e4-9762-3c970ea31ea8' from dual |
>     +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Not as pretty; I hate DDL operations that are not strictly required; but this solves the problem, plus rewriting the view means we’re not littering the tablespace.

Next post will describe the steps towards achieving GTID-like behaviour based on the above.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
2
Leave a Reply

2 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
0 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
On SHOW BINLOG/RELAYLOG EVENTS | code.openark.org

[…] Pseudo GTID, Row Based Replication […]

On SHOW BINLOG/RELAYLOG EVENTS | MySQL

[…] This is the reason why Pseudo GTID injection is made via DDL. […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.