Techy issue .... MySQL3 to MySQL5

[gaspode]

OK ... I need to move some databases off a server running MySQL3 to another running MySQL5 (both are FreeBSD based) ...

How do I do it (someone said i can simply copy the mysqldump from the server running V5 to the server running v3, run it and then just restore on the 5 - does this sound feasible ?)

Ta

Comments

[wimble.livejournal.com]

Point. I hadn't noticed that gaspodex was talking about running the v5 mysqldump against the v3 database. I was thinking just about moving the actual dump files.

Using the wrong version of dump sounds like a road to a headache. What happens if the internal format of the data has changed (which seems likely)? eg. from 8 bit characters to UTF-16? (ok, this example is probably obsolete, but it's just an example): the v5 mysqldump will read 2 bytes, and treat them as a single character. So the process might not throw an error, but will corrupt all the data. In practice, it'll probably not be anything this coarse, but that just makes it harder to know if there's an issue.

[alex-holden.livejournal.com]

I suspect the v5 mysqldump will simply refuse to connect to the v3 server. I would try doing the dump on the v3 server using the v3 mysqldump and then import the dump file on the v5 server using the v5 mysql client.

[gaspodex.livejournal.com]

according to several post son the MySQL website you should use the v5 version of mysqldump so the dumped data is in the right format for V5 (theres a compatability switch it seems) .. I just need to find someone thats done it ...

[sugoll.livejournal.com]

That shouldn't be the case. Admittedly, I was going in the other direction, but I managed to use v5 versions of both mysqldump and mysql when dumping data from my v5 db to waveney's v3 server. The --compatible=mysql323 switch was needed when dumping so that things like views got converted into tables, and various other bits of syntax was commented appropriately (MySQL's dump format is just SQL, but it'll include comments around stuff with versions embedded, and clients of a suitable rev or above will look inside the comments and treat them as actual SQL).

[sugoll.livejournal.com]

It's not that much of a problem; the dump format is just SQL to re-create the data (hardly space-efficient, but who cares?).

[wimble.livejournal.com]

Well, if the mysqldump fails to understand the format of the database it's extracting from (I've no idea whether it reads the database directly, or invokes some sort of connector to perform the access), then it'll generate perfectly valid SQL, but the constants in the generated SQL won't represent the values correctly.