8. Amministrazione di MySQL
Lezioni di web attivo
8. Amministrazione di MySQL
8.1 Architettura client/server
Dal punto di vista strutturale il DBMS MySQL si comporta come un server standard TCP.
Questo significa che su un host accessibile dalla rete Internet è in esecuzione una applicazione
che accetta connessioni TCP da applicazioni client su una porta predefinita (well-known, la
porta di default è la 3306).
Una volta stabilita la connessione il client può interrogare il server inviando query sulla
connessione usando un protocollo di comunicazione specifico del sistema MySql. Il server
risponde alle interrogazioni elaborando i dati richiesti sul proprio file-system e fornendo i
risultati attraverso la connessione.
La connessione viene chiusa dal client quando ha terminato le operazioni di interrogazione. Il
server è in grado di accettare più connessioni contemporaneamente.
Per motivi di sicurezza l’accettazione delle connessioni può essere limitata secondo vari criteri:
Solo da localhost: i clients devono trovarsi sullo stesso host su cui si trova il
server. Questa è la situazione più comune; infatti questo non vuole dire che l’utente
finale deve trovarsi su localhost ma vi si deve trovare lo script che interroga, ad
esempio una pagina di web attivo che fa parte del sito e può essere interrogata da
chiunque da Internet.
Solo dall’interno del dominio: i clients possono trovarsi su altri hosts
appartenenti allo stesso dominio del server. Questa situazione consente lo sviluppo
di banche dati distribuite fra più host dello stesso dominio che potrebbero anche
trovarsi fisicamente lontani
Da host esterni al dominio: i clients possono trovarsi anche su host esterni al
dominio (eventualmente anche tutti gli host di internet). Questa situazione è molto
pericolosa perché lascia aperto il sistema ad intrusioni attraverso la porta di
connessione. L’unica utilità che presenta è la possibilità di manutenzione remota
usando una applicazione client
Una volta ottenuta la connessione al server l’accesso ai databases gestiti dal server DBMS è
limitato dal fatto che ogni database è accessibile solo dagli utenti MySql autorizzati ad
accedervi.
Gli utenti MySql non coincidono con gli utenti di sistema ma sono invece utenti che devono
essere definiti al momento della generazione di un nuovo database. Un database quando nasce
è proprietà di un particolare utente predefinito (root) che comunque rimane proprietario di tutti
i database del DBMS. L’utente root può concedere diritti (lettura, inserimento, cancellazione,
modifica, concessione) ad altri utenti eventualmente identificati da password.
Solo gli utenti che possiedono un database lo possono interrogare nei limiti dei diritti che sono
stati loro concessi.
Esistono molti strumenti di manutenzione sia integrati nel pacchetto MySql che forniti da terze
parti. Questi strumenti si possono riassumere in tre categorie:
Client a riga di comando: sono comandi integrati nel pacchetto e residenti su
localhost. Sono utili all’amministratore di DBMS per effettuare una rapida
manutenzione locale in particolare usando script automatizzati.
Applicazioni client visuali: sono applicazioni visuali che consentono una ricca
manutenzione interattiva. In genere si trovano su un host diverso e quindi il DBMS
deve consentire l’accesso ad host esterni (in genere nel dominio)
Client web on-line: sono pagine attive collocate su localhost ed accessibili
dall’esterno, combinando in questo modo i pregi delle due soluzioni precedenti
(accesso sicuro e interfaccia interattiva) ma sono piuttosto lenti.
ITIS “O.Belluzzi” – Laboratorio di Sistemi
1-10
Lezioni di web attivo
8. Amministrazione di MySQL
8.2 Client a riga di comando
I client a riga di comando si invocano da una shell del sistema operativo che ospita il DBMS
inserendo il comando, gli eventuali parametri e le eventuali redirezioni. E’ infatti possibile
redirigere sia l’input standard (tastiera) sia l’output standard (monitor) a files in modo da
trasformare l’azione del client da interattiva a batch.
I comandi di uso più frequente sono:
mysqladmin: esegue operazioni amministrative sul DBMS come creare, cancellare
database, fermare e riavviare il server, fornire informazioni sul suo stato.
mysql: è una console di comando che consente di effettuare interrogazioni ai
database gestiti dal DBMS sia in modo interattivo che in modo batch.
mysqlshow: questa utility mostra in formato testo la struttura dei databases.
mysqldump: questa utility mostra in formato testo sia la struttura che i contenuti
dei databases sotto forma di interrogazioni. Se usata in modo batch costituisce un
strumento di backup di un database che si combina con mysql usato in modo batch
come strumento di restore.
mysqlimport: questa utility consente l’importazioni di dati da un file di testo (ad
esempio un CSV)
mysqlcheck: questa utility effettua il controllo di correttezza e la eventuale
riparazione di banche dati.
8.3 Applicazioni client visuali
Esistono molte applicazioni client visuali, tutte realizzate da terze parti, che integrano in
un’unica finestra tutte le funzioni a riga di comando. Ne vengono citate alcune:
mysqlyog: semplice client visuale per win32
mycc: client visuale multipiattaforma
dbdesigner: ambiente multipiattaforma di sviluppo visuale dello schema. Può
collegarsi anche ad altri DBMS oltre a MySql (via ODBC) e consente oltre alla
progettazione anche l’analisi visuale dello schema.
8.4 Client web online
Esistono molte applicazioni web on-line, tutte realizzate da terze parti, che integrano in un sito
web attivo tutte le funzioni a riga di comando. Nel pacchetto MySql sia sulla piattaforma linux
che win32 è integrata l’applicazione phpMyAdmin che consente, attraverso pagine autenticate,
l’amministrazione del DBMS e la manipolazione delle banche dati attraverso una interfaccia
grafica intuitiva.
ITIS “O.Belluzzi” – Laboratorio di Sistemi
2-10
8. Amministrazione di MySQL
Lezioni di web attivo
8.5 Amministrazione dei database
Le operazioni di amministrazione dei database si possono realizzare in vari modi. Negli esempi
che seguono si utilizzano gli strumenti a linea di comando per creare, progettare, popolare,
proteggere e verificare un database.
Tutte le informazioni che consentono di amministrare i vari DB sono a loro volta contenute in
un DB predefinito denominato “mysql” accessibile sono all’amministratore.
8.5.1 Creazione di un database
Il database deve essere generato dall'amministratore mysql invocato però dalla shell
normale utente di sistema che conosce la password dell'amministratore mysql.
un
In questo esempio l'utente di sistema è "user" che dopo avere ottenuto la propria shell di
sistema esegue il comando:
mysqladmin -u <utente> -p create <nomedatabase>
-u <utente> specifica l'utente che accede al comando.
L'utente root è il root di mysql non il root del sistema ospite.
-p specifica che il comando chiede interattivamente la password (non è sicuro fare script che
contengano già la passoword in chiaro). In ambiente di sviluppo locale è comodo lasciare
l’amministratore senza password (easyphp installa proprio in questo modo) ma su un host
operativo la presenza della password di amministratore è inderogabile.
Se l'utente non dispone dell'accesso come amministratore mysql deve chiedere la creazione
della banca dati all'amministratore.
Esempio:
mysqladmin -u root -p create prova
Il DBMS chiede la password e se accettata genera il DB di nome “prova”. Il DB al momento è
vuoto nel senso che non contiene alcuna tabella ed è proprietà esclusiva di “root”
8.5.2 Verifica della esistenza del database
Ora il DB esiste ma non contiene dati e non ha neppure uno schema.
La verifica può essere fatta con il comando:
mysqlshow -u root -p
Il comando restituisce una lista di tutti i database gestiti dal DBMS del tipo:
+-------------+
| Databases |
+-------------+
| ...
|
| mysql
|
| ...
|
| prova
|
| ...
|
+-------------+
ITIS “O.Belluzzi” – Laboratorio di Sistemi
3-10
Lezioni di web attivo
8. Amministrazione di MySQL
dove "..." sta per altri database eventualmente gestiti dallo stesso DBMS (la lista può variare in
funzione dell'host)
Rieseguendo il comando con il DB prova come parametro si ottengono ulteriori dettagli su quel
DB:
mysqlshow -u <utente> -p <nomedatabase>
Nel caso in esempio:
Mysqlshow -u root -p prova
Viene mostrata la lista delle tabelle che compongono il database (in questo caso ancora vuota)
Database: prova
+--------+
| Tables |
+--------+
+--------+
Per il momento solo l'amministratore mysql può fare questa operazione ma dopo aver
concesso l'uso della banca dati "prova" all'utente mysql “user” anch'esso può visitare la banca
dati che possiede.
8.5.3 Assegnazione dei privilegi di accesso al manutentore del database
Il manutentore della banca dati è un utente mysql che possiede tutti i diritti di accesso alla
banca dati e quindi su di essa può fare qualsiasi azione eccetto in genere concedere i propri
diritti ad un altro utente mysql.
L'utente mysql non è necessariamente coincidente con l'utente di sistema ma per motivi di
comodità si può definire un utente mysql avente "nome utente" e "password" coincidenti con
l'utente ldi sistema che farà la manutenzione.
Per motivi di sicurezza l'accesso di questo utente alla banca dati è protetto da password, quindi
ad ogni connessione con il DBMS questa password deve essere specificata.
L'accesso come utente manutentore quindi può essere fatto solo dalla shell inserendo
interattivamente la password o da una pagina web autenticata che acceda dinamicamente ad
una banca dati di servizio contenente nomi utenti e password criptate accessibile in lettura
senza password solo da localhost (vedi punto 7).
In questo modo la password non compare staticamente in chiaro nella pagina web (sarebbe un
fatale pericolo per la sicurezza). Non si può usare per l'autenticazione la banca dati mysql
stessa perchè essa è a sua volta protetta dalla password di root.
L'assegnazione dei previlegi al manutentore si effettua inserendo le informazioni nella banca
dati di servizio "mysql" accessibile solo da shell da parte root mysql:
mysql -u root -p mysql
ITIS “O.Belluzzi” – Laboratorio di Sistemi
4-10
8. Amministrazione di MySQL
Lezioni di web attivo
Questo comando, che chiede interattivamente la password, porta al prompt della consolle
mysql avendo connesso la banca dati di servizio mysql
mysql>
La banca dati "mysql" è una normale banca dati quindi tutti i comandi sql sono validi, ma
esistono particolari comandi di servizio che facilitano la manutenzione:
GRANT <privilegi> ON <database>.* TO <utente>@<host> IDENTIFIED BY '<pwd>';
REVOKE <privilegi> ON <database>.* FROM <utente>@<host>;
In questo caso si vuole ottenere un utente che ha tutti i diritti su un particolare database ma
che si deve connettere al motore dall'host locale (quindi o una shell dell'host locale oppure una
pagina autenticata collocata sull'host locale).
La query da eseguire è:
GRANT ALL PRIVILEGES ON prova.* TO user@localhost IDENTIFIED BY '1QazXsW2';
ALL PRIVILEGES vuole dire che l'utente sul quel database può fare:
SELECT: selezionare righe da tabelle
INSERT: inserire righe in tabelle
UPDATE: modificare righe in tabelle
DELETE: cancellare righe da tabelle
INDEX: modificare indici di tabelle
ALTER: modificare la struttura di tabelle
CREATE: creare tabelle
DROP: cancellare tabelle
GRANT: cedere diritti sul db ad altri utenti
localhost vuole dire che l'utente è riconosciuto solo se si connette al motore dallo stesso host
su cui si trova il motore mediante shell o pagina autenticata.
La password compare in chiaro nel comando. La query GRANT provvede alla criptazione prima
della memorizzazione nel database mysql.
N.B. Non è sicuro mantenere in archivio files che contengono le password in chiaro. Poiché è
comodo utilizzare scripts per queste operazioni di manutenzione questi scritps vanno rimossi
dal file system dopo l'uso.
8.5.4 Privilegi del utente generico locale della banca dati
L'utente generico locale è un utente fittizio che possiede i principali diritti di manipolazione
della banca dati ma non può modificare la struttura della banca dati ne può cancellerla.
Questo utente non protetto da password viene utilizzato per la generazione delle pagine web di
visita, inserimento e cancellazione dati.
Poichè le pagine web attive sono eseguite dal web server che si identifica come utente linux
"nobody" è opportuno utilizzare questo nome per l'utente generico locale.
L'assenza di password consente di creare pagine web locali che non contengono informazioni
pericolose. Se si vuole limitare l'accesso alle pagine che contengono modifiche al contenuto
della banca dati (questo utente comunque non riesce a modificarne la struttura) si possono
proteggere le pagine con autenticazione mediante un altro data base contentente nomi utente
e password criptate (vedi punto 7).
ITIS “O.Belluzzi” – Laboratorio di Sistemi
5-10
Lezioni di web attivo
8. Amministrazione di MySQL
Come nel caso precedente l'assegnazione dei diritti deve essere fatta da root accedendo alla
banca dati "mysql".
In questo caso si vuole ottenere un utente che ha solo i diritti di SELECT,INSERT,UPDATE su un
particolare database ma che si deve connettere al motore dall'host locale (quindi o una shell
linux dell'host locale oppure una pagina non necessariamente collocata sull'host locale).
La query da eseguire è:
GRANT SELECT,INSERT,UPDATE,DELETE ON prova.* TO nobody@localhost\g
L'assenza di IDENTIFIED BY fa si che l'utente non sia protetto da password.
8.5.5 Privilegi del utente generico di dominio della banca dati
L'utente generico di dominio è un utente fittizio che possiede solo diritti di consultazione sulla
banca dati ma non può modificare ne contenuti ne struttura della banca dati ne può
cancellarla.
Questo utente non protetto da password viene utilizzato per la generazione delle pagine web di
visita da un host che contiene le pagine web ad un altro host dello stesso dominio in modo da
realizzare una banca dati distribuita.
Poichè le pagine web attive sono eseguite dal web server che si identifica come utente linux
"nobody" è opportuno utilizzare questo nome per l'utente generico di dominio.
L'assenza di password consente di creare pagine web remote che non contengono informazioni
pericolose.
Come nel caso precedente l'assegnazione dei diritti deve essere fatta da root accedendo alla
banca dati "mysql".
In questo caso si vuole ottenere un utente che ha solo i diritti di SELECT su un particolare
database e si deve connettere al motore da un host appartenente allo stesso dominio dell'host
locale.
La query da eseguire è:
GRANT SELECT prova.* TO nobody@'%.scuole.bo.it';
Il simbolo % è una wildcard che consente l'accesso da qualsiasi host appartente al dominio
scuole.bo.it
L'assenza di IDENTIFIED BY fa si che l'utente non sia protetto da password.
8.5.6 Accessi da fuori del dominio
Gli accessi da fuori del dominio falliscono perchè non esiste alcun utente che abbia accesso al
motore da fuori del dominio.
Questo non vuole dire che non si può consultare o modificare la banca dati accedendo da un
host fuori dominio ma che per farlo si devono visitare pagina collocate nel dominio o in un
particolare host del dominio per rispettivamente consultare e/o modificare la banca dati.
Ciò che fallisce è la visita di pagine collocate fuori dominio che tentano di accedere al motore.
A questo punto ci troviamo con tre tipi di utenti della banca dati:
Manutentore:
manut@localhost
tutti i diritti
Generico locale:
nobody@localhost
consultazione e modifica
Generico remoto:
[email protected]
consultazione
ITIS “O.Belluzzi” – Laboratorio di Sistemi
6-10
8. Amministrazione di MySQL
Lezioni di web attivo
8.6. Generazione delle tabelle
La generazione delle tabelle può essere fatta solo dal manutentore che ha i diritti di create e
drop.
Sebbene la generazione si possa fare anche mediante una pagina web poichè è una operazione
occasionale è più rapido farla da una shell.
Conviene produrre uno script da inviare allo standard input della consolle mysql mandando lo
standard output ad un file di testo in modo da diagnosticare e correggere rapidamente gli
inevitabili errori di progetto e digitazione.
mysql -u utente -p banca_dati < file_script > file_log
Gli elementi in grassetto sono parametri a cui si devono sostituire i valori attuali.
Lo script è un file di testo contenente i comandi da inviare in consolle. Ad esempio:
#cancella la tabella se esiste
DROP TABLE IF EXISTS tab1;
#crea la struttura della tabella
CREATE TABLE tab1 (
ID INT NOT NULL AUTO_INCREMENT,
Dato
CHAR(80) NOT NULL,
PRIMARY KEY Pk (ID),
INDEX dt (Dato)
);
Note:
- Il simbolo # indica commento da non processare
- La cancellazione prima della creazione serve per rieseguire un comando che era
parzialmente fallito in precedenza
- La cancellazione di una tabella cancella automaticamente anche tutti i dati contenuti
- Il comando eseguito in questo modo torna immediatamente alla shell del sistema operativo
in modo silente
- I risultati ed eventuali fallimenti si vedono aprendo il file ris.txt
- I valori sono sensibili al caso (maiuscolo/minuscolo) mentre i comandi sql no.
Ora la banca dati contiene tabelle anche se vuote.
Ripetendo il comando del punto 8.5.2, ora anche come utente "manut" si possono vedere le
tabelle contenute in bd:
mysqlshow -u manut -p prova
Viene mostrata la lista delle tabelle che compongono il database (in questo caso ancora vuota)
Database: prova
+--------+
| Tables |
+--------+
| tab1
|
+--------+
ITIS “O.Belluzzi” – Laboratorio di Sistemi
7-10
Lezioni di web attivo
8. Amministrazione di MySQL
Rieseguendo il comando con la banca dati e la tabella come parametro si ottengono ulteriori
dettagli su quella tabella :
mysqlshow -u <utente> -p <nomebancadati> <nometabella>
Nel caso in esempio:
mysqlshow -u manut -p prova tab1
Viene mostrata la struttura delle tabelle che compongono il database.
Database: prova
Table: tab1
Rows: 0
+-------+----------+------+-----+---------+----------------+---------------------------------+
| Field | Type
| Null | Key | Default | Extra
| Privileges
|
+-------+----------+------+-----+---------+----------------+---------------------------------+
| ID
| int(11) |
| PRI |
| auto_increment | select,insert,update,references |
| Dato | char(80) |
| MUL |
|
| select,insert,update,references |
+-------+----------+------+-----+---------+----------------+---------------------------------+
8.7 Popolazione delle tabelle
La popolazione delle tabelle serve per generare alcuni dati fittizi che consentano di verificare la
correttezza della struttura di db prima di procedere alla generazione delle pagine attive.
Se emergono dei difetti è opportuno modificare la struttura prima di andare troppo avanti nello
sviluppo.
Come nel caso della creazione conviene produrre uno script da inviare allo standard input della
consolle mysql mandando lo standard output ad un file di testo in modo da diagnosticare e
correggere rapidamente gli inevitabili errori di progetto e digitazione.
mysql -u utente -p banca_dati < file_script > file_log
Gli elementi in grassetto sono parametri a cui si devono sostituire i valori attuali.
Lo script è un file di testo contenente i comandi da inviare in consolle. Ad esempio
(popola.sql):
#cancella il contenuto della tabella
DELETE FROM tab1\p\g
#dimmi l'ultimo id inserito
SELECT LAST_INSERT_ID()\p\g
#aggiungi una riga
INSERT INTO tab1 (Dato) VALUES('Pippo')\p\g
SELECT LAST_INSERT_ID()\p\g
INSERT INTO tab1 (Dato) VALUES('Pluto')\p\g
SELECT LAST_INSERT_ID()\p\g
INSERT INTO tab1 (Dato) VALUES('Topolino')\p\g
SELECT LAST_INSERT_ID()\g
#mostra il risultato finale
SELECT * FROM tab1\p\g
ITIS “O.Belluzzi” – Laboratorio di Sistemi
8-10
8. Amministrazione di MySQL
Lezioni di web attivo
Il comando di creazione in questo caso quindi è:
mysql -u manut -p accpriv < popola.sql > ris.txt
Note:
- Il simbolo # indica commento da non processare
- La cancellazione prima dell'inserimento serve per svuotare la tabella e rieseguire un
comando che era parzialmente fallito in precedenza
- La estrazione dell'ultimo ID inserito serve solo per scopo informativo
- L'inserimento viene fatto specificando solo il dato perchè l'ID viene automaticamente
generato dal motore e il suo valore si vede nella richiesta di LAST_INSERT_ID successiva.
- I risultati ed eventuali fallimenti si vedono aprendo il file ris.txt
- I valori sono sensibili al caso (maiuscolo/minuscolo) mentre i comandi sql no.
Ora la banca dati contiene tabelle popolate.
Si può vedere il contenuto oltre che nel file ris.txt anche dalla consolle mysql:
In questo esempio:
mysql -u manut -p prova
In questo modo si potranno fare tutte le operazioni su prova
ma anche:
mysql –u nobody prova
In questo modo si potranno fare select,insert,update,delete su prova
In ogni caso dal prompt mysql il comando di base per verificare il successo della popolazione
è:
mysql> SELECT * from tab1;
Viene mostrato il contenuto della tabella selezionata
+----+----------+
| ID | Dato
|
+----+----------+
| 1 | Pippo
|
| 2 | Pluto
|
| 3 | Topolino |
+----+----------+
3 rows in set (0.00 sec)
ITIS “O.Belluzzi” – Laboratorio di Sistemi
9-10
Lezioni di web attivo
8. Amministrazione di MySQL
E’ anche possibile manipolare la tabella:
mysql> INSERT INTO tab1 (Dato) VALUES('Minni')\p\g
ripetendo la select si ottiene:
+----+----------+
| ID | Dato
|
+----+----------+
| 1 | Pippo
|
| 2 | Pluto
|
| 3 | Topolino |
| 4 | Minni
|
+----+----------+
4 rows in set (0.00 sec)
8.8 Esportazione/Backup della banca dati
Una banca dati può essere esportata in un file di testo mediante il comando mysqldump.
Lo scopo dell’esportazione è duplice:
Trasferimento della banca dati in un altro motore di banca dati.
Salvataggio di backup di una copia della banca dati.
L’operazione di esportazione produce un file di testo (stile unix) che contiene i comandi di
cancellazione, creazione e popolazione di una banca dati.
In questo modo il file può essere usato come ingresso in uno script che carichi la banca dati
nello stesso motore (restore da un backup) oppure in un diverso motore (importazione)
La sintassi del comando è la seguente:
mysqldump –u root –p –-add-drop-table nomebancadati > filediesportazione
Note:
- Il comando produce l’uscita su standard output quindi per ottenere un file si deve
redirezionare.
- L’opzione –-add-drop-table inserisce le cancellazioni delle tabelle
8.9 Importazione della banca dati
Una banca dati può essere importata da un file di testo salvato o costruito nel modo indicato
nel paragrafo precedente.
E’ anche possibile importare singole tabelle da un file di testo ad esempio in formato CSV
mediante il comando mysqlimport
mysqlimport [options] database textfile1
Opzioni:
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
ITIS “O.Belluzzi” – Laboratorio di Sistemi
10-10
