Large MySQL Backup keeps failing

dhamma

dhamma

New member
Dec 2, 2008
145
4
0
I´m trying to backup my >250MB MySQL DB (version 5.0.51a @debian linux) and import it to a newer (local) version of MySQL 5.1.35 (@win 7).

Besides splitting up the dumpfile via bigdump - i get numerous errors. sometimes syntax errors (??) - sometimes relational constraints.
I´ve wasted hours on this problem .. and am deleting single lines via vim on a 250 MB file, download the dumpfile - just to find out that there is another error a few lines down the road... so I´ve to start this procedure again....

This is a pain in the a$$ and robs my time.

Any advice would be very welcome?

thx
 


how exactly are you try to dump your database (command line syntax, etc), also do you have shell access?

Normally for a very big sql file, I'll simply tar-gzip it, you'd be surprised how small that gets when you're not dealing with blob data fields.

Are you making sure to take the appropiate safeguards like full/extended inserts, `quoting` the field names, etc?

PS: There may be *some* issues alone going from 5.0 to 5.1 without actually upgrading your database depending on the manner that you're doing it (ie: raw files vs sql statements). In a local-remote development situation, I always recommend keeping the versions the same between both systems to help rule out issues due to version incompatibility.
 
I create the dump via command line - mysqldump
don´t have blob data - so that´s no issue.

And yes - the problem has probably to do with some 5.0 5.1 incompatibilities.

Thanks - I´ll probably have to downgrade my dev-version.
 
thx again ;-)
14.jpg
 
PS: try to use

Code: 
tar -zcvf mydump.tar.gz mydump.sql

after you dump so that you can more easily download the file onto your machine (though if you can't figure out how to untar/zip the file on a windows box you can instead use gzip -r9 mydump.zip mydump.sql to create a zip file).
 
Going from 5.0.x to 5.1.x should work fine. I had a small issue going from a 5.1.x backup to a 5.0.x system, but a small one-line change to the a table CREATE command fixed it.

Check out this command-line option for mysqlbackup:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compatible
--compatible=name Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.8, “Server SQL Modes”.
This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.
This option requires a server version of 4.1.0 or higher. With older servers, it does nothing.
Click to expand...
 
thanks supergeek. --compatible= param would´ve helped def.

the main issue in my opinion was, that I was dealing with very long insert statements - which somehow broke the bigdump importer. The relationship constraints have definitely to do with version 5.0 vs. 5.1 incompatibility.

Anyways - copying the .frm, .MYI, etc. files worked out for me.

thanks to everyone
 
Just out of curiosity, was the dump using multiple insert commands on a single line?

e.g.
INSERT blah blah; INSERT blah blah; INSERT blah blah

There's a commline option to use just one command per line, although I don't know it offhand. Just an FYI, and if anyone else searches on this topic in the future.
 
I´m not sure about that - could well be.

most errors appeared on huge insert statements on ONE single line.
Think of a Search-Index with ~4MB per Statement.
Will have to take a look at --commline too.
 
dhamma said:
I´m not sure about that - could well be.

most errors appeared on huge insert statements on ONE single line.
Think of a Search-Index with ~4MB per Statement.
Will have to take a look at --commline too.
Click to expand...

FyI having inserts combined will almost always cause a problem, best to do one per line like supergeek said (easier to resume from too).
 
You must log in or register to reply here.
Top Bottom
Back