You are here

Shinguz's blog

Max_used_connections per user/account

How many connections can be opened concurrently against my MySQL or MariaDB database can be configured and checked with the following command:

| Variable_name   | Value |
| max_connections | 505   |

If this limit was ever reached in the past can be checked with:

FromDual Backup Manager for MySQL 1.2.2 has been released

FromDual has the pleasure to announce the release of the new version 1.2.2 of the popular Backup Manager for MySQL and MariaDB (fromdual_bman).

You can download the FromDual Backup Manager from here.

Wir suchen Dich: MySQL/MariaDB DBA für FromDual Support

Taxonomy upgrade extras: 

Wer sind wir?

FromDual ist das führende unabhängige Beratungs- und Dienstleistungsunternehmen für MySQL, Galera Cluster, MariaDB und Percona Server in Europa mit Hauptsitz in der Schweiz.

Controlling worldwide manufacturing plants with MySQL

A MySQL customer of FromDual has different manufacturing plants spread across the globe. They are operated by local companies. FromDuals customer wants to maintain the manufacturing receipts centralized in a MySQL database in the Head Quarter in Europe. Each manufacturing plant should only see their specific data.


Manufacturing log information should be reported backup to European Head Quarter MySQL database.

Logging Galera Cluster conflicts

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...

Galera Cluster last inactive check and VMware snapshots

Taxonomy upgrade extras: 

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:

Rename MySQL Partition

Taxonomy upgrade extras: 

Before I forget it and have to search again here a short note about how to rename a MySQL Partition:

My dream:

ALTER TABLE history RENAME PARTITION p2015_kw10 INTO p2015_kw09;

In reality:
PARTITION p2015_kw09 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-02 00:00:00'))

Caution: REORGANIZE PARTITION causes a full copy of the whole partition!

MySQL Enterprise Incremental Backup simplified

MySQL Enterprise Backup (MEB) has the capability to make real incremental (differential and cumulative?) backups. The actual releases are quite cool and you should really look at it...

Unfortunately the original MySQL documentation is much too complicated for my simple mind. So I did some testing and simplified it a bit for our customers...

Creating Event Handlers with MySQL Enterprise Monitor

MySQL Enterprise Monitor (MEM) has by default no Event Handlers created and activated. These Event Handlers you have to define yourself according to your needs.

Nagios and Icinga plug-ins for MySQL 1.0.0 have been released

FromDual has the pleasure to announce the release of the new version 1.0.0 of its widely used Nagios and Icinga plug-ins for MySQL, Galera Cluster, MariaDB and Percona Server.

All plug-ins are basically renewed and should now work all correctly.

The new Nagios/Icinga plug-ins can be downloaded here.

Download MySQL Enterprise Features

MySQL provides some great enterprise features beside the MySQL Server. The ones we are asked the most at customers are:

MySQL table Point-in-Time-Recovery from mysqldump backup

Sometimes we face the situation where we have a full MySQL database backup done with mysqldump and then we have to restore and recover just one single table out of our huge mysqldump file.
Further our mysqldump backup was taken hours ago so we want to recover all the changes on that table since our backup was taken up to the end.

In this blog article we cover all the steps needed to achieve this goal for MySQL and MariaDB.

Impacts of max_allowed_packet size problems on your MySQL database

We recently run into some troubles with max_allowed_packet size problems during backups with the FromDual Backup/Recovery Manager and thus I investigated a bit more in the symptoms of such problems.

Read more about: max_allowed_packet.

Avoid temporary disk tables with MySQL

Taxonomy upgrade extras: 

For processing SELECT queries MySQL needs some times the help of temporary tables. These temporary tables can be created either in memory or on disk.

The number of creations of such temporary tables can be found with the following command:

Making HAProxy High Available for MySQL Galera Cluster

After properly installing and testing a Galera Cluster we see that the set-up is not finished yet. It needs something in front of the Galera Cluster that balances the load over all nodes.

failed MySQL DDL commands and Galera replication

Taxonomy upgrade extras: 

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

Get rid of wrongly deleted InnoDB tables

Taxonomy upgrade extras: 

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.


A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*


We do some analysis first:

Migration between MySQL/Percona Server and MariaDB

This week, we did some migrations from MariaDB 10.0 to Percona Server 5.6 at the IT department of a big German bank.

We were perfectly aware that since version 10.0 the MariaDB code base started to diverge slightly away from the MySQL and Percona Server code base which are still pretty close to each other.

Because of the Percona Server option enforce_storage_engine we wanted to do this migration.

Replication channel fail-over with Galera Cluster for MySQL

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:

Backup Manager for MySQL, MariaDB and Percona Server (fromdual_bman)


The FromDual Backup Manager for MySQL and MariaDB(fromdual_bman) is a wrapper script for standard MySQL backup tools. The Problem with MySQL backup tools is, that they have many options and thus are overcomplicated and errors are easy made.

fromdual_bman has the intention to make backups for MySQL easier and technically correct. This means it should per default not allow non-consistent backups or complain if some functions or parameters are used in the wrong way to guarantee proper backups.

MySQL single query performance - the truth!

MySQL single query performance - the truth!

As suggested by morgo I did a little test for the same query and the same data-set mentioned in Impact of column types on MySQL JOIN performance but looking into an other dimension: the time (aka MySQL versions).

The answer

To make it short. As a good consultant the answer must be: "It depends!" :-)

Impact of column types on MySQL JOIN performance

Taxonomy upgrade extras: 

In our MySQL trainings and consulting engagements we tell our customers always to use the smallest possible data type to get better query performance. Especially for the JOIN columns.

Galera Cluster 3.1 GA is out!

Great News: Galera Cluster v3.1 GA for MySQL 5.6 was released at Percona Live London (PLUK) 2013. The information is still a bit hidden...

You can find it here:

Murphy’s Law is also valid for Galera Cluster for MySQL

We had a Galera Cluster support case recently. The customer was drenched in tears because his Galera Cluster did not work any more and he could not make it work any more.

Upsss! What has happened?

A bit of the background of this case: The customer wanted to do a rolling-restart of the Galera Cluster under load because of an Operating System upgrade which requires a reboot of the system.

Lets have a look at the MySQL error log to see what was going on. Customer restarted server with NodeC:

Huge amount of TIME_WAIT connections

In MySQL we have the typical behaviour that we open and close connections very often and rapidly. So we have very short-living connections to the server. This can lead in extreme cases to the situation that the maximum number of TCP ports are exhausted.

The maximum number of TCP ports we can find with:

# cat /proc/sys/net/ipv4/ip_local_port_range
32768   61000

In this example we can have in maximum (61000 - 32768 = 28232) connections concurrently open.


Subscribe to RSS - Shinguz's blog