Take MySQL Backup using PHP

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);
}
Default image
Vignesh A Sathiyanantham
AWS Certified developer with 8 years of extensive experience working for Cloud and Datacenter solutions and leading a small team in a lean startup based on Bangalore, India
Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.