Bitte warten...

PHP: Backup von MySQL-Daten

Das hier vorgestellte Skript dient nur der Veranschaulichung. Es bietet eine einfache Backup-Möglichkeit für MySQL-Tabellen im Rahmen der anderen, auf dieser Website vorgestellten MySQL-Funktionen. Da bestimmte MySQL-Funktionen unter Umständen von diesem Skript nicht unterstützt werden, kann keine Haftung für eventuellen Datenverlust durch die Verwendung dieses Skripts übernommen werden! Eine zuverlässige Methode für den Datenexport bietet phpMyAdmin oder die Shell.

Ähnlich der Exportfunktion von phpMyAdmin kann man über ein Skript eine SQL-Datei erzeugen, die die Inhalte einer Datenbank in Form von SQL-Befehlen enthält. Auf diese Weise kann diese Datei sowohl über ein spezielles Import-Skript als auch über phpMyAdmin wieder importiert werden, um die Tabellen wieder herzustellen.

In dieser Datei müssen noch die Zugangsdaten für die Datenbank der eigenen Situation angepasst werden:

Code kopieren
<?php
  $thisfile = basename(__FILE__);
  $database['uname'] = "web007";        # Benutzername für den Zugang zur Datenbank
  $database['pword'] = "moneypenny";    # Passwort für den Zugang zur Datenbank
  $database['name']  = "usr_web007_1";  # Name der Datenbank
  
  session_start();
  header("content-type: text/html; charset=utf-8");
  setlocale(LC_ALL, 'de_DE.UTF-8');
  date_default_timezone_set("Europe/Berlin");
  $my = mysqli_connect("localhost", $database['uname'], $database['pword'], $database['name']) or die("Error: ".mysqli_connect_error());
  mysqli_set_charset($my, "utf8");
  
  function backup_db($backupfile, $database, $my, $tables) {
    $defaults = ["bit", "tinyint", "smallint", "mediumint", "int", "bigint", "float", "double", "decimal", "char", "varchar", "date", "datetime", "time", "year", "binary", "varbinary", "enum", "set"];
    $quotes = ["bit", "char", "varchar", "tinytext", "text", "mediumtext", "longtext", "date", "datetime", "timestamp", "time", "binary", "varbinary", "enum", "set"];
    $sql  = "-- decocode SQL Dump\r\n";
    $sql .= "-- Version 3.2\r\n";
    $sql .= "-- https://www.decocode.de/\r\n";
    $sql .= "--\r\n";
    $sql .= "-- Host: ".$_SERVER['SERVER_NAME']."\r\n";
    $sql .= "-- Erstellungszeit: ".date("d F Y @ h:i a", time())."\r\n";
    $sql .= "-- Server-Version: ".mysqli_get_server_info($my)."\r\n";
    $sql .= "-- PHP-Version: ".phpversion()."\r\n";
    $sql .= "\r\n";
    $sql .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\r\n";
    $sql .= "\r\n";
    $sql .= "--\r\n";
    $sql .= "-- Datenbank: `".$database['name']."`\r\n";
    $sql .= "--\r\n";
    $res = mysqli_query($my, "SHOW TABLE STATUS");
    while ($dbt = mysqli_fetch_assoc($res)) {  # walk all database tables
      if (in_array($dbt['Name'], $tables)) {
        $sql .= "\r\n-- --------------------------------------------------------\r\n";
        $sql .= "\r\n--\r\n";
        $sql .= "-- Tabellenstruktur für Tabelle `".$dbt['Name']."`\r\n";
        $sql .= "--\r\n\r\n";
        $sql .= "DROP TABLE IF EXISTS `".$dbt['Name']."`;\r\n";
        $sql .= "CREATE TABLE IF NOT EXISTS `".$dbt['Name']."` (\r\n";
        $fields = []; $coltype = [];
        $res2 = mysqli_query($my, "SHOW COLUMNS FROM `".$dbt['Name']."`");
        while ($col = mysqli_fetch_assoc($res2)) {  # walk all table columns
          $sql .= "  `".$col['Field']."` ".$col['Type'];
          $fields[] = $col['Field'];
          if ($col['Null'] == "NO") $sql .= " NOT NULL";
          $type = $col['Type'];
          if (substr_count($type, "(")) $type = substr($type, 0, strpos($type, "("));
          if (substr_count($type, " ")) $type = substr($type, 0, strpos($type, " "));
          $coltype[$col['Field']] = $type;
          if ($col['Default'] == "CURRENT_TIMESTAMP") $default = "CURRENT_TIMESTAMP";
          elseif (isset($col['Default'])) $default = "'".$col['Default']."'"; else $default = "NULL";
          if (
            (isset($col['Default']) && $default != "''") ||
            (in_array($type, $defaults) && $col['Null'] == "YES")
          ) $sql .= " default ".$default;
          if ($col['Extra']) $sql .= " ".$col['Extra'];
          $sql .= ",\r\n";
          if ($col['Key'] == "PRI" && !isset($key)) $key = $col['Field'];
        }
        if (isset($key)) $sql .= "  PRIMARY KEY (`".$key."`)\r\n";
        else $sql = mb_substr($sql, 0, mb_strlen($sql) - 3)."\r\n";
        $sql .= ") ENGINE=".$dbt['Engine']." DEFAULT CHARSET=".substr($dbt['Collation'], 0, strpos($dbt['Collation'], "_"));
        if ($dbt['Auto_increment']) $sql .= " AUTO_INCREMENT=".$dbt['Auto_increment'];
        $sql .= ";\r\n";
        if ($dbt['Rows']) {  # entries exist?
          $sql .= "\r\n--\r\n";
          $sql .= "-- Daten für Tabelle `".$dbt['Name']."`\r\n";
          $sql .= "--\r\n\r\n";
          $sql .= "LOCK TABLES `".$dbt['Name']."` WRITE;\r\n";
          $insert = "INSERT INTO `".$dbt['Name']."` (";
          foreach($fields as $field) $insert .= "`".$field."`, ";
          $insert = mb_substr($insert, 0, mb_strlen($insert) - 2).") VALUES\r\n";
          if (isset($key)) $order = " ORDER BY `".$key."`"; else $order = NULL;
          $block = $insert;
          $res2 = mysqli_query($my, "SELECT * FROM `".$dbt['Name']."`".$order);
          while ($row = mysqli_fetch_assoc($res2)) {  # walk all entries
            $line = "(";
            foreach($fields as $field) {
              $q = NULL;
              $value = $row[$field];
              if (is_null($value)) $value = "NULL";
              elseif (in_array($coltype[$field], $quotes)) $q = "'";
              elseif (substr_count($coltype[$field], "blob")) {
                if ($value) $value = "0x".bin2hex($value);
                else $q = "'";
              }
              $value = mysqli_escape_string($my, $value);
              $value = str_replace("\\'", "''", $value);
              $value = str_replace("\\\"", "\"", $value);
              $line .= $q.$value.$q.", ";
            }
            $line = mb_substr($line, 0, mb_strlen($line) - 2)."),\r\n";
            if (mb_strlen($block) <= 50000) $block .= $line;
            else {
              $block = mb_substr($block, 0, mb_strlen($block) - 4).");\r\n";
              $sql .= $block;
              $block = $insert.$line;
            }
          }
          $block = mb_substr($block, 0, mb_strlen($block) - 4).");\r\n";
          $sql .= $block;
          $sql .= "UNLOCK TABLES;\r\n";
        }
      }
    }
    if (!file_put_contents($backupfile, $sql)) die("SQL file could not be created!");
  }
  
  function restore_db($backupfile, $my) {
    $line = 1; $res = [0, ""];
    $handle = fopen($backupfile, "r");
      while (!feof($handle) && !$res[0]) {
        $content = trim(fgets($handle));
        if ($content && substr($content, 0, 2) != "--") {
          if (!$query) $qline = $line;
          $query .= $content." ";
          if (substr($content, -1) == ";") {
            if (!mysqli_query($my, $query)) $res = [$qline, mysqli_error($my)];
            unset($query);
          }
        }
        $line++;
      }
    fclose($handle);
    return $res;
  }  
  
  if (isset($_GET['export'])) {
    if ($_POST['tables']) {
      $backupfile = "mysql_export-".date("Y.m.d-H.i",time()).".sql";
      backup_db($backupfile, $database, $my, $_POST['tables']);
      header("content-type: text/plain; charset=utf-8");
      header("content-disposition: attachment; filename=".$backupfile);
      readfile($backupfile);
      unlink($backupfile);
    } else header("Location: ./".$thisfile);
    exit;
  }
  
  if (isset($_GET['import'])) {
    if ($_FILES['uploadfile']['name']) {  # Dateipfad wurde angegeben
      $backupfile = $_FILES['uploadfile']['name'];
      if (!move_uploaded_file($_FILES['uploadfile']['tmp_name'], basename($backupfile))) {  # Upload ist gescheitert
        $_SESSION['message'] = "      <p style='color:red;'>Die Datei konnte nicht importiert werden!</p>\r\n";
      } elseif (substr($backupfile, strlen($backupfile) - 4, 4) == ".sql") {  # SQL-Datei wurde hochgeladen
        # Zunächst wird ein Backup der aktuellen Tabellen gemacht
        $tables = [];
        $res = mysqli_query($my, "SHOW TABLE STATUS");
        while ($dbt = mysqli_fetch_assoc($res)) $tables[] = $dbt['Name'];
        backup_db("reset.sql", $database, $my, $tables);
        # Jetzt werden die Tabellen anhand der Backup-Datei wiederhergestellt
        $res = restore_db($backupfile, $my);
        if (!$res[0]) $_SESSION['message'] = "      <p style='color:green;'>Die Datenbanktabellen wurden erfolgreich wiederhergestellt.</p>\r\n";
        else {  # Die Backup-Datei enthält Fehler
          $_SESSION['message']  = "      <p style='color:red;'>Die SQL-Datei enthält einen Fehler in der Anweisung ab Zeile ".$res[0].":<br><samp style='color:black;'>".htmlspecialchars($res[1], ENT_QUOTES, 'UTF-8')."</samp></p>\r\n";
          restore_db("reset.sql", $my);
        }
        unlink("reset.sql");
        unlink($backupfile);
      } else {  # Die Datei ist keine SQL-Datei
        $_SESSION['message'] = "      <p style='color:red;'>Dies ist keine SQL-Datei!</p>\r\n";
        unlink($backupfile);
      }
    }
    header("Location: ./".$thisfile);
    exit;
  }
  
  echo "<!DOCTYPE html>
