MySQL Administrationsklasse

MySQL administration class

I implemented this class for administrative MySQL tasks. It is based on MySql and provides additional functionalities like retrieving CREATE and DROP queries for existing tables or the database. These functions are used by to "main methods": backup() and restore(). backup() writes the whole database (or only a part of it) in a zip file, including the creation queries and a backup info file. restore() can read and interpret these files and regenerates the database structure including the contents. The table backup/restore works with direct file I/O of the MySQL server, so if you have a webspace that has a distributed system of web server and MySQL server, then the web server might not be able to access the generated files (as they are not stored in the local file system of the web server). Simply check it if it works (maybe I will have published a modified version of this class soon to address this possible issue).

Diese Klasse habe ich für administrative Aufgaben von MySQL implementiert. Sie basiert auf der Klasse MySql und bietet die Funktionen wie CREATE- oder DROP-Queries für existierende Tabellen und die Datenbank. Alle diese Methoden werden von zwei "Hauptmethoden" verwendet: backup() und restore(). backup() schreibt die gesamte Datenbank (oder optional Teile davon) zusammen mit einer Backup-Info-Datei in ein ZIP-Archiv. restore() kann den Inhalt dieses Archivs einlesen, interpretieren und daraus die Datenbank wiederherstellen. Die beiden Methoden verwenden die direkte Datei-Ein/Ausgabe von MySQL, daher kann es sein, dass auf verteilten Serversystemen (d.h. der Webserver und der MySQL-Server ist nicht derselbe) die Ausgabedateien der MySQL-Abfrage nicht vom Webserver gelesen werden können (weil sie nicht im lokalen Dateisystem gespeichert sind). Einfach mal ausprobieren. Ich denke mal, dass ich demnächst eine andere Lösung dafür habe und dann eine neue Version dieser Klasse hier veröffentliche.

Sample source code

Anwendungsbeispiel

$dba = new MySqlAdministration('test', 'root', '', 'localhost');
 
// Get the name of the database
$r = $dba->getDatabaseName();
print '$db->getDatabaseName() = ' . print_r($r, true) . '<br/>';
 
// This would be the query to delete the database
$r = $dba->getDatabaseDropQuery('test');
print '$db->getDatabaseDropQuery() = ' . print_r($r, true) . '<br/>';
 
// This would be the query to delete the table "test"
$r = $dba->getTableDropQuery('test');
print '$db->getTableDropQuery(...) = ' . print_r($r, true) . '<br/>';
 
// This would be the query to create the database
$r = $dba->getDatabaseCreationQuery();
print '$db->getDatabaseCreationQuery() = ' . print_r($r, true) . '<br/>';
 
// This would be the query to create all the tables (structure) as they are now
$r = $dba->getTableCreationQuery();
print '$db->getTableCreationQuery() = ' . print_r($r, true) . '<br/>';
 
// This would be the query to create the table 'test'
$r = $dba->getTableCreationQuery('test');
print '$db->getTableCreationQuery("test") = ' . print_r($r, true) . '<br/>';
 
// Lock the table, so that we can query multiple requests
$readlock = array('test');
$writelock = array('test2');
$dba->lock($readlock, $writelock);
// ... Here we do some stuff ...
// Unlock the table, this will be automatically done on destruction
$dba->unlock();
 
// Get table information (this is a method derived from MySql)
$r = $dba->getTableStatus('test');
print '$db->getTableStatus(...) = ' . print_r($r, true) . '<br/>';
 
 
// Make a backup of the database in a zip file
$tablePrefix = '';  // We don't have a table prefix here
$tables = null;     // null or array() means ALL tables
$comments = 'This is my backup BLA BLA ...'; // A comment for the info file
$dropTablesBeforeInsert = true; // We drop the tables before we recreate them
$dropAndRecreateDatabase = false; // We do not drop the whole database
 
$r = $dba->backup($tablePrefix, $tables, $comments, $dropTablesBeforeInsert, $dropAndRecreateDatabase);
print '$db->backup(...) = ' . print_r($r, true) . '<br/>';
 
