mysql backup via php?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Hi all, I am wanting to automatically do dump of a mysql database nightly
using cron and some kind of script that will email the whole db structure to
me (as an attachment or as plain text in my email) - including the data in
the db. So, if the db was ever lost, I could go to my email, find the dump
and copy and paste it into the mysql db again.

Does anyone know how to do this in PHP or of a premade (and free) script out
there, preferably written in PHP?



Re: mysql backup via php?

Mosher wrote:

Quoted text here. Click to load it

Dump it using mysqldump into a text file using one of the system() or
exec() type function calls, then either attach the resulting text file
to your email or make it the body of the message.

Chris Hope | |

Re: mysql backup via php?

Chris Hope wrote:

Quoted text here. Click to load it
I'm no great Php expert, probably not perfect Php, but here's the
function I use in my script:

function create_db_backup ($path, $tables = "")
$filename=$DBNAME . "_backup" . "_" . date("n-j-y_H-i-s") .

system("mysqldump --add-drop-table -u $USRNAME -p $PASSWRD $DBNAME
$tables > $path/$filename", $result);

system ("gzip $path/$filename", $result);

$gzname = $filename . ".gz";

return $gzname;

$path is the directory I save backups to.

$tables is optional. If empty, mysqldump will backup all tables,
otherwise it's a list of tables to backup (separated by a space, e.g."
table1 table2 table3" - no quotes).

I use --add-drop-table because it seems like the simplest sql file to
use for a restore.

Finally, I gzip it to make it smaller.

Now all you have to do is mail it to yourself. I use Phpmailer.

 Chuck Anderson Boulder, CO
 Integrity is obvious.
 The lack of it is common.

Re: mysql backup via php?

Thanks for the help all - I'll try what you mentioned.


Quoted text here. Click to load it

Re: mysql backup via php?

Quoted text here. Click to load it

..... though if your data is really important, perhaps regular e-mail should
be avoided, lest anyone snoop in on it...

Re: mysql backup via php?

Good Man wrote:

Quoted text here. Click to load it

Especially if it contains sensitive data. If you have ssh or sftp access
to the server you can always download the dump file via sftp which
makes it much safer. I manage my own servers and all the databases are
dumped to text files on a daily basis and rsync'd to my local machine
on a daily basis via ssh.

Chris Hope | |

Re: mysql backup via php?

I have a DOS batch file setup with the Windows task scheduler to run a
batch (BAT) file every morning. My script is dynamically created my an
HTA application that rebuilds it every morning (for the dates), but
that is not necessary to run the script. The basic batch scripting is
pretty simple.

--- Begin code ---

"C:\Program Files\MySQL\bin\mysqldump.exe" --databases --all
--allow-keywords --quote-names --flush_logs --host=localhost --opt
--user=root library > "D:\MySQL_Backups05.01.24 SYSTEM mysql library

--- End code ---
(Should be all on one continous line)

"--host=" is the name of the computer to connect to.
"--user=" is the login to use in accessing the DB.
Specify the database name after the "--user=root" section where you see
"library" in the line.
The text after ">" at the end is the destination path where the "SQL"
file is to be deposited.

The resulting text file is nothing more that a really big MySQL query
that rebuilds the databases, indexes, data, etc. I use the "SQLyog"
application v3.02 (" ") to restore the files. It
has an "Execute Batch Script" feature that reads in the SQL file and
processes it back into the database. I have to create the empty
database first, then restoring it is a piece of cake. I use this
technique to copy databases to my development system at home to work on
stuff. So far it works pretty good, and if you ZIP the file it
compresses really nicely.


Site Timeline