Additional languages
« 2.10.3 Securing the Initial MySQL Accounts
2.11.1 Upgrading from MySQL 5.0 to 5.1 »
Section Navigation      [Toggle]

2.11. Upgrading MySQL

2.11.1. Upgrading from MySQL 5.0 to 5.1
2.11.2. Copying MySQL Databases to Another Machine

As a general rule, we recommend that when you upgrade from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 4.0 and wish to upgrade to a newer series, upgrade to MySQL 4.1 rather than to 5.0 or 5.1.

The following items form a checklist of things that you should do whenever you perform an upgrade:

  • Before upgrading from MySQL 5.0 to 5.1, read Section 2.11.1, “Upgrading from MySQL 5.0 to 5.1”, as well as Appendix C, MySQL Change History. These provide information about features that are new in MySQL 5.1 or differ from those found in MySQL 5.0. If you wish to upgrade from a release series previous to MySQL 5.0, you should upgrade to each successive release series in turn until you have reached MySQL 5.0, and then proceed with the upgrade to MySQL 5.1. For information on upgrading from MySQL 5.0, see the MySQL 5.0 Reference Manual; for earlier releases, see the MySQL 3.23, 4.0, 4.1 Reference Manual.

  • Before you perform an upgrade, back up your databases, including the mysql database that contains the grant tables.

  • Some releases of MySQL introduce incompatible changes to tables. (Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.) Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.

    To avoid problems due to such changes, after you upgrade to a new version of MySQL, you should check your tables (and repair them if necessary), and update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.5.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • If you are running MySQL Server on Windows, see Section 2.3.14, “Upgrading MySQL on Windows”.

  • If you are using replication, see Section 6.3.3, “Upgrading a Replication Setup”, for information on upgrading your replication setup.

  • As of MySQL 5.1.9, the mysqld-max server is included in binary distributions. There is no separate MySQL-Max distribution. As of MySQL 5.1.12, binary distributions contain a server that includes the features previously included in mysqld-max.

  • If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different non-conflicting name. The same is true if the new version of MySQL implements a built-in function with the same name as an existing stored function. See Section 9.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

You can always move the MySQL format files and data files between different versions on the same architecture as long as you stay within versions for the same release series of MySQL. If you change the character set when running MySQL, you must run myisamchk -r -q --set-collation=collation_name on all MyISAM tables. Otherwise, your indexes may not be ordered correctly, because changing the character set may also change the sort order.

If you are cautious about using new versions, you can always rename your old mysqld before installing a newer one. For example, if you are using MySQL 5.0.13 and want to upgrade to 5.1.10, rename your current server from mysqld to mysqld-5.0.13. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld.

If, after an upgrade, you experience problems with recompiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, you should check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries.

If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation.

It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP mysql extension and the Python MySQLdb module.