Mini Guida a MySQL

www.lonerunners.net
Mini Guida su MySql
Versione 0.01
Rilasciata il: 20/11/2004
Copyright
Questo documento viene rilasciato sotto licenza Alcoolware, la
quale non è altro che una normale licenza Creative Commons
Attribute­NonCommercial­ShareALike
[http://creativecommons.org/licenses/by­nc­sa/2.0/] ma con
l'aggiunta che se mi incontrate dobbiamo andare a bere qualcosa.
In sintesi è liberamente distribuibile per usi non commerciali,
copiabile e modificabile purchè citiate l'autore e la fonte.
Se volete distribuire questo documento sul vostro sito siete
pregati per favore di comunicarmelo in modo che possa spedirvi
le nuove versioni.
Disclaimer – tenere lontano dalla portata dei bambini
Questo articolo è frutto della libera espressione di una mente
malata, se l'argomento non vi interessa, vi disturba o offende
il vostro credo siete pregati di interrompere la lettura.
Il materiale è fornito a scopo esclusivamente educativo e
informativo, non mi ritengo in alcun caso responsabile per danni
a cose o persone derivati da un uso improprio di questo
documento. Questo documento nella sua versione attuale non è
commestibile e non è fumabile.
Tutti gli errori, le omissioni e le cavolate che ho scritto non
sono dovute alla mia volontà ma alla nota marca del mio alcolico
preferito, quindi non prendetevela con me ma con chi lo produce,
magari se trovate qualcosa avvertitemi. jekil ­ Alessandro Tanasi
[email protected]
Pagina: 1
INDICE
Capitolo 1: Introduzione
1.1Introduzione
1.2Storia
1.3Prestazioni
Capitolo 2: Installazione
2.1Le versioni
2.2Installare da binari
2.3Installare da sorgenti
2.4File di configurazione
Capitolo 3: Utilizzo
3.1Utilizzare il client
3.2Utilizzare un database
3.3Dati esterni
Capitolo 4: Funzionamento
4.1I dati
4.2Gli utenti
4.3I registri
4.4Le variabili
4.5Tipi di tabelle
Capitolo 5 : Amministrazione
5.1Avvio e arresto
5.2Backup, ripristino e manutenzione
5.3Sicurezza
Capitolo 6 : Strumenti grafici
6.1PhpMyAdmin
Bibliografia
Pagina: 2
CAPITOLO 1 : INTRODUZIONE
Ho scritto questa piccola guida con l'obbiettivo di fornire delle conoscenze basiche,
senza addentrarmi nei dettagli, facendo soltanto una panoramica sulle varie
funzionalità.
In particolare non parlo del linguaggio SQL (Structured Query Language), visto che
probabilmente lo conoscete già, in ogni caso è molto simile all'inglese parlato quindi
risulta di facile comprensione.
Sono sicuro che chi ha bisogno di ulteriori informazioni si affiderà ad un buon libro o
al suo migliore amico [http://www.google.it].
1.1Introduzione
Dall'esigenza di gestire velocemente database anche di grandi dimensioni è nato
MySQL, un server DBMS (Database Management System) multi utente e multi
processo che rispetta lo standard ANSI SQL-92.
L'affermazione che MySQL sia un vero DBMS è contestabile, ma per il momento
facciamo finta di niente.
MySQL si pronuncia “mai es-qu-el” ma sentirete anche “mai si-qu-el”, troverete
sempre qualcuno che lo pronuncia in un modo diverso e crede di avere ragione,
quindi potete fregarvene e pronunciarlo come vi pare, l'importante è capirsi. MySQL è facile da installare, portabile su oltre venti piattaforme, scalabile, open
source e dispone di ampia documentazione on line.
Vediamo quali potrebbero essere dei buoni motivi per sceglierlo:
➢ Velocità
➢ Capacità
➢ Facilità d'uso
➢ Supporto del linguaggio SQL
➢ Connettività e sicurezza
➢ Portabilità
➢ Dimensioni ridotte
➢ Open Source
➢ Disponibile per molte piattaforme
Grazie alle soluzioni in alta affidabilità viene utilizzato da organizzazioni quali Yahoo!,
Slashdot, la NASA, AOL e Google.
E' disponibile in due licenze, quella GNU/GPL e con licenza commerciale per chi
necessita di supporto tecnico.
Al momento della scrittura di questo documento la versione stabile di MySQL è la 4.x,
la prossima release sarà la 5 che implementerà nuove funzionalità che aumenteranno
la potenza e la versatilità di MySQL.
1.2Storia
Nel 1979 Michael Widenius scrive UNIREG, uno strumento per la gestione dei
database, che viene utilizzato per generare pagine dimaniche ma richiede troppe
Pagina: 3
risorse.
Si decide quindi di sviluppare un nuovo gestore di database partendo dall'esperienza
di UNIREG e mSQL.
Nel 1996 fu rilasciata su Internet la versione 3.11.1 in forma di binari per GNU/Linux e
Solaris.
MySQL è stato progettato per fornire alte prestazioni su sitemi operativi open source e
macchine non particolarmente prestanti, organizzazioni che prima si sognavano un
DBMS ora possono averne uno ad alte prestazioni e a basso costo.
Il logo è un delfino azzurro chiamato “Sakila”, è stato scelto il delfino perché è un
animale piccolo e veloce che senza sforzo naviga tutti gli oceani “di dati”. 1.3Prestazioni
Le prestazioni di MySQL sono di tutto rispetto, riesce a tenere testa ai suoi più
rinomati concorrenti commerciali, anche se bisogna tenere conto che non dispone di
tutte le loro funzionalità.
Nel febbraio 2002 la rivista indipendente eWeek [http://www.eweek.com/] ha
effettuato alcuni test prestazionali dai quali si nota come MySQL possa sostenere le
prestazioni di Oracle.
(fonte: eWeek)
Pagina: 4
CAPITOLO 2 : INSTALLAZIONE
MySQL può essere installato su tutti i principali sistemi operativi in commercio, da AIX
a Novell NetWare passando per Windows.
Questo capitolo si occupa della sola installazione su macchine Unix like.
Se vogliamo utilizzare MySQL dobbiamo installare due componenti, il server e il
client, il primo serve per far girare il DBMS mentre il secondo serve per connettersi al
database ed effettuare le nostre query (richieste).
Potete scaricare MySQL dal sito ufficiale [http://dev.mysql.com/downloads/], ricordate
di verificare l'MD5sum o la firma gpg alla fine del download.
2.1Le versioni
Come prima cosa bisogna scegliere la versione di MySQL che si vuole utilizzare.
Attualmente le versioni disponibili sono la vecchia 3.23, la 4.1 che rappresenta la
versione stabile da impiegare in ambienti di produzione e la 5.0 che è la versione in
sviluppo e diventerà la prossima edizione stabile. Per le installazioni di produzione
consiglio la versione 4.1.
La filosofia di rilascio è “no known bugs”, cioè una versione è considerata matura
soltano se non presenta bachi conosciuti.
2.2Installare da binari
Se non volete perdere tempo per compilare i sorgenti, oppure volete soltanto
effetuare dei test, esistono i binari precompilati per molti sistemi operativi.
Per alcuni sono disponibili diversi binari compilati con molti compilatori e con varie
opzioni, in modo da scegliere il binario che più si adatta alla propria architettura.
Quindi se non avete particolari esigenze di personalizzazione potete utilizzare i binari,
altrimenti vi consiglio di compilarvi i sorgenti.
Prima di passare all'installazione leggete attentamente la documentazione allegata.
Per installare i binari è sufficente scompattare l'archivio nella directory desiderata e
configurare correttamente i permessi.
groupadd mysql
useradd ­g mysql mysql
cd /usr/local
gunzip < /PATH/TO/MYSQL­VERSION­OS.tar.gz | tar xvf ­
ln ­s FULL­PATH­TO­MYSQL­VERSION­OS mysql
cd mysql
scripts/mysql_install_db ­­user=mysql
chown ­R root .
chown ­R mysql data
chgrp ­R mysql .
bin/mysqld_safe ­­user=mysql &
Se la vostra distribuzione dispone di un gestore di pacchetti come apt (Debian) o
Pagina: 5
emerge (Gentoo) allora disporrete sicuramente di pacchetti precompilati ad hoc per il
vostro sistema operativo.
Ad esempio utilizzando apt­get bisogna digitare:
apt­get install mysql­server mysql­common mysql­client
2.3Installare da sorgenti
Se i binari non sono disponibili per il vostro sistema oppure volete personalizzare e
ottimizzare l'installazione dovete compilare il codice sorgente.
Prima di fare questo una lettura completa della documentazione allegata è
caldamente consigliata.
Se utilizzate un architettura ix86, utilizzare il compilatore di Intel icc, invece che gcc,
può garantirire un incremento di prestazioni anche notevole, anche se questa
operazione può non essere di facile realizzazione.
Come prima cosa decomprimiamo la tarpalla dell'archivio, ed eseguiamo lo script di
configurazione con ./configure, per conoscere le opzioni digitiamo ./configure –help,
vediamo le più importanti.
✗ --prefix=/directory installa MySQL nella directory indicata
✗ --with-tcp-port=numero permette di scegliere la porta su cui mettere il server in
ascolto
✗ --with-unix-socket-path=/directory/mysql.sock indica un file di socket diverso da
quello di default
✗ --without-server compila soltanto il client e le librerie necessarie, ma non il server
✗ --with-client-ldflags=all-static compila staticamente le librerie del client
✗ –with-mysqld-ldflags=all-static compila staticamente le librerie del server
✗ --with-charset=charset sceglie il set di caratteri
✗ --with-innodb abilita lo storage engine InnoDB (questa opzione è impostata di
default da MySQL 4 in poi)
✗ --with-bdb abilita lo storage engine Berkley DB
✗ --with-debug abilita la modalità di debug
✗ --with-low-memory la compilazione viene fatta usando poca memora (per le
macchine che ne sono carenti)
✗ --with-raid compila con il supporto raid
✗ --with-mit-threads compila con il supporto mit thread, serve sui vecchi sistemi BSD
oppure su Solaris
Per ottimizzare la compilazione dobbiamo giocare con i parametri del compilatore che
abbiamo scelto, vedere il relativo manuale per ulteriori informazioni.
Adesso possiamo digitare il tipico make e poi make install, successivamente se non ci
sono errori eseguiamo un make test per verificare la correttezza dell'installazione.
Se vogliamo essere sicuri di lavorare in un ambiente pulito eseguiamo prima un make
clean.
Adesso dobbiamo creare un file di configurazione my.cnf, il modo più facile per farlo è
copiare il file “preconfezionato” che troviamo nella directory support-files/mymedium.cnf dell'archivio di installazione.
Pagina: 6
Per inizializzare le tabelle dei permessi si usa il comando mysql_install_db che crea
tutte le tabelle necessarie dando i permessi all'utente root, per creare le tabelle con
permessi diversi usare il comando mysql_install_db --user=mysql che attribuisce i
permessi all'utente mysql.
Viene cosi creato un account di root e un account anonimo, entrambi senza
password, quindi come prima cosa ricordate di assegnare una.
Colgo l'occasione per ricordare che è buona norma non fare girare il processo con
privilegi di root, ma creare utente e un gruppo apposito.
Vediamo la procedura di installazione in dettaglio:
groupadd mysql
useradd ­g mysql mysql
gunzip < mysql­VERSION.tar.gz | tar ­xvf ­
cd mysql­VERSION
./configure ­­prefix=/usr/local/mysql
make
make install
cp support­files/my­medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db ­­user=mysql
chown ­R root .
chown ­R mysql var
chgrp ­R mysql .
bin/mysqld_safe ­­user=mysql &
2.4Il file di configurazione
Nel file di configurazione le righe vuote vengono ignorate, come quelle che
cominciano per # o ; che vengono considerate commenti.
Le opzioni sono raggruppate dal tag [group] dove group è il nome del programma per
cui devono valere le opzioni.
Indicare un opzione in questo file ha lo stesso effetto che passarla sulla riga di
comando quando si richiama il server, ad esempio l'opzione esempio è come –esempio e
esempio=valore è come –esempio=valore Pagina: 7
CAPITOLO 3 : UTILIZZO
In questo capitolo vedremo come connetterci a un server MySQL e come utilizzare
dei comandi di base, non tratto il linguaggio SQL perché richiederebbe un opera a
parte.
3.1Utilizzare il client
Il programma che utilizzeremmo per connetterci al server si chiama mysql, senza
opzioni si connette al server locale (127.0.0.1) utilizzando l'utente corrente.
Per indicare un host diverso si usa l'opzione -h host, dove host è l'indirizzo del server,
per utilizzare una porta diversa da quella standard si usa -P porta, per usare un
utente diverso di usa l'opzione -u utente e per fare in modo che venga richiesta una
password si usa l'opzione -p. alessandro@bronco:~$mysql ­u root ­p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.20­log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
il prompt indica che ora mysql è pronto a ricevere i nostri comandi, che saranno
impartiti utilizzando il linguaggio SQL.
Per ottenere una lista di comandi digitiamo \h
mysql> \h
For the complete MySQL Manual online visit:
http://www.mysql.com/documentation
For info on technical support from MySQL developers visit:
http://www.mysql.com/support
For info on MySQL books, utilities, consultants, etc. visit:
http://www.mysql.com/portal
List of all MySQL commands:
(Commands must appear first on line and end with ';')
help (\h) Display this help.
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are
db and host.
Pagina: 8
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute a SQL script file. Takes a file name as
an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
I comandi più utili sono \. per eseguire uno script esterno, \! per far eseguire un
comando dalla shell e \q oppure QUIT per uscire.
Adesso possiamo effettuare le nostre query (interrogazioni) in linguaggio SQL
ricordandoci di farle terminare con un punto e virgola.
mysql> SELECT VERSION();
+­­­­­­­­­­­­+
| VERSION() |
+­­­­­­­­­­­­+
| 4.0.20­log |
+­­­­­­­­­­­­+
1 row in set (0.04 sec)
Questa è una semplice query che visualizza la versione del server MySQL, come vedete
i dati vengono restituiti in formato tabulare e vengono indicate il numero di righe
ritornate assieme al tempo necessario per l'esecuzione della query (interrogazione).
Possiamo scrivere più comandi sulla stessa riga purchè siano separati dal ; mentre per
richiamare i comandi precedenti si utilizza la fraccia su.
Per eseguire dei comandi memorizzati in un file senza entrare nella modalità
interattiva possiamo redirigere l'input utilizzando quella che si chiama modalità batch:
mysql ­u root ­p < prova.sql 3.2Utilizzare un database
In questo paragrafo vedremo i comandi base per creare e utilizzare un database, per
approfondimenti su SQL vi consiglio un bel manuale che fa sempre bene la sera per
Pagina: 9
addormentarsi facilmente.
Come prima cosa, dopo avere stabilito una connessione con il client mysql, dobbiamo
indicare su che database intendiamo lavorare, oppure se disponiamo di privilegi
adeguati possiamo creare uno tutto per noi.
Per vedere i database gestiti dal server si usa il comando SHOW DATABASES; che ne
mostra un elenco
mysql> SHOW DATABASES;
+­­­­­­­­­­­­+
| Database |
+­­­­­­­­­­­­+
| convert |
| mysql | | phpstat |
| prova |
| test |
+­­­­­­­­­­­­+
5 rows in set (0.05 sec)
Se disponiamo dei permessi neccessari possiamo creare un database nostro con
l'istruzione CREATE DATABASE nomedatabase; nell'esempio seguente creiamo il
database topogigio:
mysql> CREATE DATABASE topogigio;
Query OK, 1 row affected (0.42 sec)
Ora possiamo indicare il database che vogliamo utilizzare con USE database;
mysql> USE topogigio
Database changed
Possiamo indicare il database che vogliamo utilizzare anche quando richiamiamo il
client mysql utilizzando l'opzione -u database.
Per vedere le tabelle all'interno di un database si usa SHOW TABLES;
mysql> SHOW TABLES;
Empty set (0.06 sec)
in questo caso non esistono tabelle perché non ne abbiamo create, per crearle si usa
CREATE TABLE che è una delle vostre care istruzioni SQL (lo avete letto il librone
vero?).
Per conoscere la struttura di una tabella si usa DESCRIBE tabella;
mysql> DESCRIBE php_stats_ip;
+­­­­­­­­+­­­­­­­­­­­­­­­­­­+­­­­­­+­­­­­+­­­­­­­­­+­­­­­­­+
| Field | Type | Null | Key | Default | Extra |
Pagina: 10
+­­­­­­­­+­­­­­­­­­­­­­­­­­­+­­­­­­+­­­­­+­­­­­­­­­+­­­­­­­+
| ip | varchar(15) | | PRI | | |
| date | int(11) | | | 0 | |
| hits | int(10) unsigned | | | 0 | |
| visits | int(10) unsigned | | | 0 | |
+­­­­­­­­+­­­­­­­­­­­­­­­­­­+­­­­­­+­­­­­+­­­­­­­­­+­­­­­­­+
4 rows in set (0.00 sec)
3.3Dati esterni
Per motivi quali portabilità, una migrazione o anche un semplice backup possiamo
essere costretti a caricare dati o scaricarli.
Un problema molto semplice è quello di caricare in una tabella dei dati contenuti in un
file di testo.
Il file deve contenere un record per ogni riga e i campi devono essere separati da un
carattere di tabulazione.
Questa istruzione carica nella tabella pluto i dati contenuti nel file testo.txt
LOAD DATA LOCAL INFILE '/testo.txt' INTO TABLE pluto; se il file txt è stato scritto da una macchina non Unix, dobbiamo normalizzare i
caratteri di “a capo” utilizzando un apposito tool oppure con LOAD DATA LOCAL INFILE '/testo.txt' INTO TABLE pluto LINES
TERMINATED BY '\r\n';
Per eseguire una LOAD DATA da shell di comando si usa il programma mysqlimport.
Pagina: 11
CAPITOLO 4 : FUNZIONAMENTO
Per utilizzare e amministrare al meglio MySQL è importante conoscere, almeno
superficialmente, il suo funzionamento.
4.1I dati
Tutti i dati, i file di registro e di stato sono conservati dal processo demone mysqld in
un apposita directory del file system chiamata directory dei dati.
La conoscenza di questa directory e di come i dati vengono memorizzati al suo interno
è basilare.
La posizione della directory dei dati viene scelta in fase di compilazione, tuttavia è
possibile specificarla quando si avvia il server utilizzando l'opzione –datadir=directory,
come recita la pagina del man mysqld
­h|­­datadir=path
Path to the database root.
è anche possibile specificare la directory dove abbiamo installato mysql:
­b|­­basedir=path
Path to installation directory. All paths are usually resolved relative to this.
Questi valori vengono usualmente indicati nel file di configurazione.
Se vogliamo conoscere la posizione del database di mysql dobbiamo verificare il
contenuto della variabile datadir utilizzando il comando mysqladmin variables da shell
di comando oppure impartire l'istruzione SHOW VARIABLES tramite client, entrambi
hanno l'effetto di mostrarci il contenuto di tutte le variabili del server mysqld.
All'interno della directory dei dati i database vengono memorizzati utilizzando una
struttura ad albero dove a ciasun database corrisponde una cartella e le tabelle in esso
contenute sono rappresentate con dei file al suo interno.
Nella figura seguente vediamo come all'interno della datadir sono contenute varie
directory, una per ogni database DB1 e DB2, dentro ad esse ci sono i file, ognuno
contenente una tabella.
Pagina: 12
Le directory hanno lo stesso nome del database che rappresentano, tali directory hanno
i permessi del server MySQL e da esso sono accessibili.
Al loro interno, le tabelle sono rappresentate da almeno un file, con estensione .frm
contenente la descrizione della sua struttura. A seconda del tipo di tabella i dati sono
memorizzati in file diversi.
Le tabelle ISAM utilizzano tre file, tutti con lo stesso nome, ed estensioni: .frm per
memorizzare le struttura, .ISM per gli indici e .ISD per i dati.
Anche le tabelle MyISAM utilizzano tre file, tutti con lo stesso nome, ed estensioni: .
frm per memorizzare le struttura, .MYI per gli indici e .MYD per i dati.
Le tabelle BDB sono contenute in due file, entrambi con lo stesso nome, ed estensioni: .
frm per memorizzare la struttura, .db per i dati.
Le tabelle Merge sono rappresentate da due file, entrambi con lo stesso nome, ed
estensioni: .frm per la struttura e .MRG che contiente una lista della tabelle MyISAM
interessate dal merge.
Le tabelle HEAP vengono tenute in memoria, su disco è presente soltanto un file .frm
che ne memorizza la struttura.
Le tabelle ti tipo InnoDB vengono memorizzate dall'apposito gestore in un area
comune, all'interno della directory del database è presente soltanto il file .frm per la
descrizione della struttura, mentre i dati vengono tenuti nello spazio unificato.
Quest'area comune, se non specificato, è la directory dei dati che può contenere anche
file PID, file di registro e di stato che forniscono informazioni sullo stato del server,
evenutali certificati.
Memorizzando i dati sotto forma di file e directory MySQL deve sottostare alle
limitazioni del file system su cui viene utilizzato.
I nomi di tabelle e database non devono quindi contenere caratteri speciali e non vedo
il motivo di utilizzare nomi “esotici” per le tabelle.
La dimensione dei file non dove eccedere la dimensione massima consentita dal nostro
file system, o la dimensione massima consentita da MySQL. Per questo argomento vi
rimando alla reference guide, siccome la dimensione massima, 4 Gbyte, basta e avanza
per le normali applicazioni.
Le prestazioni della nostra base di dati sono limitate dalle prestazioni del file system su
cui vengono tenuti i dati, è bene prestare attenzione alla sua scelta, in particolare deve
avere buone prestazioni quando deve gestire molti file di piccole dimensioni. 4.2Gli utenti
MySQL è un server multi utente, in grado quindi di gestire più utenti che accedono
contemporaneamente ai vari database
Per controllare l'accesso ai database ogni utente dispone di propri privilegi, solo
l'utente root ha poteri indiscriminati.
Gli utenti di MySQL non corrispondono agli utenti della macchina su cui viene
eseguito il server ma sono gestiti internamente.
I dati relativi alle utenze vengono tenuti in un database chiamato mysql, che contiene
tutti i dati neccessari all'autenticazione memorizzati nelle seguenti tabelle:
● user: contiene i dati sugli utenti e sui loro privilegi
● db: contiene un elenco dei database e loro proprietà
Pagina: 13
host: contiene l'elenco degli host ● proc: contiene le stored procedure (solo in MySQL 5)
● tables_priv: indica i privilegi sulle tabelle
● columns_priv: indica i privilegi sui campi
I dati di queste tabelle vanno modificate usando mysqladmin oppure il comando SET
PASSWORD o GRANT, sconsiglio, se non in casi di emergenza, di modificare queste
tabelle a mano.
I permessi assegnati ad un utente nella tabella user, valgono per qualunque database,
quindi attenzione perchè potrebbero esserci delle violazioni di sicurezza.
Per fare in modo che un utente abbia accesso ad un unico database si utilizzeranno le
tabelle tables_priv e colums_priv.
●
4.3I registri
E' possibile avviare MySQL attivando i registri, che servono per tenere sotto controllo
lo stato e la storia del server. I file di registro sono tenuti nella datadir.
Vediamo quali sono i registri principali:
● Il registro generale, memorizza le connessione e gli altri eventi.
● Il registro degli aggiornamenti, memorizza le modifiche al database
● Il registro degli aggiornamenti binario, ha lo stesso contenuto del registro degli
aggiornamenti memorizzato in un formato più efficiente
● Il registro delle query lente, indica le query lente, relativamente al valore della
variabile log_query_time
● Il registro degli errori, usato per registrare le informazioni diagnostiche
I registri di aggiornamento sono utili per ripristinare il database in caso di arresto non
previsto del server, visto che contengono la lista di tutte le query che hanno
modificato i dati.
4.4Le variabili
Quando il server viene avviato vengono inizializzate molte variabili al loro valore di
default che indicano come è configurato.
Il server mantiene due tipi di variabili, quelle globali, che hanno effetto sempre, e
quelle di sessione, che hanno effetto per un unico client.
Il valore di una variabile globale viene indicato nel file di configurazione oppure
quando si richiama il demone server, per cambiarne il valore si utilizza l'istruzione
SET GLOBAL.
Per ogni client che si connette il server mantiene delle variabili di sessione che
possono essere cambiate con SET SESSION.
Per ottenere la lista delle variabili e dei loro valori si utilizza il comando SHOW
VARIABLES, per visualizzare le variabili che rappresentano lo stato del server si usa
SHOW STATUS
Il comportamento del server può essere modificato lavorando sulle variabili, questo
risulta utile quando bisogna testare diverse opzioni e non si vuole perdere tempo in
riavvii del server.
Analizziamo le variabili più significative:
Pagina: 14
●
●
●
●
flush_time indica ogni quando scrivere su disco i dati della cache
key_buffer_size indica la dimensione del buffer per memorizzar i blocchi degli indici
max_connections il numero massimo di connessioni client accettate
table_cache dimensione della cache della tabella
4.5Tipi di tabelle
Il server è in grado di gestire diversi tipi di tabelle, ognuno con delle propie proprietà e
un modo diverso di rappresentare i dati.
● Tabelle ISAM
Sono il vecchio formato con cui è nato il server MySQL, l'accesso avviene in
maniera sequenziale. Questo tipo ormai non viene più usato, viene mantenuto
solo per motivi storici e con il tempo non sarà più supportato.
● Tabelle MyISAM
Il formato MyISAM è quello predefinito di MySQL ed è un miglioramento del
vecchio standard ISAM.
Ora le tabelle possono essere più grandi, hanno meno limitazioni sugli indici,
memorizzano i dati in un formato indipendente dalla macchina, offrono un
indice di compressione migliore e consentono l'uso degli indici FULLTEXT.
● Tabelle MERGE
Le tabelle MERGE sono un costrutto logico che unisce varie tabelle MyISAM,
effettuando una query ad una tabella MERGE il DBMS esaminerà tutte le
tabelle MyISAM di cui è composta.
● Tabelle BDB
Le tabelle Berkley DB possono essere ripristinate automaticamente in caso di
arresto improvviso del servente, supportano le transazioni e il blocco a livello
di pagina.
● Tabelle InnoDB
Il gestore delle tabelle InnoDB mette a disposizione transazioni, gestioni di
chiavi esterne, un sistema di locking migliorato e il ripristrino automatico in
caso di arresto improvviso del servente.
● Tabelle HEAP
Le tabelle HEAP sono memorizzate in memoria, hanno una lunghezza
predeterminata e sono quindi estremamente veloci.
Essendo tenute in memoria sono temporanee, quando il server viene arrestato
il loro contenuto è perso.
Pagina: 15
CAPITOLO 5 : AMMINISTRAZIONE
Un database contiene dati di vitale importanza per la nostra azienda, l'attività di
amministrazione è quindi fondamentale e di estrema delicatezza.
Un amministratore di database ha il compito di tenere la base di dati in perfetta
efficienza, mantenere le prestazioni a un buon livello, gestire gli account, prevenire i
disastri e rimettere tutto in funzione quando accadono.
Vedremo come amministrare un server da linea di comando e come utilizzare i
principali tool grafici.
5.1Avvio e arresto
Come prima cosa, vediamo la parte più importante, come avviare e arrestare il server
MySQL.
Utilizzando gli script in rc.d il server potrebbe essere avviato all'avvio della macchina,
che è la configurazione tipica.
Avviando il server a mano dobbiamo ricordare che il processo server prende i privilegi
dell'utente che lo avvia, quindi potrebbe essere pericoloso avviarlo da una shell di
root, ovviamente dovremmo utilizzare sempre la stessa utenza per avere uniformità
sui permessi dei file nella datadir.
Solitamente viene creato un account apposito per MySQL, che in Debian si chiama
appunto mysql, per specificarlo si utilizza l'opzione user=mysql nel file di
configurazione.
Il metodo di avvio tipico è richiamare lo script avvio, in Debian /etc/init.d/mysql, che
accetta argomenti tra cui start per avviare il server e stop per fermarlo.
Oppure si utilizza il comando mysqld_safe che avvia il server verficando che non
avvengano problemi e riavviandolo se collassa, per uteriori dettagli vedere la pagina
man mysqld_safe.
Sconsiglio avviare il server richiamando direttamente il comando mysqld.
I parametri forniti mysld_safe sono passati a mysqld, vediamo quali sono i più
importanti:
✗ --datadir=directory indica la posizione della directory dei programmi
✗ --basedir=directory indica la posizione della directory dei dati
✗ --defaults-file=file legge il file di opzioni indicato al posto di quello di default
✗ --pid-file=file indica la posizione del file pid
✗ --socket=file indica la posizione del socket
✗ --port=numero la porta su cui il server è in ascolto
✗ --nice=numero il valore di nice con cui viene eseguito il server
Lo spegnimento del processo server avviene con il comando mysqladmin shutdown
oppure con l'istruzione SHUTDOWN.
Se l'arresto non avviene correttamente sarà necessario fare un recovery.
5.2Backup, ripristino e manutenzione
Una corretta politica di backup è di fondamentale importanza per i nostri dati, per
Pagina: 16
qualsiasi tipo di dati, altrimenti prima o poi li perderete tutti.
In base alla frequenza delle operazioni di scrittura sul database dobbiamo decidere
ogni quanto fare una copia totale e incrementale.
Come abbiamo visto le tabelle sono rappresentate come file di vario tipo sul file
system, quindi è facile eseguire delle copie di sicurezza.
Uno dei metodi per fare un backup, quello più semplice ma anche sconsigliato, è fare
una copia di tutte le cartelle e file all'interno della datadir.
Per fare questo bisogna fare in modo che durante la copia i nostri dati mantengano la
consistenza, quindi prima di fare la copia blocchiamo le tabelle con LOCK TABLES e
scriviamo i dati dalla memoria al disco con FLUSH TABLES. Adesso possiamo
effettuare un backup con cp o con tar.
E' preferibile eseguire un backup a livello SQL cioè ottenendo un file che in linguaggio
SQL descrive tutte le nostre tabelle e i nostri dati.
Per fare questo usiamo mysqldump o mysqlhotcopy (che funziona solo per la tabelle
ISAM e MyISAM).
mysqldump genera file di testo che contengono la rappresentazione in linguaggio
SQL dei dati e delle tabelle, che lo rende portabile su diversi sistemi.
Per eseguire un backup incrementale possiamo copiare, ad esempio una volta a
settimana, tutti i dati e giornalmente mantenere una copia del updatelog dove
vengono memorizzati tutti i cambiamenti fatti al database in formato SQL.
In caso di arresto invonlontario del server DBMS o di problemi al file system possiamo
avere problemi con la rappresentazione dei dati, per ripristinare la situazione possiamo
ricorre al ripristino automatico oppure ai tool manuali.
Il ripristino automatico viene effettuato ogni volta che viene avviato il demone server,
nella maggior parte dei caso il controllo iniziale è in grado di risolver i problemi.
Ad esempio il seguente comando salva il contenuto del database paperino nel file
backup.bck
mysqldump paperino > backup.bck
Se vogliamo che il blocco sulle tabelle sia gestito automaticamente useremo
mysqlhotcopy, il seguente comando esegue una copia del db paperino nella directory /
backup
mysqlhotcopy paperino /backup/
Dobbiamo ricordare che non possiamo utilizzare i tool di riparazione su tabelle in uso
corrente, ma dobbiamo arrestare il server o porre un lock sulle tabelle.
Il programma principale utilizzato per ottiminizzare e riparare le tabelle si chiama
myisamchk, se viene eseguito senza opzioni esegue un controllo sull'integrità dei dati.
myisamchk *.MYI
Vediamo le opzioni di rilevanza:
✗ --help mostra una sintesi di aiuto
Pagina: 17
--silent scrive un output soltanto se incontra degli errori
✗ --verbose modalità verbose
✗ --version mostra la versione corrente
✗ --check controlla le tabelle in cerca di errori
✗ --extend-check esegue un controllo apporofondito
✗ --fast controlla solo le tabelle che non sono state chiuse correttamente
✗ --medium-check questa opzione esegue un controllo veloce e trova il 99.9% degli
errori
✗ --recover ripara gli errori
Quando si effettuano operazioni di riparazione su tabelle bisogna assicurarsi che
queste non siano in uso.
E' buona norma effettuara un controllo periodico e un ottimizzazione sulle tabelle.
✗
5.3Sicurezza
I database contengono tutti i nostri dati più importanti, non dovete quindi trascurare la
sicurezza della macchina.
L'argomento sicurezza è vastissimo per questo non lo affronto nella sua integrità ma
pongo degli spunti e indicazioni per un successivo approfondimento.
Come prima cosa bisogna impedire l'accesso alla directory dove sono contenuti i dati
e i file di registro, verificate quindi che tutti i permessi e le utenze siano corrette.
Controlliamo anche che il file delle opzioni che potrebbe contenere informazioni
pericolose non sia leggibile dagli altri utenti.
Se il DBMS non deve essere accessibile dalla rete facciamo in modo che accetti
connessioni solo dal file di socket o sul localhost. Questo e' il caso tipo di una
macchina LAMP (Linux Apache MySQL Php).
Se invece vogliamo accettare connessioni dall'esterno come prima cosa dobbiamo
sistemare i permessi degli utenti che dovranno accedere ai db.
Ricordate che i privilegi che vengono assegnati nella tabella user sono globali, e
quindi valgono per tutti i database residenti sulla macchina. Valutate attentamente
ogni privilegio che date a un utente.
Se non sono necessari cancella gli account per l'accesso anonimo che sono
pericolosi anche in sola lettura.
Il migliore modo per effettuare un autenticazione decente è richiedere un certificato
X509 valido.
Cercate di utilizzare connessioni SSL specificando tutte le varie opzioni tra cui il
numero massimo di tentativi.
Si deve disporre di OpenSSL installato e MySQL deve essere compilato con l'opzione
–with-openssl, per verificare se tutto funziona e il server supporta SSL si usa la
seguente query SHOW VARIABLES LIKE 'have_openssl' che mostra lo stato della
variabile have_openssl.
Pagina: 18
CAPITOLO 6 : STRUMENTI GRAFICI
6.1PhpMyAdmin
PhpMyAdmin [http://www.phpmyadmin.net] è un utility di amministrazione di facile
utilizzo, scritta in PHP e quindi utilizzabile via internet. Questo permette a qualsiasi
utente di amministrare il suo database da qualsiasi computer abbia un client web.
E' possibile gestire un intero server MySQL o soltanto un database, controllando tutte
le principali funzioni in base ai permessi di cui si dispone, quali ad esempio:
✔ Creare, modificare, cancellare tabelle e interi database
✔ Modificare struttura e dati delle tabelle
✔ Amministrare privilegi e utenze
✔ Eseguire query SQL
✔ Esportare e importare dati in vari formati
L'installazione è molto semplice, ma richiede la presenza di un server web e del
modulo php per interpretare gli script.
Supporta il dump e la compressione GZip o Bzip2 che permette di eseguire backup
da remoto utilizzando l'interfaccia web e di ripristinare il database.la
Se dobbiamo fare in modo che un utente possa gestirsi il proprio database
PhpMyAdmin è la scelta ideale perchè permette una facile gestione via web. Senza che
l'utente si debba connettere direttamente al server utilizzando un client come mysql.
Pagina: 19
BIBLIOGRAFIA
●
●
●
●
●
●
●
●
●
Sito ufficiale MySQL [http://www.mysql.com]
Sito ufficiale PHPMyAdmin [http://www.phpmyadmin.net]
“MySQL” di Paul DuBois, editore Pearson 2004
“SQL: The Complete Reference” di James R. Groff, editore McGraw-Hill
“MySQL Reference Manual” [http://www.mysql.com]
Sito ufficiale PHP [http://www.php.net]
Sito ufficiale Apache [http://www.apache.org]
Sito Perl DBI [http://dbi.perl.org]
Benchmark
comparativo
eseguito
dalla
rivista
[http://www.eweek.com/article2/0,4149,293,00.asp]
eWeek
Nota: tutti i marchi registrati che cito sono marchi registrati :P
Pagina: 20