MySQL-Datenbank kopieren

In MySQL gibt es keinen Spezialbefehl um eine ganze Datenbank inklusive Daten und Struktur zu kopieren. Mit folgenden zwei Befehlen kann eine Tabelle inklusive der Originalstruktur kopiert werden:

create table Zieldatenbank.Tabelle_1 like Quelldatenbank.Tabelle_1;
insert into Zieldatenbank.Tabelle_1 select * from Quelldatenbank.Tabelle_1;

Der erste Befehl kopiert die Struktur der Tabelle inklusive aller Indizes. Der zweite Befehl kopiert alle Daten in die gerade kopierte Tabelle.

Basierend auf diesem Befehl können alle Tabellen der Quelldatenbank mit dem Befehl SHOW TABLES; angezeigt werden. Dabei müssen Views ausgeschlossen werden, da sie nicht mit dem o. g. Befehlen kopiert werden können:

show full tables where Table_type = 'BASE TABLE';

Die Liste der Tabellen kann in Excel kopiert werden und dort mit der folgenden Formel in der Nachbarspalte in ein SQL-Skript umgewandelt werden, der eine Tabelle nach der anderen in die neue Datenbank kopiert:

="create table shirta_local_com." & A2 & " like " & A2 & "; insert into shirta_local_com." & A2 & " select * from " & A2 & ";"

Da viele Datenbanken die Fremdschlüssel nutzen, kann die Prüfung der Fremdschlüssel wärend des Kopiervorgangs mit dem folgenden Befehl abgeschaltet und anschließend wieder aktiviert werden:

set foreign_key_checks = 0;
...
set foreign_key_checks = 1;

Der gesamte Skript könnte wie folgt in Excel umgesetzt werden:

mysql-datenbanken-kopieren

Excel-Vorlage: Download

Verwaltung von Benutzer und Zugriffsrechte in MySQL

Die Authentifikation eines Clients an einem MySQL-Server besteht aus drei Parametern: Neben der üblichen Benutzername und Passwort ist noch der Host entscheidend, von dem sich der Client verbindet. Die Zugriffskontrolle erfolgt in zwei Phasen. In der ersten Phase werden Host, Benutzer und Passwort geprüft. Wird in der Benutzertabelle ein passender Eintrag gefunden, wird die Verbindung zum Server hergestellt. In der zweiten Phase werden die Zugriffsrechte überprüft.

Grant-Tabellen in MySQL

Auf einem MySQL-Server werden Benutzer und Zugriffsrechte in einem speziellen Datenbankschema namens mysql verwaltet. Alle Tabellen dieses Schemas werden als Grant-Tabellen (Erlaubnistabellen) bezeichnet. Übersicht der wichtigsten Grant-Tabellen:

  • user – Verbindungsphase und globale Benutzerrechte
  • db – Benutzerrechte auf der Datenbankebene
  • tables_priv – Rechtevergabe auf der Tabellenebene
  • columns_priv – Rechtevergabe auf der Spaltenebene
  • procs_priv – Rechtevergabe auf der Prozedurebene
  • host – Host-Basierte Zugriffsrechte

Achtung: Grant-Tabellen speichern Erlaubnisse aber keine Verbote.
Bei Vergabe der Zugriffsrechte gilt deshalb: Für Einschränkungen auf einer tieferen Ebene müssen Erlaubnisse auf einer höheren Ebene entzogen werden!

Achtung: REVOKE ALL PRIVILEGES ON *.* … entzieht nur die globalen Rechte, nicht aber die Rechte auf den Datenbank- und Tabellenebenen.

REVOKE ALL PRIVILEGES

  • … ON *.* … entzieht ausschließlich globale Zugriffsrechte (Grant-Tabelle: mysql.user), Zugriffsrechte auf der Datenbank- und Tabellenebene bleiben erhalten
  • … ON db_name.* … entzieht Zugriffsrechte auf der Datenbankebene (Grant-Tabelle: mysql.db)
  • … ON db_name.tbl_name … entzieht Zugriffsrechte auf der Tabellen- und Spalten-Ebenen (Grant-Tabellen: mysql.tables_priv und mysql.columns_priv)

Granularität der Zugriffsrechte in MySQL

mysql-grant