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

NB! Järgnev tekst on kirjutatud peamiselt silmas pidades MySQL versiooni 5.0, ehk uuemate versioonide puhul võib esineda teatavaid pisierinevusi.

Install

FreeBSD

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

Samuti on valaik esmaste baaside genereerimine

# mysql_install_db --user=mysql --basedir=/usr/local --datadir=/var/db/mysql

/etc/rc.conf lisame rea mysql_enable="YES" stardime serveri ning seadistame root parooli

/usr/local/etc/rc.d/mysql-server start
/usr/local/bin/mysqladmin -u root password "parool"

Gentoo

# emerge mysql-server

Ning, et teenus käivituks arvuti startimisel kirjutame

# rc-update add mysql default

Debian

Tarkvara paigaldamiseks tuleb öelda

 # apt-get install mysql-server

Mille järel küsitakse MySQL serveri juurkasutaja parool, seda Debianis hiljem täiendavalt seadistama ei pea

Andmebaasi käivitab skript

 # /etc/init.d/mysql start|stop|reload|...

ja andmebaasi tööd kontrollib seadistusfail /etc/mysql/my.cnf, kusjuures see juhib nii serverit kui klienti (kui serveris vahel kasutatakse klienti).

Andmefailid asuvad Debianis vaikimisi kataloogis /var/lib/mysql.

MySQL paigaldamine ja kasutamine ametlikus veebis asuvast paketist

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 operatsioonisüsteemile sobilik pakett alla tõ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

Lisaks kui mysql_install_db käsku sai käivitatud ruuduna tuleb baas anda mysql omadusse tagasi, nt

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

Ja luues my.cnf faili kus defineeritud teine port, andmebaasi asukohaks olev kaust (datatir) ning startides käsitsi

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

NB! Gentoos asub nt skript /usr/share/mysql/scripts/mysql_install_db ja seda tuleks viidata täisrajaga.

Root parool ja Ligipääsuõigused

Käivitame 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)

Või siis muuta lihtsalt andmebaasi ligipääsu hosti aadressi

mysql> update db set Host='192.168.1.8' where Db='salesdb';
mysql> update user set Host='192.1681.8' where user='foo';
mysql> \q

Kasutaja ja andmebaasi loomine

Root kasutaja alt andmebaasi kasutamist tuleb kindlasti vältida. Vajalik oleks luua igale kasutajale temale kuuluv andmebaas mida ta ainsana muuta saab.

Loome kasutaja mart kes saab logida sisse localhostist parooliga salakala

# mysql -u root mysql
mysql> CREATE USER 'mart'@'localhost' IDENTIFIED BY 'salakala';

Tekitame andmebaasi martdb

mysql> CREATE DATABASE martdb;

Anname andmebaasi mrtdb kasutamisõigused kasutajale mart parooliga 'salakala'

mysql> GRANT ALL ON mrtdb.* TO 'mart'@'localhost' IDENTIFIED BY 'salakala';

Soovides eemaldada kõik tehtud muudatused tuleb anda käsud

mysql> DROP DATABASE bobdb;                       
mysql> DROP USER bob;                            
mysql> DELETE FROM mysql.user WHERE user='bob and host='hostname'; # Alt. command
mysql> FLUSH PRIVILEGES;

Soovides lubada kasutajal mart logida kuskilt eemalt arvutist tuleb anda järgnevad õigused

# mysql -u root mysql
mysql> GRANT ALL ON martdb.* TO mart@'ip-aadress' IDENTIFIED BY 'salakala';

Soovides lubada ligipääsu kõijalt tuleb ip-aadressi asemel kirjutada '%', üldiselt seda turvakaalutlustel ei soovitaks.

Selle kustutamiseks omakorda

mysql> REVOKE GRANT OPTION ON foo.* FROM mart@'ip-aadress';

Kasutajate vaatamiseks

SELECT User,Host FROM mysql.user;

Kasutaja õiguste vaatamiseks

SHOW GRANTS FOR 'mart'@'localhost';

Kasutaja kustutamiseks

DROP USER 'mart’@‘localhost’;

Kindlasti tuleb peale seda ka teha flush privileges

mysql> FLUSH PRIVILEGES;

MySQLi seadistusfail my.cnf

Kõiki MySQL võimalusi saab seadistada my.cnf nimelisest failist. 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 järgnev

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

default_storage_engine=MyISAM
log-error                                       = /var/log/mysqld.err
datadir                                         = /kaust/kus/asuvad/andmebaasid/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

thread_concurrency muutuja võiks seadistada valemi alusel: CPU'de arv x 2. Max_used_connections on soovitatud dokumentides seadistada sama mis Max_used_connections value.

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

