You are here

Shinguz's blog

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.

Situation

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

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

Analysis

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 (mysql_bman)

About

The MySQL Backup Manager (mysql_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.

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

Galera Arbitrator (garbd)

Taxonomy upgrade extras: 

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

Galera Cluster for MySQL and hardware load balancer

Our bigger customers where we help to deploy Galera Cluster for MySQL set-ups have some commercial hardware (e.g. F5 or Cisco) for load balancing instead of software load balancers.

To UNION or not to UNION...

Recently a forum question [ 1 ] got my attention:

Is there any performance issue with Union?

I used union all sometime back and it was performance issue just to make an opinion that we should used union in query.

MySQL and Secure Linux (SELinux)

Maybe you experienced some strange behaviour with MySQL: Everything is installed correctly and should work. But it does not.

Symptoms we have seen:

Unbreakable MySQL Cluster with Galera and Linux Virtual Server (LVS)

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 need you: MySQL DBA for FromDual Support line

FromDual is looking for professional, enthusiastic and experienced people who:

Switching from MySQL/MyISAM to Galera Cluster

Taxonomy upgrade extras: 

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.

Block MySQL traffic for maintenance windows

From time to time some maintenance work on the MySQL database has to be done. During the maintenance window we do not want to have application traffic on the database.

Sometimes it is hard to shut down all applications spread over the whole company. Or we want to allow only some specific hosts to access mysql from remote (for example the monitoring system or the backup server).

For this purpose we can use the Linux packet filtering.

To see what packet filtering rules are available we can run the following command:

Bootstrapping Galera Cluster the new way

Taxonomy upgrade extras: 

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.

Privileges of MySQL backup user for mysqldump

Taxonomy upgrade extras: 

Some MySQL customers do not want to use the root user for mysqldump backups. For this user you have to grant the following minimal MySQL privileges:

mysqldump --single-transaction (InnoDB)

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secret';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

mysqldump --lock-all-tables (MyISAM)

GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';

If we missed a privilege please let us know.

Shrinking InnoDB system tablespace file ibdata1 PoC

Taxonomy upgrade extras: 

In this weeks MySQL workshop we were discussing, beside other things, about the innodb_file_per_table parameter and its advantages of enabling it. In addition there was a discussion if the InnoDB system tablespace file can be shrinked once it has been grown very large or not. We all know the answer: The InnoDB system tablespace file does never shrink again.

Last login of MySQL database users

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

MySQL backup to file, gzip and load in one step

Taxonomy upgrade extras: 

When a MySQL Slave is set-up with mysqldump you have 2 possibilities:

Resize XFS file system for MySQL

Important: Before you start any operation mentioned below do a proper file system backup of your XFS file system you want to resize. If MySQL is running on this mount point do this with a stopped mysqld. Alternatively you can also use mysqldump to do the MySQL backup but test the restore time before continuing to not experience ugly surprises...

All these operations have to be performed as the root user. First we want to see what mount points are available:

Pages

Subscribe to RSS - Shinguz's blog