Is it possible to store all the sql statements as they are processed

[o0O0o.o0O0o]

hi ,


I recently had a database crash , now the company owner wants the full security. I have daily backups at 4 Am in the morning . But if database got crashed in the night then all the customer orders from 4AM is lost

So what i was thinking that is there any way to store all the sql statements as they are run in a .sql file on the server for each day.

so that even if the whole database is gone , then i can go back to previous back up and the execute that sql file to get those orders and all details.

[gnomeontherun]

Just have PHP write the SQL statements to a text file, which is cleared daily. So after every execution, just have it write it also to the file.

http://www.tizag.com/phpT/fileappend.php

[oracleguy]

Just have PHP write the SQL statements to a text file, which is cleared daily. So after every execution, just have it write it also to the file.

http://www.tizag.com/phpT/fileappend.php
But what happens if the server's hard drive that contains the db and the text file is lost?

The only way to protect that is to make sure the data is stored on some sort of fault tolerant data storage (RAID 1 or 5 or 6 as an example).

Still that doesn't mean the filesystem couldn't be corrupted or something. Basically you can put in 50 safe guards and there is still a chance some data will be lost. I'd say have a cron job run every 15 minutes that would back up that text file to a different server.

[PappaJohn]

This might be obvious, but just in case ...

You wouldn't need to write all queries to the file, just those that alter the database. UPDATE's, INSERT's, DELETE's - no need to store the SELECT's

[o0O0o.o0O0o]

But what happens if the server's hard drive that contains the db and the text file is lost?

The only way to protect that is to make sure the data is stored on some sort of fault tolerant data storage (RAID 1 or 5 or 6 as an example).

Still that doesn't mean the filesystem couldn't be corrupted or something. Basically you can put in 50 safe guards and there is still a chance some data will be lost. I'd say have a cron job run every 15 minutes that would back up that text file to a different server.

what i will be doing is to email that txt file once it completed and once after evry hour or so on yahoo account everyday .

I don't know what will happen if 100 customers are accessing the site and how queries will be written on text file. Won't they mix up.

Is there any easy solution to this , I don't know in case i need to run all those queries again , i won't be 100% sure whether that will work or not. Currently there are about 100 admin scripts and 100 users scripts . All using database update, How can they be stored in order.

[oracleguy]

If memory serves, the proper way to handle this would be to use a DBMS that supports transactions. And then you could backup the transactions log.

The problem with just logging the SQL queries is that you'd have to make sure the queries executed in the exact same order otherwise when you restore the data some inserted data could have different primary keys than it did before the crash. What happens if 20 people press the order button at the same time? The DB might execute the queries in a different order than the website writes to the log file. Additionally it would cause problems with any information already sent to the client that utilizes said keys.

And if the primary key changes, DELETE statements later in the log file that use that primary key could delete the wrong data.

[o0O0o.o0O0o]

Is there any other way beside Transactions . I can't chnage 300 scripts now.

Usually there are tables which contains the sales information for the accounts people.

If i can email those tables after every hour or so , then at least i can have some data.



or i can log sql statements on few tables