decocode decocode deco    

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:

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
<?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 = array("bit", "tinyint", "smallint", "mediumint", "int", "bigint", "float", "double", "decimal", "char", "varchar", "date", "datetime", "time", "year", "binary", "varbinary", "enum", "set");
    $quotes = array("bit", "char", "varchar", "tinytext", "text", "mediumtext", "longtext", "date", "datetime", "timestamp", "time", "binary", "varbinary", "enum", "set");
    $sql  = "-- decocode SQL Dump\r\n";
    $sql .= "-- Version 3.1\r\n";
    $sql .= "-- http://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 = array(); $coltype = array();
        $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."`, ";
          reset($fields);
          $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.", ";
            }
            reset($fields);
            $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 = array(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 = array($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 = array();
        $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>\r\n";
  echo "<html lang='de'>\r\n";
  echo "  <head>\r\n";
  echo "    <title>MySQL Backup</title>\r\n";
  echo "    <meta charset='UTF-8'>
    <style type='text/css'>
      body { padding:20px; }
      fieldset { background-color:#def; width:440px; height:300px; }
      input[type=submit] { width:200px; }
    </style>\r\n";
  echo "  </head>\r\n";
  echo "  <body>\r\n";
  echo "    <h2>MySQL Backup • Datenbank: <samp>".$database['name']."</h2>\r\n";
  echo "    <fieldset style='float:left;'><legend>Datenbanktabellen sichern</legend>\r\n";
  echo "      <p>Wählen Sie die zu sichernden Datenbanktabellen aus:</p>\r\n";
  echo "      <form accept-charset='utf-8' action='".$thisfile."?export' method='post'>\r\n";
  echo "        <div>\r\n";
  echo "          <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>\r\n";
  echo "          <input type='submit' value='Backup-Datei exportieren'>\r\n";
  echo "        </div>\r\n";
  echo "      </form>\r\n";
  echo "    </fieldset>\r\n";
  echo "    <p></p>\r\n";
  echo "    <fieldset><legend>Datenbanktabellen wiederherstellen</legend>\r\n";
  if (isset($_SESSION['message'])) echo $_SESSION['message'];
  echo "      <form accept-charset='utf-8' action='".$thisfile."?import' method='post' enctype='multipart/form-data'>\r\n";
  echo "        <div><br>\r\n";
  echo "          <input type='file' size='40' name='uploadfile'><br><br>\r\n";
  echo "          <input id='up1' type='submit' onclick=\"document.getElementById('up1').style.display='none';document.getElementById('up2').style.display='inline';\" value='Backup-Datei importieren'>\r\n";
  echo "          <input id='up2' type='submit' style='display:none;' disabled='disabled' value='Warten…'>\r\n";
  echo "        </div>\r\n";
  echo "      </form>\r\n";
  echo "    </fieldset>\r\n";
  echo "  </body>\r\n";
  echo "</html>\r\n";
  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).