473,320 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

mysql backup via php?

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
Jul 17 '05 #1
6 1782
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
Jul 17 '05 #2
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...
Jul 17 '05 #3
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.
*****************************
Jul 17 '05 #4
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
Jul 17 '05 #5
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

Jul 17 '05 #6
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.
*****************************

Jul 17 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: James | last post by:
HI, I'm looking for a script that will allow users/admins to have a one click backup solution for a MYSQL Database.. 'BACK DATABASE' button, click and its done... The a restore option, that...
4
by: Neil | last post by:
Hi, I hope this question isn't too far off topic....I'm almost at my wits end trying to figure this out. I have a Mysql database and I wish to automate the backup of the database because I...
1
by: Konrad | last post by:
I have an internet portal based on MySql database and what I need is to make a database backup , after each actualization of data. I know how should PHP code look like but I have no idea how to...
0
by: Duane Winner | last post by:
Hello all - I'm having a small problem with the mysql startup script that ships with MySQL-3.23.56-1. I'm running on RedHat Linux. It works fine, but I have a backup server that runs a script...
3
by: John Smith | last post by:
Hi I am currently reviewing our backup procedures. Part of the issue has been finding the right fit. There are obviously backup products that provide mysql agents but unfortunately can not for...
1
by: Astra | last post by:
Hi All I understand that the basic principles to create a TCP/IP backup program for a remote MySQL DB are: a) Query/retrieve the schema. b) Query/retrieve each table and create inserts...
4
by: news | last post by:
Our production database in an exported textfil runs about 60 MB. Compressed that's about 9 MB. I'm trying to import the export into another machine running FC3 and mySQL 11.18, and it appears as...
0
by: newman | last post by:
Dear all, I have mysql 4.1.11 on my current server, i need my database restore another server.. (another server mysql version is 4.1.11 same.) And now... I just created new my database to new...
6
by: frank78 | last post by:
Hi everyone, I am having a little bit of trouble backing up some mySQL tables. I've been trying to adapt a script I found on the internet at...
4
by: Bootstrap Bill | last post by:
I'm looking for a PHP program to backup and restore a mysql database. I'm using Godaddy to host a forum. Their mysql control panel will only restore a database of two megabytes or less. My...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.