Topic: mySQL Backups (Read 5674 times)

pseudonymph · « **on:** March 03, 2005, 08:34:23 AM »

So I was trying to upload my site backup tables since I screwed something up, and everytime I upload them it says 'The document contains no data'. I have opened the file and see all the tables there. So do you think the file is too big and the server is timing out? Can I slip it in half or do I need to split it more? It's 88 MB.

Demosthenes · « **Reply #1 on:** March 03, 2005, 12:41:00 PM »

88mb doesn't seem that big... maybe the tables are becoming corrupt in transit or something? How are you transferring the database?

pseudonymph · « **Reply #2 on:** March 03, 2005, 01:50:47 PM »

I'm currently using phpMyAdmin

hackess · « **Reply #3 on:** March 03, 2005, 02:46:06 PM »

It sounds like your backup might be corrupt.

Demosthenes · « **Reply #4 on:** March 03, 2005, 05:11:58 PM »

That's possible too. Either that or it could be a permissions issue, but I'm not sure that would be anything you could do anything about.

pseudonymph · « **Reply #5 on:** March 03, 2005, 06:55:10 PM »

damnit.

at htis point, I'm wondering if I should just start over. I dont even know where to begin with that... do I delete all the tables and reinstall?

ivan · « **Reply #6 on:** March 03, 2005, 07:18:35 PM »

Who cares about the SQL shit... Where da games at?

pseudonymph · « **Reply #7 on:** March 03, 2005, 10:01:07 PM »

they run off SQL! I swear I just wanna cry and if I get this goddamn site back up I will never touch the tables again!!!!

I think I will just get rid of the postnuke and have the forums with a portal. They have the games for IPB too

you think I can just deleted all the postnuke tabels and reinstall the forums? I guess I will have to start from scratch unless I can save the tables just from the forums....

ivan · « **Reply #8 on:** March 03, 2005, 10:18:38 PM »

Dang, I wish I had more experience with mySQL.

Is there a way to convert the backups into flat text files and importing them into empty SQL tables? Or am I talking out my ass?

pseudonymph · « **Reply #9 on:** March 03, 2005, 10:23:04 PM »

yeah, I can open the .dmp file in notepad to view it. but then I just sit and stare at it and wonder what to do next...

hackess · « **Reply #10 on:** March 03, 2005, 10:30:42 PM »

Quote from: ivan

Dang, I wish I had more experience with mySQL.

Is there a way to convert the backups into flat text files and importing them into empty SQL tables? Or am I talking out my ass?

I'm not sure what format pseudo's backup was exported in, but phpMyAdmin just spat a backup out for me in plain text. *shrug*

ivan · « **Reply #11 on:** March 03, 2005, 10:42:57 PM »

If it's a text file, then there's a way to import the data into a SQL table.

If it's not a text file (dumps a bunch of ASCII garbage on the screen when opened with Notepad), then you need a way to convert it to plain text..

pseudonymph · « **Reply #12 on:** March 03, 2005, 10:46:06 PM »

hmmmm...

let me try something.

ivan · « **Reply #13 on:** March 03, 2005, 10:49:16 PM »

Again, I'm just making this up as I go along, but if the .dmp file is plain text, then the problem might be that mySQL doesn't know how to parse it. If it's a backup of an entire database, then there must be some delimiters that determine where a table begins and ends, and where columns begin. There may be table names and column names, as well.

If mySQL can't be made to parse the backup file, then the file will have to be broken up into separate files, one for each table, and imported one by one into predefined table structures in the new database.

hackess · « **Reply #14 on:** March 03, 2005, 10:51:38 PM »

mySQL backup exported as .sql, and opens in Notepad, plain text.

ivan · « **Reply #15 on:** March 03, 2005, 11:02:20 PM »

Google reveals some intersting bits:

Quote

i noticed on the memberops area under mysql, there are several days backups of the data. they have an extention of .dmp (dump files).
how do you restore one of these to retrieve information. i noticed that in the .dmp is scripting, as well as all of the data im missing.

You will have to download the .dmp file and unzip it, then upload to your server.
Place it in the same dir as the script and use that filename to restore your DB.

And another...

Quote

ok, i've searched a lot, and i couldn't find a solution for my problem...

i have a backup of the database in .dmp format and i need to partially restore it (there were some deletions by mistake in it and i need to restore some part of the records).

the problem is that i can't extract the file - i found NXTract utility, but it doesn't work with CLOB fields (and i'm not willing to pay for it, plus i'm not sure if commercial version does support it)

i know that i can import that .dmp file into the database, but that way all records will be imported and changes made after the backup was done will be removed. even if they won't be removed - there will be lots of mess.

i just need to extract some records with some criteria out of that file. is there any way to do it?

i don't have administrative access to oracle neither can i install it localy. that would take lots of time, and i need just part of the records in that dmp file... so basically i drop the idea of creating the new database, importing the stuff there and extracting the needed data only with some script.

well i hope someone will understand me and help me... thanks.

If it's only a few records in a couple of tables you could do this:

1. rename the tables you need to import
2. import just the tables you need import .... tables=(t1,t2,t3)
3. now you can delete/insert into the table from the backup you made in step 1.

this would be quite cumbersome to do with an entire db but if it's only a few rows it would be ok.

Seems like there might be some clues there...

pseudonymph · « **Reply #16 on:** March 03, 2005, 11:22:15 PM »

I think I may have to try that second bit.

turns out the max upload is 2000kb or so... so I guess it is timing out and not accepting the file size of 88MB.

its a little late now, so I will try tomorrow... thanks guys

Anonymous · « **Reply #17 on:** March 04, 2005, 06:58:16 AM »

Quote from: pseudonymph

I think I may have to try that second bit.

turns out the max upload is 2000kb or so... so I guess it is timing out and not accepting the file size of 88MB.

its a little late now, so I will try tomorrow... thanks guys

I was going to say that. It's a good thing I browsed to the end and realized that you said that, Pseudo.

Sounds like a cron job, or just simple SSH.

Demosthenes · « **Reply #18 on:** March 04, 2005, 01:18:16 PM »

Quote from: Chris

Sounds like a cron job, or just simple SSH.

Sheesh, Chris. Aren't you a little young to be offering to give people cron jobs?

ivan · « **Reply #19 on:** March 04, 2005, 01:24:04 PM »

I always type cr0n to fuXor the filters.

Anonymous · « **Reply #20 on:** March 04, 2005, 03:28:18 PM »

Chris is the offical cronstar from now on.

pseudonymph · « **Reply #21 on:** March 04, 2005, 07:30:01 PM »

well, I just got a very promising email:

Quote

Hello Syn,

Please provide us with the location and the name of your database backup so
we can have the admins restore it for you.

Thank you for choosing PowWeb,
Jeffery F.

PowWeb, Inc.

yay!

now I dont have to spend all that time breaking it up when I can spend it drinking beer and watching a movie with the cats!!!

ivan · « **Reply #22 on:** March 04, 2005, 07:42:14 PM »

Can I come over?

I like movies, beer and cats.

pseudonymph · « **Reply #23 on:** March 04, 2005, 10:21:50 PM »

absolutely! take the red eye, I'll have a cold one waiting

Demosthenes · « **Reply #24 on:** March 07, 2005, 10:21:24 AM »

Nice avvie, Pseudo!

News:

Recent Forum Posts

Shout Box

Author Topic: mySQL Backups (Read 5674 times)

ivan

ivan

ivan

ivan

ivan

Anonymous

ivan

Anonymous

ivan