x
 Informacje dla studentów 
  Administracja bazą MySQL
    Podstawy   |   Język SQL   |     Wyrażenia   |     Relacje   |     Regexp   |     Transakcje   |     Administracja   |     Procedury i funkcje   |     Triggery   |     Widoki   |     Pytania egzaminacyjne


Administracja bazą danych jest zajęciem dość 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 (ciekawe, skąd to wie) to spojrzenie na serwer baz danych z punktu widzenia administratora jest doświadczeniem dość ciekawym i pouczającym.

Poniżej są przedstawione niektóre tylko zagdanienia 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 ponieżej można podzielić na nastepują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. ACLi (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 klintem 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ż w przypadku innych aplikacji internetowych.

Podstawowe zasady bezpieczeństwa podczas pracy na stanowisku admina MySQL

  • Należy bezwglę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 wszystkih 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 zainwestować w filtr pakietów (firewall). To powinno obronić serwer bazy danych przed 50% exploitów krążących w sieci i przed wieloma innymi atakami, również przed atakami DOS.

  • 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 konto |
        MAX_UPDATES_PER_HOUR konto |
        MAX_CONNECTIONS_PER_HOUR konto]]

    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.

    • 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.

    • 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.

    • 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.

    • 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 odpowiedanich danych z kilku tablic bazy mysql. Aby usunąć wszstkie 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] ...

    Powyższe polecenie działa od wersji 4.1.2 MySQL. We wczśniejszych wersjach należy posłużyć się nastepującymi dwoma poleceniami:

    REVOKE ALL PRIVILEGES ON *.* FROM user [, user] ...
    REVOKE GRANT OPTION ON *.* FROM user [, user] ...

    Dla poleceń GRANT i REVOKE typ przywileju typ_przyw może być 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
    PROCESS Możliwość wykonywania SHOW FULL PROCESSLIST
    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 bazy mysql
    SUPER Możliwość wykonywania CHANGE MASTER, KILL, PURGE MASTER LOGS, SET GLOBAL, również pozwolenie na połączenie z bazą mysql, nawet w przypadku kiedy przekroczono maksymalna liczbe 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)

    Opcji USAGE używamy, kiedy chcemy założyć konto użtkownikowi nie nadając mu żadnych przywilejów.

    Przywileje EXECUTION, FILE, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, 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.

    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 ALLON 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 w bazie MySQL, 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 odebrac 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.

    Uwaga. Polecenia DROP USER można używać dopiero od wersji 4.1.1 MySQL-a. W poprzednich wersjach usunięcie konta odbywa sie w nastepujący sposób:

    1. Wyświetlenie przywilejów dla danego użytkownika (polecenie SHOW GRANTS) a następnie usunięcie ich (polecenie REVOKE), tak jak w przypadku poprzednim.

    2. Następnie usunięcie odpowiednich wpisów z tablicy user bazy mysql i odświeżenie informacji o użytkownikach bazy danych i ich przywilejach:

      mysql> DELETE FROM mysql.user WHERE User='uztkownik' and Host='nazwa_hosta';

      mysql> FLUSH PRIVILEGES;


  • SET PASSWORD

    Polecenie SET PASSWORD służy do przypisania hasła dla istniejącego konta użytkownika.

    Składnia jest następująca:

    SET PASSWORD = PASSWORD('jakies haslo');

    lub też

    SET PASSWORD FOR uzytkownik = PASSWORD('jakies haslo');

    Pierwsze z tych poleceń ustawia hasło aktualnie pracującego z bazą użytkownika, a więc służy do ustawienia hasła samemu sobie.

    Drugie z tych poleceń ustawia hasło zadanemu użytkownikowi. Wartośc 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 bowiem 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 %.gda.pl, wówczas ustawienie hasła mogłoby wyglądać tak

    mysql> SET PASSWORD FOR 'student'@'%.gda.pl' = PASSWORD('haslo');

    lub równoważnie za pomocą dwóch poleceń: UPDATE oraz FLUSH PRIVILEGES

    1. mysql> UPDATE mysql.user SET Password=PASSWORD('haslo')
             WHERE User='student' AND Host='%.gda.pl';

    2. mysql> FLUSH PRIVILEGES;


Komendy 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 opcji, których nie omawiano. Aby dowiedzieć się czegoś na temat możliwych opcji tych poleceń, wystarczy w przypadku każdego z programów wydać nastepującą komendę:

shell> nazwa_programu --help

lub po prostu

