MySQL 5.6.23 Overview and Highlights

MySQL 5.6.23 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 3 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

  1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1j to version 1.0.1k. Issues fixed in the new version are described at
  2. Functionality Changed: Support for the SSL 2.0 and SSL 3.0 protocols has been disabled because they provide weak encryption. (Bug #19820550, Bug #19921150)
  3. Functionality Changed: yaSSL was upgraded to version 2.3.7. (Bug #19695101, Bug #20201864)
  4. Functionality Changed: The valid date range of the SSL certificates in mysql-test/std_data has been extended to the year 2029. (Bug #18366947)

In addition to those, there were 37 other bug fixes:

  • 13 InnoDB
  •   5 Replication
  • 18 Miscellaneous
  •   1 Partitioning

The highlights for me are the Partitioning bug, 1 of the Replication bugs, and 8 of the InnoDB bugs, as 1 was a regression bug (crashing/corruption) and the others include bugs that raise invalid assertions, server halts, break replication, and so forth, though all in all, I wouldn’t say any of these are very common and require immediate attention:

  1. InnoDB: If a DROP DATABASE statement failed on the master, mismatched tables could be left on the slave, breaking replication. This was caused by the DROP TABLE statement being binary logged if at least one table was deleted during the DROP DATABASE operation. The fix ensures that in such a situation the DROP TABLE statement is binary logged with the IF EXISTS option. (Bug #74890, Bug #20041860)
  2. InnoDB: A tablespace export operation set the purge state to PURGE_STATE_STOP but the purge thread did not check the purge state until the current purge operation was completed. In the case of a large history list, the tablespace export operation was delayed, waiting for the current purge operation to finish. The purge state is now checked with every purge batch. (Bug #20266847, Bug #75298)
  3. InnoDB: An ALTER TABLE … ADD INDEX operation raised an assertion due to assertion code that did not allow an online index status of ONLINE_INDEX_ABORTED_DROPPED. The assertion code has been relaxed. (Bug #20198726)
  4. InnoDB: DML operations on a table with full-text search indexes raised an invalid assertion. (Bug #19905246) References: This bug is a regression of Bug #19314480.
  5. InnoDB: A multiple-table delete operation caused the server to halt. (Bug #19815702)
  6. InnoDB: A FLUSH TABLES operation raised an assertion. (Bug #19803418)
  7. InnoDB: With change buffering enabled, a buffered sequence of operations that should not have been buffered resulted in an Unable to purge a record error. (Bug #19528825, Bug #73767)
  8. InnoDB: A slow shutdown (innodb_fast_shutdown=0) after crash recovery raised an assertion. Slow shutdown did not wait for background rollback operations to finish before proceeding. (Bug #16862810)
  9. Partitioning: A failed ALTER TABLE … TRUNCATE PARTITION statement or a failed TRUNCATE TABLE statement against a partitioned table sometimes left inconsistent metadata in the table cache; subsequent SQL statements reusing this metadata failed, and could in some cases also lead to a failure of the server. (Bug #74292, Bug #19786861)
  10. Replication: When using SHOW SLAVE STATUS to monitor replication performance, Seconds_Behind_Master sometimes displayed unexpected lag behind the master. This was caused by Previous_gtids log events being written to the slave’s relay log with a timestamp behind the master, and then being used to calculate the Seconds_Behind_Master. This fix ensures that events generated on the slave that are added to the relay log and are not used when calculating Seconds_Behind_Master. (Bug #72376, Bug #18622657)


So while there were no major changes, those 8 InnoDB bugs, especially in total, are of concern, so I’d consider upgrading if I were running InnoDB on a prior version of 5.6.

And with the yaSSL updates, if you use SSL connections, you may want to consider upgrading as well.

The full 5.6.23 changelogs can be viewed here (which has more details about all of the bugs listed above):

Hope this helps. 🙂


Setting up MySQL SSL on Multiple Machines

Managing SSL certificates for MySQL can be somewhat of a pain, especially when setting it up on multiple machines.

I looked around on the web, and didn’t really run into any mentions of how to do this for multiple machines, so I dug into it a bit more.

If you’ve created the certificates before, you’ll know you get prompted for various bits of information during a couple of the steps (country, state, email).

However, this can be by-passed by using the -batch option with the openssl command.

So, to set this up for multiple servers, just loop through the following 5 commands for your servers:

cd C:\mysql\certs
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -batch -key ca-key.pem > ca-cert.pem
openssl req -newkey rsa:2048 -days 3600 -batch -nodes -keyout server-key.pem > 
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem 
	-CAkey ca-key.pem -set_serial 01 > server-cert.pem

This will create the following 5 files:

ca-cert.pem, ca-key.pem, server-cert.pem, server-key.pem, and server-req.pem

Those are all that is needed to set up SSL for MySQL.

Then, just add the following lines to the [mysqld] section of your my.cnf/my.ini files:

ssl-ca	 = C:\mysql\certs\ca-cert.pem
ssl-cert = C:\mysql\certs\server-cert.pem
ssl-key	 = C:\mysql\certs\server-key.pem

Ensure you have an SSL user created, and then start up mysqld.

You can connect with the following command:

mysql -ussluser -p --ssl-key=

Connection Output:

mysql> status;
mysql  Ver 14.14 Distrib 5.5.16, for Win32 (x86)

Connection id:          1
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3430
Uptime:                 8 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 33  ...

Hope this helps.


Setting Up SSL For MySQL

I often get asked how to set up and use SSL with MySQL, especially on Windows – I think largely because the manual step-by-step is more geared towards Linux users (though this “how-to” is valid for all – anyone can copy/paste the commands after changing the paths).

So I’ve compiled a concise, yet comprehensive, how-to that I wanted to share with everyone out there who wants to set this up quickly and efficiently.

And for those who haven’t done it, setting up SSL can often seem intimidating, so I want to dispell that myth while I’m at it, and show you how quick and easy it can be to set up SSL for MySQL.

For one, I should mention that I’m using a version where SSL is ‘available’, just ‘DISABLED’ (some versions can be compiled w/out ssl support – so those would not work):

mysql> show global variables like 'have_%ssl';
| Variable_name | Value    |
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |

This is normal, and what you want to see if you’ve not already set up SSL.

Next, I like to store the SSL files I generate in their own directory, so I created a subdir named ‘certs’ in the MySQL $basedir. But by all means, store these wherever you wish.

Third, you need to have OpenSSL installed/available on your machine. Many have this already, certainly most Linux anyway (check with “where openssl” on Win or “which openssl” on Linux). (These latter commands assume you have it in the $PATH too.)

If you do not have it, you can download it from here:…/Win32OpenSSL.html <-- Windows <-- Linux (And note my commands below assume either 'openssl' in the $PATH or that you're in the directory where the 'openssl' binary/exe is located.) Now that that’s out of the way, you’re basically looking at a 5 step process:

  1. Create CA Certificates (following 2 commands create 2 files: ca-cert.pem and ca-key.pem):
    openssl genrsa 2048 > "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem"
    openssl req -new -x509 -nodes -days 3600 -key 
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" > 
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
  2. Create Server Certificates (following 2 commands create 3 files: server-cert.pem, server-key.pem, and server-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout 
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-key.pem" > 
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem" 
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem" 
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 > 
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-cert.pem"
  3. Create Client Certificates (following 2 commands create 3 files: client-cert.pem, client-key.pem, and client-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout 
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-key.pem" > 
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem" 
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem" 
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 > 
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-cert.pem"
  4. Create SSL User(s):
    GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;
  5. Update my.cnf and restart mysqld (add following 3 options to [mysqld] section of my.cnf file)
    ssl-ca		= "C:/Program Files/MySQL/mysql-5.5.16/certs/ca-cert.pem"
    ssl-cert	= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-cert.pem"
    ssl-key		= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-key.pem"

After restart, connect via SSL with your newly created SSL user (this is the minimum you need to specify – note the “–ssl-key=”, as-is .. refer to prev. post and related bug for further discussion on that if interested):

mysql -ussluser -pssluser -P3430 --ssl-key=


You can verify with the ‘status’ command once connected (note the SSL “cipher in use is DHE-RSA-AES256-SHA” means SSL is being used):

mysql> status
mysql  Ver 14.14 Distrib 5.5.16, for Win32 (x86)

Connection id:          11
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3430
Uptime:                 35 min 26 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 0 
Queries per second avg: 0.011


If you’re knowledgeable abut SSL (and MySQL), or if you’ve read my prior post discussing SSL, you’ll know you do not need all of the files I created above (8 in total).

In fact, for the most basic SSL setup, (“gateway SSL”, we’ll say), you only technically need these 3 files: ca-cert.pem, server-cert.pem, and server-key.pem.

But, why not create all you’d ever need, since it’s only a couple commands, and then you’re set in case you want to move beyond the “gateway SSL” setup into an even more secure SSL setup.

If you’re interested in the even-more-secure SSL setups, you’ll want to read up on the REQUIRE X509, REQUIRE ISSUER, REQUIRE SUBJECT, and REQUIRE CIPHER specifications on the following page:

As always, hope this helps. 🙂