Skip navigation links
2.12.2 Downgrading MySQL »
Section Navigation      [Toggle]

2.12.1.1. Upgrading from MySQL 5.1 to 5.4

Note

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, you should protect your data by making a backup.

To upgrade to 5.4 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading.

If you perform a binary (in-place) upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.4. This occurs due to an incompatible change in the MyISAM table index format in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables (see Section 2.12.4, “Rebuilding or Repairing Tables or Indexes”). Then upgrade from MySQL 5.0 to 5.4 and check and repair your tables.

In general, you should do the following when upgrading from MySQL 5.1 to 5.4:

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.

MySQL Enterprise.  MySQL Enterprise subscribers will find more information about upgrading in the Knowledge Base articles found at Upgrading. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

The following lists describe changes that may affect applications and that you should watch out for when upgrading from MySQL 5.1 to 5.4.

Configuration Changes:

  • Incompatible change: In MySQL 5.4, the innodb_file_io_threads system variable has been removed and replaced with innodb_read_io_threads and innodb_write_io_threads. If you upgrade from MySQL 5.1 to MySQL 5.4 and explicitly set innodb_file_io_threads at server startup, you must change your configuration. Either remove any reference to innodb_file_io_threads or replace it with references to innodb_read_io_threads and innodb_write_io_threads.

  • Incompatible change: From MySQL 5.4.0 through 5.4.3, several changes were made to the default or minimum values of the InnoDB log file parameters, innodb_log_files_in_group and innodb_log_file_size. If you upgrade from MySQL 5.1 to 5.4.0 through 5.4.2, or from MySQL 5.4.0 through 5.4.2 to 5.4.3 or higher, the server may fail to start due to these changes unless you use the following instructions to discard the old InnoDB log files and create new ones. (You need not discard and re-create the log files if you upgrade from MySQL 5.1 to MySQL 5.4.3 or higher.)

    To re-create the InnoDB log files, It is necessary to shut down your current server cleanly so that no outstanding transaction information remains in the log. The procedure to use depends on the value of innodb_fast_shutdown used in your current server:

    • If innodb_fast_shutdown is not set to 2: Stop your current MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files and edit my.cnf if necessary to specify the desired log file configuration. Then upgrade to MySQL 5.4 and start the new server. mysqld sees that no InnoDB log files exist at startup and creates new ones.

    • If innodb_fast_shutdown is set to 2: Shut down your current server, set innodb_fast_shutdown to 1, and restart the server. The server should be allowed to recover. Then you should shut down the server again and follow the procedure described in the preceding item to change InnoDB log file size. Set innodb_fast_shutdown back to 2 and start the MySQL 5.4 server.

  • Incompatible change: As of MySQL 5.4.2, the InnoDB Plugin is included in MySQL 5.4 releases. It becomes the built-in version of InnoDB in MySQL Server, replacing the version previously included as the built-in InnoDB engine. InnoDB Plugin is also available in MySQL 5.1 as of 5.1.38, but it is an optional storage engine that must be enabled explicitly using two server options: 

    [mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

    If you were using InnoDB Plugin in MySQL 5.1 by means of those options, you must remove them after an upgrade to 5.4 or the server will fail to start.

Server Changes:

  • Incompatible change: In MySQL 5.4.2, options for loading plugins such as pluggable storage engines were changed from boolean to tristate format. The implementations overlap, but if you previously used options of the form --plugin_name=0 or --plugin_name=1, you should instead use --plugin_name=OFF or --plugin_name=ON, respectively. For details, see Section 5.1.3, “Server Options for Loading Plugins”.

SQL Changes: