Talk about MySQL Backup and Security, presented at the amoocon 2009 in Rostock, Germany
Transcript
<ul><li> 1 Sun Microsystems Methoden zur Absicherung und Datensicherung eines MySQL- Servers Lenz Grimmer MySQL Community Relations Manager </li> <li> 2 bersicht Verbesserung der Server-Sicherheit > Integrierte Funktionalitt > Auf Betriebssystem-Ebene MySQL Datensicherung > physikalisch vs. logisch > Methoden und Werkzeuge </li> <li> 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 </li> <li> 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 </li> <li> 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 </li> <li> 6 MySQL-Zugangskontrolle Query true false db true Query executed Permission denied false columns_priv false tables_priv false user true true </li> <li> 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 </li> <li> 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 </li> <li> 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 </li> <li> 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 </li> <li> 11 Datensicherung Notwendigkeit > Hardware-Ausfall > Anwender- oder Applikationsfehler Zu sichernde Daten > Datenbankinhalte > Log-Dateien Weitere Aspekte > Sicherungszeitpunkt > Ort der Sicherung und Aufbewahrung </li> <li> 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 </li> <li> 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 </li> <li> 14 Zeitpunkt der Datensicherung Regelmig Auerhalb der Lastspitzen Wenig vernderliche Daten weniger hufig </li> <li> 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 </li> <li> 16 Modulare Speicher-Engine-Architektur </li> <li> 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 </li> <li> 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] </li> <li> 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 </li> <li> 20 Sicherungsmethoden logisch: SQL-Anweisungen physikalisch: Tabellendateien vollstndig vs. inkrementell > Aktivieren des Binrlogs > Zeitpunktbezogene Wiederherstellung </li> <li> 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 </li> <li> 22 mysqldump - Tipps --lock-all-tables ntzlich fr konsistente MyISAM-Backups > Aber sperrt alle DML-Anweisungen --flush-logs synchronisiert das Binrlog (Checkpointing) </li> <li> 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 </li> <li> 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 </li> <li> 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 </li> <li> 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) </li> <li> 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 </li> <li> 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/ </li> <li> 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?) </li> <li> 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 </li> <li> 31 Beispiel Wiederherstellung Letzte vollstndige Sicherung einspielen: $ mysql < backup.sql Einspielen der inkrementellen nderungen seit der letzten vollstndigen Sicherung: $ mysqlbinlog hostname-bin.000001 | mysql </li> <li> 32 Vergleich der Sicherungsmethoden Portabilitt (SQL Dumps vs. Tabellendateien) Geschwindigkeit, Speicherbedarf Overhead und Beeintrchtigung des Betriebs </li> <li> 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 </li> <li> 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! </li> <li> 35 Vielen Dank! Fragen, Kommentare, Anregungen? Lenz Grimmer </li> </ul>
