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:
Post a Comment