MySQL Restore Points

[eluser]CoffeeBeanDesign[/eluser]
Hello All,

I've written a generic CMS which I'm using for my CodeIgniter sites (MySQL). I'd like to add in something like a system restore facility. I'm thinking of having a 'last_restore_timestamp' field somewhere then do a time check on user log in (if it's longer than say a week, create a new restore point automatically). My question is, what do you guys think would be the best way to store and retrieve the data? I thought perhaps dumping the DB (they're only small sites) to a text file then running the SQL if it is selected. I could create a housecleaning function to delete files older than a few months or something too.

Do you think this would be an efficient method?? What is the best was to create the dump files and run the SQL?? Have any of you had experience with this type of functionality, what were your experiences??

There is also the slight issue of assets (uploaded images and files) to think about but I will worry about that later. I look forward to hearing your suggestions.

[eluser]uptime[/eluser]
I'm working on a hosted CMS solution which is very close to launch, here's how I do it:

Master -> slave replication for nearly up-to-the-second data at the office with (slave) backups every 10 minutes.

It doesn't effect the performance of the system because it's running on a local server.

Most of the tables use the InnoDB db engine, simply because I think locking a table as a whole is retarded (or just a missing feature in MyISAM).

On the master mysql server, I use mysqldump --single-transaction every 12 hours which means it locks rows instead of tables which I assume could take a little longer but obviously doesn't cause the whole application to freeze up (due to table locks).

You can use rsync to have the same setup with assets (or system's files).

[edit]
You can create "restore points" just by reading a list of backup-files, you can unzip/gunzip/tar them, drop all tables and import/execute the sql file using.

Pretty easy but yet risky.

What about having multiple databases (with the same permissions)? Sounds a little better but memory consuming.

[eluser]CoffeeBeanDesign[/eluser]
Thanks for the info. Sounds interesting.

The data in the DB is only updated through the CMS (page content, slugs, image data etc) so there isn't any need for regular timed backups because it only changes when they log into the CMS. I was thinking of simply adding the restore points when they log in (if it is more than a set time since the last one). Ideally, I'd have a page which listed the current restore points and let them select one. Could you elaborate on the proper (or best) way to do the dumping of the data and the importing back??

Many thanks.

[eluser]uptime[/eluser]
I have a reason to have such automatically backup, that project hosts multiple sites, it has 10 websites (some are mine, some belongs and friends and/or customers).

Without backup, if case something goes wrong I could lose those 10 sites or a lot more, depending on how successful the project will be.

If you don't need such a backup, there are enough ways to back up your data.

If the database if big, I would definitely recommend to use the command line rather to write the data with php, it -so- much faster.

Your options are probably:

* command line: mysqldump
* cPanel (do you have it installed on your server?), you can use their db backup link (it doesn't change but you do need the login info)
* php: generate the sql file yourself ( or use a pre-made script, see http://www.phpmybackuppro.net/screenshots.php )
* --> Codeigniter's build-in Database Utility Class (probably the easiest way of all)

See this:
http://ellislab.com/codeigniter/user-gui...ities.html

You can export as CSV, XML or back it up (to sql, I believe, have only used it once)

Good luck!

[eluser]CoffeeBeanDesign[/eluser]
Fantastic!

Thanks for the help. I hadn't read about bdutil before, looks very handy. I've just got to figure out the best logic now. All the sites have very modest databases so this will be perfect.

Thanks again for your time.