// This is the file you can download then
$zipArchive = $r['zipfile'];
 
// Restore the database:
$tablePrefix = '';  // We don't change the table prefix, but we could
$r = $dba->restore($zipArchive, $tablePrefix);
print '$db->restore(...) = ' . print_r($r, true) . '<br/>';
 
// Delete the zip file ...
FileSystem::delete($zipArchive);

Output

Ausgabe

$db->getDatabaseName() = test
 
$db->getDatabaseDropQuery() = DROP DATABASE IF EXISTS `test`;
 
$db->getTableDropQuery(...) = DROP TABLE IF EXISTS `test`;
 
$db->getDatabaseCreationQuery() = CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
$db->getTableCreationQuery() = CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The primary key',
  `name` varchar(128) NOT NULL COMMENT 'Name of the person',
  `email` text NOT NULL COMMENT 'email address of the person',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='An example table for emails';
 
$db->getTableCreationQuery("test") = CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
 
$db->getTableStatus(...) = Array (
    {test} => Array (
        {name} => test
        {engine} => MyISAM
        {version} => 10
        {row_format} => Fixed
        {rows} => 0
        {avg_row_length} => 0
        {data_length} => 0
        {max_data_length} => 2533274790395903
        {index_length} => 1024
        {data_free} => 0
        {auto_increment} => 30
        {create_time} => 2010-08-05 16:34:55
        {update_time} => 2010-08-05 16:34:55
        {check_time} =>
        {collation} => utf8_general_ci
        {checksum} =>
        {create_options} =>
        {comment} =>
    )
    {test2} => Array (
        {name} => test2
        {engine} => MyISAM
        {version} => 10
        {row_format} => Dynamic
        {rows} => 0
        {avg_row_length} => 0
        {data_length} => 0
        {max_data_length} => 281474976710655
        {index_length} => 2048
        {data_free} => 0
        {auto_increment} => 1
        {create_time} => 2010-08-05 16:34:55
        {update_time} => 2010-08-05 16:34:55
        {check_time} => 2010-08-05 16:34:55
        {collation} => utf8_general_ci
        {checksum} =>
        {create_options} =>
        {comment} => An example table for emails
    )
)
 
$db->backup(...) = Array (
    {zipfile} => /Applications/XAMPP/xamppfiles/temp/2010-8-5-MySqlBackup.zip
    {backupinfo} => /mysqlbackup.test.1281022607.create.sql
    {comments} => This is my backup BLA BLA ...
    {time} => 2010-08-05 15:36:47 GMT
    {database} => test
    {prefix} =>
    {create} => /mysqlbackup.test.1281022607.create.sql
    {tables} => Array (
        {test} => Array (
            {name} => test
            {file} => /mysqlbackup.test.1281022607.test.tdata
        )
        {test2} => Array (
            {name} => test2
            {file} => /mysqlbackup.test.1281022607.test2.tdata
        )
    )
)
 
$db->restore(...) =

Class source code

Klassen-Quelltext

<?php
 
/**
 * MySQL backup / restore and administrative tools. Backups the whole database
 * (or only a part) into a ZIP file, including an info file. The restore function
 * accepts the ZIP file and is able to restore the database (with table creations).
 * @gpackage de.atwillys.sw.php.swLib
 * @author Stefan Wilhelm
 * @copyright Stefan Wilhelm, 2009-2010
 * @license GPL
 * @version 1.0
 * @uses Tracer
 * @uses MySql
 * @uses MySqlException
 * @uses FileSystem
 * @uses ZipFile
 */
 
namespace sw;
 
class MySqlAdministration extends MySql {
 
  /**
   * MySQL wrapper class constructor
   * @param string $database
   * @param string $user
   * @param string $password
   * @param string $server
   */
  public final function __construct($database='', $user='', $password='', $server='') {
    parent::__construct($database, $user, $password, $server);
  }
 
