Efektywne zarządzanie bazą danych serwera wirtualnego z backendem MySQL (część 1)
Wielu osobom nieobce jest zagadnienie instalacji usług serwera wirtualnego (pDNS, Postfix, SASL, Dovecot, Pureftpd, Amavis, GPS, Apache) w oparciu backend na bazie danych MySQL. Rozwiązanie takie jest bardzo elastyczne i bezpieczne stanowiąc jednocześnie punkt wyjścia do efektywnego zarządzania usługą.
Do w/w integracji często stosuje się następujące oprogramowanie:
- pDNS jako serwer DNS (1)
- Postfix wraz z SASL jako MTA
- Dovecot jako serwer usług POP3/IMAP
- Apache
- pureFTPd (2)
1) Bind jest najczęściej stosowanym serwerem DNS, jednakże pDNS oferuje natywne wsparcie MySQL oraz bardzo łatwą (gotowe programy) migrację
2) Zazwyczaj stosuje się proFTPd, którego przez długi czas i ja byłem zwolennikiem - jednakże z uwagi na problematyczną konfigurację z MySQL (problemy z quota) zrezygnowałem z niego.
Dodatkowo stosuję oprogramowanie
- GPS - wspierający MySQL grey-list policy server dla Postfixa
- Amavis jako SMTP proxy wspierające skanowanie antyvirusowe (clamav) i antyspamowe (spamassasin)
Integracja całości jest przedmiotem wielu FAQ oraz całej masy dokumentacji którą można znaleźć w googlach, tak więc nie na tym będę się skupiał. Skupie się na samej strukturze bazy danych.
Otóż zauważyć można podczas integracji pewną nadmiarowość i duplikowanie informacji w wielu tabelach podczas konfigurowania różnych usług dla tego samego klienta.
I tak aby zgodnie z większością znanych opisów integracji metodą dodać klientowi pełną obsługę wszystkich usług musimy:
- dodać wpisy w 2 tabelach pDNS
- dodać wpisy w 3 tabelach postfixa
- dodać wpisy w tabeli dla Dovecot jeśli nie używamy tabeli Postfixa
- dodać 1 wpis w tabeli pureFTPd
- dodać 2(?) wpisy w tabeli amavis
Większość z informacji, które dodajemy do tych tabel jest duplikowana. W przypadku potrzeby zarządzania tą informacją mamy problem. Co zrobić aby nie duplikować niepotrzebnie informacji. Otóż z pomocą przychodzą nam tutaj mechanizmy view (w dalszej części będę używał nazwy widok) dostępne w MySQL od wersji 5.
Przyjrzyjmy się najpierw tabelą pDNS. Tutaj uwaga. Wszystkie tabele mają format zgodny z powszechnie stosowanymi opisami integracji usługi z MySQL. Różnią się tylko nazwą, którą dla własnej wygody prefixowałem nazwą usługi.
mysql> desc pdns_domains; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | UNI | | | | master | varchar(20) | YES | | NULL | | | last_check | int(11) | YES | | NULL | | | type | varchar(6) | NO | | | | | notified_serial | int(11) unsigned | YES | | NULL | | | account | varchar(40) | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
mysql> desc pdns_records; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | domain_id | int(11) | YES | MUL | NULL | | | name | varchar(255) | YES | MUL | NULL | | | type | varchar(6) | YES | | NULL | | | content | varchar(255) | YES | | NULL | | | ttl | int(11) | YES | | NULL | | | prio | int(11) | YES | | NULL | | | change_date | int(11) | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec)
Otóż tabel pDNS zmieniać nie możemy z tej oto przyczyny, iż pDNS zapisuje samodzielnie informacje do obu tabel, tak więc nie mogą być one przekonwertowane do widoku. Tabela pdns_domains zawiera jednakże istotne informacje, mianowicie AKTYWNE w naszym systemie domeny podstawowe i zapasowe.
Rzućmy okiem na tabele używane przez Postfixa:
mysql> show tables like 'postfix%'; +------------------------------+ | Tables_in_vserver (postfix%) | +------------------------------+ | postfix_alias | | postfix_domains | | postfix_mailbox | +------------------------------+ 3 rows in set (0.00 sec)
mysql> desc postfix_domains; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | domain | varchar(255) | NO | | | | | backupmx | tinyint(1) | NO | | 0 | | +----------+--------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
mysql> desc postfix_mailbox; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | username | varchar(255) | NO | PRI | | | | password | varchar(255) | NO | | | | | maildir | varchar(255) | NO | | | | | quota | int(10) | NO | | 0 | | | domain | varchar(255) | NO | MUL | | | | active | tinyint(1) | NO | MUL | 1 | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Otóż w tabeli postfix_domains znajduje się lista domen obsługiwanych przez nasz system. Dodatkowo znajdują się tam domeny których backup MX utrzymujemy. Z dużym prawdopodobieństwem założyć można, iż będzie to lista domen których DNS utrzymujemy w pDNS. W tabeli postfix_mailbox znajdują się konta użytkowników, tabela ta może być współdzielona przez SASL oraz Dovecot, aczkolwiek jest to nieefektywne z uwagi do różnego sposobu podejścia do tematu blokowania kont.
Zazwyczaj blokada tymczasowa ma na celu zablokowanie możliwości wysyłania i odbierania poczty przy jednoczesnym braku blokady na pocztę przychodzącą.
Tabela serwera FTP, poniższy schemat jest schematem pochodzącym z dokumentacji:
mysql> desc ftpd_users; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | User | varchar(16) | NO | PRI | | | | Password | varchar(64) | NO | | | | | Uid | int(11) | NO | | -1 | | | Gid | int(11) | NO | | -1 | | | Dir | varchar(128) | NO | | | | +----------+--------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
Do rzeczy: założenia:
- projektowany schemat danych ma mieć zastosowanie w serwerze wirtualnym, który może obsługiwać sporą ilość domen i być punktem wyjścia do zarządzania przy pomocy interfejsu WWW.
- podstawową jednostką przyporządkowania użytkowników do grup będzie domena
- Chcemy mieć na tyle elastyczny system, aby jednym wpisem w odpowiedniej tabeli dało się uaktywnić użytkownikowi cały zestaw usług.
- Chcemy dodatkowo zapewnić minimalną duplikację danych w całym systemie oraz pewne mechanizmy weryfikacji danych na poziomie bazy.
Cdn...
