The latest from the MySQL community

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 …

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

https://downloads.mariadb.org/mariadb/10.0.10/

This is the first GA ("Generally Availability", aka "recommended for production systems") release of MariaDB 10.0, and 11th overall release of MariaDB 10.0.

Since this is the initial 10.0 GA release, this is primarily a bug-fix and polishing release.

Here are the main items of note:

read more

A colleague and I have been looking at GTID on MySQL recently and you may be interested in the blog post that results from that. You can see it here. http://blog.booking.com/mysql-5.6-gtids-evaluation-and-online-migration.html.

March 3, 2014 By Severalnines

Hybrid replication, i.e. combining Galera and asynchronous MySQL replication in the same setup, became much easier with MySQL 5.6 and GTID. Although it was fairly straightforward to replicate from a standalone MySQL server to a Galera Cluster, doing it the other way round (Galera → standalone MySQL) was a bit more challenging. At least until MySQL 5.6 and GTID. 

There are a few good reasons to attach an asynchronous slave to a Galera Cluster. For one, long-running reporting/OLAP type queries on a Galera node might slow down an entire cluster, if the reporting load is so intensive that the node has to spend considerable effort coping with it. So reporting queries can be sent to a standalone server, effectively isolating Galera from the reporting load. In a belts and suspenders approach, an …

Will be giving a lightning talk at Percona Live 2014 – santa clara :)

In the world of replication, The Binlog is every ones favorite. 

But what about the Relay log, Don't you feel it can do more ?

Yes, It can. How about, if it can serve the purpose of the GTID 

feature without GTID.

https://www.percona.com/live/mysql-conference-2014/sessions/relay-log-unsung-hero

#MySQL  ( alternative to GTID )  #gtid

Yesterday I’ve put some new features into the ansible’s mysql_replication module, because we are planning to move to GTID based replication from the good old binlog position based one, and the module wasn’t aware of.

gtid_replication

This parameter can be either 0 or 1, defaults to 0. If set to 1 the replication will be threaded as GTID based replication.

warnings_filtered

This parameter threats the warnings, because MySQL 5.6 complaints a lot more than the previous versions. (For example, if the replication is not encrypted with SSL/TLS.) This could break our playbooks, so you can set it to all, warnings, none (defaults none). Speaks for itself, all means all warnings/errors will be shown, if warnings set, then only the errors will be shown, and the warnings supressed, and if none then that means, every message will be show …

We are in the middle of switching to GTID based replication from the good old logfile & log position based replication.

But what is GTID? GTID is an abbreviation of ‘GLOBAL TRANSACTION ID’ what speaks for itself: each transaction of a mysql cluster got its globally unique transaction ID, and the DBA have not spend time with positioning slaves, as well as we don’t have to ‘freeze’ any of the servers because of a master failover. The only thing we have to care about: to know what server should be used as a replication master.

OK, what was the problem with the old file-based replication?

Check out this scenario: you have two master servers, called db-master1 and db-master2 and two slaves db-slave1 db-slave2. All the writes are happening on db-master1 and this will replicating to the slaves and the failover master too.

So you have your replication set up, and everything works well. But there is a point when …