2.13.1 Upgrading MySQL

As a general rule, to upgrade from one release series to another,
go to the next series rather than skipping a series. To upgrade
from a release series previous to MySQL 5.0, 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 example, if you currently are running
MySQL 4.1 and wish to upgrade to a newer series, upgrade to MySQL
5.0 first before upgrading to 5.1, and so forth. For information
on upgrading to MySQL 5.0, see the
MySQL 5.0 Reference Manual; for earlier
releases, see the MySQL 3.23, 4.0, 4.1 Reference
Manual.

If you perform a binary (in-place) upgrade without dumping and
reloading tables, you cannot upgrade directly from MySQL 4.1 to
5.1. 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.13.4, “Rebuilding or Repairing Tables or Indexes”). Then upgrade
from MySQL 5.0 to 5.1 and check and repair your
tables.

To upgrade from MySQL 5.0 to 5.1, use
the items in the following checklist as a guide:

Read all the notes in
Section 2.13.1.1, “Upgrading from MySQL 5.0 to 5.1”. These notes
enable you to identify upgrade issues that apply to your
current MySQL installation. Some incompatibilities discussed
in that section require your attention
before upgrading. Others should be dealt
with after upgrading.

Read the
Release
Notes as well, which provide information about
features that are new in MySQL 5.1 or differ from
those found in MySQL 5.0.

After upgrading to a new version of MySQL, run
mysql_upgrade (see
Section 4.4.8, “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 use InnoDB, consider setting
innodb_fast_shutdown to 0
before shutting down and upgrading your server. When you set
innodb_fast_shutdown to 0,
InnoDB does a slow shutdown, a full purge
and an insert buffer merge before shutting down, which ensures
that all data files are fully prepared in case the upgrade
process modifies the file format.

If you upgrade 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.

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, there is no
mysqld-max server at all in binary
distributions. They 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 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 9.2.4, “Function Name Parsing and Resolution”, for the rules
describing how the server interprets references to different
kinds of functions.

For upgrades between versions of a MySQL release series that has
reached General Availability status, you can move the MySQL format
files and data files between different versions on systems with
the same architecture. For upgrades to a version of a MySQL
release series that is in development status, that is not
necessarily true. Use of development releases is at your own risk.

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 a version of MySQL
5.0 and want to upgrade to 5.1, rename
your current server from mysqld to
mysqld-5.0. If your new
mysqld then does something unexpected, you can
simply shut it down and restart with your old
mysqld.

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.

If, after an upgrade, you experience problems with compiled 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. Recompilation might
also be necessary for programs compiled against the shared client
library if the library major version number has changed (for
example from libmysqlclient.so.15 to
libmysqlclient.so.16.

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