How to create database Backup in php

Regardless of how large or small your business may be, data is an essential aspect of running it. The loss of business data can end up costing you a lot of money. Many businesses rely on online data backup for data protection to ensure that their valuable information is secure from theft, damage or the effects of a disaster.

Importance of Backup and Recovery

If an organization faces system outages or data loss, a full database recovery could cause serious operational delays. To shorten the time it takes to restore data and systems, database backups should be performed on a regular basis.
It's important for organizations to have a regular plan in place for both incremental and full data backup. But it may be costly for some to backup database in another server or hard drive. So therefore there is a easy and effective method to create a databse backup we are going to discuss below. In this it will create sql file which you can import in phpMyAdmin.

How to create database Backup

here we are using php method to create database backup but you can use this method in any language

// Datbase connection
$con = mysqli_connect("localhost", "root", "", "database_name");

// we use * if we want to create backup of whole database or if we want backup of selected //table we can write table name with comma seperater

$tables = '*';
$return = "";
$last_view = "";

if ($tables == '*') {
  // this query will fetch the table name of all the table from database
$sql = 'SHOW TABLES';
$result = mysqli_query($con, $sql);
 while ($row = mysqli_fetch_assoc($result)) {
 $alltables[] = $row;

 }

 foreach ($alltables as $keys = >$values) {
  foreach ($values as $key = >$value) {
  $alltable[] = $value;

  }
 }
} else {
//explode all the table name if table name is given in place of '*'
$alltable = is_array($tables) ? $tables : explode(',', $tables);
}
//cycle through
foreach ($alltable as $table) {
// this query will fetch the table from database
$sql = 'SELECT * FROM ' . $table;
$results = mysqli_query($con, $sql);

 while ($row = mysqli_fetch_assoc($results)) {
 $result[] = $row;
 }

$num_fields = count($result);
$return .= 'DROP TABLE IF EXISTS ' . $table . ';';
// this query will fetch create table query of every table
$sql = 'SHOW CREATE TABLE ' . $table;
$results = mysqli_query($con, $sql);
while ($row = mysqli_fetch_assoc($results)) {
$row2[] = $row;

}

if (isset($row2[0]['Create Table'])) {
$return .= "\n\n" . $row2[0]['Create Table'] . ";\n\n";

for ($i = 0; $i <$num_fields; $i++) {
$size = count($result[$i]);
$counts = 1;
$return .= "INSERT INTO " . $table . " VALUES(";

foreach ($result[$i] as $key = >$values) {
 if (isset($values)) {
 $return .= "'" . $values . "'";

 } else {

 $return .= "''";

 }

 if ($size != $counts) {
 $return .= ",";
 }
 $counts = $counts + 1;
}
$return .= ");\n";
}
$return .= "\n\n\n";
} elseif ($row2[0]['Create View']) {
//here it will add create query of view table
$last_view .= "\n\n" . $row2[0]['Create View'] . ";\n\n";
}
}
$return .= $last_view;
if (!file_exists('path/to/folder/')) {
mkdir('path/to/folder/', 0777, true);
}

$filename = 'databaseBackup.sql';
$filenameToFolder = 'path/to/folder/' . $filename;
$handle = fopen($filenameToFolder, 'w+');
//database backup file is created
fwrite($handle, $return);
$filesize = ftell($handle);
fclose($handle);



by This you can create a Sql file of mysql Database