Upload
lenz-grimmer
View
3.497
Download
2
Embed Size (px)
DESCRIPTION
Slides of my presentation about MySQL Backup & Security (in german) at the SLAC09 in Berlin, Germany on 2009-12-10.
1
Sun Microsystems
Methoden zur Absicherungund Datensicherung eines MySQL-Servers
Lenz GrimmerMySQL Community Relations Manager<[email protected]> | @lenzgrhttp://lenzg.net/
2
Übersicht
• MySQL-Absicherung> Integrierte Funktionalität> Weitere Werkzeuge> Betriebssystem-Ebene
• MySQL Datensicherung> Physikalisch vs. logisch> Methoden> Werkzeuge
3
$ whoami
1998 2002
2010?2008
4
MySQL-Absicherung
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
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
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
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
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
10
Tipps zur Benutzerverwaltung
• Wichtige Kommandos> SHOW GRANTS> SET PASSWORD> GRANT/REVOKE> DROP USER
• PROCESS/SUPER/FILE -Privilegien minimieren
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
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
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
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
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
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
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
18
Datensicherung
19
Datensicherung
• Notwendigkeit> Hardware-Ausfall> Anwender- oder Applikationsfehler
• Zu sichernde Daten> Datenbankinhalte> Log-Dateien
• Weitere Aspekte> Sicherungszeitpunkt> Ort der Sicherung und Aufbewahrung
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
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
22
Zeitpunkt der Datensicherung
• Regelmäßig• Außerhalb der Lastspitzen• Wenig veränderliche Daten weniger häufig
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
24
Modulare Speicher-Engine-Architektur
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
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]
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
28
Sicherungsmethoden
• logisch: SQL-Anweisungen• physikalisch: Tabellendateien• vollständig vs. inkrementell
> Aktivieren des Binärlogs> Zeitpunktbezogene Wiederherstellung
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
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
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)
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
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
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
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
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)
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
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/
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?)
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
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
42
Vergleich der Sicherungsmethoden
• Portabilität (SQL Dumps vs. Tabellendateien)
• Geschwindigkeit, Speicherbedarf• Overhead und Beeinträchtigung des
Betriebs
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
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!
45
Vielen Dank!
Fragen, Kommentare, Anregungen?Lenz Grimmer <[email protected]>
http://www.lenzg.net/Twitter: @lenzgr