MySQL podstawy administracji - Politechnika Gdańska · MySQL Należy bezwzględnie ustawić hasło...

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.

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.

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'


GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP

ON klient.*

TO 'normal'@'serwer.domena'


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

+-----------------------------------------------------------

---------------------------------------------------------+


---------------------------------------------------------+

+----------------------------------------------------


+----------------------------------------------------

--------------------------------------------+

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

+-----------------------------------------------------------

---------------------------------------------------------+


---------------------------------------------------------+

+----------------------------------------------------


+----------------------------------------------------

------------------------------------------+

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

MySQL podstawy administracji - Politechnika Gdańska · MySQL Należy bezwzględnie ustawić hasło...

Documents

Transcript of MySQL podstawy administracji - Politechnika Gdańska · MySQL Należy bezwzględnie ustawić hasło...