Galera

In MariaDB master/slave replication there is a feature called attribute promotion/demotion.

Simply put, it is about how the slave behaves or should behave if the master and slave have different column definitions or even a different number of columns or a different sequence of columns.

Use case of the customer

This week we discussed with a customer the case of how he could perform a rolling schema upgrade (RSU) in a Galera cluster.

With previous schema changes he has always had problems, which has led to a …

Recently I had the opportunity to have a lot of Linux systems (VMs with Rocky Linux 9) from one of our regular Galera Cluster trainings all to myself for a week. And MariaDB 11.4.4 with Galera Cluster was already installed on the machines.

Since I had long wanted to try out how a Galera Cluster behaves with an increasing number of nodes, now was the opportunity to try it out.

The following questions were to be answered:

• How does the throughput of a Galera cluster behave depending on the number of Galera …

Table of Contents

• SystemD Notizen
• InnoDB NUMA interleave with MariaDB
• Checking Galera Cluster SST timeout with SystemD
• Kill MariaDB Galera node under SystemD
• MariaDB Memory locking (--memlock) with SystemD
• Who is restarting my mariadbd?
• Location of SystemD unit files

SystemD Notizen

SystemD paged per default nach less. Das ist ungewohnt und doof, weil es gegen allgemeines Linux-Verhalten verstösst. Abhilfe schafft:

shell> systemctl --no-pager status
shell> alias systemctl='systemctl --no-pager'

Das …

Currently we are in the testing phase of the next release of our Ops Center (focmm). This testing is quite complex because it includes testing the interaction of various different components like a MariaDB Galera Cluster, a Galera Load Balancer, a virtual IP, etc.

Recently I was in the Linuxhotel for a Galera Cluster Training and there one of the other trainers was romanticising about Linux Containers and Incus. So I had a short look, if Incus could be useful to simplify our unit testing. Some old knowledge …

Sometimes customers want to have a very simple Galera Cluster set-up. They do not want to invest into machines and build up the know-how for load balancers in front of the Galera Cluster.

For this type of customers there is a possibility to just run a VIP controlled by Corosync/Pacemaker in front of the Galera Cluster moving an IP address from one node to the other. But this is just an active/passive/passive set-up and reads and writes are only possible to one node at the time.
So you loose the scaling …

We see quite often customers complaining that their Galera Cluster is not stable and “crashes” from time to time. As always one has to investigate before rating.

What comes out quite often is that the customer (or better their developers) are running huge transactions.

In general transactional database do NOT like huge transactions because of various reasons (MVCC, ROLLBACK, UNDO, Locking etc.). They can do it. But they are not quite good in doing it and they do not like it. Instead you should …

Source: Github: codership / documentation

Codership Blog for Galera Cluster with release announcements.

Galera Plugin version 4 Series

Galera plug-in 26.4.21 Release Notes, Release Date: 12 December 2024

Galera plug-in 26.4.20 Release Notes, Release Date: 2 August 2024

Galera plug-in 26.4.19 Release Notes, Release Date: 1 July 2024

Galera plug-in 26.4.18 Release Notes, Release Date: 26 March 2024

Galera plug-in 26.4.17 Release Notes, Release Date: 8 January 2024

Galera plug-in 26.4.16 Release Notes, Release …

Because we conduct many customers in MariaDB Galera Cluster upgrades and because these customers sometimes have pretty old MariaDB Galera Cluster set-ups I think it is good to have a rough MariaDB Galera Cluster Upgrade Path.

For an Upgrade Path we have to consider a few things:

• We face different MariaDB Galera Cluster version (5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5 and soon 10.6).
• We face different Galera plug-in versions (v2, v3 and v4). Direct upgrade from v2 to v4 is not possible. Upgrade from latest …

Table of Contents

• General findings
• Migration from MySQL 5.x to MariaDB 5.5
• Migration from MySQL 5.0 to MariaDB 10.3
• Migration from MariaDB 10.0 to Percona Server 5.6
• Migration from MySQL/Percona 5.6 to MariaDB 10.0
• Migration from MySQL 5.7 to MariaDB 10.2
• Migration from MySQL 5.7 to MariaDB 10.3
• Migration from MySQL 5.7 to MariaDB 10.4
• Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
• Migration from MySQL 5.7 to MariaDB 10.5
• Migration from MySQL 5.7 to MariaDB 10.6
• Migration from …

FromDual offers short term a Percona XtraDB Cluster and MySQL Galera Cluster training (2 days) in English.

The training will take place in the Linuxhotel in Essen/Germany on February 7/8 2018.

There are already enough registrations so it is certain the training will take place. But there are still free places for some additional participants.

You can book online at the Linuxhotel.

Important: The Linuxhotel is nearly fully booked out. So accommodation is in nearby locations. The Linuxhotel will recommend you …

We offer our MySQL and MariaDB trainings together with well-respected training organizations: The Linuxhotel in Essen (Germany), the Heinlein Academy in Berlin (Germany) and GFU Cyrus AG in Cologne (Germany). Our own MySQL and MariaDB training classes are hold in the building of the HSO in Zürich (Switzerland).

If you prefer a MySQL or MariaDB in-house training at your location and if you provide the infrastructure we are happy to train you on-site either in German or English as well.

Further dates or dates …

If and how to use Galera Cluster for MySQL has been described already by many others…

In this article we focus on the question: When is Galera Cluster NOT the right solution for you?

These thoughts also apply to Percona XtraDB Cluster (PXC) and MariaDB Galera Cluster.

• Galera Cluster currently supports only InnoDB (and TokuDB?) tables properly. You should avoid to use MyISAM or MEMORY tables. Otherwise data consistency is not guaranteed.
• It is wise to use Galera Cluster only if your software vendor …

We typically suggest our customers to use our MySQL/Galera Cluster my.cnf configuration template to avoid MySQL configuration and performance problems.

And we are paranoid as well. Thus we enable all useful logging:

wsrep_log_conflicts = 1

But this has also some consequences of more visibility…

If you monitor carefully your Galera Cluster for example with the FromDual Performance Monitor for MySQL and MariaDB, you might probably see some strange values increasing from time to time:

mysql< SHOW …

From time to time we see at Galera Cluster customer engagements the following, for me scary, warning in the MySQL error log:

[Warning] WSREP: last inactive check more than PT1.5S ago (PT7.06159S), skipping check

We mostly see this in VMware set-ups. Some further enquiry with the Galera developers did not give a satisfying answer:

This can be seen on bare metal as well - with poorly configured mysqld, O/S, or simply being overloaded. All it means is that this thread could not get CPU time for 7.1 seconds. …

We have recently seen a case where the following command was executed on a Galera Cluster node:

SQL> GRANT SUPER ON userdb.* TO root@127.0.0.111;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

2014-12-09 14:53:55 7457 [Warning] Did not write failed 'GRANT SUPER ON userdb.* TO root@127.0.0.111' into binary log while granting/revoking privileges in databases.
2014-12-09 14:53:55 7457 [ERROR] Slave SQL: Error 'Incorrect usage of DB GRANT and GLOBAL PRIVILEGES' on query. Default …

I wanted to install MySQL Galera Cluster 5.6 on the new Ubuntu 14.04 with provided packages. It was a bit tricky to make it work.

apt-get update
apt-get install mysql-server   # this is still 5.5
# from universe
apt-get install mysql-server-5.6 mysql-client-5.6 mysql-client-core-5.6

So far so good. Everything works fine as MySQL 5.6.

Now we have to download Galera 5.6 from Coderships website:

wget https://launchpad.net/galera/3.x/25.3.5/+download/galera-25.3.5-amd64.deb
wget …

FromDual tools provide valuable additional functionality which facilitate and optimize daily operations of your MySQL databases. Since our last newsletter a lot of things have changed in the FromDual tools.

To the MySQL Environment (MyEnv) numerous improvements and suggestions of our customers were added. The most important changes were introduced to the MySQL Backup Manager (mysql_bman).

With the MySQL Ops Center we meet the wishes of our numerous customers, which desire a graphical user interface for …

Sometimes it could be desirable to replicate from a Galera Cluster to a single MySQL slave or to an other Galera Cluster. Reasons for this measure could be:

• An unstable network between two Galera Cluster locations.
• A separation of a reporting slave and the Galera Cluster so that heavy reports on the slave do not affect the Galera Cluster performance.
• Mixing different sources in a slave or a Galera Cluster (fan-in replication).

This article is based on earlier research work (see MySQL Cluster - Cluster …

We are very satisfied with the functionality and the launch. The entire Cluster is running absolutely stable. This is a significant advantage for our customers because we are able to offer our services without interruption. We will use Galera in further projects and convert our existing Master/Slave installations.

A. Rempening, Datenbank-Administrator, KiKxxl GmbH

Download this case study: KiKxxl Gmbh: Highly available communication services based on a MySQL database and Galera Cluster (PDF, 420 kbyte).

Hello,

there are companies which earn tens of thousands of Euros per hour with their MySQL databases. Other companies operate their ERP system on MySQL, to which 1000 employees are attached to. Is the database down 1000 people are not working any more until the system is working again! Downtime costs starting at EUR 30'000.- per hour upwards.

Support through the MySQL specialists?

These companies have properly planned operations of their MySQL databases, designed their production systems redundantly and …

Features

Galera is a synchronous multi-master replication Cluster for MySQL/InnoDB databases, having features like:

• Synchronous replication
• Active-active multi-master topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel replication, on row level
• Direct client connections, native MySQL look & feel

Benefits

The benefits of these features are:

• No slave lag
• No lost transactions
• Read scalability and write …

Table of Contents

• Introduction
• Prerequisites
  • System information
  • Installed packages
  • Required packages
• Upgrade the first node
  • Upgrade the installed binaries
  • Upgrade mysql schema
  • Prepare the node to join the cluster
  • Joining the cluster
• Rolling upgrade the other nodes
• Get rid of old release option

Introduction

Codership announced from weeks ago introducing the Galera Cluster new release 3.0 having many bug fixes, performance enhancements plus the main purpose which is working with MySQL 5.6. In this …

About

glbd is a simple TCP connection balancer made with scalability and performance in mind. It was inspired by pen, but unlike pen its functionality is limited only to balancing generic TCP connections.

Features:

• list of backend servers is configurable in runtime.
• supports server draining, i.e. does not allocate new connections to server, but does not kill existing ones, waiting for them to end gracefully.
• can use epoll API provided by Linux version 2.6 and higher for ultimate routing performance.
• glb is …

It took me quite a while to find out how the beast Galera Arbitrator (garbd) works. To safe your time here a short summary:

How to start Galera Arbitrator (garbd)

shell> ./garbd --address gcomm://192.168.13.1,192.168.13.2 --group "Our Galera Cluster" --log /tmp/garbd.log --daemon

How to stop Galera Arbitrator (gardb)

shell> killall garbd

How to start Galera Arbitrator (garbd) with a configuration file

shell>./garbd --cfg /tmp/garb.cnf --daemon

The configuration file looks as follows:

# …

Table of Contents

• Accounts not properly closing connections
• Unused indexes
• Who created temporary (disk) tables
• Accounts which never connected since last start-up
• Users which never connected since last start-up
• Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
• SHOW FULL PROCESSLIST
• Storage Engines per schema
• Tables without a Primary Key
• Bad SQL queries of users
• SHOW PROFILE in PERFORMANCE_SCHEMA
• SELECT, INSERT, UPDATE and …

Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.

We used the wsrep_notify_cmd variable to hook our own script into the Galera Cluster which disables each Node on the Load Balancer when its state changed.

# my.cnf
#
[mysqld] …

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script (alter_engine.pl) long time ago already. Because we have made many of those switches recently we have extended its functionality.

New features

• Recognizes VIEW’s and does NOT try to alter their Storage Engine (bug).
• Script is MySQL version aware. Complain if …

A while ago it was pretty inconvenient to start a complete Galera Cluster from scratch. Rolling restart an such things are already working well but bootstrapping was a pain.

With Galera v2.2 new functionality came in. We tried it out and it did not work as documented. :-( Thanks to Teemu’s help we found there was a documentation bug in the Galera documentation.

The settings which were working for us are:

wsrep_cluster_address = "gcomm://192.168.1.2,192.168.1.3?pc.wait_prim=no"

And when all …

Hallo MySQL Gemeinde,

Im neuen iX (2012-10) hat es zwei nette Artikel über: MySQL HA-Lösungen (S. 116) und MySQL 5.6 (S. 66).

Viel Spass beim Lesen!

Based on customer feedback we have decided to add a plugin Galera Cluster for MySQL to our MySQL Nagios/Icinga Plugins.

The module checks, if the node is in status Primary and if the expected amount of Galera Cluster nodes is available. If not, a warning or an alarm is returned.

The script is written in Perl and is Nagios Plugin API v3.0 compatible.

You can download it from our download page.

If you have suggestions for improvements, please contact us. Bugs can be reported at our bugs database.

The …

During and after Henriks great talk about Galera Cluster at the FrOSCon 2012 in St. Augustin we found 2 important things related to Galera Cluster for MySQL:

• The InnoDB double write buffer (innodb_doublewrite) should not be disabled anymore for Galera when using v2.0 and higher!!! The reason for this is: When MySQL crashes InnoDB pages might get corrupted during the crash. They would be fixed by the blocks from the double write buffer during auto-recovery. But if the double write buffer is disabled they …

On Tuesday, September 4, 2012 the next DOAG SIG MySQL meeting will take place in Hamburg (Germany) with the topic Replication.

Possible presentations are: MySQL Replication, Galera Cluster, Replication with Oracle GoldenGate, Replication with Zimory Scale and Tungsten Replicator.

The event will presumably be located at the Hotel Böttcherhof at Wöhlerstrasse 2.

Dear MySQL User,

With this newsletter we bring you up to date about MySQL and our Services.

If you are not interested in this newsletter or if you are the wrong recipient, you can unsubscribe here or even better send this newsletter to the right person.

There are some news about the following topics:

• High-Availability(HA) with Galera Cluster for MySQL
• MySQL Performance Monitoring (MPM)
• MySQL Training in Essen and Berlin (Germany)
• 3^rd Level Support for MySQL
• Security holes in MySQL
• Technical information …

Table of Contents

• Building the Galera Load Balancer
• Starting the Galera Load Balancer
• Querying the Galera Load Balancer
• Draining nodes with Galera Load Balancer
• Removing nodes from and adding nodes to Galera Load Balancer

When you install a Galera Cluster for MySQL for High Availability (HA) it is not enough to install the Database Cluster to achieve this goal. You also have to make the application aware of this HA functionality. This is typically done with some kind of load balancing mechanism between …

On April 2^nd 2012 FromDual released the new version v0.9 of its Performance Monitor for MySQL (mpm). The new version can be downloaded from here.

The Performance Monitor for MySQL (mpm) is an agent which is hooked into Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

New functionality

• A new server module gathers MySQL database specific server informations. This is especially interesting for the Monitoring …

Helsinki, Finland, Uster, Switzerland – February 18, 2012 – Codership, the provider of Galera Cluster for MySQL, and FromDual, a MySQL consulting company, today announced collaboration to offer Galera Cluster technology and related support and consulting services for Galera users all over the world, especially in German speaking countries Germany, Austria and Switzerland (DACH). Galera Cluster is a synchronous, true multi-master replication cluster for MySQL using the well known InnoDB storage engine. …

In my set-ups I have different MySQL versions (MySQL 5.0, 5.1, 5.5 and 5.6, Percona Server 13.1 and 24.0, MariaDB 5.2.10, 5.3.3, Galera 1.0, 1.1 and 2.0) running in parallel at the same time.

Up to now I have not found a practical way yet to do this with RPM or DEB packages. If anybody knows how to do it I am happy to hear about it.

So I love and need only binary tar balls. Installation and removal is done within seconds and no remainings are left over after a removal. To operate the whole I use myenv.

Some …

A few days ago Codership announced their new version Galera v1.1 - synchronous Replication Cluster for MySQL. Before we look at the new feature of Rolling Online Schema Upgrade (OSU) we have a look at how to upgrade to the new Galera release.

A rolling upgrade of your synchronous Galera Replication Cluster from version 1.0 to 1.1 is quite easy when you stay at the same MySQL version (5.5).

To not lose the availability of your database service during the upgrade you should have at least 3 Galera nodes in …

On December 2^nd 2011 FromDual released the new version v0.7.2 of its Performance Monitor for MySQL. The new version can be downloaded from here.

The Performance Monitor for MySQL is an agent which is hooked into the agent of the well known Monitoring solution called Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

New functionality

• A new module for Galera was added (mainly Galera 0.8).
• A new module for the …

Introduction

Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:

• Master-Master Replication with MySQL
• MySQL Cluster
• The non-open source product called Schooner

Very often they can be easily replaced by Galera’s synchronous Multi-Master Replication for MySQL.

All those products have some advantages and disadvantages. Very often MySQL Master-Master Replication is used in the field because of its simplicity to set-up. But …

Introduction

MySQL/Galera synchronous Multi-Master Replication consists of 2 parts:

• The wsrep patches for MySQL (codership-mysql) and
• the Galera Replication Plugin (galera).

If you do not want to download the prepared binaries you can build it on you own.
First you have to download the native MySQL sources, then patch it with the Galera wsrep patches and compile it. In a second step you have to build the Galera Plugin.

This is especially useful because in the standard Galera binary tar balls the garbd …

Galera features

Galera provides the following features:

• Synchronous replication
• Active/active multi-master topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel row level replication
• Direct client connections
• Drop-in replacement for native MySQL

Galera benefits

Benefits using Galera Replication:

• High Availability
• No slave lag
• No lost transactions
• No more data inconsistency
• Smaller client latencies
• Read scalability …

FromDual is a support partner of MariaDB plc, Codership Oy and Oracle Corporation. This gives us the opportunity to offer you customized enterprise support subscriptions for your mission-critical MariaDB, Galera or MySQL databases for all three vendors.

Alternatively, we can offer you our remote DBA services (without SLA) or our MariaDB, Galera and MySQL trainings.

MariaDB and MariaDB Cluster Support Subscriptions

• MariaDB Enterprise support subscription: 7x24 support subscription, duration: 1 to 3 years, …

Often asked but informations are spread around: The limitations of MySQL.

If you know any other MySQL limitations, please let us know.

Table of Contents

• General limitations of MySQL
• Limitations of MySQL 4.1
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of MySQL 5.0
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of InnoDB
• Limitations of MySQL 5.1
• Limitations of Joins
• Limitations of Partitions
• Limitations of MySQL Cluster
• Limitations in Galera Cluster …