May 1, 2013 asvignesh 0Comment

One of the most important task of developer or administrators is to take backup periodically, Most of the hosting provider and cloud providers charge more for taking backup periodically , SaaS application developers or CMS developers can let the user to take the backup of the data using the interface.

Here is the easy way to take the backup dump of the MySQL

take_dump('DB_HOST','UNAME','PASS','DB');
/* backup the db OR just a table */
function take_dump($host,$user,$pass,$name,$tables = '*')
{

 $link = mysql_connect($host,$user,$pass);
 mysql_select_db($name,$link);

 //get all of the tables
 if($tables == '*')
 {
 $tables = array();
 $result = mysql_query('SHOW TABLES');
 while($row = mysql_fetch_row($result))
 {
 $tables[] = $row[0];
 }
 }
 else
 {
 $tables = is_array($tables) ? $tables : explode(',',$tables);
 }

 //process all tables
 foreach($tables as $table)
 {
 $result = mysql_query('SELECT * FROM '.$table);
 $num_fields = mysql_num_fields($result);

 $return.= 'DROP TABLE '.$table.';';
 $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
 $return.= "nn".$row2[1].";nn";

 for ($i = 0; $i < $num_fields; $i++)
 {
 while($row = mysql_fetch_row($result))
 {
 $return.= 'INSERT INTO '.$table.' VALUES(';
 for($j=0; $j<$num_fields; $j++)
 {
 $row[$j] = addslashes($row[$j]);
 $row[$j] = ereg_replace("n","n",$row[$j]);
 if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
 if ($j<($num_fields-1)) { $return.= ','; }
 }
 $return.= ");n";
 }
 }
 $return.="nnn";
 }

 //save file
 $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
 fwrite($handle,$return);
 fclose($handle);
}

Software developer experience in building storage management and Data protection software for the Private and public cloud.

Disclaimer :

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. my thoughts and opinions change from time to time… This weblog is intended to provide a semi-permanent point in time snapshot and manifestation of the various memes running around my brain, and as such any thoughts and opinions expressed within out-of-date posts may not the same, nor even similar, to those I may hold today.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *