Sunday, October 12, 2014

Disabling old_passwords=1

It is possible to disallow users from using old_passwords=1. This can be done by adding 'maximum-old_passwords=0' to your my.cnf

This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier.

Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes.

mysql> select @@old_passwords;
| @@old_passwords |
|               0 |
1 row in set (0.00 sec)

mysql> set old_passwords=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
| Level   | Code | Message                                      |
| Warning | 1292 | Truncated incorrect old_passwords value: '1' |
1 row in set (0.00 sec)

mysql> select @@old_passwords;
| @@old_passwords |
|               0 |
1 row in set (0.00 sec)

Sunday, September 21, 2014

Downgrading from MySQL 5.6 to MySQL 5.5

Last week I had to downgrade from MySQL 5.6 to 5.5. The reason for this was that the application used a very old Connector/J and that's incompatible with MySQL 5.6 because the removal of SET OPTION syntax.

We're now planning to upgrade Connector/J to be able to upgrade to 5.6 again.

There are two methods of downgrading:
  • Dump/Restore with mysqldump. This is easy and reliable, but can take more time.
  • In place (replace binaries, don't change data). This fast, but won't work if file formats have changed.
As expected this is documented in the MySQL Reference Manual.

I went for the in place method. I expected this to work without many issues as this database was not using the fancy new features like fulltext indexes for InnoDB. All tables used the Antelope format. As both MySQL versions support Antelope and Barracuda this shoud be fine. I don't know why Oracle didn't introduce a new Cheeta format with the introduction of the fulltext indexes.

The issues I encountered were:
  • A different innodb_log_file_size default (had to set it in my.cnf)
  • Missing table.  Bug #73634 (Fixed by copying it from another instance)
  • Various changes in the privilege tables. (Fixed by dropping them and running mysql_upgrade)
  • Changes in mysql.user (set plugin to '' when plugin='mysql_native_plugin')
  • Warnings about unknown flag 50. Bug #72371 (Not a Bug)
This server didn't user replication, so that wasn't an issue.

CIS Oracle MySQL 5.6 Security Benchmark

I've been working on the CIS security benchmark for Oracle MySQL 5.6. There already is a benchmark document for earlier versions of MySQL.

The benchmark document helps you to ensure you have a secure configuration for MySQL.

Center for Internet Security (CIS) is a nonprofit organization.

The Call For Participation is now open.

If you are already a CIS member then you can download the document here. If you don't have an account you can register for free.

Sunday, August 31, 2014

Putting MySQL Cluster in a container

To get more familiar with docker and to create a test setup for MySQL Cluster I created docker images for the various components of MySQL Cluster (a.k.a. NDB Cluster)

At first I created a Fedora 20 container and ran all components in one container. That worked and is quite easy to setup. But that's not how one is supposed to use docker.

So I created Dockerfile's for all components and one base image.

The base image:
  • contains the MySQL Cluster software
  • has libaio installed
  • has a mysql user and group 
  • serves as a base for the other images
The management node (ndb_mgmd) image:
  • Has ndb_mgmd as entrypoint
  • Has a config.ini for the cluster config
  • Should be started with "--name=mymgm01"
The data node (ndbmtd) image:
  • Has ndbmtd as entrypoint
  • Uses the connect string: "host=${MGM01_PORT_1186_TCP_ADDR}:1186"
  • Should be started with "--link mymgm01:mgm01" to allow it to connect to the management node.
  • You should create 2 containers of this type to create a nodegroup of 2 nodes.
The API node (mysqld) image:
  • has a my.cnf
  • Runs mysqld_safe
  • Should be started with "--link mymgm01:mgm01" to allow it to connect to the management node.
  • The ndb-connectstring is given as parameter to mysqld_safe as it comes from an environment variable. It's not possible to use environment variables from within my.cnf. Docker is supposed to also update /etc/hosts but that didn't work for me.
  • You should expose port 3306 for your application
The management client (ndb_mgm) image:
  • Runs ndb_mgm as entrypoint
  • Should be started with "--link mymgm01:mgm01" to allow it to connect to the management node.
  • Running the ndb_mgm in a container removes the need to publish port 1186 on the management server. More info here.
  • You can override the entrypoint to run other NDB utilities like ndb_desc or ndb_select_all
The images can be found on
The Dockerfiles can be found on  

Possible improvements
  • Use hostnames in the config.ini instead of IPv4 addresses. This makes it more dynamic. But that means updating /etc/hosts or fideling with DNS.
  • Using VOLUMES in the Dockerfiles to make working with data easier.

Sunday, August 17, 2014

The new cloud backup option of MySQL Enterprise Backup

MySQL Enterprise Backup 3.10 support backups to the cloud. The only supported cloud service is Amazon S3.

When the cloud destination is used mysqlbackup will upload the backup as an image file.

You can specify all options on the commandline:
mysqlbackup --cloud-service=s3 --cloud-aws-region=eu-west-1 \
--cloud-access-key-id=AKIAJLGCPXEGVHCQD27B \
--cloud-secret-access-key=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 \
--cloud-bucket=meb_myserver --cloud-object-key=firstbackup --cloud-trace=0 \
--backup-dir=/tmp/firstbackup --backup-image=- --with-timestamp backup-to-image

But you can also put the settings in the my.cnf

The with-timestamp option is important as the backup won't start if the backup-dir already exists. This is because mysqlbackup will leave the backup directory exists after uploading the backup. The backup directory will only have meta info and the log file, not the actual backup.

By using a group suffix like _cloud you can put settings for multiple types of backups in one cnf file.

mysqlbackup --defaults-group-suffix='_cloud' \
--cloud-object-key=backup_2014081701 backup-to-image

The account you're using should have this policy to be allowed to read and write to the s3 bucket:
  "Version": "2012-10-17",
  "Statement": [
      "Sid": "Stmt1408302840000",
      "Effect": "Allow",
      "Action": [
      "Resource": [

This looks like a good option to me if you're already using mysqlbackup and amazon. It would be nice if the next version would support other cloud providers (e.g. openstack swift, ceph). Implementing this should be easy for those with an s3 compatibility layer, but will probably take more time for others.

I did find some bugs (just search for tag=cloud on if you're interested).

Thursday, August 7, 2014

MySQL User Group Meetup in Amsterdam

This Tuesday Markus Winand will talk at the MySQL User Group NL meetup about "Indexes: The neglected performance all-rounder".
Markus is known for the website and the SQL Performance Explained book.

Date: Tuesday August 12
Location: Marktplaats/eBay Office Amsterdam

Sunday, July 20, 2014

Decoding (encrypted) MySQL traffic with Wireshark

In a comment on my post about Using SSL with MySQL xiaochong zhang asked if it is possible to decode SSL/TLS encrypted MySQL traffic. The short answer is: It depends.

To test this we need a MySQL server which is SSL enabled. I used MySQL Sandbox to create a sandboxed 5.6.19 server. Then I used mysslgen to create the config and the certificates.

$ make_sandbox 5.6.19
$ ./ --config=sandboxes/msb_5_6_19/my.sandbox.cnf --ssldir=sandboxes/msb_5_6_19/ssl

This assumes there already is a extracted tarball of MySQL 5.6.19 in ~/mysql/5.6.19

The script will return a message with the changes you should make in your mysqld and client sections of the my.sandbox.cnf file. Then restart the server to make it active.

For SSL to work we need to connect using TCP/IP instead of over a UNIX socket. So we connect with "./my sql -h". Now execute "\s" or "status" to see if we're indeed using SSL.

It probably looks like this:

mysql [] {msandbox} ((none)) > \s
/home/dveeden/opt/mysql/5.6.19/bin/mysql  Ver 14.14 Distrib 5.6.19, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:  3
Current database: 
Current user:  msandbox@localhost
SSL:   Cipher in use is DHE-RSA-AES256-SHA
Current pager:  stdout
Using outfile:  ''
Using delimiter: ;
Server version:  5.6.19 MySQL Community Server (GPL)
Protocol version: 10
Connection: via TCP/IP
Server characterset: latin1
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
TCP port:  5619
Uptime:   1 hour 32 min 48 sec

Threads: 1  Questions: 18  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.003

Now disconnect and start the trace.

sudo tcpdump -i lo -s 65535 port 5619 -w /tmp/mysql.pcap

First connect w/o SSL: "./my sql -h --skip-ssl". And then with SSL: "./my sql -h"
Stop the tcpdump session and start wireshark and open the mysql.pcap file. Now we can inspect the protocol. If MySQL is using the default port (3306) then wireshark will automatically decode the traffic, but now we have to use 'Decode as...' to tell wireshark this is MySQL traffic. The server greeting packet and the login request should now be visible. In the login request there are client capability flags, one of the flags indicates 'Switch to SSL after handshake' and should be set for the SSL session.

Both SSL and non-SSL sessions will use the same port and start an unencrypted session. The encrypted session will switch to SSL after the handshake. This is a bit like STARTTLS for IMAP. The current version of the MySQL protocol dissector is not (yet) aware of some of the new information in the initial handshake. So the information for the authentication plugins and connection attributes is not decoded yet. The documentation about the protocol can be found in the MySQL Internals manual.

So that's the plaintext part. Now we get to the SSL part. In my setup the default cipher suite which is used for SSL is  DHE-RSA-AES256-SHA. With OpenSSL's ciphers command we can get some more details:

$ openssl ciphers -v 'DHE-RSA-AES256-SHA'
DHE-RSA-AES256-SHA      SSLv3 Kx=DH       Au=RSA  Enc=AES(256)  Mac=SHA1

This means that SHA 1 is use for the MAC part and that AES-256 is used for encryption and the keyexchange is done with DH (Diffie-Hellman). This poses a problem as DH will generate a session key, and we don't have that in the traffic dump as it's not sent over the network. We could use gdb (and maybe a debug trace?) to get the DH keys out, but for now we have an easier solution: use a different cipher suite.

So start tcpdump again and run "./my sql -h --ssl-cipher=AES256-SHA". This cipher uses RSA for keyexchange instead of DH. This means everything we need is send over the network or is present in the SSL certificate and/or key.

Now start wireshark again and use 'Decode as...' and choose SSL. Then go Edit→Preferences→Protocols→SSL→'RSA key list' and add the server's SSL key. Now you should see the decoded traffic.

So decoding SSL/TLS encrypted MySQL traffic is possible. But you need to have:
  • All traffic since the beginning of the TCP/IP connection
  • The server's SSL key
  • The DH session key if DH is used. (you might want to read about Forward secrecy (PFS) if you're interested in the details).