Transcript Relationale Datenbank MySQL

Relationale Datenbank MySQL
MYSQL
PERL
PHP
HTML
ActiveX
JavaScript
ASP
XML
Gliederung
0. Einblick Datenbanken
1. Einführung MySQL
2. Relationale Datenbank
Einblick Datenbanken
Momentan gängige Datenbanken:
- Oracle (32% Marktanteil)
- DB2 (33% Marktanteil)
- MySQL (21 % Marktanteil)
- SQL-Server (11% Marktanteil, von
Microsoft)
Dateiorgansation Datenbanken
Einkauf
Artikel
Lieferanten
Bestellungen
Bestand
ReWe
Kunden
Forderungen
Verbindlichkeiten
Lieferanten
Produktion
Hoher Bedarf
an Kommunikation und
Datenaustausch
Artikel
Aufträge
Stücklisten
Arbeitspläne
Vertrieb
Artikel
Kunden
Angebote
Aufträge
Was ist MySQL ?
MySQL ist ein echter Multi-User, Multi-Treaded SQL Datenbank und wird von allen
großen Providern oder auch Suchmaschinenbetreibern eingesetzt. MySQL ist eine CLient/Server
Implentierung, bestehend aus einem Server-Dämon mysqld und vielen Client Programmen,
sowie Bibliotheken für PERL, PHP/3, PHP/4, ASP.
Die bedeutsamsten Vorteile von MySQL sind:
 Geschwindigkeit,
 Stabilität
 einfache Bedienbarkeit.
