MySQL security for real users

Security features overview

One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:

1. MySQL 5.7 has removed the anonymous accounts, which was the greatest threat to security. Because of those accounts, and the default privileges granted to them, users without any privileges could access the "test" database and do serious damage. Additionally, because of the way the privilege engine evaluates accounts, anonymous users could hijack legitimate users, by preventing them to work properly.
2. The "root" account now comes with a password defined during initialization. This is good news for security, but bad news for how the change was implemented.
3. There is a new way of setting an options file for connection credentials: the mysql_config_editor paired with option --login-path allows users to store encrypted credentials for secure use. Also here, while we should rejoice for the added security, we can't help feeling that the implementation is yet again far from meeting users needs.
4. There is an useful warning (introduced in MySQL 5.6) when using a password on the command line, telling users that it is a risk. Also in this case, we have a usability issue: while users care about their production deployments and use option files to avoid using passwords on the command line, there are, nonetheless, a lot of testing scripts, used in safe environment or with non-valuable data, where a password in the command line was not an issue, and the new warning simply screws up the result of those carefully crafted tests. This change, which can't be overcome without modifying the MySQL clients code, needs users to change their existing tests to adapt to the new behavior.
5. MySQL 8 introduces roles, which simplify the accounts management. There are some minor usability issues, although in general the feature meets expectations.

This is the scenario of the main enhancements in MySQL since 5.6. Each one of them has some usability problems, some minor, some really bad.
We will first have a look at the problems mentioned above, and then examine the root cause for why they have arisen.

Usability issues

I start by noticing that some developers in the MySQL team have been working there for many years, starting with the time when MySQL was a different database and was used really differently.
In those times, managing the database meant that a human (the DBA) would run operations manually, take a look at the result, and adjust when needed. And then, when things went wrong, the same human explored the database system to find out what happened, took action, and went back to sleep.

Human-centered management leads to human problems: lazy DBA left their databases without password, using the root account, and exposing the server to uninspired attacks; they used passwords on the command line, without caring for options files (or without knowing about them.) Careless DBAs did not deal with anonymous users, leaving a dangerous backdoor in their server.

Some of the new functionalities introduced in the latest MySQL versions are aimed at this type of users: when you install MySQL, you get a message saying: your root password is ************, and the lazy DBAs have no option but to take note and use it. When they use the password on the command line, the annoying warning forces them to start using an options file or the mysql_config_editor.

This is all good, but the main problem here is that the DBAs of 10 years ago are on the verge of extinction. They are replaced by a new breed of DBAs who are not lazy, because they can't afford to be, and need to use dozens, hundreds, thousands of databases at once, using configuration management tools that don't require manual intervention, and actually abhor it. In the land of automation, some of the MySQL security enhancements are not seen as a solution, but as new problems.

Let's see an interesting example: docker containers.

Using Docker, MySQL images are deployed using a password on the command line. This is done for compatibility with the first implementation of the image maintained by the Docker team, where you deploy with this syntax:

docker run -e MYSQL_ROOT_PASSWORD=secret -d mysql

The MYSQL_ROOT_PASSWORD is a directive that becomes an environment variable inside the container, and the server uses it during initialization. As you can imagine, this is not recommended for a secure environment. Then, what's the MySQL team recommendation? They suggest the same strategy used for manual installation: set a directive MYSQL_RANDOM_ROOT_PASSWORD that results in a random password being generated, then collected by the DBA and used. Alternatively, the directive MYSQL_ONETIME_PASSWORD will force the root user to change the password on first connection.

The above suggestions were designed with the ancient DBA still in mind, while container deployment is even more automated than VMs, and it is based on the principle of immutable objects, i.e. containers that spring up from the cloud ready to run, with no configuration needed, and especially no configuration that requires someone (or some tool) to extract a new password from a log. I proposed a different solution, that would never show passwords on the command line and while it was implemented, but it still feels like a hack to circumvent an inadequate design.

As a result, the implementation inside the MySQL recommended Docker image uses "--initialize-insecure" to start the server. This is an implicit recognition of the bad design of the initialization feature. What was designed to overcome DBA's laziness becomes an obstacle towards automation.

We have a similar problem with mysql_config_editor: the tool will create a safe configuration file with credentials for multiple instances, but the password must be inserted manually. Consequently, this potentially useful feature doesn't get adopted, because it would be too difficult or impossible to automate properly.

We have seen that, of the security features that were introduced lately, only a few can be used safely in an automated environment, and all of them have at least one small usability quirk. I have talked about a confusing issue related to the removal of anonymous users where in their eagerness of removing the vulnerability the MySQL team removed also the "test" database, which was a consequence, not the cause of the problem. And I have recently talked about roles usability where there are still open problems, like the ability of telling roles from users which are apparently not considered a bug by the MySQL team.

All the above considerations led me to ask: how did we get to this point? There is an active community, and feedback is offered often with plenty of detail. How come we have such an abundance of usability issues? Don't the developers spend time with users at conferences to learn what they expect? Don't they read articles and blog posts about how a new feature meets expectations? Don't they talk to customers who have adopted new features? They certainly do. Then, why the usability problems persist?

What follows is my observation and speculation on this matter.

Disconnection between MySQL developers and users community

My experience working with system providers has put me in contact with many users. I have seen that in most cases users are very much protective of their current deployment, because it took them long time to get it right, and they don't upgrade unless they don't have another choice. I've seen users test the newer versions, realize that they would break some of their procedures, and defer the upgrade to better times that never come. I remember last year a user with a not so large set of servers was considering an upgrade to MySQL 5.6, while 5.7 had been GA for two years. The reason was a set of incompatibilities that made the upgrade too difficult.

For companies that deal with thousands of servers, the problem is similar, but exacerbated by the quantity of servers to upgrade and the need to do it without stopping operations. This latest requirement has made some users decide not to use GTID, because it required offline time for a master, and they hadn't had time enough to test the upgrade to MySQL 5.7 that would solve that problem.

For one reason or the other, many companies upgrade only two or three years after a given version became GA. And this is the main problem: until they use it in production, or at least test the version for a projected upgrade, users can't give valuable feedback, the one that is related to usage in production, and when they do, the version for which they provide feedback has been GA for long time, and can't be changed, while the next one is already close to GA, and as such will be untouchable.

The MySQL team gets feedback on a release from a handful of curious users who don't delay testing until the new version is GA, but don't provide the kind of important feedback that get the development team attention, such as deployment in production by large customers. In many cases, large customers are the ones that upgrade several years after GA, and by then their input is difficult to act upon.

We have then a curious situation, where the warnings given by the early software explorers are confirmed years later by the users to which the MySQL team listens more closely, but by then the next version of the server is already locked in a release schedule that nobody wants to alter to fix usability issues.

How can we solve this problem? Simple: listen to early software explorers and try to fix problems before GA.

PerconaLive Amsterdam 2016 - Talks and shows

With Oracle Open World behind us, we are now getting ready for the next big event, i.e. the European edition of PerconaLive. I am going to be a presenter three times:

• MySQL operations in Docker is a three-hour tutorial, and it will be an expansion of the talk by the same title presented at OOW. Attendees who want to play along can do it, by coming prepared with Docker 1.11 or later and the following images already pulled (images with [+] are mandatory, while [-] are optional):

  • mysql/mysql-server [+]
  • mysql/mysql-gr [+]
  • mysql [-]
  • percona/percona-server [-]
  • mariadb [-]
  • mysql/shell [-]
  • datacharmer/mysql-minimal-5.5 [-]
  • datacharmer/mysql-minimal-5.6 [-]
  • datacharmer/mysql-minimal-5.7 [-]
  • datacharmer/mysql-minimal-8.0 [-]
  • datacharmer/my-ubuntu [-]
  • datacharmer/my-debian [-]
  • datacharmer/my-centos [-]

• The fastest customized MySQL deployment ever is a presentation where I show two ways of deploying MySQL on a custom server, with MySQL Sandbox and Docker.

• The lightning talks will be held during the Community dinner at Booking.com, diamond sponsor of the conference. If you want to attend, you need to register, and if you want a free ticker for that dinner, there is still ONE SLOT for the lightning talks. Contact me directly with a title and an abstract if you want to be considered for that slot (finding my email is part of the challenge, but it should not be that difficult).

UPDATE: here's the lightning talks program. Percona should eventually add it to the schedule.

• 19:00: arrival at Booking.com by boat
• 19:15: welcoming speech and beginning of the LT
• 20:00 food buffet opens, LT are not finished yet
• 20:30: LT are done, buffet still open
• 21:15: buffet closes

The talks accepted are the following:

• Jaime Crespo
  dbhell: a tiny Python framework for the administration and monitoring of farms of MySQL servers
• Federico Razzoli
  How to write slow and buggy stored procedures
• Art Van Scheppingen
  Simple household rules that keep MySQL running
• Pavel Tru
  What internal statistics every self-respecting dbms should have!
• Giuseppe Maxia
  Fastest, smallest, newest: download and deploy MySQL at high speed
• Ronald Bradford
  An Awe-Inspiring Journey Through the World of Numbers

MySQL at Oracle Open World 2016

MySQL is a growing presence at Oracle Open World. While most of the headlines belong to the main products, where Oracle services are aiming at world domination, MySQL shared the spotlight, as it was announced to be part of Oracle database cloud. It seems a logical move for Oracle: after all the effort to make MySQL 5.7 the biggest release ever, it stands to reason that it is offered as a competitive feature in its own database as a service.

With this offer, Oracle is applying enterprise pricing and methodologies to a target of emerging companies. MySQL in the Oracle cloud differs from the competition by a few key points:

• It's only MySQL 5.7. While this is the most advanced MySQL server available, users with existing deployments may exercise caution before adopting this cloud service. Companies that are starting now, instead, may take advantage of using the latest and greatest.
• It's MySQL Enterprise edition, with a wealth of additional monitoring and management tools that are missing in other cloud offerings.
• Unlike some popular competitors, it includes access to the server host, allowing DBAs to enable plugins, fine tune the deployment, and explore the operating system in addition to the database.
• It includes Oracle support.

