I made an error... (Stupid)
I wanted to update a record in a database, so in mysql i ran:

update table_name set column_name='my new data';

But, as you can see, i forgot the 'WHERE' clause, and now every row contains this data.

Is there some kind of trick to get the data back? (Some kind of Undo)

I tried 'rollback' but that generated an error. It looks like i did not activate rollback functionality. (autocommit)

Is there some logfile where I get the data back, or is there at least some logfile of the outpu of all commands I ran? (I saw a tee option, but never used it. Is there somewhere a default file with this info in it?)

Any help would be grately appriciated.

A very stupid and thus sad person.....

...you're pretty well up the creek. Backups are always highly recommended.

If this is a business system and you aren't ghosting your entire system, you should at least back up mission-critical data on a daily basis. This data includes your MySQL databases and possibly your entire /usr partition (in nearly every distro, this will also include your /home tree, because /home is usually a symlink to /usr/home).

I recommend, if you are using the box in question to maintain business-related data, that you invest in at least a 10GB tape drive. They aren't cheap, but they most certainly will give you a rollback of sorts in case this happens again. Also, turn off auto-commit as well. Much safer without it.

If you want information on backing up your MySQL tables, go to the documentation at http://www.mysql.com and search for both "mysqldump" and "backup" in the manual. I can't remember the section numbers offhand, but I believe they are 4.8.x. These tools will be invaluable, especially if set up with cron to automate the process.

Yup.... I know that... However, it wasn't a business critical system, so no real harm is caused. It was a table containing some kind of "weblog cq guestbook" for the users of my site.

But, one always finds out too late that one should have made a backup.....

Before i did the update statement, I did a select * statement.

So some records I could see the data of, so manually, i can put that in again. But my screen wasn't big enough to hold all records. I was hoping mysql (or even linux) kept some kind of logfile.

If there's someway of telling wich data was in it, before I ran the update statement, i would be helped alot. Then it's only going to take some time to get all the data back in, but at least I know what data there was....

Isn't there some logfile somewhere???? I'd settle with anything, as long as i can read the data that was in there. Getting it back into the database isn't the problem.....

Does anyone know if its possible to find the data back? Or at least find out what used to be in there, so i can reinsert it???

Alas, shy of shipping your hard-drive off to a specialty lab, there is no real way of getting your data back.

Additionally, autocommit can only be turned off on InnoDB and BDB tables. See http://www.mysql.com/documentation/m...ce.html#COMMIT

As for log files, there is a history file associated with mysql the client: ~/.mysql_history . It won't have the output from your select * SQL, however.

If your data was generated via GET commands through your website, then you could whip up a perl script to recreate the data from your weblogs. If it was via POST, then unless your CGI/PHP/JSP, etc pages log information, that info is lost.

My 0.063 cents....

-G

Thanks all... It seems I just have to start over....