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 Progress Bar using PHP & jQuery

Some days age I have created a post that deals with How we can upload file with progress bar using php and jQuery. Some of my readers asked me how we can upload multiple files with progress bar. In this post I am going to show you how we can upload multiple files with progress bar.We can upload multiple files with progress bar exactly same way as I shown previous. But we need to do some modification with our file upload form and our php script to handle multiple files.

Force File Download with PHP

If you want to control or track your downloadable contents then a force file downloader script can give you more option. Especially if you want make images or other stemming contents like music file (mp3, wav, ogg), video files (flv, mp4, ogg, 3gpp), css, JavaScripts or even a php file downloadable then you must need to use a force file downloader script.
In this post I am going to share a simple but useful force file downloader script.

File Upload with Progress Bar using jQuery and PHP

In this post you will learn how to create a AJAX file uploading system with progress bar using jQuery and PHP. For this purpose we will going to use jQuery Form Plugin. It is a easy to use and powerful jQuery AJAX form submitting plugin. It supports XMLHttpRequest Level 2 and iframe file transportation. That's why it will work for both old and new browsers.