FYI: mySQL export/import

pSouper · Post by **pSouper** » Wed 26. May 2004, 21:16

Hi All,
I have just had to export my database from remote server and imported it to my localhost. I was getting an SQL error that meant I could NOT import the database

The reason for the error was that the table 'phpwcms_articlecontent' was just to darn big. mySQL has a 1mb default on it's 'max_allowed_packet' or to us simple folk maximum individual 'query' size. As your site grows (and it will

) you to will have this problem if you should ever need to import a backup of your databes too.
There are a number of fixes if you have control over my.ini but if not a quick hack to your .sql will be equally good...
isolate the table 'phpwcms_articlecontent' then perform a search & replace...

Find: ),

Replace with: ); INSERT INTO `phpwcms_articlecontent` VALUES

This effectivly breaks your single large query into tiny manageble sizes.

I hope you never have to use it but if you do I hope it helps

frold · Post by **frold** » Wed 26. May 2004, 23:19

I have this little cool tool I use when I need to upload huge databases,,

The program is 24kb and let you chunks a database in to peaces of 250kb...

If you database is 1mb you will have 4 files that is 250kb big and named 1,2,3,4...

Very fancy little tool...

I will share it with you

http://www.frold.dk/filer/MakeChunks.zip

pSouper · Post by **pSouper** » Thu 27. May 2004, 00:01

Mmm that app is as smooth as a babies bottom (trust me, i know)

SNap · Post by **SNap** » Thu 27. May 2004, 08:40

hmmm strange, on my Forum my Database is about 60MB big, and i have no Problem reimporting it on my local site.

how did you backup? mith 'mysqldump' ?

Pappnase · Post by **Pappnase** » Thu 27. May 2004, 08:42

hello snap

look at this link! maybe you use mysql front?

http://www.script-test.de

SNap · Post by **SNap** » Thu 27. May 2004, 10:44

nope i use commandline 'mysql < dumpfile' (not correct syntax!)

pSouper · Post by **pSouper** » Thu 27. May 2004, 16:22

I had just used 'Export' form phpMyAdmin.
then loaded the file back in via the SQL query (file browse).
By default Max_Allowed_Packet = 1MB but I was unable to affect a change for some reason.
I have strange things affot with my mySQL installation... my.ini is show as per the file within my 'winMySQLadmin' BUT the opertation is not as stated regarding passwords or set_variables.

but it works so I'm NOT touching it

SNap · Post by **SNap** » Thu 27. May 2004, 16:59

you shouldn't use phpmyadmin for such big backup/restore actions! to slow anyway

do it with mysqldump and mysql < dumpfile. much faster and reliable

frold · Post by **frold** » Thu 27. May 2004, 17:12

SNap wrote: do it with mysqldump and mysql < dumpfile. much faster and reliable

What is that?

pSouper · Post by **pSouper** » Thu 27. May 2004, 17:18

thanks.

of course I live an a small world that worship the god of 'crap' we bury our heads in the mud and show our bottoms to the sky religiously once every lunar month. Our entire belief system revolves around the premise that by sacrificing our databases to him (or her) on a regular basis at great time and cost (the more the better) he (or she) will not shower us in sh*t for another four weeks.
in short: if it is too quick and easy for us to backup & restor the sacrifice will not be so effective

SNap · Post by **SNap** » Fri 28. May 2004, 22:21

frold wrote:
What is that?

What is what?

make a backup with my script http://www.phpwcms.de/forum/viewtopic.php?t=2166

and restore with

Code: Select all

mysql -h host -u user -p password database < ./dumpfile

did you mean that?