7 Replies - 1479 Views - Last Post: 14 May 2010 - 12:54 AM

#1 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Backup MySQL Database

Posted 11 September 2009 - 10:31 PM

hi guys :D

i am working in a website for my client and they want a backup facility in there website, so i created a function to backup mysql database it works fine, but need to know if every thing is okay or not because as i checked so it is working fine,, please check it and inform me...

PUT YOUR COMMENTS IN BOTH CASES IF YOU FIND ANY MISTAKE OR IF IT WORKED FINE......

<?PHP
function backup($db){
		header("Content-disposition: filename=$db.sql");
		header("Content-type: application/octetstream");
		header("Pragma: no-cache");
		header("Expires: 0");
	
	$con = mysqli_connect("localhost","root","",$db)or die(mysqli_error($con));
	$tables = mysqli_query($con,"SHOW TABLES")or die(mysqli_error());
	$tbls = array();
	
	while($table = mysqli_fetch_assoc($tables)){
		$tbls[] .= $table["Tables_in_$db"];
	}
	echo 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";'."\n\n";
	echo "CREATE DATABASE IF NOT EXISTS $db;\n\n";
	echo "USE $db;\n";
	foreach($tbls as $tbl){
		
		$str_cols = "SHOW COLUMNS FROM `$db`.`$tbl`";
		$str_query = "SELECT * FROM `$db`.`$tbl`";
		$str_fields = "SHOW FIELDS FROM `$db`.`$tbl`";
		
		$qry_cols = mysqli_query($con,$str_cols)or die(mysqli_error($con));
		$qry_query = mysqli_query($con,$str_query)or die(mysqli_error($con));
		$qry_fields = mysqli_fetch_fields($qry_query)or die(mysqli_error($con));
		
		//show fields query
		$fields_qry = mysqli_query($con,$str_fields)or die(mysqli_error($con));
		$fields_num = mysqli_num_rows($fields_qry)or die(mysqli_error($con));
		$nums = 0;
		$str_create = "";
		echo "\n\n";
		$str_create .= "CREATE TABLE IF NOT EXISTS `$db`.`$tbl` (\r\n";	
		while($fields_fetch = mysqli_fetch_array($fields_qry)){	
			$str_create .="`$fields_fetch[Field]` $fields_fetch[Type] ";
			if($fields_fetch['Null']=="NO"){$str_create .="Not Null ";}else{$str_create .= "Null ";}
			if($fields_fetch['Extra'] !=""){$str_create .= " PRIMARY KEY";}
			if($nums < $fields_num-1){
				$str_create .=",";
			}
			$str_create .="\r\n";
			$nums++;	
		}
		$str_create .=")";	
		echo $str_create;
		
			$query_str = "SELECT * FROM `$db`.`$tbl`";
			$query = mysqli_query($con,$query_str)or die(mysqli_error($con));
			$auto = mysqli_num_rows($query);
			$fields = array();
			
		echo "ENGINE=MYISAM AUTO_INCREMENT=$auto;\n\n";
	
			$cols = mysqli_query($con,"show columns from `$db`.`$tbl`")or die(mysqli_error($con));
			$cols_array = array();
			$cols_num  = mysqli_num_rows($cols);
			
		
			while($r = mysqli_fetch_field($query)){
				$fields[] .=$r->name;
			}
		
			while($fetch_cols = mysqli_fetch_assoc($cols)){	
				$cols_array[] .=$fetch_cols['Field'];
			}
			
			$query = mysqli_query($con,$query_str)or die(mysqli_error($con));
			while($query_fetch = mysqli_fetch_assoc($query)){
			echo "INSERT INTO `$db`.`$tbl` SET ";
				for($i=0;$i<(count($cols_array));$i++){
					if(isset($query_fetch[$cols_array[$i]])){
					//select attribute, check for key if yes then don't select this;
						echo "`$cols_array[$i]`='".mysqli_real_escape_string($con,$query_fetch[$cols_array[$i]])."'";
						if($i<(count($cols_array)-1)){
							echo ",";
						}
					}
				}
			echo "; \r\n";
			}
	}
}

backup('mydb');
?>


Is This A Good Question/Topic? 1
  • +

Replies To: Backup MySQL Database

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Backup MySQL Database

Posted 12 September 2009 - 12:35 PM

I don't see any obvious problems with the code. It produces the SQL file fine and it looks OK to me. I haven't actually tried importing the dump this produces, though.

However, it seems to me like you're doing it the hard way. Why not just shell out to mysqldump and let it do all the work? After all, you're running the script on the same server as the database, so it's a pretty safe bet the MySQL utilities are installed. You could have saved yourself about 80 lines of code and you wouldn't have to worry about the correctness of the backup.
Was This Post Helpful? 1
  • +
  • -

#3 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Backup MySQL Database

Posted 12 September 2009 - 09:17 PM

Thanks AdaHacker :^: i know about mysqldump but i was trying to create my own function for expreience :)

now can anyone please try to import the exported data with this code :)
Was This Post Helpful? 0
  • +
  • -

#4 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Backup MySQL Database

Posted 13 September 2009 - 04:27 AM

Nice job :)

I'm not sure if it makes a difference when reading data, but I would consider using
SET NAMES "utf8"
to make it more universal if that's the case. By default the MySQL Client Library's compiled as latin1 so some characters could get screwed up. Again, I'm not sure if that only refers to putting stuff INTO the database or not.
Was This Post Helpful? 1
  • +
  • -

#5 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Backup MySQL Database

Posted 13 September 2009 - 09:44 PM

thanks for checking MageUK :)

Quote

SET NAMES "utf8"


you mean when i am creating the database?
echo "CREATE DATABASE IF NOT EXISTS `$db` CHARACTER SET NAMES 'utf8';\n\n";
Was This Post Helpful? 0
  • +
  • -

#6 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Backup MySQL Database

Posted 14 September 2009 - 10:37 AM

No.

Execute that before you do any database creation etc, it should be the very first thing that you do, in both the output and in your code (an actual query).
Was This Post Helpful? 0
  • +
  • -

#7 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Backup MySQL Database

Posted 14 September 2009 - 10:42 PM

OK thanks MageUK :)
Was This Post Helpful? 0
  • +
  • -

#8 saturnx  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 30
  • Joined: 07-May 09

Re: Backup MySQL Database

Posted 14 May 2010 - 12:54 AM

I was wondering, how would you guys go about backing up a huge database?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1