MySQL Absicherung und Datensicherung

  • Published on
    01-Sep-2014

  • View
    4.781

  • Download
    0

DESCRIPTION

Talk about MySQL Backup and Security, presented at the amoocon 2009 in Rostock, Germany

Transcript

1 Sun Microsystems Methoden zur Absicherung und Datensicherung eines MySQL- Servers Lenz Grimmer MySQL Community Relations Manager 2 bersicht Verbesserung der Server-Sicherheit > Integrierte Funktionalitt > Auf Betriebssystem-Ebene MySQL Datensicherung > physikalisch vs. logisch > Methoden und Werkzeuge 3 MySQL-Absicherung Wichtige Arbeitsschritte nach Erstinstallation 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 4 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 5 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 6 MySQL-Zugangskontrolle Query true false db true Query executed Permission denied false columns_priv false tables_priv false user true true 7 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) Wichtige SQL-Anweisungen: SHOW GRANTS, SET PASSWORD, GRANT/REVOKE PROCESS/SUPER/FILE Privilegien minimieren 8 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 9 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 10 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 11 Datensicherung Notwendigkeit > Hardware-Ausfall > Anwender- oder Applikationsfehler Zu sichernde Daten > Datenbankinhalte > Log-Dateien Weitere Aspekte > Sicherungszeitpunkt > Ort der Sicherung und Aufbewahrung 12 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 13 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 14 Zeitpunkt der Datensicherung Regelmig Auerhalb der Lastspitzen Wenig vernderliche Daten weniger hufig 15 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 16 Modulare Speicher-Engine-Architektur 17 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 18 Das Binrlog Speichert alle datenverndernden SQL- Anweisungen (DML) z.B. CREATE, INSERT, DELETE, DROP, UPDATE Zweck: > Erleichtert Datenwiederherstellung > Replikation Enthlt zustzliche Informationen (Zeitstempel, Laufzeit) Binr codiert, mysqlbinlog zum decodieren Aktiviert mit --log-bin[=datei] 19 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 20 Sicherungsmethoden logisch: SQL-Anweisungen physikalisch: Tabellendateien vollstndig vs. inkrementell > Aktivieren des Binrlogs > Zeitpunktbezogene Wiederherstellung 21 Gngige MySQL-Sicherungspraktiken mysqldump > Vollstndige Sicherung $ mysqldump mydb > mydb.20050925.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 22 mysqldump - Tipps --lock-all-tables ntzlich fr konsistente MyISAM-Backups > Aber sperrt alle DML-Anweisungen --flush-logs synchronisiert das Binrlog (Checkpointing) 23 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 24 XtraBackup / Maatkit https://launchpad.net/percona-xtrabackup Online-Backup fr InnoDB In my.cnf: > [xtrabackup] target_dir = /home/backups Backup-Kommando: > xtrabackup backup http://maatkit.org/ Multi-threaded Perl wrapper scripts > mk-parallel-dump / mk-parallel-restore 25 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 26 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) 27 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 28 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/ 29 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?) 30 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 31 Beispiel Wiederherstellung Letzte vollstndige Sicherung einspielen: $ mysql < backup.sql Einspielen der inkrementellen nderungen seit der letzten vollstndigen Sicherung: $ mysqlbinlog hostname-bin.000001 | mysql 32 Vergleich der Sicherungsmethoden Portabilitt (SQL Dumps vs. Tabellendateien) Geschwindigkeit, Speicherbedarf Overhead und Beeintrchtigung des Betriebs 33 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 34 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! 35 Vielen Dank! Fragen, Kommentare, Anregungen? Lenz Grimmer

Recommended

View more >