mysql backup via php?

[Mosher]

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?

Thanks,

Mosher

[Chris Hope]

Mosher wrote:

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?

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 | www.electrictoolbox.com | www.linuxcdmall.com

[Good Man]

Chris Hope <bl*******@electrictoolbox.com> wrote in news:d593li$i6f$1
@lust.ihug.co.nz:

Mosher wrote:

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?

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.

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

[Chuck Anderson]

Chris Hope wrote:

Mosher wrote:

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?

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.

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") .
".sql";

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
http://www.CycleTourist.com
Integrity is obvious.
The lack of it is common.
*****************************

[Chris Hope]

Good Man wrote:

Chris Hope <bl*******@electrictoolbox.com> wrote in news:d593li$i6f$1
@lust.ihug.co.nz:

Mosher wrote:

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?

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.

.... though if your data is really important, perhaps regular e-mail
should be avoided, lest anyone snoop in on 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 | www.electrictoolbox.com | www.linuxcdmall.com

[devans14]

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_Backups\2005.01.24 SYSTEM mysql library
backup.sql"

--- 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 ("http://www.webyog.com") 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.

Daniel

[Mosher]

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

Mosher

"Chuck Anderson" <we************@seemy.sig> wrote in message
news:xt********************@comcast.com...

Chris Hope wrote:

Mosher wrote:

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?

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.

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") .
".sql";

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
http://www.CycleTourist.com
Integrity is obvious.
The lack of it is common.
*****************************