MySQL
Einrichtung des MySQL-Servers auf dem Caipirinha-Server
MySQL ist für das vollständige Funktionieren eines LAMP-Systems eine der Grundvoraussetzungen. Auch auf dem Caipirinha-Server ist MySQL installiert, und zwar mit den Paketen:
- mysql
- mysql-administrator
- mysql-client
- mysql-gui-tools
- mysql-query-browser
- mysql-workbench
Auf dem Caipirinha-Server wird die Datenbank für folgende Dienste benutzt:
- Die Caipigallery (basiert auf coppermine)
- Die Caipiwiki, also diese Wiki
- GroupOffice
Die Konfiguration von MySQL erfolgt über die zentrale Konfigurationsdatei /etc/my.cnf, die hier in Auszügen wiedergegeben ist:
... # The following options will be passed to all MySQL clients [client] password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 16M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # New entries by Gabriel Rüeck datadir = /home/public/Datenbanken log-error = /var/log/mysql/errors.log log-slow-queries = /var/log/mysql/slow_queries.log log-warnings ft_min_word_len = 2 ft_max_word_len = 40 ft_stopword_file = "" long_query_time = 1 # Set the Character Set of the server to UTF-8 character_set_server = utf8 collation_server = utf8_unicode_ci init_connect ='SET character_set_connection=utf8; SET collation_connection=utf8_unicode_ci' # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # # skip-networking … # The safe_mysqld script [safe_mysqld] log-error=/var/log/mysql/mysqld.log [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL # safe-updates …
Die Parameter key_buffer und max_allowed_packet wurden auf 16M eingestellt, um ganze Projektpläne für MS Project abspeichern zu können. Solche Projektpläne haben große binäre Blöcke, in denen MS Project proprietäre Daten speichert.
Mit dem Parameter datadir werden alle Datenbanken auf dem Caipirinha-Server in das Verzeichnis /home/public/Datenbanken gelegt, welches exklusiv dem Benutzer mysql:mysql gehört.
Die Parameter log-error, log-slow-queries und log-warnings legen fest, dass Fehler, Warnmeldungen und lange dauernde Abfragen festgehalten werden. Die beiden Fehlerdateien /var/log/mysql/errors.log und /var/log/mysql/slow_queries.log sollten regelmäßig überprüft werden. In der Datei /var/log/mysql/slow_queries.log werden SQL-Abfragen festgehalten, deren Abarbeitung unbotmäßig viel Rechenzeit in Anspruch nimmt. Eine häufige Ursache dafür sind nicht optimal strukturierte SQL-Abfragen, in denen Tabellen verknüpft werden. Eine alternative Verknüpfung mit einer JOIN LEFT-Syntax bewirkt meist Abhilfe.
Mit den Parametern ft_min_word_len und ft_max_word_len legt man fest, dass für die Volltextsuche ein Suchbegriff mindestens 2 und höchstens 40 Zeichen lang sein darf. Der leere Wert für ft_stopword_file schließlich deaktiviert die standardmäßige Stoppwortliste.
Mit dem Parameter long_query_time wird hier festgelegt, dass alle Abfragen, welche länger als 1s benötigen, als “zu lange” eingestuft werden.
Das Verzeichnis /var/log ist nur für den Benutzer root beschreibbar. Deshalb legt man dort ein Unterverzeichnis namens mysql an, so dass MySQL in diesem Unterverzeichnis alle Log-Dateien ablegen und diese auch rotieren kann. Dazu muss man nun noch als Benutzer root folgende Kommandos ausführen:
mkdir /var/log/mysql chown mysql:mysql /var/log/mysql
Nach diesen Anpassungen wird MySQL mit /etc/init.d/mysql start gestartet.
Jetzt muss unbedingt ein Passwort fur den Benutzer root
auf dem MySQL-Server vergeben werden, denn im Moment kann sich noch
jeder Benutzer auf dem Caipirinha-Server mit dem MySQL-Server verbinden.
Man beachte, dass der MySQL-Benutzer root nicht mit dem Systembenutzer root des Caipirinha-Servers identisch ist. MySQL hat seine eigenen Benutzer, und unter denen gibt es auch einen Superuser root. Mit mysqladmin –u root password geheimes_Passwort
vergibt man das root-Passwort und verhindert dadurch automatisch, dass
sich Benutzer am MySQL-Server ohne Passwort anmelden können. Das heisst,
es obliegt nun dem MySQL-root, weitere MySQL-Benutzer einzurichten, wenn dies erforderlich ist. Das Einloggen auf dem MySQL-Server muss jetzt mit dem Befehl mysql –u Benutzer –p geheimes_Passwort geschehen. Man kann auch das Passwort weglassen, dann wird man beim Einloggen explizit danach gefragt.
MySQL hat seine eigene Sprache und Syntax, und an dieser Stelle sollen nur einige elementare Dinge vorgestellt werden.
Elementare MySQL-Befehle
Im einem nun folgenden Beispiel werden folgende Dinge gezeigt:
- Ein Benutzer loggt sch als als MySQL-Benutzer root ein.
- Der nun als root eingeloggte MySQL-Benutzer lässt sich alle Datenbanken anzeigen.
- Dann wählt er die MySQL-Systemdatenbank mysql aus (welche schon bei der Installation eingerichtet wird).
- Er lässt sich eine Übersicht aller Tabellen der Datenbank mysql geben.
- Er lässt sich die Tabelle user näher beschreiben.
- Aus der Tabelle user lässt er sich alle Benutzer anzeigen.
- Danach loggt sich der MySQL-Benutzer root wieder aus.
Wie in der Literatur üblich, werden die MySQL-Befehle in Großbuchstaben geschrieben. Dies ist aber nicht notwendig.
caipirinha:~ # mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.1.36 SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| CCPM_Test |
| GroupOffice |
| PCBA |
| Wiki |
| coppermine |
| mysql |
| nonCCPM_Test |
+--------------------+
8 rows in set (0.00 sec)
mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.09 sec)
mysql> DESCRIBE user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)
mysql> SELECT Host,User FROM user;
+-----------+-----------------+
| Host | User |
+-----------+-----------------+
| % | gabriel |
| localhost | cms_user |
| localhost | coppermine_user |
| localhost | go_user |
| localhost | lp_mis |
| localhost | mis |
| localhost | root |
+-----------+-----------------+
7 rows in set (0.02 sec)
mysql> QUIT;
Bye
caipirinha:~ #
Man erkennt mehrere Benutzer, von denen sich alle ausser gabriel nur auf dem Caipirinha-Server selbst einloggen können und sich nicht von einer entfernten Maschine aus über Port 3306 verbinden können. Aus Sicherheitsgründen ist es grundsätzlich zu empfehlen, die Benutzerrechte so weit wie möglich einzuschränken. In diesem Fall darf sich nur der Benutzer gabriel auch von entfernten MySQL-Clients über Port 3306 mit dem MySQL-Server auf Caipirinha verbinden.
Im nächsten Beispiel wird ein neuer Benutzer test mit dem geheimen Passwort abc’ angelegt. Er bekommt uneingeschränkte Zugriffsrechte auf die Datenbank Wiki. Diese beiden Schritte kann man gleich in einem Schritt zusammen fassen, was hier auch so gemacht wird. Dann schauen wir uns die gesetzten Rechte an und löschen den Benutzer wieder.
mysql> GRANT ALL ON Wiki.* TO 'test' IDENTIFIED BY 'abc'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'test'; +-----------------------------------------------------------------------------------------------------+ | Grants for test@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' | | GRANT ALL PRIVILEGES ON `Wiki`.* TO 'test'@'%' | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> DROP USER 'test'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Hier wird ersichtlich, dass durch das kombinierte Anlegen des Benutzers eigentlich gleich zweimal Benutzerrechte vergeben werden. Zum einen erhält der Benutzer überhaupt Zugangsrechnte zum MySQL-Server. Und zum zweiten erhält er dann uneingeschränkte Zugriffsrechte auf die Datenbank Wiki. Auf andere Datenbanken kann er nicht zugreifen. Man sieht außerdem, dass das Passwort verschlüsselt abgelegt wird.
Backup der Datenbanken
Alle Datenbanken müssen natürlich regelmäßig gesichert werden. Dazu gibt es die beiden Programme mysqldump und mysqlhotcopy, die beide ihre Vor- und Nachteile haben. Auf dem Caipirinha-Server habe ich mich für mysqldump entschieden. mysqldump erzeugt komplette SQL-Sequenzen in derselben Reihenfolge, in der man sie eintippen müsste, wenn man die entsprechende Datenbank neu erstellen müsste.
Das Backup für den MySQL-Server ist inzwischen im allgemeinen Backup-Skript integriert worden. Mit den folgenden Befehlen kann man in einer Shell beispielsweise alle Datenbanken in einem mit gzip komprimierten Format sichern:
readonly MYSQL_USERNAME=user
readonly MYSQL_PASSWORD=geheimes_passwort
DATABASE_LIST=$(mysql -e 'show databases' -u$MYSQL_USERNAME -p$MYSQL_PASSWORD)
DATABASE_LIST=$(echo $DATABASE_LIST | cut -d " " -f2-)
for DATABASE in $DATABASE_LIST
do mysqldump -BC --hex-blob -u$MYSQL_USERNAME -p$MYSQL_PASSWORD $DATABASE | gzip -n9 > /backup/$DATABASE.sql.gz
done
Zum Wiederherstellen muss man dann lediglich die von mysqldump erzeugte SQL-Datei wieder mit gunzip entpacken und mit dem Befehl mysql -uroot -p geheimes Passwort < backup_datei.sql einlesen.
Replikation von Datenbanken
Mit einer Replikation kann man den Datenbestand eines Slave Servers mit dem Datenbestand eines Master Servers synchronisieren. Dadurch kann man folgende Vorteile erreichen:
- Lastverteilung bei stark frequentierten Systemen, indem Schreibzugriffe auf den Master Server und Lese-Zugriffe auf einen oder mehrere Slave Server verlegt werden
- Eine Art Backup-Lösung, bei der man bei Ausfall des Master Servers durch Modifikation einiger Dateien relativ schnell auf den laufen Slave Server umschalten kann (vorausgesetzt, die Applikation unterstützt das)
- Daten-Synchronisation mit entfernten Standorten
Eine solche Replikation setzt also mindestens 2 MySQL-Server voraus, mit unterschiedlichen Server IDs. Die Versionsnummern der MySQL-Server sollten idealerweise die gleichen sein, ansonsten sollte der Slave Server stets die höhere Versionsnummer als der Master Server haben.
Auf dem Master Server wird die Konfigurationsdatei /etc/my.cnf so modifiziert:
# Replication Master Server (default) # binary logging is required for replication log-bin = /var/log/mysql/binlog max_binlog_size = 256M # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1
Außerdem wird im MySQL Master Server ein Replikationsbenutzer angelegt, und zwar mit:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'Replikationsbenutzer' IDENTIFIED BY 'Replikationspasswort';
Replikationsbenutzer und Replikationspasswort müssen
natürlich einen sinnvollen Benutzernamen und ein sinnvolles Passwort
ersetzt werden. Man kann den Replikationsbenutzer auch einschränken,
beispielsweise auf 'Replikationsbenutzer'@'10.130.25.110',
wenn der Slave-Server auf einer Maschine mit der statischen IP-Adresse
10.130.25.110 läuft. Das erhöht die Sicherheit etwas. Ansonsten hilft
nur ein möglichst kompliziertes Passwort.
Jetzt muss man mit /etc/init.d/mysql restart den
Master Server neu starten. Danach nimmt man ein Backup des Datenbestands
des Master Servers vor und spielt dieses Backup im Slave Server ein.
Idealerweise erfolgt daher der Neustart des Master Servers und das
Anfertigen des Backups zu einer Zeit, da möglichst wenig Datenzugriffe
stattfinden. Ansonsten bietet die MySQL-Dokumentation [1]
noch Hinweise, wie man Datenbanken gegen Schreibzugriffe sperren kann,
so dass konsistente Datenbestände gesichert werden können.
Jetzt (nach dem Einspielen des Backups) muss man die Konfigurationsdatei /etc/my.cnf des Slave Servers anpassen, beispielsweise auf:
# Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # ... # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted server-id = 2 master-host = 172.16.0.1 master-user = "Replikationsbenutzer" master-password = "Replikationspasswort" master-port = 3306 report_host = "caipirinha.homelinux.org" report_port = 3306 # binary logging - not required for slaves, but recommended #log-bin = /var/log/mysql/binlog #max_binlog_size = 256M max_relay_log_size = 256M replicate-wild-do-table = GroupOffice.% replicate-wild-do-table = Wiki.% replicate-wild-do-table = coppermine.%
In diesem Beispiel wird mit dem Parameter max_relay_log_size die maximale Größe der Relay-Log-Datei des Slave Servers auf 256MB begrenzt. Die Relay-Log-Dateien sind die “Slave”-Version der binären Log-Dateien des “Masters”. Der Slave Server hält dort fest, welche Änderungen des Master Servers er bereits nachvollzogen hat.
Mit dem Parameter replicate-wild-do-table kann man die Namen einer oder mehrerer Tabellen festlegen, die repliziert werden sollen. Es ist hierbei möglich, alle Tabellen einer Datenbank auszuwählen, indem man den Datenbanknamen, gefolgt von .% angibt. Man kann den Parameter auch komplett weglassen, dann werden alle Datenbanken repliziert. Ich halte es dann für sinnvoll, Namen anzugeben, wenn auf dem Slave Server noch andere Datenbanken liegen, die nicht aus einer Replikation eines Master Servers stammen, sondern die lokal verwaltet werden.
Nun muss der Slave Server mit der neuen Konfiguration gestartet werden, allerdings, nachdem die zuvor gesicherten Datenbanken des MAster Servers eingespielt worden sind.
Es ist nicht schlimm, wenn das Aufsetzen des Slave Servers etwas Zeit braucht, denn der Master Server schreibt ja inzwischen alle Änderungen in die binäre Log-Datei, und der Slave-Server wird sich dann ab dem Einspielen der Datenbanken alle Änderungen vom Master Server holen diese nachvollziehen.
In MySQL kann man sich auf dem Master Server den Status der Replikation mit folgenden Befehlen anzeigen lassen:
mysql> SHOW MASTER STATUS; +---------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | binlog.000001 | 10941 | | | +---------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> SHOW PROCESSLIST; +----+--------+------------------+------+-------------+------+----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+------------------+------+-------------+------+----------------------------------------------------------------+------------------+ | 8 | sombra | 172.16.0.6:58988 | NULL | Binlog Dump | 157 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 24 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+--------+------------------+------+-------------+------+----------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec) mysql> SHOW SLAVE HOSTS; +-----------+--------------------------+------+-------------------+-----------+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id | +-----------+--------------------------+------+-------------------+-----------+ | 2 | caipirinha.homelinux.org | 3306 | 0 | 1 | +-----------+--------------------------+------+-------------------+-----------+ 1 row in set (0.00 sec)
Hier sieht man, dass ein Replikationsbenutzer namens sombra auf dem Master Server eingeloggt ist. Auf dem Slave Server kann man sich analog dazu den Status der Replikation mit folgenden Befehlen anzeigen lassen:
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.1
Master_User: sombra
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 10941
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 11083
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: GroupOffice,Wiki,coppermine
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 10941
Relay_Log_Space: 11239
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 174 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 44 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 4 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
Hier sieht man, dass der Slave Server derzeit auf Änderungen durch den Master Server wartet.
Der Slave Server legt im Datenbank-Verzeichnis eine Datei namens master.info (Textdatei) an, in welcher wichtige Replikationsparameter aus der Konfigurationsdatei /etc/my.cnf
des Slave Servers abgelegt werden. Existiert diese Datei, dann werden
die Replikationsparameter beim Start des Slave Servers nicht mehr aus /etc/my.cnf gelesen, selbst wenn sie dort verändert worden sind. Will man also Änderungen wirksam machen, muss man entweder master.info löschen oder die ab MySQL 6 empfohlene Syntax CHANGE MASTER TO... im MySQL-Interface anwenden.
Ausführliche Informationen zum Thema Replikation finden sich ferner in [2].
Posted on: 2010-07-20Gabriel Rüeck