Saturday, March 29, 2014

Notes on the AES encryption in MySQL

Oracle has improved the AES encryption/decryption functions in MySQL 5.6.17. They improved it a lot and posted a blog which explains all the details.

If you would like to know more about encryption there are two resources I would recommend:
  • The Code Book by Simon Singh. This is about the history of cryptography, but it also includes a lot of information about crypto which is currently in use. This is also a very entertaining read.
  • Crypto 101, a free/opensource book which gives a intro to crypto. The webpage also has a video of the talk on which the book is based.
And if you're going to use the AES encryption functions in MySQL there are some things you should consider:
  • Connections with a UNIX socket (or shared memory on Windows) should be safe. Local TCP/IP connections are also safe in most circumstances.
  • Remote connections which use the AES encryption should use a protected connection (SSL, VPN, etc). Otherwise someone might be able to sniff the network traffice which contains your encryption key.
  • Replication traffic should also be protected (especially if you're using statement based replication. Mixed or row based might be safer).
  • There is a trick to prevent the key to be logged:
    • SET @encrypted := AES_ENCRYPT('foo', 'bar');
    • INSERT INTO t1(c1) VALUES(@encrypted);
  • MySQL 5.6 prevents logging of passwords, but this doesn't include the AES functions (yet). (Bug #72158). So be careful with your logging.
  • It is possible to use the AES_DECRYPT in a view definition, but then your probably storing your key with your data, which defeats the purpose. The same is true for storing your key in a stored procedure, function or a virtual column in case of MariaDB.
And there are alternatives to using encryption within MySQL: Your application could encrypt the data before it's sent over the network. And depending on why you need encryption it might also be possible to encrypt the whole data directory. This can be achieved with LUKS or eCryptFS if you're on Linux or ZFS if you're on Solaris. On Windows you might use the EFS feature of NTFS or use Bitlocker.

The AES functions in MySQL are a good solution if you only want to encrypt specific data (which is often the case) or if you need to search on the decrypted data (WHERE AES_DECRYPT(..)='foo')

Friday, March 14, 2014

MySQL NL Meetup March 18

The next MySQL User Group NL meetup is this Tuesday at the Oracle office in Utrecht.

For more info go to the Event page @

Saturday, February 8, 2014

Unittesting your indexes

During FOSDEM PGDay I watched the "Indexes: The neglected performance all-rounder" talk by Markus Winand. Both his talk and the "SQL Performance Explained" book (which is also available online) are great.

The conclusion of the talk is that we should put more effort in carefully designing indexes. But how can we make sure the indexes are really used now and in the future? We need to write some tests for it.

So I wrote a small Python script to test index usage per query. This uses the JSON explain format available in MySQL 5.6. It's just a proof-of-concept so don't expect too much of it yet (but please sent pull requests!).

A short example:
import indextest

class tester(indextest.IndexTester):
    def __init__(self):
        dbparams = { 'user': 'msandbox',
                     'password': 'msandbox',
                     'host': 'localhost',
                     'port': '5615',
                     'database': 'imdb'}
        self.dbparams = dbparams

    def test_query1(self):
        q1 = self.query("SELECT * FROM movies WHERE rank>9.8")
        return q1.testEqual('query_block.table.access_type', 'range')

    def test_query2(self):
        q2 = self.query("SELECT * FROM actors WHERE first_name='Tom'")
        return q2.testEqual('query_block.table.access_type', 'range')

    def test_query3(self):
        q3 = self.query("SELECT * FROM actors WHERE first_name='%Tom'")
        return q3.testEqual('query_block.table.key', 'idx_first_name')

run = tester()
$ ./ 
Query: SELECT * FROM movies WHERE rank>9.8
Test: query_block.table.access_type == range
Result: range == range: True

Query: SELECT * FROM actors WHERE first_name='Tom'
Test: query_block.table.access_type == range
Result: range == ref: False

Query: SELECT * FROM actors WHERE first_name='%Tom'
Test: query_block.table.key == idx_first_name
Result: idx_first_name == idx_first_name: True

Tested 3 queries, Pass: 2, Fail: 1

The code is available on github

Thursday, January 30, 2014

The FOSDEM schedule in SQL

I thought it would be nice to be able to query the FOSDEM schedule for the MySQL devroom with SQL.

So I wrote a small Python script which downloads the schedule and inserts it into a MySQL database.

Some examples:
mysql> SELECT summary,dtstart,location,description FROM calendar 
    -> WHERE calname='MySQL devroom' 
    -> AND MATCH (summary, description) AGAINST ('Fabric')\G
*************************** 1. row ***************************
    summary: Sharding and Scale-out using MySQL Fabric
    dtstart: 2014-02-01 16:05:00
   location: UA2.114 (Baudoux)
description: MySQL Fabric is an open-source solution released by the MySQL Engineering team 
at Oracle. It makes management of MySQL server farms easy and available for both applications 
with small and large number of servers.
1 row in set (0.00 sec)

mysql> SELECT summary,dtstart FROM calendar 
    -> WHERE calname='MySQL devroom' ORDER BY dtstart DESC;
| summary                                                           | dtstart             |
| MaxScale, the Pluggable Router                                    | 2014-02-01 18:35:00 |
| OSM data in MySQL                                                 | 2014-02-01 18:05:00 |
| Practical sysbench                                                | 2014-02-01 17:35:00 |
| ProxySQL : High Availability and High Performance Proxy for MySQL | 2014-02-01 17:05:00 |
| Troubleshooting performance problems in MySQL                     | 2014-02-01 16:35:00 |
| Sharding and Scale-out using MySQL Fabric                         | 2014-02-01 16:05:00 |
| Spider Storage Engine                                             | 2014-02-01 15:35:00 |
| Handling failover with MySQL 5.6 and Global Transaction IDs       | 2014-02-01 15:05:00 |
| Inside MySQL 5.7 Replication Features                             | 2014-02-01 14:35:00 |
| Incompatible changes proposed in MySQL 5.7                        | 2014-02-01 14:05:00 |
| MariaDB Connect Storage Engine                                    | 2014-02-01 13:35:00 |
| Advanced Fulltext Search with Sphinx                              | 2014-02-01 13:05:00 |
| 15 Tips to improve your Galera Cluster                            | 2014-02-01 12:35:00 |
| Galera Cluster IRL                                                | 2014-02-01 12:05:00 |
| mysqlv8udfs - Writing MySQL UDFs in Javascript                    | 2014-02-01 11:35:00 |
| Getting started with MySQL Performance Schema                     | 2014-02-01 11:05:00 |
| Welcome in the MySQL & Friends Devroom 2014                       | 2014-02-01 11:00:00 |
17 rows in set (0.00 sec)

Wednesday, January 15, 2014

MySQL in Oracle Critical Patch Update Advisory January 2014

Oracle has released the Critical Patch Update (CPU) advisory for January 2014.

The affected MySQL products are:
  • Oracle MySQL Enterprise Monitor, versions 2.3, 3.0 
  • Oracle MySQL Server, versions 5.1, 5.5, 5.6
So this means that you should consider updating MySQL. For MySQL Enterprise the updates should be available on My Oracle Support and for the Community version the new versions are on the regular download locations. I guess the official repositories are already updated.

For MySQL 5.6 you should upgrade to 5.6.15
For MySQL 5.5 you should upgrade to 5.5.35
For MySQL 5.1 you should upgrade to 5.1.73

If you use the MySQL release from your distribution you should ask them to fix the CVE numbers fixed in these releases (if they didn't do that already).

The highest CVSS score for MEM is 10 and for MySQL the highest score is 6.8.

Update: for more information see this blog post by Dave Stokes.

Thursday, January 2, 2014

SSL with MySQL does not have to be complicated

I've blogged before about how complicated setting up SSL with MySQL can be. Since then many bugs have been fixed related to SSL.

But still many people are using unencrypted connections for replication and client connections.

This could be because of possible performance degradation. But then it could be used for only the admin connections.

It could also be because they already use SSH or VPN.

But for many it's because they think it's complicated. This is not necessary. I've created mysslgen which makes it easier to setup SSL. Just run and then the CA, server and client certificates and keys will be generated for you. The most difficult part it to get python 3.3 or python 2.7 to run on CentOS/RHEL/OEL 6.
If you think SSL should be part of the default MySQL installation, then click the 'Affects me' button for Bug #70734.

During the creation of mysslgen I found out MySQL fails to load private keys if they are in PKCS#8 format (Bug #71271). So, be careful if you create the private keys yourself!

With SSL enabled it should be easier to grant users the permission to use MySQL for Excel, MySQL with LibreOffice or any other desktop tool with a MySQL feature.

Tuesday, December 31, 2013

Looking back on 2013

The year is almost at its end. Looking back at the past year I think it was a good year for MySQL. The 5.6 release was released in February and has been proven to be a very good release. There were many folks who reported bugs, which is good as this means they care about the product. But MySQL is not only a product, its a large ecosystem. One of the big players in the ecosystem is Oracle and this year they really participated in the MySQL community by having their engineers attend conferences which were not organized by Oracle (Like Percona Live and FOSDEM).
This year I couldn't attend the MySQL Connect and Percona Live conferences, but I hope to be able to attend in 2014 again. I did attend FOSDEM, which is a really nice (and different) conference.

For MariaDB it also was an interesting year as a number of  Linux distributions and customers switched from MySQL to MariaDB (and sometimes back again). I wonder what 2014 will bring for MariaDB.

The TokuDB storage engine was released as opensource this year.

The Galera replication system did get quite some attention this year and saw a 3.0 release.

I think 2014 is going to be even better for MySQL as the 5.7 release already looks great and Fabric looks promising.

Have a safe an fun new years eve! I'm going to eat an oliebol!