Talk about MySQL Backup and Security, presented at the amoocon 2009 in Rostock, Germany
Text of MySQL Absicherung und Datensicherung
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
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/
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 nderunge
