Galera Cluster using GTID: MySQL vs. MariaDB

| Keine Kommentare | Keine TrackBacks

Using GTID to attach an asynchronous Slave sounds promising. Lets have a look at the two existing GTID implementations and their integration with Galera.

GTID@MariaDB

There is one GTID used by the cluster and every node increments the common seqno by itself. This works well as long all transactions are replicated by Galera (simplified InnoDB). Because Galera takes care of the Commit Order of the transactions on all nodes. So having identical GTID/seqno from the start there are no problems.

  node1> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

  node2> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

  node3> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

But think about having a DML not replicated by Galera. Lets assume we write into a MyISAM/MEMORY table on node1. Then only the seqno of node1 is increased:

  node1> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-505 |
  +-----------------+---------+

  node2> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

  node3> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

Galera does not care about the different seqno on the hosts. The next transaction replicated by Galera increases the seqno of all nodes by 1:

  node1> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-506 |
  +-----------------+---------+

  node2> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-505 |
  +-----------------+---------+

  node3> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-505 |
  +-----------------+---------+

So we have different mapping between GTID@MariaDB and GTID@Galera on the different hosts. This is far from optimal.

Having that situation think about a slave switching the Master. You will do one of two outcomes:

  • Loosing transactions
  • Reapplying transactions

If you are lucky replication fails. As the data is inconsistent.

So it is up to you to make sure you have only DML’s on the cluster, being replicated by Galera only.

GTID@MySQL/Percona

MySQL-Galera integration looks different:

GTID@MySQL uses server_uuid combined with the seqno to build its GTID. Galera makes a little trick in using a separate servre_uuid for transactions written/replicated by Galera. All other transactions use the original server_uuid of the server.

Let’s have a look on one node:

 node2> show global variables like 'gtid_executed';
 +---------------+-------------------------------------------------+
 | Variable_name | Value                                           |
 +---------------+-------------------------------------------------+
 | gtid_executed | 6d75ac01-ed37-ee1b-6048-592af289b902:1-10,
 933c5612-12c8-11e4-82d2-00163e014ea9:1-6 |
 +---------------+-------------------------------------------------+

6d75ac01-ed37-ee1b-6048-592af289b902 ist die server_uuid für Galera. 933c5612-12c8-11e4-82d2-00163e014ea9 ist die server_uuid für alle anderen Transaktionen.

So lets write into an InnoDB table:

node2> node2> show global variables like 'gtid_executed';
+---------------+--------------------------------------------------+
| Variable_name | Value                                            |
+---------------+--------------------------------------------------+
| gtid_executed | 6d75ac01-ed37-ee1b-6048-592af289b902:1-11,
933c5612-12c8-11e4-82d2-00163e014ea9:1-6 |
+---------------+--------------------------------------------------+

node1>  show global variables like 'gtid_executed';
+---------------+--------------------------------------------------+
| Variable_name | Value                                            |
+---------------+--------------------------------------------------+
| gtid_executed | 6c7225e2-12cc-11e4-8497-00163e5e2a58:1-2,
6d75ac01-ed37-ee1b-6048-592af289b902:1-11 |
+---------------+--------------------------------------------------+

And into a MyISAM table:

node2> show global variables like 'gtid_executed';
+---------------+------------------------------------------------------+
| Variable_name | Value                                                |
+---------------+------------------------------------------------------+
| gtid_executed | 6d75ac01-ed37-ee1b-6048-592af289b902:1-11,
933c5612-12c8-11e4-82d2-00163e014ea9:1-7 |
+---------------+------------------------------------------------------+

So because of the distinction between the writesets. All Galera data has the same GTID@MySQL to GTID@Galera mapping.

While there is still the possibility replication will break because the non-Galera replication ;)

Resumé

As long as all your data is handled by Galera Replication both GTID integrations should work fine. By no later than you do an Rolling Upgrade (mysql_upgrade) you are lost using GTID@MariaDB.

For GTID@MariaDB imho it would be a good idea to have a separate domainid for galera only and the ability to filter on the slave on that domainid.

Viel Spaß

Erkan :)

Keine TrackBacks

TrackBack-URL: http://linsenraum.de/mt/mt-tb.cgi/338

Jetzt kommentieren

Über diese Seite

Diese Seite enthält einen einen einzelnen Eintrag von erkan vom 29.07.14 0:02.

MariadB Galera: Attaching an asynchronous Slave using GTID ist der vorherige Eintrag in diesem Blog.

Aktuelle Einträge finden Sie auf der Startseite, alle Einträge in den Archiven.

Seiten

Powered by Movable Type 4.23-en