Mysql server

Sissejuhatus

MySQL on vabatarkvaraline SQL baasil loodud andmebaasi haldamise tarkvara, mida kasutavad ka näiteks Google, Yahoo!, HP, MP3.com, Motorola, Silicon Graphics ja NASA ning ka arvatavasti umbkaudu enam-vähem 70% maailma veebilehtedest

Mysql populaarsus veebiaplikatsioonina on lähedalt seotud PHP programmeerimiskeele populaarsusega. Lausa standardiks on muutunud LAMP lahendus - Linux, Apache, Mysql, Php

Install

FreeBSD

cd /usr/ports/databases/mysql50-server
make install clean

/etc/rc.conf lisame rea mysql_enable="YES"

Gentoo

emerge mysql-server

Ning kirjutame

rc-update add mysql default

Debian

...

Mysql teeb asja lihtsamaks olles enamusele tuntud operatsioonisüsteemile ise valmistanud kompileeritud binaarsed failikogumid. Neid saab muretseda aadressilt näiteks http://dev.mysql.com/downloads/mysql/5.0.html#downloads Või kui vaja vanemaid http://dev.mysql.com/downloads/mysql/4.1.html

Piisab kui enda opertsioonisüsteemile sobilik pakett allatõmmata, valida saab enamasti ka 32v64 bitiste versioonide vahel ning sobilikku kausta lahtipakkida.

Käivitada saab seejärel käsuga

/kuskil/mysqli_binaarfailide_kataloogis/bin/mysqld --defaults-extra-file=/kuskil/confi_kataloogis/my.cnf --basedir=/kuskil mysqli_binaarfailide_kataloogis --datadir=/home/andmebaasid

Sarnaselt saad startida ka mitmeid mysql protsesse näiteks tekitades eraldi mysql default andmebaasid

mysql_install_db --datadir=/home/mysql5

Ja luues my.cnf faili kus defineeritud teine port ja datatir ning startides käsitsi

/usr/local/bin/mysqld_safe  --defaults-extra-file=/home/mysql5/my.cnf --datadir=/home/mysql5 &

Default baaside genereerimine

installime default baasi ja seadistame õigused kuna vaikimisi tekib see root omandusse

mysql_install_db

Lisaks kui seda käsku sai käivitatud ruuduna tuleb baas anda mysql omadusse tagasi

chown -R mysql:mysql /var/db/mysql

Root parool ja Ligipääsuõigused

strdime mysql serveri

/usr/local/etc/rc.d/mysql-server start 

või linux

/etc/init.d/mysqld start

Ning seejärel seadistame root parooli

/usr/local/bin/mysqladmin -u root password 'parool234'

Kontrollime kas mysql lubab ligi ja paroolita sisenemine keelatud

# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.15-beta FreeBSD port: mysql-server-5.1.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Kasutajaõigustest rääkides tasub ka tähele panna, et uuematel MySQLi versioonidel on kombeks paigalduse käigus tekitada 'anonüümne kasutaja', mis tähendab seda et lokaalsest masinast saab MySQLiga ühendust võtta, andes ette suvalise kasutajanime ja tühja parooli. Anonüümsel kasutajal on kõik õigused andmebaasile 'test', mis MySQLi paigalduse käigus samuti vaikimisi luuakse.

Anonüümse kasutaja olemasolust saame aimu järgmise päringu abil:

 mysql> SELECT host,user,password FROM mysql.user WHERE user='';
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost |      |          |
| myserver  |      |          |
+-----------+------+----------+ 

Nagu näeme, tagastas päring kaks rida - anonüümselt on ligipääs lubatud aadressidelt localhost ja myserver. See ei pruugi olla hea mõte, kuna annab näiteks võimaluse igale lokaalse masina kasutajale häirida MySQLi ja potentsiaalselt ka teiste rakenduste tööd, laadides andmebaasi 'test' nii palju andmeid et MySQLi andmete partitsioon saab täis. Kui soovime anonüümse kasutaja eemaldada, siis võime seda teha, andes käsud

mysql> DELETE from mysql.user WHERE user='';
mysql> FLUSH PRIVILEGES;

Kui näiteks oleks vaja võimaldada mysql'i logimist mõnest teisest serverist tuleks käivitada käsud

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'host.edu.ee' IDENTIFIED BY 'parool' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

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

my.cnf

