Apr
24
2013
The other MySQL 5.6 Replication features!
Posted by Luís Soares on Wed 24 Apr 2013 17:12 UTC
Tags:

2010 was a good year for implementing many small feature requests. Lets start by thanking those responsible for requesting these enhancements: Matt Lord, Mikiya Okuno, Matthew Montgomery, Mark Callaghan, Domas Mituzas and Mats Kindahl.
MySQL 5.6 has lots of big and shinny new replication features. In fact some, like global transaction identifiers or multi-threaded slave, are multiple features together under one big headline. Therefore, they get a lot of buzz and since they are complex, game-changing and very exciting to the end user, they deserve a lot of blog posts. But what about other smaller enhancements that are in 5.6 but that do not get so much highlight? 
Let me present a few that I think are particularly interesting, especially for monitoring and/or configuration purposes. To get more details, click on the links and they will take you to the feature requests or worklog entries. 1. Last_[IO|SQL]_Error_Timestamp in SHOW SLAVE STATUS output. Back in 2010, a small feature request from Matt was implemented. In short, the request was to get timestamps along the messages reported on the error message fields of SHOW SLAVE STATUS... And this is indeed very useful information for debugging and diagnosis purposes regarding reported failures on a replication slave. 
As a consequence, a patch was written that made the server prepend error messages, reported in SHOW SLAVE STATUS, with the timestamp at which the related failure was reported as an error. That patch was pushed to 5.6.1. Later, it was decided that the timestamp should have its own field in SHOW SLAVE STATUS instead of being embedded into the error message. 
So it was changed, and starting from 5.6.3, SHOW SLAVE STATUS has: 

  • Last_IO_Error_Timestamp: A timestamp in YYMMDD HH:MM:SS format that shows when the most recent I/O error took place.
  • Last_SQL_Error_Timestamp: A timestamp in YYMMDD HH:MM:SS format that shows when the last SQL error occurred.  

2. Master_Retry_Count in SHOW SLAVE STATUS output.  Still in 2010, and still in SHOW SLAVE STATUS. Mikiya submitted a feature request asking that the value of Master_Retry_Count should be visible through SHOW SLAVE STATUS or SHOW GLOBAL VARIABLES. So it was decided that a new field would be appended to the output of SHOW SLAVE STATUS. The field would export the current value of Master_Retry_Count. In the end, that was exactly what was implemented. Here is the description of this new field: 

  • Master_Retry_Count: The number of times the slave can attempt to reconnect to the master in the event of a lost connection. 

This was introduced in 5.6.1. 3. Master_Retry_Count dynamically settable through "CHANGE MASTER TO". Continuing in 2010. This time a feature request from Matthew was implemented.

Basically, Matthew wanted MASTER_RETRY_COUNT to be dynamically settable through the slave configuration command "CHANGE MASTER TO". Given that MASTER_CONNECT_RETRY was already a configuration option that one could change dynamically, it only made sense to make MASTER_CONNECT_RETRY to be dynamically configurable also. As such, a new parameter to "CHANGE MASTER TO" was added: 

  • MASTER_RETRY_COUNT: Limits the number of reconnection attempts and updates the value of the Master_Retry_Count column in the output of SHOW SLAVE STATUS.

This feature was released in 5.6.1.  4. Last_IO_Error displays the actual number of connection retries made. Yes, that's right, 2010 was very fruitful with respect to small enhancements related to SHOW SLAVE STATUS. As such, this feature was also worked on in 2010.
Mark submitted a feature request asking that the error message printed when slave failed to connect to the master, should be improved to provide details on the reconnection attempts.

As a consequence, changes were made so that once a reconnection to the master failed, the error message would print out the number of times the slave
has retried instead of showing the value of MASTER_RETRY_COUNT. This was implemented in 5.6, so when a MySQL 5.6 slave fails to connect, the user can inspect through SHOW SLAVE STATUS how many reconnect attempts have been made so far. This information is included as part of the message in the Last_IO_Error field for connection failures. 
To be precise, this feature was released in 5.6.1  5. Allowing slave to bind to a specific interface when connecting to the master. I kid you not. :) This fhis feature was also worked on 2010... 
Domas had requested that the user should be allowed to specify that the replication IO thread should bind to some IP when connecting to the master, instead of always using the default address.

This is especially interesting if the host where mysqld is running has several interfaces with different routes to the same master. The configuration of the binding is dynamic and can be done through the command "CHANGE MASTER TO". For this purpose a new parameter was devised:

  • MASTER_BIND: for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master. 

The user can also inspect the status of this configuration option through SHOW SLAVE STATUS, since a new field was added:

  • Master_Bind: Shows the network interface that the slave is bound to, if any, set using the MASTER_BIND option for the CHANGE MASTER TO statement.

This feature was added to MySQL 5.6. In fact, as one can read in the report, this feature had been implemented in MySQL cluster and then it was ported it to the regular MySQL.
It was released as part of 5.6.2.  6. System variables that hold paths to relay log and binary log files.  Finally, and obviously in 2010, a feature was created that allows the user to get more information from the server about its binary logs, in particular, about the paths of the logs and the index files. This feature introduced four new system variables: 

  • log_bin_basename: The variable shows the full path to the binary log files, excluding the extension added by the server. 
  • relay_log_basename: The variable shows the full path to the relay log files, excluding the extension added by the server. 
  • log_bin_index: The variable shows the full path to the binary log index file.
  • relay_log_index: The variable shows the full path to the relay log index file. 

The user can now find where his binary logs are by checking these variables. Mats did blog about this feature roughly three years ago. His blog post has a lot more details and as usual is an interesting read. I suggest you have a look if you are interested in the gory details. 
This change was introduced in 5.6.2. 
Once again, I think the reporters deserve a big "thank you" since they took the time to write up the feature requests and therefore improving MySQL, so here goes: Thank you Matt Lord, Mikiya Okuno, Matthew Montgomery, Mark Callaghan, Domas Mituzas, Mats Kindahl! Looking forward to more requests! In the meantime, enjoy MySQL 5.6!