Category Archives: Database

Controllare e riparare le tabelle in MySQL

MySQL mette a disposizione due alternative per controllare e riparare le tabelle dei database.

La prima possibilità consiste nell’utilizzare myisamchk, uno strumento creato appositamente per controllare e riparare le tabelle MyISAM.

Tramite questo strumento è possibile eseguire una scansione dei database, individuare i problemi presenti, ottimizzare le tabelle e ripararle.

La seconda possibilità consiste nell’utilizzare i comandi SQL, risulta infatti possibile controllare e riparare le tabelle tramite i comandi CHECK TABLEREPAIR TABLE e OPTIMIZE TABLE.

Solitamente l’utilizzo di myisamchk garantisce prestazioni migliori.

Vediamo ora come utilizzare gli strumenti.
Se si riscontrano problemi nell’accedere a una tabella, la prima cosa da fare è verificare la presenza di errori, è possibile eseguire questa operazione fermando MySQL e utilizzando il seguente comando.
myisamchk /usr/local/mysql/data/phpbb/user.myi

Se vengono individuati errori, è possibile riparare le tabelle eseguendo mysamchk con l’opzione –recover.
myisamchk –recover /usr/local/mysql/data/phpbb/user.myi

L’opzione –recover ricostruisce gli indici della tabella dopo avere cancellato i dati che potrebbero avere causato il problema.

Le stesse operazioni possono essere eseguire usando i comandi sql, per eseguire il controllo di una tabella è necessaio utilizzare il seguente comando dal prompt MySQL.
mysql> CHECK TABLE user;

Utilizzando REPAIR TABLE è poi possibile riparare la tabella.
mysql> REPAIR TABLE user;

Backup e ripristino dei database MySQL

In questa breve guida vediamo come effettuare il backup e il ripristino dei database MySQL.

Mysqldump è lo strumento più utilizzato per il backup dei database MySQL, viene utilizzato da linea di comando e genera un file ASCII in cui sono presenti le istruzioni SQL tramite le quali è possibile ricreare il database.

Il file può quindi essere utilizzato anche per creare il database su un nuovo server.

Per eseguire il backup di tutti i database presenti sul server è possibile utilizzare l’opzione –all-database.
mysqldump –all-database > backup.sql -u root -p
In questo caso il backup viene creato nel file backup.sql.
L’opzione -u specifica l’utente da utilizzare per collegarsi al database mentre -p indica che deve essere inserita la password.

Usando l’opzione –database è possibile specificare i database di cui si vuole fare il backup.
mysqldump –database database1 database1 > backup.sql -u root -p

Mysqldump supporta anche diverse altre opzioni tra cui troviamo le seguenti
–add-drop-table – Inserisce le istruzioni per eliminare le tabelle prima di crearle.
–add-lock – Blocca gli inserimenti nel database.
–extended-insert – Velocizza l’inserimento dei dati.
–lock-tables – Blocca le tabelle in lettura.

Il ripristino di un backup eseguito con mysqldump risulta piuttosto semplice e può essere fatto con il comando mysql.
mysql –one-database database < database.sql -u root -p

Abilitare l’accesso da remoto a MySQL

L’accesso remoto a MySQL è disabilitato di default per motivi di sicurezza, in alcuni casi è però possibile che sia necessario abilitarlo.

L’abilitazione dell’accesso da remoto a MySQL è piuttosto semplice, vediamo nel dettaglio le operazioni da eseguire.

Come prima cosa è necessario accedere al server su cui è in esecuzione MySQL e aprire il file di configurazione my.cnf.
La posizione di questo file può cambiare a seconda della distribuzione Linux che si utilizza, nel nostro caso facciamo riferimento a Centos e quindi lo troviamo in /etc/my.cnf.

vi /etc/my.cnf

Dopo che si è aperto il file, bisogna individuare la sezione [mysqld] e aggiungere la riga bind-address=<indirizzo ip del server>.

Dopo le modifiche, il file my.cnf sarà quindi simile al seguente.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
bind-address=1.1.1.1

Nel caso nel file fosse presente la riga skip-networking, bisogna cancellarla o commentarla.
Questa opzione specifica infatti che MySQL non ascolta le connessioni tcp/ip e le operazioni vengono eseguite attraverso socket Unix.

Una volta effettuate le modifiche, è possibile salvare il file e riavviare mysql.

/etc/init.d/mysql restart

