Skip navigation links

Additional languages
2.11.1.1 Upgrading from MySQL 4.0 to 4.1 »
Section Navigation      [Toggle]

2.11.1. Upgrading MySQL

[+/-]

2.11.1.1. Upgrading from MySQL 4.0 to 4.1
2.11.1.2. Upgrading from MySQL 3.23 to 4.0

As a general rule, to 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 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0.

Whenever you perform an upgrade, use the items in the following checklist as a guide:

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

  • Read all the notes the upgrading section for the release series to which you are upgrading. Read the change notes as well. These provide information about new features you can use.

  • Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. After you update to a new version of MySQL, you should 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 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

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

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

  • If you are upgrading an installation originally produced by installing multiple RPM packages, it is best to upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.

  • If you previously installed a MySQL-Max distribution that includes a server named mysqld-max, and then upgrade later to a non-Max version of MySQL, mysqld_safe still attempts to run the old mysqld-max server. If you perform such an upgrade, you should remove the old mysqld-max server manually to ensure that mysqld_safe runs the new mysqld server.

  • 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 nonconflicting 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 8.2.3, “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. Before MySQL 4.1, if you change the character set when running MySQL, you must run myisamchk -r -q --set-character-set=charset_name on all MyISAM tables. Otherwise, your indexes may not be ordered correctly, because changing the character set may also change the sort order. As of MySQL 4.1, to convert tables created before 4.1 to the format that includes character set and collation information, use the instructions in Section 9.1.11.2, “Converting 4.0 Character Columns to 4.1 Format”.

Normally, you can upgrade MySQL to a newer MySQL version without having to do any changes to your tables. Please confirm whether the upgrade notes to the particular version you are upgrading to tell you anything about this. If there would be any incompatibilities you can use mysqldump to dump your tables before upgrading. After upgrading, reload the dump file using mysql or mysqlimport to re-create your tables.

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 4.0.18 and want to upgrade to 4.1.1, rename your current server from mysqld to mysqld-4.0.18. 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 want to start or that you cannot connect without a password, verify that you do not have some 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.

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.

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 PHP mysql and extensions or the Python MySQLdb module.


User Comments

Posted by Graham Wideman on July 3 2008 8:42am[Delete] [Edit]

Reality-check:
Upgrading from 5.0.something to 5.1.25... many hours, ultimately a dead-end.
-- Centos 5.1 and trying to let yum manage everything.
-- I obtained the 5.1.25 community RPMs for client and server.
-- Above article fails to discuss whether to uninstall existing version first or not. When I attempted yum upgrade it didn't see existing install (possibly because of name change from "mysql-server" to MYSQL-community-server?). So I did yum uninstall, which also removed dependent Perl, PHP and Python modules.
-- yum install for server failed to complete... later determined to be inability to start server due to inability to create /var/run/mysqlsd. Fixed perms, and was able to run mysql_upgrade, and start server OK (it preserved existing database, users etc.)
-- yum install for client ran OK.
-- client could not connect to server, because one of the installs had renamed my.cnf, and without that, server and client default to different (or no) mysql.sock. This seems a perennial bug. Fixed that, connects OK.
-- Also able to connect from remote client.
-- Now the fatal part: Embarked on reinstating MYSQL-python, dovecot, php-mysql and perl-DBD-MySQL. Yum says all are dependent on "mysql" 5.0.45 . After finding no guidance on this, I let yum proceed... whereupon yum promptly UNinstalled 5.1 community client AND server (with no prompt or warning).

Bottom line: the final sentence of this article "It's a good idea to rebuild and reinstall [Perl, PHP and Python modules]. "Good idea"???? You can't proceed unless you uninstall them, and then where are the replacements that are compatible? I realize that it's not the job of mysql to provide them, but if upgrading MySQL results in no Perl, Python or PHP to interact with it, perhaps something more helpful could be mentioned here.


Posted by John Malkowich on November 21 2008 10:04pm[Delete] [Edit]


I just did a upgrade from 5.0 to 5.1
Worked fine with yum. In comment #1 its likely a RPM name that caused issue.

I was on mysql.i386 5.0.22-2.2.el5_1.1 and upgraded to
mysql.i386 5.1.29-2.1
OS :RHEL 5

I did mysql_upgrade /tmp

So far I have not see any major issues.

John

Posted by Simon Mudd on May 26 2009 7:01am[Delete] [Edit]

I've also had mixed results regarding the 5.0 to 5.1 process.

It seems the documentation has been produced for smaller sites that can afford downtime, don't mind doing a msyqldump followed by load, and perhaps are not doing much replication. The extra issues that need to be addressed are not documented clearly if you leave this realm.

I've created several bug reports with MySQL so am hoping that many of these issues will be addressed in later versions and that should make it clearer in the future.

Also be careful if trying to run multiple versions of mysql on the same server at the same time. This can be done but care is needed.

My only comment for people who have important data and resources to do this would be to setup a slave which you can afford to break and throw away and upgrade that slave. Then any problems can be addressed and the process repeated again if necessary until the upgrade works successfully.

You can then switch the slave to the master role, or make new slaves as copies of the original slave, depending on the situation at your site.

I've so far upgraded several different instances at work and only one caused a lot of hassle. That one had triggers and stored functions so if you don't use these features you may find your upgrade goes much smoother.

Add your own comment.