Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. See the notes regarding these changes later in this section, particularly regarding overriding them to preserve backward compatibility if that is a concern.
It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, protect your data by making a backup.
To upgrade to 5.6 from any previous version,
MySQL recommends that you dump your tables with
mysqldump before upgrading and reload the
dump file after upgrading. Use the
--all-databases option to
include all databases in the dump. If your databases include
stored programs, use the
--events options as well.
In general, do the following when upgrading from MySQL 5.5 to 5.6:
Read all the items in these sections to see whether any of them might affect your applications:
Section 2.11.1, “Upgrading MySQL”, has general update information.
The items in the change lists provided later in this section enable you to identify upgrade issues that apply to your current MySQL installation. Some incompatibilities discussed there require your attention before upgrading. Others should be dealt with after upgrading.
The MySQL 5.6 Release Notes describe significant new features you can use in 5.6 or that differ from those found in earlier MySQL releases. Some of these changes may result in incompatibilities.
Note particularly any changes that are marked
Known issue or
Incompatible change. These
incompatibilities with earlier versions of MySQL may require
your attention before you upgrade. Our
aim is to avoid these changes, but occasionally they are
necessary to correct problems that would be worse than an
incompatibility between releases. If any upgrade issue
applicable to your installation involves an incompatibility
that requires special handling, follow the instructions
given in the incompatibility description. Sometimes this
involves dumping and reloading tables, or use of a statement
CHECK TABLE or
For dump and reload instructions, see
Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”. Any procedure that
REPAIR TABLE with
must be done before upgrading. Use of
this statement with a version of MySQL different from the
one used to create the table (that is, using it after
upgrading) may damage the table. See
Section 220.127.116.11, “
REPAIR TABLE Syntax”.
Before upgrading to a new version of MySQL, Section 2.11.3, “Checking Whether Tables or Indexes Must Be Rebuilt”, to see whether changes to table formats or to character sets or collations were made between your current version of MySQL and the version to which you are upgrading. If so and these changes result in an incompatibility between MySQL versions, you will need to upgrade the affected tables using the instructions in Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”.
After upgrading to a new version of MySQL, run mysql_upgrade (see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”). This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. (Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.)
If you run MySQL Server on Windows, see Section 2.3.7, “Upgrading MySQL on Windows”.
If you use replication, see Section 16.4.3, “Upgrading a Replication Setup”, for information on upgrading your replication setup.
If your MySQL installation contains a large amount of data that
might take a long time to convert after an in-place upgrade, you
might find it useful to create a “dummy” database
instance for assessing what conversions might be needed and the
work involved to perform them. Make a copy of your MySQL
instance that contains a full copy of the
mysql database, plus all other databases
without data. Run your upgrade procedure on this dummy instance
to see what actions might be needed so that you can better
evaluate the work involved when performing actual data
conversion on your original database instance.
Read all the items in the following sections to see whether any of them might affect your applications:
Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. The motivation for these changes is to provide better out-of-box performance and to reduce the need for the database administrator to change settings manually. These changes are subject to possible revision in future releases as we gain feedback.
In some cases, a parameter has a different static default
value. In other cases, the server autosizes a parameter at
startup using a formula based on other related parameters or
server host configuration, rather than using a static value.
For example, the setting for
back_log now is its
previous default of 50, adjusted up by an amount
proportional to the value of
max_connections. The idea
behind autosizing is that when the server has information
available to make a decision about a parameter setting
likely to be better than a fixed default, it will.
The following table summarizes changes to defaults. Any of these can be overridden by specifying an explicit value at server startup.
|Parameter||Old Default||New Default|
|50||Autosized using |
|1800 (on Windows)||0|
|1||8 (platform dependent)|
|300||Autosized using |
With regard to compatibility with previous releases, the most important changes are:
Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:
[mysqld] innodb_file_per_table=0 innodb_checksum_algorithm=INNODB binlog_checksum=NONE
Those settings preserve compatibility as follows:
With the new default of
operations following an upgrade will move
InnoDB tables that are in
the system tablespace to individual
.ibd files. Using
will prevent this from happening.
permits binary downgrades after upgrading to this
release. With a setting of
InnoDB would use checksumming that older MySQL versions
the server can be used as a replication master without
causing failure of older slaves that do not understand
binary log checksums.
Incompatible change: As of
MySQL 5.6, the full-text stopword file is loaded and
any table was created with
indexes while the server character set was
repair it using this statement:
Incompatible change: MySQL
5.6.11 and later supports
TABLE ... [SUB]PARTITION BY
ALGORITHM=, which can be used to create a table whose
n [LINEAR] KEY
KEY partitioning is compatible with a
MySQL 5.1 server (
#14521864, Bug #66462) This syntax is not accepted by MySQL
5.6.10 and earlier, although it is supported in MySQL 5.5
beginning with MySQL 5.5.31. mysqldump in
MySQL 5.5.31 and later MySQL 5.5 releases includes the
ALGORITHM option when dumping tables
using this option, but surrounds it with conditional
comments, like this:
CREATE TABLE t1 (a INT) /*!50100 PARTITION BY KEY */ /*!50531 ALGORITHM = 1 */ /*!50100 () PARTITIONS 3 */
When importing a dump containing such
TABLE statements into a MySQL 5.6.10 or earlier
MySQL 5.6 server, the versioned comment is not ignored,
which causes a syntax error. Therefore, prior to importing
such a dump file, you must either change the comments so
that the MySQL 5.6 server ignores them (by removing the
!50531 or replacing it with
!50611, wherever it occurs), or remove
This is not an issue with dump files made using MySQL 5.6.11
or later, where the
ALGORITHM option is
/*!50611 ... */.
Some keywords may be reserved in MySQL 5.6 that were not reserved in MySQL 5.5. See Section 9.3, “Reserved Words”.
YEAR(2) data type has certain issues
that you should consider before choosing to use it. As of
YEAR(2) is deprecated.
YEAR(2) columns in existing tables are
treated as before, but
YEAR(2) in new or
altered tables are converted to
For more information, see
Section 11.3.4, “
YEAR(2) Limitations and Migrating to
As of MySQL 5.6.6, it is explicitly disallowed to assign the
DEFAULT to stored procedure or
function parameters or stored program local variables (for
example with a
statement). This was not previously supported, or documented
as permitted, but is flagged as an incompatible change in
case existing code inadvertantly used this construct. It
remains permissible to assign
var_name = DEFAULT
system variables, as before, but assigning
DEFAULT to parameters or local variables
now results in a syntax error.
After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.
In MySQL, the
type differs in nonstandard ways from other data types:
TIMESTAMP columns not
explicitly declared with the
attribute are assigned the
attribute. (Columns of other data types, if not
explicitly declared as
NULL values.) Setting such a
NULL sets it to the current
column in a table, if not declared with the
NULL attribute or an explicit
UPDATE clause, is automatically assigned the
DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP
following the first one, if not declared with the
NULL attribute or an explicit
DEFAULT clause, are automatically
00:00:00' (the “zero” timestamp).
For inserted rows that specify no explicit value for
such a column, the column is assigned
'0000-00-00 00:00:00' and no warning
Those nonstandard behaviors remain the default for
TIMESTAMP but as of MySQL
5.6.6 are deprecated and this warning appears at startup:
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
As indicated by the warning, to turn off the nonstandard
behaviors, enable the new
system variable at server startup. With this variable
enabled, the server handles
TIMESTAMP as follows instead:
TIMESTAMP columns not
explicitly declared as
NULL values. Setting such a
NULL sets it to
NULL, not the current timestamp.
TIMESTAMP column is
CURRENT_TIMESTAMP attributes automatically.
Those attributes must be explicitly specified.
NOT NULL and without an
DEFAULT clause are treated
as having no default value. For inserted rows that
specify no explicit value for such a column, the result
depends on the SQL mode. If strict SQL mode is enabled,
an error occurs. If strict SQL mode is not enabled, the
column is assigned the implicit default of
'0000-00-00 00:00:00' and a warning
occurs. This is similar to how MySQL treats other
temporal types such as
To upgrade servers used for replication, upgrade the slaves
first, then the master. Replication between the master and
its slaves should work provided that all use the same value
Bring down the slaves, upgrade them, configure them with
the desired value of
and bring them back up.
The slaves will recognize from the format of the binary
logs received from the master that the master is older
(predates the introduction of
and that operations on
TIMESTAMP columns coming
from the master use the old
Bring down the master, upgrade it, and configure it with
value used on the slaves, and bring it back up.