We’ve all been through downtime at some point and deplore the lost work and productivity that comes with it. And the only thing worse than server downtime is having to report that downtime to frustrated customers who are depending on your service for their business.

Yesterday, AWS server issues meant downtime for highly trafficked sites Instagram, Vine, Airbnb, and IFTTTA. According to AWS’s health dashboard, the company’s North Virginia data center may be to blame.

The fact is, any cloud system is subject to outages, from Yahoo Mail to Microsoft BPOS and VMware; however, outages aren’t and shouldn’t be business as normal.

While we never wish for anyone to experience downtime, when stories like AWS hit the …

Dear MySQL users,

MySQL Connector/Net 6.5.7, a new version of the all-managed .NET driver for MySQL has been released.  This is a maintenance release for 6.5.x.
This release is feature complete. It is recommended for use in production environments.
It is appropriate for use with MySQL server versions 5.0-5.6

It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/#downloads and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

Enjoy and thanks for the support!Enjoy and thanks for the support!

Connector/NET Team

I am certainly not a knowledgeable Perl person, however I recently ran into the error Can’t locate MHA/MasterMonitor.pm on Red Hat 6.x. I have installed MySQL MHA on various systems before without any issues.

$ masterha_manager -version
Can't locate MHA/MasterMonitor.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/masterha_manager line 26.
BEGIN failed--compilation aborted at /usr/bin/masterha_manager line 26.

The issue was that MySQL MHA is not installed in any of the acceptable default paths for this disto default installation.

$ find / -type d -name MHA
/usr/lib/perl5/vendor_perl/MHA

The fix was simple on this OS, but I expect there is a correct Perl approach?

ln -s …

Thanks to everyone who attended last week’s webinar on MySQL security; hopefully you’ve all gone out and set SELinux to enforcing mode if you weren’t already running that way. If you weren’t able to attend, the recording and slides are available for viewing/download. But now, without further ado, here are the questions which we didn’t have time to cover during the presentation.

Q: Do you have a favorite software firewall you recommend that I can run on an EC2 instance in front of my MySQL server?
A: I’d probably just do this with iptables. Any of the other Linux-based software firewall packages are all going to be wrappers around iptables anyway. However, if …

Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.

Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.

First get the 15 biggest tables:

root@mysqlserver [(none)]> set global innodb_stats_on_metadata=0; select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY round(sum(data_length+index_length)) DESC LIMIT 15;
 Query OK, 0 rows affected (0.00 sec)
 …

The Galera series

Overview and some history

Some years ago (2011), I was presenting in one article what at that time I have call “A dream on MySQL parallel replication” (http://www.tusacentral.net/joomla/index.php/mysql-blogs/96-a-dream-on-mysql-parallel-replication).

At that time I was dreaming about having a replication mechanism working on the commit operations, and was also trying to explain how useless is to have parallel replication by schema. I have to say that I got a lot of feedback about that article, directly and indirectly, most of it telling me that I was crazy and that what I was looking for cannot be done.

The only crazy guy that was following me in my wandering was Herik Ingo, who mentions to me Codership and Galera.

After …

With one of my previous post I have run through the steps involve in setting up Jenkins and today I’m going to discuss about the how to get a PHP project integrated with Jenkins on a Mac Mini server(It’s a pain in the ass compared to a Linux box when it comes to setting up […]

A tip for all those cloud users that like cloning database servers (as reported in my book Effective MySQL – Replication Techniques in Depth).

Starting with MySQL 5.6, MySQL instances have a UUID. Cloning servers to quickly create slaves will result in the following error message.

mysql> SHOW SLAVE STATUSG
...
  Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
...

The solution is simple. Clear our the file based configuration file (located in the MySQL datadir) and restart the MySQL instance.

$ rm -f /mysql/data/auto.cnf
$ service mysql restart

MySQL5.6 introduced a new system variable eq_range_index_dive_limit. This may significantly affect query execution plans. Here I show a typical example.

There is a table "t". Primary key consists of multiple columns starting from "id1". There are 1.67M rows in table t, and cardinality of id1 is 46K (These numbers can be collected by SHOW TABLE STATUS / SHOW INDEX). So there are 36 rows (1.67M/46K=36) per id1 on average, but actual id1 distribution is not even. There are nearly 1M rows where id1 between 1 and 10.

mysql> explain select count(*) from t force index (PRIMARY) where id1 in (1,2,3,4,5,6,7,8,9)\G

*************************** 1. row ***************************

…

MySQL5.6 introduced a new system variable eq_range_index_dive_limit. This may significantly affect query execution plans. Here I show a typical example.

There is a table "t". Primary key consists of multiple columns starting from "id1". There are 1.67M rows in table t, and cardinality of id1 is 46K (These numbers can be collected by SHOW TABLE STATUS / SHOW INDEX). So there are 36 rows (1.67M/46K=36) per id1 on average, but actual id1 distribution is not even. There are nearly 1M rows where id1 between 1 and 10.

mysql> explain select count(*) from t force index (PRIMARY) where id1 in (1,2,3,4,5,6,7,8,9)\G

*************************** 1. row ***************************

…