A questo punto il server mysql è configurato per accettare connessioni da altri computer, è necessario però assegnare i permessi all’utente che sarà utilizzato per connettersi da remoto.

Come prima cosa si esegue la connessione al server mysql
mysql -u root -p mysql

In questo esempio ipotizziamo di dovere abilitare l’accesso a un nuovo database chiamato prova per l’utente user1 che si connette dall’indirizzo ip 2.2.2.2 e per cui la password è PASSWORD.
mysql> CREATE DATABASE prova;
mysql> GRANT ALL ON prova.* TO user1@’2.2.2.2′ IDENTIFIED BY ‘PASSWORD’;

Dopo che si è eseguito questo comando, l’utente specificato potrà connettersi da remoto al server mysql.

Un’ultima cosa da considerare è la presenza di firewall, in una situazione di questo tipo è necessario creare una regola iptables che permetta l’accesso alla porta 3306 dall’indirizzo specificato

/sbin/iptables -A INPUT -i eth0 -s 2.2.2.2 -p tcp –destination-port 3306 -j ACCEPT

Per testare il funzionamento delle modifiche, basta eseguire il seguente comando dal computer da cui si vuole effettuare l’accesso remoto al server mysql.

mysql -u user1 -h 1.1.1.1 -p

Abilitare l’accesso remoto a mysql può risultare necessario quando il server web e il server mysql non si trovano sullo stesso computer.
Risulta importante cercare di limitare al massimo le connessioni remote possibili per evitare problemi di sicurezza.

Replicare un database MySQL

n questa breve guida vediamo come creare una replica di un database MySQL su un server.

Dopo che si è creata la replica, gli aggiornamenti effettuati sul server Master saranno replicati sul server Slave.

Una replica non è un sistema di backup, si tratta però di una configurazione che può essere utile nel caso il server Master smetta di funzionare.
In una situazione del genere, basta infatti connettere l’aplicazione al server slave su cui sono presenti gli stessi dati.

Il presupposto della guida è che MySQL sia già installato sui due server.

Il funzionamento è piuttosto semplice, tutte le query eseguite sul server Master sono archiviate in un file di log.
Il server Slave legge questo file di log e ripete le query che modificano i dati sul proprio database.

Configurare il server Master
Sul server Master creiamo un utente user_slave che sarà utilizzato dal server Slave per connettersi.
mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘user_slave’@’%’ IDENTIFIED BY ‘password_slave’;
mysql>FLUSH PRIVILEGES;

A questo punto creiamo una cartella in cui saranno archiviati i log
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql

L’operazione successiva consiste nel modificare il file my.cnf in modo da specificare che il server è un server Master e quale database deve scrivere i log.
In questo esempio il database che viene replicato è test_db
vim /etc/my.cnf

[mysqld]
[ . . . .]
log-bin = /var/log/mysql/text_db-bin.log
binlog-do-db = test_db
server-id = 1
[ . . . .]

A questo punto è possibile riavviare il server mysql
service mysqld restart

L’operazione successiva consiste nell’inserire un READ LOCK nel database in modo che questo non possa essere modificato mentre viene eseguito il backup.
mysql> USE test_db
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+——————–+———-+———————-+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————–+———-+———————-+——————+| test_db-bin.000001 | 106 | test_db | |+——————–+———-+———————-+——————+

Il comando FLUSH TABLES WITH READ LOCK chiude tutte le tabelle fino a quando queste non vengono aperte eseguendo il comando UNLOCK TABLES.

Si può quindi eseguire il backup senza chiudere la shell Mysql precedente in modo che non venga eliminato il lock.
mysqldump -u root -p test_db > test_db.sql

e copiare il file sul server Slave
scp text_db.sql root@slave-server:

A questo punto è possibile eliminare il lock e chiudere la shell MySql
mysql> UNLOCK TABLES;
mysql> QUIT;

Configurare il server Slave
Come prima cosa modifichiamo il file my.cnf in modo da specificare che il server è Slave.

[mysqld]
[ . . . ]
server-id = 2
master-host = 10.42.43.1
master-user = user_slave
master-password = password_slave
replicate-do-db = test_db
master-connect-retry = 60

e poi riavviamo il server
service mysqld restart

A questo punto creiamo il database e importiamo il backup eseguito sul master.
mysql> STOP SLAVE;
mysql> CREATE DATABASE test_db;
mysql> quit;
[slave-server]$ mysql -u root -p test_db < test_db.sql

