MySQL Backup und Security - guug.deGUUG-Hamburg-MySQLBackup...$ mysql u root mysql mysql> SET PASSWORD FOR [email protected]=PASSWORD('new_password'); • Entfernen des anonymous Benutzers

Copyright 2007 MySQL AB 1The World’s Most Popular Open Source Database

MySQL Backup und SecurityPraktische Hinweise um MySQLsicher unter Linux zu betreiben

Lenz Grimmer <[email protected]>

(SAGE)/GUUG-TreffenHamburg, Germany

10. Mai 2007

MySQL AB


Einführung

Dieser Vortrag gibt einen Überblick über die verfügbaren Sicherheitsmechanismen des MySQL Servers und wie sie mit Sicherheitsfunktionen unter Linux kombiniert und verbessert werden können.

Zusätzlich werden verschiedene Werkzeuge und Strategien für die Datensicherung eines MySQL-Servers unter Linux vorgestellt.


Inhalt

• Verbesserung der Server-Sicherheit– Auf der MySQL-Seite– Auf Linux OS-Ebene

• MySQL Backup-Methoden– Physikalischer vs. logischer Backup– OSS Werkzeuge zum Erstellen von Backups– Kommerzielle Backup-Lösungen


Verbesserung der MySQL-Sicherheit

• Absicherung eines MySQL-Server ist erster Arbeitsschritt nach der Installation

• Standardinstallation ist bereits relativ sicher, aber einige weitere Schritte sind notwendig

• Zusätlich zu den von MySQL angebotenen Sicherheitsfeatures sollten ergänzende Maßnahmen auf OS-Ebene getroffen werden


MySQL Server Post-Installation• Wichtig: ein Passwort für den root Benutzer

$ mysql u root mysqlmysql> SET PASSWORD FOR [email protected]=PASSWORD('new_password');

• Entfernen des anonymous Benutzers oder Vergabe eines Kennworts

• Entfernen der test Datenbank, wenn nicht benötigt

• All dies kann auf einen Schlag mit dem mysql_secure_installation Script erledigt werden


Zugangskontrolle

• Verbindungsaufbau– Wenn ein Client Verbindung aufnimmt, prüft

der Server die user Tabelle ob er einen passenden Eintrag für username, host und password findet

• SQL-Abfrage– Für jede Abfrage prüft der Server die user, db, tables_priv und column_privs Tabellen


Zugangskontrolle bei Abfragen

Hat der Anwender die erforderlichen Rechte, die folgende Abfrage auszuführen?

Query

true

false

db

true

Query executed

Permissiondenied

false

columns_priv

false

tables_priv

false

user

true

true


Weitere Sicherheitshinweise

• Verwende die Option bindaddress in my.cfg um den TCP-Port an eine bestimmte Netzwerkschnittstelle zu binden (z.B. 127.0.0.1)

• Die Option skipnetworking erlaubt nur Verbindungen über den lokalen Unix-Socket

• Zugriff sollte nur von bestimmten Hosts erlaubt sein• Zugriff auf die mysql.user Tabelle sollte nur dem root Anwender erlaubt sein

• Lerne wie die SHOW GRANTS, SET PASSWORD und GRANT/REVOKE Kommandos verwendet werden

• Verwende phpMyAdmin oder MySQL Administrator für die Benutzerverwaltung



• Beschränke PROCESS/SUPER/FILE Rechte auf das notwendige Minimum

• Speichere keine Klartext-Kennworte in Deiner Datenbank. Verwende stattdessen MD5(), SHA1() oder eine andere Einweg-Hashfunktion.

• Deaktiviere LOAD DATA LOCAL durch Setzen von localinfile=0 in my.cnf

• Starte mysqld immer unter Verwendung eines nichtprivilegierten Benutzerkontos



• Für Paranoiker:– Ersetze das root Konto mit einem anderen,

schwerer zu ratenden Namen, um brute-force Wörterbuch-Attacken zu verhindern

– Denke daran, die Historie des mysql Kommandozeilen-Clients (~/.mysql_history) zu löschen oder zu bereinigen, wenn Du damit Benutzerkontos oder Kennwörter editiert hast


Views und Stored Procedures