Time will tell if this offering will be successful. It may not appeal to all the users, but there is surely a category of power users who can take advantage of these features.

The surprises of the MySQL keynote did not stop at the cloud. We had already seen, one week before the conference, that MySQL 8.0 was released, with many juicy features. What came unexpected are two announcements:

• MySQL group replication, a product that has been living in MySQL Labs for quite a while, was declared "release candidate" despite lacking documentation and being released in a format that discourages adoption, except from diehard hackers.
• Another product is been released, again in the labs, with an ambitious mission. The MySQL InnoDB cluster is based on group replication and wants to be a 15-minute deployment of high-availability and scalable system, thanks to an enhanced version of MySQL Shell (the same used for the document store. Its feature set are exciting, but what we have seen in the demos suggests that the product is still in the early stages of development.

With these moves, Oracle is showing two paths of MySQL development:

• in the main path, which has produced the GA of MySQL 5.5, 5.6, and 5.7, the MySQL team is showing the positive influence of Oracle engineering, with focus on security, performance, and stability.
• in a parallel course, which started last April with the announcement of MySQL document store and its related shell, the team wants to introduce new features to a GA release as plugins, with the reasoning that the main release will not be touched (thus avoiding the taboo of altering a stable product) but users are free to enable plugins and unleash new functionalities.

The mix of traditional and agile releases are provoking exciting thoughts, albeit moderated by the fear of using together experimental code in a GA deployment.

The methodology of these releases is also baffling. It is unclear how mature is the document store. The plugin comes with the server, and it is accompanied by an huge set of documentation, which implies that it has been designed extensively and tested internally for a while, but the main tool for the feature, mysql shell is labeled as development preview: not very encouraging. On the other hand, the latest plugin addition, the MySQL group replication, which has been declared of release candidate quality, is still in the labs (no plugin in the server release), and without documentation.

All considered, while it is clear that Oracle is putting an enormous engineering effort into growing MySQL, I have the feeling that the replication features have been neglected and the announcement of group replication mixed fortunes confirms me in this belief.

The conference was useful to me. I had the chance of meeting many Oracle engineers and users, and discuss technical and community matters at length. My own presentation, a two-hour tutorial on MySQL operations in Docker was highly satisfactory, as it proved to be an interesting topic that was actively discussed by the audience.

Improving the design of MySQL replication

Now that MySQL 8.0 has been revealed, it's time to take a deep look at replication features in the latest releases, and review its overall design.

Server UUID vs Server-ID

At the beginning of replication, there was the server_id variable that identified uniquely a node in a replication system. The variable is still here, but in MySQL 5.6 it was joined by another value, which is created during the server initialisation, regardless of its involvement in a replication system. The server_uuid is a string of hexadecimal characters that is the basis for global transaction identifiers:

select @@server_id, @@server_uuid;
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 4c0a9670-7f9a-11e6-9f8b-188f83e4eefc |
+-------------+--------------------------------------+

If this server is a master, its slave will report:

        Master_Server_ID: 101 
             Master_UUID: 4c0a9670-7f9a-11e6-9f8b-188f83e4eefc
       [...]
       Retrieved_Gtid_Set: 4c0a9670-7f9a-11e6-9f8b-188f83e4eefc:1-42
        Executed_Gtid_Set: 4c0a9670-7f9a-11e6-9f8b-188f83e4eefc:1-42

The serverid is still indispensable. We can't use the same value for two nodes in the replication system, or we get errors. But instead of using the serverid as the identifier for global transaction identifiers (GTID) we use the serverUUID, thus being subjected to the inhuman treatment of dealing with unreadable values to track our valuable transactions. Yet, server UUIDs have not replaced everything: if we want to exclude one or more servers from being applied, we still refer to them by serverid, as in the IGNORE_SERVER_IDS clause of CHANGE MASTER TO.

How should it be instead

While UUIDs guarantee that identifiers are unique in the the whole planet, the number of nodes in a replication system are finite, and for practical a single system may not go beyond the thousands. I don't care if my serverid is the same as a serverid in another continent: all I want is to be unique within my system. And since we have to use unique values for every node server_id, it is unnecessarily cruel to force users to deal with long strings instead of plain numbers.

VARIABLES vs STATUS

In general, MySQL behavior gets changed by setting variables to a given value either in the options file or using SET GLOBAL dynamically. To see the setting of a given value, we use SHOW VARIABLES:

show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 15    |
+-----------------+-------+

Here max_connections was changed from its default value to a much lower one. To see how many connections we have consumed, we use SHOW STATUS:

show status like '%connections%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_max_connections | 0                   |
| Connections                       | 7                   |
| Max_used_connections              | 1                   |
| Max_used_connections_time         | 2016-09-20 18:01:40 |
+-----------------------------------+---------------------+

That's the general theory. Sometimes, in replication, MySQL follows the same path. For example, in semi-synchronous replication, we have the settings of the functionality using variables:

 show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

and the running results of how the feature is performing using status:

set status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 4     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 431   |
| Rpl_semi_sync_master_tx_wait_time          | 863   |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     |
+--------------------------------------------+-------+

However, when it comes to GTID, the paradigm is broken:

slave1 [localhost] {msandbox} ((none)) > show global VARIABLES like '%gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | 00011808-1111-1111-1111-111111111111:1-42 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 00011808-1111-1111-1111-111111111111:1-42 |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > show global STATUS like '%gtid%';
Empty set (0.01 sec)

Here we have variables like gtid_mode and enforce_gtid_consistency that are used to enable the behavior, but the monitoring of the result (gtid_executed and gtid_purged) should have been in STATUS, not in VARIABLES. The same usage of VARIABLES can be seen in MySQL group replication.

How should it be instead

The values that monitor the functionality should be in STATUS rather than in VARIABLES. However, in this case, it would be better if the values were tracked in performance_schema tables, as noted below.

GTID executed

When GTIDs are enabled, we can see its value in SHOW MASTER STATUS

SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 6325
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22
1 row in set (0.00 sec)

The information here has a precise meaning: the transaction set 1-22 comes from a master identified by UUID 00007801-1111-1111-1111-111111111111. The latest transaction corresponds to binary log mysql-bin.000002 at position 6325.

This is the identification of a source, and as such is the basis for monitoring. When we want to check if the slave service is running fine, we need to compare the slave progress against the information reported by the master. So far, so good.

However, when we use multiple masters, the matter becomes less clear. A slave that was replicating from this master and from two more, will show the following:

SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4

This is completely wrong. What we see here has nothing to do with being a master, as the data shown in the above statement was originated in different servers. The data is also wrong because it is thrown together in the same field. Using the same server, we can see the same information in different ways.

select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4

This is the same info seen before. In this context, it may seem less wrong, as it is only data coming from other servers.

Here again the information will become much more confused when the slave is also a master and produces its own data sets. If we create something in this server, we end up with the following:

select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4,
00007804-4444-4444-4444-444444444444:1-2

Now we have in the same field the data that was produced in this server and the data that came through replication. The matter looks even more embarrassing if we look at SHOW SLAVE STATUS, which reports distinct data for every channel, but the executed_gtid_set is reported all mixed up, in every channel, unchanged and confused, master data and slave data alike.

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
[...]
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 6325
               Relay_Log_File: mysql-relay-node1.000002
                Relay_Log_Pos: 6538
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
          Exec_Master_Log_Pos: 6325
              Relay_Log_Space: 6747
[...]
             Master_Server_Id: 101
                  Master_UUID: 00007801-1111-1111-1111-111111111111
[...]
           Retrieved_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22
            Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4,
00007804-4444-4444-4444-444444444444:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node1
           Master_TLS_Version:
*************************** 2. row ***************************
[...]
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 6039
               Relay_Log_File: mysql-relay-node2.000002
                Relay_Log_Pos: 6252
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
          Exec_Master_Log_Pos: 6039
              Relay_Log_Space: 6461
[...]
             Master_Server_Id: 102
                  Master_UUID: 00007802-2222-2222-2222-222222222222
[...]
           Retrieved_Gtid_Set: 00007802-2222-2222-2222-222222222222:1-21
            Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4,
00007804-4444-4444-4444-444444444444:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node2
           Master_TLS_Version:
*************************** 3. row ***************************
[...]
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1177
               Relay_Log_File: mysql-relay-node3.000002
                Relay_Log_Pos: 1390
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
          Exec_Master_Log_Pos: 1177
              Relay_Log_Space: 1599
[...]
             Master_Server_Id: 103
                  Master_UUID: 00007803-3333-3333-3333-333333333333
[...]
           Retrieved_Gtid_Set: 00007803-3333-3333-3333-333333333333:1-4
            Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4,
00007804-4444-4444-4444-444444444444:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node3
           Master_TLS_Version:

How should it be instead

First off, the data should not be in a single variable, because it is composed of multiple values. But even if it were to be kept in the environment (as STATUS items, not variables, as we have seen above,) we should have a distinction:

SHOW STATUS LIKE 'gtid_applied'
*************************** 1. row ***************************
@@global.gtid_executed: 00007801-1111-1111-1111-111111111111:1-22,
00007802-2222-2222-2222-222222222222:1-21,
00007803-3333-3333-3333-333333333333:1-4

SHOW STATUS LIKE 'gtid_generated'
*************************** 1. row ***************************
00007804-4444-4444-4444-444444444444:1-2

The SHOW SLAVE STATUS should report the right line for each channel. For example:

*************************** 1. row ***************************
[...]
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 6325
               Relay_Log_File: mysql-relay-node1.000002
                Relay_Log_Pos: 6538
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
          Exec_Master_Log_Pos: 6325
              Relay_Log_Space: 6747
[...]
             Master_Server_Id: 101
                  Master_UUID: 00007801-1111-1111-1111-111111111111
[...]
           Retrieved_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22
            Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node1
           Master_TLS_Version:
*************************** 2. row ***************************
[...]
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 6039
               Relay_Log_File: mysql-relay-node2.000002
                Relay_Log_Pos: 6252
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
          Exec_Master_Log_Pos: 6039
              Relay_Log_Space: 6461
[...]
             Master_Server_Id: 102
                  Master_UUID: 00007802-2222-2222-2222-222222222222
[...]
           Retrieved_Gtid_Set: 00007802-2222-2222-2222-222222222222:1-21
            Executed_Gtid_Set: 00007802-2222-2222-2222-222222222222:1-21
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node2
           Master_TLS_Version:
*************************** 3. row ***************************
[...]
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1177
               Relay_Log_File: mysql-relay-node3.000002
                Relay_Log_Pos: 1390
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
          Exec_Master_Log_Pos: 1177
              Relay_Log_Space: 1599
[...]
             Master_Server_Id: 103
                  Master_UUID: 00007803-3333-3333-3333-333333333333
[...]
           Retrieved_Gtid_Set: 00007803-3333-3333-3333-333333333333:1-4
            Executed_Gtid_Set: 00007803-3333-3333-3333-333333333333:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node3
           Master_TLS_Version:

Missing information in performance_schema tables

The idea of having replication information in performance schema is to replace SHOW SLAVE STATUS with a set of tables that report the same info. The theory is good, but the implementation is lacking.

First of all, the tables are misnamed. replication_applier_configuration has only settings about the applier delay, while replication_applier_status again reports only info about the remaining delay. The information worth looking at for monitoring is in a table named replication_connection_status, while it should refer to applier status or applier progress.

select * from replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: node1
               GROUP_NAME:
              SOURCE_UUID: 00007801-1111-1111-1111-111111111111
                THREAD_ID: 35
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 362
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-20 23:05:41
 RECEIVED_TRANSACTION_SET: 00007801-1111-1111-1111-111111111111:1-22
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
             CHANNEL_NAME: node2
               GROUP_NAME:
              SOURCE_UUID: 00007802-2222-2222-2222-222222222222
                THREAD_ID: 39
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 362
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-20 23:05:41
 RECEIVED_TRANSACTION_SET: 00007802-2222-2222-2222-222222222222:1-21
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
             CHANNEL_NAME: node3
               GROUP_NAME:
              SOURCE_UUID: 00007803-3333-3333-3333-333333333333
                THREAD_ID: 43
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 362
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-20 23:05:42
 RECEIVED_TRANSACTION_SET: 00007803-3333-3333-3333-333333333333:1-4
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

What is missing here is the applied data, i.e. what appears in @@global.gtid_executed, which, as said above, should be split by server. Moreover, we miss the corresponding binary logs and positions, which we find in some neglected tables in mysql databases and that should be removed, with their contents integrated here. Another thing that's missing are the schema and the timestamps of the latest transaction, both the creation and the replication times. All this information is valuable to the DBA when troubleshooting. Rather than opening binary logs to find out the details, they should be all collected in this table for ease of use.

How should it be instead

The monitoring tables should include information about:

• transaction timestamp;
• binary log and position (or what a future implementation would use to transport data);
• default schema.

Here's an example:

select * from replication_applier_progress\G
*************************** 1. row ***************************
             CHANNEL_NAME: node1
               GROUP_NAME:
              SOURCE_UUID: 00007801-1111-1111-1111-111111111111
                THREAD_ID: 35
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 362
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-20 23:05:41
    TRANSACTION_TIMESTAMP: 2016-09-20 22:24:13
          APPLY_TIMESTAMP: 2016-09-20 22:24:15
 RECEIVED_TRANSACTION_SET: 00007801-1111-1111-1111-111111111111:1-22
  APPLIED_TRANSACTION_SET: 00007801-1111-1111-1111-111111111111:1-22
          Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 6325
           Relay_Log_File: mysql-relay-node1.000002
            Relay_Log_Pos: 6538
    Relay_Master_Log_File: mysql-bin.000002
       applying_on_schema: db1
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
             CHANNEL_NAME: node2
               GROUP_NAME:
              SOURCE_UUID: 00007802-2222-2222-2222-222222222222
                THREAD_ID: 39
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 362
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-20 23:05:41
    TRANSACTION_TIMESTAMP: 2016-09-20 22:25:15
          APPLY_TIMESTAMP: 2016-09-20 22:25:18
 RECEIVED_TRANSACTION_SET: 00007802-2222-2222-2222-222222222222:1-21
  APPLIED_TRANSACTION_SET: 00007802-2222-2222-2222-222222222222:1-21
          Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 6039
           Relay_Log_File: mysql-relay-node2.000002
            Relay_Log_Pos: 6252
    Relay_Master_Log_File: mysql-bin.000002
       applying_on_schema: db2
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
             CHANNEL_NAME: node3
               GROUP_NAME:
              SOURCE_UUID: 00007803-3333-3333-3333-333333333333
                THREAD_ID: 43
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 362
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-20 23:05:42
    TRANSACTION_TIMESTAMP: 2016-09-20 22:28:16
          APPLY_TIMESTAMP: 2016-09-20 22:28:19
 RECEIVED_TRANSACTION_SET: 00007803-3333-3333-3333-333333333333:1-4
  APPLIED_TRANSACTION_SET: 00007803-3333-3333-3333-333333333333:1-4
          Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 1177
           Relay_Log_File: mysql-relay-node3.000002
            Relay_Log_Pos: 1390
    Relay_Master_Log_File: mysql-bin.000002
       applying_on_schema: db3
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

The same level of detail should be used for replication_applier_status_by_worker, which, by the way, has a more appropriate name than replication_connection_status.

Monitoring tables are in different places

MySQL 5.6 introduced replication tables in the mysql database. Unfortunately, this inclusion happened before GTID was implemented. As a result, we have tables in mysql database reporting log names and positions and tables in performance_schema reporting GTIDs, but without correlations.

How should it be instead

The replication tables in mysql should be removed, and integrated in the performance_schema as shown above.

There is no table for SHOW MASTER STATUS

The information in SHOW SLAVE STATUS has been translated almost entirely in performance_schema tables. No such thing happened to SHOW MASTER STATUS. To make monitoring more efficient, all the data used for monitoring should be in tables, and the information should not contain more than one piece of information per field, as we have seen in previous cases.

How should it be instead

There should be a table containing the information from SHOW MASTER STATUS, except the filter info, which should be in a different table:

select * from replication_master_status\G
SHOW MASTER STATUS\G
*************************** 1. row ***************************
                 File: mysql-bin.000002
             Position: 154
    Executed_Gtid_Set: 00007801-1111-1111-1111-111111111111:1-22
Transaction_timestamp: 2016-09-20 22:24:13

select * from replication_filters\G
*************************** 1. row ***************************
     Filter_Name: binlog_do_db
     Filter_Yype: binlog
    Filter_Value: personnel.*
   Filter_Status: ACTIVE

Call for action

Dear MySQL product managers: please take action to make replication more consistent and usable. I may have forgotten something, but I think the requests in this article are a good start to improve the design of MySQL replication.

The fastest MySQL Sandbox setup ever!

MySQL-Sandbox 3.1.11 introduces a new utility, different from anything I have put before in the MySQL Sandbox toolkit.

make_sandbox_from_url downloads a tiny MySQL tarball from a repository and install it straight away.

As of today, the following packages are available

Major release	versions	package size (what you download)	expanded size (storage used)	original size (not included)
5.0	5.0.96	20M	44M	371M
5.1	5.1.72	23M	59M	485M
5.5	5.5.50	15M	49M	690M
5.6	5.6.31	18M	61M	1.1G
5.7	5.7.13	33M	108M	2.5G

The sizes of the tarballs mentioned in the table above are much smaller than the original packages. The binaries have been stripped of debug info, compressed whenever possible, and purged of all binaries that are not needed for sandbox operations. This means that:

• You can download the needed tarball very fast;
• The storage needed for the binaries is reduced immensely.

Here is an example of the script in action. We download and install mySQL 5.0.96 in one go:

$ make_sandbox_from_url 5.0 -- --no_show
wget -O 5.0.96.tar.gz
'http://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata/5.0.96.tar.gz?raw=true'
URL transformed to HTTPS due to an HSTS policy
--2016-07-10 17:59:33--
https://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata/5.0.96.tar.gz?raw=true
Resolving github.com (github.com)... 192.30.253.112
Connecting to github.com (github.com)|192.30.253.112|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location:
https://github.com/datacharmer/mysql-docker-minimal/raw/master/dbdata/5.0.96.tar.gz
[following]
--2016-07-10 17:59:33--
https://github.com/datacharmer/mysql-docker-minimal/raw/master/dbdata/5.0.96.tar.gz
Reusing existing connection to github.com:443.
HTTP request sent, awaiting response... 302 Found
Location:
https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/5.0.96.tar.gz
[following]
--2016-07-10 17:59:34--
https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/5.0.96.tar.gz
Resolving raw.githubusercontent.com (raw.githubusercontent.com)...
151.101.12.133
Connecting to raw.githubusercontent.com
(raw.githubusercontent.com)|151.101.12.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 20052235 (19M) [application/octet-stream]
Saving to: ‘5.0.96.tar.gz’

5.0.96.tar.gz
100%[=================================================================================>]
19.12M  15.2MB/s    in 1.3s

2016-07-10 17:59:37 (15.2 MB/s) - ‘5.0.96.tar.gz’ saved [20052235/20052235]

    The MySQL Sandbox,  version 3.1.11
    (C) 2006-2016 Giuseppe Maxia
# Starting server
. sandbox server started
# Loading grants
Your sandbox server was installed in $HOME/sandboxes/msb_5_0_96

If you call the same command twice, you will get a message saying that you can now use make_sandbox x.x.xx to install your sandbox.

The script is doing what I should probably have done from the beginning by default: expands the tarball in $SANDBOX_BINARY (by default $HOME/opt/mysql) from where it is easy to reuse with minimum typing.

As of today, the binaries are Linux ONLY. I made this repository to use it with Docker (I will write about it soon) and that means using Linux. This is still part of an experiment that so far is working well. The project can either evolve in smarter directions or merge with clever containers. It's early to say. For now, enjoy the fastest set-up that MySQL Sandbox can offer!

MySQL Sandboxes in Docker

Overview

When I got interested in Docker, I started playing idly with the idea of integrating containers and MySQL Sandbox. My first experiments were not encouraging. Using a container the same way I would use a regular server produced horrible results. I started by creating a Debian or CentOS container, installing MySQL Sandbox, and then importing an expanded tarball into the container. What happens is that tarballs of recent MySQL versions expand to roughly 2 GB of binaries. When you try to put that into a container you get a bloated file system. If you want to expand more than one tarball, you get an enormous unusable blob that is contrary to what containers should be used for. There is, of course, the possibility of using volumes, which would avoid the problem of making the container too big, but would not make it easier for users. The current difficulty of MySQL Sandbox is its non-intuitiveness. I want to use containers to make things easier for users. Getting the tarballs for more than one version and using them transparently is possible, but not simple. This was my starting point.

Shrinking MySQL tarballs

I read two articles recently about reducing the size of a MySQL distribution. I hadn't given much thought to this issue, other than noticing that the binaries are taking more and more storage in my hosts. While this has not been a huge problem in my servers so far, it would be nice to save a few gigabytes in my laptop. So these two posts made me think again:

• Looking for the smallest possible MySQL Footprint
• Creating a minimal MySQL installation for embedded system

The procedure is simple. What you need to run a MySQL server in a sandbox is only a handful of files. For example, for MySQL 5.7.11, you need, from the ./bin directory:

3.5M my_print_defaults
4.4M mysql
4.1M mysqladmin
4.8K mysql_config
 25M mysqld
 26K mysqld_safe

This is about 33M. The sizes are small because I run strip on all binaries. Additionally, I also need some files from the ./share directory, from which I shave away all languages except English, leaving me with about 3.5M

To complete the usability of the sandbox, I need these files, which add 8.6M to the total.

4.4M mysqlbinlog
4.2M mysqldump

This brings the grand total for MySQL 5.7 to ~ 47M. Compared to the original size of the tarball at 2GB, it's an impressive reduction. However, I need to add something if I plan to use plugins in the sandbox. This requires the ./lib directory, with the contents of ./lib/plugin. This will double the size of the repository. But 90M is hardly a problem, compared to dealing with the size of original tarballs.

Of course, I have automated the process, with a file list for every version of MySQL. After the reduction, I got the following (sizes in MB):

Version	original size	reduced size
5.0	371	44
5.1	485	59
5.5	687	49
5.6	1100	61
5.7	2000	91

Building the images

My goal with using Docker in conjunction with MySQL Sandbox is to simplify the user experience, while keeping intact the advanced functionalities. After a few unsatisfactory attempts that allowed me to learn a few tricks and avoid pitfalls, I came up with the plan of producing three images:

• One with the MySQL Sandbox software and the libraries to support it;
• One with that also includes the MySQL tarballs compressed;
• And one that includes the tarballs expanded.

The main image mysql-sb-base

This image datacharmer/mysql-sb-base is the base for the others, but it can be used stand-alone with the tarballs already in your host computer. The image is relatively light, is based on Debian, and its size is 167 MB.

For example, you have expanded tarballs in $HOME/opt/mysql

$ ls $HOME/opt/mysql
5.6.28  5.7.11

$ docker run -ti --name sbox -v $HOME/opt/mysql:/opt/mysql datacharmer/mysql-sb-base bash
msandbox@3d4a8d9ca186:~$ make_sandbox 5.6.28 -- --no_show
The MySQL Sandbox,  version 3.1.05
(C) 2006-2016 Giuseppe Maxia
loading grants
. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_28
msandbox@3d4a8d9ca186:~$ ~/sandboxes/msb_5_6_28/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) >

Or, you may have a tarball in a directory:

$ ls $HOME/downloads
mysql-5.1.72-linux-x86_64-glibc23.tar.gz

$ docker run -ti --name sbox -v $HOME/downloads:/opt/mysql datacharmer/mysql-sb-base bash

msandbox@2b0a53ad8e71:~$ make_sandbox ~/opt/mysql/mysql-5.1.72-linux-x86_64-glibc23.tar.gz -- --no_show
unpacking /home/msandbox/opt/mysql/mysql-5.1.72-linux-x86_64-glibc23.tar.gz
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_1_72

msandbox@2b0a53ad8e71:~$ ~/sandboxes/msb_5_1_72/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.72 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) >

The intermediate image mysql-sb-gz

If you want to use the reduced tarballs that I have prepared, the less expensive image is datacharmer/mysql-sb-gz. It contains all the binaries mentioned above (from 5.0 to 5.7) still compressed. The image weighs 272 MB, and you can use the binaries with the help of a script that ships with the image.

$ docker run -ti --name sbox datacharmer/mysql-sb-gz bash

msandbox@b0672d141e3d:~$ ls
opt  setup.sh  README
msandbox@b0672d141e3d:~$ ./setup.sh
# expanding 5.0.96.tar.gz
# expanding 5.1.72.tar.gz
# expanding 5.5.48.tar.gz
# expanding 5.6.28.tar.gz
# expanding 5.7.11.tar.gz

