08 MySQL Database Server



Instalimi i Serverit MySQL


[root@localhost student]# yum install mysql-server
......
.....



Pas instalimit eshte e nevojshme qe te bejme disa konfigurime baze te serverit MySQL si per shembull te vendosim password e perdoruesit qe eshte administrator i serverit MySQL, i cili quhet root.

Me instalimin e paketave te serverit mysql ne dosjen /usr/bin eshte shkarkuar gjithashtu edhe nje skript i cili na lehteson kryerjen e ketyre veprimeve fillestare me nje nderfaqe te thjeshte me pyetje e pergjigje.

Me instalimin e serverit MySQL krijohet automatikisht nje baze te dhenash me emrin test ne te cilen kane akses te gjithe perdoruesit. Gjate konfigurimit mund te na kerkohet nese duam apo jo ta fshijme kete baze te dhenash.

Gjithashtu me instalimin si default shtohet edhe nje perdorues anonim i cili mund te hyje ne serverin mysql. Skripti mund te na kerkoje nese duam apo ta fshijme kete perdorues.

Kujdes
Eshte e nevojshme qe sherbimi i MySQL ( mysqld ) te jete i ngritur ne menyre te tille qe skripti i konfigurimit te mund te komunikoje me te.


[root@localhost student]# service mysqld start
Starting mysqld:                                           [  OK  ]
[root@localhost student]# 


Permbajtja e skriptit /usr/bin/mysql_secure_installation 
Skripti mysql_secure_installation


Pas ekzekutimit te skriptit kemi nje instalim te MySQL Serverit te konfiguruar ne parametra baze konfigurimi.




Njohja me serverin MySQL


Pjese e procesit te instalimit eshte edhe instalimi i nje klienti mysql tekstual qe na mundeson te komunikojme me serverin mysql.

mysql
Komanda mysql na mundeson te lidhemi me nje server mysql me nje username dhe me nje fjalekalim.
Komanda mysql ka gjithashtu nje liste te gjate me opsione.

Pasi e instaluam tani mund te logohemi ne serverin MySQL nepermjet perdoruesit root me fjalekalimin qe i vendosem gjate konfigurimit te meparshem:

[root@localhost student]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 




 
Mund te shikojme nje liste me bazat e te dhenave qe ndodhen ne server:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> 


tabela mysql.user
MySQL ruan informacion per perdoruesit ne nje tabele me emrin mysql.user , e cila ne disa kollona ruan informacionin mbi perdoruesit e sistemit. Nder kollonat me te rendesishme jane user,host,password.

user , ruan username e perdoruesit per shembull root
host, ruan IP ose emrin e serverit. Nese vlera e host eshte % perdoruesi eshte e vlefshem pavaresisht nga IP e tij
password, ruan nje hash te passwrd te perdoruesit.

Per te gjeneruar nje hash te password ose per te verifikuar saktesine e nje password perdoret funksioni i mysql PASSWORD(fjalekalimi)

shembull i listimit te perdoruesve:

mysql> select user,host,password from mysql.user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *6CFD3C2C03D2B52EE0EF65B93FCAE088C09A032D |
| root | localhost.localdomain | *6CFD3C2C03D2B52EE0EF65B93FCAE088C09A032D |
| root | 127.0.0.1             | *6CFD3C2C03D2B52EE0EF65B93FCAE088C09A032D |
| ict  | %                     | *6CFD3C2C03D2B52EE0EF65B93FCAE088C09A032D |
+------+-----------------------+-------------------------------------------+
4 rows in set (0.14 sec)

mysql> 


Shembull i verifikimit te kredencialeve te nje perdoruesi

mysql> select user,host from mysql.user where user='ict' and password=PASSWORD('ict');
+------+------+
| user | host |
+------+------+
| ict  | %    |
+------+------+
1 row in set (1.65 sec)

mysql> 

Vini re qe fjalekalimi i perdoruesit ict ruhet i enkriptuar. Per te verifikuar saktesine e tij rigjenerohet hash-i nepermejt funksionit PASSWORD i cili merr si argumetn fjalekalimin e perdoriesit ict qe eshte = ict.


Parametri host
Parametri host (-h ) i komandes mysql na lejon te percaktojme serverin mysql me te cilin do te lidhemi.
Nese ky parameter nuk vendoset nenkuptohet localhost

Sic duket nga listimi i perdoruesve me lart perdoruesi root mund te lidhet vetem ne hostet : localhost, localhost.localdomain, 127.0.0.1.
Vlera % (perqindje) tek kollona host nenkupton cdo IP ne te cilen mund te lidhet perdoruesi. Per shembull perdoruesi ict mund te lidhet ne te gjitha IP qe degjon serveri.

Ne fakt konfgurimi i root qe te kape serverin vetem ne localhost eshte nje mase sigurie qe kerkon qe administratori i serverit mund te hyje ne server vetem lokalisht.

Vini re shembujt me poshte:

