Want to create a script that takes backup of entire database and downloads it

Question

Hi guys, I've built a simple cms with an admin section. I want to create something like a backup system which would take a backup of the information on the website, and also a restore system which would restore backups taken. I'm assuming backups to be SQL files generated of the tables used.

I'm using PHP and MySQL here - any idea how I can do this?

Answer 1

If linux/unix, check out "man pg_dump". It basically dumps whole databases (with all tables and table definitions), e.g. "pg_dump mydb > db.sql"

Answer 2

One simple solution for backup:

• Call mysqldump from php http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html, and save the backup file somewhere convenient.
• This is a safe solution, because you have mysqldump on any mysql installation, and it's a safe way to generate a standard sql script
• It's also safe to save the whole database.
• Be careful with blobs, like saved images inside database
• Be careful with utf-8 data

One simple solution for restore:

• Restore operation can be done with the saved script.
• First disable access to the the site/app.
• Take down the database.
• Restore the database from the script with mysqlimport http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Calling external applications from php:

http://php.net/manual/en/book.exec.php

Answer 3

Create a script that calls mysqldump

Answer 4

Is it just for yourself or do you want it as a feature of the CMS?

I would advise using MySQL Administrator from www.mysql.com to create backups. It's a very useful tool which you can use to schedule backups from external databases you have access to. It allows you to restore to and be selective over which tables.

If it's for a feature of the CMS then a) I'm not sure that's a great plan and b) one of the above should do it!

Answer 5

phpMyAdmin and SQLBuddy have functions to import/export the entire database.

Answer 6

for the backup part you can use a ready solution. check out astrails-safe for mysql/pgsql/plain_files/subversion backup with encryption and upload to S3/SFTP.

shouldn't be too hard to add the restore capability (basically just decrypt, decompress, and pipe data to the appropriate command). for mysql (w/o the encryption):

zcat -d mysqldump-blog.090820-0000.sql.gz | mysql database_name