Skip to main content

Backup MySQL Database With PHP

Database is the backbone of every dynamic websites. That's why webmaster needs to often backup there website's database. There are many ways to backup MySQL database like backup form phpmyadmin, using shell commend and also many other ways. Today I am going to show you my way to backup MySQL database. I have coded a PHP class to take MySQL database backup. This class can backup all or specified tables on a database.




The DBBackup Class:
class DBBackup
{
  private $con;
  private $tables = array();
  private $path;

  function __construct($host = null, $username = null, $password = null, $database = null)
  {
    /*
    Connect and select MySQL DB
    */
    if( is_null($host) || is_null($username) || is_null($password) || is_null($database) ) throw new Exception("The host, username, password and database name arguments must be specified!");
    if( ! $this->con = @mysql_connect($host, $username, $password) ) throw new Exception("Could not connect MySQL server {$username}@{$host}");
    if( ! @mysql_select_db($database, $this->con) )throw new Exception("Could not select database: {$database}");
  }


  public function table( $table = 'all' )
  {
    /*
    Get defined tables
    */
  	if( is_array( $table )){
      // Cheque table validity
      foreach($table as $t){
        if(in_array($t , $this->getAllTables())){
          $this->tables[] = $t;
        }
        else{
          throw new Exception("Table `{$t}` not exists in the DB");
        }
      }
  	}
  	elseif( $table == 'all' ){
  		$this->tables = $this->getAllTables();
  	}
  	else{
  		throw new Exception("Please enter tables name as array");
  	}
  }

  public function path($path = '')
  { 
    /*
    Set upload path
    */
    $this->path = $path;
  }

  public function backUp()
  {
    /*
    Done backup
    */
  	$return = '';
  	foreach($this->tables as $table){
  		$return .= $this->tableStracture($table);

  		foreach ($this->recode($table) as $result) {
  			$return .= $result;
  		}
  	}
  	$this->writeToFile($return);
    print_r($return);
    return true;
  }

  private function getAllTables()
  {
    /*
    Get tables list name from DB
    */
  	$sql = mysql_query("SHOW TABLES");
  	while ($row = mysql_fetch_row($sql) )
    {
      foreach ($row as $key => $value)
      {
        $table[]  = $value;
      }
  	}
	return $table;
  }

  private function tableStracture($table)
  {
    /*
    Get table stracture
    */
  	$return = "\nDROP TABLE IF EXISTS `{$table}`;\n\n";
    $row = ( mysql_fetch_row(mysql_query("SHOW CREATE TABLE {$table}")) );
    $return .= $row[1].";\n\n";
    return $return;
  }

  private function recode($table)
  {
    /*
    Get data recodes
    */
  	$query = mysql_query("SELECT * FROM {$table} LIMIT 0, 1000");
  	$num_fields = mysql_num_fields($query);
  	$num_rows = mysql_num_rows($query);
  	$results = array();
  	if ($num_rows){
  		
  		while($row = mysql_fetch_row($query))
  		{
  			$return = "INSERT INTO {$table} VALUES(";
  			for($i=0; $i<$num_fields; $i++) 
  			{
          $row[$i] = addslashes($row[$i]);
          $row[$i] = str_replace("\n","\\n",$row[$i]);
          $row[$i] = str_replace("\r","\\r",$row[$i]);

  				$return .= ( isset($row[$i]) ) ? "'{$row[$i]}'" : "''";
  				if ($i<($num_fields-1)) { $return.= ','; }
  			}
  			$return.= ");\n";
  			$results[] = $return;
  		}

  	}
  	return $results;
  }

  private function writeToFile($str)
  {
    /*
    Write down backup file
    */
    $path = (isset($this->path))? $this->path : '';
    $backupPath = $path . date('Y-m-d-H-i').'_'.md5(uniqid()).'.sql';
  	if( ! $handle = @fopen( $backupPath ,'w+') ) throw new Exception("Could not save backup file at {$backupPath}");  
    fwrite($handle, $str);
    fclose($handle);
  }

  public function close()
  {
    /*
    Close MySQL connection
    */
    mysql_close($this->con);
  }
}


Configuration:
define('HOST',		'localhost');		// Host name
define('USERNAME',	'root');		// User name
define('PASSWORD',	'root');		// Password
define('DATABASE',	'mydb');		// DB Name
define('PATH',		'backup/');		// Backup Directory


General usage:
try {
	$db = new DBBackup(HOST, USERNAME, PASSWORD, DATABASE);
	$db->path(PATH);
	$db->backUp();
	$db->close();
	echo "Done!";
} catch (Exception $e) {
	die($e->getMessage());
}

Backup Specified tables:
try {
	$db = new DBBackup(HOST, USERNAME, PASSWORD, DATABASE);
	$db->table(array('table1', 'table2', 'table3'));
	$db->path(PATH);
	$db->backUp();
	$db->close();
	echo "Done!";
} catch (Exception $e) {
	die($e->getMessage());
}

Comments

  1. Thank you ist super!!!! Reno

    ReplyDelete

Post a Comment

Popular posts from this blog

Multiple File Upload with PHP

Sometimes we need to allow our users to upload multiple file upload. On my previous post I was shown the basic of simple file upload with PHP. Today I am going to show you how to allow users to upload multiple files. It is almost similar like simple file upload but we need to do some modification with html markup and php code. multiple attribute is needed to add on the html markup. Major web browsers like Firefox, Chrome, Safari and IE 9+ support this attribute. Now I am going to show you how to upload multiple file with PHP and basic HTML form.

Live Comment System with jQuery Ajax PHP and MySQL

Recently I have been made a post that focused on how to submit form without page refreshing. That post shows the basic functionality of using jQuery Ajax. On the same follow this post will describe how we can create a comment system to post an instant comment without page refreshing. Let's start...

Resize Image While Uploading with PHP

On my previous post I was shown an example how to upload file with php and html. It was based on php simple image upload functionality. Today I am going to show you little bit advance of image uploading. Suppose if you run a website and want to allow users to upload image files than you need to think little bit about server's space and bandwidth. For this purpose you need to do some optimization with image files. Image resize while uploading is one of them. All major website do it in the same way. If a user uploads a 5mb image file they resize it in different sizes and store on there server. It helps them to seed up there website and reduce there budget. Now take a look, how we can do it ourself.