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 mysql.host 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.