query cache saame seadistada reaga

query_cache_size= 32M

Graafik keskmisest sajakonna baasiga mysql serverist

Päringute puhvri kasutuse statistikat näeb käsuga

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 374      |
| Qcache_free_memory      | 14010240 |
| Qcache_hits             | 172444   |
| Qcache_inserts          | 1638     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 24146    |
| Qcache_queries_in_cache | 1638     |
| Qcache_total_blocks     | 3661     |
+-------------------------+----------+
8 rows in set (0.00 sec)

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 rida max_connectin suuremaks

max_connections                         = 500

MySQLi jõudluse parandamisel on olulisteks muutujateks veel key_buffer_size ja table_cache. Ideaalis võiks key_buffer_size olla suur piisavalt, et sisaldada kõiki MyISAM tabelite indekseid. Ehk sama suur kui kõik mysql serveris olevad .MYI failid.

Aeglased päringud oleks soovitatav logida eraldi faili, kergem leida päringuid mis koormavad serverit ja teevad rakenduse aeglaseks.

; enable the slow query log, default 10 seconds
log_slow_queries = db-slow.log
; log queries taking longer than 5 seconds
long_query_time = 5

MySQL'i kasutamine

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

• kuvada andmevälju
• kirjeid filtreerida, näiteks neid, mille välja vanus väärtus on suurem kui 45
• kirjeid sorteerida
• teha andmeväljadega tehteid, näiteks korrutada kahte arvulist andmevälja
• grupeerida andmeid ja leida gruppe iseloomustavaid statistikuid, näiteks arvutada Haapsalus elavate inimeste keskmine vanus
• kustutada kirjeid ja andmetabeleid
• lisada kirjeid ja andmetabeleid

salvestada andmeid teatud tingimustel olemasolevasse teisse andmetabelisse

Andmeväljade nimed eraldatakse komaga ning esitatakse samas järjekorras vasakult paremale.

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>

Loome andmebaas mida asume kasutama

mysql > create database zoo;

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.

Kõigi kirjete nime ja elukoha välja kuvamiseks tuleb anda korraldus SELECT nimi,vanus FROM inimesed;

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'.

Andmete muutmine

Andmetabeli andmeid muudetatakse korraldusega UPDATE. Näiteks kirjutame kõigi nende kirjete andmevälja vanus väärtuseks 46, mille anmevälja nimi väärtus on 'Pille'

UPDATE inimesed SET vanus=46 where nimi='Pille';

Soovides muuta kõigi kirjete andmevälja vanus, andke korraldus

UPDATE inimesed SET vanus=46;

Andmete kustutamine

Teatud tingimustele vastava kirje kustutamiseks kasutatakse käsku DELETE. Näiteks kustutame kõik Haapsalu kohta käivad andmed:

DELETE FROM inimesed WHERE elukoht = 'Haapsalu';

Kogu andmetabeli sisu kustutamiseks tuleks anda korraldus

DELETE FROM inimesed;

Kusjuures andmetabel ise jääb oma struktuuriga alles. Andmetabeli kustutamiseks andke korraldus

DROP TABLE inimesed;

Kui te soovite muuta tulba nime, kasutage klauslit AS

SELECT nimi AS kodanik, elukoht AS "kodu linn" FROM inimesed;

kui uues päises sisaldub tühik, tuleb kasutada jutumärke

Andmetabeli struktuuri muutmine

Andmetabelit saab muuta käsuga ALTER. Näiteks lisame olemasolevasse andmetabelisse andmevälja sugu

ALTER TABLE inimesed ADD sugu VARCHAR(30) AFTER nimi;

Muudame olemasoleva tabeli nime inimesed uueks nimega kodanikud

ALTER TABLE inimesed RENAME kodanikud;

Muudame olemasoleva tulba nime elukohast aadressiks

ALTER TABLE inimesed change elukoht aadress varchar(18);

Uue nime järel tuleb näidata ka andmevälja tüüp, olgugi, et see ei muutu.

Eemaldame tulba nimi

ALTER TABLE inimesed DROP COLUMN nimi;

Kirjete filteerimine

Soovides andmetabelist filtreerida välja teatud tingimustele vastavad kirjed kasutatakse klauslit WHERE, mille taga näidatakse tingimus. Tingimused võivad olla sellised: <, >, =, <>, =>, <= ning neid võib kombineerida loogiliste tehetega: AND, OR, NOT. Näiteks filtreerime kirjed, mille välja vanus väärtus on suurem kui 18 ja väiksem kui 30

