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"); ?> Link to comment https://forums.phpfreaks.com/topic/65202-php-and-mysql-backup-script/ 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); } ?> Link to comment https://forums.phpfreaks.com/topic/65202-php-and-mysql-backup-script/#findComment-325580 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); } ?> Link to comment https://forums.phpfreaks.com/topic/65202-php-and-mysql-backup-script/#findComment-325588 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.