01-11-2008, 02:00 AM
Is it possible to save last three backups of mysql dtatabse on server automatically.
i want that i should have database backup for last three days so that i can revert back if problem occurs, because when usually when we know some problem has occured we are 2,3 days late .
01-11-2008, 06:32 AM
Crontabs and a tracking table / tracking records.
Consider how on OS chooses how to do incremental backups. Make a similar structure to you information, either choose to backup records on a per table changed basis, or on a record changed basis. If you choose the table method, create a new table that tracks if a table needs archiving - and add to your code the archive bit so when anything changes this is set to 1. For records, add an additional attribute that will track which records require updating in a similar fashion.
Then write a backup script (SQL has dump commands for it if you just want to do all), and have either a real cron do the work for you, or use a 'poor mans' cron if you don't have cron available (that is, a table tracking last updates and determining on a page load if backups are required). I don't recommend 'poor mans' methods for archiving.
No matter which way you do it, you will need to create a way of determining how and what data requires backing up. For specific days in the past, you will be responsible for creating a way to track creation date, and that may eat at your resources quite quickly.
To be quite blunt, its probably a lot more simplistic to just have a cron to execute a database dump script on all data than on specific data. The downside is that if you have a lot of data, this will take a lot of time. This would be a lot easier if you have access to the server itself (either physical or remotely) and can loggin. If you can do that, then this task is 100x easier as you can command line it.
With the command line in mention as well, its possible that this is an easier task to preform in Perl than php. Maybe check with the perl guys as well to see if they think it would be easier. This assumes a Unix or linux based hosting service as well.