Backup MySQL Database with PHP, then encrypt file for upload to another server

Question

I've read several SO about related code snippets and suggestions for this, but I don't think there has been consensus between them for my task, so I'm combining some questions to ask once more. Also, the other ones were pretty old, and I'd like to get some updated responses...

The Goal

• Dump SQL DB completely (in such a way that this could be imported back into MySQL should the DB be wiped out...). Ideally, this sql dump could be imported via PHPMyAdmin, for example.
• Take the dump, encrypt it via PHP5 (I am thinking with mcrypt).
• Take the encrypted file, and upload it to a remote server via SSH2 in PHP.
• Delete local encrypted file and local unencrypted dump.

On the reverse - should I need to access the saved backup

• Manually SFTP into remote server where backups are stored.
• Donwload encrypted backup.
• Decrypt it.
• Manually import back into MySQL via PHPMyAdmin

I don't really need a script to do the reverse process. Should I ever need a backup, I'm already deeply involved in getting things back up and runing - so decrypting manually shouldn't be a problem. However I do want to be sure I know the best way to do it, beyond reading the PHP manual...

Answer 1

Thanks tim. I found a good pre-built solution in PHPMySQLAutoBackup . I am adding in the code which will encrypt with GPG using system().. although its not without my hardships at the moment. I will open a new question if I continue to have GPG issues...

Answer 2

Sounds pretty straightforward, really.

If you want to script it in PHP, that's fine. However, you probably don't need much PHP functionality. You'll basically just be using simple control structures, shell_exec(), which you'll use to drive a couple of command-line tools, such as mysqldump, gpg (or other crypto utility), scp (to send your data offsite), and unlink() to delete your local copy.

So it sounds like you just need some pointers to command-line tools. I mentioned the one's I'd use above. Beyond that, it's just a matter of putting together the right command-line strings and exec() or shell_exec()ing them.