<html lang='de'>
  <head>
    <title>MySQL Backup</title>
    <meta charset='UTF-8'>
    <style>
      body { padding:20px; }
      fieldset { background-color:#def; width:440px; height:300px; }
      input[type=submit] { width:200px; }
    </style>
  </head>
  <body>
    <h2>MySQL Backup • Datenbank: <samp>".$database['name']."</h2>
    <fieldset style='float:left;'><legend>Datenbanktabellen sichern</legend>
      <p>Wählen Sie die zu sichernden Datenbanktabellen aus:</p>
      <form accept-charset='utf-8' action='".$thisfile."?export' method='post'>
        <div>
          <select name='tables[]' size='8' multiple='multiple'>\r\n";
  $res = mysqli_query($my, "SHOW TABLE STATUS");
  while ($dbt = mysqli_fetch_assoc($res)) echo "            <option>".$dbt['Name']."</option>\r\n";
  echo "          </select><br><br>
          <input type='submit' value='Backup-Datei exportieren'>
        </div>
      </form>
    </fieldset>
    <p></p>
    <fieldset><legend>Datenbanktabellen wiederherstellen</legend>
  if (isset($_SESSION['message'])) echo $_SESSION['message'];
      <form accept-charset='utf-8' action='".$thisfile."?import' method='post' enctype='multipart/form-data'>
        <div><br>
          <input type='file' size='40' name='uploadfile'><br><br>
          <input id='up1' type='submit' onclick=\"document.getElementById('up1').style.display='none';document.getElementById('up2').style.display='inline';\" value='Backup-Datei importieren'>
          <input id='up2' type='submit' style='display:none;' disabled='disabled' value='Warten…'>
        </div>
      </form>
    </fieldset>
  </body>
</html>";
  unset($_SESSION['message']);
?>

Besonders die Behandlung von Tabellenfeldern des Datentyps timestamp sowie von Tabellen, die mehrere Schlüssel besitzen, ist noch unausgereift. Das Export-Skript sucht lediglich nach dem ersten Vorkommen eines Schlüssels und gibt diesem den Typ PRIMARY KEY (s. Zeilen 58 und 60).