MySQL Backups and a lot more
A couple of years back, I had asked several people through groups, and posts the possibility of backing up a MySQL database programatically, from client side applications which could connect to remote servers running MySQL. There was a lot of suggestions,
* php script on server to take backup on server
* application code to select the data and do a round trip
* mysqldump from client machine with the server parameters
And finally from all those valuable suggestions from all the helpful people, I derived my own hybrid method which I would summarize in a few words. This is a method, which I have adopted for web applications in php as well as custom single machine applications in Visual Basic. How this can be termed as hybrid can be known by going into the structure, though it seems to be a bit complicated, it would serve the purpose and be worth the headache.
Using the application logic, using the SHOW TABLES statement, go through all the tables in the said DATABASE, issuing SHOW CREATE to get the CREATE TABLE queries, then use some sort of regular expression replaces to convert each CREATE TABLE into a single line query. Add DROP TABLE IF EXISTS line before each CREATE TABLE. Add a LOAD DATA INFILE <TABLE_NAME>.TSV, issue a SELECT * FROM TABLE_NAME INTO OUT FILE <TABLE_NAME>.TSV, with some options. The whole lot is saved into a folder, with the DROP .. CREATE .. LOAD set saved as the index file. For and example, if the backups are to be named in dMY format, 2APR2007 would be the name of the folder and that of the index file too. Finally either use tar-(b|g)zip combination or use zip to compress the whole lot into a single file, renaming the default .zip to what you fancy.
Several things can be considered to give frills, which will give a more professional look to the backup. One is to provide a comment at the top regarding the date, time, and no of tables in an easy format for the restore tool to parse. Adding a comment before and after each DROP .. CREATE .. LOAD chain to act as a separator and what not. All these can be utilized when designing the restore tool to show progress bar, backup details etc.
The bottleneck with the above implementation is that you will need access to the file system either through direct method or through ftp, scp etc. Without which both the backup and restore will not function. In case you are stuck in such a situation, the best method would be to use a server side language to do the proxy work, like use php to do the backups, and restore letting you handle only the final result. Means that the backup will provide the backup either as a download file, or save on the server or to a remote ftp site. And restore will accept either file upload, or a (ht|f)tp url to download the backup file, or let you browse the hosted server to locate an old backup.
In case your application language supports SOAP RPC ( tell me one which does not ), better steer that way, and publish your backup and restore as a SOAP Service. I am not finished with this article yet and may come back any day to add more views to this.