dr inż. Grzegorz Michalski

Administracja
bazami danych
dr inż. Grzegorz Michalski
Na podstawie wykładów dra inż. Juliusza Mikody
Dlaczego MySQL?
szybkość – programiści twierdzą, że jest najszybszą bazą
danych,
łatwość użycia – MySQL jest względnie prostym
systemem w instalacji, użytkowaniu i administracji,
koszt – bezpłatna w (MySQL Community Edition GPL), komercyjne - MySQL Enterprise – ~5000USD
na rok (http://www.mysql.com/products/),
możliwości – system wielodostępny (transakcyjny)
dostępny na wielu platformach oraz wielu odmianach
(łącznie z wersjami wieloprocesorowymi – klastrowymi)
Dostępność MySQL
Dystrybucja MySQL dostarcza wiele narzędzi:
• silniki
MySQL dostępne na wielu platformach,
• programy
klienckie mające na celu udostępnienie
zasobów (danych) serwera,
• MySQL Query
Browser, MySQL Administrator,
MySQL Migration Toolkit,
• biblioteki
klienta mające na celu wspomóc pisanie
własnych aplikacji
• ODBC,
J, Net, MXJ, C++, C (libmysql), mysqlnd
(php), OpenOffice
Architektura MySQL
Model Klient – Serwer
Protokoły komunikacyjne:
•
TCP/IP – lokalnie i zdalnie, wszystkie systemy
•
UNIX Socket – lokalnie, Unix
•
Shared Memory – lokalnie, tylko Windows
•
Named Pipes – lokalnie, tylko Windows
Instalacja MySQL
Linux (debian):
•
sudo apt-get install mysql-server mysql-client
•
sudo aptitude install mysql-server mysql-client
Windows
•
mysql-5.5.9-win32.msi
Uruchomienie MySQL
• Uruchomienie
mysql:
/etc/init.d/mysql start
• Zatrzymanie
mysql:
/etc/init.d/mysql stop
• Zrestartuj
mysql:
/etc/init.d/mysql restart
Ustawienie hasła root
Root to użytkownik uprzywilejowany (główny) zarządzający
całą bazą danych. Przydziela on prawa innym użytkownikom.
Ma dostęp do wszystkich baz danych, także bazy systemowej
MySQL.
Ustawienie hasła root odbywa się poprzez:
•
mysqladmin -u root password nowe_has o
lub bezpieczniej po zalogowaniu się:
•
mysqladmin -u root -p
•
set password for
root@localhost=password('nowe_has o');
•
set password for root@host=password('nowe_has o');
Bazy danych - zarządzanie
Wyświetlenie dostępnych baz danych
SHOW DATABASES;
Tworzenie bazy danych
CREATE DATABASE gentoo;
lub
mysqladmin create gentoo
Wybór bazy danych
USE gentoo;
Usuwanie bazy danych
DROP DATABASE gentoo;
lub
mysqladmin drop gentoo
Nowy użytkownik
mysql -u root -p
grant all on *.* to admin identified by 'haslo';
Objaśnienie:
all – użytkownikowi admin nadajemy wszystkie dostępne
prawa, z wyjątkiem grant,
*.* – oznacza nadanie praw, dla wszystkich baz danych i dla
wszystkich tabel,
identified by – rozpoznawanego przez, nowe hasło które
wprowadzimy dla tego użytkownika.
Przywileje
grant ... on …
ALL - Użytkownik otrzymuje wszystkie przywileje za
wyjątkiem GRANT OPTION
CREATE - Pozwala użytkownikowi na tworzenie tabel
SELECT - Pozwala na przeszukiwanie tabel
INSERT - Pozwala dodawać nowe informacje do tabel
SHOW DATABASES - Pozwala użytkownikowi na
przeglądanie listy dostępnych baz danych
USAGE - Służy do tworzenia użytkownika bez uprawnień
GRANT OPTION - Pozwala użytkownikowi nadawać
uprawnienia.
Dostępność
grant ALL on *.* to ...
*.* - Wszystkie tabele we wszystkich bazach danych
gentoo.* - wszystkie tabele w bazie gentoo,
gentoo.test - wszystkie uprawnienia dla tabeli test w
bazie gentoo,
Użytkownik i dostęp
grant all on *.* to admin identified by 'haslo';
Dostęp dla użytkownika admin,
grant all on *.* to admin@localhot identified by 'haslo';
Dostęp dla użytkownika jedynie z lokalnego komputera
grant all on *.* to [email protected] identified by
'haslo';
Dostęp dla użytkownika admin z zewnętrznej maszyny o
podanym numerze IP.
Przeglądanie użytkowników
Tabela user głównej bazy MySQL zawiera listę wszystkich
użytkowników systemu oraz informacje o nich:
USE mysql;
SELECT Host,User FROM user;
Wyświetlenie informacji dla użytkownika admin:
SELECT Host,User FROM user WHERE User =
'admin';
describe user;
# wszystkie pola tabeli
Usuwanie użytkowników
Aby usunąć użytkownika z systemu wystarczy usunąć
odpowiedni wpis w tabeli user:
DELETE FROM user WHERE User='admin';
Aby odświeżyć informacje o użytkownikach –
zaktualizować dane w systemie należy wykonać polecenie:
FLUSH PRIVILEGES;
Lokalizacja MySQL
Lokalizacja katalogu z danymi:
• /var/lib/mysql
– wersja rpm(deb)
• /usr/local/var
– wersja w postaci kodu źródłowego
• /usr/mysql/data
• c:\mysql\data
– wersja w postaci binarnej
– wersja Windows
Stałe dla bazy MySQL
Odczyt zmiennych systemowych:
mysqladmin -u root -p variables
mysqladmin -–port=nr -u root -p variables
Istotne dane:
• basedir
– lokalizacja bazy
• datadir
– lokalizacja ścieżki z danymi
• port
– numer portu nasłuchowego
Struktura katalogów
• Katalog
danych zawiera podkatalogi które odpowiadają
utworzonym bazą danych.
• Każdej
• *.frm
tabeli odpowiadają trzy pliki (MyISAM):
– format tabeli oraz opis jej zależności
• *.myd
– plik danych
• *.myi
– plik indeksów związanych z tabelą
• W przypadku
bazy InnoDB podkatalogi zawierają jedynie
format tabeli. Dane są mieszczone w pliku danych, w
głównym katalogu.
Przenoszenie bazy danych
• Przenoszenie
bazy danych (brak miejsca):
mysqladmin -u root -p shutdown
cd datadir
mv bazadanych nowalokalizacja
ln nowalokalizacja/bazadanych .
/etc/init.d/mysql start
Przenoszenie tabel
• Przenoszenie
pojedynczych tabel jest możliwe lecz
niewskazane.
• Przeniesione
dane mogą utrudnić modyfikację struktury
tabeli, usunięcie jej oraz optymalizację jej działania.
Administracja MySQL
• Przydatne
programy do administracji:
• mysqladmin
– pozwala wykonać wiele opcji
administracyjnych,
• mysqldump
– kopiowani bazy danych i przenoszenie
w inne miejsce,
• mysql_install_db
• myisamchk
– skrypt tworzenia nowej bazy danych
– badanie integralności bazy danych.
mysqladmin
• flush-privileges
• mysqladmin
• processlist
• reload
– odświeżenie uprawnień użytkowników
-u root -p version - Informacja o systemie
– lista aktywnych wątków
– odświeżenie tablicy uprawnien
• shutdown
• variables
– zakończenie działania serwera
– parametry systemu.
Kopia bezpieczeństwa
Podstawowe metody kopii bezpieczeństwa to:
• Kopia
plików/katalogów danych na bezpieczny nośnik
• Użycie
programu mysqldump do utworzenia kopii bazy
danych w postaci skryptu sql.
• Kopia
bezpieczeństwa wykonywana przez mysqldump jest
metodą wolniejszą. Natomiast utworzony plik tekstowy
pozwala na przeniesienie danych na inna platformę
sprzętową.
Kopia zapasowa - mysqldump
Kopia danych – plików binarnych bazy danych:
tar -czvf baza.tgz /var/lib/mysql/baza
Skrypt tworzenie kopii bezpieczeństwa:
mysqldump baza -u root -p > baza.08.03.16.sql
Kopia bezpieczeństwa w postaci archiwum:
mysqldump baza -u root -p | gzip> baza.08.03.16.sql.gz
Odtwarzanie bazy danych
Odtwarzanie z plików binarnych sprowadza się do powtórnego
skopiowania danych w miejsce ich pochodzenia. Taki
mechanizm nie jest jednak bezpieczny ze względu na
aktualizację bazy danych.
Odtwarzanie z plików tekstowych (wynik działania
mysqladmin) następuje po wykonaniu komendy:
mysql --one-database nazwa < baza.08.03.16.sql
Plik konfiguracyjny
Znajduje się w katalogu: /etc/mysql/my.cnf
Przydatne elementy:
• port
= 3306
• basedir
= /usr
• datadir
= /dane/mysql
• tmpdir
= /tmp
• max_connections
= 100