prowebinteractiveinc
asked on
backup mysql db like webAdmin
I would like to create a php function to export the structure and data from my mySQL db and save it as file.sql.
I will then add this function to my list of scheduled tasks
I am using the following to connect to the db:
Thanks
I will then add this function to my list of scheduled tasks
I am using the following to connect to the db:
function dbConnect()
{
$mysql_local = "";
$mysql_db = "";
$db_user = "";
$db_user_pass = "";
if(!isset($oConn))
{
$oConn = @mysql_pconnect($mysql_local,$db_user,$db_user_pass) or die ("Could not connect to local server");
mysql_select_db($mysql_db) or die ("Unable to select database");
}
}
function dbDisconnect()
{
if(isset($oConn))
{
mysql_close ($oConn);
}
}
Thanks
ASKER
Im getting this error..
There was a warning during the export of dbName to ~/chooseFilenameForBackup. sql
Im looking for it to do exactly what webAdmin does, accept, it writes the contents to a file in a folder on the server.
There was a warning during the export of dbName to ~/chooseFilenameForBackup.
Im looking for it to do exactly what webAdmin does, accept, it writes the contents to a file in a folder on the server.
You're in luck! http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
ASKER
it looks complicated !
It's not complicated when compared to rebuilding a data base that wasn't backed up!
ASKER
Hi Ray. I found the code below on the page of the link you sent me. I see that it connects through root, and takes a copy of all the databases, what if I want just one particular database and dont have root access, but just need my db anyways and to save it to a folder with in the root of the website im working on. can I not just connect to it like I connect to it now to do the mysqldump ? what can I modify in the code below to do exactly what I need ?
Thanks
Thanks
$username = "root";
$password = "password";
$backup_dir = "/mnt/backup";
$dump = "/usr/bin/mysqldump";
$grep = "/bin/grep";
$gzip = "/bin/gzip";
// This should not need changing from here
function sql_dict($sql){
$x = mysql_query($sql);
if ($x) return mysql_fetch_assoc($x);
}
function cleanup($dir){
$d = dir($dir);
echo "Path: " . $d->path . "\n";
while (false !== ($entry = $d->read())) {
if ($entry=="." or $entry=="..") continue;
$e = str_replace(".sql.gz","",$entry);
$x = sql_dict("describe $e");
if (!$x) {
print "Removing old backup file [$entry]\n";
unlink("$dir/$entry");
}
}
$d->close();
}
function crc32_file($filename)
{
global $gzip;
$x = exec("$gzip --list --verbose $filename");
$x = explode(" ",$x);
return $x[1];
}
if (mysql_connect("localhost",$username,$password)) print "Connected.\n";
else die("Failed to connect to database.");
$dbs = mysql_query("show databases");
if ($dbs) while ($db = mysql_fetch_array($dbs, MYSQL_ASSOC)) {
$db = $db['Database'];
if ($db=="information_schema") continue;
if (mysql_select_db($db)) print "Selected [$db]\n";
else die("Failed to select db [$db]");
foreach (array("schema","data") as $pass){
$sql = mysql_query("show tables");
$day = date("l");
if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
else $dir = "/$backup_dir/$db/$day";
if (!file_exists($dir)) system("mkdir -p $dir");
if (!file_exists($dir)) die("Couldn't Create $dir");
if ($pass=="data"){
$latest = "/$backup_dir/$db/latest";
unlink($latest);
system("/bin/ln -s \"$dir\" \"$latest\"");
}
cleanup($dir);
if ($sql) while ($s = mysql_fetch_assoc($sql)) {
if (!isset($s["Tables_in_{$db}"])) {
print "no result";
print_r($sql);
die();
}
$t = $s["Tables_in_{$db}"];
if (
$pass=="schema" ) $data = "--no-data";
else $data = "--lock-tables";
$tab = $t;
$lim = 30;
if (strlen($tab)>$lim) $tab = substr($tab,0,$lim-3)."...";
while (strlen($tab)<30) $tab .= " ";
print "BACKUP: $pass : $day : $db : $tab : ";
if ($pass=="data"){
print "Check : ";
$check = sql_dict("check table $t");
$check = $check['Msg_text'];
print "$check : ";
if ($check != "OK") {
print "Repair";
$repair = sql_dict("repair table $t");
$repair = $repair['Msg_text'];
print " : $repair : ";
}
if ($day=="Sunday"){
// optimize
print "Optimize : ";
$type = sql_dict("show table status like '$t'");
$type = $type['Engine'];
if ($type=="MyISAM") sql("optimize table $t");
if ($type=="InnoDB") sql("alter table $t engine='InnoDB'");
}
}
if (isset($argv[1])){
print "Skipping dump\n";
} else {
$temp = "/tmp/backup.$t.sql.gz";
$out = "$dir/$t.sql.gz";
print "Dump : ";
$cmd = "$dump -u$username -p$password $data --quick --add-drop-table $db $t | $grep -v 'Dump completed' | $gzip -n > $temp";
system($cmd);
print "CRC32 : ";
if (!file_exists($out)){
print "Saving : ";
$cmd = "/bin/mv $temp $out";
system($cmd);
} else {
$md5 = crc32_file($temp);
$nmd5 = crc32_file($out);
if ($md5!=$nmd5) {
print "Saving : ";
$cmd = "/bin/mv $temp $out";
system($cmd);
} else {
print "Skipped : ";
unlink($temp);
}
}
$size = filesize($out);
print "[$size]\n";
}
}
}
}
?>
I don't know. You would have to go through the code line-by-line. I would probably look for the part where it finds all of the data bases, and then right after that part I would override the list of data bases with the name of the data base that I want.
ASKER
ok, well I have no permissions to the file now. Im going to to use chown but how can I dynamically get my username if I use exec('whoami'); it gives me apache
Maybe a different approach. Do you have phpMyAdmin installed?
ASKER
yes I have it installed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK. in php my admin when you go to export you can choose to export structure and/or data
then you choose the compression. if no compression is chosen, then it shows the export in a large textarea - this is what im looking for in the end. Im want to save all the tables and data in an uncompresed file. im also having issues with permissions now. as you can see on line 3 the directories are being made. how do I chown and / or chmod for all the parent directories
then you choose the compression. if no compression is chosen, then it shows the export in a large textarea - this is what im looking for in the end. Im want to save all the tables and data in an uncompresed file. im also having issues with permissions now. as you can see on line 3 the directories are being made. how do I chown and / or chmod for all the parent directories
if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
else $dir = "/$backup_dir/$db/$day";
if (!file_exists($dir)) system("mkdir -p $dir");
if(!chmod($dir, 0777)){ print "Permissions change error."; };
Thanks
ASKER
Ray_Paseur for future reference. the follwing line of code is all that is needed to do what I needed.
Thanks for your assistance.
exec("mysqldump --user=" . $db_user . " --password=" . $db_user_pass . " --host=" . $mysql_host . " " . $mysql_db . " > " . $dir);
Thanks for your assistance.
http://faq.1and1.com/web_space__access/mysql/13.html