MySQL/Migrate to 5.0

The MySQL herd is proud to announce that MySQL 5.0 will soon be found in Gentoo's stable tree. This document describes how to upgrade to MySQL 4.* and to 5.0.*.

Straight upgrade, suggested for 4.1 => 5.0 migration
The myisam storage engine in version 4.1 was already mature enough to allow a direct upgrade to the next major version of MySQL.

For this step two shells are required because locks belong to the mysql session.

This next step should be done in the second shell:

Return to the first shell to run this command:

The following commands should be done in the second shell:

Return to the first shell for the rest of the upgrade:

Upgrading from old versions of MySQL
Users upgrading from an old version (<4.0.24) of MySQL will first have to install MySQL 4.0.25. If you are already running a more recent version, you can skip this section and continue with backing up the databases.

Creating a backup of your current data
One of the most important tasks that every database administrator has to perform is backing up data. Here we go:

Now a file named should exist, which can be used later to recreate your data. The data is described in the MySQL dialect of SQL, the Structured Query Language.

Now would also be a good time to see if the backup you have created is working.

Upgrading from recent versions of MySQL
If you have skipped step #1, you now have to create a backup package (of the database server, not the data) of the currently installed version:

Now it's time to clean out the current version and all of its data:

After you get rid of your old MySQL installation, you can now install the new version. Note that  is necessary for rebuilding packages linking against MySQL.

Update your config files, you may also use dispatch-conf:

Now configure the newly installed version of MySQL and restart the daemon:

Finally you can import the backup you have created during step #2.

Older mysqldump utilities may export tables in the wrong order when foreign keys are involved. To work around this problem, surround the SQL with the following statements:

Fixing foreign key checks

Next, import the backup.

If you restart your MySQL daemon now and everything goes as expected, you have a fully working version of 4.1.x.

If you encountered any problems during the upgrade process, please report them on Bugzilla.

Recover the old installation of MySQL 4.0
If you are not happy with MySQL 4.1, it's possible to go back to MySQL 4.0.

Replace with the one used when creating the backup:

Introduction
This chapter is not intended to be an exhaustive guide on how to do such conversions, rather a short list of hints on which the reader can elaborate.

Converting a database may be a complex task and difficulty increases with data variancy. Things like serialized object and blobs are one example where it's difficult to keeps pieces together.

Indexes
Every utf-8 character is considered 3 bytes long within an index. Indexes in MySQL can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that the limits are measured in bytes, whereas the length of a column is interpreted as number of characters.

MySQL can also create indexes on parts of a column, this can be of some help. Below are some examples:

Environment
The system must be configured to support the UTF-8 locale. You will find more information in the UTF-8 article and Localization Guide documents.

In this example, we set some shell environment variables to make use of the English UTF-8 locale in :

Be sure to run  afterward.

iconv
, provided by, is used to convert text files from one charset to another. The package can be used as well.

From Japanese to utf8:

can be used to recode a sql dump even if the environment is not set to utf8.

SQL Mangling
It's possible to use the  and   MySQL functions to convert data in your SQL scripts.

Apache (webserver)
To use utf-8 with apache, you need to adjust the following variables in : AddDefaultCharset, CharsetDefault, CharsetSourceEnc. If your source html files aren't encoded in utf-8, they must be converted with  or.

Acknowledgements
We would like to thank the following authors and editors for their contributions to this guide:


 * Michael Kohl
 * Francesco Riosa