Need to provide this facility on a button-click in my web application. Could be done using mysqldump, but for that I'd have to start an external process? (the command prompt)
Is there a simple SQL query that lets us backup and restore databases? (particularly for MySQL)
If you use MySQL 6, you're fine -- see http://dev.mysql.com/doc/refman/6.0/en/backup-database-restore.html . If you're on MySQL 5, things aren't so rosy -- you're probably better off with the mysqldump / external process approach.
You can either call the external command line executables here is a nice intro to that.
Or you run a couple of queries (like PHPMyAdmin does) and store the result somewhere by your own.
But there is no simple "one command" query for that.
mysqldump is probably your best bet. You can save table rows with SELECT ... INTO OUTFILE and reload it with LOAD DATA INFILE but you have to do it table by table, and write out the table schemas to another file for a complete backup.
What's so bad about spawning a process to run mysqldump?
(MySQL 6.0 will include the rather useful BACKUP DATABASE statement!)