  /**
   * Locks the specified tables (array with the table names)
   * in the database. If both read and write lock is not specified,
   * all tables in the database will be write locked.
   * CAUTION: USE unlock() method when you leave the critical section.
   * @param array $readLockTables
   * @param array $writeLockTables
   */
  public final function lock($readLockTables=null, $writeLockTables=null) {
    if (empty($readLockTables) && empty($readLockTables)) {
      // No parameters: write lock all
      $writeLockTables = $this->getTableNames();
    } else if ((!is_array($readLockTables) && !empty($readLockTables) ) || (!is_array($writeLockTables)) && !empty($writeLockTables)) {
      // No array and not null, array(), false: invalid.
      throw new MySqlException('The arguments for the tables to lock is incorrect.');
    }
 
    // conditionize
    if (!empty($readLockTables)) {
      foreach ($readLockTables as $key => $table) {
        $readLockTables[$key] = !in_array($table, $writeLockTables) ? self::escape(trim($table)) : false;
      }
      $readLockTables = array_filter($readLockTables);
      $readLockTables = !empty($readLockTables) ? (implode(' READ,', $readLockTables) . ' READ') : '';
    } else {
      $readLockTables = '';
    }
    if (!empty($writeLockTables)) {
      foreach ($writeLockTables as $key => $table) {
        $writeLockTables[$key] = self::escape(trim($table));
      }
      $writeLockTables = '' . implode(' WRITE,', $writeLockTables) . ' WRITE';
    } else {
      $writeLockTables = '';
    }
 
    self::trace("Lock tables");
    $this->query('LOCK TABLES ' . $readLockTables . (($readLockTables != '' && $writeLockTables != '') ? ',' : '') . $writeLockTables);
  }
 
  /**
   * Unlocks all table locks
   */
  public final function unlock() {
    self::trace('Unlock all tables');
    try {
      $this->query("UNLOCK TABLES");
    } catch (MySqlException $e) {
      self::trace('Falied to unlock tables');
    }
  }
 
  /**
   * Returns the SQL query for creating the database in the same
   * manner as the actual one is. The query always includes an
   * "IF NOT EXISTS" clause.
   * @return string
   */
  public final function getDatabaseCreationQuery() {
    $r = reset($this->query("SHOW CREATE DATABASE `" . $this->getDatabaseName() . "`"));
    $r = array_change_key_case($r, CASE_LOWER);
    $r = explode("\n", $r['create database'] . ';', 2);
    $r[0] = str_ireplace('CREATE DATABASE', 'CREATE DATABASE IF NOT EXISTS', $r[0]);
    $r = implode("\n", $r);
    return $r;
  }
 
  /**
   * Returns the SQL query for creating a table that is identical
   * to the existing table. If no table is specified, the method
   * generates a creation query for alltables in the database.
   * @param string $table
   * @param bool $ifNotExists
   * @return string
   */
  public final function getTableCreationQuery($table=null, $ifNotExists=true) {
    if (is_null($table)) {
      $tables = $this->getTableNames();
    } else {
      $tables = array(self::escape($table));
    }
 
    $queries = array();
    foreach ($tables as $table) {
      $r = reset($this->query("SHOW CREATE TABLE `" . $table . "`"));
      $r = array_change_key_case($r, CASE_LOWER);
      $r = $r['create table'] . ';';
      if ($ifNotExists) {
        $r = explode("\n", $r, 2);
        $r[0] = str_ireplace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $r[0]);
        $r = implode("\n", $r);
      }
      $queries[] = $r;
      $queries[] = '';
    }
 