MySQL wurde ursprünglich entwickelt, weil auf TCX (dem Server der Entwickler) ein
SQL Server benötigt wurde, der sehr große Datenbanken handeln konnte, und zwar um eine
Größenordnung schneller, als die Datenbankhersteller damals liefern konnten. MySQL ist nun
seit 1996 auf vielen Seiten im Einsatz.
Begriffe
• Eine Datenbank ist eine strukturierte Sammlung
von Daten - Datenbasis
• Aufbau, Verwaltung und Strukturierung werden
von einem Datenbank-Management- System
(DBMS) übernommen.
• Die Abfrage erfolgt über spezielle
Abfragesprachen, wie z.B. SQL (Structured Query
Language) oder ODBC (Open DataBase Connectivity)
ODBC
• Open DataBase Connectivity, (ODBC), Ein
Standard für den Zugriff auf verschiedene
Datenbanksysteme.
• ODBC hat Schnittstellen für Java, Visual Basic,
C++, SQL
• ODBC Treiberpaket beinhaltet Treiber für
Access, Paradox, dBase, Excel and Btrieve
• ODBC übersetzt Abfragen jeweils in die
datenbankspezifische Sprache.
ODBC
Vorteile:
• Datenbankunabhängige Entwicklung möglich
• Zugriff auf entfernte Datenbanken
Nachteile:
• Langsam gegenüber nativen Zugriffen
• Microsoft Fixiertheit
• Notwendigkeit, eigene Treiber für die
Datenbanken zu installieren und Datenbanken
u.U. selbst eintragen zu müssen
Datenbanktypen
• hierarchisch
Daten werden in einer Baumstruktur
gespeichert
• objektorientiert
besitzt sämtliche Merkmale
objektorientierter Programmierung, wie
Klassen, Objekte, Vererbung usw
Datenbanktypen
• relational
Daten werden in Tabellen gespeichert. Die Tabellen sind
durch Beziehungen (relations) miteinander verknüpft.
Dadurch ist es möglich, Daten aus mehreren Tabellen zu
kombinieren bei Abfragen.
Merkmale
• Eine Relation zwischen zwei Tabellen entsteht dadurch,
dass eine Tabelle ein Feld mit Primärschlüsselwerten
einer anderen Tabelle enthält.
• Relationen machen eine DB der
Tabellenkalkulation(Tabelle) überlegen
MySQL - Merkmale
• MySQL ist ein Datenbank Management System
für relationale Datenbanken.
• Es ist in kompilierter Form und auch als SourceCode frei verfügbar.
• Es läuft auf Windows und UNIX Servern
• Installation und Administration ist relativ einfach
• Es hat Programmierschnittstellen zu den
wichtigsten Programmiersprachen: Java, C++,
Perl, PHP …
MySQL - Sicherheit
•
MySQL besitzt ein zuverlässige
Sicherheitskonzept. Es basiert auf:
– Benutzer
– Passworte
– Zugriffsrechte: Administrator kann
Zugriffsrechte auf Datenbanken, Tabellen
und einzelne Felder innerhalb der Tabelle für
jeden einzelnen Benutzer festlegen
MySQL - Datentypen
MySQL kennt 27 Datentypen, u.a.:
• INTEGERS (ganzzahlige Zahlen),
• FLOAT, DOUBLE (Gleitkommazahlen)
• DECIMAL (Festkommazahlen)
• CHAR, VARCHAR (Zeichenketten)
• TEXT, BLOB (Texte, Binärobjekte)
• DATE, TIME, DATETIME (Datum)
• TIMESTAMP, YEAR (Datum)
• SET, and ENUM (Auswahl aus vorgegebenen
Werten)
Attribute für Tabellenspalten
Attribute
• beeinflussen die Eigenschaften von Tabellenspalten
(=Datenfeldern)
• Angabe von Attributen erfolgt bei der
Spaltendeklaration in der CREATE-Anweisung
(Tabellenerstellung)
• Die Angabe ist optional
Attribute für Datentypen
Syntax der Spaltendeklaration:
Spaltenname, Spaltentyp
[NOT NULL|NULL] [DEFAULT Standardwert]
...
Attribute für Datentypen
Attribut
Bedeutung
Feldtyp
[DEFAULT Standardwert]
Wert eintragen, wenn sonst Alle
keine Angaben
[NULL]
Spalte darf NULL-Werte
enthalten
Alle
[NOT NULL]
Spalte darf keine NULLWerte enthalten
Alle
[AUTO_INCREMENT]:
automatisch generierte
Zahlenfolge
INTEGER
Attribute für Datentypen
Attribut
Bedeutung
Feldtyp
[(M)]
Anzeigenbreite
INTEGER
[(M,D)]
Anzeigenbreite, Zahl
der Nachkommastellen
Dezimalzahlen
(FLOAT, DOUBLE)
[UNSIGNED]
Ohne Vorzeichen - Nur
positive Werte möglich
INTEGER, FLOAT,
DOUBLE
[ZEROFILL]
Füllt bis zur
Anzeigenbreite mit
Nullen auf
INTEGER, FLOAT,
DOUBLE
Attribute für Datentypen
Attribut
Bedeutung
Feldtyp
[PRIMARY KEY]
Primärschlüssel, eindeutig
Alle
[KEY]
Schlüssel
Alle
[INDEX]
Tabellenindex –
Alle
beschleunigt Suchabfragen
[UNIQUE]
Eindeutiger Indexwert, darf
nur einmal vorkommen
INTEGER
CHAR
Tabellenindizes
• Indizes können für eine oder mehrere
Tabellenspalten angelegt werden.
• Indizes sind sinnvoll bei Spalten, die oft
als Suchkriterium benutzt werden.
• Sie beschleunigen Suchabfragen.
• Indizes benötigen Speicherplatz und
verlangsamen das Einfügen von neuen
Datensätzen.
Beziehung zwischen SQL und MySQL
• SQL ist eine Programmiersprache.
• MySQL ist ein RDBMS (relationales
Datenbank-Management-System), das SQL
benutzt, um Daten zu bearbeiten, zu
erstellen und anzuzeigen.
Relationale Datenbank
•
•
•
•
•
•
Tabelle
Feld
Eintrag
Schlüssel
Beziehung
Normalisierung
Relationale Datenbank
Ein Primärschlüssel ist ein eindeutiges Feld innerhalb der
Tabelle. Kein anderer Datensatz in diesem Feld hat den
gleichen Wert. Dies dient dazu, einen Datensatz von allen
anderen Datensätzen in dieser Tabelle zu unterscheiden.
Ein Fremdschlüssel repräsentiert den Wert des
Primärschlüssel für eine verknüpfte Tabelle. Es dient als
strukturelle Link, der die Beziehung zwischen verschiedenen
Tabellen definiert.
Relationale Datenbank
Type der Beziehung
– Eins-zu-eins (1:1)
– Eine-zu-vielen (1:n)
– Viel-zu-vielen (m:n)
Relationale Datenbank
Eins-zu-eins Beziehung
Kunde
Tabelle1
...
Datensatz
Datensatz
Datensatz
...
Tabelle2
...
Datensatz
Datensatz
...
Kundennummer
Relationale Datenbank
Eine-zu-vielen Beziehung
Kunde
Tabelle1
...
Datensatz
Datensatz
Datensatz
...
Tabelle2
...
Datensatz
Datensatz
Datensatz
Datensatz
...
Bestellung
Relationale Datenbank
Viele-zu-vielen Beziehung
Artikel
Tabelle1
...
Datensatz
Datensatz
Datensatz
Datensatz
...
Tabelle2
...
Datensatz
Datensatz
Datensatz
Datensatz
...
Bestellung
Anlegen von Indexen und Tabellen
Die MySQL-Anweisung zum Erzeugen von Tabellen lautet:
CREATE TABLE tabelle
( spaltenname Datentyp [NOT NULL], ...)
Für jede Spalte sind Bezeichnung und Typ anzugeben, zusätzlich kann mit
NOT NULL festgelegt werden, daß die Spalte keine unbestimmten NULLWerte beinhalten darf, was z.B. bei Schlüsselspalten erforderlich ist
CREATE INDEX (Index für Tabelle anlegen)
Löschen von Indexen und Tabellen
Logische Objekte können vollständig (d.h. sowohl die Beschreibung als auch
die beinhalteten Daten!) aus einer Datenbank gelöscht werden. Für Tabellen
und Indexen lauten die SQLAnweisungen:
DROP TABLE
DROP INDEX
MySQL
MySQL –Syntax
• CREATE
CREATE DATABASE Datenbankname
CREATE TABLE Tabellenname
• DROP
DROP DATABASE Datenbankname
DROP TABLE Tabellenname
• INSERT
INSERT INTO Tabellenname VALUES (Wert, Wert, Wert, ...)
• UPDATE
UPDATE Tabellenname SET Spaltenname = Wert WHERE x = y
Abfragen von Tabellen
• SELECT ist der wichtigste SQL Befehl. Jede Anfrage an eine
Datenbank beginnt mit SELECT. Die Techniken zur Abfrage von
Datenbanken werden auch zu deren Veränderung mit DELETE,
INSERT und UPDATE verwendet.
• Der SELCT Befehl kann auf eine oder mehrere Tabellen
angewendet werden. Das Ergebnis der Suche ist wieder eine
Tabelle. Die Auswahl der Spalten für die Ergebnistabelle nennt
man Projektion, die der Zeilen Selektion. Werden für eine
Anfrage Daten aus mehreren Tabellen zusammengetragen,
spricht man von einem Join.
SQL-Queries dazu
• SELECT raum FROM vorlesung;
• SELECT zeit FROM vorlesung WHERE titel
LIKE ‘Math%';
• SELECT name FROM studentin WHERE matrnr IN
(SELECT matrnr FROM hoeren WHERE vorlnr = 'Inf 001');
MySQL
• DELETE
DELETE FROM Tabellenname WHERE a = b
• JOIN
CROSS JOIN
INNER JOIN (Beinhaltet nur die Datensätze, bei denen die
Inhalte der verknüpften Felder beider Tabellen gleich sind.)
LEFT JOIN
• Sortieren
ORDER BY Spaltennamen
MySQL
• Aggregat-Funktionen:
Funktionen, die in Spalten zusammenfassende,
mathematische Berechnung durchführen.
COUNT()
COUNT(DISTINCT)
MAX()
MIN()
AVG()
SUM()
STD()
Interaktion
• Clientseitige Möglichkeiten
– Sicherstellen der
Anzeigefähigkeit von
gelieferten Informationen
(bspw. PlugIns)
– Sicherstellen von „richtigen“
Anfragen an den Server
(Eingabeüberprüfungen)
– Transformation von gelieferten
Informationen in der
gewünschten Art und Weise
(Formatierung von Siteinhalten,
StyleSheets)
• Serverseitige Möglichkeiten
– Entgegennahme von HTMLRequests  Lieferung von
HTML-Seiten
– Transformation der Requests
in der gewünschten Art und
Weise  z.B. Übersetzung der
Anfrage in ein MySQLStatement oder auch zurück,
d.h. Umwandlung eines
Abfrageergebnisses in ein
HTML-Dokument
– Weiterleitung von ClientRequests an andere Server,
insbes. Datenbankserver
Interaktion
•
Mit einem solchen Modell besteht die Möglichkeit, in
Kombination der serverseitigen und clientseitigen
Programmierung Webapplikationen zu erstellen, die
die gewohnte Interaktivität von
Computerprogrammen bieten
•
•
gestatten es, den Added Value für eine Site zu erzeugen
Man wird hiermit in die Lage versetzt, dem User
Webseiten präsentieren zu können, welche sich frei
nach seinen Wünschen konfigurieren lassen!
•
d.h. entsprechend seines Verhaltens kann auf die Eingaben
des Nutzers reagiert werden.
Dynamische Webseiten
• HTML-Formulare
• URL-Extensions (Datenübertragung zum WebServer)
• JavaScript (Darstellung & Transformation der
Informationen)
• Java (Darstellung & Transformation der
Informationen)
• CSS/DHTML (Darstellung der Informationen)
• PlugIns (Darstellung von Informationen,
teilweise auch Transformationen)
Verarbeitung des
HTMLDokuments/QueryResultats mittels
Computer-Programm
• CGI-Scripts (i.d.R.
PERL)
• ASP (Active Server
Pages)
• JSP (Java Server
Pages)
• PHP ...
• MySQL-Queries
werden dazu
benutzt den
Datenbestand
abzufragen und
zu analysieren
•
•
•
•
Konzept der Datenanbindung ist flexibel
– Zugriff auf eine einfache Textdatei (TDC)
– Zugriff über Datenbankabfragesprache SQL auf eine
Datenbank, die SQL-Befehle verarbeiten kann.
Anbindung einer HTML-Datei an Daten aus einer
Datenbank, die über die Datenbank-Schnittstellen OLE-DB
oder ODBC verfügen
– beispielsweise MS Access, Oracle oder SQL-Server
wie MySql
Speichern von Daten möglich
Ebenfalls ActiveX-Control notwendig
Datenbankabfrage mit PHP
Db_connection
PHP
Kunden
Nummer
SELECT * FROM
MySQL
Db_result
Name
Rechnung
Login
PHP_DB
Datenbankabfrage mit PHP
<?php
$db_connection = @mysql_connect("localhost","root“,“pw");
mysql_select_db(„ PHP_DB ");
$db_result = mysql_query( "SELECT * FROM Kunden“, $db_connection);
mysql_close($db_connection);
?>
Datenbankabfrage mit PHP
Resultat nach Select
KundenNummer
Name
Rechnung
122
Münz
450,45€
123
Müller
15,00 €
<?php
$db_result =mysql_query( "SELECT KundenNummer,Name,Rechnung FROM Kunden“,
$db_connection);
while( $row =mysql_fetch_array( $db_result ))
{
echo $row[“KundenNummer"];
echo $row[“Name“];
echo $row[“Rechnung“];
}
?>
PHP Bank mit Datenbank-Anbindung
Kunden Nummer
Login
Einlogen
<?php
echo ‘<h2>Login</h2>‘;
$KundenNummer = $_POST[‘KundenNummer '];
$Login = $_POST[‘Login'];
phpBank_login( $ KundenNummer, $ Login);
...
?>
login.php
function phpBank_login($v_ KundenNummer, $v_ Login){
$db_connection = @mysql_connect("localhost","root“,“pw");
mysql_select_db("PHP_DB");
$db_result = mysql_query(
"SELECT * FROM Kunden WHERE
KundenNummer =“.$v_ KundenNummer.“ AND PIN = “.$v_ Login“,
$db_connection);
If( $row = mysql_fetch_array( $db_result ) ){
echo ‘Hallo ‘.$row[‘Name‘].‘ Ihr Rechnung: ‘.$row[‘Rechnung‘];
}else{ die(‘Login ist falsch‘); }
}
Login
Hallo user2
Ihr Rechnung:15€
Login ist falsch
SESSION
• Ausgangsproblem:
– Über HTTP werden keine ständigen Verbindungen zu
den Servern hergestellt, sondern die Kommunikation
läuft immer nach dem Schema „Anfrage“ => „Antwort“
(request/response). Deshalb können sich Webseiten
per se nicht „erinnern“ (= stateless protocol).
• Lösung durch Sessions:
– Entweder server- oder clientseitig (=> cookies)
werden pro User-Agent (= Browser etc.) individuelle
Parameter gespeichert, auf die dann alle PHP Skripte
Zugriff haben. Diese Parameter müssen dann nicht
mehr mit POST oder GET übermittelt werden.
Lediglich eine Session ID muss von Seite zu Seite
übermittelt werden.
Gutes Design
Darstellung
z.B.
<<HTML>>
Controller
<<PHP>>
DatenbankAbstraktionsschicht
Datenbank
(leicht auswechselbar)
Wiederverwendbare
Komponenten
<<PHP-Library>>
Datenbank Abstraktionsschicht
• In einer Datenbankabstraktionsschicht werden
alle Zugriffe auf die DB durch eine zentrale Datei
mediatisiert.
• Sinn einer Datenbankabstraktionsschicht ist es,
das Umstellen auf andere Datenbanken oder –
quellen zu erleichtern. Auch können die
Datenbankabfragen unabhängig vom restlichen
Programmiercode entwickelt und getestet
werden.
Quellen:
• MySQL - Einfьhrung, Anwendung, Referenz, 1. Auflage
Autor: Michael Kofler
• http://www.torsten-horn.de/techdocs/postgresql.htm
• Marsch J., Fritze J.: SQL: Eine praxisorientierte Einführung
• http://www.physik.uni-stuttgart.de
• http://www.mysteries-megasite.com/linux/SQL-tutorial.html
SQL Tutorial Links
• http://www.mysql.com/
MySQL Dokumentation, Download, Tools
• http://www2.little-idiot.de/mysql/
MySQL Datenbankhandbuch
• MySQL Datenbankhandbuch
Guido Stepken ( [email protected])
Datenbankadministration mit MySQL
•
•
•
•
Datensicherheit
Wartung von Tabellen
Replikationsdatenbanken
Datenmigration, Import und Export
• Beispiel (PHP und MySQL)
Konstantin Altenhof
Dateiebene
Speicherung der Daten auf Dateiebene
mysql
MS-SQL-Server
Datensicherheit
• Prävention mit Metadaten
• Zeitangabe
Es ist eine gute Angewohnheit, in jeder Tabelle ein Feld mit dem
Datentyp TIMESTAMP einzurichten.
• TIMESTAMP wird von MySQL automatisch erzeugt
• Die Feldbreite durch die Deklaration bestimmt
–
–
–
–
–
TIMESTAMP[14]: YYYYMMDDHHMMSS
TIMESTAMP[12]: YYYYMMDDHHMM
TIMESTAMP[10]: YYYYMMDDHH
TIMESTAMP[8]: YYYYMMDD
TIMESTAMP[6]: YYMMDD
• Beispiel aus Praxis.
Datensicherheit
• Benutzerdaten
– Für Stammdaten häufig überlegenswert.
• Benutzer zu ermitteln unter MySQL
– USER()
Beispiel:
INSERT INTO Mitglieder (Name, Vorname, Erfasst_von)
VALUES (`Schmidt`, `Beate`, USER() );
oder
UPDATE Mitglieder SET NAME=`Bayer`, Erfasst_von=
USER();
Bemerkung: Im Web – IP-Adresse (PHP Funktion REMOTE_ADDR)
Datensicherheit
• Prüfziffern
– Aufbau der Tabelle:
CREATE TABLE kd_konten (
Kunde
CHAR(8) – Fremdschlüssel
Kto_nr
CHAR(8)
Blz
CHAR(10)
Metha_when TIMESTAMP
Pruefziffer
BIGINT );
Beispiel:
SET
@Kunde
=`wischmil`,
@Kto_nr
=`22222222222`,
@Blz =`4420442044`,
@Pruef =MD5(CONCAT(@Kunde,@Kto_nr,@Blz);
INSERT INTO kd_konten VALUES (
@Kunde, @Kto_nr, @Blz, NULL, CONV(@Pruef,16,10);
Identität abgleichen, ohne die eigentlichen Daten zu kennen.
Daten aus anderen Datenbeständen übernehmen.
Datensicherheit
• Vollständige Datensicherung
– Mysqldump
– Mysqlhotcopy
Wer, wann, wie?
– Automatisieren
– Einmal die Woche ist eine gute Faustregel
– Speichern Sie gesicherte Daten immer auf
einem anderem Datenträger (Tipp: Keine
Disketten verwenden).
Datensicherheit
• Sichern mit mysqldump
– Erzeugt eine Datei, die SQL-Befehle enthält um die Datenbank
mit allen Tabellen und Indizes wieder zu erzeugen und mit Daten
zu fühlen.
mysqldump Datenbankname >Backupdatei.sql
mysqldump Db_1 Db_2 Db_3 >Backupdatei.sql
Schalter:
- - password=xxx (Sicherheitslücke: Benutzerpasswort)
- - - all-databases (aber …..)
- - - add-drop-table (DROP TABLE, CREATE TABLE)
- - - add-locks
(LOCK TABLE, INSERT, UNLOCK)
- - - extended-insert (nicht empfehlenswert, Syntax MySQLspezifisch)
- - - complete-insert (ANSI-konform, Gegenstück zu –extendedinsert)
Datensicherheit
• Sicherung unter UNIX automatisieren
– Cron-jobs.
Aufgabe: Jeden Sonntag um 3 Uhr morgens ein Backup
der Datenbank Kundenservice automatisch erstellen.
Lösung:
1. Textdatei mit folgendem Inhalt anlegen:
0 3 * * 0 mysqldump Kundenservice \\
>/home/db_admin/Backups/ kundenservice.sql
Die Datei speichern unter meine_cron_jobs.txt.
2. In einem Terminalfenster eingeben:
cronjob meine_cron_jobs.txt
Datensicherheit
• Sichern einzelner Tabellen
BACKUP TABLE tabellenname TO `/home/mein_zuhaus/bak`;
Bei diesem Vorgang fordert MySQL einen Read Lock an, kopiert Dateien
tabellenname.frm und tabellenname.myd (Indexfiles werden neue
generiert).
RESTORE TABLE tabellenname FROM `home/mein_zuhaus/bak`;
WICHTIG: Vorher eventuell vorhandenen Dateien
tabellenname.MYD
tabellenname.MZI
tabellenname.frm
Datensicherheit
• Sichern mit mysqlhotcopy
Perlskript, Kopieren von Datenbanken im laufenden
Betrieb.
Der unterschied zu mysqldump ist, dass dieses
Programm die Datenbankdateien kopiert und nicht etwa
eine SQL-Datei generiert.
Vorteil – Backups wesentlich weniger Zeit benötigen.
Nachteil – kopierten Dateien sind plattformabhängig und
versionsabhängig.
Datensicherheit
•
Inkrementelle Strategien
- Veränderungen an der Tabelle aufzeichnen.
- Vollständige Backups restaurieren und
anschließend mit den inkrementellen Backups
vervollständigen.
Datensicherheit
•
Logging-Dateien
Skript einlegen und als Cron-Job täglich ausführen:
mv hostname-bin.001 hostname-bak.001
mysqladmin flusch-logs
cp hostname-bak.001 /usr/local/backups/mysql/
Nachteil: alle Änderungen aller Datenbanken.
Bemerkung: mysqlbinlog wandelt Binärformat in einen
Textformat um.
Datensicherheit
•
Restaurieren von Datenbanken
Zurücklesen von SQL-Dateien
Die Dateien die mit mysqldump erstellt wurden, erhalten SQL-Statements, die ausgeführt
werden müssen.
Erste Möglichkeit:
Starten Sie SQL-Monitor mit
mysql –u root
Mit der SQL-Anweisung SOURCE die Backupdatei einlesen:
SOURCE /usr/local/backups/aktuellesbackup.sql
Zweite Möglichkeit:
SQL-Datei direkt auf dem Kommandozeile zu übergeben:
mysql – u root </ usr/local/backups/aktuellesbackup.sql
Datensicherheit
•
Restaurieren von Datenbanken
Logfiles einlesen
Dafür müssen diese aber zuerst in eine Textdatei mit SQLBefehl konvertiert werden:
mysqlbinlog –short-form \\
/usr/local/backups/log_02122004-bin>recover.sql
Anschließend die Datei recover.sql mit einer der
beschriebenen Methoden in System einlesen.
Wartung von Tabellen
•
SQL-Befehle zur Wartung von Tabellen
•
•
•
•
ANALIZE TABLE
CHECK TABLE
OPTIMIZE TABLE
REPAIR TABLE
Wartung von Tabellen
CREATE TABLE top_secret(
very_secret varchar(120));
INSERT INTO top_secret VALUES(
‚Dies ist eine geheime Botschaft, die unter keinen Umständen länger als
unbedingt notwendig gespeichert werden soll`);
DELETE FROM top_secret WHERE very_secret LIKE ‚Dies%‘;
Wartung von Tabellen
•
ANALIZE TABLE
ANALIZE TABLE Tabellenname_1, Tabellenname_2,…;
Der Befehl analysiert und reorganisiert die Indizes der
jeweiligen Tabellen und gibt Information über aktuellen
Zustand der Tabelle aus.
•
CHECK TABLE
CHECK TABLE Tabellenname_1, Tabellenname_n [QUICK
| FAST | MEDIUM | EXTENDED];
Zustand der Tabelle überprüfen und eine Diagnose
erstellen.
Wartung von Tabellen
•
OPTIMIZE TABLE
OPTIMIZE TABLE Tabellenname_1, Tabellenname_2,…;
Mit OPTIMIZE TABLE werden aus einer Tabelle gelöschte
Daten auch physikalisch entfernt.
•
REPAIR TABLE
REPAIR TABLE Tabellenname_1, Tabellenname_n [QUICK
| EXTENDED];
Hiermit lassen sich korrupte Tabellen reparieren. Wenn
Wiederherstellen nicht gelingt, versuchen Sie es mit
myisamchk -o.
Wartung von Tabellen
•
myisamchk und isamchk
myisamchk
Schalter:
-r
-recover – Stellt praktisch jede beschädigte Tabelle wieder her. Die
einzige Fehler, die mit dieser Methode nicht beseitigt werden kann, sind mehrer
gleiche Werte in einem eindeutigen Index, was allerdings ein sehr
unwahrscheinlicher Fall ist.
-o
•
-safe-recover – Ist wesentlich langsamer als –recover.
myisampack
Damit könnten gepackte Tabellen erzeugt werden. Beim Packen der Tabellen wird jede
Spalte für sich gepackt (es wird für jede Spalte getrennt einen Huffmann-Baum
angelegt, mit dem anschließend die Daten codiert werden), was Dateigröße
erheblich reduziert.
Einsatz in folgenden Fällen:
–
–
–
Wenn die Datensätze nie oder höchst selten geändert werden.
Wenn eine harte Kopie einer Tabelle als Backup erstellt wird.
Wenn die Daten für nur-lesende Aplikationen auf CD-ROM abgelegt und ausgeliefert
werden (Wörterbuch, Lernsoftware)
Replicationsdatenbanken
Um ein möglichst ausfallsicheres System einzurichten, bietet sich die
Möglichkeit, eine oder mehrere Replikationsdatenbanken anzulegen.
MySQL synchronisiert dann eine oder mehrere Slave-Datenbanken mit
einem Master-Datenbank
Außerdem bietet ein Slave in einem Produktionssystem die Möglichkeit,
Backups nicht vom Master, sondern von einem Slave zu machen. Der
Slave kann zu diesem Zweck ganz heruntergefahren, das
Datenverzeichnis komprimiert und auf Band oder CD-Rom kopiert
werden.
In der von MySQL verwendeten Technologie kann auf den Master lesend und
schreibend zugegriffen werden, während von den Slaves nur gelesen
werden kann.
Ein typischer Einsatz einer Replikationsdatenbank ist die eines Internetstores.
Da über das Internet hauptsächlich lesend zugegriffen wird, werden die
Daten von einem Slave zur Verfügung gestellt. Der Master läuft dann auf
einem Server, der gar nicht vom Internet aus erreichbar ist.
Replicationsdatenbanken
MASTER-SEVER
User mit File-Rechte einlegen
GRANT FILE ON *.* TO repl@“%“ IDENTIFIED BY `XXX`;
In my.ini bzw. my.cnf im Abschnitt [mysqld] einen Server-ID eintragen.
server-id=1
Diesen Eintrag muss später auch bei den Slaves gesetzt werden, wobei jeder der Server
eine andere Nummer bekommt.
Slave-Server
Folgende einträge in der Konfigurationsdatei my.ini bzw. my.cnf vornehmen.
•
master-host.
•
master-user
•
master-passwd.
•
master-port.
Replicationsdatenbanken
Einen Slave zum Master machen
Slave-Server herunterfahren.
Folgende Parameter in dem Bereich [mysqld] in
der Datei my.ini bzw. my.cnf auskommentieren
und Server-ID auf die den alten Master setzen.
•
•
•
•
master-host.
master-user
master-passwd.
master-port.
Datenmigration, Import und Export
•
Dateien als Textdatei aus Access exportieren und in MySQL importieren.
Mit mysqlimport werden die Datensätze aus einer Textdatei importiert.
Beispiel:
mysqlimport Personalverwaltung c:\temp\fuhrpark.txt
(Vorausgesetzt, dass die Tabelle Fuhrpark in der Datenbank Personalverwaltung
bereits besteht).
Optionen:
- fields-terminated-by (Trennung zwischen Spalten)
- lines-terminated-by (gibt Zeichen an, mit denen eine Zeile abgeschlossen
wird)
- fields-escaped-by (Markierung für Sonderzeichen)
Bespiel:
mysqlimport –local „-fields-terminated-by:“ „lines-terminated-by=\r\n“ exceptions
c:\temp\Fuhrpark.txt
Datenmigration, Import und Export
Import der Daten mit LOAD DATA INFILE
Auch das Importieren von Daten direkt mit einem SQL-Befehl ist
möglich.
Der SQL-Befehl würde dann folgendermaßen lauten:
LOAD DATA INFILE “c:\temp\fuhrpark.txt“ INTO TABLE
Personalverwaltung.Fuhrpark;
oder für lokalen Datenbank
LOAD DATA LOCAL INFILE “c:\temp\fuhrpark.txt“ REPLACE INTO
TABLE Fuhrpark;
Import der Daten unter MySQL-Front
Im MySQL-Editor MySQL-Front habe Sie eine menügeführte
Import- und Exportfunktion für Daten, die im Text- oder CSVFormat vorliegen.
Datenmigration, Import und Export
Daten als Textdatei aus MySQL exportieren
Mysql –u root –password=xxx –batch „execute=SELECT * FROM
fuhrpark:“ Personalverwaltung > export.txt
Daten als html aus MySQL exportieren
Mysql –u root –password=xxx –html „execute=SELECT * FROM
fuhrpark:“ Personalverwaltung > export.html
Daten als Textdatei aus MySQL exportieren (Vertikale
Datendarstellung)
Mysql –u root –password=xxx –vertical „execute=SELECT * FROM
fuhrpark:“ Personalverwaltung > export_vertikal.txt
Beispiel
Beispiel
Datenmigration, Import und Export
if($interpret!="")
{
if($jahr=="")$jahr="NULL";
mysql_query("INSERT INTO cds
(titel,interpret,jahr)
VALUES('$titel','$interpret',$jahr);");
}
Beispiel
Beispiel
Datenmigration, Import und Export
if($action=="del")
{
mysql_query("DELETE FROM cds
WHERE id=$id;");
}
Beispiel
Beispiel
Beispiel
Quellen:
1.
2.
3.
4.
Das große Buch MySQL, DATA BECKER, Markus Barth und
Günther Karl
MySQL/PHP Datenbankanwendungen, MITP, Jay Greenspan,
Brad Bulger
PHP 4 (Umfassend zur MySQL –Programmierung, HANSER,
Jörg Krause
www.mysql.com
http://www.mysql.com/
MySQL Dokumentation, Download, Tools