Treehouse: Grow your CSS skills. Land your dream job.

Last updated on:

MySQL Backup Class

<?php

define('MSB_VERSION', '1.0.0');

define('MSB_NL', "\r\n");

define('MSB_STRING', 0);
define('MSB_DOWNLOAD', 1);
define('MSB_SAVE', 2);
define('__SEP__', "/*sep*/" );

set_time_limit( 600 );

class MySQL_Backup {
   var $server = 'localhost';
   var $port = 3306;
       var $username = 'root';
       var $password = '';
       var $database = '';
       var $link_id = -1;
       var $connected = false;
       var $tables = array();
       var $drop_tables = true;
       var $struct_only = false;
       var $comments = true;
       var $backup_dir = '';
       var $fname_format = 'd_m_y__H_i_s';
       var $error = '';

       var $complete_inserts  = false;
       var $inserts_block     = 200;

   function Execute($task = MSB_STRING, $fname = '', $compress = false) {
       if ( !( $sql = $this->_Retrieve() ) ) {
           return false;
       }
       if ( $task == MSB_SAVE ) {
           if (empty($fname)) {
               $fname = $this->backup_dir;
               $fname .= date($this->fname_format);
               $fname .= ($compress ? '.sql.gz' : '.sql');
           }
           return $this->_SaveToFile($fname, $sql, $compress);
       } elseif ($task == MSB_DOWNLOAD) {
           if ( empty( $fname ) ) {
               $fname = date($this->fname_format);
               $fname .= ($compress ? '.sql.gz' : '.sql');
           }
           return $this->_DownloadFile($fname, $sql, $compress);
       } else {
           return $sql;
       }
   }

   function _Connect() {
       $value = false;
       if (!$this->connected) {
           $host = $this->server . ':' . $this->port;
           $this->link_id = mysql_connect($host, $this->username, $this->password);
       }
       if ($this->link_id) {
           if (empty($this->database)) {
               $value = true;
           } elseif ($this->link_id !== -1) {
               $value = mysql_select_db($this->database, $this->link_id);
           } else {
               $value = mysql_select_db($this->database);
           }
       }
       if (!$value) {
           $this->error = mysql_error();
       }
       return $value;
   }


   function _Query($sql) {
       if ($this->link_id !== -1) {
           $result = mysql_query($sql, $this->link_id);
       } else {
           $result = mysql_query($sql);
       }
       if (!$result) {
           $this->error = mysql_error();
       }
       return $result;
   }


   function _GetTables() {
       $value = array();
       if ( !( $result = $this->_Query('SHOW TABLES') ) ) {
           return false;
       }
       while ( $row = mysql_fetch_row( $result ) ) {
           if ( empty( $this->tables) || in_array( $row[0], $this->tables ) ) {
               $value[] = $row[0];
           }
       }
       if (!sizeof($value)) {
           $this->error = 'No tables found in database.';
           return false;
       }
       return $value;
   }


   function _DumpTable( $table ) {
       $value = '';
       $this->_Query( 'LOCK TABLES ' . $table . ' WRITE' );
       if ( $this->comments ) {
           $value .= '#' . MSB_NL;
                   $value .= '# Table structure for table `' . $table . '`' . MSB_NL;
                   $value .= '#' . MSB_NL . MSB_NL;
       }
       if ( $this->drop_tables ) {
           $value .= 'DROP TABLE IF EXISTS `' . $table . '`;' . __SEP__ . MSB_NL;
       }
       if ( !( $result = $this->_Query('SHOW CREATE TABLE ' . $table) ) ) {
           return false;
       }
       $row = mysql_fetch_assoc($result);
       $value .= str_replace("\n", MSB_NL, $row['Create Table']) . ';' . __SEP__;
       $value .= MSB_NL . MSB_NL;
       if (!$this->struct_only) {
               if ($this->comments) {
                   $value .= '#' . MSB_NL;
                   $value .= '# Dumping data for table `' . $table . '`' . MSB_NL;
                   $value .= '#' . MSB_NL . MSB_NL;
               }
               $value .= $this->_GetInserts($table);
       }
       $value .= MSB_NL . MSB_NL;
       $this->_Query('UNLOCK TABLES');
       return $value;
   }