Kõiki mysql peamisi võimalusi saab seadistada my.cnf nimelise seadistusfailiga. Seadistusfail tuleb paigutada ebatüüpiliselt sageli mitte /etc harusse vaid vaikimisi otsib mysql seda hoopis enda andmefailide juurest. St enamast /var alt näit FreeBSD's /var/db/mysql

Üks tüüpiline optimeeritud mysql konfiguratsioonifail näeb välja selline

[mysqld]
server-id = 1
user                                            = mysql
port                                            = 3306

default_storage_engine=MyISAM
log-error                                       = /var/log/mysqld.err
datadir                                         = /koht/andmebaasidel/mysql

skip-locking
max_connections                         = 500
key_buffer                              = 64M
max_allowed_packet                      = 10M
table_cache                             = 256
sort_buffer_size                        = 1M
net_buffer_length                       = 8K
read_buffer_size                        = 128K
read_rnd_buffer_size            = 512K
myisam_sort_buffer_size         = 64M
tmp_table_size                  = 32M
query_cache_size = 32M
thread_cache_size = 8
thread_concurrency = 4

log-bin                         = db-bin
max_binlog_size = 100M

query_cache annab näiteks võimaluse hoida viimati tehtud päringuid mälus ning kiirendada seega nende täitmist.

query cache saame seadistada reaga

query_cache_size= 32M

Graafik keskmisest sajakonna baasiga mysql serverist

Vajadusel saab mysql'ist engineid välja lülitada. Näiteks ,et keelata innodb tuleb lisada seadistusfaili rida

skip-innodb

Rohkemate paraleelsete ühenduse lubamiseks tuleb aga muuta näiteks rea

max_connections                         = 500

Olevat väärtust

MySQL'i kasutamine

Kui server käib, siis saab seda kasutada käsureal programmi 'mysql' abil. Te saate ette uue prompti ja saate teha nt. sellise asja

antud juhul on eesmärk - luua andmebaas minu_sobrad - sisestada sinna andmed - teha päring mis peaks ekraanile tooma kõigi inimeste nimed kes elavad Tartus

Seanss näeb välja nii:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 3.22.15-gamma

Type 'help' for help.

Andmetabeli loomine käsurealt

Enne järgneva tegevuse juurde asumist logige enda MySQLi serverisse

bash$ mysql -h www.zoo.edu.ee -u zoo -pzooparool
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57764 to server version: 3.22.32-log

Type 'help' for help. 

mysql>

Andmetabeli loomisel peab näitama millisesse andmebaasi see tekitatakse. Selleks tuleb võtta kasutusele käsuga 'use' andmebaas, milleks on antud juhul zoo

mysql> use zoo
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>

Kirjeldame kolme väljaga andmetabeli inimesed:

• nimi - tekstiline andmeväli, kuni 15 tähemärki
• vanus - täisarvuline andmeväli
• elukoht - tekstiline andmeväli, kuni 18 tähemärki

Selleks tuleb anda korraldus

mysql> create table inimesed (nimi varchar(15), vanus int, elukoht varchar(18));

MySQLi käsk lõppeb semikooloniga (;). Seejärel sisetame andmetabelisse kolm kirjet

nimi      vanus     elukoht
Mart      16        Narva
Priit     17        Tallinn
Laa       12        Kuressaare

Selleks tuleb anda korraldused:

mysql> INSERT INTO inimesed VALUES ('Mart', '16', 'Narva');
mysql> INSERT INTO inimesed VALUES ('Priit', '17', 'Tallinn');
mysql> INSERT INTO inimesed VALUES ('Laa', '12', 'Kuressaare');

MySQLi kliendi käsureal saab kursorit liigutada näiteks vasakule ja paremale nooleklahvide abil ning olemasolevat teksti redigeerida. Sarnaselt saab varemantud käskude vahel valida neid sirvides üles ja alla nooleklahvide abil.

Otse käsurealt on tõenäoliselt tülikas sisestada suurt hulka andmeid, sellepärast selgitame allpool kuidas seda teha 'batch' rezhiimis.

Olles lõpetanud andmete sisestamise sulgeda MySQLi klient käsuga 'quit'.

Päringu sooritamine käsureal

Päringu sooritamine käsurealt eeldab, et teil on olemas andmed.

Esmalt logige enda MySQLi andmebaasi sisse ja võtke kasutusele oma andmebaas.

