B. Keith Murphy wrote:
> Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to
> 5.0.45. That's not the problem :). The problem is that MySQL recommends
> dumping and importing your data when doing this upgrade to make sure that
> things go as smoothly as possible. We have quite a bit of data spread over
> multiple servers. We have slaves off each master server and the plan is
> upgrade the slaves and then make them the masters.
>
> The problem is that I am realizing that this dump/import is going to take
> hours and in some cases days. I am looking for any way to speed this up. Any
> suggestions?
The fastest way I've found is to do SELECT INTO OUTFILE on the master, which
selects into a sort of tab-delimited format by default -- don't specify any
options like field terminators or whatnot. This file can then be imported
directly into LOAD DATA INFILE, again without options.
I think this is faster than loading files full of SQL statements, which have to
be parsed and query-planned etc.
I thought mysqldump had an option to dump this way, but I can't see it now.
I'd use mysqldump to just dump the structures, routines etc without data, and
then load the data separately.
Beware: replication from 4.1.x to 5.0.40 will fail. The MySQL changelog didn't
list it as an incompatible change, but there is some bug that got fixed around
5.0.38 (sorry, I forget what it was and can't find it now). When the slave
checks the master version, it throws an error and stops saying "I refuse because
of this bug."
Baron
