Backup MySQL Database as SQL File with PHP

Backup MySQL Database as SQL File can be done with PHP Programming. It wasn't new thing for the expert. Backup MySQL Database as SQL File can be an alternative programming if something wrong (with server acces Telnet access, or PHPMyadmin). so the better thing we prepare is a backup programming MySQL Database as SQL File with PHP.

A simple way to do this is using a PHP Class to Backup MySQL Database as SQL. But we will got some times to search. Simply use code provide below will help us to do any Backup MySQL Database as SQL File.

The result of the Class is a SQL file with named set in the Class. The SQL File contain all Table as a data definition language (DDL) and data manipulation language (DML).

You need just provide a mysql connection in connection file, example here, i use mysqli_connect

$link=mysqli_connect($server, $username, $password, $database);

<?php
require_once('../include/koneksi.php');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=
". "kkkk". date('Ymd'). ".sql ");
header("Content-Transfer-Encoding: binary ");


echo "create database if not exists `db_wisuda`;\r\n";
echo "\r\n";
echo "use `db_wisuda`;\r\n";
echo "\r\n";
$m = "SHOW TABLE STATUS FROM db_wisuda";
$n = mysqli_query($link,$m);

while ($datao = mysqli_fetch_array($n))
{
$table=$datao[0];
echo "/*Table structure for table `$table` */\r\n";
echo "\r\n";
echo "DROP TABLE IF EXISTS `$table`;\r\n";
echo "\r\n";
echo "CREATE TABLE `$table` (\r\n";
$sql0="desc $table";
$res0= mysqli_query($link,$sql0);
$jum0=mysqli_num_rows($res0);
$j=0;
$key=0;
while($row0= mysqli_fetch_row($res0)){
 $j++;
 if($jum0==$j and $key==0)
 {
  if($row0[2]=='NO')
  {
   echo "`$row0[0]` $row0[1] NOT NULL);\r\n";
  }
  else
  {
   echo "`$row0[0]` $row0[1] default NULL);\r\n";
  }
 }
 else
 {
  if($row0[2]=='NO')
  {
   echo "`$row0[0]` $row0[1] NOT NULL,\r\n";
  }
  else
  {
   echo "`$row0[0]` $row0[1] default NULL,\r\n";
  }
 }
 if($row0[3]=='PRI')
 {
  $key=$key+1;
 }
}
$sql1= "desc $table";
$res1= mysqli_query($link,$sql1);
$k=0;
while ($row1= mysqli_fetch_row($res1))
{
 if($row1[3]=='PRI')
 {
  $k++;
  if($key-1==0)
  {
   echo "PRIMARY KEY  (`$row1[0]`)\r\n";
   echo ");\r\n";
  }
  else if($k>1 and $k<$key)
  {
   echo "`$row1[0]`,";
  }
  else if($k==$key)
  {
   echo "`$row1[0]`)\r\n";
   echo ");\r\n";
  }
  else
  {
   echo "PRIMARY KEY  (`$row1[0]`,";
  }
 }
  }
echo "\r\n";
  $sql= "SELECT * FROM $table";
$res= mysqli_query($link,$sql);
while($row= mysqli_fetch_row($res)){
echo "INSERT INTO `$table` VALUES('".implode("','",$row)."');\r\n";
}
}
echo "\r\n";
?>

Posted in: PHPMySQL