Release Notes - MySQL 5.6

Overview - MySQL 5.6 (CentOS 6 only)

This Release Note covers all point releases for the MySQL 5.6 branch that are offered by eApps. Upgrade instructions are also included.

NOTE - eApps only maintains MySQL Server 5.6 for CentOS 6 OS Templates. Virtual Servers using CentOS 7 OS Templates will use MariaDB, which is a drop in replacement for MySQL. Please see the MariaDB User Guide for more information. If you are on a CentOS 6 OS Template and want to move to a CentOS 7 OS Template, please contact eApps Sales.

MySQL 5.6 is a major release of the MySQL database server. This release has introduced new functionality and significant changes to the existing MySQL server. If you are upgrading from any prior version of MySQL to MySQL 5.6, there is a HIGH RISK of data loss and downtime if you do not carefully test your existing application and database with this new version.

eApps recommends, as a best practice, to maintain a staging server where you can test software upgrades before moving them into production. This approach will allow you to minimize the risk of data loss and downtime of your production service when performing software upgrades. If necessary, you can rebuild your staging Virtual Server on demand so that you have a fresh installation to test on. If you need more information about setting up a staging Virtual Server, please contact eApps Sales.

IMPORTANT! MySQL version 5.6 has an extensive list of new features and changes. Upgrading to this version may significantly impact the operation of your database. The official What is New in MySQL 5.6 documentation is located here - http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html. Please read this to understand all of the changes in the MySQL 5.6 release. For additional information, the official documentation for MySQL is located here - http://dev.mysql.com/doc/

eApps Release Notes for MySQL 5.6.27
eApps Release Notes for MySQL 5.6.25
eApps Release Notes for MySQL 5.6.24
eApps Release Notes for MySQL 5.6.22
eApps Release Notes for MySQL 5.6.21
eApps Release Notes for MySQL 5.6.20
eApps Release Notes for MySQL 5.6.17
eApps Release Notes for MySQL 5.6.12

How to upgrade
MySQL 5.1 to MySQL 5.6
MySQL 5.6.x to MySQL 5.6.y

Highlighted Features and Fixes in MySQL 5.6.27

Features

• yaSSL upgraded to 2.3.7d

Fixes

• Several fixes for ALTER TABLE operations with partitioning
• Fix for CREATE TABLE with an invalid function not failing gracefully
• Memory allocation sanity checks added to memcached code

The official Release Notes for MySQL 5.6.27 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-27.html

Highlighted Features and Fixes in MySQL 5.6.25

Features

• The my_print_defaults configuration now masks passwords by default.
• The innodb_stress suite of tests is now included

Fixes

• The UPDATE_TIME column of the INFORMATION_SCHEMA.TABLES table now shows when a partition InnoDB table was last updated
• The memcached set command no longer accepts a negative expire time value
• SHOW ENGINE INNODB STATUS negative signal count values fixed

The official Release Notes for MySQL 5.6.25 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-25.html

Highlighted Features and Fixes in MySQL 5.6.24

Features

• CMake has been updated to version 3.1
• Server version number is now added to starting message in error log

Fixes

• Fix for an InnoDB memcached extra_col_value[] array causing a server exit.
• Fix for a server exit that could occur after queries compared two rows and the rows belonged to different character sets
• Fixed an issue where creating a user after dropping columns from the mysql.user table could result in a server exit

The official Release Notes for MySQL 5.6.24 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-24.html

Highlighted Features and Fixes in MySQL 5.6.22

Features

NOTE - the Replication variable binlogging_impossible_mode has been renamed to binlog_error_action. The binlogging_impossible_mode variable is now deprecated.

Fixes

• Fix for an InnoDB COMMIT related to a full text search that caused a seg fault
• Several fixes related to Replication - please see the full release notes for more info
• Fix for mysqldump not reporting a disk-full error if the destination was an NFS mount

The official Release Notes for MySQL 5.6.22 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-22.html

Highlighted Features and Fixes in MySQL 5.6.21

Features

NOTE - the --skip-innodb option is now deprecated and will be removed in a future MySQL release. Using this feature now results in a warning. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).

Fixes

• InnoDB - fixed a bug where a seg fault would occur during recovery and mark a table as corrupt
• Fix for a bug where a competition between threads could lead to timeout failure trying to rotate the audit log file
• The client library now includes a call to X509_verify_cert_error_string() in the SSL certificate verification code to be more robust in detecting invalid certificates.

The official Release Notes for MySQL 5.6.21 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-21.html

Highlighted Features and Fixes in MySQL 5.6.20

Features

NOTE - mysqlhotcopy is now deprecated and will be removed in a future version of MySQL. Alternatives include mysqldump and the MySQL plugin available with the Enterprise Backup Service.

• CMake support updated to handle CMake v3
• Creation of a my.cnf file can be suppressed when using mysql_install_db

Fixes

NOTE - several important changes/fixes to the behavior of DROP TABLE have been implemented. Please read the official Release Notes (linked to below) for more information.

The official Release Notes for MySQL 5.6.20 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

Highlighted Features and Fixes in MySQL 5.6.17

Features

WARNING - There are several incompatible changes with previous versions of MySQL that are included in this release. Please read the official Release Notes for more details. These changes are in place in preparation for MySQL 5.7, the next major release.

Some of the incompatible changes are:

• Statements that use AES_ENCRYPT and AES_DECRYPT are now unsafe for statement based replication
• ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE are all deprecated, and in MySQL 5.7 will do nothing.

Fixes

• InnoDB - MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL
• mysql_install_db provides a more informative diagnostic message when required Perl modules are missing
• InnoDB - the FLUSH TABLES statement's FOR EXPORT option is now supported for partitioned InnoDB tables.

The official Release Notes for MySQL 5.6.17 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Highlighted Features and Fixes in MySQL 5.6.12

Features

• mysql_upgrade now verifies that the server version matches the compiled version and exits if there is a mismatch

Fixes

• Fixed an InnoDB ALTER_TABLE bug that caused a partial rollback to be treated as a full rollback
• Fixed an issue with SET_PASSWORD and users in the mysql.users table

The official Release Notes for MySQL 5.6.12 are available here - http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-12.html

How to upgrade

Before upgrading MySQL, make a backup of your databases. Instructions are here - http://support.eapps.com/ispmgr/mysql5#backing-up-your-mysql-databases

To upgrade MySQL, you will need to work from the command line using SSH, and you will need to be able to work as the root user. You will need to use yum to upgrade the application. You will also need to know the root password for the MySQL server (not the super user root password).

MySQL 5.1 to MySQL 5.6

There is no direct upgrade path between major MySQL releases, such as between MySQL 5.1 and MySQL 5.6. In order to upgrade, you will need to export your databases, uninstall MySQL, move your existing data and configuration files, update the mysql libraries and client, install the new version of MySQL, and then import your databases. If you have custom configurations that are still compatible with MySQL 5.6, you will need to move those back into place.

NOTE - If you try to update MySQL without following these steps, the upgrade will fail, and you may encounter data loss due to database corruption.

Export your databases

To export your databases, use the mysqldump --all-databases -p > dumpfile.sql command. This exports ALL your databases into the current working directory. You may need to enter the MySQL root password.

[root@eapps-example ~]# mysqldump --all-databases -p > dumpfile.sql
Enter password:
[root@eapps-example ~]#

Uninstall MySQL server

To uninstall the MySQL server, use the yum remove mysql-server command. This removes the mysql-server package, but leaves the libraries and mysql client application, both of which will be updated in the next few steps.

[root@eapps-example ~]# yum remove mysql-server

You will be asked to confirm that you wish to remove the package, answer y.

Move the existing mysql directory and configuration file

The update to MySQL 5.6 will install a new mysql directory and my.cnf file. If your existing mysql directory and configuration file are still in place, the new version of MySQL will not start.

To do this, you will need to use the mv (move) command, and you will need to rename the exiting directory and configuration file so that they do not interfere with the newly installed files. The mysql directory is located at /var/lib/mysql, move it to mysql_orig. The configuration file is /etc/my.cnf, move it to my.cnf_orig.

[root@eapps-example ~]# mv /var/lib/mysql/ /var/lib/mysql_orig
[root@eapps-example ~]# mv /etc/my.cnf /etc/my.cnf_orig

Update the mysql libraries and client

Along with the MySQL server, the MySQL libraries and client application have to be updated. Updating the MySQL libraries will update the MySQL client at the same time.

To update the libraries and client, use the yum update mysql-libs command.

[root@eapps-example ~]# yum update mysql-libs

You will be asked to confirm that you wish to update two packages, answer y.

Install the new version of the MySQL server

After updating the MySQL libraries and client, you will need to install the new version of the MySQL server. This is done with the yum install mysql-server command. This will also install the libaio application as a dependency.

[root@eapps-example ~]# yum install mysql-server

You will be asked to confirm that you wish to install two packages, answer y.

Start the MySQL server

After the installation completes, you will need to start the MySQL server.

• For CentOS 6, use the service mysqld start command:

  [root@eapps-example ~]# service mysqld start

Import your databases and restart MySQL

After the upgrade is complete, you will need to import your databases back into MySQL, and then restart the MySQL server. Import your databases back into MySQL with the mysql -u root < dumpfile.sql command. Because this is a new install of MySQL, there is currently no MySQL root user password - the password will be put into place when you import your databases and restart the mysqld service.

[root@eapps-example ~]# mysql -u root < dumpfile.sql

Once you have imported the databases, restart MySQL.

• For CentOS 6, use the service mysqld restart command:

  [root@eapps-example ~]# service mysqld restart

You should now be able to access MySQL as the MySQL root user, with the correct password.

[root@eapps-example ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or .
Your MySQL connection id is 1
Server version: 5.6.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '' for help. Type '' to clear the current input statement.
mysql>

Update your MySQL configuration file

As part of the upgrade process, you moved the my.cnf configuration file to a new location (my.cnf_orig). If you had made any changes to the original my.cnf, please review the official MySQL 5.6 documentation before adding those changes to the new my.cnf file. Some of the configuration parameters may have changed, and using an older value may cause issues with the new version of MySQL.

If you are not sure, move your custom configurations from the original my.cnf file into the new my.cnf file one at a time, and restart MySQL after each addition. If MySQL fails to start, then you know that particular configuration is not compatible with the new version.

MySQL 5.6.x to MySQL 5.6.y (newer version of MySQL 5.6)

To update MySQL you will need to connect to your Virtual Machine via SSH. Once you are connected to the Virtual Machine, you will need to run the following commands as the root user: yum clean all and yum -y update mysql-server

[root@eapps-example ~]# yum clean all
Loaded plugins: fastestmirror, priorities, remove-with-leaves
Cleaning up Everything
Cleaning up list of fastest mirrors
[root@eapps-example ~]# yum -y update mysql-server

You will know that the new version of MySQL is installed when you see a Complete! message and you are returned to the command prompt. Once the installation has completed, restart MySQL.

• For CentOS 6, use the service mysqld restart command:

  [root@eapps-example ~]# service mysqld restart