Upgrading MySQL from 4.0/Latin-1 to 4.1/UTF-8

I upgraded the MySQL database server powering this and a few other sites from 4.0 to 4.1. At the same time, I converted the data from Latin-1 to UTF-8, which proved to be a bit more complicated thing that I had anticipated. This is how it was done.

Backing up data and removal of 4.0

First, backing up of the entire 4.0 database. This was done with the superhandy MySQL utility mysqldump with the following command:mysqldump --opt --all-databases > d:\dump.sqlThere was 420Mb of data in sql form. However, MySQL can INSERT several rows with one command, so there is no one INSERT command per one row; it is much more compact.

Just to be sure, I zipped the dump.sql file and copied it onto another computer.

Then, removal of the MySQL 4.0. This was done by using the standard Add/remove programs -thing in the control panel. However, the uninstaller does not uninstall the service registration. To do this, use mysqld --remove

Installation of 4.1

The installer was quite a standard installer, easy to use.

When the installer has finished, it launches a configuration program, which was very welcome! It set up the things that previously have been available only in configuration files.

Feeding the data

Since I was unable to find any other solution for charset conversion, I wrote a small program, SqlDumpImporter, to feed the data into the database. You will need MySQLDriverCS to use it. The program reads data from an external file in iso-8859-1, converts it to utf-8 and recognizes the different databases. It executes only the commands of the databases specified. If you want to import databases kissa and vuohi from file kuukkeli.sql, invoke the program thusly: sqlimporter kuukkeli.sql kissa vuohi Note that the connection string is hardcoded into the program.

I did not use the program to import the data to the mysql database, which contains the user data. There were so few database users that it was fast to re-create the accounts.

Categories: Computers
Posted by Matias at 06.12.2004 21.49 (12 years ago) | 55 comments


Post a new comment

Will be displayed within an image file, hopefully undecipherable to address harvesters.

content licensed under Creative Commons BY-NC-SA - Valid HTML 5