SELECT * FROM inimesed WHERE vanus > 18 AND vanus < 30;

Kirjete sorteerimine

Soovides andmetabeli kirjeid sorteerida vastavalt teatud tingimustele kasutatakse klauslit ORDER BY, mille taga näidatakse tingimus. Tingimus näitab tavaliselt millise andmevälja järgi ja millises suunas so kasvavalt või kahanevalt järjestada. Näiteks sorteerime vanuse järgi ja siis nimejärgi

SELECT * FROM inimesed ORDER BY vanus, nimi DESC;   

Kui sama vanusega on mitu inimest (18), siis järjestatakse nad omavahel nime järgi(Malle, Malle, Leida), kuid tagurpidi (DESC). Tehted andmeväljadega

Lisaks kuvamisele saab teha andmeväljadega tehteid, näiteks leida kui vana on inimene aastal 2020

SELECT vanus+20 AS "vanus aastal 2000", vanus, nimi FROM inimesed;

Tehete puhul on oluline kasutada ASi.

Võimalik on sorteerida ka kirjeid mingi teatava märgi alusel, näiteks välistame kõik read kus @ märk

SELECT * FROM `tabel` WHERE dest NOT LIKE '%@%'

Mitme tingimuse alusel sorteerimiseks tuleb panna tingimuste vahele AND näiteks

SELECT * FROM `tabel` WHERE väli tingimus AND väli tingimus

Grupeerimine

Leiame erinevatest linnades pärit inimeste vanuste keskmise ning lisaks järjestame tulemused selle järgi

SELECT avg(vanus) AS keskmine_vanus, elukoht FROM inimesed GROUP BY elukoht ORDER BY keskmine_vanus;       

Siin on kasutatud funktisiooni avg (ingl. k. average). Lisaks saab kasutada funktsioone: min - vähima leidmine max - suurima leidmine sum - summa arvutamine

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 andmebaasi serverisse 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 saadetakse 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

Kontrollimehanisme

MySQLi stored procedure manual asub siin:

• http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-compound-statements.html

See laseb kõiksugu kontrollimehanisme (WHILE, LOOP, REPEAT jne) ning ka CURSOReid kasutada.

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 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 mitme miljoni reaga tabel vajab kindlasti juba indeksit. Kujutage ette näiteks raamatut millel 500 000 lehte ja pole sisukorda.

View'd

Tegemist on nö virtuaalsete tabelitega mis koostatud mingitest teistest tabelitest saadud info alusel

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Näiteks

CREATE VIEW email_sorteeritud AS
SELECT *
FROM `email`
WHERE 'uid'='10'

Kustutamiseks

DROP VIEW email_sorteeritud

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:

• -d --no-data, 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

Kui on tahtmine teha kõigist andmebaasidest varukoopia

$ mysqldump --default-character-set=utf8 --all-databases >> suur.sql

Peale mysql tarkvara uuendamist tasub startida mysql-upgrade

# mysql_upgrade -u root -p

Tasub tähele panna, et mysqldumbil skip-dabase võtit pole, küll saab aga kasutada võtit --ignore-table näiteks

--ignore-table=andmebaas.segane_tabel

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.

Üks alternatiivne võimalus on lisada ka my.cnf faili rida

expire_logs_days = 31

Millejärel kõik logifailid mis vanemad kui 31 päeva kustutatakse automaatselt iga kord kui logid on flushitud.

mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)

Vaikimisi on väärtus 0, mis tähendab “no automatic removal.”

MySQL kasutamine PHP abil

Järgnev näiteskript võtab etteantud kasutaja ning parooli abil serveriga ühendust ning üritab ühenduda ühe andmebaasiga. PHP peab olema kompileeritud MySQL toega.

<?php
mysql_connect("server", "kasutaja", "parool") or die(mysql_error());
echo "Connected to MySQL< br /> ";
mysql_select_db("andmebaas") or die(mysql_error());
echo "Connected to Database";
?>

MySQL versioon 5.1

Täielik 5.1 versioonis leiduv uuenduste nimekiri.

• http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html

Faili asemel andmebaasi logimine.

Uskuge või mitte aga kohati on märksa mugavam infot analüüsida SQLis kui failis. Seega on mõistlik kohati logida kõik päringud (või slow queries) näiteks mingi rakenduse raendamise ajal andmebaasi.

Selleks, et lubada andmebaasi logimine tuleb lisada my.cnf faili järgnev rida

log_output = TABLE

Mille järel kirjutatakse logid tabelitesse nimedega 'slow_log' ja 'general_log' mysql andmebaasis.