shell> nazwa_programu

  • mysqladmin

    mysqladmin jest programem służącym do przeprowadzania czynności administracyjnych. Można przy pomocy tego polecenia sprawdzić status serwera baz danych, sprawdzic konfigurację, utworzyć i usunąć bazy danych, i inne.

    Sposób wywołania:

    shell> mysqladmin [opcje] komenda1 [opcje_komendy] komenda2 ...

    mysqladmin rozumie nastepujące komendy:

    • create nazwa_bazy,
      utworzenie bazy danych o nazwie nazwa_bazy.

    • drop nazwa_bazy,
      usunięcie bazy danych o nazwie nazwa_bazy.

    • extended-status,
      wyświetla status serwera i aktualne wartości zmiennych.

    • flush-hosts,
      opróżnia z informacji pamięć hosta.

    • flush-logs,
      "wypuszcza" wszystkie informacje czekające na zapisanie do plików dziennika.

    • flush-privileges,
      odświerza informacje o przywilejach zapisanych w tablicach.

    • flush-status,
      wyczyszcza zmienne statusu.

    • flush-tables,
      "wypuszcza" wszystkie informacje czekające z jakichś powodów do zapisania w tablicach.

    • flush-threads,
      opróżnia z informacji pamięć wątków.

    • kill id, id, ...,
      "zabija" wątki serwera o odpowiednich numerach id.

    • password nowe_haslo,
      zmienia hasło na nowe_haslo. użytkownikowi, który właśnie posługuje się komendą mysqladmin.

    • ping,
      sprawdza, czy serwer MySQL jest uruchomiony. Jeśli serwer działa, to mysqladmin ping zwróci wartość 0, natomiast wartość 1 zostanie zwrócona jeśli MySQL nie działa. Czasem otrzymujemy tez iformację w stylu mysqld is alive.

    • processlist,
      pokazuje listę aktywnych wątków serwera. Wynik jest taki sam, jakbyśmy w trybie interaktywnym wydali polecenie SQL SHOW PROCESSLIST.

    • reload,
      przeładowuje informacje o przywilejach użytkowników.

    • refresh,
      "wypuszcza" wszystkie informacje czekające do zapisania w tablicach, zamyka, a natępnie otwiera pliki dziennika (pliki log).

    • shutdown,
      zatrzymuje pracę serwera baz danych.

    • status,
      wypisuje w skrócieinformacje o statusie pracy serwera. Jest to skrócona informacja, jaką uzyskaujemy w trybie interaktywnym, jeśli wykonamy polecenie status;.

    • variables,
      wyświetla wartości zmiennych serwera bazy danych

    • version,
      wyświetla wersję serwera bazy danych i dodatkowo status.

    Mozna 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  | mate | 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.

    Na przykład

    shell> mysqlshow
    +-----------+ 
    | Databases | 
    +-----------+ 
    | bank      | 
    | mysql     | 
    | prod      | 
    | test      | 
    | wydatki   | 
    +-----------+ 

    albo

    shell> mysqlshow test
    +------------+ 
    |   Tables   | 
    +------------+ 
    | dostawy    | 
    | klient     | 
    | sesje      | 
    | towar      | 
    | zamowienia | 
    +------------+ 

  • mysqlcc

    mysqlcc (centrum dowodzenia) jest programem do zarządzania serwerem baz danych, jak również do wykonywania poleceń SQL. Udostepnia interfejs graficzny (GUI) niezależny od platformy systemowej.

    Obecnei jest to już przestarzały program. Należy używać innych podobnych programów: MySQL Administrator lub MySQL Query Browser.

  • 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ć sie 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 typu MyISAM

    mysqldump - służy do wykonania backupu danych. Opisane szerzej w dalszej części wykładu.

    mysqlhotcopy - służy do wykonania kopii zapasowych tablic typu MyISAM

    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

    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 sydtrybucjach 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 pecjalnego pliku (pliku konfiguracyjnego) lub też z linii komend.

    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 wydac 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). Innymi słowy rozpocznie sie 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 (o ile ktoś przy zdrowych zmysłach odważyłby się postawić serwer bazodanowy na takim "systemie") jest to plik

    plik opcje
    WINDIR\my.ini globalne
    C:\my.cnf globalne

    WINDIR reprezentuje tutaj katalog Windows. Jest to zwyczajowo `C:\Windows' lub `C:\WinNT'. Można to sprawdzić wydając nastepującą komendę

    C:\> echo %WINDIR%

    W systemach UNIXowych 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 tym pliku:

    • 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 bierzą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

    Opcja [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, na przykład można tam umieścić hasło (co nie jest zresztą zbyt bezpiecznym rozwiązaniem).

  • Zatrzymanie pracy serwera bazy danych MySQL

    Aby bezpiecznie zatrzymać pracę serwera baz danych należy posłużyć się skryptem mysql-server.sh lub 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

    mysql> 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 w ogóle 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.
      Nastepuje po uruchomieniu którejś z wyżej wymienionych komend. Może też ten proces być zainicjowany gdy serwer otrzyma odpowiedni sygnał od systemu opearcyjnego. 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

    W ten sposób zostają bezpiecznie zamykane połączenia, transakcje zostają zapisane w dzienniku transakcji, Kończone są wszelkie polecenia i zatrzymywany jest w końcu serwer baz danych.

  • 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 informacje o problemach związanych z uruchomieniem, zatrzymaniem i pracą serwera baz danych
    log dla tablic ISAM informacje o zmianach w tablicach typu ISAM
    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 mysqladmin flush-logs lub 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 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 różnych programów z interfejsem tj. phpMyAdmin

    Preferowany jest pierwszy sposób założenia konta, z uwagi na mniejsze prawdopodobieństwo popełnienia błędu.

    A zatem, 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:

    mysql> mysql --user=root --password mysql

    1. mysql> GRANT ALL PRIVILEGES ON *.* TO 'gerwazy'@'localhost'
             IDENTIFIED BY 'haslo' WITH GRANT OPTION;

    2. mysql> GRANT ALL PRIVILEGES ON *.* TO 'gerwazy'@'%'
             IDENTIFIED BY 'haslo' WITH GRANT OPTION;

    3. mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

    4. mysql> GRANT USAGE ON *.* TO 'someone'@'localhost';

    W dwóch pierwszych przypadkach założono konto użytkownikowi o identyfikatorze gerwazy z hasłem haslo. Obydwa konta mają uprawnienia administratora, ze wszystkimi możliwymi przywilejami, nawet z przywilejami nadawania i odbierania przywilejów. Pierwsze konto ('gerwazy'@'localhost') może być używane tylko przy połączeniu z lokalnego komputera (localhost), natomiast drugie konto ('gerwazy'@'%') z dowolnego innego komputera. Warto zauważyć, że aby móc korzystać z konta 'gerwazy' 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 i 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.

    Zamiast powyższych poleceń GRANT można użyć odpowiednich poleceń INSERT i później przeładować tablice przywilejów:

    shell> mysql --user=root --password mysql

    1. mysql> INSERT INTO user
             VALUES('localhost','gerwazy',PASSWORD('haslo'),
             'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
             'Y','Y','Y','Y');

    2. mysql> INSERT INTO user
             VALUES('%','gerwazy',PASSWORD('haslo'),
             'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
             'Y','Y','Y','Y');

    3. mysql> INSERT INTO user SET Host='localhost',User='admin',
             Reload_priv='Y', Process_priv='Y';

    4. mysql> INSERT INTO user (Host,User,Password)
             VALUES('localhost','someone','');

    mysql> FLUSH PRIVILEGES;

    Powodem, dla którego należy użyć FLUSH PRIVILEGES po wpisaniu odpowiednich danych do tablicy user jest konieczność ponownego przeczytania danych z tej tabeli (normalnie dane te czytane są przy starcie serwera baz danych). Jeśli tego nie uczynimy, wówczas wszelkie zmiany, których dokonaliśmy zostaną niezauważone do momentu ponownego uruchomienia serwera baz danych. Użycie FLUSH PRIVILEGES nie jest konieczne jeśli posługujemy się polecenienm GRANT.

    Kolejnym ważnym zadaniem jest użycie funkcji PASSWORD() do zakodowania wprowadzanego hasła podczas zakładania konta użytkownika przy pomocy polecenia INSERT. Polecenie GRANT samo koduje hasło, więc podczas używania tego polecenia nie trzeba używać funkcji PASSWORD().

    Wartości 'Y' w odpowiednich polach w poleceniu INSERT oznaczają nadanie odpowiedniego przywileju dla danego konta.

    W wyrażeniu INSERT zakładającym konto o nazwie someone nadano wartości tylko trzem polom: Host, User i Password. Poza tym, żadnemu z pól określających przywileje nie nadano dosłownych wartości. Oznacza to, że została im nadana wartość domyślna 'N', tzn. brak przywileju. Jest to więc ekwiwalent polecenia GRANT USAGE.

    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'

    mysql> mysql --user=root --password mysql

    1. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
             ON bank.*
             TO 'normal'@'localhost'
             IDENTIFIED BY 'haslo';

    2. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
             ON wydatki.*
             TO 'normal'@'whitehouse.gov'
             IDENTIFIED BY 'haslo';

    3. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
             ON klient.*
             TO 'normal'@'serwer.domena'
             IDENTIFIED BY 'haslo';

    Użytkownicy normal mają następujące prawa:

    1. W pierwszym przypadku dostęp do wszystkich danych z bazy bank ale tylko z localhosta.

    2. W drugim przypadku dostęp do wszystkich danych z bazy wydatki ale tylko z hosta whitehouse.gov.

    3. W trzecim przypadku dostęp do wszystkich danych z bazy klient ale tylko z hosta serwer.domena.

    Zamiast powyższych poleceń GRANT można użyć odpowiednich zestawów poleceń INSERT i przeładować tablice przywilejów:

    shell> mysql --user=root --password mysql

    1. mysql> INSERT INTO user (Host,User,Password)
             VALUES('localhost','normal',PASSWORD('haslo'));

    2. mysql> INSERT INTO user (Host,User,Password)
             VALUES('whitehouse.gov','normal',PASSWORD('haslo'));

    3. mysql> INSERT INTO user (Host,User,Password)
             VALUES('serwer.domena','normal',PASSWORD('haslo'));

    4. mysql> INSERT INTO db
             (Host,Db,User,Select_priv,Insert_priv,
             Update_priv,Delete_priv,Create_priv,Drop_priv)
             VALUES('localhost','bank','normal',
             'Y','Y','Y','Y','Y','Y');

    5. mysql> INSERT INTO db
             (Host,Db,User,Select_priv,Insert_priv,
             Update_priv,Delete_priv,Create_priv,Drop_priv)
             VALUES('whitehouse.gov','wydatki','normal',
             'Y','Y','Y','Y','Y','Y');

    6. mysql> INSERT INTO db
             (Host,Db,User,Select_priv,Insert_priv,
             Update_priv,Delete_priv,Create_priv,Drop_priv)
             VALUES('serwer.domena','klient','haslo',
             'Y','Y','Y','Y','Y','Y');

    mysql> FLUSH PRIVILEGES;

    Pierwsze trzy polecenia INSERT dodają nowego użytkownika o identyfikatorze normal do tablicy user, dzieki czemu może on łączyć się z serwerem baz danych z różnych hostów w sieci Internet używając hasła haslo. Jednak szystkie przywileje są domyślnie nienadane. Zatem, następne trzy polecenia nadają przywileje użytkownikowi normal w celu wykonywania przez niego operacji SELECT, INSERT, UPDATE, DELETE, CREATE, DROP w bazach bank, wydatki, klient. Dopiero te 6 poleceń INSERT odpowiada poprzednim 3 poprzednim poleceniom GRANT. A właściwie trzeba było dodać jeszcze jedno polecenie zaktualizowania informacji o użytkownikach i ich przywilejach - FLUSH PRIVILEGES;

  • 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 nastepnie 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)

    shell> mysql --user=root --password mysql

    1. mysql> 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'|
      ------------------------------------+

    2. mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM
             'normal'@'localhost';

    3. mysql> DROP USER 'normal'@'localhost';

    W przypadku użytkownika normal@whitehouse.gov (konto do połączeń z bazą wydatki z hosta whitehouse.gov)

    shell> mysql --user=root --password mysql

    1. mysql> 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'|
      --------------------------------------------+

    2. mysql> REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
             ON `wydatki`.* FROM 'normal'@'whitehouse.gov';

    3. mysql> DROP USER 'normal'@'whitehouse.gov';

    A w przypadku użytkownika normal@serwer.domena (konto do połączeń z bazą klient z hosta serwer.domena)

    shell> mysql --user=root --password mysql

    1. mysql> 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'|
      ------------------------------------------+

    2. mysql> REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
             ON `klient`.* FROM 'normal'@'serwer.domena';

    3. mysql> DROP USER 'normal'@'serwer.domena';

    W przypadku gdybyśmy chcieli (lub musieli w przypadku używania bazy MySQL starszej niż wersja 4.1.1) użyć polecenia DELETE zamiast polecenia DROP USER wówczas procedura usuwania konta użytkownika wyglądałaby podobnie do już opisanej, jednak zamiast punktu 3-go (polecenie DROP USER) należałoby użyć dwóch poleceń: (polecenia DELETE oraz polecenia FLUSH PRIVILEGES).

    A zatem w przypadku użytkownika normal@localhost zamiast punktu trzeciego mielibyśmy:

    1. mysql> DELETE FROM mysql.user
             WHERE User='normal' and Host='localhost';
    2. mysql> FLUSH PRIVILEGES;

    W przypadku użytkownika normal@whitehouse.gov zamiast punktu trzeciego mielibyśmy:

    1. mysql> DELETE FROM mysql.user
             WHERE User='normal' and Host='whitehouse.gov';
    2. mysql> FLUSH PRIVILEGES;

    A w przypadku użytkownika normal@serwer.domena zamiast punktu trzeciego mielibyśmy:

    1. mysql> DELETE FROM mysql.user
             WHERE User='normal' and Host='serwer.domena';
    2. mysql> FLUSH PRIVILEGES;

  • 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 udostepniania zasobów baz danych.

    Możemy ograniczać możliwośc korzystania z nastepujących zasobów serwera baz danych (począwszy od MySQL wersji 4.0.2):

    • Liczbę zapytań na godzinę dla zadanego konta,

    • Liczbę zmian w bazie na godzinę dla zadanego konta,

    • Liczbę połączeń na godzinę dla zadanego konta

    mysql> 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;

    mysql> 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:

    mysql> 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:

    mysql> 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.

    • Aby wyzerować limity dla wszystkich kont, należy posłużyc sie poleceniem: FLUSH USER_RESOURCES lub FLUSH PRIVILEGES lub komendą mysqladmin reload.

    • 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śc 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żą komendy mysqldump oraz mysqlhotcopy. Obydwu poleceń można używać bądź to do wykonywania backu pu 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 odtworzyc 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 sie dowiedzieć wydając polecenie