   function _GetInserts($table) {
       $value = '';
       if (!($result = $this->_Query('SELECT * FROM ' . $table))) {
           return false;
       }
       if ( $this->complete_inserts ) {
               while ($row = mysql_fetch_row($result)) {
                   $values = '';
                   foreach ($row as $data) {
                       $values .= '\'' . addslashes($data) . '\', ';
                   }
                   $values = substr($values, 0, -2);
                   $value .= 'INSERT INTO ' . $table . ' VALUES (' . $values . ');' . __SEP__ . MSB_NL;
               }
       } else {
               $blocks_counter = 0;
           $blocks = array();
           while ($row = mysql_fetch_row($result)) {
               $values = array();
               foreach ($row as $data) {
                   $values[] = '\'' . addslashes($data) . '\'';
               }
               $blocks[] = '(' . implode( ',', $values ) . ')';

               if ( $blocks_counter < $this->inserts_block ) {
                   $blocks_counter++;
               } else {
                       $value .= 'INSERT INTO ' . $table . ' VALUES ' . implode( ',', $blocks ) . ";" . __SEP__ . MSB_NL;
                       $blocks = array();
                       $blocks_counter = 0;
               }
           }
               if ( count( $blocks ) ) {
               $value .= 'INSERT INTO ' . $table . ' VALUES ' . implode( ',', $blocks ) . ";" . __SEP__ . MSB_NL;
               }
       }
       return $value;
   }


   function _Retrieve() {
       $value = '';
       if (!$this->_Connect()) {
           return false;
       }
       if ($this->comments) {
           $value .= '#' . MSB_NL;
           $value .= '# MySQL database dump' . MSB_NL;
           $value .= '# Created by MySQL_Backup class, ver. ' . MSB_VERSION . MSB_NL;
           $value .= '#' . MSB_NL;
           $value .= '# Host: ' . $this->server . MSB_NL;
           $value .= '# Generated: ' . date('M j, Y') . ' at ' . date('H:i') . MSB_NL;
           $value .= '# MySQL version: ' . mysql_get_server_info() . MSB_NL;
           $value .= '# PHP version: ' . phpversion() . MSB_NL;
           if (!empty($this->database)) {
               $value .= '#' . MSB_NL;
               $value .= '# Database: `' . $this->database . '`' . MSB_NL;
           }
           $value .= '#' . MSB_NL . MSB_NL . MSB_NL;
       }
       if (!($tables = $this->_GetTables())) {
           return false;
       }
       foreach ($tables as $table) {
           if (!($table_dump = $this->_DumpTable($table))) {
               $this->error = mysql_error();
               return false;
           }
           $value .= $table_dump;
       }
       return $value;
   }


   function _SaveToFile($fname, $sql, $compress) {
       if ($compress) {
           if (!($zf = gzopen($fname, 'w9'))) {
               $this->error = 'Can\'t create the output file.';
               return false;
           }
           gzwrite($zf, $sql);
           gzclose($zf);
       } else {
               if (!($f = fopen($fname, 'w'))) {
                   $this->error = 'Can\'t create the output file.';
                   return false;
               }
               fwrite($f, $sql);
               fclose($f);
       }
       return true;
   }

   function _DownloadFile($fname, $sql, $compress) {
       header('Content-disposition: filename=' . $fname);
       header('Content-type: application/octetstream');
       header('Pragma: no-cache');
       header('Expires: 0');
       echo ($compress ? gzencode($sql) : $sql);
       return true;
   }
}

?>

Comments

  1. Hitesh
    Permalink to comment#

    Please Add Some Description Here !!

  2. eulerss
    Permalink to comment#

    check this link, te original class

    http://www.phpclasses.org/browse/file/12128.html

  3. Just wanted to leave a link to a site with examples.

    http://www.phpclasses.org/package/1066-PHP-MySQL-backup-class.html

  4. where are the description ??

  5. Rob
    Permalink to comment#

    This is a terrible example. Nobody should use mysql_ functions. Let’s promote the use of PDO instead! (note: I do realize this was posted in 2009, but it should probably be deleted..)

Leave a Comment

Posting Code

We highly encourage you to post problematic HTML/CSS/JavaScript over on CodePen and include the link in your post. It's much easier to see, understand, and help with when you do that.

Markdown is supported, so you can write inline code like `<div>this</div>` or multiline blocks of code in in triple backtick fences like this:

```
<script>
  function example() {
    element.innerHTML = "<div>code</div>";
  }
</script>
```