Halvaks küljeks asjal on see, et andmebaasi logimine nõuab rohkem arvutus resurssi, seega on mõistlik nimetatud featuret kasutada peamiselt debugimise/arendamise juures.

Katkimineku korral

myisam tabeli riknemise puhul aitab seda taas remontida käsk

# myisamchk -r andmebaas/*.MYI

Olemas on veel mysqlcheck käsk, nt ühele adnembaasile

# mysqlcheck -h db.eenet.ee -u root -p mart
mart.test_class_metadata                          OK
mart.test_file_metadata                           OK 

Võib ka listada automaatse paranduse

# mysqlcheck -A --auto-repair -u kasutaja -p

Või siis rohkemate võtmetega kõigile andmebaasidele.

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Ja mysql käsurealt nt

# mysql> REPAIR TABLE tabelinimi;

Tabelite korrasolekut saab kontrollida käsuga

mysql> show table status;

+-------+--------+---------+-----+---------------------+
| Name  | Engine | Version | ... | Create_time         |
+-------+--------+---------+-----+---------------------+
| good  | MyISAM |      10 | ... | 2007-06-08 09:44:01 |
| users | MyISAM |       7 | ... | 2005-03-15 12:03:11 |
+-------+--------+---------+-----+---------------------+

Innodb parandamine on keerukam kuna selle remontimise funktsioon puudub

mysql> REPAIR TABLE Attachments;
+-----------------+--------+----------+---------------------------------------------------------+
| Table           | Op     | Msg_type | Msg_text                                                |
+-----------------+--------+----------+---------------------------------------------------------+
| rt4.Attachments | repair | note     | The storage engine for the table doesn't support repair |
+-----------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec) 

Kuna innodb katkestab vigase baasi või tabeli dumpimisel sql ühenduse on raske ka andmeid osaliselt kätte saada. Selle käitumise muutmiseks võib nt seadistada

[mysqld]
innodb_force_recovery = 1

Rohkem lugemist https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

Võimalus on ka innodb katkine tabel konvertida myisamiks ja siis remontida

ALTER TABLE log ENGINE = InnoDB

Mysqli server read-only

Vahel võib juhtuda, et tarvis on seadistada nt dumpimise või replikeerimise alustamiseks mysql server olekusse, kus ta võtab vastu ainult select päringuid aga mitte insert-update käske. Selleks

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1; 

Testimiseks select

mysql> select * from kuup2evad;
+------------+---------------------+---------------------+------------+
| d          | dt                  | ts                  | value      |
+------------+---------------------+---------------------+------------+
| 2008-05-20 | 2008-05-20 17:04:21 | 2008-05-20 17:04:21 |            |
| 0000-00-00 | 0000-00-00 00:00:00 | 2008-05-21 16:24:51 |            |
| 0000-00-00 | 2012-09-17 11:58:51 | 2012-09-17 11:58:51 | NULL       |
+------------+---------------------+---------------------+------------+
6 rows in set (0.00 sec) 

Ja create

mysql> create table inimesed (nimi varchar(15), vanus int, elukoht varchar(18));
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock 

Selleks, et seadistada ta tagasi kirjutatavaks

SET GLOBAL read_only = 0;
UNLOCK TABLES;

Kaotatud administraatori parooli taastamine

Peatame mysql serveri kasutades näiteks ametlikke init skripte. Käivitame selle uuesti käsitsi jättes laadimata õiguste ehk grant tabelid

# mysqld_safe --skip-grant-tables

Logime mysqli sisse kasutajaga root ja valime mysql andmebaasi

# mysql --user=root mysql

Seadistame root kasutajale uue parooli

mysql> update user set Password=PASSWORD('parool') where user='root';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Flushime privileegid

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Ja väljume

mysql> exit;

Nüüd võib mysql protsessi uuesti peatada ning restartida mysql serveri. Andmebaasi on ligipääsetav uue parooliga.

Erinevad abivahendid

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

# mytop --dbuser=root --password=salas2na

MySQLi konffimiseks jagab soovitusi mingi selline skript http://mysqltuner.pl/mysqltuner.pl

näiteks väljund ühel andmebaasiserveril selline

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 128M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 8G)

Optimiseerimisest veel lugemist http://www.databasejournal.com/features/mysql/article.php/3367871/Optimizing-the-mysqld-variables.htm

Klientide tekitatud koormuse emuleerimine

• http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

Mysqli ühendused krüpteeritult üle SSLi

Kliendile tuleb öelda ca

[client]
ssl-ca=/etc/cert/cachain.pem

