Methoden zur Absicherung und Datensicherung eines MySQL-Servers Lenz Grimmer MySQL Community Relations Manager | @lenzgr http://lenzg.net/ Sun Microsystems 1

bersicht MySQL-Absicherung > Integrierte Funktionalitt > Weitere Werkzeuge > Betriebssystem-Ebene MySQL Datensicherung > Physikalisch vs. logisch > Methoden > Werkzeuge 2

$ whoami 1998 2002 2008 2010? 3

MySQL-Absicherung 4

MySQL-Absicherung Erster Arbeitsschritt nach Neuinstallation Sicherheit der Standardinstallation bereits relativ hoch Zustzliche Sicherungsmanahmen des Betriebssystems flankieren die im Server enthaltenen Funktionen http://dev.mysql.com/doc/refman/5.0/en/security.html 5

Benutzerkonten Kennwort fr den root-User $ mysql -u root mysql mysql> 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 6

MySQL Sicherheitsmodell Authentifizierung gegen 'user'@'host' Fein granulierte Zugriffsrechte Verschlsselte Passworte in DB Untersttzt keine Rollen oder Gruppen Erzwingt keine Passwort-Standards Obsolete Privilegien knnen bestehen bleiben 7

Prfung der Zugriffsrechte Verbindungsaufbau > Server berprft anhand der user-Tabelle, ob ein passender Eintrag fr username, host und passwort existiert SQL-Abfrage > Server berprft Privilegien anhand der user, db, tables_priv and column_privs Tabellen http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html 8

MySQL Sicherheitsmodell Hierarchische Prfung der Privilegien GRANTED Execute Query USER DENIED check db Table GRANTED Execute Query DB DENIED check tables_priv Table GRANTED Execute Query TABLE DENIED check columns_priv Table GRANTED Execute Query COLUMNS DENIED BLOCK Query 9

Tipps zur Benutzerverwaltung Wichtige Kommandos > SHOW GRANTS > SET PASSWORD > GRANT/REVOKE > DROP USER PROCESS/SUPER/FILE -Privilegien minimieren 10

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 (Lnge, Komplexitt, Historie, Ablauf) Schnelles Blockieren von Konten 11

Auditing mit oak-security-audit Shlomi Noach's Openark kit http://code.openark.org/forge/openark-kit/ oak-security-audit Regelmige, automatische berprfung > Nicht-lokale root-Konten > Anonyme Benutzerkonten > Konten ohne Hostname > Konten mit leeren oder identischen Passworten > Nicht-root Benutzer mit vollen Privilegien 12

Sicherheitsfunktionen Ntzliche 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 13

Weitere Hinweise Keine Benutzerkennwrter im Klartext in Tabellen speichern MD5() oder SHA1(), nicht PASSWORD() Verschlsselung (SSL, SSH, VPN) LOAD DATA LOCAL deaktivieren: --local-infile=0 Nie mysqld als root-Benutzer ausfhren History-Datei ~/.mysql_history absichern oder lschen MySQL root-User umbenennen 14

Views & Stored Procedures VIEWs knnen 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 15

Absicherung auf OS-Ebene Zugriff auf das Datenverzeichnis beschrnken (chown/chmod) > Tabellen und Log-Dateien schtzen 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 16

Absicherung von Daten und Kommunikation Verschlsselung der Kommunikation > OpenSSL > SSH tunnel > OpenVPN > Cipe Verschlsselung Daten-Verzeichnis > cryptoloop devices > dm_crypt kernel module 17

Datensicherung 18

Datensicherung Notwendigkeit > Hardware-Ausfall > Anwender- oder Applikationsfehler Zu sichernde Daten > Datenbankinhalte > Log-Dateien Weitere Aspekte > Sicherungszeitpunkt > Ort der Sicherung und Aufbewahrung 19

Wann werden Sicherungen bentigt? 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 20

Was sollte gesichert werden? Datenbankinhalte > Fr komplette Sicherungen > Logisch oder phyikalisch Log-Dateien > Fr inkrementelle Sicherungen > Wiederherstellungszeitpunkte (Point in time recovery) Konfigurationsdateien > /etc/my.cnf > Cron-Jobs 21

Zeitpunkt der Datensicherung Regelmig Auerhalb der Lastspitzen Wenig vernderliche Daten weniger hufig 22

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 23

Modulare Speicher-Engine-Architektur 24

MySQL Datenverzeichnis Alle Datenbanken und Logfiles werden standardmig hier gespeichert Ort abhngig von der MySQL distribution (einkompilierter Wert): > /usr/local/mysql/data (tarball) > /var/lib/mysql (RPM) Mit --datadir=/pfad/zum/datadir anpabar SHOW VARIABLES LIKE 'datadir'; InnoDB: innodb_data_home_dir 25

Das Binrlog Speichert alle datenverndernden SQL- Anweisungen (DML) oder die genderten Zeilen Zweck: > Erleichtert Datenwiederherstellung > Replikation Enthlt zustzliche Informationen (Zeitstempel, Laufzeit) Binr codiert, mysqlbinlog zum decodieren Aktiviert mit --log-bin[=datei] 26

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 lscht alle binrlogs PURGE MASTER lscht alle binrlogs bis zu einem best. Zeitpunkt 27

