Tuesday 8 July 2014

How to get Mysql Database Backup Using PHP

If you want to get backup of all tables in your database or specific table in those database .
I think this script will help you for getting database backup.

Here is the example of Database Backup PHP Script :

<?php function backup_db() { $host="localhost"; $uname="database_username"; $pass="database_password"; $database = "databasename";  $return = ""; $connection=mysqli_connect($host,$uname,$pass,$database);  /* Store All Table name in an Array */ $allTables = array(); $result = mysqli_query($connection,'SHOW TABLES'); while($row = mysqli_fetch_row($result)){ $allTables[] = $row[0]; } foreach($allTables as $table){ $result = mysqli_query($connection,'SELECT * FROM '.$table); $num_fields = mysqli_num_fields($result); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysqli_fetch_row(mysqli_query($connection,'SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysqli_fetch_row($result)){   $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++){   $row[$j] = addslashes($row[$j]);   $row[$j] = str_replace("\n","\\n",$row[$j]);   if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; }    else { $return.= '""'; }   if ($j<($num_fields-1)) { $return.= ','; } }   $return.= ");\n"; } } $return.="\n\n"; } // Create Backup Folder $folder = 'e:/DB_Backup/'; // I am getting database backup on my E driver. if (!is_dir($folder)) mkdir($folder, 0777, true); chmod($folder, 0777); date_default_timezone_set('Asia/Kolkata'); $date = date('m-d-Y-H-i-s', time());  $filename = $folder."db-backup-".$date;  $handle = fopen($filename.'.sql','w+'); fwrite($handle,$return); fclose($handle); } // Call the function backup_db();?>