One year ago, I blogged about Evaluation and Online Migration of MySQL 5.6 GTIDs.  At that time, we setup the following test environment where:

A is a production master with GTIDs disabled, D to Z are standard slaves with GTIDs disabled, B is an intermediate master running my recompiled version of MySQL implementing the ANONYMOUS_IN-GTID_OUT mode (see the details my previous post), C is a slave

This past week was marked by a series of personal findings related to the use of Global Transaction IDs (GTIDs) on Galera-based clusters such as Percona XtraDB Cluster (PXC). The main one being the fact that transactions touching MyISAM tables (and FLUSH PRIVILEGES!) issued on a giving node of the cluster are recorded on a GTID set bearing the node’s server_uuid as “source id” and added to the binary log (if the node has binlog enabled), thus being replicated to any async replicas connected to it. However, they won’t be replicated across the cluster (that is, all of this is by design, if wsrep_replicate_myisam is …

Multi-threaded replication is a new feature introduced in MySQL 5.6 and MariaDB 10.0. In traditional single-threaded replication, the slaves have a disadvantage as they have to process in sequence what a master executed in parallel. This, plus the fact that slaves usually have a lot of read-only connections to deal with as well, can easily create performance problems. That is, a single-threaded slave needs to be set to allow fewer connections, otherwise there’s a higher risk of it not being able to keep up with the replication stream. There is no exact rule for this, as it relates to general I/O capacity and fsync latency, as well as general CPU and RAM considerations and query …

Martin Arrieta and I gave an online presentation last week on “Putting MySQL Fabric To Use.” If you missed it, you can find a recording and the slides here, and the vagrant environment we used plus a transcript of the commands we ran here (be sure to check out the ‘sharding’ branch, as that’s what we used during the webinar).

Thank you all for attending and asking interesting questions. We were unable to answer all of them in the scheduled time, so here are our replies to all the questions.

What is GTID? And how does it relate to MySQL Fabric?
GTID stands for Global …

Jean-François talks about binlog servers. Take a look here: http://blog.booking.com/mysql_slave_scaling_and_more.html

MariaDB 10.0.11 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.11/

This is the second GA release of MariaDB 10.0, and 12th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

1. Updated TokuDB engine to version 7.1.6
2. Updated Spider storage engine to version 3.2 (now Gamma)
3. Updated XtraDB storage engine to version 5.6.17-65.0
4. Updated InnoDB storage engine to version 5.6.17
5. Updated …

I have previously written about the new replication protocol that comes with GTIDs in MySQL 5.6. Because of this new replication protocol, you can inadvertently create errant transactions that may turn any failover to a nightmare. Let’s see the problems and the potential solutions.

In short

• Errant transactions may cause all kinds of data corruption/replication errors when failing over.
• Detection of errant transactions can be done with the GTID_SUBSET() and GTID_SUBTRACT() functions.
• If you find an errant transaction on one …

One of the MySQL 5.6 features many people are interested in is Global Transactions IDs (GTIDs). This is for a good reason: Reconnecting a slave to a new master has always been a challenge while it is so trivial when GTIDs are enabled. However, using GTIDs is not only about replacing good old binlog file/position with unique identifiers, it is also using a new replication protocol. And if you are not aware of it, it can bite.

Replication protocols: old vs new

The old protocol is pretty straightforward: the slave connects to a given binary log file at a specific offset, and the master sends all the transactions from there.

The new protocol is slightly different: the slave first sends the range of GTIDs it has executed, and then the master sends every missing transaction. It also guarantees that a transaction with a given …

While at the keynote of Tomas Ulin at Percona Live MySQL Conference & Expo Santa Clara 2014, he asked the audience what they were running, and most of the audience was on MySQL 5.5 while about 15% of the audience was on MySQL 5.6. This number is steadily increasing I’m sure, so one thing that becomes important is that people will probably start turning on Global Transaction Identifiers (GTIDs). 

As you may already know, MariaDB 10 has a different implementation of Global Transaction ID. To me, this poses a problem in a mixed use environment (or even a migration scenario). Which is why …

MySQL 5.6 introduced a new feature called GTID (Global Transaction IDentifier) support in Replication. For every transaction that is committed on to the server, a GTID of the format :

server_uuid:transaction_id is written into the master's binary log.

This offers the following advantages:

• Very helpful to set up a slave and create a replication setup.

• User need not worry about fetching the master's binlog filename and position in the “CHANGE MASTER TO” command which is used to synchronise the slave with the master.

• Applying GTIDs on slaves ensures consistency – since GTIDs are unique, it cannot be applied more than once on the server.

For a gtid enabled server, the following properties need to be set on both Master and Slave configuration files as shown below in …