Serveripoolel aga tekitada ja seadistada sertifikaadid

[mysqld]
ssl-ca=/etc/cert/cachain.pem
ssl-cert=/etc/cert/wildcard.example.com.crt
ssl-key=/etc/cert/wildcard.example.com.key

Selleks, et mingi kindel kasutaja ühendused käiksid krüpteeritult tuleb anda sql käsk

GRANT USAGE ON *.* TO 'user'@'hostname' REQUIRE SSL;

Varundamine tab formaadis

Mysqli on võimalik varundada ja taastada selliselt, et iga tabeli kohta tekitatakse eraldi kaks faili. Struktuuri ja andmete jaoks. See annab võimaluse taastada andmeid kiiremini ja näiteks tabelite kaupa.

Varundamine

# mysqldump -u root -p test --tab kaust

ja taastamiseks siis esmalt laadida sql failid ja seejärel anda andmefailide kaustas käsk

# mysqlimport --use-threads 10 -u root -p[password] [database] `pwd`/*

Protsessi, näeb mysql käsurealt

MariaDB [(none)]> show processlist;
+------+------+-----------+-------------+---------+------+--------------+--------------------------------------------------------------------------------------------  ----------+----------+
| Id   | User | Host      | db          | Command | Time | State        | Info                                                                                                  | Progress |
+------+------+-----------+-------------+---------+------+--------------+-------------------------------------------------------------------------------------------- ----------+----------+
| 2399 | root | localhost | moodle_test | Query   |  590 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/assign_submission.txt' INTO TABLE  `assign_submission` IGNO |   20.355 |
| 2404 | root | localhost | moodle_test | Query   |  590 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/assign_user_mapping.txt' INTO TABLE  `assign_user_mapping`  |   34.020 |
| 2406 | root | localhost | moodle_test | Query   |  590 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/assign_grades.txt' INTO TABLE `assign_grades`  IGNORE 0 LIN |   36.953 |
| 2408 | root | localhost | moodle_test | Query   |  590 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/assignfeedback_comments.txt' INTO TABLE  `assignfeedback_co |   44.738 |
| 2419 | root | localhost | moodle_test | Query   |  572 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/assignsubmission_file.txt' INTO TABLE  `assignsubmission_fi |   48.143 |
| 2423 | root | localhost | moodle_test | Query   |  566 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/assignsubmission_onlinetext.txt' INTO TABLE  `assignsubmiss |   36.349 |
| 2480 | root | localhost | moodle_test | Query   |  379 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/block_instances.txt' INTO TABLE `block_instances`  IGNORE 0 |   42.727 |
| 2509 | root | localhost | moodle_test | Query   |  285 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/block_recent_activity.txt' INTO TABLE  `block_recent_activi |   28.151 |
| 2567 | root | localhost | moodle_test | Query   |   27 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/book_chapters.txt' INTO TABLE `book_chapters`  IGNORE 0 LIN |    0.218 |
| 2573 | root | localhost | moodle_test | Query   |   14 | reading file | LOAD DATA   INFILE '/mnt/backup/moodletab/chat.txt' INTO TABLE `chat` IGNORE 0 LINES                  |   38.989 |
| 2577 | root | localhost | NULL        | Query   |    0 | init         | show processlist                                                                                      |    0.000 |
+------+------+-----------+-------------+---------+------+--------------+-------------------------------------------------------------------------------------------- ----------+----------+
11 rows in set (0.00 sec)

Nagu näha taastatakse tõepoolest kümne paraleelse lõimega

phpmyadmin

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

Lihtne seadistus failis config.inc.php oleks sellele järgnev

/* Global configuration */
$cfg['blowfish_secret'               ] = 'use_your_own_random_string';

/* Servers configuration */
$i = 0;

/* First serveur */
$i++;
$cfg['Servers'][$i]['host'           ] = '127.0.0.1';
$cfg['Servers'][$i]['extension'      ] = 'mysqli';
$cfg['Servers'][$i]['connect_type'   ] = 'tcp';
$cfg['Servers'][$i]['compress'       ] = false;
$cfg['Servers'][$i]['auth_type'      ] = 'cookie';

Lingid

http://et.wikipedia.org/wiki/MySQL

http://www.securityfocus.com/infocus/1726 Link ühele seadistusjuhendile

http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ Muutujate seadistamisest

https://wiki.itcollege.ee/index.php/MySQL_andmebaasi_j%C3%B5udluse_parandamine

http://www.ibm.com/developerworks/library/l-tune-lamp-3.html jõudluse tõstmine

https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/ Mysqli ja Mariadb põhjalik võrdlus