nezbo Posted August 16, 2007 Share Posted August 16, 2007 Hi I am atempting to make a very very simple MySql Backup page. I have the following code and the backup.sql is blank I have tryed doing the backup in the shell and the mysqldump dosent send anything. where am i going wrong? or is there a script that works that any one has i can use <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $dbname = 'call_log'; $filename = "backup.sql"; passthru("mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname > $filename"); ?> Quote Link to comment Share on other sites More sharing options...
tibberous Posted August 16, 2007 Share Posted August 16, 2007 Yeah, use this. I didn't write it but it's what I tend to end up using. If your backing up passwords and shit I have the code to encrypt and decry the backups this makes too. <?php function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only) { //this function creates a text file (or output to a HTTP connection), that when parsed through MYSQL's telnet client, will re-create the entire database //Parameters: // $host: usually "localhost" but depends on where the MySQL database engine is mounted // $dbname : The MySQL database name // $uid : the database's username (not your account's), leave blank if none is required // $pwd : the database's password // $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank. // $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output. if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false; //connect to MySQL database $con=mysql_connect("localhost",$uid, $pwd); $db=mysql_select_db($dbname,$con); //open back-up file ( or no file for browser output) //set up database out($fptr, "create database $dbname;\n\nuse $dbname;\n\n"); //enumerate tables $res=mysql_list_tables($dbname); $nt=mysql_num_rows($res); for ($a=0;$a<$nt;$a++) { $row=mysql_fetch_row($res); $tablename=$row[0]; //start building the table creation query $sql="create table $tablename\n(\n"; $res2=mysql_query("select * from $tablename",$con); $nf=mysql_num_fields($res2); $nr=mysql_num_rows($res2); $fl=""; //parse the field info first for ($b=0;$b<$nf;$b++) { $fn=mysql_field_name($res2,$b); $ft=mysql_fieldtype($res2,$b); $fs=mysql_field_len($res2,$b); $ff=mysql_field_flags($res2,$b); $sql.=" $fn "; $is_numeric=false; switch(strtolower($ft)) { case "int": $sql.="int"; $is_numeric=true; break; case "blob": $sql.="text"; $is_numeric=false; break; case "real": $sql.="real"; $is_numeric=true; break; case "string": $sql.="char($fs)"; $is_numeric=false; break; case "unknown": switch(intval($fs)) { case 4: //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type $sql.="tinyint"; $is_numeric=true; break; default: //we could get a little more optimzation here! (i.e. check for medium ints, etc.) $sql.="int"; $is_numeric=true; break; } break; case "timestamp": $sql.="timestamp"; $is_numeric=true; break; case "date": $sql.="date"; $is_numeric=false; break; case "datetime": $sql.="datetime"; $is_numeric=false; break; case "time": $sql.="time"; $is_numeric=false; break; default: //future support for field types that are not recognized (hopefully this will work without need for future modification) $sql.=$ft; $is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown break; } //VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator if (strpos($ff,"unsigned")!=false) { //timestamps are a little screwy so we test for them if ($ft!="timestamp") $sql.=" unsigned"; } if (strpos($ff,"zerofill")!=false) { //timestamps are a little screwy so we test for them if ($ft!="timestamp") $sql.=" zerofill"; } if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment"; if (strpos($ff,"not_null")!=false) $sql.=" not null"; if (strpos($ff,"primary_key")!=false) $sql.=" primary key"; //End of field flags if ($b<$nf-1) { $sql.=",\n"; $fl.=$fn.", "; } else { $sql.="\n);\n\n"; $fl.=$fn; } //we need some of the info generated in this loop later in the algorythm...save what we need to arrays $fna[$b]=$fn; $ina[$b]=$is_numeric; } out($fptr,$sql); if ($structure_only!=true) { //parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself... for ($c=0;$c<$nr;$c++) { $sql="insert into $tablename ($fl) values ("; $row=mysql_fetch_row($res2); for ($d=0;$d<$nf;$d++) { $data=strval($row[$d]); if ($ina[$d]==true) $sql.= intval($data); else $sql.="\"".mysql_escape_string($data)."\""; if ($d<($nf-1)) $sql.=", "; } $sql.=");\n"; out($fptr,$sql); } out($fptr,"\n\n"); } mysql_free_result($res2); } if ($fptr!=false) fclose($fptr); return 0; } function out($fptr,$s) { if ($fptr==false) echo("$s"); else fputs($fptr,$s); } ?> Quote Link to comment Share on other sites More sharing options...
nezbo Posted August 16, 2007 Author Share Posted August 16, 2007 Cheers for this, but i am not to good with functions and i am a beginner. and i am not to sure what i need to do to this script to make it work? Neil Yeah, use this. I didn't write it but it's what I tend to end up using. If your backing up passwords and shit I have the code to encrypt and decry the backups this makes too. <?php function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only) { //this function creates a text file (or output to a HTTP connection), that when parsed through MYSQL's telnet client, will re-create the entire database //Parameters: // $host: usually "localhost" but depends on where the MySQL database engine is mounted // $dbname : The MySQL database name // $uid : the database's username (not your account's), leave blank if none is required // $pwd : the database's password // $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank. // $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output. if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false; //connect to MySQL database $con=mysql_connect("localhost",$uid, $pwd); $db=mysql_select_db($dbname,$con); //open back-up file ( or no file for browser output) //set up database out($fptr, "create database $dbname;\n\nuse $dbname;\n\n"); //enumerate tables $res=mysql_list_tables($dbname); $nt=mysql_num_rows($res); for ($a=0;$a<$nt;$a++) { $row=mysql_fetch_row($res); $tablename=$row[0]; //start building the table creation query $sql="create table $tablename\n(\n"; $res2=mysql_query("select * from $tablename",$con); $nf=mysql_num_fields($res2); $nr=mysql_num_rows($res2); $fl=""; //parse the field info first for ($b=0;$b<$nf;$b++) { $fn=mysql_field_name($res2,$b); $ft=mysql_fieldtype($res2,$b); $fs=mysql_field_len($res2,$b); $ff=mysql_field_flags($res2,$b); $sql.=" $fn "; $is_numeric=false; switch(strtolower($ft)) { case "int": $sql.="int"; $is_numeric=true; break; case "blob": $sql.="text"; $is_numeric=false; break; case "real": $sql.="real"; $is_numeric=true; break; case "string": $sql.="char($fs)"; $is_numeric=false; break; case "unknown": switch(intval($fs)) { case 4: //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type $sql.="tinyint"; $is_numeric=true; break; default: //we could get a little more optimzation here! (i.e. check for medium ints, etc.) $sql.="int"; $is_numeric=true; break; } break; case "timestamp": $sql.="timestamp"; $is_numeric=true; break; case "date": $sql.="date"; $is_numeric=false; break; case "datetime": $sql.="datetime"; $is_numeric=false; break; case "time": $sql.="time"; $is_numeric=false; break; default: //future support for field types that are not recognized (hopefully this will work without need for future modification) $sql.=$ft; $is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown break; } //VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator if (strpos($ff,"unsigned")!=false) { //timestamps are a little screwy so we test for them if ($ft!="timestamp") $sql.=" unsigned"; } if (strpos($ff,"zerofill")!=false) { //timestamps are a little screwy so we test for them if ($ft!="timestamp") $sql.=" zerofill"; } if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment"; if (strpos($ff,"not_null")!=false) $sql.=" not null"; if (strpos($ff,"primary_key")!=false) $sql.=" primary key"; //End of field flags if ($b<$nf-1) { $sql.=",\n"; $fl.=$fn.", "; } else { $sql.="\n);\n\n"; $fl.=$fn; } //we need some of the info generated in this loop later in the algorythm...save what we need to arrays $fna[$b]=$fn; $ina[$b]=$is_numeric; } out($fptr,$sql); if ($structure_only!=true) { //parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself... for ($c=0;$c<$nr;$c++) { $sql="insert into $tablename ($fl) values ("; $row=mysql_fetch_row($res2); for ($d=0;$d<$nf;$d++) { $data=strval($row[$d]); if ($ina[$d]==true) $sql.= intval($data); else $sql.="\"".mysql_escape_string($data)."\""; if ($d<($nf-1)) $sql.=", "; } $sql.=");\n"; out($fptr,$sql); } out($fptr,"\n\n"); } mysql_free_result($res2); } if ($fptr!=false) fclose($fptr); return 0; } function out($fptr,$s) { if ($fptr==false) echo("$s"); else fputs($fptr,$s); } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.