MySQL
Z openSUSE wiki
(Przekierowano z Administracja MySQL)
Instalacja w openSUSE 
Najnowsze wersje MySQL możemy znaleźć zawsze pod:
http://download.opensuse.org/repositories/server:/database:/STABLE/openSUSE_11.3/ http://download.opensuse.org/repositories/server:/database:/STABLE/openSUSE_11.2/ http://download.opensuse.org/repositories/server:/database:/STABLE/openSUSE_11.1/ http://download.opensuse.org/repositories/server:/database:/STABLE/openSUSE_11.0/ http://download.opensuse.org/repositories/server:/database:/STABLE/openSUSE_Factory/ //zazwyczaj niestabilna!!! http://download.opensuse.org/repositories/server:/database:/STABLE/SLE_10/ http://download.opensuse.org/repositories/server:/database:/STABLE/SLE_11/
Tak więc zależnie od systemu dodajemy opowiednią liniję do repozytorium w Yast (lub za pomocą zyppera) oraz instalujemy MySQL.
Przykładowe dodanie repozytorium dla systemu 11.2, to:
# zypper addrepo http://download.opensuse.org/repositories/server:/database:/STABLE/openSUSE_11.2/ DatabaseMySQL
Więcej na temat zyppera, możemy znaleźć tutaj: Zypper
Potrzebne pakiety (na dzień dzisiejszy), to:
kris@sphinx:~> rpm -qa | grep -i mysql mysql-client-5.1.44-10.1.i586 mysql-5.1.44-10.1.i586 mysql-test-5.1.44-10.1.i586
Jeśli chcemy sprawdzić, gdzie znajdują się poszczególne pliki, wydajemy komendę:
kris@sphinx:~> rpm -ql mysql-5.1.36-6.7.2 | less
Dla przykładu uzyskamy:
/etc/init.d/mysql /etc/logrotate.d/mysql /etc/my.cnf /usr/lib/mysql /usr/sbin/mysqld itd.
Główny plik uruchamiający usługę MySQL, znajduje się w:
kris@sphinx:~> ls -ltr /etc/init.d/ | grep mysql -rwxr-xr-x 1 root root 15015 10-24 06:46 mysql
Uruchomienie usługi MySQL
Sprawdzamy czy mysql nasłuchuje na standardowym porcie 3306:
kris@sphinx:~> netstat -ntlp (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN - tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -
Jak widzimy powyżej nie nasłuchuje, więc próbujemy uruchomić usługę:
kris@sphinx:~> /etc/init.d/mysql start cat: /etc/my.cnf: Permission denied cat: /etc/my.cnf: Permission denied chown: changing ownership of `/var/tmp/mysql': Operation not permitted Can't create secure /var/tmp/mysql failed
Jak widzimy powyżej mamy failed. Spowodowane jest to tym, iż usługa ta należy do użytkownika root. Logujemy się więc na root'a i uruchamiamy powtórnie usługę mysql:
sphinx:~ # /etc/init.d/mysql start Starting service MySQL done
A więc mamy done (czyli wykonane) Usługa mysql wysartowała pomyślnie.
Sprawdzamy status:
sphinx:~ # rcmysql status Checking for service MySQL: running
Odpowiednio inne potrzebne polecenia, dla
- zatrzymania usługi, to:
sphinx:~ # /etc/init.d/mysql stop Shutting down service MySQL done
- restartu usługi, to:
sphinx:~ # sphinx:~ # /etc/init.d/mysql restart Restarting service MySQL Shutting down service MySQL done Starting service MySQL done
Jeszcze raz sprawdzamy czy nasza usługa nasłuchuje (możemy z opcją netstat -ntlp | grep :3306)
sphinx:~ # netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 31893/mysqld
Jak widzimy mysql nasłuchuje na porcie 3306
Uruchomienie MySQL
Aby uruchomić linię poleceń, wpisujemy w konsoli:
kris@sphinx:~> mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Ustawienia domyślne
2 domyślne konta i 3 domyślne bazy
- anonymous
- root
gdzie:
- anonymous - posiada dostęp do:
- test
- information_schema
- root - posiada dostęp do wszystkich baz dbms, tzw. full access, ale domyślnie są to jedynie bazy:
- information_schema
- mysql
- test
Użytkownik anonymous
Wyświetlmy bazy do których user anonymous ma uprawnienia:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.10 sec)
mysql> select user(); +----------------+ | user() | +----------------+ | kris@localhost | +----------------+ 1 row in set (0.00 sec)
oraz:
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2010-02-04 00:48:56 | +---------------------+ 1 row in set (0.08 sec)
mysql> exit Bye
kris@sphinx:~> whoami kris
Użytkownik root
Z użytkownika kris logujemy się do mysql root:
kris@sphinx:~> mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Jak widzimy poniżej użytkownik root posiada uprawnienia do 3 baz:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec)
Jak widzimy poniżej user root posiada uprawnienia do hosta localhost:
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
mysql> exit Bye
kris@sphinx:~> whoami kris
Dostęp przez SSH do MySQL
Próba zalogowania się na root'a:
kris@sphinx:~> ssh root@127.0.0.1 ssh: connect to host 127.0.0.1 port 22: Connection refused
Opcje jakie udostępnia usługa sshd, to:
kris@sphinx:~> /etc/init.d/sshd
Usage: /etc/init.d/sshd {start|stop|status|try-restart|restart|force-reload|reload|probe}
Nie jesteśmy zalogowani na root'a, więc:
kris@sphinx:~> /etc/init.d/sshd start grep: /etc/ssh/sshd_config: Permission denied Generating /etc/ssh/ssh_host_key. Generating public/private rsa1 key pair. open /etc/ssh/ssh_host_key failed: Permission denied. Saving the key failed: /etc/ssh/ssh_host_key. Generating /etc/ssh/ssh_host_dsa_key. Generating public/private dsa key pair. open /etc/ssh/ssh_host_dsa_key failed: Permission denied. Saving the key failed: /etc/ssh/ssh_host_dsa_key. Generating /etc/ssh/ssh_host_rsa_key. Generating public/private rsa key pair. open /etc/ssh/ssh_host_rsa_key failed: Permission denied. Saving the key failed: /etc/ssh/ssh_host_rsa_key. Starting SSH daemon/etc/ssh/sshd_config: Permission denied startproc: exit status of parent of /usr/sbin/sshd: 1 failed
Logujemy się na root'a:
kris@sphinx:~> su -
Hasło:
Startujemy usługę sshd:
sphinx:~ # /etc/init.d/sshd start Generating /etc/ssh/ssh_host_key. Generating public/private rsa1 key pair. Your identification has been saved in /etc/ssh/ssh_host_key. Your public key has been saved in /etc/ssh/ssh_host_key.pub. The key fingerprint is: 8d:23:a1:3c:0f:96:1a:a7:ff:f7:b4:ae:63:47:c2:0e root@sphinx The key's randomart image is: +--[RSA1 1024]----+ | | | | | . | | . o . o | | . O ..S . | | * +E.o.. | | o .o o. | | . =... | | ...o.*+ | +-----------------+ Generating /etc/ssh/ssh_host_dsa_key. Generating public/private dsa key pair. Your identification has been saved in /etc/ssh/ssh_host_dsa_key. Your public key has been saved in /etc/ssh/ssh_host_dsa_key.pub. The key fingerprint is: 1e:f0:7d:51:cd:06:1f:d8:75:0f:21:c4:6b:f9:20:06 root@sphinx The key's randomart image is: +--[ DSA 1024]----+ | oo B*+| | E .+ +B| | . . .o .o| | o .o =. | | S..o.o | | . . . . | | . | | | | | +-----------------+ Generating /etc/ssh/ssh_host_rsa_key. Generating public/private rsa key pair. Your identification has been saved in /etc/ssh/ssh_host_rsa_key. Your public key has been saved in /etc/ssh/ssh_host_rsa_key.pub. The key fingerprint is: dd:58:2a:1b:05:2d:1b:f6:e7:86:de:71:74:f7:74:4b root@sphinx The key's randomart image is: +--[ RSA 1024]----+ | .. | | +.. | | . =. . | | .o.=. .E=| | S ++...o=| | +. + ...| | .. o o | | . . | | | +-----------------+ Starting SSH daemon done
Wylogowujemy się z root:
sphinx:~ # logout
Logujemy się przez ssh na root:
kris@sphinx:~> ssh root@127.0.0.1 The authenticity of host '127.0.0.1 (127.0.0.1)' can't be established. RSA key fingerprint is dd:58:2a:1b:05:2d:1b:f6:e7:86:de:71:74:f7:74:4b. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '127.0.0.1' (RSA) to the list of known hosts. Password: Have a lot of fun... sphinx:~ #
Sprawdzamy wersję systemu operacyjnego:
sphinx:~ # cat /etc/SuSE-release openSUSE 11.2 (i586) VERSION = 11.2
oraz:
sphinx:~ # arp Address HWtype HWaddress Flags Mask Iface 192.168.2.1 ether 00:17:3f:10:c1:92 C wlan1
Czasami może się zdarzyć, że:
sphinx:~ # ping -c 3 sphinx ping: unknown host
Być może przyda nam się też polecenie:
kris@sphinx:~> hostname -f sphinx.egipt.pl
Więc sprawdzamy plik /etc/hosts naszym ulubionym edytorem, np.
# emacs /etc/hosts
i dodajemy linijkę:
127.0.0.2 sphinx.egipt.pl sphinx
Sprawdzamy ping:
sphinx:~ # ping -c 3 sphinx PING sphinx.egipt.pl (127.0.0.2) 56(84) bytes of data. 64 bytes from sphinx.egipt.pl (127.0.0.2): icmp_seq=1 ttl=64 time=0.055 ms 64 bytes from sphinx.egipt.pl (127.0.0.2): icmp_seq=2 ttl=64 time=0.057 ms 64 bytes from sphinx.egipt.pl (127.0.0.2): icmp_seq=3 ttl=64 time=0.056 ms --- sphinx.egipt.pl ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2000ms rtt min/avg/max/mdev = 0.055/0.056/0.057/0.000 ms
Połączenia mogą odbywać się tylko z localhost:
sphinx:~ # mysql -h sphinx ERROR 1130 (HY000): Host 'sphinx.egipt.pl' is not allowed to connect to this MySQL server
sphinx:~ # mysql -h sphinx -u kris ERROR 2005 (HY000): Unknown MySQL server host 'sphinx' (0)
sphinx:~ # mysql -h sphinx -u root ERROR 2005 (HY000): Unknown MySQL server host 'sphinx' (0)
Jeśli chcemy logować się z innego komputera w sieci, to należy podać w /etc/hosts jego dane, tj. IP lub hostname.
W pliku passwd sprawdzamy czy wszystko jest w porządku:
sphinx:~ # cat /etc/passwd | grep mysql mysql:x:60:113:MySQL database admin:/var/lib/mysql:/bin/false
Tak więc w końcu możemy zalogować się:
kris@sphinx:~> ssh root@127.0.0.1 Password: Last login: Thu Feb 4 01:18:21 2010 from localhost Have a lot of fun... sphinx:~ #
Aby przejrzeć historię logowań na ssh;
kris@sphinx:~> history | grep ssh 17 ssh root@127.0.0.1 18 /etc/init.d/sshd 19 /etc/init.d/sshd start 21 ssh root@127.0.0.1 26 ssh root@127.0.0.1 48 history | grep ssh
MySQL Admin
Pomoc do mysqladmin:
kris@sphinx:~> mysqladmin --help
Przykład:
Za pomocą mysqladmin możemy ustawić sobie hasło, np.
kris@sphinx:~> mysqladmin -u root password abc123
kris@sphinx:~> echo $? 0
2 sposoby logowanie się do MySQL za pomocą hasła
Skoro za pomocą mysqladmin ustawiliśmy sobie hasło, więc nie możemy się zalogować:
kris@sphinx:~> mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Jak widzimy powyżej nie jesteśmy w stanie zalogować się na root'a bez podania hasła. Od tej pory będziemy się logować na 2 różne sposoby:
- Pierwszy sposób:
kris@sphinx:~> mysql -u root -pabc123 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \q Bye
- Drugi sposób:
kris@sphinx:~> mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Sprawdźmy jeszcze użytkownika:
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
oraz bazy:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+
3 rows in set (0.00 sec)
Baza mysql
Aby użyć danej bazy musimy posłużyć się komendą:
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
Wyświetlamy tabele:
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.00 sec)
Aby wyświetlić wszystkie informacje o userach:
mysql> select * from user -> ;
Lub też krócej, gdy potrzebne są nam tylko niektóre informacje. Jak widzimy poniżej znajduje się tutaj tylko user root:
mysql> select user,host from user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | | localhost | | root | localhost | | | sphinx | | root | sphinx | +------+-----------+ 5 rows in set (0.00 sec)
Ustawianie haseł
Jak widzimy poniżej hało (abc123) mamy ustawione tylko dla root & localhost:
mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | | | root | 127.0.0.1 | | | | localhost | | | | sphinx | | +------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
Należy ustawić również hasła dla pozostałych pozycji:
mysql> set password for ' '@'sphinx'=password('abc123'); //Uwaga! Ze względów bezpieczeństwa należy ustawiać inne hasła.
Query OK, 0 rows affected (0.02 sec)
mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | | | root | 127.0.0.1 | | | | localhost | | | | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | //teraz to hasło zostało ustawione +------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
Ustawiamy hasło dla localhost:
mysql> set password for ' '@'localhost'=password('abc123');
Query OK, 0 rows affected (0.15 sec)
mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | | | root | 127.0.0.1 | | | | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | //teraz to hasło zostało ustawione | | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | +------+-----------+-------------------------------------------+ 5 rows in set (0.05 sec)
Ustawiamy hasło dla root oraz sphinx:
mysql> set password for 'root'@'sphinx'=password('abc123');
Query OK, 0 rows affected (0.03 sec)
mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | //teraz to hasło zostało ustawione | root | 127.0.0.1 | | | | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | +------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
I zostało nam ostatnie hasło do ustawienia, czyli:
mysql> set password for 'root'@'127.0.0.1'=password('abc123');
Query OK, 0 rows affected (0.05 sec)
mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | //teraz to hasło zostało ustawione | | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | +------+-----------+-------------------------------------------+ 5 rows in set (0.03 sec)
Na innej konsoli próbujemy się zalogować do mysql
kris@sphinx:/etc/logrotate.d> mysql ERROR 1045 (28000): Access denied for user 'kris'@'localhost' (using password: NO)
Świetnie! Wszystko przebiega pomyślnie. Zostały ustawione hasła. Teraz od tego momentu, aby zalogować się na usera anonymous, musimy wydać polecenie:
kris@sphinx:~> mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Sprawdzamy czy faktycznie jest to anonymous:
mysql> select user(); +----------------+ | user() | +----------------+ | kris@localhost | +----------------+ 1 row in set (0.22 sec)
oraz
mysql> select current_user(); +----------------+ | current_user() | +----------------+ | @localhost | +----------------+ 1 row in set (0.00 sec)
Aktualizacja bazy DBMS z usunięciem konta anonymous
Jak widzimy poniżej nie posiadamy praw dla usera anonymous ponieważ baza ta dotępna jest tylko dla root;
mysql> use mysql; ERROR 1044 (42000): Access denied for user ' '@'localhost' to database 'mysql'
mysql> DELETE FROM mysql.user WHERE user =' '; ERROR 1142 (42000): DELETE command denied to user @'localhost' for table 'user' mysql>
Więc logujemy się na root:
kris@sphinx:~> mysql -u root -pabc123 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DELETE FROM mysql.user WHERE user =' '; Query OK, 2 rows affected (0.36 sec)
lub też drugim sposobem:
mysql> DELETE FROM user WHERE user =' '; Query OK, 2 rows affected (0.36 sec)
Jak widzimy poniżej pozbyliśmy się usera anonymous:
mysql> select user,host, password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | +------+-----------+-------------------------------------------+ 3 rows in set (0.05 sec)
UWAGA! Ale to jeszcze nie koniec. Sprawdźmy co się dzieje. Pod mysql nie zalogujemy się:
kris@sphinx:~> mysql ERROR 1045 (28000): Access denied for user 'kris'@'localhost' (using password: NO)
ale już pod mysql -p, tak:
kris@sphinx:~> mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user(); +----------------+ | user() | +----------------+ | kris@localhost | +----------------+ 1 row in set (0.00 sec)
mysql> select current_user(); +----------------+ | current_user() | +----------------+ | @localhost | +----------------+ 1 row in set (0.00 sec)
Co się więc dzieje? Przecież usera anonymous usunęliśmy! Spokojnie. Wszystko jest w porządku. Potrzebna nam jest tylko jeszcze jedna komenda:
FLUSH PRIVILEGES
kris@sphinx:~> mysql -u root -pabc123 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.1.36-log SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.37 sec)
mysql> \q Bye
Jak widzimy poniżej, wszystko jest w porządku. Od momentu wydania komendy FLUSH PRIVILEGES nie możemy zalogować się już na anonymous:
kris@sphinx:~> mysql -p Enter password: ERROR 1045 (28000): Access denied for user 'kris'@'localhost' (using password: YES)
Cel został osiągnięty.
GRANT TABLES
Przykład 1
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
Jeśli użyliśmy mysql (tak jak powyżej), to piszemy:
mysql> describe db;
w przeciwnym razie piszemy:
mysql> describe mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.26 sec)
Z powyższego wyświetlmy pozycje host, db oraz user:
mysql> select host,db, user from db; +------+---------+------+ | host | db | user | +------+---------+------+ | % | test | | | % | test\_% | | +------+---------+------+ 2 rows in set (0.00 sec)
Przykład 2
mysql> describe host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
mysql> select host,db from host; Empty set (0.02 sec)
Przykład 3
mysql> select host, user, password, Shutdown_priv from user; +-----------+------+-------------------------------------------+---------------+ | host | user | password | Shutdown_priv | +-----------+------+-------------------------------------------+---------------+ | localhost | root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | Y | | sphinx | root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | Y | | 127.0.0.1 | root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | Y | +-----------+------+-------------------------------------------+---------------+ 3 rows in set (0.00 sec)
CREATE USERS
Wyświetlmy tabelle:
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.06 sec)
Dla usera:
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
posiadamy uprawnienia:
mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6691484EA6B50DDDE1926A220DA01FA9E575C18A' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec)
Nadajmy uprawnienia dla nowego usera:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'kristopher'@'%' IDENTIFIED BY 'xyz123' WITH GRANT OPTION; Query OK, 0 rows affected (0.14 sec)
oraz sprawdźmy:
mysql> select user, host, password from user; +------------+-----------+-------------------------------------------+ | user | host | password | +------------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | kristopher | % | *FEB88477570B28D197669CF6DA702E8DF7C9B2F2 | //to zostało właśnie dodane +------------+-----------+-------------------------------------------+ 4 rows in set (0.08 sec)
mysql> \q Bye
Zalogujmy się na nowego super usera:
- Pierwszy sposób:
kris@sphinx:~> mysql -u kristopher -pxyz123 mysql>
oraz sprawdźmy jego uprawnienia:
mysql> show grants; +--------------------------------------------------------------------------------------------------------------------------------------+ | Grants for kristopher@% | +--------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'kristopher'@'%' IDENTIFIED BY PASSWORD '*FEB88477570B28D197669CF6DA702E8DF7C9B2F2' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
- Drugi sposób:
Uwaga! Nazwa hosta (w tym wypadku sphinx) musi być dopisana do /etc/hosts
kris@sphinx:~> mysql -h sphinx -u kristopher -p Enter password: mysql>
oraz sprawdźmy jego uprawnienia:
mysql> show grants; +--------------------------------------------------------------------------------------------------------------------------------------+ | Grants for kristopher@% | +--------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'kristopher'@'%' IDENTIFIED BY PASSWORD '*FEB88477570B28D197669CF6DA702E8DF7C9B2F2' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
mysql> select user, host from user; +------------+-----------+ | user | host | +------------+-----------+ | kristopher | % | | root | 127.0.0.1 | | root | localhost | | root | sphinx | +------------+-----------+ 4 rows in set (0.00 sec)
Niebezpieczeństwa
Spróbujmy dodać usera anonymous dla wszystkich hostów:
1. Pierwszy przypadek z hasłem
mysql> GRANT ALL PRIVILEGES ON *.* TO ' '@'%' IDENTIFIED BY 'xyz123'; Query OK, 0 rows affected (0.04 sec)
mysql> select user, host, password from user; +------------+-----------+-------------------------------------------+ | user | host | password | +------------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | | % | *FEB88477570B28D197669CF6DA702E8DF7C9B2F2 | //to zostało właśnie dodane | kristopher | % | *FEB88477570B28D197669CF6DA702E8DF7C9B2F2 | +------------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
Czyli i jeśli hacker zna hasło, to:
kris@sphinx:~> mysql -u hacker -p -h sphinx Enter password: mysql>
mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for @% | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO ' '@'%' IDENTIFIED BY PASSWORD '*FEB88477570B28D197669CF6DA702E8DF7C9B2F2' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER ON `test`.* TO @'%' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER ON `test\_%`.* TO @'%' | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
2. Drugi przypadek bez hasła
mysql> GRANT ALL PRIVILEGES ON *.* TO ' '@'%' IDENTIFIED BY ' '; Query OK, 0 rows affected (0.06 sec)
mysql> select user, host, password from user; +------------+-----------+-------------------------------------------+ | user | host | password | +------------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | kristopher | % | *FEB88477570B28D197669CF6DA702E8DF7C9B2F2 | | | % | | //to zostało dopisane +------------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
mysql> \q Bye
Włamywaczowi nie potrzebne jest hasło:
kris@sphinx:~> mysql -u hacker -h sphinx mysql>
mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for @% | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO ' '@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER ON `test`.* TO @'%' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER ON `test\_%`.* TO @'%' | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
Zapobieganie
Powyższe pozycje nie są zbyt bezpieczne dlatego pozbywamy się ich:
mysql> DELETE FROM mysql.user WHERE user =' '; Query OK, 1 row affected (0.35 sec)
mysql> DELETE FROM mysql.user WHERE password =' '; Query OK, 0 rows affected (0.08 sec)
mysql> DELETE FROM mysql.user WHERE host =' '; Query OK, 0 rows affected (0.05 sec)
DROP USER
Nadajemy uprawnienia nowemu userowi test:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'aaabbbccc123' WITH GRANT OPTION; Query OK, 0 rows affected (0.08 sec)
Sprawdzamy:
mysql> select user, host, password from user; +------------+-----------+-------------------------------------------+ | user | host | password | +------------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | kristopher | % | *FEB88477570B28D197669CF6DA702E8DF7C9B2F2 | | test | % | *B0FAA8A187F42C6AA69EB3788F8D202CE826DCDC | +------------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
Poleceniem drop usuwamy usera test:
mysql> drop user test; Query OK, 0 rows affected (0.09 sec)
Ponownie sprawdzamy:
mysql> select user, host, password from user; +------------+-----------+-------------------------------------------+ | user | host | password | +------------+-----------+-------------------------------------------+ | root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | sphinx | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | root | 127.0.0.1 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | | kristopher | % | *FEB88477570B28D197669CF6DA702E8DF7C9B2F2 | +------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec)
Sukces. User test został usunięty.
Inne możliwości komend:
mysql> drop user ' '; Query OK, 0 rows affected (0.09 sec)
mysql> drop user '%'; Query OK, 0 rows affected (0.09 sec)
Tworzenie i przywracanie kopii
Artykuł http://wiki.suse.pl/wiki/index.php/Backup#MySQL_-_Tworzenie_i_przywracanie_kopii pomoże każdemu użytkownikowi MySQL utworzyć oraz przywrócić kopię bazy danych
