Need to upgrade MySQL, but worried...

Question

Need to upgrade MySQL, but worried...

I have a 4.0.24 installation of MySQL (on 10.3.9 server) and I need to update this to at least 4.1.x (5.x preferred).

I have a major concern though... I've never done updates to MySQL, but I have heard horror stories (especially when going from 4.0.x to something newer.

To give a quick rundown, ALL of my databases contain UTF8 (Japanese mostly) and Shift-JIS data held in what is usually the default collations (as 4.0.x didn't fully support UTF8)

I guess my question would be, how can I update AND retain the databases with their content fully intact?

iMac G5 20" (Ambient Light Sensor) 2GB RAM, Mac OS X (10.3.9), Mac G5 Dual 2.5 GHz 1GB RAM

Posted on Jan 14, 2007 9:43 PM

Reply

Answer 1

Jan 18, 2007 11:16 PM in response to Jonathan Reinfelds

This is where building the 10.4.8 version of mysql in 10.3.x makes doing this some much easier.

If you have some time I can help you with this process but I wont have much tollerance for any explanation or education on what is being done, meaning I'll tell you what to do, give you the tools you need and you'll get the results you want but answering questions about what or why wont be an option as it's a very technical and complicated process and my tolerance these days is very limited.

Reply

Answer 2

Jan 19, 2007 10:46 PM in response to Dale Walsh

Thanks for the offer!
sounds very straight forward and to the point. I'll be ready for this right after the weekend if that is ok for you.

Reply

Answer 3

Jan 20, 2007 5:08 PM in response to Jonathan Reinfelds

No problem, send me an e-mail off forum and we'll set it up, as payment for assistance I expect you to host the generated software for other to obtain, the bandiwth used will be far less than any fee you'd have to pay and it would help others in this same situation.

Reply

Answer 4

Jan 25, 2007 5:25 PM in response to Dale Walsh

Yesterday, I managed to find a way to successfully export the utf8 data from the 4.0 installation and import to a 5.0 installation. Below are the steps I took:

On the machine with the 4.0.24 installation:
For each database, I went over each table. For all fields with type (char, varchar, text, mediumtext, longtext) I altered them to their binary equivalents (charbinary, varcharbinary, blob, mediumblob, longblob).
eg: mysql> ALTER TABLE mytable MODIFY myfield BINARY;

I then exported the data, dumping using the HEX option to help retain the data.
$ mysqldump --opt --user=username --password= ** --allow-keywords --flush-logs --hex-blob --max allowedpacket=16M --quote-names database > database.sql

I then ran iconv on the outputed SQL file to complete the utf8 conversion.

iconv -f latin1 -t UTF-8 database.sql > database-utf8.sql

On the other computer, I installed a binary release of MySQL 5.0 (from mysql.org)

I created a database that would be used to house the exported data.

mysql> CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8 generalci;

Then, transferred over the utf8 version of the dump to the new comp and imported:

$ mysql -u root -p mydatabase < database-utf8.sql

Checked the DB to see if all the data was intact and it was 🙂 This worked for me, but I don't know if this is a "good" method or not, so take with a grain of salt. As I didn't care about the mysql users, I didn't bother transferring the mysql database itself... I just recreated the mysql users in the 5.0 install as there were only a couple.

Again, not sure if this is the "best" method, but it did work for me. Hopefully someone else can find this useful.

Reply