Monday, January 07, 2013

Easily testing MySQL 5.6 GTID in a sandbox

MySQL 5.6 seems to be ready for GA. I have no inside information about it, but from some clues collected in various places I feel that the release should not be far away. Thus, it's time for some serious testing, and for that purpose I have worked at updating MySQL Sandbox with some urgent features.

I have just released MySQL Sandbox 3.0.28, with more support for MySQL 5.6. Notably in this release, there is suppression of MySQL 5.6 annoying verbosity, additional suppression of more annoying warnings ( actually a bug) when using empty passwords on the command line.

There is also an enhancement to the 'clear' command. In previous versions of MySQL, this command removed everything from the data directory, leaving the server ready for a clean start. In MySQL 5.6, this is not feasible, because there are innodb tables in the mysql schema. Therefore, what happens now is that, immediately after creating the sandbox users, the installation program stores a dump of the 'mysql' schema. The 'clear' command will remove the innodb tables from mysql, and the 'start' command will notice that and reload the schema from the dump.

More interesting, though, the replication installer creates a file (only if the MySQL server is 5.6.9 or higher) called 'enable_gtid' which restarts the replication cluster with Global Transaction Identifiers enabled.

Let's see an example session:
$ make_replication_sandbox 5.6.9
installing and starting master
installing slave 1
installing slave 2
starting slave 1
... sandbox server started
starting slave 2
... sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_5_6_9

$ cd $HOME/sandboxes/rsandbox_5_6_9

$ ./check_slaves 
slave # 1
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2590
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 2590
slave # 2
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2590
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 2590

Now we use the 'enable_gtid' command. It will simply restart the cluster with the appropriate options.

$ ./enable_gtid 
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
executing "start" on master
. sandbox server started
executing "start" on slave 1
. sandbox server started
executing "start" on slave 2
. sandbox server started

$ ./check_slaves 
slave # 1
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 151
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 151
slave # 2
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 151
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 151

Now, let's see if Global Transaction IDs are enabled.

$ ./m -e 'create table test.t1(i int not null primary key)'
$ ./m -e 'insert into test.t1 values (1)'
$ ./m -e 'insert into test.t1 values (2)'
$ ./m -e 'show master status\G'
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 825
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3

$ ./m -e 'show binlog events in "mysql-bin.000002"'
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.9-rc-log, Binlog ver: 4                           |
| mysql-bin.000002 | 120 | Previous_gtids |         1 |         151 |                                                                   |
| mysql-bin.000002 | 151 | Gtid           |         1 |         199 | SET @@SESSION.GTID_NEXT= 'C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1' |
| mysql-bin.000002 | 199 | Query          |         1 |         317 | create table test.t1(i int not null primary key)                  |
| mysql-bin.000002 | 317 | Gtid           |         1 |         365 | SET @@SESSION.GTID_NEXT= 'C0DF6B8A-5823-11E2-BC44-3970854BE7A5:2' |
| mysql-bin.000002 | 365 | Query          |         1 |         440 | BEGIN                                                             |
| mysql-bin.000002 | 440 | Query          |         1 |         540 | insert into test.t1 values (1)                                    |
| mysql-bin.000002 | 540 | Xid            |         1 |         571 | COMMIT /* xid=26 */                                               |
| mysql-bin.000002 | 571 | Gtid           |         1 |         619 | SET @@SESSION.GTID_NEXT= 'C0DF6B8A-5823-11E2-BC44-3970854BE7A5:3' |
| mysql-bin.000002 | 619 | Query          |         1 |         694 | BEGIN                                                             |
| mysql-bin.000002 | 694 | Query          |         1 |         794 | insert into test.t1 values (2)                                    |
| mysql-bin.000002 | 794 | Xid            |         1 |         825 | COMMIT /* xid=29 */                                               |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

Good. The master has stored the GTID in the binary logs. What about the slaves?

$ ./s1 -e 'show slave status\G' |grep 'Running:\|Gtid'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           Retrieved_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3
            Executed_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3

$ ./s2 -e 'show slave status\G' |grep 'Running:\|Gtid'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           Retrieved_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3
            Executed_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3

Also the slaves are collecting GTIDs. All is well.

Notice that this method is only safe because we are using a system with no traffic at all. If your replication is under load, then you need to follow the method described in the MySQL 5.6 user manual.

No comments: