472,986 Members | 2,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,986 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 1767
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: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.