[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 968
  • Last Modified:

php script to dump MySQL tables

Is it possible to write a php script to "dump" selected tables of a MySQL database?

I want the resultant file to be a .sql file that could be easily restored (manually) if need be.
0
Richard Korts
Asked:
Richard Korts
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
That one of the things that phpMyAdmin does.  http://www.phpmyadmin.net/home_page/index.php  Install or use it, much easier than trying to write your own.  They've been doing it for a long time.

If you have shell or command line access, you can use the 'mysqldump' program.
0
 
Loganathan NatarajanLAMP DeveloperCommented:
I used this script on my hosting package, it worked well. Check out

I had permission to run as cron.
0
 
Marco GasiFreelancerCommented:
Some time ago I wrote this class and I used it to integrate into a my application the ability to create backups of a MySql db. But if you only need to do your backups, I agree with DaveBaldwin: phpMyAdmin is the best tool for that. Anywy I post hee the class code and the example to learn how to use it

The class
<?php
if (!class_exists('MySqlDumper', false)){
    /**
     * MySqlDumper
     *
     * <p>This is a PHP class which allow you to programmatically create
     * mysql database's dump files and use these dump files to restore a database
     * to its prvious state</p>
     * <p>MySqlDumper is free of charge and you can freely use and distribute it.</p>
     * <p>Use it at your own risk: I'm not responsable for any damage derving from 
     * use of MySqlDumper</p>
     * @name MySqlDumper
     * @package MySqlDumper
     * @version 0.1.0
     * @author Marco Gasi - info@em-web.it
     * @link http://www.em-web.it
     */

    class MysqlDumper {
    /**
     *
     * <p>MySqlDumper class</p>
     * <p>MySqlDumper is a simple class which allow you to programmatically create
     * mysql database's dump files and use these dump files to restore a database
     * to its prvious state</p>
     *
     */

        private $dbConn;
        private $hostname;
        private $username;
        private $password;
        private $dbName;
        private $destFile;
        private $destFolder = "dumps";
        private $dumpFile;
        private $errorMsg = array();

        /**
         * Description and usage
         *
         * Public function dbConnect
         * @return void
         * @param  string $val
         *
         * <p>It establishes a connection to database. If a connection exists, it close
         * it and then open a new connection: this allows user to choose a new database
         * and establish a new connection to it without destroy object which holds connection.</p>
         * <p>This function return true if connection was successfully established, otherwise
         * it returns false and shows error message.</p>
         */
        private function _dbConnect() {
            $this->errorMsg = array();
            if ($this->_checkDbConn()) $this->dbConn = null;
            if (empty($this->dbName)){
                $conn = @mysql_connect($this->hostname, $this->username, $this->password);
                if(!$conn) $this->errorMsg[] = "<b>function _dbConnect - </b>MySqlDumper cannot establish a connection with following message " . mysql_error();
                $this->dbConn = $conn;
            }else{
                $conn = @mysql_connect($this->hostname, $this->username, $this->password);
                if(!$conn) $this->errorMsg[] = "<b>function _dbConnect - </b>MySqlDumper cannot establish a connection with following message " . mysql_error();
                $this->dbConn = $conn;
                $selDb = @mysql_select_db($this->dbName, $conn);
                if(!$selDb) $errorMsg[] = "Database not found: " . mysql_error();
            }
            if (empty($this->errorMsg)){
                $this->_setDestFile();
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }

        /**
         * Private function _checkDbConn
         * @param void
         * @return boolean
         * check if a database connection is active
         */
        private function _checkDbConn(){
            if (!isset($this->dbConn)){
                return false;
            }else{
                return true;
            }
        }

        /**
         * Private function getErrors
         * @param void
         * @return error's list
         */

        private function getErrors(){
            if(!empty($this->errorMsg)){
                ob_end_clean();
                echo "Following errors was found:<br />";
                foreach ($this->errorMsg as $e){
                    echo "$e<br />";
                }
            }
            die();
        }

        /**
         * Description and usage
         *
         * <p>Here they are set deafult values for new instances.</p>
         * <p>$hostname, $username and $password are mandatory,
         * $dbName is optional. In fact you can create a new instance
         * of MySqlDumper without specify a database and then retrieve a
         * list of all databases hosted in yuour server to choose among them
         * hich database use for backup procedures.</p>
         *
         * <p>You create a new insatance of MySqlDumper this way:</p>
         * <code>$dumper = new MySqlDumper('host_name', 'user_name', 'password', ['database_name'])</code>
         * <br>
         * <p>After having set parameters for a connection, MySqlDumer calls
         * private function _dbConnect() to establish a connection to your
         * database server.</p>
         * @param <string> $hostname
         * @param <string> $username
         * @param <string> $password
         * @param <string> $dbName
         */

        public function __construct($hostname, $username, $password, $dbName="") {
            error_reporting(E_NONE);
            ob_start();
            set_exception_handler('getErrors');
            $this->hostname = $hostname;
            $this->username = $username;
            $this->password = $password;
            if ($dbName != "") $this->dbName = strtolower($dbName);
            $this->_dbConnect();
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory: it closes database connection</p>
         */
        public function  __destruct() {
            mysql_close($this->dbConn);
        }

        /**
         * Description and usage
         *
         * <p>It returns the database name selected for backup or restore.
         * If a database was not selected yet, it returns an empty string.</p>
         * <p>Usage:</p>
         * <code>$dbname = $dumper->getDbName();</code>
         * <br>
         * @param void
         * return string
         */
        public function getDbName(){
            if (!empty($this->dbName)){
                return $this->dbName;
            }
            if(empty($this->dbName) && !empty($this->dumpFile)){
                $dbName = preg_replace("/-(\d){8}-(\d){6}/", "", $this->dumpFile);
                $this->dbName = substr($dbName, 5, strlen($dbName)-9);
                return $this->dbName;
            }else{
                return "";
            }
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->getHostName();</code>
         * <br>
         * @return <string>
         */
        public function getHostName(){
            return $this->hostname;
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->getUserName();</code>
         * <br>
         * @return <string>
         */
        public function getUserName(){
            return $this->username;
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->getPassword();</code>
         * <br>
         * @return <string>
         */
        public function getPassword(){
            return $this->password;
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->setDbName('databasew_name');</code>
         * <br>
         * <p>Useful if you don't select a database when you instantiate MySqlDumper.</p>
         *
         * @param string
         * return void
         */
        public function setDbName($dbName){
            if($this->dbName == ''){
                $this->dbName = strtolower($dbName);
                $this->_dbConnect();
            }
        }

        /**
         * Private function _setDestFile
         * @param void
         * @return void
         * set the name of dump file
         */
        private function _setDestFile(){
            $now = getdate();
            $now = date("-Ymd-His");
//            $this->destFile = "dumps/dump_" . $this->dbName . "-" . $now["year"] . $now["mon"] . $now["mday"] . "-" .$now["hours"] . $now["minutes"] . $now["seconds"] . ".sql";
            $this->destFile = "dumps/dump_" . $this->dbName . $now . ".sql";
        }

        /**
         * Description and usage
         *
         * <p>When you wish restore a database, you can use this function to choose
         * a dump file to use.</p>
         * <p>If a database was not selected when you created
         * MySqlDumper instance, it will be set here</p>
         * <p>If choosen dump file doesn't fit database you're connected to,
         * you'll be noticed with an error message.</p>
         * <p>Otherwise, the function return true.</p>
         * <p>Usage:</p>
         * <code>$dumper->setDumpFile('dumFile_name');</code>
         * <br>
         * @param <string> $value
         * @return boolean
         */
        public function setDumpFile($value){
            $this->dumpFile = $value;
            $dbName = preg_replace("/-(\d){8}-(\d){6}/", "", $this->dumpFile);
            $dbName = substr($dbName, 5, strlen($dbName)-9);
            if ($this->dbName == ''){
                $this->setDbName($dbName);
            }
            if ($this->dbName != $dbName){
                $this->errorMsg[] = "<b>Function setDumpFile()</b>: You want to restore $dbName database
                        but you're already connected to $this->dbName database. Please, choose a
                        different dump file or establish a new database connection.";
            }
            if (empty($this->errorMsg)){
                $this->_setDestFile();
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumpfile = $dumper->getDumpFile();</code>
         * <br>
         * @param void
         * #return string
         */
        public function getDumpFile(){
            return $this->dumpFile;
        }

        /**
         * Description and usage
         *
         * <p>This function will be used internally to serialize MySqlDumper
         * instance and store it in a $_SESSION variable</p>
         * @return <array>
         */
        public function __sleep()
        {
            return array('hostname', 'username', 'password', 'dbName');
        }

        /**
         * Description and usage
         *
         * <p>This function will be used internally to unserialize MySqlDumper
         * instance and establish a connection to database</p>
         * return void
         */
        public function __wakeup()
        {
            $this->_dbConnect();
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$lastDumpfile = $dumper->getLastDump();</code>
         * <br>
         * @param void
         * #return string
         */

        public function getLastDump(){
            return $this->destFile;
        }

        /**
         * private function getAllValues
         * @param string $sql
         * @param boolean $show_error
         * @return array
         */

        private function getAllValues($sql){
            if (!isset($sql) || $sql == ''){
                echo "Can't execute the query: please, verify it.";
                return false;
            }
            $error = "";
            $result = mysql_query($sql) or $error = mysql_error();
            if ($error == ""){
                $resultSet = array();
                $keys = array();
                $values = array();
                while ($row = mysql_fetch_assoc($result)){
                    foreach ($row as $k=>$v){
                        $keys[] = $k;
                        $values[] = $v;
                    }
                    for ($x=0;$x<count($keys);$x++){
                        $partialResult[$keys[$x]] = $values[$x];
                    }
                    $resultSet[] = $partialResult;
                }
                return $resultSet;
            }else{
                $this->errorMsg[] = "<b>Function getAllValues:</b> Error occurred executing query $sql: ". $error;
                $this->getErrors();
            }
        }


        /**
         * Description and usage
         *
         * <p>It analyzes selected database and creates a dump file accordingly,
         * storing it on the hard drive.</p>
         * <p>On failure, it returns one or more error messages.</p>
         * <p>Usage:</p>
         * <code>$dumper->doBackup();</code>
         * <br>
         * @param  void
         * @return boolean
         */

        public function doBackup() {
            $this->errorMsg = array();
            if (!isset($this->dbConn)) {
                $this->errorMsg[] = "<b>Function doBackup - </b>No valid connection to a database was found.";
            }
            if (empty($this->dbName)) {
                $this->errorMsg[] = "<b>Function doBackup - </b>No database selected to backup.";
            }
            if (!$fp = fopen($this->destFile, "w")) {
                $this->errorMsg[] = "<b>Function doBackup - </b>Error creating backup file.";
            }
            fwrite($fp, "CREATE DATABASE IF NOT EXISTS $this->dbName;\n");
            $tables = $this->listTables();
            if (count($tables) == 0){
                $this->errorMsg[] = "<b>Function doBackup - </b>No tables found.";
            }else{
                foreach ($tables as $t) {
                    fwrite($fp, "DROP TABLE IF EXISTS `$t`;\n");
                    $sql = "SHOW CREATE TABLE `$t`";
                    $res = @mysql_query($sql);
                    if (!$res){
                        $this->errorMsg[] = "<b>Function doBackup - </b>Query cannot be executed with following message: ".mysql_error();
                    }else{
                        $row = @mysql_fetch_row($res);
                        $create = $row[1].";";
                        $create = preg_replace("%\n%", " ", $create);
                        fwrite($fp, $create . "\n");
                    }
                    $sql = "SELECT * FROM `$t`";
                    $data = $this->getAllValues($sql);
                    if (count($data)>0){
                        $sql = "SHOW COLUMNS FROM `$t`";
                        $fields = $this->getAllValues($sql);
                        if (count($fields) == 0){
                            $this->errorMsg[] = "<b>Function doBackup - </b>Query cannot be executed with following message: ".mysql_error();
                        }else{
                            $insert = "INSERT INTO `$t` (";
                            foreach ($fields as $key => $value) {
                                $insert .= $value['Field'] . ", ";
                            }
                            $insert = substr($insert, 0, -2);
                            $insert .= ") VALUES";
                        }
                        $num = count($data);
                        foreach ($data as $d) {
                            $insert .= "(";
                            $i = 0;
                            foreach ($d as $s) {
                                $insert .= "'" . addslashes($s) . "', ";
                            }
                            $insert = substr($insert, 0, -2);
                            $insert .= "),";
                        }
                        $insert = substr($insert, 0, -1);
                        fwrite($fp, $insert . ";");
                        fwrite($fp, "\n");
                    }
                }
                fclose($fp);
            }
            if (empty($this->errorMsg)){
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }

        /**
         * Description and usage
         *
         * <p>It analyzes selected dump file and executes queries to,
         * restore database on its previous state.</p>
         * <p>On failure, it returns one or more error messages.</p>
         * <p>Usage:</p>
         * <code>$dumper->doRestore();</code>
         * <br>
         * @param  void
         * @return boolean
         */
        public function doRestore() {
            $this->errorMsg = array();
            if (!isset($this->dbConn)) {
                $this->errorMsg[] = "<b>Function doRestore - </b>No valid connection to a database was found.";
            }
            if (empty($this->dbName) && empty($this->dumpFile)) {
                $this->errorMsg[] = "<b>Function doRestore - </b>No database selected to restore.";
            }elseif (empty($this->dbName)) {
                $dbName = preg_replace("/-(\d){8}-(\d){6}/", "", $this->dumpFile);
                $this->dbName = substr($dbName, 5, strlen($dbName)-9);
            }
            $fname = $this->getDumpFile();
            if (!$dumps = $this->listDumpFiles()){
                $this->getErrors();
            }else{
                if (in_array($fname, $dumps)){
                    $fname = $this->destFolder."\\".$fname;
                    $queries = file($fname);
                    foreach ($queries as $q){
                        $res = mysql_query($q);
                        if (!$res){
                            $this->errorMsg[] = "<b>Function doRestore - </b>Query cannot be executed with following message: ".mysql_error();
                        }
                    }
                }else{
                    $this->errorMsg[] = "<b>Function doRestore - </b>cacca No database selected to restore.";
                }
            }
            if (empty($this->errorMsg)){
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }


        /**
         * Description and usage
         *
         * <p>It retrieves a list of databases hosted in server you're connected
         * to and it allows to select a database to backup.</p>
         * <p>Usage:</p>
         * <code>
         * $databases = $dumper->listDatabases();<br><foreach ($databases as $db){
         *      echo "<input type='radio' name='db' value='$db'>$db<br/>";
         * }</code>
         * <br>
         * <p>On failure it returns one or more error messages.</p>
         * @param <$caseSensitive> boolean default true
         * @param <$silent> boolean default false
         * @return array
         */
        public function listDatabases($caseSensitive=true, $silent=false) {
            $this->errorMsg = array();
            if(!$this->_checkDbConn()){
                $this->errorMsg[] = "<b>Function listDatabases - </b>No connection was found.";
                $this->getErrors();
            }else{
                $databases = array();
                $sql = "SHOW DATABASES";
                $res = @mysql_query($sql);
                if (!$res){
                    $this->errorMsg[] = "<b>Function listDatabases - </b>Query cannot be executed with following message: ".$res->errorInfo();
                }
                while ($row = mysql_fetch_array($res)) {
                    if ($caseSensitive){
                        $databases[] = $row[0];
                    }else{
                        $databases[] = strtolower($row[0]);
                    }
                }
            }
            if (count($databases)>0){
                return $databases;
            }else{
                if ($silent) $this->getErrors();
                return false;
            }
        }

        private function showAlert($msg){
            echo "<script type=\"text/javascript\">alert('$msg');</script>";
        }

        /**
         * Description and usage
         *
         * <p>It retrieves a list of tables contained in database you're connected to.</p>
         * <p>Usage:</p>
         * <code>
         * $tables = $dumper->listTablses();
         * echo "<ul>";
         * <foreach ($tables as $tb){
         *      echo "<li>table: $tb</li>";
         * }
         * echo "</ul>";
         * </code>
         * <br>
         * <p>On failure it returns one or more error messages.</p>
         * @param <$silent> boolean default false
         * @return array
         */
        public function listTables() {
            $this->errorMsg = array();
            if (empty($this->dbName)){
                $this->errorMsg[] = "<b>Function listTables - </b>No database was selected.";
                $this->getErrors();
            }else{
                $tables = array();
                $sql = "SHOW TABLES";
                $res = @mysql_query($sql);
                if (!$res){
                    $this->errorMsg[] = "<b>Function listTables - </b>Query cannot be executed with following message: ".mysql_error();
                    $this->getErrors();
                }else{
                    while ($row = mysql_fetch_array($res)) {
                        $tables[] = $row[0];
                    }
                }
            }
            if (empty($this->errorMsg)){
                if (count($tables)>0){
                    return $tables;
                }else{
                    return false;
                }
            }else{
                $this->getError();
            }
        }

        /**
         * Description and usage
         *
         * <p>It retrieves a list of tables contained in database you're connected to.</p>
         * <p>Usage:</p>
         * <code>
         * $dumps = $q->listDumpFiles();
         * echo "<ul>";
         * foreach ($dumps as $dump){
         *      echo "<input type='radio' name='dump' value='$dump'>$dump<br/>\n";
         * }
         * echo "</ul>";
         * </code>
         * <br>
         * <p>On failure it returns one or more error messages.</p>
         * @param <$silent> boolean default false
         * @return array
         */
        public function listDumpFiles($silent=false){
            $this->errorMsg = array();
            if (!$dumpDir = scandir($this->destFolder,1)) {
                //$this->errorMsg[] = "<b>Function listDumpFiles - </b>Dumps directory was not found on this server.";
                //$this->getErrors();
            }else{
                $found = false;
                foreach ($dumpDir as $dump){
                    if (!preg_match('/^dump_/', $dump)) continue;
                    $dumpFiles[] = $dump;
                    $found = true;
                }
                if (!$found){
                    //$this->errorMsg[] = "<b>Function listDumpFiles - </b>No dump file was found in on this server.";
                }
            }
            if (count($dumpFiles)>0){
                return $dumpFiles;
            }else{
                if ($silent) $this->getErrors();
                return false;
            }
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>
         * foreach($q->showDump() as $dump){
         *      echo $dump."<br />";
         * }
         * </code>
         * <br>
         * @param void
         * #return string
         */
        public function showDump() {
            return file($this->destFile);
        }

    }
}
?>

Open in new window


The example
<?php
error_reporting(E_ALL ^ E_NOTICE);
ini_set("display_errors", 1);
include("MySqlDumper.php");
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
    <head>
        <title>MySql Dumper Demo</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <meta name="MSSmartTagsPreventParsing" content="TRUE" />
        <script type="text/javascript">
            /**
             * You can call this function passing how many ids you wish
             * the function will check if a value was set for each element id
             * if not
             */
            function checkValues(){
                for (var i = 0; i < arguments.length; i++){
                    var arg = arguments[i];
                    var element = document.getElementById(arg);
                    if (element.value == ''){
                        alert("You must specify all requested values.");
                        return false;
                    }
                }
                return true;
            }
        </script>
        <style type="text/css">
            /*<![CDATA[*/
            html,body{
                margin:0;
                padding:0;
                text-align:center;
                background:#E1E1E1;
            }
            #centered{
                width:600px;
                margin: 100px auto;
                text-align: left;
            }
            #content{
                width:600px;
                background:#D4D4FF;
                padding:10px;
                border:1px solid #911B1B;
                overflow:auto;
            }
            h2{
                text-align:center;
            }
            .code{
                background: #FFFFD4;
                background:#FDFDD2;
                color: blue;
                padding-left:10px;
            }
            .list ul, li{
                list-style:decimal-leading-zero;
                border-bottom:1px solid #911B1B;
            }
            #dumpreader{
                width:400px;
                height:300px;
                background:white;
                color:black;
                overflow:auto;
                padding:4px;
                border-right:2px solid #D2D2D2;
                border-bottom:2px solid #D2D2D2;
                border-top:2px solid #A5A5A5;
                border-left:2px solid #A5A5A5;
            }
            form{
                text-align:right;
            }
            .left{
                text-align:left;
            }
            #colA{
                float:left;
                width:279px;
                padding:10px;
                border-right:1px solid #A5A5A5;
            }
            #colB{
                float:left;
                width:280px;
                padding:10px;
            }
            .simpleLink{
                background: transparent url(bgt.gif)top left no-repeat;
                border:none;
                color:blue;
                cursor:pointer;
                text-decoration:underline;
                display:table-cell;
            }
            /*]]>*/
        </style>
    </head>
    <body>
        <div id="centered">
        <div id="content">
        <h2>MySql Dumper Demo</h2>
        <?php
        $pageID = isset($_POST['pageID']) ? $_POST['pageID'] : 1;
        switch ($pageID) {
            case 1:
                echo "The first thing to do is to set some variable to allow connection to database server.<br />
                    You are free to not to choose a database to backup now and leave blank database field: you'll be
                    able to select a database later.<br /><br /><br />";
                echo "<form id='' action='" . $_host['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='" . ($pageID+1) . "' />";
                echo "Host name (usually localhost): <input type='text' id='host' name='host' size='30' /><br />";
                echo "User name (usually root): <input type='text' id='user' name='user' size='30' /><br />";
                echo "Your password: <input type='password' id='pwd' name='pwd' size='30' /><br />";
                echo "Database name (optional);<input type='text' id='1dbname' name='dbname' size='30' /><br />";
                echo "<input type='submit' id='submit' name='submit' onclick='return checkValues(\"host\", \"user\", \"pwd\")' value='Send data' />";
                echo "</form>";
                break;
            case 2:
                $host = $_POST['host'];
                $user = $_POST['user'];
                $pwd = $_POST['pwd'];
                if($_POST['dbname'] != '') $dbname = $_POST['dbname'];
                echo "Data for database connection:<br /><br />";
                echo "Host name: $host<br />User name: $user<br />Password: $pwd<br />Database name: $dbname<br /><br />";
                echo "Now we can establish a connection using this line of code:";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>\$q = new MysqlDumper(\$host, \$user, \$pwd);</div>";
                echo "<br />";
                echo "<br />";
                echo "As you can see, you can establish a connection to database server without passing a database name, but
                    it's probable you'll find more comfortable to pass it directly in the constructor using this syntax:";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>\$q = new MysqlDumper(\$host, \$user, \$pwd, \$dbname);</div>";
                echo "<br />";
                echo "<br />";
                echo "Click button to proceed and establish a connection to your database server, please.";
                echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='" . ($pageID+1) . "' />";
                echo "<input type='hidden' id='host' name='host' value='$host' />";
                echo "<input type='hidden' id='user' name='user' value='$user' />";
                echo "<input type='hidden' id='pwd' name='pwd' value='$pwd' />";
                if(isset($dbname))
                    echo "<input type='hidden' id='dbname' name='dbname' value='$dbname' />";
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<br />";
                echo "<br />";
                echo "<input type='submit' id='submit' name='submit' value='Connect' />";
                echo "</form>";
                break;
            case 3:
                $host = $_POST['host'];
                $user = $_POST['user'];
                $pwd = $_POST['pwd'];
                if ($_POST['dbname'] != '') $dbname = $_POST['dbname'];
                $q = new MysqlDumper($host, $user, $pwd, $dbname);
                $_SESSION['conn'] = serialize($q);
                $dumps = $q->listDumpFiles();
                echo "<div id='colA'>";
                if (count($dumps) > 0){
                    echo "If you have made some backup yet, perhaps you wish to see what dump files you have:<br />";
                    echo "<br />";
                    echo "<br />";
                    echo "<div class='code'>\$dumps = \$dumps = \$q->listDumpFiles();</div>";
                    echo "<br />";
                    echo "<br />";
                    echo "<form class='left' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                    echo "<h3>Dump files:</h3>";
                    foreach ($dumps as $dump){
                        echo "<input type='radio' name='dump' value='$dump'>$dump<br/>\n";
                    }
                    echo "<br />";
                    echo "<br />";
                    echo "Now you can choose a database to restore and write:";
                    echo "<br />";
                    echo "<br />";
                    echo "<div class='code'>\$q->doRestore();</div>";
                    echo "<br />";
                    echo "<br />";
                    echo "<input type='hidden' id='pageID' name='pageID' value='7' />";
                    unset($_POST['submit']);
                    unset($_POST['pageID']);
                    echo "<input type='submit' id='submit' name='submit' value='Restore procedure' />";
                    echo "</form>";
                }else{
                    echo "You don't have dump files to restore.";
                }
                echo "</div>";
                echo "<div id='colB'>";
                echo "If you wish to perform a backup click button below and go to the next page.<br />";
                echo "<br />";
                echo "<br />";
                echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='" . ($pageID+1) . "' />";
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<input type='submit' id='submit' name='submit' value='Backup procedure' />";
                echo "</form>";
                echo "</div>";
                break;
            case 4:
                $q = unserialize($_SESSION['conn']);
                echo "<br />";
                echo "<br />";
                echo "Now we can retrieve a list of databases hosted by specified host:";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>\$db = \$q->listDatabases();</div>";
                echo "<br />";
                echo "<br />";
                echo "Database hosted by ".$q->getHostName().":";
                echo "<br />";
                echo "<br />";
                $db = $q->listDatabases();
                if($q->getDbName() != ''){
                    echo "<h4>You have selected the database ". $q->getDbName() .".</h4>";
                    echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                    echo "<table><tr><td>";
                    echo "If you want save another database, you have to go to the first page:";
                    echo "<input type='hidden' id='pageID' name='pageID' value='1' />";
                    unset($_POST['submit']);
                    unset($_POST['pageID']);
                    echo "<input type='submit' class='simpleLink' id='submit' name='submit' value='Back to beginning' />";
                    echo "</td></tr></table>";
                    echo "</form>";
                    echo "<form class='left' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                }else{
                    echo "<h4>Select the database you wish to backup:</h4>";
                    echo "<form class='left' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                    foreach ($db as $d){
                        echo "<input type='radio' name='db' value='$d'>$d<br/>\n";
                    }
                }
                echo "<br />";
                echo "<br />";
                echo "If you have not yet set a database name when you have initialized the \$q object, you can do it this way:<br />";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>\$q->setDbName('my_database');</div>";
                echo "<br />";
                echo "<br />";
                echo "<input type='hidden' id='pageID' name='pageID' value='" . ($pageID+1) . "' />";
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<br />";
                echo "<br />";
                echo "<input type='submit' id='submit' name='submit' value='Next' />";
                echo "</form>";
                break;
            case 5:
                $passValue = false;
                $q = unserialize($_SESSION['conn']);
                if ($_POST['db'] != ''){
                    $dbname = $_POST['db'];
                    $q->setDbName($dbname);
                    $passValue = true;
                }
                echo "<br />";
                echo "<br />";
                echo "Now we'll retrieve a list of all tables in the selected database:";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>\$tb = \$q->listTables();</div>";
                echo "<br />";
                echo "<br />";
                echo "Tables listed in database ".$q->getDbName().":";
                echo "<br />";
                echo "<br />";
                $tb = $q->listTables();
                echo "<ul class='list'>";
                foreach ($tb as $tbname){
                    echo "<li>table: $tbname</li>";
                }
                echo "</ul>";
                echo "<br />";
                echo "<br />";
                echo "Click backup button to proceed saving selected database, please;";
                echo "<br />";
                echo "<br />";
                echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='" . ($pageID+1) . "' />";
                if($passValue){
                    echo "<input type='hidden' id='dbname' name='dbname' value='". $q->getDbName() ."' />";
                }
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<input type='submit' id='submit' name='submit' value='Backup!' />";
                echo "</form>";
                break;
            case 6:
                $q = unserialize($_SESSION['conn']);
                echo "<br />";
                echo "<br />";
                if ($q->getDbName() == ''){
                    $q->setDbName($_POST['dbname']);
                }
                echo "To backup your database simply add this line of code:<br />";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>\$q->doBackup();</div>";
                echo "<br />";
                echo "<br />";
                $q->doBackup();
//                You can get the same result of showDump() function using file_get_contents() function this way:
//                echo "<div id='dumpreader'>".file_get_contents($q->getLastDump())."</div><br /><br />";
                echo "<div id='dumpreader'>";
                foreach($q->showDump() as $dump){
                    echo $dump."<br />";
                }
                echo "</div><br /><br />";
                echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='1' />";
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<input type='submit' id='submit' name='submit' value='End' />";
                echo "</form>";
                break;
            case 7:
                $df = $_POST['dump'];
                $q = unserialize($_SESSION['conn']);
                $q->setDumpFile($df);
                echo "<br />";
                if ($tb = $q->listTables()){
                    echo "Tables listed in database ".$q->getDbName().":";
                    echo "<br />";
                    echo "<br />";
//                    echo "count is ".count($tb);
                    echo "<br />";
//                    echo "table is $tb[0]";
                    echo "<ul class='list'>";
                    foreach ($tb as $tbname){
                        echo "<li>table: $tbname</li>";
                    }
                    echo "</ul>";
                }else{
                    echo "Database is empty.";
                }
                echo "<br />";
                echo "<br />";
                echo "Now you have choosen a database to restore and you can write:";
                echo "<br />";
                echo "<br />";
                echo "<div class='code'>if (\$q->doRestore()){<br />echo \"Database was successfully restored.\";<br />}</div>";
                echo "<br />";
                echo "<br />";
                echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='" . ($pageID+1) . "' />";
                echo "<input type='hidden' id='dump' name='dump' value='$df' />";
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<input type='submit' id='submit' name='submit' value='Restore' />";
                echo "</form>";
                break;
            case 8:
                $df = $_POST['dump'];
                $q = unserialize($_SESSION['conn']);
                $q->setDumpFile($df);
                if ($q->doRestore()){
                    echo "Database was successfully restored.";
                }
                echo "<br />";
                echo "<br />";

                echo "<form id='' action='" . $_SERVER['PHP_SELF'] . "' method='post'>";
                echo "<input type='hidden' id='pageID' name='pageID' value='1' />";
                unset($_POST['submit']);
                unset($_POST['pageID']);
                echo "<input type='submit' id='submit' name='submit' value='End' />";
                echo "</form>";
                break;
        }
        ?>
        </div>
        </div>
    </body>
</html>

Open in new window


Cheers
<?php
if (!class_exists('MySqlDumper', false)){
    /**
     * MySqlDumper
     *
     * <p>This is a PHP class which allow you to programmatically create
     * mysql database's dump files and use these dump files to restore a database
     * to its prvious state</p>
     * <p>MySqlDumper is free of charge and you can freely use and distribute it.</p>
     * <p>Use it at your own risk: I'm not responsable for any damage derving from 
     * use of MySqlDumper</p>
     * @name MySqlDumper
     * @package MySqlDumper
     * @version 0.1.0
     * @author Marco Gasi - info@em-web.it
     * @link http://www.em-web.it
     */

    class MysqlDumper {
    /**
     *
     * <p>MySqlDumper class</p>
     * <p>MySqlDumper is a simple class which allow you to programmatically create
     * mysql database's dump files and use these dump files to restore a database
     * to its prvious state</p>
     *
     */

        private $dbConn;
        private $hostname;
        private $username;
        private $password;
        private $dbName;
        private $destFile;
        private $destFolder = "dumps";
        private $dumpFile;
        private $errorMsg = array();

        /**
         * Description and usage
         *
         * Public function dbConnect
         * @return void
         * @param  string $val
         *
         * <p>It establishes a connection to database. If a connection exists, it close
         * it and then open a new connection: this allows user to choose a new database
         * and establish a new connection to it without destroy object which holds connection.</p>
         * <p>This function return true if connection was successfully established, otherwise
         * it returns false and shows error message.</p>
         */
        private function _dbConnect() {
            $this->errorMsg = array();
            if ($this->_checkDbConn()) $this->dbConn = null;
            if (empty($this->dbName)){
                $conn = @mysql_connect($this->hostname, $this->username, $this->password);
                if(!$conn) $this->errorMsg[] = "<b>function _dbConnect - </b>MySqlDumper cannot establish a connection with following message " . mysql_error();
                $this->dbConn = $conn;
            }else{
                $conn = @mysql_connect($this->hostname, $this->username, $this->password);
                if(!$conn) $this->errorMsg[] = "<b>function _dbConnect - </b>MySqlDumper cannot establish a connection with following message " . mysql_error();
                $this->dbConn = $conn;
                $selDb = @mysql_select_db($this->dbName, $conn);
                if(!$selDb) $errorMsg[] = "Database not found: " . mysql_error();
            }
            if (empty($this->errorMsg)){
                $this->_setDestFile();
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }

        /**
         * Private function _checkDbConn
         * @param void
         * @return boolean
         * check if a database connection is active
         */
        private function _checkDbConn(){
            if (!isset($this->dbConn)){
                return false;
            }else{
                return true;
            }
        }

        /**
         * Private function getErrors
         * @param void
         * @return error's list
         */

        private function getErrors(){
            if(!empty($this->errorMsg)){
                ob_end_clean();
                echo "Following errors was found:<br />";
                foreach ($this->errorMsg as $e){
                    echo "$e<br />";
                }
            }
            die();
        }

        /**
         * Description and usage
         *
         * <p>Here they are set deafult values for new instances.</p>
         * <p>$hostname, $username and $password are mandatory,
         * $dbName is optional. In fact you can create a new instance
         * of MySqlDumper without specify a database and then retrieve a
         * list of all databases hosted in yuour server to choose among them
         * hich database use for backup procedures.</p>
         *
         * <p>You create a new insatance of MySqlDumper this way:</p>
         * <code>$dumper = new MySqlDumper('host_name', 'user_name', 'password', ['database_name'])</code>
         * <br>
         * <p>After having set parameters for a connection, MySqlDumer calls
         * private function _dbConnect() to establish a connection to your
         * database server.</p>
         * @param <string> $hostname
         * @param <string> $username
         * @param <string> $password
         * @param <string> $dbName
         */

        public function __construct($hostname, $username, $password, $dbName="") {
            error_reporting(E_NONE);
            ob_start();
            set_exception_handler('getErrors');
            $this->hostname = $hostname;
            $this->username = $username;
            $this->password = $password;
            if ($dbName != "") $this->dbName = strtolower($dbName);
            $this->_dbConnect();
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory: it closes database connection</p>
         */
        public function  __destruct() {
            mysql_close($this->dbConn);
        }

        /**
         * Description and usage
         *
         * <p>It returns the database name selected for backup or restore.
         * If a database was not selected yet, it returns an empty string.</p>
         * <p>Usage:</p>
         * <code>$dbname = $dumper->getDbName();</code>
         * <br>
         * @param void
         * return string
         */
        public function getDbName(){
            if (!empty($this->dbName)){
                return $this->dbName;
            }
            if(empty($this->dbName) && !empty($this->dumpFile)){
                $dbName = preg_replace("/-(\d){8}-(\d){6}/", "", $this->dumpFile);
                $this->dbName = substr($dbName, 5, strlen($dbName)-9);
                return $this->dbName;
            }else{
                return "";
            }
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->getHostName();</code>
         * <br>
         * @return <string>
         */
        public function getHostName(){
            return $this->hostname;
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->getUserName();</code>
         * <br>
         * @return <string>
         */
        public function getUserName(){
            return $this->username;
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->getPassword();</code>
         * <br>
         * @return <string>
         */
        public function getPassword(){
            return $this->password;
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumper->setDbName('databasew_name');</code>
         * <br>
         * <p>Useful if you don't select a database when you instantiate MySqlDumper.</p>
         *
         * @param string
         * return void
         */
        public function setDbName($dbName){
            if($this->dbName == ''){
                $this->dbName = strtolower($dbName);
                $this->_dbConnect();
            }
        }

        /**
         * Private function _setDestFile
         * @param void
         * @return void
         * set the name of dump file
         */
        private function _setDestFile(){
            $now = getdate();
            $now = date("-Ymd-His");
//            $this->destFile = "dumps/dump_" . $this->dbName . "-" . $now["year"] . $now["mon"] . $now["mday"] . "-" .$now["hours"] . $now["minutes"] . $now["seconds"] . ".sql";
            $this->destFile = "dumps/dump_" . $this->dbName . $now . ".sql";
        }

        /**
         * Description and usage
         *
         * <p>When you wish restore a database, you can use this function to choose
         * a dump file to use.</p>
         * <p>If a database was not selected when you created
         * MySqlDumper instance, it will be set here</p>
         * <p>If choosen dump file doesn't fit database you're connected to,
         * you'll be noticed with an error message.</p>
         * <p>Otherwise, the function return true.</p>
         * <p>Usage:</p>
         * <code>$dumper->setDumpFile('dumFile_name');</code>
         * <br>
         * @param <string> $value
         * @return boolean
         */
        public function setDumpFile($value){
            $this->dumpFile = $value;
            $dbName = preg_replace("/-(\d){8}-(\d){6}/", "", $this->dumpFile);
            $dbName = substr($dbName, 5, strlen($dbName)-9);
            if ($this->dbName == ''){
                $this->setDbName($dbName);
            }
            if ($this->dbName != $dbName){
                $this->errorMsg[] = "<b>Function setDumpFile()</b>: You want to restore $dbName database
                        but you're already connected to $this->dbName database. Please, choose a
                        different dump file or establish a new database connection.";
            }
            if (empty($this->errorMsg)){
                $this->_setDestFile();
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$dumpfile = $dumper->getDumpFile();</code>
         * <br>
         * @param void
         * #return string
         */
        public function getDumpFile(){
            return $this->dumpFile;
        }

        /**
         * Description and usage
         *
         * <p>This function will be used internally to serialize MySqlDumper
         * instance and store it in a $_SESSION variable</p>
         * @return <array>
         */
        public function __sleep()
        {
            return array('hostname', 'username', 'password', 'dbName');
        }

        /**
         * Description and usage
         *
         * <p>This function will be used internally to unserialize MySqlDumper
         * instance and establish a connection to database</p>
         * return void
         */
        public function __wakeup()
        {
            $this->_dbConnect();
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>$lastDumpfile = $dumper->getLastDump();</code>
         * <br>
         * @param void
         * #return string
         */

        public function getLastDump(){
            return $this->destFile;
        }

        /**
         * private function getAllValues
         * @param string $sql
         * @param boolean $show_error
         * @return array
         */

        private function getAllValues($sql){
            if (!isset($sql) || $sql == ''){
                echo "Can't execute the query: please, verify it.";
                return false;
            }
            $error = "";
            $result = mysql_query($sql) or $error = mysql_error();
            if ($error == ""){
                $resultSet = array();
                $keys = array();
                $values = array();
                while ($row = mysql_fetch_assoc($result)){
                    foreach ($row as $k=>$v){
                        $keys[] = $k;
                        $values[] = $v;
                    }
                    for ($x=0;$x<count($keys);$x++){
                        $partialResult[$keys[$x]] = $values[$x];
                    }
                    $resultSet[] = $partialResult;
                }
                return $resultSet;
            }else{
                $this->errorMsg[] = "<b>Function getAllValues:</b> Error occurred executing query $sql: ". $error;
                $this->getErrors();
            }
        }


        /**
         * Description and usage
         *
         * <p>It analyzes selected database and creates a dump file accordingly,
         * storing it on the hard drive.</p>
         * <p>On failure, it returns one or more error messages.</p>
         * <p>Usage:</p>
         * <code>$dumper->doBackup();</code>
         * <br>
         * @param  void
         * @return boolean
         */

        public function doBackup() {
            $this->errorMsg = array();
            if (!isset($this->dbConn)) {
                $this->errorMsg[] = "<b>Function doBackup - </b>No valid connection to a database was found.";
            }
            if (empty($this->dbName)) {
                $this->errorMsg[] = "<b>Function doBackup - </b>No database selected to backup.";
            }
            if (!$fp = fopen($this->destFile, "w")) {
                $this->errorMsg[] = "<b>Function doBackup - </b>Error creating backup file.";
            }
            fwrite($fp, "CREATE DATABASE IF NOT EXISTS $this->dbName;\n");
            $tables = $this->listTables();
            if (count($tables) == 0){
                $this->errorMsg[] = "<b>Function doBackup - </b>No tables found.";
            }else{
                foreach ($tables as $t) {
                    fwrite($fp, "DROP TABLE IF EXISTS `$t`;\n");
                    $sql = "SHOW CREATE TABLE `$t`";
                    $res = @mysql_query($sql);
                    if (!$res){
                        $this->errorMsg[] = "<b>Function doBackup - </b>Query cannot be executed with following message: ".mysql_error();
                    }else{
                        $row = @mysql_fetch_row($res);
                        $create = $row[1].";";
                        $create = preg_replace("%\n%", " ", $create);
                        fwrite($fp, $create . "\n");
                    }
                    $sql = "SELECT * FROM `$t`";
                    $data = $this->getAllValues($sql);
                    if (count($data)>0){
                        $sql = "SHOW COLUMNS FROM `$t`";
                        $fields = $this->getAllValues($sql);
                        if (count($fields) == 0){
                            $this->errorMsg[] = "<b>Function doBackup - </b>Query cannot be executed with following message: ".mysql_error();
                        }else{
                            $insert = "INSERT INTO `$t` (";
                            foreach ($fields as $key => $value) {
                                $insert .= $value['Field'] . ", ";
                            }
                            $insert = substr($insert, 0, -2);
                            $insert .= ") VALUES";
                        }
                        $num = count($data);
                        foreach ($data as $d) {
                            $insert .= "(";
                            $i = 0;
                            foreach ($d as $s) {
                                $insert .= "'" . addslashes($s) . "', ";
                            }
                            $insert = substr($insert, 0, -2);
                            $insert .= "),";
                        }
                        $insert = substr($insert, 0, -1);
                        fwrite($fp, $insert . ";");
                        fwrite($fp, "\n");
                    }
                }
                fclose($fp);
            }
            if (empty($this->errorMsg)){
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }

        /**
         * Description and usage
         *
         * <p>It analyzes selected dump file and executes queries to,
         * restore database on its previous state.</p>
         * <p>On failure, it returns one or more error messages.</p>
         * <p>Usage:</p>
         * <code>$dumper->doRestore();</code>
         * <br>
         * @param  void
         * @return boolean
         */
        public function doRestore() {
            $this->errorMsg = array();
            if (!isset($this->dbConn)) {
                $this->errorMsg[] = "<b>Function doRestore - </b>No valid connection to a database was found.";
            }
            if (empty($this->dbName) && empty($this->dumpFile)) {
                $this->errorMsg[] = "<b>Function doRestore - </b>No database selected to restore.";
            }elseif (empty($this->dbName)) {
                $dbName = preg_replace("/-(\d){8}-(\d){6}/", "", $this->dumpFile);
                $this->dbName = substr($dbName, 5, strlen($dbName)-9);
            }
            $fname = $this->getDumpFile();
            if (!$dumps = $this->listDumpFiles()){
                $this->getErrors();
            }else{
                if (in_array($fname, $dumps)){
                    $fname = $this->destFolder."\\".$fname;
                    $queries = file($fname);
                    foreach ($queries as $q){
                        $res = mysql_query($q);
                        if (!$res){
                            $this->errorMsg[] = "<b>Function doRestore - </b>Query cannot be executed with following message: ".mysql_error();
                        }
                    }
                }else{
                    $this->errorMsg[] = "<b>Function doRestore - </b>cacca No database selected to restore.";
                }
            }
            if (empty($this->errorMsg)){
                return true;
            }else{
                $this->getErrors();
                return false;
            }
        }


        /**
         * Description and usage
         *
         * <p>It retrieves a list of databases hosted in server you're connected
         * to and it allows to select a database to backup.</p>
         * <p>Usage:</p>
         * <code>
         * $databases = $dumper->listDatabases();<br><foreach ($databases as $db){
         *      echo "<input type='radio' name='db' value='$db'>$db<br/>";
         * }</code>
         * <br>
         * <p>On failure it returns one or more error messages.</p>
         * @param <$caseSensitive> boolean default true
         * @param <$silent> boolean default false
         * @return array
         */
        public function listDatabases($caseSensitive=true, $silent=false) {
            $this->errorMsg = array();
            if(!$this->_checkDbConn()){
                $this->errorMsg[] = "<b>Function listDatabases - </b>No connection was found.";
                $this->getErrors();
            }else{
                $databases = array();
                $sql = "SHOW DATABASES";
                $res = @mysql_query($sql);
                if (!$res){
                    $this->errorMsg[] = "<b>Function listDatabases - </b>Query cannot be executed with following message: ".$res->errorInfo();
                }
                while ($row = mysql_fetch_array($res)) {
                    if ($caseSensitive){
                        $databases[] = $row[0];
                    }else{
                        $databases[] = strtolower($row[0]);
                    }
                }
            }
            if (count($databases)>0){
                return $databases;
            }else{
                if ($silent) $this->getErrors();
                return false;
            }
        }

        private function showAlert($msg){
            echo "<script type=\"text/javascript\">alert('$msg');</script>";
        }

        /**
         * Description and usage
         *
         * <p>It retrieves a list of tables contained in database you're connected to.</p>
         * <p>Usage:</p>
         * <code>
         * $tables = $dumper->listTablses();
         * echo "<ul>";
         * <foreach ($tables as $tb){
         *      echo "<li>table: $tb</li>";
         * }
         * echo "</ul>";
         * </code>
         * <br>
         * <p>On failure it returns one or more error messages.</p>
         * @param <$silent> boolean default false
         * @return array
         */
        public function listTables() {
            $this->errorMsg = array();
            if (empty($this->dbName)){
                $this->errorMsg[] = "<b>Function listTables - </b>No database was selected.";
                $this->getErrors();
            }else{
                $tables = array();
                $sql = "SHOW TABLES";
                $res = @mysql_query($sql);
                if (!$res){
                    $this->errorMsg[] = "<b>Function listTables - </b>Query cannot be executed with following message: ".mysql_error();
                    $this->getErrors();
                }else{
                    while ($row = mysql_fetch_array($res)) {
                        $tables[] = $row[0];
                    }
                }
            }
            if (empty($this->errorMsg)){
                if (count($tables)>0){
                    return $tables;
                }else{
                    return false;
                }
            }else{
                $this->getError();
            }
        }

        /**
         * Description and usage
         *
         * <p>It retrieves a list of tables contained in database you're connected to.</p>
         * <p>Usage:</p>
         * <code>
         * $dumps = $q->listDumpFiles();
         * echo "<ul>";
         * foreach ($dumps as $dump){
         *      echo "<input type='radio' name='dump' value='$dump'>$dump<br/>\n";
         * }
         * echo "</ul>";
         * </code>
         * <br>
         * <p>On failure it returns one or more error messages.</p>
         * @param <$silent> boolean default false
         * @return array
         */
        public function listDumpFiles($silent=false){
            $this->errorMsg = array();
            if (!$dumpDir = scandir($this->destFolder,1)) {
                //$this->errorMsg[] = "<b>Function listDumpFiles - </b>Dumps directory was not found on this server.";
                //$this->getErrors();
            }else{
                $found = false;
                foreach ($dumpDir as $dump){
                    if (!preg_match('/^dump_/', $dump)) continue;
                    $dumpFiles[] = $dump;
                    $found = true;
                }
                if (!$found){
                    //$this->errorMsg[] = "<b>Function listDumpFiles - </b>No dump file was found in on this server.";
                }
            }
            if (count($dumpFiles)>0){
                return $dumpFiles;
            }else{
                if ($silent) $this->getErrors();
                return false;
            }
        }

        /**
         * Description and usage
         *
         * <p>Self-explanatory</p>
         * <p>Usage:</p>
         * <code>
         * foreach($q->showDump() as $dump){
         *      echo $dump."<br />";
         * }
         * </code>
         * <br>
         * @param void
         * #return string
         */
        public function showDump() {
            return file($this->destFile);
        }

    }
}
?>

Open in new window

0
 
Ray PaseurCommented:
I've used this...

<?php // RAY_mysql_backup_dump.php
error_reporting(E_ALL);
date_default_timezone_set("America/Chicago");


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB LIST: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// ITERATE OVER THE LIST OF NAMES TO MAKE AN ARRAY
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}


// ANYTHING IN POST DATA YET?
if (empty($_POST["d"]))
{
    // NOTHING POSTED - ASK CLIENT TO CHOOSE THE DATA BASE
    echo "<form method=\"post\">\n";
    echo "BACK UP A DATA BASE:<br/>";
    foreach ($db_names as $db_name)
    {
        echo "<input type=\"radio\" name=\"d\" value=\"$db_name\">$db_name <br/>\n";
    }
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $db_names)) die("ERROR: DATABASE {$_POST["d"]} NOT FOUND");
$db_name = $_POST["d"];


// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}


// SET THE NAME OF THE BACKUP WITH A TIMESTAMP
$bkup = 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
$fp   = fopen($bkup, "w");


// GET THE LIST OF TABLES
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
if (mysql_num_rows($res) == 0) die( "NO TABLES IN $db_name" );
while ($s = mysql_fetch_array($res))
{
    $tables[] = $s[0];
}


// ITERATE OVER THE LIST OF TABLES
foreach ($tables as $table)
{

    // WRITE THE DROP TABLE STATEMENT
    fwrite($fp,"DROP TABLE `$table`;\n");

    // GET THE CREATE TABLE STATEMENT
    $res = mysql_query("SHOW CREATE TABLE `$table`");
    if (!$res) die( mysql_error() );
    $cre = mysql_fetch_array($res);
    $cre[1] .= ";";
    $txt = str_replace("\n", "", $cre[1]); // FIT EACH QUERY ON ONE LINE
    fwrite($fp, $txt . "\n");

    // GET THE TABLE DATA
    $data = mysql_query("SELECT * FROM `$table`");
    $num  = mysql_num_fields($data);
    while ($row = mysql_fetch_array($data))
    {
        // MAKE INSERT STATEMENTS FOR ALL THE VALUES
        $txt = "INSERT INTO `$table` VALUES(";
        for ($i=0; $i < $num; $i++)
        {
            $txt .= "'".mysql_real_escape_string($row[$i])."', ";
        }
        $txt = substr($txt, 0, -2);
        fwrite($fp, $txt . ");\n");
    }
}

// ALL DONE
fclose($fp);

// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>BACKUP OF $db_name CREATED HERE:\n";
echo "<br/><a href=\"$bkup\">$bkup</a>\n";

Open in new window

And this:

<?php // RAY_mysql_backup_restore.php
error_reporting(E_ALL);


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'RAY_backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// FIND THE BACKUP FILES
if (!$my_dir = scandir('backups',1)) die('NO BACKUP DIRECTORY FOUND');

// IF NOTHING POSTED YET
if (empty($_POST))
{

// ASK CLIENT TO CHOOSE ONE FOR RESTORE
    $x = FALSE;
    echo "<form method=\"post\">\n";
    echo "RESTORE A DATA BASE:<br/>";
    foreach ($my_dir as $my_file)
    {
        if (!ereg('^mysql', $my_file)) continue;
        echo "<input type=\"radio\" name=\"d\" value=\"$my_file\">$my_file <br/>\n";
        $x = TRUE;
    }
    if (!$x) die('NO BACKUP FILES FOUND');
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $my_dir)) die("ERROR: FILE {$_POST["d"]} NOT FOUND");

// THE NAME OF THE BACKUP FILE
$fname = $_POST["d"];

// GET THE NAME OF THE DB FROM THE BACKUP FILE NAME
$db_name = $fname;
$db_name = ereg_replace("^mysql", '', $db_name);
$db_name = ereg_replace("\.txt$",         '', $db_name);
$db_name = substr($db_name, 15); // LENGTH 15 = strlen("date('Ymd\THis')")


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// LOAD THE BACKUP DATA QUERIES
$sqls  = file('RAY_backups/' . $fname);

// ITERATE OVER THE QUERIES TO RELOAD THE DATA
foreach ($sqls as $sql)
{
    if (!$res = mysql_query($sql))
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }
}

Open in new window

And for a single table backup on the same data base...

<?php // RAY_mysql_backup_table.php
error_reporting(E_ALL);
echo "<pre>\n";



// A FORM-ACTIVATED TABLE BACKUP
// RESPONSE TO EE QUESTION http://www.experts-exchange.com/Database/MySQL/Q_25465037.html?cid=1749



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// IF THE CLIENT HAS CHOSEN A TABLE FROM OUR FORM
if (!empty($_POST["dbt"]))
{
    // CLEAN NAMES FOR THE TABLES
    $dbt      = mysql_real_escape_string($_POST["dbt"]);
    $dbt_bkup = $dbt . 'BCK';

    // REMOVE ANY OLD BACKUP DATA (AND IGNORE QUERY ERRORS THAT MAY OCCUR)
    $sql = "DROP TABLE $dbt_bkup";
    var_dump($sql);
    mysql_query($sql);

    // CREATE A NEW BACKUP TABLE WITH THE STRUCTURE OF THE ORIGINAL TABLE
    $sql = "CREATE TABLE $dbt_bkup LIKE $dbt";
    var_dump($sql);
    mysql_query($sql) or die( mysql_error() );

    // POPULATE THE NEW BACKUP TABLE WITH THE DATA FROM THE ORIGINAL TABLE
    $sql = "INSERT INTO $dbt_bkup SELECT * FROM $dbt";
    var_dump($sql);
    mysql_query($sql) or die( mysql_error() );

    // ALL DONE
    echo "$dbt HAS BEEN BACKED UP IN $dbt_bkup" . PHP_EOL;
}



// GIVE THE CLIENT A FORM TO CHOOSE THE TABLE TO BACKUP
if (empty($_POST["dbt"]))
{
    // RUN A QUERY TO GET A LIST OF TABLES ON THIS DB
	$t = mysql_query("SHOW TABLES") or die( mysql_error() );

	// IF NO TABLES
	if (mysql_num_rows($t) == 0)
	{
		die('NO TABLES');
	}

	// GET AN ARRAY OF TABLES WITH THE TABLE NAME AS THE INDEX
	while ($show_tables	= mysql_fetch_array($t))
	{
		$my_tables[] = $show_tables[0];
	}

	// SORT THE NAMES
    natcasesort($my_tables);

    // PRODUCE THE FORM TO CHOOSE THE TABLE
    echo "<form method=\"post\">" . PHP_EOL;
    echo "CHOOSE TABLE TO BACKUP" . PHP_EOL;
    foreach ($my_tables as $my_table)
    {
        // GET THE NUMBER OF ROWS FOR THIS TABLE
        $t = mysql_query("SELECT COUNT(*) AS n FROM $my_table") or die( mysql_error() );
        $r = mysql_fetch_assoc($t);
        $n = $r["n"];

        // PRODUCE A RADIO BUTTON FOR THE CLIENT TO CHOOSE A TABLE
        echo "<input type=\"radio\" name=\"dbt\" value=\"$my_table\" />$my_table ($n ROWS)" . PHP_EOL;
    }

    // END OF SELECTION FORM
    echo "<input type=\"submit\" value=\"BACKUP NOW\" />" . PHP_EOL;
    echo "</form>" . PHP_EOL;
}

Open in new window

HTH, ~Ray
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now