I can't even imagine an SQL dump of a 400GB database would restore anyway. How long would that take? 3 weeks?
Might want to dump the data to CSV files and the schema to an SQL file if you want a full dump/restore. On Fri, Feb 15, 2013 at 4:54 PM, Reindl Harald <h.rei...@thelounge.net>wrote: > "our database is 400 GB, mysqldump is 600MB" was not a typo and you > honestly believed that you can import this dump to somewhat? > > WTF - as admin you should be able to see if the things in front > of you are theoretically possible before your start any action > and 1:400 is impossible, specially because mysql-dumps are > ALWAYS WAY LARGER then the databasses because they contain > sql-statements and not only data > > Am 15.02.2013 23:37, schrieb Mike Franon: > > Your right I am going to run another mysqldump, maybe something > > happened and pick this up next week.. > > > > Thanks all. > > > > On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy <bmur...@paragon-cs.com> > wrote: > >> Something doesn't add up. If the data set is 400 GB then your dump has > to > >> bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump > isn't > >> working correctly or your data set is much smaller? If the dump output > is > >> less than a gig I would just edit it with something like vi and look at > the > >> offending line. > >> > >> Keith > >> > >> On Feb 15, 2013 3:55 PM, "Mike Franon" <kongfra...@gmail.com> wrote: > >>> > >>> I am having a real hard time upgrading just from 5.0.96 to 5.1 > >>> > >>> I did a full mysqldump and then restore the database, keep in mind our > >>> database is 400 GB, mysqldump is 600MB file, about 30 minutes into the > >>> restore get this error on one table on an insert: > >>> > >>> ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; > >>> check the manual that corresponds to your MySQL server version for the > >>> right syntax to use near ''2010-04-10 20' at line 1 > >>> > >>> It weird because If I upgrade 5.1 right over 5.0 without doing a > >>> mysqldump, and then do a mysqlcheck it works, except for 5 tables, and > >>> triggers, so trying to think of the best way to get to 5.1 > >>> > >>> On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy <bmur...@paragon-cs.com > > > >>> wrote: > >>>> While it might be GA I would not recommend that you deploy it for a > >>>> while. > >>>> ... at least several point releases. There will be new bugs uncovered > as > >>>> it > >>>> moves out to a wider audience. > >>>> > >>>> Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 > off > >>>> it > >>>> and test. Be patient. Save yourself some heartache. Just my two cents. > >>>> > >>>> Keith > >>>> > >>>> On Feb 15, 2013 9:27 AM, "Mike Franon" <kongfra...@gmail.com> wrote: > >>>>> > >>>>> Thanks everyone for suggestions. > >>>>> > >>>>> I am doing this on a test box with a copy of our db before doing > this > >>>>> on production db servers. > >>>>> > >>>>> I just upgraded from 5.0 to 5.1, and ran mysql_upgrade > >>>>> > >>>>> and see I have a few tables with the following error: > >>>>> > >>>>> error : Table upgrade required. Please do "REPAIR TABLE > >>>>> `tablename`" or dump/reload to fix it! > >>>>> > >>>>> I got this on 4 tables so far, but it still checking, my database is > >>>>> huge so might be a while. > >>>>> > >>>>> The question I have what is the best way to fix this? > >>>>> > >>>>> To install all I did was remove all of the 5.0, and then did a yum > >>>>> install 5.1 on my AWS machine. and then just started mysql. > >>>>> > >>>>> Should I instead do a complete mysqldump, and use that instead? > >>>>> > >>>>> On Thu, Feb 14, 2013 at 7:40 PM, Rick James <rja...@yahoo-inc.com> > >>>>> wrote: > >>>>>> Sounds like something that, once discovered, can be fixed in the old > >>>>>> version > >>>>>> -- then it works correctly in both. > >>>>>> > >>>>>> > >>>>>> > >>>>>> That is what happened with a 4.0->5.1 conversion years ago. With > >>>>>> 1000 > >>>>>> different tables and associated code, we encountered two > >>>>>> incompatibilities. > >>>>>> One had to do with NULLs, the other with precedence of commajoin vs > >>>>>> explicit > >>>>>> JOIN. > >>>>>> > >>>>>> > >>>>>> > >>>>>> From: Singer Wang [mailto:w...@singerwang.com] > >>>>>> Sent: Thursday, February 14, 2013 3:41 PM > >>>>>> To: Rick James > >>>>>> Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; > >>>>>> <mysql@lists.mysql.com> > >>>>>> > >>>>>> > >>>>>> Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 > >>>>>> > >>>>>> > >>>>>> > >>>>>> Its a very pedantic case, but we had a few instances where it was an > >>>>>> issue > >>>>>> at my last job. It basically involved multi-table deletes and > >>>>>> aliasing.. > >>>>>> I > >>>>>> quote the change notes for MySQL 5.5.3 > >>>>>> > >>>>>> > >>>>>> > >>>>>> Incompatible Change: Several changes were made to alias resolution > in > >>>>>> multiple-table DELETE statements so that it is no longer possible to > >>>>>> have > >>>>>> inconsistent or ambiguous table aliases. > >>>>>> > >>>>>> § In MySQL 5.1.23, alias declarations outside the table_references > >>>>>> part > >>>>>> of > >>>>>> the statement were disallowed for theUSING variant of multiple-table > >>>>>> DELETE > >>>>>> syntax, to reduce the possibility of ambiguous aliases that could > >>>>>> lead > >>>>>> to > >>>>>> ambiguous statements that have unexpected results such as deleting > >>>>>> rows > >>>>>> from > >>>>>> the wrong table. > >>>>>> > >>>>>> Now alias declarations outside table_references are disallowed for > >>>>>> all > >>>>>> multiple-table DELETE statements. Alias declarations are permitted > >>>>>> only > >>>>>> in > >>>>>> the table_references part. > >>>>>> > >>>>>> Incorrect: > >>>>>> > >>>>>> > >>>>>> > >>>>>> DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; > >>>>>> > >>>>>> DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; > >>>>>> > >>>>>> Correct: > >>>>>> > >>>>>> > >>>>>> > >>>>>> DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; > >>>>>> > >>>>>> DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; > >>>>>> > >>>>>> § Previously, for alias references in the list of tables from which > >>>>>> to > >>>>>> delete rows in a multiple-table delete, the default database is used > >>>>>> unless > >>>>>> one is specified explicitly. For example, if the default database is > >>>>>> db1, > >>>>>> the following statement does not work because the unqualified alias > >>>>>> reference a2 is interpreted as having a database of db1: > >>>>>> > >>>>>> § > >>>>>> > >>>>>> § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 > >>>>>> > >>>>>> WHERE a1.id=a2.id; > >>>>>> > >>>>>> To correctly match an alias that refers to a table outside the > >>>>>> default > >>>>>> database, you must explicitly qualify the reference with the name of > >>>>>> the > >>>>>> proper database: > >>>>>> > >>>>>> > >>>>>> > >>>>>> DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 > >>>>>> > >>>>>> WHERE a1.id=a2.id; > >>>>>> > >>>>>> Now alias resolution does not require qualification and alias > >>>>>> references > >>>>>> should not be qualified with the database name. Qualified names are > >>>>>> interpreted as referring to tables, not aliases. > >>>>>> > >>>>>> Statements containing alias constructs that are no longer permitted > >>>>>> must > >>>>>> be > >>>>>> rewritten. (Bug #27525) > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> On Thu, Feb 14, 2013 at 6:11 PM, Rick James <rja...@yahoo-inc.com> > >>>>>> wrote: > >>>>>> > >>>>>> Singer, do you have some examples? > >>>>>> > >>>>>> > >>>>>>> -----Original Message----- > >>>>>>> From: Singer Wang [mailto:w...@singerwang.com] > >>>>>>> Sent: Thursday, February 14, 2013 2:59 PM > >>>>>>> To: Mihail Manolov > >>>>>>> Cc: Mike Franon; Akshay Suryavanshi; <mysql@lists.mysql.com> > >>>>>>> Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 > >>>>>>> > >>>>>> > >>>>>>> There are queries that works with 5.1/5.0 that do not work with > 5.5, > >>>>>>> I > >>>>>>> would test extensively.. > >>>>>>> > >>>>>>> S > >>>>>>> > >>>>>>> > >>>>>>> On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov < > >>>>>>> mihail.mano...@liquidation.com> wrote: > >>>>>>> > >>>>>>>> You could jump from 5.0 directly to 5.5 and skip 5.1. I have > >>>>>>>> without > >>>>>>>> any issues. There are some configuration file change, which you > >>>>>>>> may > >>>>>>>> want to consider checking. I definitely recommend upgrading your > >>>>>>>> development servers for an extensive testing. Some queries _may_ > >>>>>>>> run > >>>>>>>> slower or not work at all and you may have to rearrange how you > >>>>>>>> join > >>>>>>> tables in your queries. > >>>>>>>> > >>>>>>>> The upgrade from 5.5 to 5.6 should me smoother, though. > >>>>>>>> > >>>>>>>> > >>>>>>>> On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: > >>>>>>>> > >>>>>>>>> Great thanks for the info, I guess the best way to do this is > >>>>>>>>> take > >>>>>>> a > >>>>>>>>> spare server, set it up with our standard setup, and then start > >>>>>>>>> the > >>>>>>>>> upgrade as you said 5.0 -> 5.1 -> 5.5, test and then upgrade to > >>>>>>>>> 5.6 > >>>>>>>>> and test. > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