• Views können verwendet werden, um den Zugriff auch bstimmte Spalten oder Tabellen zu beschränken

• Stored Procedures können dazu verwendet werden, daß Tabellen nicht direkt vom Anwender oder einer Applikation modifziert werden

• Hinweis: verfügbar ab MySQL 5.0.x!


Verbessern der Zugriffsbeschränkung• Reduziere die Zugriffsrechte auf das

Datenbankverzeichnis mit chown und chmod– Anwender können Tabellen nicht zufällig zerstören– Anwender haben keinen Zugriff auf Daten, die sie nicht

einsehen können sollen• Auch die Logdateien sollten abgesichert sein:

– Erneut könnten Anwender Einsicht auf Daten haben, die sie nicht haben sollten

– Abfragen wie GRANT werden in den Logdateien gespeichert, jeder mit Zugriff auf die Logs könnte so an Passworte kommen

• Generell sollten reguläre Anwender keinen Shell-Zugriff auf dem DB-Server haben


Verminderung von Sicherheitsrisiken mit Linux

• Verwende iptables um den Server mit einer Firewall abzusichern

• Starte MySQL in einer chroot() Umgebung

• Verwende SELinux oder Novell AppArmor

• Starte den MySQL-Server in einer virtuellen Machine– Xen– UML (User Mode Linux)– VMware / Parallels– Virtuozzo


Absicherung der Daten und Kommunication

• Verschlüsselung des Netzverkehrs– OpenSSL– SSH tunnel– OpenVPN– Cipe

• Verschlüsselung des Datenverzeichnisses– cryptoloop devices– dm_crypt kernel module


Sicherung von MySQL-Daten

• Wann braucht man Backups?• Was sollte gesichert werden?• Wann sollten Backups durchgeführt

werden?• Wo sollen die Backups aufbewahrt

werden?• Wie können Backups durchgeführt

werden?


Wann braucht man Backups?

• Hardware-Ausfall– Ein Teil der Daten einer Datenbank könnte durch

einen Crash verloren gegangen sein– Ein Festplattenausfall führt mit großer

Warscheinlichkeit zu starkem Datenverlust• Anwenderfehler

– Ein Anwender hat versehentlich eine DROP TABLE oder DELETE FROM Abfrage abgesetzt

– Jemand (ein Administrator?) hat versucht, die Tabellen mit einem Texteditors zu bearbeiten, was üblicherweise zu Inkonsistenzen führt


Was sollte gesichert werden?

• Datenbank-Inhalte– Für vollständige Backups– Logischer oder Physikalischer Backup

• Log-Dateien– Für inkrementelle Backups– point-in-time recovery


Wann sollten Backups durchgeführt werden?

• Regelmäßig!• Nicht zu den Stoßzeiten sondern nach

Feierabend• Statische Daten können weniger häufig

gesichert werden


Wo sollten Backups aufbewahrt werden?

• Auf dem Datenbankserver– Aber wenigstens auf einem separaten

Dateisystem/Volume oder zweiter Festplatte

• Kopiert zu einem anderen Server– Lokal oder in einem anderen Gebäude

• Backup auf Band/Platte– Lokal aufbewahrt oder woanders

• Nicht alles an einem Ort


Das Datenbankverzeichnis• Standardmäßig werden alle Datenbanken und Logdateien

im “data directory” gespeichert• Eine Verzeichnis-Vorgabe ist in den Server einkompiliert

– /usr/local/mysql/data/ (tarball installation)– /var/lib/mysql (RPM-Pakete)

• Das Verzeichnis kann beim Start des Servers vorgegeben werden: datadir=/your/path/

• Falls das Verzeichnis nicht bekannt ist, kann der Server danach befragt werden: – mysql> SHOW VARIABLES like 'data%';


Das Binary Log

• Enthält alle SQL-Anweisungen die tatsächlich Daten ändern

• Enthält weiterhin zusätzliche Informationen über jede Abfrage wie z.B. Zeitstempel

• Das Binary Log ist keine Textdatei, information ist in einem effizienteren Binärformat kodiert

• Inhalt kann mit mysqlbinlog eingesehen werden• Aktivierung mittlels logbin[=file_name]• Update-Logs werden sequenziell erzeugt

z.B. file_namebin.001, file_namebin.002, etc.

• Das Binary Log ist transaktionskompatibel • mysqld erzeugt eine Binary Log-Indexdatei, diese

enthält die Namen aller verwendeter Binärlogs


Verwaltung des Binärlogs Aufgabe des Binärlogs

Leichtere Wiederherstellung nach einem Crash Replikation

SHOW MASTER LOGS zeigt alle Binärlogdateien auf dem Server

FLUSH LOGS oder Server-Neustart erzeugen eine neue Logdatei

RESET MASTER löscht alle BinärlogsPURGE MASTER löscht alle Binärlogs bis zu

einem bestimmten Zeitpunkt


Das Error Log

• Wird der Server mittels mysqld_safe gestartet, werden alle Fehlermeldungen in das Error Log geleitet

• Die Datei enthält Informationen wann mysqld gestarted und angehalten wurde sowie alle Fehlermeldungen während des Betriebs

$ cat /var/log/mysql.err000929 15:29:45 mysqld started/usr/sbin/mysqld: ready for connections000929 15:31:15 Aborted connection 1 to db: 'unconnected' user: 'root' host: `localhost' (Got an error writing communication

packets)000929 15:31:15 /usr/local/mysql/bin/mysqld: Normal shutdown

000929 15:31:15 /usr/local/mysql/bin/mysqld: Shutdown Complete

000929 15:31:54 mysqld started/usr/sbin/mysqld: ready for connections


mysqldump

mysqldump speichert die Tabellenstrukturen und Inhalte als SQL-Anweisungen, die als Textdatei abgespeichert werden kann $ mysqldump mydb > mydb.20050925.sql

Man kann individuelle Tabellen oder ganze Datenbanken sichern

The default output from mysqldump consists of SQL statements, CREATE TABLE statements for table structure and INSERT statements for the data

mysqldump can also be used directly as input into another mysqld server (without creating any files) $ mysqldump opt world | mysql hwork.mysql.com world


Recovering With Backups

Recovered database = Backup files + binary log• In order to restore the tables to the state before a

crash you will need both your backup files and the binary log– From the backup files you can restore the tables to

the state they were at the time of the backup– From your synchronised binary logs you can extract

the queries issued between the backup and now• Beware, if you are recovering data lost due to

unwise queries remember not to issue them again


Example SQL level restore

• Restore the last full backup

mysql < backup.sql

• apply all incremental changes done after the last full backup

mysqlbinlog hostnamebin.000001 | mysql


MySQL table files backup

• Also called “physical” backup• Database files can be simply be copied

after issuing FLUSH TABLES WITH READ LOCK;

• The mysqlhotcopy Perl script automates this process (MyISAM table files only)

• Locking all tables for consistency can be expensive, if the file backup operation takes a long time


mysqlhotcopy

mysqlhotcopy is a Perl script with which you can easily backup databases

It can only be run on the same machine as where the databases are

It does the following LOCK TABLES FLUSH TABLES Copies the table files to the desired location with cp

or scp UNLOCK TABLES

The user has to have write access to the target directory


Backing Up InnoDB Databases

• You can use the mysqldump single transaction tool to make an on-line backup

• To take a ’binary’ backup, do the following:1. Shutdown the MySQL server2. Copy your data files, InnoDB log files, .frm files and

my.cnf file(s) to a safe location3. Restart the server

• It is a good idea to backup with mysqldump also, since an error might occur in a binary file without you noticing it


OSS backup tools

• The usual suspects: cp, tar, cpio, gzip, zip called in a shell script via a cron job

• Use rsync or unison for bandwidth-friendly remote backups

• Complete network-based backup solutions like afbackup, Amanda or Bacula provide more sophisticated features (e.g. catalogs)


Linux backup support

• LVM snapshots• DRBD (“RAID1 over the network”)• Distributed file systems

– OpenAFS– GFS– Lustre– Novell iFolder


Backup using LVM snapshots

• Linux LVM snapshots provide a very convenient and fast backup solution for backing up entire databases without disruption

• The snapshot volume does not need to be very large (10-15% are sufficient in a typical scenario)

• A backup of the files from the snapshot volume can be performed with any tool

• I/O performance may be degraded due to the additional LVM logging


Linux LVM snapshot creation

Basic principle:

mysql> FLUSH TABLES WITH READ LOCK$ lvcreate -s –-size=<size> --name=backup <LV>mysql> UNLOCK TABLES$ mount /dev/<VG>/backup /mnt$ tar czvf backup.tar.gz /mnt/*$ umount /mnt$ lvremove /dev/<VG>/backup


The mylvmbackup script

• A Perl script for quickly creating backups of MySQL server data files using LVM snapshots

• The LVM snapshot is mounted to a temporary directory and all data is backed up using the tar program

• Use of timestamped archive names allows you to run mylvmbackup many times without danger of rewriting old archives.

• requires Perl and the DBI and DBD::mysql modules• Available from http://www.lenzg.org/mylvmbackup/

http://www.lenzg.org/mylvmbackup/


MySQL replication

• Backing up a replication slave is less time-critical (the master is not blocked for updates)

• A slave can use different storage engines• One Master can replicate to many slaves• Keep the limitations of MySQL replication in

mind• Make sure to back up the master.info and relay-log.info files as well as any SQL_LOAD-* files (if LOAD DATA INFILE is replicated)


Kommerzielle Backuplösungen

• Acronis True Image• ARCServe• Arkeia• InnoDB HotBackup• SEP sesam• Veritas vxfs snapshots• Zmanda


Backup Method Comparison

• The output from mysqldump is portable to any other DBMS (without the opt option) whereas the copied files only work with MySQL

• The file copying methods are much faster than mysqldump

• So it comes down to your preferences:– Which tool do you prefer to use– Speed vs. portability


• Perform backups regularly• Turn on the binary update log

– The update logs are needed to restore the database without losing any data

• Synchronise your update log files with your backup files– Use FLUSH LOGS

• Name your backups consistently and understandably– Include the date in the file name mydb.20050925.sql

• Store your backups on a different file system than where your databases are

• Backup your backup files with file system backups

Backup Principles


General backup notes

• Putting the binary logs on a different file system (or even a different drive) than the data directory is recommended (increases performance and avoids data loss)

• Make sure the backup is consistent and complete!

• Define backup schedules and policies as well as recovery procedures

• Test that these actually work!


The MySQL Online Backup API

• Work is in in progress to define an API to perform a streaming MySQL online backup, independent of the Storage Engine

• Transactional tables will contain data only from committed transactions

• Non-transactional tables will contain data only from completed statements

• Referential integrity will be maintained between all tables backed up with a specific backup command

• The spec is now available for comments/review on MySQL Forge: http://forge.mysql.com/wiki/OnlineBackup

http://forge.mysql.com/wiki/OnlineBackup


Thank you!

Questions, Comments?Lenz Grimmer <[email protected]>

mailto:[email protected]

MySQL Backup und Security - guug.deGUUG-Hamburg-MySQLBackup...$ mysql u root mysql mysql> SET PASSWORD FOR [email protected]=PASSWORD('new_password'); • Entfernen des anonymous Benutzers

Text of MySQL Backup und Security - guug.deGUUG-Hamburg-MySQLBackup...$ mysql u root mysql mysql> SET...

PHP & MySQL

ROOT Einführung im Rahmen des FP - hacol13.physik.uni ...hacol13.physik.uni-freiburg.de/fp/root2009-1/FP_Root.pdf · 03/03/09 Julian Glatzer - ROOT Einführung 2 Was ist ROOT? ROOT

ROOT Einführung - hacol13.physik.uni-freiburg.dehacol13.physik.uni-freiburg.de/fp/root2010-2/FP_Root.pdf · 08/30/10 Julian Glatzer - ROOT Einführung 2 Was ist ROOT? ROOT ist ein

NoSQL with MySQL

Self Service Reset Password - Schulweb - Suedtirol · 2017. 8. 3. · Self Service Reset Password Benutzerhandbuch Der Service „Self Service Reset Password“ bietet den Benutzern

AnleitungzurInstallationundKonﬁgurationdes MySQL …members.gaponline.de/strobelmax/MySQL/mysql09/u_i9_01.pdf · 2009/2010 InstallationundKonﬁgurationdesMySQL-Datenbanksystems

MySQL Architekturen für Oracle DBA's... 2 Inhalt MySQL Architekturen für Oracle DBA's Der LAMP Stack Geschichte von MySQL Open Source Branches und Forks Wechsel von Oracle? MySQL

MySQL 5.1 Referenzhandbuch - home.uni-leipzig.dehome.uni-leipzig.de/kurs7/phpkurs/uploads/mysqlrefman-5.1-de.a4.pdf · MySQL 5.1 Referenzhandbuch Dies ist eine Übersetzung des MySQL-Referenzhandbuchs,

MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Backup

Tiket Elektronik Menggunakan One Time Password (OTP) dan

Handbuch Password Depot 10 · PDF filePassword Depot sperren ... Index 138. Willkommen bei Password Depot! Herzlichen Glückwunsch zu Ihrer Entscheidung, Ihre Kennwörter und Zugangsdaten

MySQL/MariaDB für nicht-MySQL DBAs - fromdual.com · Sun Microsystems kauft MySQL für USD 1 Mia, Apr 2008

ROOT Einführung - hacol13.physik.uni-freiburg.dehacol13.physik.uni-freiburg.de/fp/root2013-1/FP_Root_Felix.pdf · 25.02.2013 Felix Bührer - ROOT Einführung 2 Was ist ROOT? ROOT

Clustering mit MySQL 5...Seminar MySQL Cluster 09.03.2007 4 Clustering mit MySQL 5 MySQL 5 verwendet für seinen NDB14-Cluster synchrone Replikation in einer „Shared-Nothing“ Umgebung

MySQL für Oracle DBAs - MySQL, Galera Cluster and MariaDB ... · MySQL für Oracle DBAs Einsatz von MySQL Installation, ... mysql5.7.1linuxx86_64.tar.gz ... Kostenpflichtiges Modul?

MySQl - "after select"

MySQL Security

MySQL Performance Tuning für Entwickler - fromdual.com · 1 / 29 MySQL Performance Tuning für Entwickler Linux-Tage 2015, Chemnitz Oli Sennhauser Senior MySQL Consultant, FromDual

Anleitung – Turnitin · Anleitung – Turnitin ... Class ID und Class enrollment password finden Sie in Ihrem jeweiligem Moodle ... Forgot Your Password. Learn More

MySQL Beispiele aus der Praxis - Wie setzen Kunden MySQL ein?

Synchrone multi-Master Replikation für MySQL - doag.org file 1 / 23 Synchrone multi-Master Replikation für MySQL DOAG SIG-MySQL 2013, München Oli Sennhauser Senior MySQL Consultant,

Gemeinde Root Bau- und Zonenreglement (BZR) · Gemeinde Root 4 ecoptima Bau- und Zonenreglement Ingress Die Stimmberechtigten der Gemeinde Root erlassen gestützt auf §§ 17 Abs

Trans It Usb Password Pr Deutsch

Devops ohne root

Handbuch System Configurator - Webland.ch · phpMyAdmin MySQL Datenbank Allgemein Passwort für eine MySQL Datenbank ändern. System Configurator MySQL Datenbank Benutzer mit Leserechten

MySQL lernen - Anfangen, anwenden, verstehen mysql < mycommands.sql Database musikschule mysql test raum_id raum_name etage personen 1 A5.33 5

MySQL Hochverfügbarkeitslösungen

MySQL Security für Oracle DBAs - FromDual · MySQL Security für Oracle DBAs DOAG Konferenz 2016, Nürnberg Oli Sennhauser Senior MySQL Consultant, FromDual GmbH [email protected]

PHP und MYSQL

Documents

MySQL Backup und Security - guug.deGUUG-Hamburg-MySQLBackup...$ mysql u root mysql mysql> SET PASSWORD FOR [email protected]=PASSWORD('new_password'); • Entfernen des anonymous Benutzers

Text of MySQL Backup und Security - guug.deGUUG-Hamburg-MySQLBackup...$ mysql u root mysql mysql> SET...