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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]