Per configurare il server come slave del master su cui abbiamo operato in precedenza è necessario eseguire il seguente comando.
mysql> CHANGE MASTER TO MASTER_HOST=’10.42.43.1′, MASTER_USER=’user_slave’, MASTER_PASSWORD=’password_slave’, MASTER_LOG_FILE=’test_db-bin.000001′, MASTER_LOG_POS=106;

i valori di MASTER_LOG_FILE e MASTER_LOG_POS sono quelli che abbiamo visto in precedenza configurando il server Master.

A questo punto è possibile avviare il server Slave
mysql> START SLAVE;

La configurazione è terminata e eseguendo il comando
mysql> SHOW SLAVE STATUS
dovrebbe essere possibile visualizzare i vari dettagli della configurazione.

MySQL – MyISAM o InnoDB?

MySQL supporta diverse tipi di tabelle, quelle più utilizzate sono MyISAM e InnoDB, quale scegliere?

La scelta dipende dall’utilizzo che si deve fare del database, vediamo brevemente le differenze.

Le tabelle MyISAM si caratterizzano per le prestazioni elevate e le richieste ridotte in termini di risorse, bisogna però dire che mancano alcune caratteristiche piuttosto importanti come il supporto alle foreign key e alle transazioni mentre viene data la possibilità di utilizzare indici FULLTEXT.
Le tabelle MyISAM sono compose da tre file che hanno estensioni .frm, .MYD e .MIY.
I file .frm contengono la struttura della tabella, i file .MYD contengono i dati mentre nel file .MIY si trovano gli indici.
Le tabelle possono essere spostate da un server all’altro semplicemente copiando questi tre file.

InnoDB è decisamente più completo, supporta infatti le foreign key e le transazioni, risulta quindi possibile creare database relazioni e transazionali.
Le transazioni sono operazioni che iniziano con l’istruzione BEGIN, seguita da altre istruzioni che modificano i dati della tabella e concluse dall’istruzione COMMIT con la quale viene eseguita la scrittura dei dati o dall’istruzione ROLLBACK con la quale vengono annullate le istruzioni precedenti.
Si tratta quindi di una funzionalità molto importante nel commercio elettronico e nei casi in cui è richiesta la conferma della validità di una carta di credito.
Le foreign key permettono invece di creare vincoli di integrità referenziale tra le tabelle.
Il maggiore numero di funzionalità ha però come conseguenza richieste di risorse superiori rispetto a MyISAM.

Quando utilizzare MyISAM?
MyISAM è la soluzione migliore quando il database riceve un numero di query per la lettura dei datti decisamente maggiore rispetto alle operazioni di modifica dei dati.
Se il rapporto tra letture e scritture è inferiore al 15%, risulta consigliabile l’utilizzo di MYISAM.
Un altro caso in cui è preferibile MyISAM è quello in cui si hanno risorse disco e memoria limitate.

Quando utilizzare InnoDB?
Quando si ha la necessità di creare database relazionali e transazionali, è necessario scegliere InnoDB.

Controllare MySQL con Mytop

MyTop è uno strumento sviluppato in Perl per il monitoraggio dei database MySQL tramite il quale è possibile verificare le prestazioni e capire come le applicazioni interagiscono con il database.

MyTop è presente nei repository Fedora e quindi può essere installato tramite yum.

Se si utilizza Red Hat Enterprise o CentOS, è possibile installare yum tramite il repository di RPMForge.

MyTop richiede nome utente e password per accedere al database, queste informazioni possono essere salvate nel file di configurazione o inserite al prompt dei comandi quando vengono richieste.
Il file di configurazion è ~/.mytop e ha un contenuto simile al seguente.

user=root
#pass=sekret
host=localhost
db=test
#port=3306
socket=/var/lib/mysql/mysql.sock
header=1
color=1

Oltre a nome utente e password, è quindi possibile specificare il database e la porta da utilizzare.

Per avviare MyTop è necessario eseguire il seguente comando.
mytop –prompt

Il risultato del comando è simile al seguente

MySQL on localhost (5.1.44)                                  up 5+20:03:30 [11:56:50]
Queries: 91.3k  qps:    0 Slow:     2.0         Se/In/Up/De(%):    01/98/00/00    qps now:    2 Slow qps: 0.0
Threads:    3 (   2/   0) 22/00/00/00
Key Efficiency: 93.3%  Bps in/out:  43.9/  9.0   Now in/out: 111.1/ 3.0k

