Embedded Files
Per te instaluar My-SQL Server
sudo apt-get install mysql-server
Procesi i instalimit do te na kerkoje te vendosim nje fjalekalim per administratorin e serverit mysql(root).
Per te verifikuar nese mysql serveri u instalua mund te afishojme sherbimet qe degjojne ne server nepermjet komandes netstat
student@ubuntu:~$ netstat -t -a
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 ns.example.com:domain *:* LISTEN
tcp 0 0 localhost:domain *:* LISTEN
tcp 0 0 *:ssh *:* LISTEN
tcp 0 0 localhost:ipp *:* LISTEN
tcp 0 0 localhost:953 *:* LISTEN
tcp 0 0 *:https *:* LISTEN
tcp 0 0 localhost:mysql *:* LISTEN
tcp 0 0 *:http *:* LISTEN
tcp 1 0 ns.example.com:37431 mulberry.canonical:http CLOSE_WAIT
tcp 0 52 ns.example.com:ssh 192.168.1.102:64747 ESTABLISHED
tcp6 0 0 [::]:domain [::]:* LISTEN
tcp6 0 0 [::]:ssh [::]:* LISTEN
tcp6 0 0 ip6-localhost:ipp [::]:* LISTEN
tcp6 0 0 ip6-localhost:953 [::]:* LISTEN
student@ubuntu:~$
Konfigurimi i mysql
MySQL i ruan skedarte e DB ne /var/lib/mysql/
MySQL instalon disa skedare konfigurimi ne /etc/mysql.
student@ubuntu:/etc/mysql$ ls -l
total 16
drwxr-xr-x 2 root root 4096 Apr 1 19:49 conf.d
-rw------- 1 root root 333 Apr 1 19:49 debian.cnf
-rwxr-xr-x 1 root root 1220 Feb 21 15:02 debian-start
-rw-r--r-- 1 root root 3505 Feb 21 15:02 my.cnf
student@ubuntu:/etc/mysql$
Skedari debian.cnf dhe skedari debian-start jane nje skedare konfigurimi dhe nje skript qe perdoren per konfigurimin dhe start e serverir mysql ne ambjentet linux debian,ubuntu.
Skedari my.cnf eshte skedari i konfigurimit te mysql serverit
Me poshte eshte nje skedar my.cnf shembull
student@ubuntu:/etc/mysql$ sudo cat my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
student@ubuntu:/etc/mysql$
port=3306
Percakton qe MySQL degjon ne porten 3306.
bind-address=127.0.0.1
Percakton adresen IP ne te cilen degjon MySQL. Default eshte 0.0.0.0. Nese duam qe te lejojme lidhjen nga rrjeti per mysql duhet te vendosim IP e serverit ose 0.0.0.0
Si rregull MySQL nuk lejon access nga nje host tjeter nepermjet rrjetit. Pa konfigurime te tjera shtese MySQL lejon lidhje vetem nga serveri local.
Per shembull skedari me poshte http://localhost:81/endri/mysql.php
<?php
mysql_connect('192.168.1.104','root','root')
or die('nuk lidhet');
echo 'lidhja me serverin MySQL u realizuar me sukses';
?>
nese hapet nga nje server tjeter i cili lidhet me rrjet me serverin MySQL (192.168.1.104 ne shembullin tone) do te afshonte nje mesazh gabimi.
Warning: mysql_connect() [function.mysql-connect]: Host '192.168.1.102' is not allowed to connect to this MySQL server in C:\xampp\htdocs\endri\mysql.php on line 2
nuk lidhet
Ndersa nese te njejtin skedar do ta hapnim nga website ne serverin http://www.ict.local/mysql.php
<?php
mysql_connect('localhost','root','root')
or die('nuk lidhet');
echo 'lidhja me serverin MySQL u realizuar me sukses';
?>
do te afishonte
lidhja me serverin MySQL u realizuar me sukses
Konfigurimi i MySQL qe te pranoje lidhje nga rrjeti.
Per te konfiguruar MySQL qe te pranoje lidhje nga rrjeti se pari duhet te percaktojme 0.0.0.0 si bind-address. Gjithashtu mundet thjesht ta bejme koment rreshtin
bind-address = 127.0.0.1
ne skedarin my.cnf, pasi si rregull nese nuk eshte percaktuar bind-address do te jete 0.0.0.0
Se dyti, per te lejuar lidhjen nga rrjeti duhet te percaktojme se per cilin perdorues dhe ne cilen database do te lejojme aksesin nga rrjeti.
Per kete do te krijojme nje perdorues dhe nje databaze shembull duke perdorur komandat tekstuale te mysql.
1- Te lidhemi me MySQL nga terminali
student@ubuntu:/$ mysql -u root -p
Enter password:
komanda mysql hap nje shellin e komandave te mysql.
Opsioni -u user (-u root) percaton username me te cilin duam te lidhemi ne MySQL. root eshte perdoruesi administrator qe u krijua gjate procesin te instalimit.
Opsioni -p , tregon qe perdoruesi duhet te vendose passowrd
student@ubuntu:/$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.29-0ubuntu0.12.04.2 (Ubuntu)
Copyright (c) 2000, 2012, 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>
Pas login-it na shfaqet prompt i mysql:
mysql>
Te krijojme fillimisht nje database per te cilen do te lejojme hyrjen nga rrjeti.
Shenim: Komandat mysql duhet ti perfundojme me pikepresje
mysql> create database ict;
Query OK, 1 row affected (0.00 sec)
Me krijimin e database do te krijojme nje perdorues te ri te cilit me pas do ti japim akses tek ky database. Perdoruesin do ti vendosim kredencialet username=ict dhe password=ict.
Username dhe passwrd duhet ti vendosim ne thonjeza teke
mysql> create user 'ict' identified by 'ict' ;
Query OK, 0 rows affected (0.00 sec)
mysql>
Se fundi, do te percaktojme qe perdoruesi ict do te kete akses ne server ne database ict nga nje IP e paracaktuar ne rrjet ose nga cdo IP (0.0.0.0)
mysql> grant all privileges on ict.* to ict@0.0.0.0 identified by 'ict';
Query OK, 0 rows affected (0.00 sec)
mysql>
Pas ketij veprimi faqa http://localhost:81/endri/mysql.php
<?php
mysql_connect('192.168.1.104','ict','ict')
or die('nuk lidhet');
echo '<p>lidhja me serverin MySQL u realizuar me sukses</p>';
?>
do te shfaqet
lidhja me serverin MySQL u realizuar me sukses
Database Engines
MySQL lejon ruajtjen e te dhenave ne disa menyra. Keto menyra referoheni si storage engines. Ka dy modele kryesore te ruajtjes se te dhenave: InnoDB dhe MyISAM. Ato jane are transparente per perdoruesin. Pavaresishte se cila engine perdoret perdoruesit perdorin te njejten menyre per te trajtuar te dhenat
MyISAM
MyISAM eshte engina me e vjeter nga te dyja. Mund te jete me e shpejte se InnoDB ne disa skendare te cilat kane kryesisht veprime leximi. MyISAM suportin tipin e te dhenave FULLTEXT , i cili lejon kerkime te shpejte ne volume te medha te dhenash. POR MyISAM kyc te gjithe tabelen kur do te shkruaje ne te. Kjo do te thote qe vetem nje proces mund ta aksesoje tabelen ne nje moment te ckatuar, vetem procesi qe e ka kycuar ate per modifikim.
InnoDB
InnoDB eshte nje database engine me e re, e koncepuar per te qene ACID compliant qe garanton transaksione korrekte ne database. Kycja per shkrim behet ne nivel rreshti jo ne nivel tabele. Kjo do te thote qe ne te njejten tabele mund te kryhen disa ndryshime ne te njejten kohe, mjafton qe ato te jene ne rreshta te ndryshem. Gjithashtu Data caching ruhet ne memorje brenda database engine, duke lejuar nje model me eficent te caching ne nivel rreshti . Per te garantuar ACID compliance te gjithe transaksionet ruhen ne journal .
Qe me MySQL 5.5 InnoDB eshte default engine, dhe rekomandohet kundrejt MyISAM .
Page updated
Report abuse