of 45 /45
1 Sun Microsystems Methoden zur Absicherung und Datensicherung eines MySQL-Servers Lenz Grimmer MySQL Community Relations Manager <[email protected]> | @lenzgr http://lenzg.net/

Methoden zur Absicherung und Datensicherung eines MySQL-Servers

Embed Size (px)

DESCRIPTION

Slides of my presentation about MySQL Backup & Security (in german) at the SLAC09 in Berlin, Germany on 2009-12-10.

Citation preview

Page 1: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

1

Sun Microsystems

Methoden zur Absicherungund Datensicherung eines MySQL-Servers

Lenz GrimmerMySQL Community Relations Manager<[email protected]> | @lenzgrhttp://lenzg.net/

Page 2: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

2

Übersicht

• MySQL-Absicherung> Integrierte Funktionalität> Weitere Werkzeuge> Betriebssystem-Ebene

• MySQL Datensicherung> Physikalisch vs. logisch> Methoden> Werkzeuge

Page 3: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

3

$ whoami

1998 2002

2010?2008

Page 4: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

4

MySQL-Absicherung

Page 5: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

5

MySQL-Absicherung

• Erster Arbeitsschritt nach Neuinstallation• Sicherheit der Standardinstallation bereits

relativ hoch• Zusätzliche Sicherungsmaßnahmen des

Betriebssystems flankieren die im Server enthaltenen Funktionen

http://dev.mysql.com/doc/refman/5.0/en/security.html

Page 6: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

6

Benutzerkonten

• Kennwort für den root-User$ mysql ­u root mysqlmysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

• Entfernen des anonymen Benutzers• Entfernen der test-Datenbank

• Script: mysql_secure_installation

• Konten: nur die erforderlichen• Privilegien: nur die notwendigen• Check auf old_passwords

Page 7: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

7

MySQL Sicherheitsmodell

• Authentifizierung gegen 'user'@'host'• Fein granulierte Zugriffsrechte• Verschlüsselte Passworte in DB• Unterstützt keine Rollen oder Gruppen• Erzwingt keine Passwort-Standards• Obsolete Privilegien können bestehen

bleiben

Page 8: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

8

Prüfung der Zugriffsrechte

• Verbindungsaufbau> Server überprüft anhand der user-Tabelle, ob

ein passender Eintrag für username, host und passwort existiert

• SQL-Abfrage> Server überprüft Privilegien anhand der user, db, tables_priv and column_privs Tabellen

http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html

Page 9: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

9

USERGRANTED – Execute QueryDENIED – check db Table

DBGRANTED – Execute QueryDENIED – check tables_priv Table

TABLEGRANTED – Execute QueryDENIED – check columns_priv Table

MySQL SicherheitsmodellHierarchische Prüfung der Privilegien

COLUMNSGRANTED – Execute QueryDENIED – BLOCK Query

Page 10: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

10

Tipps zur Benutzerverwaltung

• Wichtige Kommandos> SHOW GRANTS> SET PASSWORD> GRANT/REVOKE> DROP USER

• PROCESS/SUPER/FILE -Privilegien minimieren

Page 11: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

11

Securich

• http://www.securich.com/• Implementiert als Stored Routines

(ab MySQL 5.0)• Bietet Rollen & Benutzergruppen• Klonen von Benutzerkonten• Flexible Zuteilung von Zugriffsrechten• Verbesserte Behandlung von Passworten

(Länge, Komplexität, Historie, Ablauf)• Schnelles Blockieren von Konten

Page 12: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

12

Auditing mit oak-security-audit

• Shlomi Noach's Openark kit• http://code.openark.org/forge/openark-kit/• oak-security-audit

• Regelmäßige, automatische Überprüfung> Nicht-lokale root-Konten> Anonyme Benutzerkonten> Konten ohne Hostname> Konten mit leeren oder identischen

Passworten> Nicht-root Benutzer mit vollen Privilegien

Page 13: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

13

Sicherheitsfunktionen

• Nützliche Optionen in my.cfg:> bind-address – lauscht nur am einem TCP-

Interface (z.B. 127.0.0.1)> skip-networking – Kommunikation nur

lokal (via socket-Datei)> secure_auth – Erfordert 4.1

Authentifizierung> max_connection_errors – blockiert bei zu

vielen fehlerhaften Verbindungsversuchen

Page 14: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

14

Weitere Hinweise

• Keine Benutzerkennwörter im Klartext in Tabellen speichern

• MD5() oder SHA1(), nicht PASSWORD()

• Verschlüsselung (SSL, SSH, VPN)• LOAD DATA LOCAL deaktivieren:--local-infile=0

• Nie mysqld als root-Benutzer ausführen

• History-Datei ~/.mysql_history absichern oder löschen

• MySQL root-User umbenennen

Page 15: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

15

Views & Stored Procedures

• VIEWs können Zugriff auf bestimmte Spalten regeln> http://dev.mysql.com/doc/refman/5.0/en/views.html

• Stored Procedures schirmen die realen Tabellen vor direkten Zugriffen durch Anwender und Applikationen ab> http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