bash$ mysql -h www.zoo.edu.ee -u zoo -pzooparool
mysql> use zoo

Esitame näiteks päringu, mis näitab andmetabeli inimesed kõikide kirjete kõiki välju

mysql> select * from inimesed;
+-------+-------+------------+
| nimi  | vanus | elukoht    |
+-------+-------+------------+
| Mart  |    16 | Narva      |
| Priit |    17 | Tallinn    |
| Laa   |    12 | Kuressaare |
+-------+-------+------------+
3 rows in set (0.00 sec)

Vahel on abiks näha andmetabeli struktuuri, selleks andke korraldus

mysql> show columns from inimesed;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| nimi    | varchar(15) | YES  |     | NULL    |       |
| vanus   | int(11)     | YES  |     | NULL    |       |
| elukoht | varchar(18) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Olles lõpetanud päringud väljuge MySQLi kliendist käsuga 'quit'. Andmetabeli kustutamine käsureal

Olemasoleva andmetabeli kustutamiseks andmebaasist logige end anmbebaasi sisse ja andke korraldus

mysql> drop table inimesed;
Query OK, 0 rows affected (0.01 sec)

Arvestage, et nii kustutatakse kõik andmed, mis sisalduvad kõnealuses andmetabelis ja te olete neist jäädavalt ilma.

Batch rezhiimi kasutamine

Mahukamate sisestuste puhul ei ole otstarbekas anda MySQLile käske käsitsi, vaid tuleks kasutada MySQLi klienti batch rezhiimis. Sel puhul täidetakse käske samuti ükshaaval, kuid mysql ei oota neid klaviatuurilt, vaid loeb tekstifailist. Kui antud käsud genereerivad väljundi, siis saadekse see programmi väljundisse, mida saab UNIXi kooriku vahenditega ümber suunata.

Näiteks lisame batch rezhiimis andmebaasi inimesed kirjed

Leida   18   Elva
Maie    45   Põlva
Väino   39   Haapsalu

Selleks tekitage järgmise sisuga tekstifail andmelisa.mysql-batch

use zoo
INSERT INTO inimesed VALUES ('Leida', '18', 'Elva');
INSERT INTO inimesed VALUES ('Maie', '45', 'Põlva');
INSERT INTO inimesed VALUES ('Väino', '39', 'Haapsalu');
quit

Ning andmete lisamiseks andke operatsioonisüsteemi viibast käsk

bash$ mysql -h www.zoo.edu.ee -u zoo -pzooparool < andmelisa.mysql-batch

Tulemusena lisati andmetabelisse zoo andmed Leida, Maie ja Väino kohta.

Batch rezhiimis saab sooritada ka päringut. Näiteks salvestame kõigi kirjete kõigi väljade sisu faili koik.inimesed

Selleks koostame esmalt sobiva batch-faili koik.mysql-batch

use zoo
select * from inimesed;
quit

Ning käivitame selliselt

bash$ mysql -h www.zoo.edu.ee -u zoo -pzooparool < koik.mysql-batch > koik.inimesed

Tulemusena suunatakse batch-failis sisalduvad käsud MySQLi kliendi sisendisse ning see, mis selle peale vastatakse suunatakse faili koik.inimesed. Failis koik.inimesed on kolm tulpa üksteisest tabulaatoritega eraldatud

nimi    vanus   elukoht
Mart    16      Narva
Priit   17      Tallinn
Laa     12      Kuressaare
Leida   18      Elva
Maie    45      Pulva
Vdino   39      Haapsalu

Andmebaasi varukoopia

Iga kasutaja enda huvides on, et tal oleks oma andmebaasist varukoopia. MySQLi puhul saab programmiga mysqldump hõlpsasti salvestada andmebaasi andmetabeli struktuuri ja/või sisu taastamiseks vajalikud käsud tekstifaili.

Andmebaasi zoo andmetabeli inimesed struktuuri väljastab korraldus

bash$ mysqldump -d -h www.zoo.edu.ee -u zoo -pzooparool zoo inimesed 


# MySQL dump 7.1
#
# Host: www.zoo.edu.ee    Database: zoo
#--------------------------------------------------------
# Server version        3.22.32-log

#
# Table structure for table 'inimesed'
#
CREATE TABLE inimesed (
  nimi varchar(15),
  vanus int(11),
  elukoht varchar(18)
);

kus on kasutatud selliseid võtmeid ja argumente:

