There has been a lot of talk about MySQL 5.6 replication improvements. With few exceptions, what I have seen was either marketing messages or hearsay. This means that few people have really tried out the new features to see whether they meet the users needs.
As usual, I did try the new version in my environment. I like to form my own opinion based on experiments, and so I have been trying out these features since they have appeared in early milestones.
What follows is a list of (potentially) surprising results that you may get when using MySQL 5.6.
All the examples are made using MySQL 5.6.6.
Gotcha #1 : too much noise
I have already mentioned that MySQL 5.6 is too verbose when creating data directory. This also means that your error log may have way more information than you'd like to get. You should check the contents of the error log when you start, and either clean it up before using it on a regular basis or take note of what's there after a successful installation, so you won't be surprised when something goes wrong.
Gotcha #2 : Innodb tables where you don't expect them
Until version 5.5, after you installed MySQL, you could safely drop the ib* files, change the configuration file, and restart MySQL with optimized parameters. Not anymore.
When you run
select table_schema, table_name from information_schema .tables where engine='innodb'; +--------------+----------------------+ | table_schema | table_name | +--------------+----------------------+ | mysql | innodb_index_stats | | mysql | innodb_table_stats | | mysql | slave_master_info | | mysql | slave_relay_log_info | | mysql | slave_worker_info | +--------------+----------------------+
The
Another side effect of this issue is that, whatever setting you want to apply to innodb (size of the data files, file-per-table, default file format, and so on) must be done when you run
Gotcha #3 : Global transaction IDs and security
The information about Global transaction ID is not easy to locate. But eventually, searching the manual, you will get it. The important information that you take from this page is that this feature only works if you enable all these options in all the servers used for replication:
log-bin server-id=xx log-slave-updates gtid-mode=ON disable-gtid-unsafe-statements
The first two options are not a surprise. You need them for replication anyway. Check.
The third one is puzzling. Why would you want this option in a master? But then you realize that this will allow any server to be promoted or demoted at will. Check.
The last option forces the server to be safe, by using only transactional tables, and by forbidding things like temporary tables inside transactions and
The trouble is, if you enable gtid-mode=ON (with its mandatory ancillary options), you can't run mysql_secure_installation, because that utility needs to delete anonymous users and clean the 'db' table for anonymous usage of the 'test' database.
The workaround is to enable GTID after you secure the installation, which means one more server restart.
Gotcha #4 (bug): multi thread slave won't work without safe-crash slave tables
To enable parallel replication, you need to change the value of 'slave_parallel_workers" to a value between 1 and 1024.
show variables like '%worker%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.06 sec)
slave1 [localhost] {msandbox} ((none)) > set global slave_parallel_workers=5;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} (mysql) > start slave;
Query OK, 0 rows affected, 1 warning (0.05 sec)
slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_worker_info\G
Empty set (0.00 sec)
What the hell? The workers table is empty.
I know the cause: the
Anyway, once you are in this bizarre condition, you can't activate
Gotcha #5 (bug) : master and relay_log repository must be either set forever or they will fail
After we have activated parallel threads, without enabling table repositories, you can't easily get to a clean replication environment:set global relay_log_info_repository='table'; start slave; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error logAnd the error log says:
120822 14:15:08 [ERROR] Error creating relay log info: Error transfering information.
What you need to do is
- stop the slave
- enable both master_info_repository and relay_log_info_repository as 'table'
- set the number of parallel threads
- restart the slave
slave1 [localhost] {msandbox} (mysql) > stop slave;
Query OK, 0 rows affected (0.02 sec)
slave1 [localhost] {msandbox} (mysql) > set global master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} (mysql) > set global relay_log_info_repository='table';
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} (mysql) > set global slave_parallel_workers=5;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} (mysql) > start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
slave1 [localhost] {msandbox} (mysql) > select count(*) from slave_worker_info;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
This sequence of commands will start parallel replication, although MySQL crashes when restarting the slave.
Gotcha #6 : Global transaction IDs not used in parallel threads
Global transaction IDs (GTIDs) are very useful when you need to switch roles from master to slave, and especially when you deal with unplanned failovers. They are also a great simplification in many cases where you need to identify a transaction without getting lost in the details of binary log file and position.
However, one of the cases where GTIDs would have been most useful, they are not there. The table
