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
[email protected]=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
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
