Use iconv to help migrate databases

Iconv is a useful tool to convert the encoding of given files from one encoding to another - which comes in very handy if you want to migrate that old MySQL 4 database to a hot new MySQL 5.

I recently had to migrate a database from MySQL 4 to 5 and its not as easy as you would expect. One of the issues I had to tackle was encrypted passwords saved in the db which obviously needed special character support.

I started by dumping all the SQL for the structure and content of the MySQL 4 database into a zip file. When doing this from MySQL 4 it is likely the encoding of the file will be ISO-8859-1, or Latin-1.

What I wanted was UTF-8 for MySQL 5, so that all characters are supported and the encoded passwords don't go all crazy. This is fairly straight forward using iconv and the command looks a little like this:
iconv -f ISO-8859-1 -t UTF-8 /path/to/mysql4/dump.sql >> utf8-encoded.sql

Yet another reason I enjoy developing on a Linux environment like Ubuntu so much.

Problems:
There are a few other issues you might need to consider, the collation of the database and the encoding used not only by the MySQL server, but the MySQL client also.

Collation:
Collation is basically a way to compare an encoding, as explained here.

MySQL Encoding
Encoding of the MySQL client as opposed to the server is an issue that you should be aware of, the two can differ and this has an impact on what is saved in the database as mentioned here.

Resources:
Straight forward tute on converting MySQL
Character sets and Collation from MySQL
Possible issues with converting MySQL from 4 to 5