Sicherungsmethoden logisch: SQL-Anweisungen physikalisch: Tabellendateien vollstndig vs. inkrementell > Aktivieren des Binrlogs > Zeitpunktbezogene Wiederherstellung 28

Gngige MySQL-Sicherungspraktiken mysqldump > Vollstndige Sicherung $ mysqldump mydb > mydb.20091210.sql > Struktur und/oder Daten als SQL- Anweisungen: CREATE TABLE, INSERT > Einzelne Tabellen oder Datenbanken mglich > portabel, aber unhandlich bei groen Datenmengen Mit anderen Unix-Werkzeugen kombinierbar (Piping) $ mysqldump --opt world | mysql -h remote.host.com world 29

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 30

mysqldump - Tipps --lock-all-tables ntzlich fr konsistente MyISAM-Backups > Aber sperrt alle DML-Anweisungen --flush-logs synchronisiert das Binrlog (Checkpointing) 31

XtraBackup / Maatkit https://launchpad.net/percona-xtrabackup Online-Backup fr InnoDB / MyISAM 2009-12-10: Version 1.0 (incl. Windows) In my.cnf: > [xtrabackup] target_dir = /home/backups Backup-Kommando: > xtrabackup backup 32

Maatkit http://maatkit.org/ Perl Script-Kollektion Multi-threaded SQL dumps > mk-parallel-dump / mk-parallel-restore Ein Unterverzeichnis pro DB Backups knnen gestckelt werden Binlog-Position wird mitgesichert Regulre Ausdrcke zur Selektion 33

mydumper https://launchpad.net/mydumper C-Programm (glib, pcre) Parallele Daten-Dumps Leicht zu verarbeitende Ausgabe Regulre Ausdrcke Konsistenz > Snapshot > Master/Slave Binlog-Positionen 34

Weitere Sicherungsmglichkeiten Replikation > Sicherung erfolgt auf Slave > Bonus: erhhte Verfgbarkeit Dateisystem-Snapshots > semi-hot > Linux: LVM (mylvmbackup) > Solaris: ZFS (mysql-snapback) MySQL 6.0: Online Backup API http://forge.mysql.com/wiki/OnlineBackup 35

Backups ber Dateisystem-Snapshots Bequeme und schnelle Lsung zur unterbrechungsfreien Sicherung vollstndiger Datenbanken Geringer Platzbedarf des LVM-Snapshots (10-15% reichen blicherweise aus) Backup der Dateien auf dem Snapshot Volumen mit beliebigen Tools Beeintrchtigung der I/O Performance (Linux LVM) 36

Linux LVM Snapshot-Erzeugung Funktionsprinzip: mysql> FLUSH TABLES WITH READ LOCK $ lvcreate -s -size= --name=backup mysql> UNLOCK TABLES $ mount /dev//backup /mnt $ tar czvf backup.tar.gz /mnt/* $ umount /mnt $ lvremove /dev//backup 37

Das mylvmbackup-Script Script zur schnellen Erzeugung von MySQL- Backups mit LVM-Snapshots Snapshots werden in ein temporres Verzeichnis eingehngt, die Daten werden mit tar,rsync oder rsnap gesichert Archivnames mit Zeitstempeln ermglichen wiederholte Backup-Lufe ohne berschreiben Kann vor dem Backup InnoDB- Wiederherstellung auf dem Snapshot durchfhren Bentigt Perl, DBI and DBD::mysql http://www.lenzg.net/mylvmbackup/ 38

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?) 39

Wiederherstellung Letzte vollstndige Sicherungskopie (+ binre Logdatei) Einspielen des SQL-Dumps oder Kopieren der gesicherten Tabellendateien Wiederherstellung eines bestimmten Zeitpunkts (point-in-time recovery) durch Zeitstempel im Binrlog mglich 40

Beispiel Wiederherstellung Letzte vollstndige Sicherung einspielen: $ mysql < backup.sql Einspielen der inkrementellen nderungen seit der letzten vollstndigen Sicherung: $ mysqlbinlog hostname-bin.000001 | mysql 41

Vergleich der Sicherungsmethoden Portabilitt (SQL Dumps vs. Tabellendateien) Geschwindigkeit, Speicherbedarf Overhead und Beeintrchtigung des Betriebs 42

Sicherungsstrategien Regelmige Durchfhrung Binrlog aktivieren Log-Dateien synchronisieren (FLUSH LOGS) SQL-Dumps konsistent und verstndlich benennen (z.B. mit Zeitstempel im Dateinamen) Aufbewahrung der Sicherungen auf anderen Dateisystemen 43

Generelle Backup-Hinweise Binrlogs auf einem anderen Laufwerk/Dateisystem ablegen > Verbesserte Performance > Vermeidet vollstndigen Datenverlust Backups auf Vollstndigkeit/Korrektheit berprfen Prozeduren und Zeitplne fr Backups und Wiederherstellung festlegen Testen, ob sie auch wirklich funktionieren! 44

Vielen Dank! Fragen, Kommentare, Anregungen? Lenz Grimmer http://www.lenzg.net/ Twitter: @lenzgr 45