MySQL Auto Backup Routine

Published: January 10th 2013

In the process of trying to partially automate a service from a web application I realised I really needed to create a routine that didn't rely on the mysql dump command from a shell. Having a scour around the Intenet I couldn't find anything that matched what I wanted to achieve so I started piecing together my own system.

The main focus was to be on something that was simple to call from either an install script or from inside a web application from a click event initiated by a user. I didn't want anything too fancy, and above all I wanted something that didn't require a shell_execute routine!

First of all I needed a simple DB construct class to begin the system with. Something that allowed me to just do the basics. If you have your own database handling system or use a framework such as Zend, CodeIgniter or Symfony you could just plug in the backup and import classes:

/**
* Database Construct and Manipulation class
* Includes Export and Import routines for your MySQL database system
* v 1.0.0
* @author Rick Trotter
*/
abstract class db {

  private static $boolConnected;

  function dbconnect($dbhost, $dbuser, $dbpass, $dbname){
    // Connect to the database
    if(!self::$boolConnected)
    {
    $conn= mysql_connect($dbhost, $dbuser, $dbpass);
    if($conn === FALSE)
    {
      die ("Cannot connect to MySql database control.");
    }
    else{
    $db = mysql_select_db($dbname, $conn);
    if($db === FALSE)
    {
    die ("Cannot connect to your database.");
    }
    }
      $boolConnected = true;
    }
  }

  // Function to run the SQL passed to you from the code
  public function execute($sql){

    // Run the requested SQL
    $result = mysql_query($sql);
    if($result)
    {
    return true;
    }
    else
    {
    return false;
    }
  }

  // Function to return a single row from SQL
  public function returnrow($sql){

    $sql .= " LIMIT 1";
    $result = mysql_query($sql);

    if($result)
    {
      return mysql_fetch_array($result);
    }
    else
    {
      return false;
    }
  }

  public function returnallrows($sql){
  // Get all rows from the database given the SQL from the application
    $result = mysql_query($sql);
    $resultset = array();

    while($arow = mysql_fetch_assoc($result)){
      $resultset[] = $arow;
    }

    return $resultset;

  }

  public function escapechars($var){
    // Escape any nasty code in the user input text
    return mysql_real_escape_string(trim($var));

  }

  public function getnumrows($sql){
    // Get the number of rows for a SQL query
    $result = mysql_query($sql);
    $numrows = mysql_numrows($result);
    return $numrows;
  }

  public function getlastid(){
    // Get the last inserted SQL ID
    $id = mysql_insert_id();
    return $id;
  }

  public function disconnect(){
    mysql_disconnect;
  }
}

Backing up the database

This gave me a starting point to start building the additional functions for the class

To back up the database we need to loop through all tables on the database and then grab the data to re-create the table information. I decided to ensure that we had full insert items for rebuilding later.

// Function to back up your database
// @param string $outputpath location where you want to
public function backupDatabase($outputpath)
{
  $outputpath = $this->escapechars($outputpath);

  //save file
  $path = $outputpath.'/backup-'.date('YmdHis').'.sql';
  $fp = fopen($path,'w');
  // populate a list of all the tables

  $tables = $this->returnallrows('SHOW TABLES');
  // iterate through each table
  foreach($tables as $table)
  {
    foreach($table as $item){
      $return = '-- DUMPING CONSTRUCT AND DATA FOR '.$item.';\n\n';

      $sql = "SHOW CREATE TABLE $item";
      $result = $this->returnallrows($sql);
      // Dump the generate SQL
      foreach($result as $entry){
      foreach($entry as $entryarray){
        $return .= $entryarray . ";\n\n";
      }
      }
      // Dump the data for the table
      $sql = "SELECT * FROM $item";
      $data = $this->returnallrows($sql);
      $fieldsql = 'SHOW COLUMNS FROM '.$item;
      $num_fields = $this->getnumrows($fieldsql);

      foreach($data as $output){
        $return .= "INSERT INTO $item VALUES(";
        $i = 1;
        foreach($output as $blob){

          $blob = addslashes($blob);
          $blob = ereg_replace("\n","\\n",$blob);

          $return .= "$blob";
          if($i < $num_fields){
            $return .= ",";
          }
          $i++;
        }
        $return .= ");\n\n";
      }
      fwrite($fp,$return);
      }
    }
  }

Importing your SQL backups

There's no point in generating a semi-automated (or fully automated) backup routine if you can't provide a similar service to re-import or rebuild from backups. Given this realisation, I built an additional function to pull data back into a system or create a new instance (for example for installation scripts).

// Function to load a SQL file into your database
// @param string $myfile location and filename of the SQL file to load
function loadSQL($myfile){
  // load file
  $myfile = $this->escapechars($myfile);

  $commands = file_get_contents($myfile);

  //delete comments
  $lines = explode("\n",$commands);
  $commands = '';
    foreach($lines as $line){
      $line = trim($line);
      if( $line && (substr($line,0,2) != '--') ){
        $commands .= $line . "\n";
      }
  }
  //convert to array
  $commands = explode(";", $commands);
  //run commands
  $total = $success = 0;
  foreach($commands as $command){
    if(trim($command)){
      $this->execute($command);
      $total++;
    }
  }

  return $total.' commands executed';
}

And that's about it, really!

It's not vastly optimised and I know there are improvements that can be made to the DB class for efficiency in memory handling for large tables. I need to work on that one at some point, perhaps it's something that you could fork on my Github repo.

Happy Hacking!
gingerCoder().

Next Item... Baking a ServerGrid Raspberry Pi