MYSQL Backup & Restore using PHP ?

[James]

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 shows all current backups, and restores the
selected one with one click...

Can this be done, ?

Can you point me in the right direction ?

Thanks

[James]

Re: MYSQL Backup & Restore using PHP ?

Zac..

Thanks, I think you've cover every solution !!

Thanks

On Wed, 9 Jul 2003 13:03:15 -0600, "Zac Hester" <news@planetzac .net>
wrote:
[color=blue]
>Hi James,
>
>This is a very simple task and should not require a tremendous amount of
>programming (I've done it with several web sites).
>
>How do you plan on backing up the database? I've used three methods
>depending on how you want to handle it:
>
>1. You can run a "mysqldump" from the command line (using passthru() or
>exec()) and redirect the output to a file:
>
> exec('mysqldump --add-drop-table -h hostname -u username -ppassword
>databasename > backup.sql');
>
>This would create a text file containing all of the database information
>necessary to recreate the entire database from scratch. Keeping multiple
>backups is just a matter of giving each filename a unique name (using the
>date, for instance). To restore your database from this backup you can run
>the mysql client like this:
>
> exec('mysql -h hostname -u username -ppassword databasename <
>backup.sql') ;
>
>The advantage is that this is really simple. The disadvantage is that the
>directory to which you are writing the backups has to be writable by the web
>server user (which is an inherent security risk).
>
>2. If you're not so worried about the database server crashing (why would
>you be if you're using MySQL), you can take the results of the mysqldump
>command and send it to a separate database:
>
> $backup = passthru('mysql dump --add-drop-table -h hostname -u
>username -ppassword databasename');
> $query = 'insert into backups (backuptime, data) values ('.mktime().',
>\''.addslashes ($backup).'\')' ;
>
>Where the table containing the backups looks like this:
>
> create table backups (
> id int(4) not null auto_increment,
> backuptime int(12) not null,
> data largetext,
> primary key(id)) type=MyISAM;
>
>To restore the data, you would want to send the queries back into the mysql
>client like this:
>
> exec('mysql -e \''.str_replace ("\n", " \\ \n", $backup).'\' -h
>hostname -u username -ppassword databasename');
>
>This has the advantage of being much more secure (it doesn't require any
>crazy write permessions) and is still pretty simple. However, it does rely
>on the database backing itself up. If you fear your DB server isn't very
>stable, this is not an acceptable solution. If you're backing up the DB
>just in case a user foobars it, this is a great solution. Just make sure
>the backup table is held in a different database, so you don't try to
>backup/restore all the backups.
>
>3. The last method I've used involves a lot more programming, but is nice
>if you want a lot of control over your backups. Since you probably already
>know your table structure in advance, you can just query the database from
>PHP (like normal) retrieving all of the information. Then, store the
>results of the individual queries in a backup database (like the previous
>example). Then, when you need to restore all or part of your database,
>delete the information from the affected tables and reinsert it using the
>information from your backup database. You can keep extensive catalogs of
>backups or just "snapshots" of the entire DB this way. This is also a nice
>way to backup your database onto a completely different computer (if you
>just send the backups to a different host for storage).
>
>HTH,
>Zac
>
>
>
><James @ nothere.com (James)> wrote in message
>news:3f0c0ee0. 704813538@news. btclick.com...[color=green]
>> 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 shows all current backups, and restores the
>> selected one with one click...
>>
>> Can this be done, ?
>>
>> Can you point me in the right direction ?
>>
>> Thanks[/color]
>
>[/color]

[Zac Hester]

Re: MYSQL Backup &amp; Restore using PHP ?

If you want to use this method, I would recommend two things:

1. Make this a directory where you don't want to keep any "live" web
content.
2. Restrict access to this directory so the web server can not serve
anything from it. The easy way is to use an .htaccess file like this:

Deny from all

Just drop this file into the directory where you will be keeping your
backups and the web server won't be able to serve anything requested from
the directory which eleminates the possibility of someone writing/modifying
a file here and then requesting it.

Better yet, just write all of your backups somewhere outside of the web
server's scope (something "above" htdocs).

Finally, to answer your question, the most secure and usable permissions for
this directory are 646. I'm assuming you don't need to run any programs or
CGI from this directory (PHP doesn't count as CGI unless you are using the
CGI interpreter). This makes the directory readable by user, group, and
world and writable by user and world. Don't say I didn't warn you.

HTH,
Zac



"Jez" <jez.hailwood@b tinternet.com> wrote in message
news:ad15f8ee.0 307100716.379e0 00e@posting.goo gle.com...[color=blue]
> I've created a very similar script to number 1 below. Can you tell me
> how I should chmod the directory in which the sql file is dumped? I
> currently use 777, but is there something safer?
>
> Jez
>
>
> "Zac Hester" <news@planetzac .net> wrote in message[/color]
news:<3f0c6714$ 1@news.enetis.n et>...[color=blue][color=green]
> > Hi James,
> >
> > This is a very simple task and should not require a tremendous amount of
> > programming (I've done it with several web sites).
> >
> > How do you plan on backing up the database? I've used three methods
> > depending on how you want to handle it:
> >
> > 1. You can run a "mysqldump" from the command line (using passthru() or
> > exec()) and redirect the output to a file:
> >
> > exec('mysqldump --add-drop-table -h hostname -u username -ppassword
> > databasename > backup.sql');
> >
> > This would create a text file containing all of the database information
> > necessary to recreate the entire database from scratch. Keeping[/color][/color]
multiple[color=blue][color=green]
> > backups is just a matter of giving each filename a unique name (using[/color][/color]
the[color=blue][color=green]
> > date, for instance). To restore your database from this backup you can[/color][/color]
run[color=blue][color=green]
> > the mysql client like this:
> >
> > exec('mysql -h hostname -u username -ppassword databasename <
> > backup.sql');
> >
> > The advantage is that this is really simple. The disadvantage is that[/color][/color]
the[color=blue][color=green]
> > directory to which you are writing the backups has to be writable by the[/color][/color]
web[color=blue][color=green]
> > server user (which is an inherent security risk).
> >
> > 2. If you're not so worried about the database server crashing (why[/color][/color]
would[color=blue][color=green]
> > you be if you're using MySQL), you can take the results of the mysqldump
> > command and send it to a separate database:
> >
> > $backup = passthru('mysql dump --add-drop-table -h hostname -u
> > username -ppassword databasename');
> > $query = 'insert into backups (backuptime, data) values[/color][/color]
('.mktime().',[color=blue][color=green]
> > \''.addslashes( $backup).'\')';
> >
> > Where the table containing the backups looks like this:
> >
> > create table backups (
> > id int(4) not null auto_increment,
> > backuptime int(12) not null,
> > data largetext,
> > primary key(id)) type=MyISAM;
> >
> > To restore the data, you would want to send the queries back into the[/color][/color]
mysql[color=blue][color=green]
> > client like this:
> >
> > exec('mysql -e \''.str_replace ("\n", " \\ \n", $backup).'\' -h
> > hostname -u username -ppassword databasename');
> >
> > This has the advantage of being much more secure (it doesn't require any
> > crazy write permessions) and is still pretty simple. However, it does[/color][/color]
rely[color=blue][color=green]
> > on the database backing itself up. If you fear your DB server isn't[/color][/color]
very[color=blue][color=green]
> > stable, this is not an acceptable solution. If you're backing up the DB
> > just in case a user foobars it, this is a great solution. Just make[/color][/color]
sure[color=blue][color=green]
> > the backup table is held in a different database, so you don't try to
> > backup/restore all the backups.
> >
> > 3. The last method I've used involves a lot more programming, but is[/color][/color]
nice[color=blue][color=green]
> > if you want a lot of control over your backups. Since you probably[/color][/color]
already[color=blue][color=green]
> > know your table structure in advance, you can just query the database[/color][/color]
from[color=blue][color=green]
> > PHP (like normal) retrieving all of the information. Then, store the
> > results of the individual queries in a backup database (like the[/color][/color]
previous[color=blue][color=green]
> > example). Then, when you need to restore all or part of your database,
> > delete the information from the affected tables and reinsert it using[/color][/color]
the[color=blue][color=green]
> > information from your backup database. You can keep extensive catalogs[/color][/color]
of[color=blue][color=green]
> > backups or just "snapshots" of the entire DB this way. This is also a[/color][/color]
nice[color=blue][color=green]
> > way to backup your database onto a completely different computer (if you
> > just send the backups to a different host for storage).
> >
> > HTH,
> > Zac
> >
> >
> >
> > <James @ nothere.com (James)> wrote in message
> > news:3f0c0ee0.7 04813538@news.b tclick.com...[color=darkred]
> > > 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 shows all current backups, and restores the
> > > selected one with one click...
> > >
> > > Can this be done, ?
> > >
> > > Can you point me in the right direction ?
> > >
> > > Thanks[/color][/color][/color]

[Frank]

Re: MYSQL Backup &amp; Restore using PHP ?

Isn't it just a matter of copyomg the file from one directory to
another ??

Or copy from server to Floppy ?



On Wed, 9 Jul 2003 12:49:08 +0000 (UTC), James @ nothere.com (James)
wrote:
[color=blue]
>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 shows all current backups, and restores the
>selected one with one click...
>
>Can this be done, ?
>
>Can you point me in the right direction ?
>
>Thanks[/color]