ABCmysqlDumper - MySQL db backup creator (PHP script)

[akhileshbc]

Hi guys

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
/*
    ********** 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
    /* 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

[akhileshbc]

:: 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

--- ~ 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

-- 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);