    return implode("\n", $queries);
  }
 
  /**
   * Returns the SQL query for removing a table from database
   * @param string $table
   * @return string
   */
  public function getTableDropQuery($table) {
    return "DROP TABLE IF EXISTS `" . self::escape($table) . "`;";
  }
 
  /**
   * Returns the SQL query for deleting the whole database
   * @return string
   */
  public function getDatabaseDropQuery() {
    return "DROP DATABASE IF EXISTS `" . self::escape($this->getDatabaseName()) . "`;";
  }
 
  /**
   * Backups the table structure as SQL file and all tables as query output,
   * if the $tablePrefix (if spexified) matches the begin of the table name
   * (case insensitive). Returns an array containing information about the
   * backup files of all tables, the prefixes, and the table names without
   * prefix, as well as a boolean flag that indicated weather the table was
   * backuped or not.
   * @param string $tablePrefix
   * @param array $tables
   * @param string $comments
   * @param bool $dropTablesBeforeInsert
   * @param bool $dropAndRecreateDatabase
   * @return array
   */
  public final function backup($tablePrefix='', $tables=null, $comments='', $dropTablesBeforeInsert=true, $dropAndRecreateDatabase=false) {
    // Conditionize and check arguments
    if (empty($tables)) {
      $tables = $this->getTableNames();
    } else if (!is_array($tables)) {
      throw new MySqlException('If specified, then the tables (names) to backup must be pass as array');
    }
 
    if (!settype($comments, 'string')) {
      throw new MySqlException('Comment must be a string');
    }
 
    $date = new UtcDate();
    $backupFolder = FileSystem::getTempDirectory() . "/mysqlbackup";
 
    $return = array(
        'zipfile' => '',
        'backupinfo' => '',
        'comments' => $comments,
        'time' => $date->toString(),
        'database' => $this->getDatabaseName(),
        'prefix' => $tablePrefix,
        'create' => '',
        'tables' => array()
    );
 
    // Delete backup folder to have a new folder with proper write conditions
    if (FileSystem::exists($backupFolder)) {
      FileSystem::delete($backupFolder);
    }
    FileSystem::mkdir($backupFolder);
    $filebase = $backupFolder . '/mysqlbackup.' . $this->getDatabaseName() . '.' . $date->getTimeStamp() . '.';
 
    // Main backup in critical section surrounded by lock(), try {} catch() {}, unlock()
    $ee = null; {
      // Lock all tables READ/WRITE
      $structureData = '';
      $this->lock();
      try {
        // Backup structure
        if ($dropAndRecreateDatabase) {
          $structureData .= str_ireplace('`' . $this->getDatabaseName() . '`', '<<DATABASE>>', $this->getDatabaseDropQuery()) . "\n\n";
        }
        $structureData .= str_ireplace('`' . $this->getDatabaseName() . '`', '<<DATABASE>>', $this->getDatabaseCreationQuery()) . "\n\n";
 
        // Iterate tables
        foreach ($tables as $table) {
          $table = trim($table);
          if (empty($tablePrefix) || stripos($table, $tablePrefix) === 0) {
            $name = empty($tablePrefix) ? $table : str_ireplace($tablePrefix, '', $table);
            $file = $filebase . $name . '.tdata';
            $this->query("SELECT * FROM `" . self::escape($table) . "` INTO OUTFILE '$file'");
            // Save data for further usage
            $return['tables'][$name] = array(
                'name' => $name,
                'file' => str_ireplace($backupFolder, '', $file)
            );
 
            // If desired, drop original table
            if ($dropTablesBeforeInsert) {
              $dt = $this->getTableDropQuery($table);
              if (!empty($tablePrefix)) {
                $dt = str_replace($table, '<<PREFIX>>' . $name, $dt);
              }
              $structureData .= $dt . "\n\n";
              unset($dt);
            }
 
            // Create table:
            $ct = $this->getTableCreationQuery($table);
            if (!empty($tablePrefix)) {
              $ct = explode("\n", $ct, 2);
              $ct[0] = str_replace($table, '<<PREFIX>>' . $name, $ct[0]);
              $ct = implode("\n", $ct);
            }
            $structureData .= $ct . "\n\n";
            unset($ct);
          }
 
          // Export structure to file
          $file = $filebase . "create.sql";
          if (@file_put_contents($file, $structureData) === false) {
            throw new LException('Could not write structure file');
          } else {
            $return['create'] = str_ireplace($backupFolder, '', $file);
            ;
          }
        }
 
        // Generate backup info
        $return['backupinfo'] = str_ireplace($backupFolder, '', $file);
        $backupInfo = json_encode($return);
        $file = $filebase . "backup.json";
        if (@file_put_contents($file, $backupInfo) === false) {
          throw new LException('Could not write backup info file');
        }
      } catch (\Exception $e) {
        Tracer::traceException($e);
        $ee = $e;
      }
      // unlock the tables
      $this->unlock();
    }
 
    // LException handling after unlock()
    if (!is_null($ee)) {
      try {
        FileSystem::delete($backupFolder);
      } catch (\Exception $e) {
 
      }
      throw $ee;
    }
    // Compress backup folder
    $zip = FileSystem::getTempDirectory() . '/' . $date->getYear() . '-' . $date->getMonth() . '-' . $date->getDay() . '-MySqlBackup.zip';
    ZipFile::compress($backupFolder, $zip);
    FileSystem::delete($backupFolder);
    $return['zipfile'] = $zip;
    return $return;
  }
 
  /**
   * Restores the database content from zip archive. If $tablePrefix
   * is not spexified, the argument will be read from the backup info file in
   * the zip archive. The database is the database actually selected in this
   * object.
   * @param string $zipArchive
   * @param string $tablePrefix
   */
  public final function restore($zipArchive, $tablePrefix=null) {
    $backupFolder = FileSystem::getTempDirectory() . "/mysqlbackup";
    if (FileSystem::exists($backupFolder)) {
      FileSystem::delete($backupFolder);
    }
    try {
      ZipFile::extract($zipArchive, FileSystem::getTempDirectory());
      if (!FileSystem::exists($backupFolder)) {
        throw new LException('Backup folder does not exist after extracting zip archive');
      } else {
        $info = FileSystem::find($backupFolder, "*.backup.json");
        if (count($info) == 0) {
          throw new LException('Could not find a backup info file in zip archive');
        } else if (count($info) > 1) {
          throw new LException('Threr are more than one backup info files in zip archive. Do not know which to use');
        } else {
          // Get arguments or default arguments from info file
          $info = json_decode(FileSystem::readFile(reset($info)), true);
 
          if (is_null($tablePrefix)) {
            if (isset($info['prefix']) && strlen(trim($info['prefix'])) > 0) {
              $tablePrefix = $info['prefix'];
            } else {
              throw new LException('No table prefix specified and no prefix in the backup info file');
            }
          }
 
          if (!isset($info['create'])) {
            throw new LException('Missing "create" entry in info file');
          } else if (!isset($info['tables']) || !is_array($info['tables'])) {
            throw new LException('Missing tables entry in info file');
          } else if (empty($info['tables'])) {
            throw new LException('Tables entry in info file empty, no tables to restore registered.');
          } else {
            $tablePrefix = addslashes(trim($tablePrefix));
            $create = FileSystem::readFile($backupFolder . '/' . trim($info['create'], " /"));
            $create = str_replace('<<PREFIX>>', $tablePrefix, $create);
            $create = str_replace('<<DATABASE>>', $this->getDatabaseName(), $create);
            $create = str_replace("\n", "", str_replace("\r", "", $create));
            $tables = array();
            Tracer::trace_r($info['tables'], '$info["tables"]');
            foreach ($info['tables'] as $table) {
              $tables[$tablePrefix . $table['name']] = $backupFolder . '/' . trim($table['file'], " /");
            }
 
            Tracer::trace_r($tables, '$tables');
            foreach (explode(';', $create) as $query) {
              $query = trim($query);
              if (!empty($query)) {
                Tracer::trace($query);
                $this->query($query);
              }
            }
            foreach ($tables as $tableName => $tableFile) {
              @chmod($tableFile, 0777);
              $query = "LOAD DATA INFILE '$tableFile' INTO TABLE `" . self::escape($tableName) . "`";
              if (!empty($query)) {
                Tracer::trace($query);
                $this->query($query);
              }
            }
          }
        }
      }
    } catch (\Exception $e) {
      FileSystem::delete($backupFolder);
      throw $e;
    }
    FileSystem::delete($backupFolder);
  }
 
}
 
;