Nese tentojme te lidhemi me perdoruesin root ne IP e serverit (192.168.221.81 ne kete shembull) , do te marrim mesazh gabimi per arsye sepse perdoruesi root nuk lejon lidhjen nga host 192.168.221.81


[root@localhost ~]# mysql -h 192.168.221.81 -u root -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'ns.example.com' (using password: YES)
[root@localhost ~]# 


Ndersa nese do tentojme te lidhemi me perdoruesin ict i cili ka si host % 

mysql> select user,host from mysql.user where user='ict';
+------+------+
| user | host |
+------+------+
| ict  | %    |
+------+------+
1 row in set (0.13 sec)

mysql> 


 do te mund te lidhemi dhe te punojne normalisht ne database server:

[root@localhost ~]# mysql -h 192.168.221.81 -u ict -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 



Krijimi i ambjentit per perdoruesit e rinj


Per te krijuar perdorues te rinj apo per te modifikuar atributet e perdoruesve mjafton te ekzekutojme komanda mbi tabelen mysql.user.

Ne shembullin me poshte :





Shtimi i nje perdoruese ne MySQL

mysql> insert into mysql.user(user,host,password) values ('myuser','%',PASSWORD('myuser'));
Query OK, 1 row affected, 3 warnings (0.35 sec)

mysql> select user,host from mysql.user;
+--------+-----------------------+
| user   | host                  |
+--------+-----------------------+
| ict    | %                     |
| myuser | %                     |
| root   | 127.0.0.1             |
| root   | localhost             |
| root   | localhost.localdomain |
+--------+-----------------------+
5 rows in set (0.00 sec)

mysql> 


mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)

mysql> 



Krijimi i nje baze te dhenash ne MySQL

mysql> create database myuser_db;
Query OK, 1 row affected (0.09 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ict                |
| mysql              |
| myuser_db          |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 



Dhenia e te drejtave te aksesit te perdoruesit mbi nje baze te dhenash


mysql> GRANT ALL PRIVILEGES ON myuser_db.* TO myuser;
Query OK, 0 rows affected (0.18 sec)

mysql> 


Lidhja me serverin me kredencialet e reja

[root@localhost student]# mysql -h localhost -u myuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 




Konfigurimi i MySQL Server

Porta default eshte 3306

Skedari i konfigurimit te MySQL Serverit ndodhet is rregull ne /etc/my.cnf

Ne skedarin /etc/mycnf rreshtat qe fillojne me # jane komente.
Konfigurimet jane te organizuara ne grupe konfigurimesh qe fillojne me [grupi] 

Me poshte eshte nje skedar shembull /etc/my.cnf


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost student]# 



Grupi i konfigurmit 

[mysqld]

Percakton parametrat per daemon-in e mysql.

Parametri datadir=/var/lib/mysql     percakton qe si default dosja ne te cilen do te ruhet bazat e te dhenave do te jete /var/lib/mysql.

Ne kete dosje per cdo baze te re te dhenash qe krijohet do te krijohet nje dosje me te njejtin emer si baza e te dhenave. 
Brenda dosjes me emrin e bazes se te dhenave per cdo tabele qe krijohet do te kete 3 skedare me te njejtin emer por me praprashtesa te ndryshme.  
Vini re strukturen e direktorive dhe skedaret per bazen e te dhenave qe krijuam me lart me emrin ict ne te cilen u krijua nje tabele me emrin students:


[root@localhost student]# ls /var/lib/mysql -l
total 20496
-rw-rw---- 1 mysql mysql 10485760 Dec  4 01:38 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Dec  4 01:38 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Dec  3 00:45 ib_logfile1
drwx------ 2 mysql mysql     4096 Dec  4 01:29 ict
drwx------ 2 mysql mysql     4096 Dec  3 00:45 mysql
drwx------ 2 mysql mysql     4096 Dec  3 05:45 myuser_db
drwx------ 2 mysql mysql     4096 Dec  3 00:45 test
[root@localhost student]#
 

Ne permbajtjen e dosjes /var/lib/mysql  ka nje direktori me emrin ict, brenda te ciles:

[root@localhost student]# ls /var/lib/mysql/ict/ -l
total 24
-rw-rw---- 1 mysql mysql   65 Dec  3 00:50 db.opt
-rw-rw---- 1 mysql mysql 8618 Dec  3 00:58 students.frm
-rw-rw---- 1 mysql mysql   44 Dec  3 00:59 students.MYD
-rw-rw---- 1 mysql mysql 1024 Dec  3 01:19 students.MYI
[root@localhost student]# 


Ka 4 skedare.

Skedari db.opt ruan parametra te konfigurimit te bazes se te dhenave


[root@localhost student]# cat /var/lib/mysql/ict/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
[root@localhost student]#


ndersa skedaret students.frm, students.myd, students.myi ruajne te dhenat e tabeles students, si me poshte:

students.frm - ruan strukturen e tabeles students
students.myd - ruan te dhenat e tabeles students
students.myi - ruan indekset e tabeles students


Skedaret e logut
MySQL i ruan loget ne 

log-error=/var/log/mysqld.log