473,226 Members | 1,625 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,226 software developers and data experts.

MYSQL Backup & Restore using PHP ?

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
Jul 16 '05 #1
3 15420
Zac..

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

Thanks

On Wed, 9 Jul 2003 13:03:15 -0600, "Zac Hester" <ne**@planetzac.net>
wrote:
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('mysqldump --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:3f****************@news.btclick.com...
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



Jul 16 '05 #2
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" <je**********@btinternet.com> wrote in message
news:ad**************************@posting.google.c om...
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" <ne**@planetzac.net> wrote in message

news:<3f********@news.enetis.net>...
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('mysqldump --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:3f****************@news.btclick.com...
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

Jul 16 '05 #3
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:
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


Jul 16 '05 #4

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

Similar topics

1
by: Jerry T | last post by:
How can I backup and restore a MySQL DB WITHOUT USING EXEC() MYSQL MYSQLDUMP ?? If I try running exec() and mysqldump, I get Fork warning errors ..
1
by: Elaine | last post by:
hi Hopefully this is simple enough. I'm running 3.23 and 4.1.7 mysql databases. I run a /usr/bin/mysqlhotcopy backup on a specific database name , and copy my.cnf and ib* files to the backup...
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...
4
by: wzhao6898 | last post by:
Hi there, I'm trying to figure out if it's going painless to upgrade our oooold mysql 3.23 to the latest MySQL 4 or 5. Any suggestions? Thanks in advance! David
7
by: phillip.s.powell | last post by:
We're looking at a GUI interface for our MySQL DB and I am interested in MySQL Administrator, however, one of our requirements is to be able to import/export databases. Is this possible or do I...
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...
3
by: deepstar | last post by:
Hello I have a rather large database (about 102 megabytes when dumped to an SQL file) and need advice on backup software for MySql databases. So far I've only tried Navicat and it takes about 30...
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...
15
by: Jerry Yang | last post by:
Hi I'm having issues with mysqldump so need to create backups of my mysql databases using PHP. Can any one recommend a way to do this with out calling mysqldump ? I did find one script and it...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.