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]

I know as little as I possibly can about MySQL. Certainly not this much.

But yes, it sounds plausible. Extract the data from the existing database, copy it to the new server, and then re-insert it. I'm taking your word for it that extraction and insertion tools exist ;-)

[glitterboy1.livejournal.com]

I've never tried running the mysqldump from one version against a different version's database (can't you just use mysqldump from v.3?), but if that works, then the plan sounds feasible.

Separately, you'll need to recreate the required users on the v.5 server.

[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.

[sugoll.livejournal.com]

Assuming that both systems are on the network, you shouldn't need to copy the mysqldump binary. You can do:

mysqldump -u root --password=xxx -h oldhost olddbname | \
mysql -u root --password=yyy newdbname

(You might want to be more selective over the account name, or try importing the resulting dump into a new, trial database first...)

There's a compatibility switch, e.g.:

--compatible=mysql323

which tells mysqldump to create a dump suitable for feeding into said version - handy going from a newer system to an old one, but since you're going the other way, you should be okay.

[glitterboy1.livejournal.com]

Assuming that both systems are on the network...

True.

...and also assuming that the chosen user is allowed to connect to the database from the new server. I think about MySQL security just infrequently enough that it makes my head hurt every time.