Id      User         Host/IP         DB      Time    Cmd Query or State
–      —-         ——-         –      —-    — ———-
193        rq       localhost        rqp         0  Query show full processlist
195        rq       localhost        rqp         0  Query SELECT DISTINCT p_tag

Le prime quattro righe contengono informazioni riassuntive relative al server MySQL.
Nella prima riga viene visualizzato il nome del server, la versione di MySQL e il tempo da cui è attivo.
La seconda riga mostra il numero di query elaborate dal server, il numero medio di query al secondo, il numero di query attive e il numero di query lente.
La terza riga mostra informazioni relative ai thread.
La quarta riga mostra quanto spesso le informazioni sono lette dal buffer e non dal disco.

Le righe successive mostrano i thread attivi con informazioni come id, utente, computer da cui proviene la connessione, database e comando eseguito.

MyTop può essere utile per individuare le cause che provacano il rallentamento del database, il limite dell’applicazione è che possibile ottenere informazioni solo da un database alla volta.

Ottimizzare MySQL per WordPress

WordPress richiede la presenza di MySQL per il funzionamento, gli articoli e le impostazioni vengono infatti salvate all’interno di un database.
Questo significa che, quando vengono visualizzate delle pagine, vengono eseguiti numerosi accessi al database, risulta quindi importante ottimizzare MySQL per WordPress.

In questo articolo vediamo quali sono i principali parametri da modificare all’interno del file di configurazione di MySQL, My.cnf.

nano /etc/mysql/my.cnf

Una delle prime impostazioni riguarda la disabilitazione del supporto per InnoDB, si tratta infatti di un’impostazione attiva di default che non è necessaria per WordPress e che ha come conseguenza l’utilizzo elevato di memoria.
Per disabilitare il supporto InnoDB è necessario inserire la seguente riga.
skip-innodb

Vediamo ora gli altri parametri.
query_cache_size – La funzionalità query cache permette a MySQL di salvare in cache i risultati delle query. Questo permette di velocizzare le operazioni quando vengono eseguite spesso le stesse query sugli stessi dati.
Se Apache e MySQL sono in esecuzione sullo stesso server, è possibile trovare il valore da assegnare al parametro dividendo i mb di memoria per 16.
Nel caso Apache sia installato su un altro server, è possibile aumentare il valore.
key_buffer_size – Specifica la dimensione del buffer utilizzato con gli indici. Un buffer più grande ha come conseguenza una maggiore velocità nell’esecuzione dei comandi SQL.
Il valore dovrebbe essere grande abbastanza per contenere tutti gli indici, generalmente non deve essere inferiore a un quarto della memoria e non superiore alla metà.
table_cache – Controlla il numero di tabelle aperte per tutti i thread. Ogni volta che MySQL accede a una tabella, la mette in cache.
Se il sistema deve accedere a molte tabelle, averle in cache velocizza le operazioni.
Per determinare il valore da assegnare al parametro bisogna verificare il valore di open_tables nei momenti di maggiore traffico eseguendo SHOW STATUS.
Il valore di open_tables dovrebbe essere inferiore a quello assegnato a table_cache.
thread_cache – Nel caso si abbia un server che riceve molte connessioni, può essere utile impostare questo valore  in modo che il valore di Threads_created, visualizzato eseguendo il comando SHOW STATUS, smetta di crescere.
sort_buffer_size – Specifica la dimensione del buffer per le clausole ORDER BY e GROUP BY. Aumentando il valore è possibile velocizzare queste query.

Un modo per semplificare la configurazione di MySQL consiste nell’utilizzare MySQLTuner, uno script in perl che verifica le prestazioni del server e in base a queste sufferisce le modifiche da effettuare alla configurazione.
wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Riparare tabelle MySql

I database MySql possono danneggiarsi, è possibile quindi trovarsi nella situazione in cui si ha la necessità di riparare tabelle MySql.

Vediamo quindi come è possibile riparare i vari tipi di tabelle MySql da linea di comando.

Riparare tabelle MyISAM

Accedere alla cartella in cui si trovano i file del database
cd /var/lib/mysql/nomedb
e eseguire il seguente comando
myisamchk *. MYI

Riparare tabelle ISAM
Accedere alla cartella in cui si trovano i file del database
cd /var/lib/mysql/nomedb
e eseguire il seguente comando
isamchk *. MYI