• -t väljastatakse struktuur
• -h MySQL serveri nimi
• -u MySQL serveri kasutajanimi
• -p MySQL serveri kasutaja parool

kaks viimast argumenti on andmebaasi nimi (zoo) ja andmetabeli nimi (inimesed)

Kasutades UNIXi ümbersuunamisi saab selle väljundi suunata ka faili

bash$ mysqldump -d -h www.zoo.edu.ee -u zoo -pzooparool zoo inimesed > zoo.str.inimesed.sql

Andmebaasi zoo andmetabeli inimesed sisu väljastab eelmisele sarnane korraldus kasutades võtme -d asemel võtit -t

bash$ mysqldump -t -h www.zoo.edu.ee -u zoo -pzooparool zoo inimesed
# MySQL dump 7.1
#
# Host: www.zoo.edu.ee    Database: zoo
#--------------------------------------------------------
# Server version        3.22.32-log

#
# Dumping data for table 'inimesed'
# 

INSERT INTO inimesed VALUES ('Mart',16,'Narva');
INSERT INTO inimesed VALUES ('Priit',17,'Tallinn');
INSERT INTO inimesed VALUES ('Laa',12,'Kuressaare');
INSERT INTO inimesed VALUES ('Leida', '18', 'Elva');
INSERT INTO inimesed VALUES ('Maie', '45', 'Põlva');
INSERT INTO inimesed VALUES ('Väino', '39', 'Haapsalu');

Kirjeldatud moel saadud väljundit saab näiteks kasutada batch rezhiimis andmebaasi taastamiseks. Samuti on võimalik UNIXi umbersuunamisi kasutades andmeid üle võrgu kopeerida suunates väljundi ümber mõne teise MySQLi serveri andmebaasi sisendisse.

Soovides salvestada andmebaasi kõigi andmetabelite sisu ja struktuuri andke korraldus

bash$ mysqldump --skip-lock-tables -h www.zoo.edu.ee -u user -p parool basename > basename-backup.sql

Nendest andmetest toimub andmebaasi taastamine selliselt

bash$ mysql -h www.zoo.edu.ee -u zoo -pzooparool zoo < zoo.opt.sql

Peale mysql tarkvara uuendamist tasub startida mysql-upgrade

http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html

Binlog

binaarne logi on põhiliselt vaja toimingute replikeerimiseks slave serverites ja tehtud vigade tagasivõtmiseks. Kui sellist asja hetkel ei käi ja on piisav varukoopia, siis võib suht julgelt vanemaid kustutada sest need võtavad aegamööda kettale kogunedes üsnagi ohtralt ruumi

mysql> PURGE MASTER LOGS BEFORE '2008-08-20';

või

mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 62 DAY); 

Viimase käsu puhul saab antud toimingut kergemini automatiseerida ja igaöiseks muuta

See käsk on eelistatum lihtsalt failide kustutamisele, kuna see uuendab ka logi index-faili.

Indeksid

Indeksid on mõeldud andmebaasi otsingute kiirendamiseks Neid tasub lisada just väljadele mille järgi midagi otsitakse, või on arvata ,et otsitakse. Päris kogu infot ei ole mõtet indekseerida kuna võib tekitada hoopis lisakoormust.

näiteks miks soovitatakse fulltext otsingut tavalise like asemele sest fulltekst kasutab ka indekseid kuidagi ja tänu sellele kiirem, indeks on nagu sisukord raamatus et sa ei pea lappama tervet raamatut läbi mingi peatüki leidmiseks

Näide indekseeritud väljaga tabeli loomisest

CREATE TABLE inimesed (
	nimi VARCHAR(50), 
	kasutaja_id INT, INDEX (id_index)
)

Olemasolevale tabelile saab indeksi lisada

CREATE INDEX id_index ON inimesed(kasutaja_id)

Suurte andmeahtude juures on indeksid üldiselt alati olulised näiteks mitlemiljoni realine tabel vajab kindlasti juba indeksit. Kujutage ette näiteks raamatut millel 500 000 lehte ja pole sisukorda.

Erinevad abivahendid

Mytop linux/unix program top kloon mysqlile. http://jeremy.zawodny.com/mysql/mytop/

mtop --dbuser=root --password=salas2na

Lõppsõna

Edasi võib juba paigaldada apache ja php ning phpMyAdmin'i edasiseks tööks http://www.phpmyadmin.net/home_page/index.php