• Seit MySQL 5.0 enthalten

Page 16: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

16

Absicherung auf OS-Ebene

• Zugriff auf das Datenverzeichnis beschränken (chown/chmod)> Tabellen und Log-Dateien schützen

• Keine Shell-Konten auf dem DB-Server• Kein direkter Zugriff auf Port 3306 aus

dem Internet!• Firewall/DMZ/iptables• SELinux, AppArmor, RBAC• chroot(), Zones/Container, VMs

Page 17: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

17

Absicherung von Daten und Kommunikation

• Verschlüsselung der Kommunikation> OpenSSL> SSH tunnel> OpenVPN> Cipe

• Verschlüsselung Daten-Verzeichnis> cryptoloop devices> dm_crypt kernel module

Page 18: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

18

Datensicherung

Page 19: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

19

Datensicherung

• Notwendigkeit> Hardware-Ausfall> Anwender- oder Applikationsfehler

• Zu sichernde Daten> Datenbankinhalte> Log-Dateien

• Weitere Aspekte> Sicherungszeitpunkt> Ort der Sicherung und Aufbewahrung

Page 20: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

20

Wann werden Sicherungen benötigt?

• Datenverlust durch Hardwarefehler> Absturz wg. Hardwareschaden> Festplattenausfall> Defekte Hardware

• Anwender- und Applikationsfehler> DROP TABLE oder DELETE FROM ohne WHERE-Klausel

> Development vs. Production DB> Öffnen der Tabellendateien mit der falschen

Anwendung

Page 21: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

21

Was sollte gesichert werden?

• Datenbankinhalte> Für komplette Sicherungen> Logisch oder phyikalisch

• Log-Dateien> Für inkrementelle Sicherungen> Wiederherstellungszeitpunkte

(Point in time recovery)

• Konfigurationsdateien> /etc/my.cnf> Cron-Jobs

Page 22: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

22

Zeitpunkt der Datensicherung

• Regelmäßig• Außerhalb der Lastspitzen• Wenig veränderliche Daten weniger häufig

Page 23: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

23

Speicherung der Sicherungskopien

• Auf dem DB-Server> Besser nicht!> Zumindest auf einem separaten

Dateisystem/Volume oder Laufwerk

• Kopiert auf einen anderen Server> Onsite oder offsite

• Sicherung/Archivierung auf Band/Wechselplatte

• An verteilten Orten

Page 24: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

24

Modulare Speicher-Engine-Architektur

Page 25: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

25

MySQL Datenverzeichnis

• Alle Datenbanken und Logfiles werden standardmäßig hier gespeichert

• Ort abhängig von der MySQL distribution (einkompilierter Wert):> /usr/local/mysql/data (tarball)> /var/lib/mysql (RPM)

• Mit --datadir=/pfad/zum/datadir anpaßbar

• SHOW VARIABLES LIKE 'datadir';

• InnoDB: innodb_data_home_dir

Page 26: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

26

Das Binärlog

• Speichert alle datenverändernden SQL-Anweisungen (DML) oder die geänderten Zeilen

• Zweck:> Erleichtert Datenwiederherstellung> Replikation

• Enthält zusätzliche Informationen (Zeitstempel, Laufzeit)

• Binär codiert, mysqlbinlog zum decodieren

• Aktiviert mit --log-bin[=datei]

Page 27: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

27

Log-Management

• Server rotiert die Logs• Log-Indexdatei verzeichnet alle Logs• SHOW MASTER LOGS – listet alle auf dem

Server vorhandenen logs• FLUSH LOGS – rotiert logs

• RESET MASTER – löscht alle binärlogs

• PURGE MASTER – löscht alle binärlogs bis zu einem best. Zeitpunkt

Page 28: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

28

Sicherungsmethoden

• logisch: SQL-Anweisungen• physikalisch: Tabellendateien• vollständig vs. inkrementell

> Aktivieren des Binärlogs> Zeitpunktbezogene Wiederherstellung

Page 29: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

29

Gängige MySQL-Sicherungspraktiken

• mysqldump> Vollständige Sicherung$ mysqldump mydb > mydb.20091210.sql

> Struktur und/oder Daten als SQL-Anweisungen: CREATE TABLE, INSERT

> Einzelne Tabellen oder Datenbanken möglich> portabel, aber unhandlich bei großen

Datenmengen

• Mit anderen Unix-Werkzeugen kombinierbar (Piping)$ mysqldump --opt world | mysql -h remote.host.com world

Page 30: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

30

Sicherung von InnoDB-Tabellen

• mysqldump --single-transaction erstellt konsistente Sicherungskopie ohne Locking

• Physikalische Sicherung> MySQL-Server herunterfahren!> Datenfiles, InnoDB log-Dateien, .frm-Dateien

sichern> Server wieder starten

Page 31: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

31

mysqldump - Tipps

• --lock-all-tables – nützlich für konsistente MyISAM-Backups> Aber sperrt alle DML-Anweisungen

• --flush-logs – synchronisiert das Binärlog (Checkpointing)

