FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

Database Backup

   Frihost Forum Index -> Scripting -> Php and MySQL
 


DanielXP
I want to make a script (for my news script) that will let the person make a back up of there news database

i want it to save it as [databasename][date].gz in backups/

(with out the [ ]'s

and as well i need the code so it will display there backup files in the backups/ in a drop down box and they can restore the database that way


EDIT: there are 4 databases

news
comments
users
bans

is there any way to put them all in the 1 .gz file?

and restore them all?
hexkid
Just a start:

Use the gzopen() function to create the compressed file.
Use the backtick operator to run mysqldump with the options you prefer on each/all your databases.

For example:
Code:
<?php
$databases = array('news', 'comments', 'users', 'bans');
foreach ($databases as $db) {
  $gzname = $dbname . date('Ymd') . '.gz';
  $gzfile = gzopen($gzname, 'wb');
  $gzdata = `mysqldump --databases $dbname`; ### backticks!!
  gzwrite($gzfile, $gzdata);
  gzclose($gzfile);
}
?>


To restore a database use could use "zcat GZFILE | mysql DATABASE" or something similar to the above but with gzread() to read the data in PHP and then execute that data with SQL commands.
DanielXP
where does that save it into the backups/

and i don't know the code to restore
hexkid
DanielXP wrote:
where does that save it into the backups/

Oops, I forgot about that one Smile
Try
Code:
  $gzname = 'backups/' . $dbname . date('Ymd') . '.gz';


Quote:
and i don't know the code to restore

Code:
<?php
// ...
$restore = $_POST['db2restore'];
### validate $restore very thoroughly
if (!valid_db_input($restore)) {
  exit('Wrong input. Go away!');
}
$dbrestore = substr($restore, 0, -8); // remove date
$cmd = 'zcat backups/' . $restore . '.gz | mysql $dbrestore';
### exec() is bad! Have you validated $restore very thoroughly?
exec($cmd, $output, $retval);
// ...
?>
DanielXP
the backup work great but the restore don't look to good Confused
hexkid
Maybe the webserver does not have permission to create databases? Try:
Code:
<?php
$restore = $_POST['db2restore'];
$dbrestore = substr($restore, 0, -8); // remove date
$root_pwd = 'DONTeverNEVERdoSOMETHINGlikeTHIS';
$cmd = 'zcat backups/' . $restore . '.gz | mysql -uroot -p$root_pwd $dbrestore';
exec($cmd, $output, $retval);
### check the $output and $retval variables!
?>
DanielXP
no i meant like theres no list box and that to display the backups the have in the backups/ folder
hexkid
DanielXP wrote:
no i meant like theres no list box and that to display the backups the have in the backups/ folder


Code:
<?php
// ...
echo '<select name="db2restore">';
$files = glob('backups/*.gz');
foreach ($files as $fn) {
  echo '<option>', substr($fn, 8);
}
echo '</select>';
// ...
?>


You may want to use opendir() and readdir() instead of glob()
DanielXP
Whats the $root_pwd for?
Philip
sorry, but i have a question also. Razz
is there a way to backup a databases with password using panel ?

and if i want to restore some table from csv files, ussually on local i typed
load data infile into table_name ....

if i want to upload those csv and run phpmyadmin from internet, what should i do ?

sorry Very Happy, thanks...
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.