PHP MySQL Backup Script

[bkessell]

Would someone be willing to help me by writing a php script or shell script that can be run from cron jobs that will backup a mysql database? If someone decides to accept this asking of a favor, could you also have the script send an email alert with the backup file (either .sql or .gz) to an email address? I have never run a php script or shell script from cron neither. I have tried so many times to get these to work, but I keep getting errors.

Thanks in Advanced!!

(P.S. - This is going to be used on a site that I developed for a school, so if anyone could help, it would be greatly appreciated)

[@how@]

cd /
mkdir sqlbackup
nano -w /etc/cron.daily/sqlbackup.sh
then add this with you mail

#!/bin/bash
(
cd /sqlbackup
/usr/bin/mysqldump --create-options --compatible=mysql40 --all-databases --force --user=xxxxx --password=zzzzz > sql.sql | mail -s "All SQL db backup done" email@domain.tld)

chmod +x /etc/cron.daily/sqlbackup.sh

you will see sql.sql in folder sqlbackup this backup to all db ;)


Wael

[bkessell]

I do believe that this line of code is going to do some good for me. Thanks Alot!

[@how@]

--user=xxxxx --password=zzzzz
set your username & password.
you will get mail empty (good) if error in backup you get error mail :)


Wael

[resolveit]

You could also simply TAR the directory and when you restore it just untar it. Saves a lot of CPU cycles and the headache when the sql file get's too large for importing in a single command :)

[xemaps]

see these links could help too :

http://www.cyberciti.biz/tips/how-to-backup-mysql-databases-web-server-files-to-a-ftp-server-automatically.html

http://bash.cyberciti.biz/backup/wizard-ftp-script.php

[pucky]

There is a mysql backup script available for free on Sourceforge that does just want you want. No need to re invent the wheel.

We use the script to backup all our user dbs on a daily basis in addition to the daily backups.

The script name is; automysqlbackup. Do a search for it on google and you should find the link to Sourceforge where you can get the latest version.

[chaser010]

I would like to do exactly the same thing but instead of mailing the sql database I would like to FTP the database to an offsite location daily. Oh and not all databases just for example: admin_sample