From: Mogens Melander (mogensfumlersoft.dk)
Date: Tue Jul 17 2007 - 02:49:55 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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
>> 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 think you are looking for the --single-transaction option :)

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

--
Later

Mogens Melander
+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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]