Mysql Database Backup is a very required work of most of the web developer who has use Mysql database in their web development. If you have take regular database backup then it will reduce the risk of losing of data and if we have store mysql database back up then we can easily restore database if any emergency issue has occurred. So, please take Mysql database backup on regular interval to prevent loss of important data.
There are many different method available to get backup of Mysql database in SQL file and we can get that file in a single click from database hosting server. But here we can get Mysql database backup from without login into server and we can get from our web application. So for this we have make this PHP script by using this script we can backup Mysql database from our web application and we have not login into our database hosting account or phpMyAdmin. This PHP script will make sql file from Mysql database and download in our local computer.
Here we have use simple PHP script for backup of Mysql database in a single click from our web application. In this PHP script we have PHP PDO for make of this script. We have also use PHP file system function for write SQL script in file and after this by using header() function we have force download file in local computer. In this script we have use simple Mysql query for fetch Mysql database and make sql file and after write that sql script in file and force download in local computer. So, this is simple script for Backup mysql database using PHP.
Source Code
<?php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$get_all_table_query = "SHOW TABLES";
$statement = $connect->prepare($get_all_table_query);
$statement->execute();
$result = $statement->fetchAll();
if(isset($_POST['table']))
{
$output = '';
foreach($_POST["table"] as $table)
{
$show_table_query = "SHOW CREATE TABLE " . $table . "";
$statement = $connect->prepare($show_table_query);
$statement->execute();
$show_table_result = $statement->fetchAll();
foreach($show_table_result as $show_table_row)
{
$output .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
}
$select_query = "SELECT * FROM " . $table . "";
$statement = $connect->prepare($select_query);
$statement->execute();
$total_row = $statement->rowCount();
for($count=0; $count<$total_row; $count++)
{
$single_result = $statement->fetch(PDO::FETCH_ASSOC);
$table_column_array = array_keys($single_result);
$table_value_array = array_values($single_result);
$output .= "\nINSERT INTO $table (";
$output .= "" . implode(", ", $table_column_array) . ") VALUES (";
$output .= "'" . implode("','", $table_value_array) . "');\n";
}
}
$file_name = 'database_backup_on_' . date('y-m-d') . '.sql';
$file_handle = fopen($file_name, 'w+');
fwrite($file_handle, $output);
fclose($file_handle);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=' . basename($file_name));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($file_name));
ob_clean();
flush();
readfile($file_name);
unlink($file_name);
}
?>
<!DOCTYPE html>
<html>
<head>
<title>How to Take Backup of Mysql Database using PHP Code</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<br />
<div class="container">
<div class="row">
<h2 align="center">How to Take Backup of Mysql Database using PHP Code</h2>
<br />
<form method="post" id="export_form">
<h3>Select Tables for Export</h3>
<?php
foreach($result as $table)
{
?>
<div class="checkbox">
<label><input type="checkbox" class="checkbox_table" name="table[]" value="<?php echo $table["Tables_in_testing"]; ?>" /> <?php echo $table["Tables_in_testing"]; ?></label>
</div>
<?php
}
?>
<div class="form-group">
<input type="submit" name="submit" id="submit" class="btn btn-info" value="Export" />
</div>
</form>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#submit').click(function(){
var count = 0;
$('.checkbox_table').each(function(){
if($(this).is(':checked'))
{
count = count + 1;
}
});
if(count > 0)
{
$('#export_form').submit();
}
else
{
alert("Please Select Atleast one table for Export");
return false;
}
});
});
</script>
great was looking for this already long time
ReplyDeletehopefully there is one coming on how to restore from mysql file to the database?
super. vos cours sont trop top
ReplyDeleteTHANKS YOU
ReplyDeletethank you for the code
ReplyDeletebut when i change the table name in line 2 it want work it only work with table that called "testing" pls help
And how to restore a Backup.sql using PDO in PHP
ReplyDeleteexcelent job
ReplyDeletei am getting "There is an error in Database Import" this msg even after successfully imported
ReplyDeletehello i have names arabic for export what is the solution for this ????? ????
ReplyDeletehello, thank you
ReplyDeletei have names arabic for export what is the solution for this ????? ????
great solution, usually I use this one: https://github.com/SergheiPogor/BackUp-MySQL
ReplyDeleteit helped me thouogh but,
ReplyDeletehey if the records consists apostrophe, data like father's Name , then the backup query gets all jumbled up, means the insert query gets compromised after apostrophe, considered as end of statement and all.
whats the resolution
example:
INSERT INTO web_mail_keywords (KEY, FIELDNAME, DISPLAYNAME, TABLENAME, TMPTYPE) VALUES ('7','FNAME','Father's Name','mas_employee','0');
the NULL values are appended as Empty String (NULL) -->'' in insert query
ReplyDeleteuse this to hide the error
ReplyDeletehello sir, sorry.. can you send file SQL... sorry can't work.. can you help me.. thank
ReplyDeleteNotice: Undefined index: Tables_in_testing in C:\xampp\htdocs\testing\index.php on line 76
ReplyDeletehow to solve this error?
thanks
Notice: Undefined index: tables_in_testing in E:\xampp\htdocs\erratum\save_bdd.php on line 76
ReplyDeleteNotice: Undefined index: tables_in_testing in E:\xampp\htdocs\erratum\save_bdd.php on line 76
ReplyDeleteHow to fix it?
PS: Line 76:
input type="checkbox" class="checkbox_table" name="table[]" value="" />