Page 32: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

32

XtraBackup / Maatkit

• https://launchpad.net/percona-xtrabackup• Online-Backup für InnoDB / MyISAM• 2009-12-10: Version 1.0 (incl. Windows)• In my.cnf:

> [xtrabackup]target_dir = /home/backups

• Backup-Kommando:> xtrabackup –backup

Page 33: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

33

Maatkit

• http://maatkit.org/• Perl Script-Kollektion• Multi-threaded SQL dumps

> mk-parallel-dump / mk-parallel-restore

• Ein Unterverzeichnis pro DB• Backups können „gestückelt“ werden• Binlog-Position wird mitgesichert• Reguläre Ausdrücke zur Selektion

Page 34: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

34

mydumper

• https://launchpad.net/mydumper• C-Programm (glib, pcre)• Parallele Daten-Dumps• Leicht zu verarbeitende Ausgabe• Reguläre Ausdrücke• Konsistenz

> Snapshot> Master/Slave Binlog-Positionen

Page 35: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

35

Weitere Sicherungsmöglichkeiten

• Replikation> Sicherung erfolgt auf Slave> Bonus: erhöhte Verfügbarkeit

• Dateisystem-Snapshots> „semi-hot“> Linux: LVM (mylvmbackup)> Solaris: ZFS (mysql-snapback)

• MySQL 6.0: Online Backup APIhttp://forge.mysql.com/wiki/OnlineBackup

Page 36: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

36

Backups über Dateisystem-Snapshots

• Bequeme und schnelle Lösung zur unterbrechungsfreien Sicherung vollständiger Datenbanken

• Geringer Platzbedarf des LVM-Snapshots (10-15% reichen üblicherweise aus)

• Backup der Dateien auf dem Snapshot Volumen mit beliebigen Tools

• Beeinträchtigung der I/O Performance(Linux LVM)

Page 37: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

37

Linux LVM Snapshot-Erzeugung

Funktionsprinzip:

mysql> FLUSH TABLES WITH READ LOCK$ lvcreate -s –-size=<size> --name=backup <LV>mysql> UNLOCK TABLES$ mount /dev/<VG>/backup /mnt$ tar czvf backup.tar.gz /mnt/*$ umount /mnt$ lvremove /dev/<VG>/backup

Page 38: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

38

Das mylvmbackup-Script• Script zur schnellen Erzeugung von MySQL-

Backups mit LVM-Snapshots

• Snapshots werden in ein temporäres Verzeichnis eingehängt, die Daten werden mit tar,rsync oder rsnap gesichert

• Archivnames mit Zeitstempeln ermöglichen wiederholte Backup-Läufe ohne Überschreiben

• Kann vor dem Backup InnoDB-Wiederherstellung auf dem Snapshot durchführen

• Benötigt Perl, DBI and DBD::mysql

• http://www.lenzg.net/mylvmbackup/

Page 39: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

39

Werkzeuge

• Shell: cp, tar, cpio, gzip, zip, cron• rsync, unison, rsnapshot, rdiff• afbackup, Amanda/Zmanda, Bacula• Nicht auf live-Daten anwenden!

(ma.gnolia.com anyone?)

Page 40: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

40

Wiederherstellung

• Letzte vollständige Sicherungskopie (+ binäre Logdatei)

• Einspielen des SQL-Dumps oder Kopieren der gesicherten Tabellendateien

• Wiederherstellung eines bestimmten Zeitpunkts (point-in-time recovery) durch Zeitstempel im Binärlog möglich

Page 41: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

41

Beispiel Wiederherstellung

• Letzte vollständige Sicherung einspielen:$ mysql < backup.sql

• Einspielen der inkrementellen Änderungen seit der letzten vollständigen Sicherung:$ mysqlbinlog hostname-bin.000001 | mysql

Page 42: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

42

Vergleich der Sicherungsmethoden

• Portabilität (SQL Dumps vs. Tabellendateien)

• Geschwindigkeit, Speicherbedarf• Overhead und Beeinträchtigung des

Betriebs

Page 43: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

43

Sicherungsstrategien

• Regelmäßige Durchführung• Binärlog aktivieren• Log-Dateien synchronisieren (FLUSH

LOGS)• SQL-Dumps konsistent und verständlich

benennen (z.B. mit Zeitstempel im Dateinamen)

• Aufbewahrung der Sicherungen auf anderen Dateisystemen

Page 44: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

44

Generelle Backup-Hinweise

• Binärlogs auf einem anderen Laufwerk/Dateisystem ablegen> Verbesserte Performance> Vermeidet vollständigen Datenverlust

• Backups auf Vollständigkeit/Korrektheit überprüfen

• Prozeduren und Zeitpläne für Backups und Wiederherstellung festlegen

• Testen, ob sie auch wirklich funktionieren!

Page 45: Methoden zur Absicherung und Datensicherung eines MySQL-Servers

45

Vielen Dank!

Fragen, Kommentare, Anregungen?Lenz Grimmer <[email protected]>

http://www.lenzg.net/Twitter: @lenzgr