ABCmysqlDumper - MySQL db backup creator (PHP script)-VBForums
Results 1 to 2 of 2

Thread: ABCmysqlDumper - MySQL db backup creator (PHP script)

  1. #1

    Thread Starter
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,572

    Arrow ABCmysqlDumper - MySQL db backup creator (PHP script)

    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 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
    Attached Files Attached Files

    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

    Skills: PHP, MySQL, jQuery, VB.Net, VB6, Photoshop...

  2. #2

    Thread Starter
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,572

    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:
    1. --- ~ Table structure for table: `cart` ~
    2. CREATE TABLE IF NOT EXISTS `cart` (
    3.   `id` int(11) NOT NULL AUTO_INCREMENT,
    4.   `customer_name` varchar(50) NOT NULL,
    5.   `amount` decimal(5,2) NOT NULL,
    6.   PRIMARY KEY (`id`)
    7. );
    8. --- ~ Data for table: `cart` ~
    9. INSERT INTO `cart` (`id`, `customer_name`, `amount`) VALUES
    10. ('1', 'abc', '20.00'),
    11. ('2', 'yes', '2.50');
    12. --- Total records in table `cart` = 2
    13.  
    14. --- ~ Table structure for table: `product` ~
    15. CREATE TABLE IF NOT EXISTS `product` (
    16.   `id` int(11) NOT NULL AUTO_INCREMENT,
    17.   `name` varchar(255) NOT NULL,
    18.   `description` text,
    19.   `price` double NOT NULL,
    20.   PRIMARY KEY (`id`)
    21. );
    22. --- ~ Data for table: `product` ~
    23. INSERT INTO `product` (`id`, `name`, `description`, `price`) VALUES
    24. ('1', 'soap', 'for bathing', '10'),
    25. ('2', 'shampoo', 'for hair', '5');
    26. --- Total records in table `product` = 2

    The same db, exported using the phpMyAdmin panel's EXPORT option:

    cart.sql
    sql Code:
    1. -- phpMyAdmin SQL Dump
    2. -- version 3.3.9
    3. -- http://www.phpmyadmin.net
    4. --
    5. -- Host: localhost
    6. -- Generation Time: Jul 20, 2012 at 10:11 AM
    7. -- Server version: 5.5.8
    8. -- PHP Version: 5.3.5
    9.  
    10. SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    11.  
    12.  
    13. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    14. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    15. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    16. /*!40101 SET NAMES utf8 */;
    17.  
    18. --
    19. -- Database: `cart`
    20. --
    21.  
    22. -- --------------------------------------------------------
    23.  
    24. --
    25. -- Table structure for table `cart`
    26. --
    27.  
    28. CREATE TABLE IF NOT EXISTS `cart` (
    29.   `id` int(11) NOT NULL AUTO_INCREMENT,
    30.   `customer_name` varchar(50) NOT NULL,
    31.   `amount` decimal(5,2) NOT NULL,
    32.   PRIMARY KEY (`id`)
    33. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
    34.  
    35. --
    36. -- Dumping data for table `cart`
    37. --
    38.  
    39. INSERT INTO `cart` (`id`, `customer_name`, `amount`) VALUES
    40. (1, 'abc', '20.00'),
    41. (2, 'yes', '2.50');
    42.  
    43. -- --------------------------------------------------------
    44.  
    45. --
    46. -- Table structure for table `product`
    47. --
    48.  
    49. CREATE TABLE IF NOT EXISTS `product` (
    50.   `id` int(11) NOT NULL AUTO_INCREMENT,
    51.   `name` varchar(255) NOT NULL,
    52.   `description` text,
    53.   `price` double NOT NULL DEFAULT '0',
    54.   PRIMARY KEY (`id`)
    55. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
    56.  
    57. --
    58. -- Dumping data for table `product`
    59. --
    60.  
    61. INSERT INTO `product` (`id`, `name`, `description`, `price`) VALUES
    62. (1, 'soap', 'for bathing', 10),
    63. (2, 'shampoo', 'for hair', 5);

    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

    Skills: PHP, MySQL, jQuery, VB.Net, VB6, Photoshop...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.