MySQL – podstawy administracji
Administracja bazą danych jest zajęciem odpowiedzialnym i wymagającym
dużej wiedzy i doświadczenia. Dlatego zajmować się tym raczej powinni ludzie
rzeczywiście do tego przygotowani.
Jednak, nawet jeśli koś nie zamierza lub nie będzie nigdy w przyszłości
administrował bazą danych to spojrzenie na serwer baz danych z punktu
widzenia administratora jest doświadczeniem ciekawym i pouczającym.
W niniejszej prezentacji są przedstawione niektóre tylko zagadnienia dotyczące
codziennego dnia pracy administratora (admina) bazy danych MySQL. Wiele
spośród tych zagadnień dotyczy nie tylko administracji bazą danych, ale też
administracji serwerem sieciowym, siecią LAN czy też superkomputerem. W
praktyce zresztą jest często tak, że osoba, która zarządza serwerem
bazodanowym i opiekuje się bazami danych, jest też jednocześnie zaangażowana
w administrację lokalną siecią, dbanie o bezpieczeństwo w tej sieci i
rozwiązywanie problemów związanych z aplikacjami korzystającymi z bazy
danych. Jest to więc zajęcie wymagające bardzo wszechstronnej wiedzy.
Zagadnienia, które są omawiane poniżej, można podzielić na następujące:
bezpieczeństwo baz danych
polecenia SQL służące do administracji bazą danych.
przykłady typowych zadań administracyjnych z punktu widzenia
użytkowników
przykłady typowych zadań administracyjnych z punktu widzenia pracy
serwera
pliki konfiguracyjne
backup baz danych
Bezpieczeństwo bazy danych MySQL
Baza danych, tak jak każda aplikacja internetowa jest narażona na różnego
rodzaju ataki sieciowe. Serwer baz danych nasłuchuje najczęściej na porcie 3306
protokołu TCP i jest narażony na takie same rodzaje ataków jak inne aplikacje
internetowe (przepełnienie bufora, podszywanie się pod inny adres IP, ataki DoS,
sniffing i inne).
Oprócz tego, w bazie danych znajdują się bardzo ważne dane, które powinny być
szczególnie chronione przed dostępem do niepowołanych osób. Dostęp do
pewnych danych z bazy danych (na przykład hasła w bankach, kody kart
elektronicznych) przez niepowołane osoby mógłby doprowadzić do
katastrofalnych skutków.
MySQL opiera zasady bezpieczeństwa na koncepcji tzw. ACL (ang. Access
Control Lists) w przypadku wszystkich połączeń, zapytań i innych operacji
wykonywanych przez użytkowników. Jest też odpowiednie wsparcie dla
połączeń szyfrowanych SSL między klientem i serwerem baz danych.
Większość z omawianych w tej części koncepcji nie jest specyficzna wyłącznie
dla pracy z bazą danych MySQL, lecz można je uogólnić również na przypadek
innych aplikacji internetowych.
Podstawowe zasady bezpieczeństwa podczas pracy na stanowisku admina
MySQL
Należy bezwzględnie ustawić hasło dla administratora. Jeśli po wydaniu
komendy mysql -u root nie zostaniemy poproszeni o hasło, to znaczy
że na naszym serwerze bazodanowym jest fatalnie.
Nigdy nie wolno dawać dostępu do bazy mysql innym użytkownikom poza
administratorem. Jest to sprawa kluczowa, gdyż w tablicach bazy mysql
znajdują się definicje dostępu do danych bazy danych dla konkretnych
użytkowników, między innymi hasła dostępu. Każdy kto ma dostęp do
komputera wymienionego na liście komputerów, z którego można połączyć
się z bazą danych, może podszyć się pod innego użytkownika.
Należy nauczyć się poleceń systemu nadawania i odbierania uprawnień
(polecenia GRANT i REVOKE). Nie należy nigdy dawać zbyt dużej swobody
użytkownikom. Lepiej dać mniej niż więcej.
Nie należy dawać dostępu do wszystkich baz danych, a jedynie do
wybranych. Nie należy też dawać dostępu do baz danych ze wszystkich
hostów, a jedynie z wybranych.
Nigdy nie należy przechowywać w bazie danych haseł w postaci
niezakodowanej. Zamiast tego należy używać funkcji kodujących (na
przykład MD5(), SHA1()) lub innych, które używają algorytmów
działających tylko w jedną stronę (tylko kodujących).
Nie wolno wybierać haseł, które łatwo jest złamać (na przykład odgadnąć).
Zatem nie należy absolutnie używać haseł ze słownika, imion, nazwisk, itp.
Należy w hasłach stosować znaki specjalne, na przykład #, ?, @, cyfry.
Dobrym nawykiem jest też używanie w hasłach różnej wielkości liter.
Należy zainstalować firewall. To powinno obronić serwer bazy danych przed
połową exploitów krążących w sieci.
Należy spróbować przeskanować porty na swoim komputerze. Serwer bazy
danych MySQL pracuje najczęściej na porcie 3306. Ten port nie powinien
być dostępny dla innych hostów niż te zdefiniowane w tablicy
mysql.user. Można to zrobić na najbliższym routerze albo zdefiniować w
firewallu.
Wyrażenia SQL służące do administracji bazą MySQL
GRANT i REVOKE
Polecenie GRANT służy do zakładania kont użytkownikom bazy danych i do
nadawania im pewnych przywilejów związanych z dostępem do danych.
Polecenie REVOKE służy do odbierania nadanych uprawnień zadanym
użytkownikom.
Wszystkie dane o użytkownikach i przywilejach jakie posiadają są
przechowywane w bazie o nazwie mysql, która dla nieuprawnionych
użytkowników nie powinna być nawet widoczna.
Składnia polecenia GRANT jest następująca
GRANT
typ_przyw [(lista_kolumn)]
[, typ_przyw [(lista_kolumn)]] ...
ON {tablica | * | *.* | baza.*}
TO user [IDENTIFIED BY [PASSWORD] 'haslo']
[, user [IDENTIFIED BY [PASSWORD] 'haslo']] ... [REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION |
MAX_QUERIES_PER_HOUR ile |
MAX_UPDATES_PER_HOUR ile |
MAX_CONNECTIONS_PER_HOUR ile ]]
Składnia polecenia REVOKE jest następująca
REVOKE
typ_przyw [(lista_kolumn)]
[, typ_przyw [(lista_kolumn)]] ...
ON {tablica | * | *.* | baza.*}
FROM user1 [, user2] ...
lub razem, kiedy chcemy zabrać wszystkie możliwe przywileje
wymienionym użytkownikom:
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user1 [, user2] ...
Można wyróżnić 4 poziomy dostępu do bazy danych.
o Poziom globalny
Przywileje globalne stosują się do wszystkich baz danych na danym
serwerze bazodanowym. Informacja o takich przywilejach jest
przechowywana w tablicy mysql.user.
Polecenia GRANT ALL ON *.* oraz REVOKE ALL ON *.*
stosują się właśnie do przywilejów globalnych.
o Poziom bazodanowy
Przywileje bazodanowe stosują się do wszystkich tablic w danej bazie
danych. Informacja o takich przywilejach jest przechowywana w
tablicach mysql.db oraz mysql.host.
Polecenia GRANT ALL ON nazwa_bazy.* oraz REVOKE ALL ON
nazwa_bazy.* dotyczą nadawania i odbierania przywilejów
bazodanowych, czyli tylko w bazie danych o nazwie nazwa_bazy.
o Poziom tablicowy
Przywileje tablicowe stosują się do wszystkich kolumn w danej tablicy.
Informacja o takich przywilejach jest przechowywana w tablicy mysql.tables_priv
Polecenia GRANT ALL ON nazwa_bazy.nazwa_tablicy oraz
REVOKE ALL ON nazwa_bazy.nazwa_tablicy dotyczą tylko
przywilejów tablicowych, czyli dostępu do danych w tablicy o nazwie
nazwa_tablicy w bazie o nazwie nazwa_bazy.
o Poziom kolumnowy
Przywileje kolumnowe dotyczą tylko dostępu do konkretnych
(wybranych) kolumn danej tablicy. Informacja o takich przywilejach jest
przechowywana w tablicy mysql.columns_priv.
Usunięcie wszystkich przywilejów polega więc najczęściej na usunięciu
odpowiednich danych z kilku tablic bazy mysql. Aby usunąć wszystkie
możliwe przywileje danemu użytkownikowi lub użytkownikom, należy
posłużyć się następującym poleceniem:
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
Dla poleceń GRANT i REVOKE, typ przywileju typ_przyw może być m.in.
jednym z następujących:
Przywilej Znaczenie
ALL [PRIVILEGES] Wszystkie przywileje poza GRANT OPTION
ALTER Możliwość wykonywania ALTER TABLE
CREATE Możliwość wykonywania CREATE TABLE
CREATE TEMPORARY
TABLES
Możliwość wykonywania CREATE TEMPORARY TABLE
CREATE VIEW Możliwość wykonywania CREATE VIEW
DELETE Możliwość wykonywania DELETE
DROP Możliwość wykonywania DROP TABLE
FILE Możliwość wykonywania SELECT ... INTO
OUTFILE oraz LOAD DATA INFILE
INDEX Możliwość wykonywania CREATE INDEX oraz
DROP INDEX
INSERT Możliwość wykonywania INSERT
LOCK TABLES
Możliwość wykonywania LOCK TABLES na
tablicach, w przypadku których mamy możliwość
wykonywania INSERT
RELOAD Możliwość wykonywania FLUSH, na przykład
FLUSH PRIVILEGES
ALTER Możliwość wykonywania ALTER TABLE
SELECT Możliwość wykonywania SELECT
SHOW DATABASES Możliwość wykonywania SHOW DATABASES
SHOW VIEW Możliwość wykonywania SHOW CREATE VIEW
SHUTDOWN Możliwość zatrzymywania pracy serwera
SUPER
Możliwość wykonywania CHANGE MASTER,
KILL, PURGE MASTER LOGS, SET GLOBAL,
również pozwolenie na połączenie z bazą nawet w
przypadku, kiedy przekroczono maksymalna liczbę
połączeń
UPDATE Możliwość wykonywania UPDATE
USAGE Bez żadnych przywilejów
GRANT OPTION Możliwość nadawania przywilejów innym
użytkownikom (bardzo niebezpieczna opcja)
Przywileje EXECUTION, FILE, RELOAD, SHOW DATABASES,
SHUTDOWN oraz SUPER są przywilejami administracyjnymi i mogą być
nadane tylko na poziomie globalnym (używając ON *.*). Pozostałe
przywileje mogą być nadawane bardziej specyficznie.
Opcji USAGE używamy, kiedy chcemy założyć konto użytkownikowi nie
nadając mu żadnych przywilejów.
Jedynymi przywilejami (typ_przyw), które można użyć określając dostęp
na poziomie tablicowym, są: SELECT, INSERT, UPDATE, DELETE,
CREATE, DROP, GRANT OPTION, INDEX oraz ALTER
Jedynymi przywilejami (typ_przyw), które można określić przy dostępie
do kolumn, są: SELECT, INSERT oraz UPDATE
W przypadku określania przywilejów na poziomie globalnym, bazodanowym
lub tablicowym, polecenie GRANT ALL przypisze tylko te przywileje, które
można przypisać na danym poziomie. Na przykład, jeśli użyjemy GRANT
ALL ON baza.* a więc będziemy chcieli nadać wszystkie przywileje na
poziomie bazodanowym, wówczas nie zostanie nadany żaden przywilej
tylko-globalny, na przykład FILE.
W przypadku poziomu kolumnowego (a więc wtedy, gdy w poleceniu
GRANT podamy listę kolumn), musimy wyszczególnić wszystkie przywileje,
jakie mają być nadane. Nie można w tym przypadku użyć GRANT ALL.
DROP USER
Składnia polecenia DROP USER jest następująca
DROP USER user
Polecenie DROP USER usuwa konto które nie ma żadnych przywilejów,
usuwając odpowiednie wpisy z tablicy user w bazie mysql.
Aby usunąć z bazy konto użytkownika, należy wykonać następujące kroki:
1. wykonać polecenie SHOW GRANTS aby dowiedzieć się, jakie
przywileje posiada dany użytkownik, którego konto chcemy usunąć, np.
SHOW GRANTS FOR root@'localhost';
+--------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
+--------------------------------------------------
------------------+
WITH GRANT OPTION |
------------------+
2. Użyć polecenia REVOKE aby odebrać wszystkie przywileje
użytkownikowi, które zostały wyświetlone poleceniem SHOW GRANTS.
Ta procedura usunie wszystkie wpisy dla danego konta, z tablic z bazy
mysql, zawierających informacje o przywilejach, poza wpisami w
tablicy user. Usunie też wszystkie globalne przywileje w tablicy user
3. Użyć polecenia DROP USER aby usunąć odpowiednie wpisy z tablicy
user.
SET PASSWORD
Polecenie SET PASSWORD służy do przypisania hasła dla istniejącego konta
użytkownika.
Ustawienie hasła samemu sobie:
SET PASSWORD = PASSWORD('jakies haslo');
Ustawienie hasła zadanemu użytkownikowi: SET PASSWORD FOR uzytkownik = PASSWORD('haslo');
lub w nowszych wersjach:
ALTER USER uzytkownik IDENTIFIED BY 'haslo';
Wartość uzytkownik powinna być w formacie
'nazwa_uzytkownika'@'nazwa_hosta', gdzie
nazwa_uzytkownika oraz nazwa_hosta są wartościami pól odpowiednio
User oraz Host z tablicy mysql.user danego rekordu.
Te dwa pola jednoznacznie identyfikują konto użytkownika (w tablicy
mysql.user pola User oraz Host razem tworzą klucz unikalny).
Na przykład, gdybyśmy w tablicy mysql.user mieli rekord z polami User
oraz Host o wartościach odpowiednio równych student oraz %.edu.pl,
wówczas ustawienie hasła mogłoby wyglądać tak
SET PASSWORD
FOR 'student'@'%.edu.pl' = PASSWORD('haslo');
lub równoważnie za pomocą dwóch poleceń: UPDATE oraz FLUSH PRIVILEGES
UPDATE mysql.user SET Password=PASSWORD('haslo')
WHERE User='student' AND Host='%.edu.pl';
FLUSH PRIVILEGES;
Programy dostarczane z MySQL, służące do administracji bazą MySQL
Poniżej omówiono niektóre z programów dostarczanych wraz z serwerem bazy
danych MySQL. Większość z nich posiada wiele innych opcji, których nie
omówiono. Aby dowiedzieć się czegoś na temat możliwych opcji tych poleceń,
wystarczy w przypadku każdego z programów wydać następującą komendę:
shell> nazwa_programu --help
lub po prostu
shell> nazwa_programu
UWAGA! Wyrażenie „shell> ” oznacza umowny znak zachęty systemu
operacyjnego działającego w trybie linii komend – w Windows w pracowniach
komputerowych może to być np. „C:\users\student” lub „# ”.
mysqladmin
Jest to program służący do przeprowadzania czynności administracyjnych.
Można przy pomocy tego polecenia sprawdzić status serwera baz danych,
sprawdzić konfigurację, utworzyć i usunąć bazy danych, i inne.
Sposób wywołania:
shell> mysqladmin [opcje] komenda1 [opcje_komendy]
komenda2 ...
mysqladmin rozumie nastepujące komendy:
o create nazwa_bazy
utworzenie bazy danych o nazwie nazwa_bazy. o drop nazwa_bazy
usunięcie bazy danych o nazwie nazwa_bazy. o extended-status
wyświetla status serwera i aktualne wartości zmiennych. o flush-hosts
opróżnia z informacji pamięć hosta.
o flush-logs
"wypuszcza" wszystkie informacje czekające na zapisanie do plików
dziennika. o flush-privileges
odświeża informacje o przywilejach zapisanych w tablicach. o flush-status
wyczyszcza zmienne statusu. o flush-tables
"wypuszcza" wszystkie informacje czekające z jakichś powodów do
zapisania w tablicach. o flush-threads
opróżnia z informacji pamięć wątków. o kill id, id, ...,
"zabija" wątki serwera o odpowiednich numerach id. o password nowe_haslo
zmienia hasło użytkownikowi, który właśnie posługuje się komendą
mysqladmin.
o ping
sprawdza, czy serwer MySQL jest uruchomiony. Jeśli serwer działa, to
zwróci wartość 0 lub „mysqld is alive”, natomiast wartość 1 zostanie
zwrócona jeśli MySQL nie działa. o processlist
pokazuje listę aktywnych wątków serwera. Wynik jest taki sam,
jakbyśmy w trybie interaktywnym wydali polecenie SQL SHOW
PROCESSLIST. o reload
przeładowuje informacje o przywilejach użytkowników. o refresh
"wypuszcza" wszystkie informacje czekające do zapisania w tablicach,
zamyka, a następnie otwiera pliki dziennika (pliki log). o shutdown
zatrzymuje pracę serwera baz danych. o status
wypisuje w skrócie informacje o statusie pracy serwera. Jest to skrócona
informacja, jaką uzyskujemy w trybie interaktywnym, jeśli wykonamy
polecenie status.
o variables
wyświetla wartości zmiennych serwera bazy danych o version
wyświetla wersję serwera bazy danych i dodatkowo status.
Można próbować wpisać zamiast pełnej nazwy komendy jej skróconą postać.
Na przykład:
shell> mysqladmin proc stat
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 6 | paws | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3542 Threads: 1 Questions: 11 Slow queries: 0 Opens: 6
Flush tables: 1 Open tables: 0 Queries per second avg: 0.003
mysqlshow
mysqlshow jest programem służącym do szybkiego spojrzenia na bazę
danych, strukturę tablic w bazie, na kolumny i indeksy. mysqlshow jest
jakby poleceniem wywoływanym z linii komend, które zastępuje część
możliwości poleceń SQL SET oraz SHOW
Sposób wywołania:
shell> mysqlshow [opcje] [baza [tablica [kolumna]]]
Jeśli nie podamy żadnych nazw baz danych, wówczas wszystkie dopasowane
nazwy zostaną użyte zamiast tych wyrażonych explicite, tzn. zostaną
pokazane wszystkie dostępne nazwy baz danych (ekwiwalent polecenia SQL
show databases;).
Jeśli nie podamy nazw tablic, wówczas wszystkie tablice w zadanej bazie
zostaną wyświetlone (ekwiwalent polecenia SQL show tables).
Jeśli nie podamy nazw kolumn, wówczas wszystkie kolumny dla zadanej
tablicy zadanej bazy zostaną pokazane.
Można użyć wyrażenia regularnego z powłoki lub z MySQL-a (`*', `?', `%'
lub `_'). Wówczas nastąpi dopasowywanie wzorca.
Przykłady:
shell> mysqlshow
+-----------+
| Databases |
+-----------+
| bank |
| mysql |
| prod |
| test |
| wydatki |
+-----------+
albo
shell> mysqlshow test
+------------+
| Tables |
+------------+
| dostawy |
| klient |
| sesje |
| towar |
| zamowienia |
+------------+
Uwaga – aby obejrzeć dane zastrzeżone tylko dla wybranego użytkownika,
należy podać opcje logowania/hasła: –u user –p , np. –u root -p.
perror
perror jest programem służącym do wyjaśniania co oznacza dany kod
błędu. W większości błędów systemowych, MySQL wypisuje krótką
wiadomość tekstową (która często niewiele mówi) oraz kod błędu, np.
shell> wiadomość ... (errno: #)
shell> wiadomość ... (Errcode: #)
Dzięki poleceniu perror możemy dowiedzieć się co znaczy ten kod błędu,
który wyświetlił nam MySQL, a więc dowiedzieć się czegoś więcej na temat
błędu, który popełniliśmy my lub użytkownicy.
Sposób wywołania:
shell> perror [opcje] kod_bledu ...
Na przykład
shell> perror 13 64
Error code 13: Permission denied
Error code 64: Machine is not on the network
Inne komendy dostępne po instalacji MySQL:
mysqlbinlog - służy do analizy logów serwera MySQL, zapisanych w
postaci binarnej.
mysqlcheck - służy do analizy i ewentualnej naprawy tablic
mysqldump - służy do wykonania backupu danych. Opisane szerzej w
dalszej części wykładu.
mysqlimport - służy do wykonania importu danych, tak jak polecenie
SQL LOAD DATA INFILE.
Przykłady najczęstszych działań administratora dotyczących pracy serwera
Uruchomienie serwera baz danych MySQL
/Linux/ Serwer baz danych jest uruchamiany tak samo jak inne programy
oparte o zasadę działania demonów. Po uruchomieniu demona, zaczyna on
nasłuchiwać na odpowiednim porcie zestawu protokołów TCP/IP i kiedy
dostaje sygnał od niższych warstw stosu protokołu, rozpoczyna analizę
żądania.
MySQL dostarcza specjalny skrypt o nazwie mysql.server służący do
uruchamiania demona MySQL. Czasem sam plik jest nazywany w różnych
dystrybucjach w różny sposób, na przykład mysqld, mysql-server.sh
lub po prostu mysql. Skrypt ten uruchamia demona MySQL dołączając
odpowiednie opcje albo ze specjalnego pliku konfiguracyjnego, lub też z linii
komend.
/Windows/ Serwer zwykle uruchamia się dedykowanym programem
okienkowym, np. XAMPP Control Panel w XAMPP.
Demon MySQL, mysqld, akceptuje wiele opcji podawanych albo z linii
komend, albo też wpisywanych do plików konfiguracyjnych. Aby dowiedzieć
się więcej o tych opcjach można wydać komendę:
shell> mysqld --help
Aby uruchomić serwer baz danych MySQL należy uruchomić skrypt,
dostarczany przez twórców MySQL, z opcją start. A zatem:
shell> mysql-server.sh start
Zostaną wtedy wczytane wszystkie opcje z plików konfiguracyjnych (jeśli
takie istnieją) i zostanie rozpoczęte nasłuchiwanie na porcie 3306 (lub innym,
jeśli tak zdecydujemy), czyli rozpocznie się praca serwera baz danych.
Ponieważ najczęściej opcje konfiguracyjne dla demona baz danych jak też
dla innych programów dostarczanych wraz z serwerem baz danych (na
przykład klient bazy danych MySQL, program do backupów mysqldump i
inne) umieszcza się dla porządku w specjalnym pliku konfiguracyjnym,
zatem w dalszej części zostaną omówione niektóre opcje oraz sama idea
pliku konfiguracyjnego MySQL.
Plik my.cnf
Programy MySQL, tak jak wiele innych programów, mogą czytać pewne
opcje startowe z tzw. plików konfiguracyjnych. Takie pliki są wygodnym
sposobem na zadanie wielu opcji podczas uruchamiania programów MySQL,
których nie potrzeba wówczas wpisywać przy każdym kolejnym
wykonywaniu programu.
W systemach Windows (XAMPP) jest to plik C:\xampp\mysql\bin\my.ini
W systemach UNIX-owych plikiem konfiguracyjnym jest:
plik opcje
/etc/my.cnf globalne
DATADIR/my.cnf opcje serwerowe
defaults-extra-file plik zadany po opcji
--defaults-extra-file=sciezka
~/.my.cnf opcje użytkownika
DATADIR oznacza katalog z bazami danych. Najczęściej jest to katalog
/var/db/mysql, albo /usr/local/mysql/data, albo
/usr/local/var.
MySQL szuka plików konfiguracyjnych w kolejności takiej jak to
wymieniono, a więc najpierw w katalogu /etc, następnie w katalogu
DATADIR itd. Kiedy znajdzie odpowiedni plik, szuka dalej w pozostałych
miejscach i jeśli znajdzie odpowiedni plik czyta z niego opcje
konfiguracyjne, przy czym jeśli zdefiniowano w kolejnym pliku tą sama
opcję to zostaje ona nadpisana przez nową wartość.
Sposób podawania opcji w pliku konfiguracyjnym jest podobny do sposobu
podawania opcji w linii komend, tyle, że należy ominąć poprzedzające dwa
myślniki. Na przykład --quick lub --host=localhost w linii komend
powinny być zastąpione odpowiednio przez quick i host=localhost w
pliku konfiguracyjnym.
Kilka zasad dotyczących składni w my.ini:
puste linie są ignorowane.
linie komentarzy rozpoczynają się znakiem # lub ;. [grupa]
w nawiasach kwadratowych jest nazwą programu, którego dotyczą
odpowiednie opcje pisane w następnych liniach, na przykład [mysqld]
albo [mysqldump] Po tej linii wszystkie opcje pisane w następnych
liniach dotyczą tego właśnie programu. opcja
jest to równoważne użyciu --opcja w linii komend. opcja=wartość
jest to równoważne użyciu --opcja=wartość w linii komend set-variable=nazwa_zmiennej=wartość
jest to równoważne użyciu
--set-variable=nazwa_zmiennej=wartość w linii komend.
Polecenie to przypisuje zadanej zmiennej bieżącą wartość.
Przykład:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M
character-set-server=latin2
#skip-networking
[mysql]
default-character-set=latin2
[mysqldump]
quick
Grupa [client] jest czytana przez wszystkie programy klienckie ale nie
jest czytana przez mysqld. Dzieki temu możemy zadać opcje dla wszystkich
programów klienckich, które korzystają z serwera baz danych
Zatrzymanie pracy serwera bazy danych MySQL
Aby bezpiecznie zatrzymać pracę serwera baz danych należy posłużyć się
skryptem mysql-server.sh lub programem o innej nazwie, jak to
zostało wyżej opisane.
shell> mysql-server.sh stop
Można też wydać polecenie
shell> mysqladmin shutdown
lub też wydać odpowiednie polecenie SQL
SHUTDOWN;
Proces zatrzymywania pracy serwera bazy danych jest dość ważnym
procesem, w szczególności wówczas, gdy serwer baz danych jest mocno
obciążony. Zachodzi bowiem wtedy niebezpieczeństwo, że rozpoczęte liczne
transakcje i inne działania nie zostaną poprawnie zakończone.
Proces zatrzymania pracy serwera baz danych można podzielić na
następujące etapy:
1. Rozpoczęcie procesu zatrzymania.
Następuje po uruchomieniu którejś z wyżej wymienionych komend.
Może też ten proces być zainicjowany gdy serwer otrzyma odpowiedni
sygnał od systemu operacyjnego. W systemach UNIX będzie to
SIGTERM a w systemie Windows sygnał od menadżera zadań.
2. Jeśli to konieczne, to zostaje uruchomiony specjalny proces
(wątek) zatrzymywania pracy serwera
3. Serwer przestaje akceptować nowe połączenia
Odbywa się to poprzez zamknięcie możliwości połączeń sieciowych.
4. Serwer zatrzymuje aktualne zadania
Transakcje są kończone poprzez ROLL BACK (a zatem nie są
wykonywane w całości) natomiast inne działania mogą być częściowo
wykonane a częściowo niewykonane.
5. Wypuszczane są wszystkie dane czekające z jakichś powodów w
buforach i zamykane są tablice.
6. Następuje rzeczywiste zatrzymanie pracy serwera
Analizowanie danych dziennika pracy bazy danych (logów)
Każdy administrator bazy danych musi troszczyć się o to jak pracuje baza
danych i sprawdzać, czy nie dzieje się z nią coś złego. Do tego służą
specjalne pliki (tzw. logi) do których są zapisywane różne informacje o pracy
serwera, procesie jego uruchamiania i zatrzymywania, jak też (na życzenie) o
działaniach użytkowników). Ciekawych informacji może też dostarczać sam
system operacyjny, o ile jest w stanie to zrobić (na przykład system Windows
niewiele pod tym względem potrafi).
Do administratora baz danych należy systematyczne przeglądanie logów
pracy serwera i wyciąganie wniosków z wydarzeń związanych z pracą
serwera.
MySQL posiada kilka różnych plików - dzienników pracy.
Plik log Informacja w tym pliku
nazwa_hosta.err
mysql_error.log
informacje o problemach związanych z
uruchomieniem, zatrzymaniem i pracą serwera baz
danych
log dla tablic informacje o zmianach w tablicach
log zapytań informacje o działaniach użytkowników
log binarny informacja o wszystkich wydawanych poleceniach,
które zmieniły dane w tablicach
log "powolny"
informacja o wszystkich poleceniach, które
wykonywały się dłużej niż to określono w zmiennej
long_query_time i o tych, które nie używały indeksów.
Domyślnie, wszystkie logi znajdują się w katalogu razem z plikami danych
bazy danych. Można sprawić, aby serwer baz danych wyczyścił bufor
pamięci informacji dla plików log i zrzucił oczekujące w buforze dane do
logów (polecenia shell> mysqladmin flush-logs lub shell> mysqladmin refresh
Pliki binarne służą do pewniejszego odtworzenia danych w bazie danych w
wypadku ich nagłego uszkodzenia. W plikach tych bowiem jest informacja o
wszystkich poleceniach, które zmieniały dane w bazie danych.
Aby serwer mógł pisać informacje do logów binarnych, to należy albo
uruchomić serwer baz danych z opcją --log-bin albo też w pliku
konfiguracyjnym wpisać linijkę z log-bin. Uwaga. Pliki logów binarnych
zajmują sporo miejsca na serwerze bazodanowym. Dlatego należy do
zmiennej max_binlog_size wstawić odpowiednią wartość w bajtach.
Jeśli ta wartość zostanie przekroczona, wówczas plik jest zamykany i plikiem
dla logów binarnych staje się wtedy inny plik, różniący się najczęściej
numerem w nazwie.
Wszystkie pliki binarne logów można usunąć poleceniem RESET MASTER.
Aby dowiedzieć się co takiego wykonywali użytkownicy w bazach danych
MySQL należy albo uruchomić serwer baz danych z opcją --log albo też w
pliku konfiguracyjnym wpisać linijkę z log. Wtedy wszystkie polecenia
wykonywane przez użytkowników będą zapisywane do tego pliku dziennika.
Wygląda to mniej więcej następująco:
Time Id Command Argument
041204 8:43:45 1 Connect root@localhost on
041204 8:43:49 1 Query show tables
041204 8:43:54 1 Query show databases
041204 8:43:58 1 Query SELECT DATABASE()
1 Init DB mysql
041204 8:44:15 1 Query select Host,User,Password from user
041204 8:44:17 1 Quit
Przykłady najczęstszych działań administratora dotyczących użytkowników
Dodanie nowego konta użytkownika bazy danych MySQL
Nowe konto dla użytkownika bazy (baz) danych można założyć na kilka
sposobów:
1. Używając polecenia GRANT
2. Bezpośrednio manipulując zawartością odpowiednich tablic w bazie
mysql.
3. Używając polecenia CREATE USER (od wersji 5.x) a potem GRANT
4. Używając różnych programów z interfejsem, np. phpMyAdmin
Preferowany jest pierwszy lub trzeci sposób założenia konta, z uwagi na
mniejsze prawdopodobieństwo popełnienia błędu.
Aby dodać konto użytkownika należy najpierw połączyć się z bazą danych
mysql jako administrator, który powinien mieć prawo do wykonywania
poleceń INSERT w bazie mysql oraz do wykonywania RELOAD. Następnie
jako administrator możemy już dodawać konta użytkowników.
Przykłady:
GRANT ALL PRIVILEGES ON *.* TO 'x'@'localhost'
IDENTIFIED BY 'haslo' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'x'@'%'
IDENTIFIED BY 'haslo' WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO admin'@'localhost';
GRANT USAGE ON *.* TO 'someone'@'localhost';
W dwóch pierwszych przypadkach założono konto użytkownikowi o
identyfikatorze ‘x’ z hasłem ‘haslo’. Obydwa konta mają uprawnienia
administratora, ze wszystkimi możliwymi przywilejami, nawet z
przywilejami nadawania i odbierania przywilejów. Pierwsze konto
('x'@'localhost') może być używane tylko przy połączeniu z lokalnego
komputera (localhost), natomiast drugie konto ('x'@'%') z dowolnego innego
komputera. Warto zauważyć, że aby móc korzystać z konta 'x' z dowolnego
komputera należy posiadać obydwa konta. Powodem tego jest różnica w
zawartości w polu Host tablicy mysql.user i w sposobie sortowania
danych zawartych w tej tablicy. Praktycznym wynikiem tego jest fakt, że
MySQL odróżnia konto dla połączeń z localhosta w sposób wyjątkowy.
Jeżeli chcemy więc mieć dostęp rzeczywiście ze wszystkich komputerów,
musimy założyć dwa konta: jedno do połączeń z localhosta, a drugie do
połączeń ze wszystkich innych komputerów.
Trzecie konto ma nazwę admin i nie ma hasła. Tego konta można używać
tylko z localhosta. Użytkownikowi admin nadano przywileje wykonywania
poleceń RELOAD oraz PROCESS. Oznacza to, że ten użytkownik może
wykonywać polecenia mysqladmin reload, mysqladmin refresh,
oraz mysqladmin flush-xxx oraz mysqladmin processlist.
Nie nadano żadnych przywilejów dostępu do danych w żadnej bazie danych.
Czwarte konto ma nazwę ‘someone’ i nie ma ustawionego hasła. Można
używać tego konta do połączeń z localhosta. Nie nadano żadnych
szczególnych przywilejów (USAGE). Oznacza to zapewne, że te przywileje
zostaną nadane później.
Następny przykład to założenie trzech kont i nadanie im dostępu do zadanych
baz danych. Każde z tych kont ma nazwę 'normal' i hasło 'haslo'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
ON bank.*
TO 'normal'@'localhost'
IDENTIFIED BY 'haslo';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON wydatki.*
TO 'normal'@'whitehouse.gov'
IDENTIFIED BY 'haslo';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
ON klient.*
TO 'normal'@'serwer.domena'
IDENTIFIED BY 'haslo';
Użytkownicy ‘normal’ mają następujące prawa:
W pierwszym przypadku dostęp do wszystkich danych z bazy bank ale tylko
z localhosta.
W drugim przypadku dostęp do wszystkich danych z bazy wydatki ale tylko z
hosta whitehouse.gov.
W trzecim przypadku dostęp do wszystkich danych z bazy klient ale tylko z
hosta serwer.domena.
Usunięcie konta użytkownika bazy danych MySQL
Aby usunąć konto użytkownika z bazy danych MySQL należy posłużyć się
albo poleceniem DROP USER (od wersji 4.1.1) lub też po prostu
odpowiednim poleceniem DELETE. W obydwu przypadkach trzeba najpierw
dowiedzieć się, jakie przywileje posiada właściciel konta (polecenie SHOW
GRANTS) a następnie usunąć przywileje i dopiero na końcu usunąć konto
użytkownika.
A zatem w przypadku użytkownika normal@localhost (konto do połączeń z
bazą bank z localhosta)
SHOW GRANTS FOR 'normal'@'localhost'; +------------------------------------------------------
| GRANT USAGE ON *.* TO 'normal'@'localhost' IDENTIFIED
+------------------------------------------------------
---------------------------------------------------------+
BY PASSWORD '*05A8A1A73083F816772592F13D11C8AA5CCD9681' |
---------------------------------------------------------+
+----------------------------------------------------
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
+----------------------------------------------------
------------------------------------+
ON `bank`.* TO 'normal'@'localhost'|
------------------------------------+
REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'normal'@'localhost';
DROP USER 'normal'@'localhost';
W przypadku użytkownika [email protected]
SHOW GRANTS FOR 'normal'@'whitehouse.gov'; +-----------------------------------------------------------
| GRANT USAGE ON *.* TO 'normal'@'whitehouse.gov' IDENTIFIED
+-----------------------------------------------------------
---------------------------------------------------------+
BY PASSWORD '*05A8A1A73083F816772592F13D11C8AA5CCD9681' |
---------------------------------------------------------+
+----------------------------------------------------
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
+----------------------------------------------------
--------------------------------------------+
ON `wydatki`.* TO 'normal'@'whitehouse.gov'|
--------------------------------------------+
REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
ON `wydatki`.* FROM 'normal'@'whitehouse.gov';
DROP USER 'normal'@'whitehouse.gov';
A w przypadku użytkownika [email protected]
SHOW GRANTS FOR 'normal'@'serwer.domena'; +-----------------------------------------------------------
| GRANT USAGE ON *.* TO 'normal'@'serwer.domena' IDENTIFIED
+-----------------------------------------------------------
---------------------------------------------------------+
BY PASSWORD '*05A8A1A73083F816772592F13D11C8AA5CCD9681' |
---------------------------------------------------------+
+----------------------------------------------------
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
+----------------------------------------------------
------------------------------------------+
ON `klient`.* TO 'normal'@'serwer.domena'|
------------------------------------------+
REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
ON `klient`.* FROM 'normal'@'serwer.domena';
DROP USER 'normal'@'serwer.domena';
Wprowadzanie ograniczeń i nadawanie przywilejów dla konta
użytkownika
Poza opisanymi wyżej zmianami przywilejów dla kont użytkowników można
uściślić też ograniczenia dotyczące stopnia wykorzystywania możliwości
serwera baz danych i udostępniania zasobów baz danych.
Możemy ograniczać możliwość korzystania z następujących zasobów
serwera baz danych (począwszy od MySQL wersji 4.0.2):
o Liczba zapytań na godzinę dla zadanego konta,
o Liczba zmian w bazie na godzinę dla zadanego konta,
o Liczba połączeń na godzinę dla zadanego konta.
GRANT ALL ON klient.* TO 'user'@'localhost'
IDENTIFIED BY 'haslo'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5;
SHOW GRANTS FOR 'user'@'localhost';
+---------------------------------------------------------
| GRANT USAGE ON klient.* TO 'user'@'localhost' IDENTIFIED
+---------------------------------------------------------
-------------------------------------------------------
BY PASSWORD '*05A8A1A73083F816772592F13D11C8AA5CCD9681'
-------------------------------------------------------
----------------------------------------------------
WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10
----------------------------------------------------
--------------------------
MAX_CONNECTIONS_PER_HOUR 5
--------------------------
--------------------------------------------------------+
GRANT ALL PRIVILEGES ON `klient`.* TO 'user'@'localhost'|
--------------------------------------------------------+
Wpisy o takich ograniczeniach pojawiają się w tabeli user bazy mysql w
odpowiednich polach. Domyślną wartością tych pól jest wartość 0, co znaczy
- bez ograniczeń. Jeśli więc w poleceniu GRANT nie umieścimy klauzul
ograniczających zakres używania zasobów bazy danych, wtedy domyślnie
zostanie dodane: "możliwość korzystania bez ograniczeń".
Aby zatem zmienić ograniczenia (lub ich brak) dla już istniejącego konta,
należy użyć polecenia GRANT USAGE na poziomie globalnym (ON *.*),
na przykład:
GRANT USAGE ON *.* TO 'student'@'localhost'
WITH MAX_QUERIES_PER_HOUR 100;
Wyrażenie powyższe pozostawia istniejące przywileje bez zmian, i
modyfikuje jedynie ograniczenia do korzystania z zasobów.
Odwrotnym zadaniem jest usunięcie istniejących ograniczeń do korzystania z
zasobów. W tym celu należy ustawić wartość pola ograniczenia na równą 0.
Na przykład, aby usunąć ograniczenie liczby połączeń na godzinę
użytkownika student należy użyć następującego polecenia:
GRANT USAGE ON *.* TO 'student'@'localhost'
WITH MAX_CONNECTIONS_PER_HOUR 0;
Zliczanie liczby wykorzystywania zasobów przez danego użytkownika
odbywa się ilekroć tylko użytkownik wykonuje pewne działania (jeśli
oczywiście nadaliśmy polom opisanym wyżej wartości inne niż zero). Jeśli
na przykład użytkownik student wykona w czasie krótszym niż godzina
maksymalną dopuszczalną dla niego liczbę połączeń z bazą danych, wówczas
kolejne połączenia do pełnej godziny są odrzucane i pojawia się odpowiednia
informacja. Podobnie jest w przypadku innych ograniczeń.
Istnieje sposób na wyzerowanie aktualnej liczby wykorzystania zasobów.
Można to zrobić dla wszystkich kont razem, albo tylko dla wybranych kont.
o Aby wyzerować limity dla wszystkich kont, należy posłużyć się
poleceniem: FLUSH USER_RESOURCES lub FLUSH PRIVILEGES
lub komendą
shell> mysqladmin reload.
o Aby wyzerować liczniki dla wybranego konta użytkownika, należy
posłużyć się poleceniem takim samym jakbyśmy dopiero określali limity
wykorzystywania zasobów a więc odpowiednim poleceniem GRANT
USAGE, w którym określimy wartość limitu taką, jaka aktualnie jest.
Backup bazy MySQL
Nawet najlepiej zabezpieczony serwer baz danych (ochrona przed intruzami,
wirusami i atakami sieciowymi, jak też ochrona przed awarią sprzętową
(zasilacze awaryjne, macierze dyskowe)) nie są w stanie w 100% zabezpieczyć
danych i struktury danych w bazie danych. Dlatego niezbędne jest poczynienie
kroków zabezpieczających dane z serwera bazy danych, dzięki którym możliwe
będzie szybkie odtworzenie stanu możliwie tuż przed awarią. Do tego służy
kopia zapasowa (ang. backup) bazy danych.
Do wykonywania kopii zapasowych służy program mysqldump. Można go
używać bądź to do wykonywania backupu wybranych tablic z wybranej bazy
danych, backupu całej bazy danych, kilku baz danych, lub też do
przetransmitowania danych do innego serwera bazodanowego SQL
(niekoniecznie MySQL).
Kopia zapasowa (backup) będzie zawierać zestaw poleceń SQL, dzięki którym
można będzie odtworzyć całą strukturę bazy danych (definicje tablic, relacje i
indeksy) oraz dane z tej bazy (polecenia INSERT).
Polecenie mysqldump posiada wiele opcji. O ich znaczeniu możemy się
dowiedzieć wydając polecenie
shell> mysqldump --help
Istnieją generalnie trzy sposoby wykonania mysqldump:
shell> mysqldump [opcje] nazwa_bazy [tablice]
shell> mysqldump [opcje] --databases baza1 [baza2
...]
shell> mysqldump [opcje] --all-databases
We wszystkich tych wypadkach zrzut (ang. dump) bazy danych nastepuje do
pliku STDOUT. Dlatego normalnie przekierowuje się strumień wyjścia do pliku,
który wówczas będzie rzeczywiście naszą kopią zapasową, np.
shell> mysqldump moja_baza > backup_moja_baza.sql
Wówczas odtwarza się taką bazę danych jednym poleceniem:
shell> mysql moja_baza < backup_moja_baza.sql
lub też:
shell> mysql -e
"source /sciezka_do_pliku/backup_moja_baza.sql"
moja_baza
Jeśli nie podamy żadnych nazw tabel, lub jeśli użyjemy opcji --databases
lub też --all-databases wówczas całe bazy danych zostaną skopiowane do
kopii zapasowej.
UWAGA! Program mysqldump może nie poradzić sobie z dużymi bazami
danych (o rozmiarze rzędu kilku GB i więcej) – w takim wypadku należy użyć
specjalizowanych narzędzi do kopiowania dużych baz danych, np. BigDump
(http://www.ozerov.de/bigdump/)
Kilka przykładów:
Skopiowanie bazy danych z jednego serwera baz danych do innego:
shell> mysqldump nazwa_bazy |
mysql --host=nazwa_hosta -C nazwa_bazy
Zrzut kilku baz danych do jednego pliku:
shell> mysqldump --databases baza1 [baza2 ...] >
bazy.sql
Zrzut wszystkich baz danych do jednego pliku:
shell> mysqldump --all-databases > bazy.sql
Top Related