1 Attachment(s)
ABCmysqlDumper - MySQL db backup creator (PHP script)
Hi guys :wave:
I saw Nightwalker83's codebank submission titled "Backup a Mysql database (php 5.3.0)".
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;
}
}
/* Returns the table structure in the format:
CREATE TABLE IF NOT EXISTS `table_name` (
`column_name` column_type is_null default_value extras,
`column_name` column_type is_null default_value extras,
`column_name` column_type is_null default_value extras,
...
PRIMARY KEY (`column_primary`)
);
*/
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]}`)";
/* prepare the field. Eg: "(`member_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT)" */
$fields[] = trim( '`' . $r[0] . '` ' . $r[1] . ($r[2]=='NO'?' NOT NULL ':' ') . (empty($r[4])?'':"DEFAULT '{$r[4]}' ") . $r[5] );
}
$fields[] = $tbl_primary; // add the primary key field at the end
/* Create the table strucutre with the fields */
$tbl_struct = "CREATE TABLE IF NOT EXISTS `{$tbl_name}` ( \r\n "
. implode(",\r\n ", $fields) .
"\r\n);";
unset($fields); //free the memory
$row->close();
return $tbl_struct; // return it as a string
}
else
exit('ERROR: Unable to fetch the structure of the table - ' . $tbl_name);
}
/* Returns the table data in the format:
INSERT INTO `table_name` (`field1`, `field2`, `field3`, ..., `fieldn`) VALUES
(column_value1, 'column_value2', 'column_value3', ..., 'column_valuen'),
(column_value1, 'column_value2', 'column_value3', ..., 'column_valuen'),
(column_value1, 'column_value2', 'column_value3', ..., 'column_valuen'),
...
(column_value1, 'column_value2', 'column_value3', ..., 'column_valuen');
*/
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 :wave:
Re: ABCmysqlDumper - MySQL db backup creator (PHP script)
:: Testing ::
I have created a small db with two tables.
The following is the output obtained using my script:
cart-20_Jul_2012_101024.sql
sql Code:
--- ~ Table structure for table: `cart` ~
CREATE TABLE IF NOT EXISTS `cart` (
`id` int(11) NOT NULL auto_increment,
`customer_name` varchar(50) NOT NULL,
`amount` decimal(5,2) NOT NULL,
PRIMARY KEY (`id`)
);
--- ~ Data for table: `cart` ~
INSERT INTO `cart` (`id`, `customer_name`, `amount`) VALUES
('1', 'abc', '20.00'),
('2', 'yes', '2.50');
--- Total records in table `cart` = 2
--- ~ Table structure for table: `product` ~
CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`description` text,
`price` double NOT NULL,
PRIMARY KEY (`id`)
);
--- ~ Data for table: `product` ~
INSERT INTO `product` (`id`, `name`, `description`, `price`) VALUES
('1', 'soap', 'for bathing', '10'),
('2', 'shampoo', 'for hair', '5');
--- Total records in table `product` = 2
The same db, exported using the phpMyAdmin panel's EXPORT option:
cart.sql
sql Code:
-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 20, 2012 at 10:11 AM
-- Server version: 5.5.8
-- PHP Version: 5.3.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `cart`
--
-- --------------------------------------------------------
--
-- Table structure for table `cart`
--
CREATE TABLE IF NOT EXISTS `cart` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_name` varchar(50) NOT NULL,
`amount` decimal(5,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `cart`
--
INSERT INTO `cart` (`id`, `customer_name`, `amount`) VALUES
(1, 'abc', '20.00'),
(2, 'yes', '2.50');
-- --------------------------------------------------------
--
-- Table structure for table `product`
--
CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text,
`price` double NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`id`, `name`, `description`, `price`) VALUES
(1, 'soap', 'for bathing', 10),
(2, 'shampoo', 'for hair', 5);
:wave: