Dropping tables in MySQL

If you drop a table in mysql can you reactivate it? or is it deleted?

As far as I am aware once a table is dropped in MySQL thats it, its gone. I have lost entire DBs simply by pressing the x in phpmyadmin.
I now back up my database before I do any work on them.
I hope someone proves me wrong as it would be useful to know.
All the best.

Pat

You can restore it from your most recent backup

I believe it does destroy the database though. I don't mean to hijack this thread or anything but obviously backing up your DB's is V. important and being new to MySQL and PHP I was just wondering how people go about this. What's the best way?

This is coming from an man who lost 400gb of data off a computer last thursday and lost another 150gb yesterday (hard drives died on me but 2 in a week is bad luck) and has paid a small fortune (well not that much but I shouldn't have lost the data in the first place) to get it retrieved. So lets just say my backup policy isn't a strict one nor is it flawless (it doesn't exist).


Pete.

I am assuming you use phpmyadmin to add or remove from your db, the backup is quite easy. providing you have stable HD'S (LOL).if you export your database to a text file in phpmyadmin you then have a full backup of your db which can then be imported back into your db. you may have to dump the existing tables in your db to allow the import.
I have done this many times, I also keep a copy of my latest db on another computer incase things go wrong.
I hope this makes sense.

Pat

That's cool. I didn't realise it was that easy. Is there nay way I could do this automatically every 24 hours or so or would this be more complex? Obviously if its being updated by me occasionally like say a basic info site then it would be fine but for a blog or a forum you would need regular backups. I'd simply forget.

Pete.

I am sure this is possible but the answer probably exists with someone like grahame or another db expert. I think sessions may be your answer but I am not sure

mysqldump + cron.
I suppose you could do the backup using PHP's exec() function if you really wanted.

Was in the wrong database anyway so it didnt really matter lol

Grrrrrrrrrr.
I wrote a PHP script to backup the database when executed by cron, then I went to check mysqldump syntaxes and I hit the [insert expletive here] "Google Earth" logo on the Google toolbar instead of the tab and I lost the post.

So now I am writing it again and my relationship with the Google toolbar has gone very much downhill.

Formatting didn't work inside the PHP tags, but it should give an idea of where things need changed.

PHP: Select all

<?php
$time = time();
$command = "/usr/bin/mysqldump --quick -u [i]username[/i] -p [i]password[/i] [i]db_name[/i] | gzip > ~/backups/$time.mysql.gz";
/*
/usr/bin/mysqldump   is the path to the mysqldump program
--quick   stops the program from loading the whole result into memory
-u [i]username[/i]   is the mysql username
-p [i]password[/i]   is the mysql password
[i]db_name[/i]    is the name of the database
| gzip    is an example of piping. The output from the bit before the pipe is passed to the 
command after the pipe. i.e. gzip....this compresses the result
> ~/backups/$time.mysql.gz    saves the file with a unique filename (Unix timestamp)
*/


exec($command);

?>

I would have posted the cron code here, but the webforumz servers throws a 406 Not Acceptable error at me.
The only other time I have got this message here is when I modify HTTP headers.

So if anyone actually cares, I can post it to pastebin or similar.


This requires you to have pretty much control over the server. e.g. Owning it.

I got the mysqldump syntax stuff here (Quite useful if you are doing this).

I can't test the PHP script since I don't have any physical access to a server with a database.

Oh, so its nice and simple then . Being completely useless at this sort of thing, I'll probably leave it for now. It was mainly out of interest though it is something I will have to learn how to do at some point. I have become deeply concerned about every last scrap of info I ever save anywhere and in a blog/ forum situation the it would be very useful. Imagine an e-commerce site didn't backup their DBs, it would be a nightmare. I am learning php so I can really take advantage of it (I am starting with a blog and will build up to a MySpace beater in the end). Is the alternative to manually save the backup the DBs.

By the way, balaclave, thank you. You are a great asset, you put in so much time and effort I don't know what I'd do without you. I happen to know that the rep points work, after a fashion (open in new tab), so you can have some from me!

Pete.

My import/ export to phpadmin looks like a freeway. I figured that one out on my own after loosing a very large important database when I accidently hit EMPTY...at least I still had my column headings....

Gutted!
phpMyAdmin is one of those annoying programs where it asks for confirmation using javascript, so I'd imagine if you had it turned off, then I suppose you're screwed.
Another problem with the javascript confirmations is when using prefretch tools like Google's web accelerator...it goes and tries to fetch pages before you visit them, e.g. the links to dump the database, but ignores the javascript warnings.


Sometimes if I try to manually export a database in phpMyAdmin, exporting to a filetype like gzip causes it to crash if the file is quite big.

The only option which works in this situation is export to text, which is annoying because it really exports it into a textarea, not a txt file.
When I had to do this I copied and pasted chunks from the textarea to notepad (because my computer couldn't handle copying and pasting 15mb at once), which took a long time.

However it just hit me while writing this post that a better way to do it would be to save the page where the db export is loaded into a textarea, and then remove the extraneous HTML from the file.
Or choose a host where phpMyAdmin works properly.

@pete:
Thanks