I need to take a backup of a MySQL database daily, and preferably have it email itself to an email address of my choosing.
Is it possible to do this? What is the SQL to generate a backup file? How can I add this file as an "attachment" to the email which is sent?
Thanks for the help, I really need to make these backups!
I do know how to call a script via cronjob, so the daily thing is not a problem.
You can use exec to call mysqldump in the command-line to create a backup of your database to a file of your choosing.
mysqldump -u username -p password databaseName > databaseFile.sql
Then in PHP, you can locate the file and upload as an attachment and send it out.
I do not know how safe sending the database is via e-mail, if there is alternative solutions I could suggest, I would.
Per Tom's comment, take a look at PHP Zlib and creating GZIP files "on the fly" to zip files through PHP.
Simply drop to a command shell and run a mysql_dump command like so:
mysqldump <DB name> -u<user> -p<password> | gzip -c > /backup/`date +%Y-%m-%d`.sql.gz
From this point you can just use the mail command (I don't know the syntax off hand to attach a file).
Automatic MySql Backup Script
This script finds all of the MySQL databases on your server, individually backs them up, compresses them, and stores the file in a specified directory and/or emails it to you. It has been updated since the initial public release and includes several new features.
http://www.dagondesign.com/articles/automatic-mysql-backup-script/
I used to use
MySQL database backup ===================== http://restkultur.ch/personal/wolf/scripts/db_backup
However it's a bit old, and I'm not sure if they upgraded the version etc. But it works well, and givse you lot of backup options (how many days to keep, months, as well as where to store the file - it can be uploaded via FTP to another server for redundant security).