MariaDB-Kurs / Abschlussprojekt: Bestelldatenbank, Rechte, Transaktion und Wiederherstellung
Diese Abschlussübung erstellt eine Bestelldatenbank, prüft eine gemeinsame Bestellungs- und Bestandsänderung mit einem eingeschränkten Konto und sichert anschließend den bestätigten Zustand. Die Wiederherstellung erfolgt in einer getrennten Datenbank. Struktur, fachliche Änderungen, Rechte und Wiederherstellung benötigen jeweils eigene Prüfungen; eine allgemeine SQL-Erfolgsmeldung genügt dafür nicht.
Kontenaufgaben und beobachtbare Abschlusskriterien festlegen
Verwenden Sie die neue Übungsdatenbank final48_lab, getrennt von echten Bestellungs- oder Zahlungsdiensten. Erstellen Sie Kunden, Produkte, Bestellungen und Positionen und bestätigen Sie Bestellung 105 vor der Sicherung. Trennen Sie Verwaltungskonto, strukturverwaltendes Lernkonto und eingeschränktes Anwendungskonto. Ein echtes Programm benötigt zusätzlich Eingabeprüfung, Parameterbindung, Authentifizierung und eine Verarbeitung, die den Effekt einer wiederholten Anfrage nicht verdoppelt.
Die Übung zu InnoDB-Transaktionen erläutert Bestätigung und Rücknahme. Die Übung zu Benutzerkonten und Rechten zeigt die Prüfung abgewiesener Operationen mit dem richtigen Konto.
| Bereich | Abschlussprüfung |
|---|---|
| Struktur | Vier InnoDB-Tabellen mit Referenzen und Eingabebedingungen |
| Neue Bestellung | Produkt 1: Bestand 20 → 18; Warenbetrag von Bestellung 105: 7000 |
| Rechte | Benötigte Lesezugriffe, Einfügungen und Bestandsänderungen |
| Wiederherstellung | Andere leere Datenbank: Zeilenzahlen 4, 4, 5 und 7 |
| Abgleich | Bezahlter Warenbetrag 46000; gleiche Zeilen und Bedingungen |
Eine eigene Datenbank mit vollständigem Ausgangsbestand erstellen
Prüfen Sie in einer Verwaltungsverbindung zum Übungsserver SELECT VERSION();, SHOW DATABASES und die Konten. Stellen Sie sicher, dass final48_lab, final48_restore und 'final48_app'@'127.0.0.1' unbenutzt sind. Wählen Sie bei vorhandenen Namen eigene Übungsnamen und passen Sie alle Blöcke an, statt bestehende Objekte zu überschreiben oder zu entfernen.
CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Koreanische Namen und Ortswerte bleiben erhalten. Produkte 1–4 stehen für Notizbuch, Kugelschreiber, Becher und Schreibtischlampe. Die Ortswerte der Kunden 1 und 3 bedeuten Seoul, der von Kunde 2 Busan und der von Kunde 4 Daejeon. Bewahren Sie die UTF-8-Zeichenfolgen für die Ergebnisprüfung.
Vergeben Sie gegebenenfalls über die Verwaltungsverbindung Datenbankrechte für das Lernkonto course_user. Vorhandene Rechte für shopcourse gelten nicht automatisch für diese neue Datenbank.
GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';
Der Ausgangsbestand enthält vier Kunden, vier Produkte, vier Bestellungen und sechs Positionen. Prüfen Sie mit SHOW CREATE TABLE die Fremdschlüssel und Mengen- sowie Preisbedingungen. Aktueller Katalogpreis und unit_price, der für eine Position gespeicherte Kaufpreis, beschreiben unterschiedliche Fakten.
Mit einem auf den Ablauf begrenzten Konto verbinden
Ersetzen Sie als Administrator das Demonstrationspasswort durch einen eigenen eindeutigen Wert und legen Sie das folgende Konto an. Es benötigt Produktabfragen, neue Bestellungen und Positionen sowie Bestandsänderungen. Strukturentfernung, Kontenverwaltung, Preisänderung und Löschen von Bestellungen gehören nicht zum Ablauf. Prüfen Sie SHOW GRANTS privat, da die Ausgabe Authentifizierungsinformationen enthalten kann.
CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';
Öffnen Sie im Betriebssystemterminal eine neue Verbindung mit diesem Konto und geben Sie das Passwort an der Abfrage ein. Passen Sie den Port gegebenenfalls an:
mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab
Prüfen Sie im Client SELECT CURRENT_USER(); und eine Produktabfrage. Eine nicht freigegebene ALTER-TABLE-Operation muss abgewiesen werden. Begrenzen Sie betrieblichen Zugriff anhand der tatsächlich benötigten Tabellen, Spalten und Aufgaben weiter. SELECT für die gesamte Übungsdatenbank ist hier eine ausdrückliche Entscheidung. Die offizielle GRANT-Referenz beschreibt die Geltungsbereiche.
Bestand, Bestellung und Position gemeinsam prüfen und bestätigen
USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;
Führen Sie den folgenden Block nur aus, wenn reserved_rows gleich 1 ist. Bei null betroffenen Zeilen oder einem Fehler führen Sie in dieser Verbindung ROLLBACK; aus und legen weder Bestellung noch Position an. Die Bedingung verhindert die Reservierung bei unzureichendem Bestand; die Zeilensperre bleibt innerhalb der Transaktion bestehen. Prüfen Sie ROW_COUNT unmittelbar nach der Änderung, wie im Block gezeigt.
INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;
Mit unveränderten Ausgangsdaten muss die Reservierung eine Zeile ändern und der Warenbetrag für Bestellung 105 7000.00 betragen. Schlägt eine Anweisung fehl, halten Sie vor weiteren Schritten an und nehmen Sie den ganzen Versuch zurück. Dies ist eine beaufsichtigte Übung mit bekannten Eingaben. Ein Programm muss die Verzweigungen selbst umsetzen und Werte als Treiberparameter übergeben.
Bestätigen Sie erst nach erfolgreichen Prüfungen:
COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;
Der Bestand von Produkt 1 beträgt jetzt 18; es gibt fünf Bestellungen und sieben Positionen. Eine Wiederholung mit Kennung 105 führt zu einem doppelten Schlüssel. Behandeln Sie wiederholte Anfragen ausdrücklich, statt den Fehler durch IGNORE oder REPLACE zu verdecken. Wurde bei einem fehlgeschlagenen Versuch bereits Bestand reserviert, muss ROLLBACK diese Reservierung vor der Wiederholung zurücknehmen. Ein späteres ROLLBACK macht die zuvor korrekt bestätigte Bestellung nicht rückgängig.
Den bestätigten Zustand sichern und ein getrenntes Ziel vorbereiten
Verwenden Sie für die Sicherung das entsprechend berechtigte Lernkonto course_user, nicht das eingeschränkte Anwendungskonto. Wählen Sie unter Windows PowerShell eine neue Datei, prüfen Sie den Exitcode und untersuchen Sie den Dump auf Datenbankwechsel. Der folgende Block gehört in das Betriebssystemterminal:
New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256
Erstellen Sie als Administrator die zuvor als unbenutzt bestätigte Wiederherstellungsdatenbank und vergeben Sie nur den benötigten Übungszugriff:
CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';
Verbinden Sie sich im Terminal mit dem Ziel und aktivieren Sie den Abbruch der SOURCE-Dateiverarbeitung bei Fehlern:
mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore
Prüfen Sie im Client Datenbank und leere Tabellenliste vor dem Import der vertrauenswürdigen Datei. Ist das Ziel bereits befüllt, halten Sie an.
SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;
Die Bedingungen für mariadb-dump gelten für konsistente InnoDB-Lesesichten und gleichzeitige Strukturänderungen. Die Übung zur Wiederherstellung und Prüfung erklärt Clientkompatibilität und Teilfehler. Ein Datenbankdump stellt nicht sämtliche Konten oder Servereinstellungen wieder her. Leiten Sie das Anwendungskonto nicht automatisch auf die Testwiederherstellung um.
Alternative für die Sicherung im Ubuntu-Terminal: Wählen Sie diesen Block oder die PowerShell-Variante. Die durch Klammern abgegrenzte Subshell beschränkt Dateirechte und Überschreibschutz auf diesen Vorgang.
mkdir -p "$HOME/mariadb-lab"
(
umask 077
set -C
backup_file="$HOME/mariadb-lab/final48-checked.sql"
if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
--user=course_user --password --default-character-set=utf8mb4 \
--single-transaction --quick --skip-lock-tables final48_lab > "$backup_file"; then
test -s "$backup_file" || exit 1
sha256sum "$backup_file"
else
printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
exit 1
fi
)
Ein vorhandenes Ziel muss zur Ablehnung führen, ohne dessen Inhalt zu überschreiben. Stellen Sie nach einem Fehler keine unvollständige Datei wieder her. Untersuchen Sie die Ursache und wählen Sie einen neuen Dateinamen für den nächsten Versuch. Dateigröße und SHA-256-Hash identifizieren die Datei, belegen aber keine Wiederherstellbarkeit.
Öffnen Sie bei der Wiederherstellung über Ubuntu dasselbe Ziel mit folgendem Aufruf. Verwenden Sie für SOURCE Ihren tatsächlichen absoluten Dateipfad. learner ist ein Beispielname:
mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore
SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;
Führen Sie je Ziel nur eine Wiederherstellungsvariante aus. Der SOURCE-Abbruch verhindert spätere Anweisungen der Datei, nimmt frühere Objekte und Änderungen jedoch nicht zurück. Korrigieren Sie nach einem Fehler die Ursache und verwenden Sie ein neu vorbereitetes leeres Ziel.
Prüfen Sie bei einem Fehler mit Transport Layer Security (TLS), der Verbindungsverschlüsselung, Versionen, Zertifikatsnamen, Zertifizierungsstelle und gegebenenfalls Clientzertifikate nach den offiziellen Verschlüsselungsanforderungen. Wenden Sie passende Einstellungen auf beide Werkzeuge an, ohne die Prüfung routinemäßig auszuschalten. Die mariadb-dump-Referenz beschreibt Optionen und Voraussetzungen.
Quelle, Wiederherstellung und abgewiesene Zugriffe vergleichen
SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
(SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
(SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
(SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;
Die erwarteten Zeilenzahlen sind 4, 4, 5 und 7, der bezahlte Warenbetrag beträgt 46000.00 und der Bestand von Produkt 1 ist 18. Vergleichen Sie anhand der Schlüssel auch sämtliche Feldwerte, Bedingungen und wichtige JOIN-Ergebnisse. Gleiche Zeilenzahlen oder ein erfolgreicher Import allein reichen nicht. Prüfen Sie getrennt die Ablehnung fehlender Referenzen, doppelter Schlüssel und nicht freigegebener Änderungen.
Bewahren Sie Versionen, Dateihash, Prüfungsabfragen und Ergebnisse sowie den Berechtigungsumfang auf. Schließen Sie Übungssitzungen und prüfen Sie Eigentum und Aufbewahrungsbedarf vor dem Entfernen von Konten oder Datenbanken. Diese Übung löscht Quelle, Sicherungsdatei und Prüfungsdatenbank nicht automatisch.