shell> mysqldump --help

Istnieją generalnie trzy sposoby wykonania mysqldump

  1. shell> mysqldump [opcje] nazwa_bazy [tablice]

  2. shell> mysqldump [opcje] --databases baza1 [baza2 ...]

  3. 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ą baze 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.

Kilka przykładów:

  • Skopiowanie bazy danych z jednego serwera baz danych do innego:

    shell> mysqldump --opt 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

Uwaga Domyślnie, mysqldump działa tak jakby był uruchomiony z opcją --quick lub --opt. Oznacza to, że cały backup jest najpierw ładowany do pamięci operacyjnej komputera. Jeśli więc mamy do czynienia z dużą bazą danych należy uruchomić mysqldump bez opcji --opt. a więc z opcją --skip-opt. Jednak opcja --opt automatycznie powoduje też zamknięcie dostępu do tablic, które aktualnie są zrzucane do pliku (co jest bardzo logicznym rozwiązaniem, bowiem uniemożliwia wprowadzanie danych w momencie kiedy robimy zrzut bazy danych do pliku). W takim wypadku, należy obok opcji --skip-opt zastosować też opcję --lock-tables.

Kolejnym problemem może być to, że opcja --opt automatycznie uruchamia też opcję --extended-insert, która powoduje zapisywanie INSERTów w postacie tzw. rozszerzonej. Takie rozszerzone polecenie INSERT jest szybsze (stąd optymalizacja (--opt) używa takiego sposobu zapisywania backupu). Wygląda ono mniej więcej następująco:

INSERT INTO `tabela` VALUES
   (wartosci_rekordu_1),
   (wartosci_rekordu_2), ... ;

zamiast normalnej ("rozszerzonej postaci"):

INSERT INTO `tabela` VALUES (wartosci_rekordu_1);
INSERT INTO `tabela` VALUES (wartosci_rekordu_2);
...

Ta pierwsza postać (skrócona) jest czasem niewygodna, jeśli zamierzamy część danych wykorzystać i skopiować z pliku z backupem. Wówczas bowiem trzeba samemu dopisać brakujące części polecenia INSERT.

Komenda mysqlhotcopy również służy do wykonywania backupu danych z bazy danych MySQL. mysqlhotcopy jest skryptem Perlowym, który używa interfejsu DBI perla do połączenia z baza danych, poleceń LOCK TABLES, FLUSH TABLES, oraz cp i scp do szybkiego wykonywania backupu. Jednak działa tylko w przypadku tablic typu MyISAM oraz ISAM oraz wyłącznie na tej samej maszynie, na której zlokalizowane są pliki baz danych.


do góry 
 Copyright © Maciej Bobrowski