When I saw Shlomi’s recent post which asked (in part) for blacklist support in MySQL, I started thinking about ways in which this could be done using the tools we have today.  Here’s the example requirements Shlomi noted:

Speaking of whitelist, it would be great to have a host blacklist. If I wanted to grant access to ‘gromit’@’192.168.%’ except for ’192.168.10.%’ — well, I would have to whitelist all the possible subnets. I can’t exclude a set of hosts.

I think that’s entirely possible without the overhead of whitelisting all possible subnets – let’s give it a go!

This solution will rely on the fact that the first step in authentication in MySQL is finding the most applicable host for the incoming connection.  That’s caused all sorts of annoyances in the past with the anonymous user, where …

UPDATE 2013-08-30: Tungsten 2.1.2 was released.

UPDATE 2013-08-23: We have found a few problems that happen when replicating with RBR and temporal columns. We will have to publish an updated bugfix release quite soon.

Tungsten Replicator 2.1.1 is out. Key features in this release are:

• A better installer, of which we have already given a preview in tpm, the multi-master composer. The new installer allows faster and more powerful deployments of both single and multiple masters topologies. And it also allows the next feature:
• Secured communication layer. Now the replicator data and administrative …

To only slightly misquote one of the greatest movies of all times:

You keep using that option.  I do not think it means what you think it means.

Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice.  I’m writing this blog post to help others who hold similar misconceptions of what this option does.

Many, if not most, MySQL DBAs are familiar with “host blocked” errors:

C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8
ERROR 1129 (HY000): Host …

The much-improved PERFORMANCE_SCHEMA in MySQL 5.6 provides visibility into MySQL’s host cache, including the ability to monitor for impending blocked hosts.  You can do this with the following query:

mysql> SELECT
    ->  ip,
    ->  host,
    ->  host_validated,
    ->  sum_connect_errors
    -> FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                ip: 192.168.2.4
              host: TFARMER-MYSQL.wh.oracle.com
    host_validated: YES
sum_connect_errors: 3
1 row in set (0.02 sec)

That’s helpful information, and allows DBAs to identify problematic hosts before they are blocked.  Due to Bug#69807, it’s also something MySQL 5.6 users will want to do.  This bug causes the counter maintained in the host …

Installed on a clean CentOS 6.4 AWS instance.

sudo su -
cd /tmp
wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar
tar xvf MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar
yum install -y libaio perl
rpm -i MySQL*.rpm

The following output is the sign that security is being considered with new MySQL versions. Woot!

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the …

MySQL Server has an aborted_connect status counter which will show you the number of failed attempts to establish a new connection.  The manual describes potential causes as follows:

• A client does not have privileges to connect to a database.
• A client uses an incorrect password.
• A connection packet does not contain the right information.
• It takes more than connect_timeout seconds to get a connect packet. See Section 5.1.4, “Server System Variables”.

It goes on to make the following statement:

If …

After speaking about the topic the Developer Week 2013 in Nürnberg this week, due to some scheduling coincide I repeated it today for our codecentric "Dev-Friday" in which internal or external speakers present some topic to the whole company.

For a while we have been recording these for colleagues on vacation or otherwise occupied during the talk to watch it later. Several of them are available on codecentric's YouTube channel publicly. As of a few moments ago, so is my "Man in the Middle? – No, thank you!" talk on the possibility of – and countermeasures against – man in the middle attacks against SSL connections.

For your convenience, here is the video:



The …

A fascinating post-mortem on high profile network failures:

This post is meant as a reference point–to illustrate that, according to a wide range of accounts, partitions occur in many real-world environments. Processes, servers, NICs, switches, local and wide area networks can all fail, and the resulting economic consequences are real. Network outages can suddenly arise in systems that are stable for months at a time, during routine upgrades, or as a result of emergency maintenance. The consequences of these outages range from increased latency and temporary unavailability to inconsistency, corruption, and data loss. Split-brain is not an academic concern: it happens to all kinds of systems–sometimes for days on end. Partitions deserve serious consideration.

As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory.

For other setups directories like lost+found, .zfs, etc. gave similar issues.

In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do this a ignore-db-dir option needs to be specified for each directory. …

Starting with MySQL 5.6, MySQL commercial-license builds use OpenSSL.  yaSSL – previously used as the default SSL library for all builds – remains the implementation for Community (GPL) builds, and users comfortable building from source can choose to build with OpenSSL instead.  Daniel van Eeden recently requested a global variable to indicate which SSL library was used to compile the server (bug#69226), and it’s a good request.  It’s something I’ve previously requested as well, having been fooled by the use of have_openssl as a synonym for …