On Tue, July 17, 2007 04:29, Baron Schwartz wrote:
> 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
>> things go as smoothly as possible. We have quite a bit of data spread
>> 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
>> hours and in some cases days. I am looking for any way to speed this up.
> The fastest way I've found is to do SELECT INTO OUTFILE on the master,
> selects into a sort of tab-delimited format by default -- don't specify
> 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
I think you are looking for the --single-transaction option :)
> I'd use mysqldump to just dump the structures, routines etc without data,
> then load the data separately.
> Beware: replication from 4.1.x to 5.0.40 will fail. The MySQL changelog
> list it as an incompatible change, but there is some bug that got fixed
> 5.0.38 (sorry, I forget what it was and can't find it now). When the
> checks the master version, it throws an error and stops saying "I refuse
> of this bug."
+45 40 85 71 38
+66 870 133 224
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.