msandbox@b0672d141e3d:~$ ls ~/opt/mysql/
5.0.96  5.1.72  5.5.48  5.6.28  5.7.11

msandbox@b0672d141e3d:~$ make_sandbox 5.7.11 -- --no_show
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_7_11
msandbox@b0672d141e3d:~$
msandbox@b0672d141e3d:~$ ~/sandboxes/msb_5_7_11/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) >

The complete image mysql-sb-full

The image that allows you to use a sandbox without any extra work is datacharmer/mysql-sb-full. This one contains the expanded binaries that are ready for consumption.

$ docker run -ti datacharmer/mysql-sb-full bash
msandbox@ed3cbbc088a7:~$ ls ~/opt/mysql
5.0.96  5.1.72  5.5.48  5.6.28  5.7.11  COPYING
msandbox@ed3cbbc088a7:~$ make_sandbox 5.5.48 -- --no_show
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_5_48
msandbox@ed3cbbc088a7:~$ make_replication_sandbox 5.6.28
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_28
msandbox@ed3cbbc088a7:~$ ~/sandboxes/use_all 'select version()'
version()
5.5.48
# master
version()
5.6.28-log
# server: 1:
version()
5.6.28-log
# server: 2:
version()
5.6.28-log
msandbox@ed3cbbc088a7:~$

What's next

This is a first attempt, and I am sure it can be improved. Things that come to mind, in no particular order:

• Making the container run a sandbox transparently in the host computer. This is already feasible, but not out-of-the-box. It will require a wrapper script.
• Adding binaries for more versions, such as Percona and MariaDB servers, or preview releases (MySQL Labs);
• Finding a way of adding binaries seamlessly, without making the images unnecessarily big;
• Getting the binaries from a centralised repository instead of shipping them with the image. This is also feasible, but not immediately reliable as of today. The ideal solution would be for Oracle, Percona, and MariaDB to release and maintain reduced binaries of every shipped version.

The code to create the images is already available on GitHub. Contributions are welcome!

Sound advice for GTID, with caveats

During the PerconaLive conference in Amsterdam, I attended a session where I heard a good piece of advice about using GTID. It amounts to: look at SHOW SLAVE STATUS output, and if you see more than one line in the Executed_Gtid_Set field, this tells you immediately if someone has written on a slave database.
This is good advice. Let's dissect it. Here is what a regular slave looks like, when nobody has messed up with it:

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
 [...]
             Master_Server_Id: 1
                  Master_UUID: 00013454-1111-1111-1111-111111111111
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
            Executed_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

What you see here is a slave that has received transactions from a single source (Retrieved_Gtid_Set lists only one GTID set) and has applied data from a single source (also Executed_Gtid_Set shows a single item.)
Notice that this advice holds true even when the slave being considered is an intermediate one, i.e. a relay slave which is master of one or more slaves. Due to the nature of GTIDs, even though the intermediate slave is recording the transactions to its own binary log, the transaction identifier does not change. Thus you should see a clean set of transactions throughout the chain. For example, if you have another slave that is replicating from slave #2, you would see something like this:

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
 [...]
             Master_Server_Id: 102
                  Master_UUID: 00013456-3333-3333-3333-333333333333
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
            Executed_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:

Improving Sakila database

The Sakila sample database was created almost 10 years ago, as a sample set of data for MySQL courses and examples.

The database was developed by MySQL employees, with substantial contributions form the community.

Recently, the database was updated to use some of the features in MySQL 5.7. As a result, we had two sets of samples, one to use with MySQL 5.0+, and one that only loads with MySQL 5.7.

I filed a feature request, offering a patch to use conditional schema and data changes, which was incorporated very quickly into the official release.

The current release, available within the MySQL docs, has conditional comments such as this:

/*!50610 ALTER TABLE film_text engine=InnoDB */ ;

