I thought about creating a slightly better one. Still mine needs to be improved a lot more.
Existing Method: There is a command called mysqldump which could be executed in the shell to create the dump.
Anyway, the below one is my version of the dump creator(made from scratch):
Class - ABCmysqlDumper.php:
PHP Code:
<?php
/*
********** ABCmysqlDumper ***********
*************************************
A small utility to backup the whole db
Created by:
Akhilesh B Chandran
(aka, akhileshbc)
Created On: July 18, 2012
*/
class ABCmysqlDumper
{
private $db;
private $dbname;
/* Constructor : Creates the db connection with host, dbname, username, password */
function __construct($db_host, $db_name, $db_user, $db_pwd)
{
$this->dbname = $db_name; // stores the dbname for later user
$this->db = new mysqli($db_host, $db_user, $db_pwd, $db_name);
if ($this->db->connect_errno)
{
echo "Failed to connect to MySQL: (" . $this->db->connect_errno . ") " . $this->db->connect_error;
exit();
}
}
/* Destructor : Close the db connection */
function __destruct()
{
mysqli_close($this->db);
}
/* This will do fetching and appending process, which is the core of our sqldump creator :D
@saveFile = true -- will allow the user to download the dump as a file, with the filename in the format "dbname-Day_Month_Year_HHMMSS" (only if the optional parameter $fileName is not supplied)
@saveFile = false -- will return the dump back to caller
*/
public function generateDump($saveFile = true, $fileName = '')
{
$file = ''; // will hold the whole contents
/* Fetches the table names from the database. For more details: http://dev.mysql.com/doc/refman/5.0/en/show-tables.html */
if ($result = $this->db->query('SHOW TABLES FROM ' . $this->dbname))
{
/* loop through each of the table and creates the table structure as well as the table data also */
while($row = $result->fetch_row())
{
$file .= "--- ~ Table structure for table: `{$row[0]}` ~\r\n" . $this->getTableStruct($row[0]) . "\r\n" .
"--- ~ Data for table: `{$row[0]}` ~\r\n" . $this->getTableData($row[0]) . "\r\n\r\n" ;
}
$result->close(); // close it
/* if user wants to save */
if($saveFile)
{
if($fileName == '') // if filename is not supplied, create a filename
$fileName = $this->dbname . '-' . date('d_M_Y_His');
/* Sent the header */
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=' . $fileName . '.sql');
header('Pragma: no-cache');
ob_clean();
flush();
/* output */
echo $file;
}
else
return $file; // return the dump back to the caller (if $saveFile = false)
}
else
{
return false;
}
}
*/
private function getTableStruct($tbl_name)
{
/* Fetch the column details using DESCRIBE. For more details: http://dev.mysql.com/doc/refman/4.1/en/describe.html */
if ($row = $this->db->query('DESCRIBE ' . $tbl_name))
{
$tbl_primary = ''; // will hold the primary key field
/* loop through each of the column */
while($r = $row->fetch_row())
{
/*
[0] - Field ; [1] - Type ; [2] - Null ; [3] - Attributes ; [4] - Default ; [5] - Extra
Example field: `member_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
Exampe row : Array ( [0] => member_id [1] => bigint(20) unsigned [2] => NO [3] => PRI [4] =>(default_value) [5] => auto_increment )
*/
// if PRIMARY KEY, then save it so that we could append it at the end.
if( $r[3] == 'PRI' )
$tbl_primary = "PRIMARY KEY (`{$r[0]}`)";
*/
private function getTableData($tbl_name)
{
/* Fetch all the records from the table */
if ($row = $this->db->query('SELECT * FROM ' . $tbl_name))
{
$total_rec = $row->num_rows; /* Total Records */
$records = array();
if($r = $row->fetch_assoc())
{
/* Get the first recordd and populate the column names and it's values */
$k = array();
$v = array();
foreach($r as $key=>$value)
{
$k[] = "`{$key}`";
$v[] = "'{$value}'";
}
$records[] = '(' . implode(', ', $v) . ')'; // add the column values
/* Loop through the rest of the records(second record onwards) and populate the column values */
while($r = $row->fetch_row())
{
$records[] = "('" . implode("', '", $r) . "')";
}
/* Create the table data */
$tbl_data = "INSERT INTO `{$tbl_name}` (" . implode(', ', $k) . ") VALUES\r\n"
. implode(",\r\n", $records) .
";\r\n--- Total records in table `{$tbl_name}` = {$total_rec}";
unset($records); //free the memory
$row->close();
return $tbl_data; // return it as a string
}
else
{
$row->close();
return '';
}
}
else
exit('ERROR: Unable to fetch the date from the table - ' . $tbl_name);
}
}
?>
This is the class which is the backbone.
You could use it like this: index.php
PHP Code:
<?php
/* Database Settings */
$db_host = 'localhost'; // host name
$db_name = 'abcchat'; // database name
$db_user = 'akhilesh'; // database user
$db_pwd = 'abccba'; // database password
/* -------- CHANGE THE ABOVE SETTINGS -------- */
/* Include the class */
include('ABCmysqlDumper.php');
$dumper = new ABCmysqlDumper($db_host, $db_name, $db_user, $db_pwd); // creating an object of the class, passing the db settings to its constructor
$file = $dumper->generateDump(); /* generates the dump and will allow you to download the file with filename in the format "dbname-Day_Month_Year_HHMMSS" */
//$file = $dumper->generateDump(false); /* this will return the dump(will be available in $file) */
//$file = $dumper->generateDump(true, 'abc'); /* generates the dump and will allow you to download the file with filename as "abc.sql" */
if($file === false)
echo 'Sorry, was not able to create the dump!';
//else
// echo $file;
?>
I have tried to comment most of the parts, so the code would be self explanatory. And I have tried to maintain the indenting and formatting.
I haven't intensively tested it. And the basic testing that I have done was on my local machine which runs WAMP Server 2.1
Some points of interest:
need to test whether everything is working properly, on several databases
need to test it on a big db with several tables and records
need to find a way to escape single quotes in datafields
need to check what happens to blob fields
Feel free to edit it to suit your needs. Or if you have any suggestions or comments, please post it here.
Thanks
If my post was helpful to you, then express your gratitude using Rate this Post.
And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video) My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet Social Group:VBForums - Developers from India