MySQL database backup

[Dave Moore]

Hi All,
I'm looking at using PHP and MySQL for a particular website application.
However, I was a bit concerned that if a PHP script went wrong then it would
be possible to accidentally delete or overwrite large chunks of the data in
the database which presumably would then be lost. What is the usual
procedure for these situations?. Is it possible to back up a database
somehow so that data from a previous time can be retrieved?.

Ta,
Dave

[Jacob Atzen]

On 2005-04-13, Dave Moore <da**********@b aesystems.com> wrote:

I'm looking at using PHP and MySQL for a particular website application.
However, I was a bit concerned that if a PHP script went wrong then it would
be possible to accidentally delete or overwrite large chunks of the data in
the database which presumably would then be lost. What is the usual
procedure for these situations?. Is it possible to back up a database
somehow so that data from a previous time can be retrieved?.

Of course. Read up on mysqldump.

--
Cheers
- Jacob Atzen

[Gordon Burditt]

>Hi All,

I'm looking at using PHP and MySQL for a particular website application.
However, I was a bit concerned that if a PHP script went wrong then it would
be possible to accidentally delete or overwrite large chunks of the data in
the database which presumably would then be lost. What is the usual
procedure for these situations?. Is it possible to back up a database
somehow so that data from a previous time can be retrieved?.

mysqldump.

If you can access your MySQL database directly from a host you
control (e.g. your desktop), dump it to a file on your desktop.
You do need to install the MySQL client on that machine.
This option may not be available as hosts don't allow it or
you don't want your db accessable remotely.

If you can access your MySQL database from a shell account on
the hosting server, dump it to a file on the hosting server,
then FTP it somewhere else safe (if the hosting server loses
a hard drive, it might lose the master copy AND your backup
if you don't keep a copy elsewhere).

If you have to use PHP to access MySQL, invoke mysqldump from PHP
(exec()) and redirect the output to a file on the hosting server,
then FTP it somewhere else safe.

You restore stuff generated by mysqldump (which is a series of SQL
commands) by feeding the dump into the command line client called 'mysql',
e.g. for UNIX shells:
mysqldump -h host -u user -ppassword --databases db1 db2 ... > mysql.dump

mysql -h host -u user -ppassword db1 < mysql.dump

mysqldump has lots of options. Depending on your hosting situation,
you may want to back up all databases or just the one your host
assigns to you. Or maybe you want just specific tables. You can
back up just the schema (no data) or all of it. Some of the options
(like host name) may default properly in the right circumstances.

Gordon L. Burditt

[Pingveno]

Dave Moore wrote:

Hi All,
I'm looking at using PHP and MySQL for a particular website application.
However, I was a bit concerned that if a PHP script went wrong then it would
be possible to accidentally delete or overwrite large chunks of the data in
the database which presumably would then be lost. What is the usual
procedure for these situations?. Is it possible to back up a database
somehow so that data from a previous time can be retrieved?.

Ta,
Dave

I've been wondering about a closely related, or perhaps identical,
problem. How can I make a system of nightly backups where I can revert
to any previous nightly backup without gobbling up hard drive space
storing a file for each backup. Something like Subversion or CVS, except
for being more suitable for simple backups. Any products or methods out
there?

Pingveno

[Colin McKinnon]

Dave Moore wrote:

Hi All,
I'm looking at using PHP and MySQL for a particular website
application.
However, I was a bit concerned that if a PHP script went wrong then it
would be possible to accidentally delete or overwrite large chunks of the
data in the database which presumably would then be lost. What is the
usual procedure for these situations?. Is it possible to back up a
database somehow so that data from a previous time can be retrieved?.

Not a PHP question, not even a MySQL question.

Some people use stored procedures (mysql now supports stored procedures) to
try to isolate the DB from the application but at the end of the day it
doesn't really solve the problem - the only way PHP will be asing the
database to delete lots of stuff is because a combination of your PHP code
and the users request made it so. The real answer is to implement a proper
software engineering model (like CMMS) and write better code.

.....oh and don't forget to do backups.

C.