Using these comments, we can enable specific features if the version is at least the one indicated in the comment. So, for example, we can use InnoDB tables with full-text indexes starting with version 5.6. The original table is MyISAM, but if the current version is at least 5.6.10 (that's the meaning of !50610) then the engine is changed to InnoDB.

>Similarly, there is a GEOMETRY column and SPATIAL key in the 'address' table, which are only enabled for MySQL 5.7.5+. A similar comment allows the loading of the relevant data only in MySQL 5.7.

Using these new files, you can install the Sakila database using any version of MySQL from 5.0 onwards, and it will always load correctly.

MySQL replication in action - Part 1: GTID & Co

In the theoretical part of this series, we have seen the basics of monitoring. In that article, though, we have barely mentioned the new tools available in MySQL 5.7 and MariaDB 10. Let’s start from something that has the potential of dramatically changing replication as we know it.

Crash-safe tables and Global transaction identifiers in MySQL 5.6 and 5.7

Global transaction identifiers (GTID) is a feature that has been in my wish list for long time, since the times I was working with the MySQL team. By the time I left Oracle, this feature was not even in the plans.
When MySQL 5.6 was first disclosed, the biggest improvement for replication was the introduction of crash-safe tables (see Status persistence in Monitoring 101.) There are two tables in the mysql database, named slave_master_info and slave_relay_log_info. At the beginning, these tables were using the MyISAM engine, thus defeating the purpose of making them crash-safe. In later versions, the developers decided to bite the bullet and create these tables with innodb from the beginning.
These two tables allow us to see the same information previously stored in the files master.info and relay_log.info. What makes these tables convenient is that they should survive a crash better than the standalone files.

MySQL replication monitoring 101

Replication is the process that transfers data from an active master to a slave server, which reproduces the data stream to achieve, as best as possible, a faithful copy of the data in the master.

To check replication health, you may start with sampling the service, i.e. committing some Sentinel value in the master and retrieving it from the slave.

Sentinel data: Tap tap… Is this thing on?

If you want to make sure that replication is working, the easiest test is using replication itself to see if data is being copied across from the master to the slaves. The method is easy:

1. Make sure that the data you want to see is NOT in the master or in the slave. If you skip this step, you may think that replication is working, while in fact it may not.
2. Either create a table in the master or use a table that you know exists both in the master and the slave.
3. Insert several records in the master table.
4. Check that they are replicated in the slave correctly.
5. Update a record in the master.
6. Watch it changing in the slave.
7. Delete a record in the master.
8. Watch it disappear in the slave.

MySQL defaults evolution

MySQL, the original brand, the one developed by the MySQL team at Oracle, is steadily evolving. You can feel it if you try every new release that comes out of the milestone release cycle. Or even if you don’t try all of them, just testing a release once in a while gives you something to think about.

The engineers at Oracle are trying hard to improve the defaults. If you are the out-of-the-box type, and just install the new version on top of the previous one, leaving the same setup in place, you may be up for a for a few surprises. It’s the marketing, see? They tell you that just by replacing your old MySQL (5.1 or 5.5) with MySQL 5.6 you get 30% to 70% performance improvement. Which happens to be true, not only because the server is better, but also because they have changed the defaults. However, this change in defaults may come with some serious consequences for the ones who skip the release notes.

An annoying consequence of the MySQL team focusing on security is that in MySQL 5.6 you get a mandatory warning if you use a password in the command line. On one hand, it’s a good thing, because they force you to use better security practices. On the other hand, it’s a royal PITA, because many applications are broken because of this warning, just by replacing MySQL 5.1 or 5.5 with 5.6. There are solutions. For example, you can adopt the new mysql_config_editor to handle your password, but that would break compatibility with previous MySQL versions. Rewriting complex procedures to use configuration files instead of username and passwords is tricky, especially if you are testing exactly the behavior of using a password on the command line to override the contents of an options file.

INTERMISSION: this is a point of contention with the MySQL team. They have started a trend of introducing features that will prevent working smoothly with previous versions of MySQL. Up to MySQL 5.5, installing a server and staring using it was a set of steps that would work in the same way regardless of the version. With MySQL 5.6, all bets are over. When you install a new server, you get a long list of unwanted messages to the error output (which is fortunately fixed in MySQL 5.7), and then you get the warning if you use a password on the command line. For me, and for many developers who build software related to MySQL, the ability of writing a piece of software that works well with any version is paramount. The MySQL team seems to think that users will be happy to throw everything to the wind and start writing new code for MySQL 5.6 only instead of reusing what was working until the month before. And let’s be clear: I fully understand the need of moving forward, but I don’t understand the need of trapping users in the new behavior without remedy.

Back to the defaults. What else is new? One good improvement in MySQL 5.6 is a change in the default value for SQL_MODE. Up to MySQL 5.5, it was an empty string. In MySQL 5.6.6 and later it is ‘NO_ENGINE_SUBSTITUTION.’ Can this change have side effects? Yes. Code that worked before may break. IMO, it’s a good thing, because getting an error when trying to create a table of a non-existing engine is better than having the table created silently with the default engine. I can, however, think of at least one case where a silent substitution is desirable, as I have seen in action at a customer’s deployment. That aside, one wonders why they did not go the extra mile and add STRICT_TRANS_TABLES (or even STRICT_ALL_TABLES) to the default. It turned out that they did it… and didn’t. When you install a new data directory using mysql_install_db, the procedure creates a my.cnf file in the $BASEDIR (the place where your mysql tarball was expanded), containing the line

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

However, if you have skipped the release notes, you will be left wondering where does this additional directive come from, since the manual mentions only one of them, and SHOW VARIABLES tells you that SQL_MODE contains two values.

MySQL 5.7 has also changed something. There is a lot of cleanup going on. Options and variables that were deprecated long ago suddenly disappear. Did you ever use ‘key-buffer’ as a short for ‘key-buffer-size’? If you did, that directive in the my.cnf won’t work anymore. (I could not find it in the release notes, but the test suite for MySQL Sandbox suddenly stopped working when I tried MySQL 5.7 and then I realized what was happening.) More to the point, though, is the installation procedure. In MySQL 5.6 there is a –random-passwords option that generates a random password for the server, and you can’t do anything with root until you use such random password to access the server and change the password to something else. This is an improvement over the old and despicable root without password, which has been the default since the very beginning of MySQL, and it’s been the source of many security nightmares and interesting bugs. In MySQL 5.7.4, this behavior, i.e. the generation of a random password during the installation, is now the default. It is good news, because the old behavior was a disaster, but if you have an automated way of dealing with installation, there will be more hard work in front of you to handle the changes. The implementation is not script friendly, and definitely nightmarish if you want to install several instances of the server in the same host. What happens when you install MySQL 5.7.4? The installation software generates a random password, and writes it to a file named .mysql_secret in your $HOME directory. A sample file that was created by the installer would be:

# The random password set for the root user at Mon Mar 31 10:16:54 2014 (local time):
2X7,S4PGkIg=H(lJ


EOF

If you wanted a script to read the password generated by this procedure, it would be a mistake to look for the second line. In fact, if you repeat the installation on the same host, you get something like this:

# The random password set for the root user at Mon Mar 31 10:16:54 2014 (local time):
2X7,S4PGkIg=H(lJ


# The random password set for the root user at Tue Apr 29 09:35:07 2014 (local time):
_S07zDt7dQ=,sxw9


# The random password set for the root user at Tue Apr 29 09:42:19 2014 (local time):
r[yn4ND$-5p,4q}5


EOF

Then the correct approach would be looking for the last non empty line in the file. However, if you were installing several instances in the same host (such as MySQL Sandbox does) you wouldn’t be able to find which password belongs to which server. Admittedly, multiple instances of the same server is not what most users do, but since it breaks MySQL Sandbox, which is used by many, I mention it here. BTW, MySQL Sandbox 3.0.44 has a temporary fix for this behavior. If you install MySQL 5.7.4 or later, it will include –skip-random-passwords, and defaults to the old password-less installation. There is a hackish workaround for the above uncompromising design and I will add it to MySQL Sandbox unless the next MySQL version introduces an optional path for the .mysql_secret file.

Summing up, there are mostly good improvements from the MySQL team, although the attention to usability is still low. They are whipping users into better security. A gentler approach would be appreciated. Sometimes I see a post from the community team asking for feedback on some feature being deprecated or modified. I would welcome such requests on matters that affect the default behavior of everyday tools.

MySQL Sandbox supports latest MySQL releases, has more metadata and docs

MySQL Sandbox has been updated again. The latest version is 3.0.38, which was just released. There were four releases in the space of one week, and this last one is just a polished edition.

Cherry-picking from the Change log:

• Added option --bind_address to complement the effects of --remote_access;
• The script 'enable_gtid' (for MySQL 5.6 +) now is durable. Previously the changes did not survive a restart.
• Now you can install MariaDB with its bizarre version '10.0'
• It also works well with MySQL 5.7. A bug prevented the creation of 'enable_gtid', but it is now fixed.
• Enhanced the 'check_slaves' script in replication sandboxes. Now it includes the output of 'show master status', so you can see at a glance if replication is working well;
• Added README file to each sandbox (simple or multiple)
• Added connection.json to each sandbox (simple or multiple) The file contains information to use the sandbox with third party applications.
• Added connection samples (for PHP, Perl, Python, Java, shell) to 'connection.json';
• Added about 80 new tests to the test suite. Now the package has more than 2,000 tests (~290 tests running 7 times, each with a different MySQL version)

Documentation and metadata

The changes that probably are most visible to users involve metadata and documentation.

When you install a single sandbox, you will find a README file, containing a summary of the actions that you can do with the sandbox, and instructions to find more information.

Composite sandboxes (replication, multiple, circular, custom) have their own customised README.

While README is a pleasant addition for first time users, the metadata files could be beneficial also to old timers. In each sandbox there are two files:

• connection.json
• default_connection.json

If it is a single sandbox, you will see something like this:

{
    "origin": {
        "mysql_sandbox_version" : "3.0.38",
        "mysql_version":    "5.6",
        "binaries":         "/Users/gmax/opt/mysql/5.6.11"
    },
    "connection": {
        "host":             "127.0.0.1",
        "port":             "5611",
        "socket":           "/tmp/mysql_sandbox5611.sock",
        "bind_address":     "127.0.0.1"
    },
    "users": {
       "admin": {
            "username":     "root@localhost",
            "password":     "msandbox",
            "privileges":   "all, with grant option"
        },
       "all_privileges": {
            "username":     "msandbox@127.%",
            "password":     "msandbox",
            "privileges":   "all, no grant option"
        },
        "read_write": {
            "username":     "msandbox_rw@127.%",
            "password":     "msandbox",
            "privileges":   "SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE"
        },
        "read_only": {
            "username":     "msandbox_ro@127.%",
            "password":     "msandbox",
            "privileges":   "SELECT,EXECUTE"
        },
        "replication": {
            "username":     "rsandbox@127.%",
            "password":     "rsandbox",
            "privileges":   "REPLICATION SLAVE"
        }
    },
    "samples": {
        "php": {
            "mysqli" : "$mysqli = new mysqli('127.0.0.1', 'msandbox', 'msandbox', 'test', '5611');",
            "pdo"    : "$dbh = new PDO('mysql:host=127.0.0.1;port=5531', 'msandbox', 'msandbox');"
        },
        "perl" : {
            "dbi" : "$dbh=DBI->connect( 'DBI:mysql:host=127.0.0.1;port=5611', 'msandbox', 'msandbox')"
        },
        "python" : {
            "mysql.connector" : "cnx = mysql.connector.connect(user='msandbox', password='msandbox', host='127.0.0.1', port=5611, database='test')"
        },
        "java" : {
            "DriverManager" : "con=DriverManager.getConnection(\\\"jdbc:mysql://127.0.0.1:5611/test\\\", \\\"msandbox\\\", \\\"msandbox\\\")"
        },
        "shell" : {
            "generic": "/Users/gmax/opt/mysql/5.6.11/bin/mysql -h 127.0.0.1 -P 5611 -u msandbox -pmsandbox"
        }
    }
}

The file contains most of the information needed to use a sandbox with third party applications, and even ready-made connection strings for some popular programming languages.

If you safe in a hurry, you may look at default_connection.json, instead:

{
    "host":     "127.0.0.1",
    "port":     "5611",
    "socket":   "/tmp/mysql_sandbox5611.sock",
    "username": "msandbox@127.%",
    "password": "msandbox"
}

In composite sandboxes, these files collect the json objects for the underlying sandboxes

$ cat ~/sandboxes/rsandbox_5_6_11/default_connection.json
{
"master":
    {
        "host":     "127.0.0.1",
        "port":     "18876",
        "socket":   "/tmp/mysql_sandbox18876.sock",
        "username": "msandbox@127.%",
        "password": "msandbox"
    }
,
"node1":
    {
        "host":     "127.0.0.1",
        "port":     "18877",
        "socket":   "/tmp/mysql_sandbox18877.sock",
        "username": "msandbox@127.%",
        "password": "msandbox"
    }
,
"node2":
    {
        "host":     "127.0.0.1",
        "port":     "18878",
        "socket":   "/tmp/mysql_sandbox18878.sock",
        "username": "msandbox@127.%",
        "password": "msandbox"
    }
}

There is another addition that was not emphasised before. In each single sandbox, there is a script named 'json_in_db'.

This script loads the contents of connection.json into the database server. This could be useful if you want to access the metadata through a SQL interface. I was also thinking of loading the same data into a table by default, but I did not want to introduce database objects without user acknowledgement. It's probably a behaviour that can be authorised with another installation option.

UPDATE: I have just found out that in the replication README, the head credits are included twice. Oh, well. I will fix it as soon as there are some more changes. Three releases in two days are enough.

Access sandboxes from other hosts

By default, a MySQL sandbox instance can be accessed by localhost only.

This access is regulated by an option, "--remote_access", which is set to '127.%', and it is used to create the default users. You can see the resulting instructions in the file 'grants.mysql' inside each sandbox.

grant all on *.* to msandbox@'127.%' identified by 'msandbox';
  grant all on *.* to msandbox@'localhost' identified by 'msandbox';
  grant SELECT,EXECUTE on *.* to msandbox_ro@'127.%' identified by 'msandbox';
  grant SELECT,EXECUTE on *.* to msandbox_ro@'localhost' identified by 'msandbox';
  grant REPLICATION SLAVE on *.* to rsandbox@'127.%' identified by 'rsandbox';

If you want to access the sandbox remotely, you can change "--remote_access" to include your specific subnet, or to open it completely:

--remote_access='192.168.1.%'
  --remote_access='%'

Starting with MySQL::Sandbox 3.0.34, you can also define the bind address for your MySQL server. By default it is '127.0.0.1'. It will be changed to '0.0.0.0' if you choose a customized --remote_access. If you change both --remote_access and --bind_address, No adjustment will be made.

Happy hacking!

Parallel replication and GTID - A tale of two implementations

MySQL 5.6 is probably the version of MySQL with the biggest bundle of new features. You may want to try it soon, since it's now released as GA, and I would like to offer some practical experience on how to use some of the advanced features.

Since replication is my main interest, I will focus on some of the new features in this field, and I will compare what's available in MySQL 5.6 with Tungsten Replicator.

The focus of the comparison is usability, manageability, and some hidden functionality. Parallel replication has been available with Tungsten Replicator for almost two years, and Global Transaction Identifiers for much longer than that. With MySQL 5.6, it seems that the MySQL team wants to close the gap. While the main feature (parallel execution threads) is available and performing well, there are some shocking differences in terms of ease of use, administration handles, and capabilities, that suggest at least poor integration into the server.

To test replication, I have enabled parallel workers and GTID in the slaves, and I ran 5 quick processes on separated databases. I repeated the operation with MySQL 5.6 and Tungsten Replicator. Let's see what I have found.

Monitoring

First of all, there is "SHOW SLAVE STATUS," which has been enhanced to include the GTID. Trouble is, this command was designed for a single replication channel, and when the GTID from several parallel threads are listed, the result is puzzling.

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 18675
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 386003338
               Relay_Log_File: mysql_sandbox18676-relay-bin.000006
                Relay_Log_Pos: 315698722
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 315698559
              Relay_Log_Space: 386003928
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 646
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: f6ea8d94-7147-11e2-a036-6c626da07446
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: f6ea8d94-7147-11e2-a036-6c626da07446:1-1089548
            Executed_Gtid_Set: f6ea8d94-7147-11e2-
a036-6c626da07446:1-891950:891952-891953:891955-891963:891965-891969:891971-891973:891976-891992:891994-8919
95:891997:891999:892001-892002:892005-892016:892019-892023:892025-892026:892028:892030-892032:892034-892039:
892041-892044:892046:892048-892051:892053-892054:892056-892063:892065-892072:892074-892084:892086-892089:892
091:892094:892096-892111:892113-892122:892124-892137:892139-892142:892144-892146:892148-892150:892152:892154
-892159:892161-892171:892173-892182:892184-892185:892187:892189-892193:892196-892198:892200-892206:892208:89
2210-892211:892213-892216:892218-892219:892222:892224-892226:892228:892230-892249:892251:892255:892257:89225
9-892260:892262-892263:892265:892268:892270-892288:892290:892292:892295-892298:892300-892307:892309:892312:8
92314-892317:892319-892320:892324-892328:892332-892336:892342:892344-892349:892352-892355:892359-892360:8923
62-892363:892369-892372:892376:892378-892384:892387:892390:892392:892394:892396-892397:892399:892401-892403:
892405-892407:892409:892413-892415:892417:892421:892423-892426:892429-892431:892433:892435:892437-892439:892
442:892445:892448
                Auto_Position: 0

After this abundant display of GTID (alas, without any reference to the transactions being processed), I was expecting something from the tables dedicated to replication monitoring. However, what is missing in these tables is any reference to GTIDs, and any context about the transactions. There is no indication of which schema the changes are happening, how well or bad distributed they are, whether there was the need for serialization, or other info.

select * from mysql.slave_worker_info\G
*************************** 1. row ***************************
                        Id: 1
            Relay_log_name: ./mysql_sandbox18676-relay-bin.000006
             Relay_log_pos: 91550519
           Master_log_name: mysql-bin.000002
            Master_log_pos: 91550356
 Checkpoint_relay_log_name: ./mysql_sandbox18676-relay-bin.000006
  Checkpoint_relay_log_pos: 91371802
Checkpoint_master_log_name: mysql-bin.000002
 Checkpoint_master_log_pos: 91371639
          Checkpoint_seqno: 508
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap: #####(unprintable characters replaced)#####
*************************** 2. row ***************************
                        Id: 2
            Relay_log_name: ./mysql_sandbox18676-relay-bin.000006
             Relay_log_pos: 91497101
           Master_log_name: mysql-bin.000002
            Master_log_pos: 91496938
 Checkpoint_relay_log_name: ./mysql_sandbox18676-relay-bin.000006
  Checkpoint_relay_log_pos: 91319665
Checkpoint_master_log_name: mysql-bin.000002
 Checkpoint_master_log_pos: 91319502
          Checkpoint_seqno: 501
     Checkpoint_group_size: 64
  ?! ?AA@oint_group_bitmap:  #####(unprintable characters replaced)#####
*************************** 3. row ***************************
                        Id: 3
            Relay_log_name: ./mysql_sandbox18676-relay-bin.000006
             Relay_log_pos: 91540831
           Master_log_name: mysql-bin.000002
            Master_log_pos: 91540668
 Checkpoint_relay_log_name: ./mysql_sandbox18676-relay-bin.000006
  Checkpoint_relay_log_pos: 91360379
Checkpoint_master_log_name: mysql-bin.000002
 Checkpoint_master_log_pos: 91360216
          Checkpoint_seqno: 511
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap: #####(unprintable characters replaced)#####
*************************** 4. row ***************************
                        Id: 4
            Relay_log_name: ./mysql_sandbox18676-relay-bin.000006
             Relay_log_pos: 91461999
           Master_log_name: mysql-bin.000002
            Master_log_pos: 91461836
 Checkpoint_relay_log_name: ./mysql_sandbox18676-relay-bin.000006
  Checkpoint_relay_log_pos: 91281068
Checkpoint_master_log_name: mysql-bin.000002
 Checkpoint_master_log_pos: 91280905
          Checkpoint_seqno: 510
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap: #####(unprintable characters replaced)#####
*************************** 5. row ***************************
                        Id: 5
            Relay_log_name: ./mysql_sandbox18676-relay-bin.000006
             Relay_log_pos: 91373959
           Master_log_name: mysql-bin.000002
            Master_log_pos: 91373796
 Checkpoint_relay_log_name: ./mysql_sandbox18676-relay-bin.000006
  Checkpoint_relay_log_pos: 91192982
Checkpoint_master_log_name: mysql-bin.000002
 Checkpoint_master_log_pos: 91192819
          Checkpoint_seqno: 508
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap: #####(unprintable characters replaced)#####

The table dedicated to parallel replication doesn't give us any information about GTID. And it doesn't say in which schema the transaction is happening. This lack of information, for a feature that is based on schema isolation, is quite startling.

So, we have a look at the crash-safe slave tables.

select * from mysql.slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: mysql-bin.000002
        Master_log_pos: 385282815
                  Host: 127.0.0.1
             User_name: rsandbox
         User_password: rsandbox
                  Port: 18675
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca: 
            Ssl_capath: 
              Ssl_cert: 
            Ssl_cipher: 
               Ssl_key: 
Ssl_verify_server_cert: 0
             Heartbeat: 1800
                  Bind: 
    Ignored_server_ids: 0
                  Uuid: f6ea8d94-7147-11e2-a036-6c626da07446
           Retry_count: 86400
               Ssl_crl: 
           Ssl_crlpath: 
 Enabled_auto_position: 0

select * from mysql.slave_relay_log_info\G
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./mysql_sandbox18676-relay-bin.000006
    Relay_log_pos: 235324325
  Master_log_name: mysql-bin.000002
   Master_log_pos: 235324162
        Sql_delay: 0
Number_of_workers: 5
               Id: 1

What's wrong with this picture? Two main things:

• There is no mention of GTID in these tables either. So, if you are using GTID, your only option is to look at the binary logs, and see if you get any solace when a failover occurs. But the server itself does not offer any help.
• There is only one row available for the master and relay log info. When you are using parallel replication, this means that either every commit must lock the same row, or these tables are useless for keeping the slave crash safe. If the information used for crash safe comes from the parallel workers table, then the crash safe tables are redundant. But as you can see on your own, the parallel workers table has less information than master_info and relay_log_info.

Thus, we have a look at how GTID and parallel threads are implemented in Tungsten.

Even limiting the information to the tables found in the server, we find several differences:

• The GTID is displayed in every row. (It's the column named seqno). The original binlog name and position are also preserved and associated with the GTID (in the column named eventid.)
• Every row displays in which schema it is being processed. (It's the column named shard_id)
• There is no separated table for crash-safe info and parallel threads. When Tungsten uses a single thread, the table has only one row. When it is using parallel appliers, then there are multiple rows, one for each applier. This way, there is no possibility of deadlock, as every thread locks only one row.

select * from tungsten_tsandbox.trep_commit_seqno
+---------+---------+--------+-----------+-----------+--------------+-------------------------------------+-----------------+---------------------+------------+---------------------+
| task_id | seqno   | fragno | last_frag | source_id | epoch_number | eventid                             | applied_latency | update_timestamp    | shard_id   | extract_timestamp   |
+---------+---------+--------+-----------+-----------+--------------+-------------------------------------+-----------------+---------------------+------------+---------------------+
|       0 | 2218566 |      0 | 1         | 127.0.0.1 |            0 | mysql-bin.000002:0000000330476211;0 |               1 | 2013-02-07 23:12:07 | evaluator1 | 2013-02-07 23:12:06 |
|       1 | 2218521 |      0 | 1         | 127.0.0.1 |            0 | mysql-bin.000002:0000000330469587;0 |               1 | 2013-02-07 23:12:07 | evaluator2 | 2013-02-07 23:12:06 |
|       2 | 2220380 |      0 | 1         | 127.0.0.1 |            0 | mysql-bin.000002:0000000330747639;0 |               1 | 2013-02-07 23:12:07 | evaluator3 | 2013-02-07 23:12:06 |
|       3 | 2219360 |      0 | 1         | 127.0.0.1 |            0 | mysql-bin.000002:0000000330594617;0 |               1 | 2013-02-07 23:12:07 | evaluator4 | 2013-02-07 23:12:06 |
|       4 | 2221220 |      0 | 1         | 127.0.0.1 |            0 | mysql-bin.000002:0000000330873205;0 |               0 | 2013-02-07 23:12:07 | evaluator5 | 2013-02-07 23:12:07 |
+---------+---------+--------+-----------+-----------+--------------+-------------------------------------+-----------------+---------------------+------------+---------------------+

You can see the same information in vertical output, to appreciate the level of detail.

select * from tungsten_tsandbox.trep_commit_seqno\G
*************************** 1. row ***************************
          task_id: 0
            seqno: 2257934
           fragno: 0
        last_frag: 1
        source_id: 127.0.0.1
     epoch_number: 0
          eventid: mysql-bin.000002:0000000336350320;0
  applied_latency: 9
 update_timestamp: 2013-02-07 23:12:26
         shard_id: evaluator1
extract_timestamp: 2013-02-07 23:12:17
*************************** 2. row ***************************
          task_id: 1
            seqno: 2257938
           fragno: 0
        last_frag: 1
        source_id: 127.0.0.1
     epoch_number: 0
          eventid: mysql-bin.000002:0000000336350875;0
  applied_latency: 9
 update_timestamp: 2013-02-07 23:12:26
         shard_id: evaluator2
extract_timestamp: 2013-02-07 23:12:17
*************************** 3. row ***************************
          task_id: 2
            seqno: 2257936
           fragno: 0
        last_frag: 1
        source_id: 127.0.0.1
     epoch_number: 0
          eventid: mysql-bin.000002:0000000336350648;0
  applied_latency: 9
 update_timestamp: 2013-02-07 23:12:26
         shard_id: evaluator3
extract_timestamp: 2013-02-07 23:12:17
*************************** 4. row ***************************
          task_id: 3
            seqno: 2257916
           fragno: 0
        last_frag: 1
        source_id: 127.0.0.1
     epoch_number: 0
          eventid: mysql-bin.000002:0000000336347768;0
  applied_latency: 9
 update_timestamp: 2013-02-07 23:12:26
         shard_id: evaluator4
extract_timestamp: 2013-02-07 23:12:17
*************************** 5. row ***************************
          task_id: 4
            seqno: 2257933
           fragno: 0
        last_frag: 1
        source_id: 127.0.0.1
     epoch_number: 0
          eventid: mysql-bin.000002:0000000336350156;0
  applied_latency: 9
 update_timestamp: 2013-02-07 23:12:26
         shard_id: evaluator5
extract_timestamp: 2013-02-07 23:12:17

In addition to the information that you can get from the database server, Tungsten has its own tools that provide information about the process. For example, trepctl status -name stores provides, among other things, detailed information about how many transactions were processed, accepted, and rejected by each channel, and if there was any collision that caused serialization.

trepctl status -name stores
Processing status command (stores)...
NAME                      VALUE
----                      -----
activeSeqno             : 2267132
doChecksum              : false
flushIntervalMillis     : 0
fsyncOnFlush            : false
logConnectionTimeout    : 28800
logDir                  : /home/tungsten/tsb2/db3/tlogs/tsandbox
logFileRetainMillis     : 604800000
logFileSize             : 100000000
maximumStoredSeqNo      : 2267230
minimumStoredSeqNo      : 0
name                    : thl
readOnly                : false
storeClass              : com.continuent.tungsten.replicator.thl.THL
timeoutMillis           : 2147483647
NAME                      VALUE
----                      -----
criticalPartition       : -1
discardCount            : 0
estimatedOfflineInterval: 0.0
eventCount              : 2267140
headSeqno               : 2267140
maxOfflineInterval      : 5
maxSize                 : 10
name                    : parallel-queue
queues                  : 5
serializationCount      : 0
serialized              : false
stopRequested           : false
store.0                 : THLParallelReadTask task_id=0 thread_name=store-thl-0 hi_seqno=2267140 lo_seqno=1 read=2267140 accepted=460813 discarded=1806327 events=0
store.1                 : THLParallelReadTask task_id=1 thread_name=store-thl-1 hi_seqno=2267140 lo_seqno=1 read=2267140 accepted=452812 discarded=1814328 events=0
store.2                 : THLParallelReadTask task_id=2 thread_name=store-thl-2 hi_seqno=2267140 lo_seqno=1 read=2267140 accepted=450812 discarded=1816328 events=0
store.3                 : THLParallelReadTask task_id=3 thread_name=store-thl-3 hi_seqno=2267140 lo_seqno=1 read=2267140 accepted=450397 discarded=1816743 events=0
store.4                 : THLParallelReadTask task_id=4 thread_name=store-thl-4 hi_seqno=2267140 lo_seqno=1 read=2267141 accepted=452306 discarded=1814834 events=0
storeClass              : com.continuent.tungsten.replicator.thl.THLParallelQueue
syncInterval            : 10000
Finished status command (stores)...

The same tool can show what is happening for each shard. When you are dealing with many channels, you need to know who is doing what, and sometime with a high level of detail. This is especially useful when things go wrong. Knowing what has happened to your transaction, and at which stage it stopped working could be a great help to fix problems. You can follow every transaction using either the GTID (appliedLastSeqno), or the binlog+position (appliedLastEventId), combined with the schema where it is happening (shardId.)

trepctl status -name shards
Processing status command (shards)...
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338943258;0
appliedLastSeqno  : 2275303
appliedLatency    : 18.942
eventCount        : 243189
shardId           : evaluator1
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338940934;0
appliedLastSeqno  : 2275288
appliedLatency    : 18.935
eventCount        : 231993
shardId           : evaluator10
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338939386;0
appliedLastSeqno  : 2275277
appliedLatency    : 18.931
eventCount        : 234687
shardId           : evaluator2
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338938221;0
appliedLastSeqno  : 2275269
appliedLatency    : 18.928
eventCount        : 231045
shardId           : evaluator3
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338942040;0
appliedLastSeqno  : 2275295
appliedLatency    : 18.933
eventCount        : 225755
shardId           : evaluator4
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338942486;0
appliedLastSeqno  : 2275298
appliedLatency    : 18.937
eventCount        : 221864
shardId           : evaluator5
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338942930;0
appliedLastSeqno  : 2275301
appliedLatency    : 18.94
eventCount        : 219574
shardId           : evaluator6
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338943372;0
appliedLastSeqno  : 2275304
appliedLatency    : 18.931
eventCount        : 220078
shardId           : evaluator7
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338942600;0
appliedLastSeqno  : 2275299
appliedLatency    : 18.929
eventCount        : 221647
shardId           : evaluator8
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000338940109;0
appliedLastSeqno  : 2275282
appliedLatency    : 18.931
eventCount        : 226607
shardId           : evaluator9
stage             : q-to-dbms
Finished status command (shards)...

And if the info by shard is not enough, we can also see the functioning of replication in terms of tasks. For the current transactions, we can check what is happening at every stage, with a level of detail that the MySQL server cannot provide.

trepctl status -name tasks
Processing status command (tasks)...
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339749071;0
appliedLastSeqno  : 2280695
appliedLatency    : 22.267
applyTime         : 53.543
averageBlockSize  : 1.000     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339749071;0
currentLastFragno : 0
currentLastSeqno  : 2280695
eventCount        : 2280695
extractTime       : 3053.902
filterTime        : 0.194
otherTime         : 2.864
stage             : remote-to-thl
state             : extract
taskId            : 0
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339747579;0
appliedLastSeqno  : 2280685
appliedLatency    : 22.233
applyTime         : 135.079
averageBlockSize  : 9.983     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339747579;0
currentLastFragno : 0
currentLastSeqno  : 2280685
eventCount        : 2280685
extractTime       : 2972.509
filterTime        : 0.167
otherTime         : 2.714
stage             : thl-to-q
state             : extract
taskId            : 0
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339747579;0
appliedLastSeqno  : 2280685
appliedLatency    : 22.243
applyTime         : 174.149
averageBlockSize  : 9.813     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339747579;0
currentLastFragno : 0
currentLastSeqno  : 2280685
eventCount        : 463769
extractTime       : 2930.461
filterTime        : 4.553
otherTime         : 1.317
stage             : q-to-dbms
state             : extract
taskId            : 0
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339746639;0
appliedLastSeqno  : 2280679
appliedLatency    : 22.247
applyTime         : 173.182
averageBlockSize  : 9.790     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339746639;0
currentLastFragno : 0
currentLastSeqno  : 2280679
eventCount        : 455841
extractTime       : 2931.707
filterTime        : 4.374
otherTime         : 1.219
stage             : q-to-dbms
state             : extract
taskId            : 1
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339747249;0
appliedLastSeqno  : 2280683
appliedLatency    : 22.246
applyTime         : 171.505
averageBlockSize  : 9.819     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339747249;0
currentLastFragno : 0
currentLastSeqno  : 2280683
eventCount        : 453846
extractTime       : 2933.467
filterTime        : 4.28
otherTime         : 1.23
stage             : q-to-dbms
state             : extract
taskId            : 2
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339747415;0
appliedLastSeqno  : 2280684
appliedLatency    : 22.252
applyTime         : 168.773
averageBlockSize  : 9.792     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339747415;0
currentLastFragno : 0
currentLastSeqno  : 2280684
eventCount        : 453447
extractTime       : 2936.219
filterTime        : 4.283
otherTime         : 1.213
stage             : q-to-dbms
state             : extract
taskId            : 3
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000002:0000000339746311;0
appliedLastSeqno  : 2280677
appliedLatency    : 22.25
applyTime         : 167.426
averageBlockSize  : 9.804     
cancelled         : false
currentLastEventId: mysql-bin.000002:0000000339746311;0
currentLastFragno : 0
currentLastSeqno  : 2280677
eventCount        : 454922
extractTime       : 2937.622
filterTime        : 4.188
otherTime         : 1.25
stage             : q-to-dbms
state             : extract
taskId            : 4
Finished status command (tasks)...

Fixing an error

Let's try to see what happens with a simple, classic replication error. We will enter a record in the slave, and then do the same in the master. We will assume full knowledge of the facts, and try to repair the damage.

In MySQL 5.6, when the slave stops replication because of the duplicate key error, we get this:

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 18675
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 136120191
               Relay_Log_File: mysql_sandbox18676-relay-bin.000006
                Relay_Log_Pos: 136120097
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t1 values (1)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 136119934
              Relay_Log_Space: 136120781
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t1 values (1)'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: c3054160-7b4a-11e2-a17e-6c626da07446
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 130220 11:49:56
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: c3054160-7b4a-11e2-a17e-6c626da07446:1-386581
            Executed_Gtid_Set: c3054160-7b4a-11e2-a17e-6c626da07446:1-386580,
c90226d2-7b4a-11e2-a17e-6c626da07446:1
                Auto_Position: 0

In this situation, we try the old remedy of skipping a transaction.

set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

Oops! Now I have two problems:

• How do I generate an empty transaction with a given GTID?
• Which is the given GTID? I get several ones:

  show global variables like '%gtid_executed%'\G
  *************************** 1. row ***************************
  Variable_name: gtid_executed
          Value: c3054160-7b4a-11e2-a17e-6c626da07446:1-386580,
  c90226d2-7b4a-11e2-a17e-6c626da07446:1

I found this manual page about skipping transactions, but I don't get where I should run these instructions. I tried running them in the slave, but the replication error remains in place. I guess we need a clearer example that explains how to fix the error, because this surely does not look like a solution.

Let's try the same issue with Tungsten.

$ trepctl status
Processing status command...
NAME                     VALUE
----                     -----
appliedLastEventId     : NONE
appliedLastSeqno       : -1
appliedLatency         : -1.0
channels               : -1
clusterName            : default
currentEventId         : NONE
currentTimeMillis      : 1361356976587
dataServerHost         : 127.0.0.1
extensions             : 
latestEpochNumber      : -1
masterConnectUri       : thl://127.0.0.1:12110/
masterListenUri        : thl://127.0.0.1:12111/
maximumStoredSeqNo     : -1
minimumStoredSeqNo     : -1
offlineRequests        : NONE
pendingError           : Event application failed: seqno=444898 fragno=0 message=java.sql.SQLException: Statement failed on slave but succeeded on master
pendingErrorCode       : NONE
pendingErrorEventId    : mysql-bin.000002:0000000116727315;0
pendingErrorSeqno      : 444898
pendingExceptionMessage: java.sql.SQLException: Statement failed on slave but succeeded on master
                         insert into t1 values (1) /* ___SERVICE___ = [tsandbox] */
pipelineSource         : UNKNOWN
relativeLatency        : -1.0
resourcePrecedence     : 99
rmiPort                : 10102
role                   : slave
seqnoType              : java.lang.Long
serviceName            : tsandbox
serviceType            : unknown
simpleServiceName      : tsandbox
siteName               : default
sourceId               : 127.0.0.1
state                  : OFFLINE:ERROR
timeInStateSeconds     : 18.747
uptimeSeconds          : 923.323
version                : Tungsten Replicator 2.0.7 build 278
Finished status command...

The error tells me that GTID 444898 is at fault. The remedy that I would apply in this case is the same that I would do anyway, regardless of parallel replication.

$ trepctl online -skip-seqno 444898

$ trepctl status
Processing status command...
NAME                     VALUE
----                     -----
appliedLastEventId     : mysql-bin.000002:0000000116726621;0
appliedLastSeqno       : 444893
appliedLatency         : 0.0
channels               : 5
clusterName            : default
currentEventId         : NONE
currentTimeMillis      : 1361358074668
dataServerHost         : 127.0.0.1
extensions             : 
latestEpochNumber      : 0
masterConnectUri       : thl://127.0.0.1:12110/
masterListenUri        : thl://127.0.0.1:12111/
maximumStoredSeqNo     : 444898
minimumStoredSeqNo     : 0
offlineRequests        : NONE
pendingError           : NONE
pendingErrorCode       : NONE
pendingErrorEventId    : NONE
pendingErrorSeqno      : -1
pendingExceptionMessage: NONE
pipelineSource         : thl://127.0.0.1:12110/
relativeLatency        : 1599.668
resourcePrecedence     : 99
rmiPort                : 10102
role                   : slave
seqnoType              : java.lang.Long
serviceName            : tsandbox
serviceType            : local
simpleServiceName      : tsandbox
siteName               : default
sourceId               : 127.0.0.1
state                  : ONLINE
timeInStateSeconds     : 2.076
uptimeSeconds          : 2021.404
version                : Tungsten Replicator 2.0.7 build 278
Finished status command...

And we are back in business. Since GTID in Tungsten are well integrated in every aspect of the operations, I can use them to fix problems.

More loose ends

What we've seen above shows that the integration between existing replication features and the new ones is less than desirable. There are more issues that may come to the user's attention. One that struck me as very odd is Replicate_Ignore_Server_Ids

This value is found in 'SHOW SLAVE STATUS', and it designates the list of servers that were excluded from replication. This value is associated to the option 'IGNORE_SERVER_IDS' that you can give in CHANGE MASTER TO. What's odd with this value? It is that it only accepts server-IDs, not server-UUIDs. If you try to use a server-UUID in CHANGE MASTER TO, you get an error.

However, the reason for mentioning this variable is that, just by chance, I was able to "fix" the above problem, instead of using an empty transaction. I did the following:

stop slave;
change master to ignore_server_ids=(1);
start slave;
show slave status\G
# no errors
stop slave;
change master to ignore_server_ids=();
start slave;

And we have skipped a transaction in an undocumented way. Bug or feature?