MySQL Tips and Tricks

MySQL e' un ottimo e semplice database... ma questo non vuol dire che qualche trucco non sia utile qualche volta!

Questo documento riporta percio' qualche suggerimento per utilizzare al meglio MySQL. La lettura presuppone una certa conoscenza di MySQL... meglio leggere prima MySQL - Introduzione, MySQL 4 Oracle DBAs, ... !

Se invece potete proseguire... gli argomenti sono organizzati in semplici paragrafi: Amministrazione Backup&Restore Gestione spazio disco Recovery Sicurezza Lock Varie ed eventuali Datatype La cassetta degli attrezzi Abbracadabra

DBA: Amministrazione

Che sta facendo il DB? Oltre allo show processlist (da mysql) sono disponibili alcuni comandi (da shell):

mysqladmin -i 5 status

mysqladmin -i 5 extended-status -r | grep Com_select

Volete installare un secondo server sullo stesso sistema? Ecco i comandi da utilizzare [NdA dalla versione 5.7 si utilizza il comando mysqld --initialize]:

MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
MYSQL_TCP_PORT=3307
export MYSQL_UNIX_PORT MYSQL_TCP_PORT
mysql_install_db --user=mysql --datadir=/mydata2

Troppi DB MySQL da gestire? Eccome come cambiare il PROMPT per non confondersi:

[mysql]
prompt='mysql [\h] {\u} (\d) > '

DBA: Backup&Restore

E' fondamentale impostare una completa politica di backup. Nella maggior parte dei casi sono necessari periodici backup a freddo e ("E" non "O") salvataggi logici. Per ottenere quest'ultimo:

mysqldump --single-transaction --all-databases | gzip > /backup/db.`date +\%Y\%m\%d`.sql.gz

# Generazione della chiave
openssl req -x509 -nodes -newkey rsa:2048 -keyout key.priv.pem -out key.pub.pem

# File con password per il backup per evitare possa essere vista con un semplice ps
[mysqldump]
host = localhost
user = root
password = "xxx"

# Effettuazione del backup
mysqldump --defaults-extra-file=/etc/mysqldump.cnf db \
  | gzip \
  | openssl smime -encrypt -binary -text -aes256 -out db.sql.enc -outform DER key.pub.pem

# Eventuale decrittazione
openssl smime -decrypt -in db.sql.enc -binary -inform DEM -inkey key.priv.pem -out db.sql

Ovviamente la chiave va generata ed utilizzata su un server differente...

Per esportare solo la struttura del DB?

mysqldump --all-databases --skip-add-drop-table -w 0=1 > db.sql 

mysqldump effettua un salvataggio di tipo warm (a database attivo ma con lock delle tabelle salvate). Il lock e' di breve durata e non crea alcun problema se non l'attesa da parte delle transazioni in scrittura sulla stessa tabella. MySQL fornisce, nella versione a "pagamento" denominata Enterprise, il tool mysqlbackup che consente l'hot backup degli oggetti sull'Engine InnoDB e sostituisce quindi il backup fisico a freddo [NdA sono disponibili anche tool di terze parti come XtraBackup]. Le tabelle sugli altri Engine vengono comunque salvate in modalita' warm.
Con MySQL e' anche possibile eseguire un Point-in-time Recovery. Per effettuarlo e' necessario utilizzare il bin-logging. Una descrizione piu' completa si trova nel documento MySQL Replication.

Per recuperare solo uno dei molti DB contenuti in un dump basta "ritagliare" la parte di interesse dal file generato da mysqldump. Quando le basi dati sono di grandi dimensioni utilizzare il vi o un altro editor e' impossibile ma... basta seguire questo esempio [NdE la sintassi del comando tail puo' essere differente (eg. tail -n +XXX ...)]:

$ grep -n "^CREATE DATABASE" dump.alldb.sql
20:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `horde` /*!40100 DEFAULT CHARACTER SET latin1 */;
433:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */;
785:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
...

$ tail +433 dump.alldb.sql | head -`expr 785 - 433` > dump.mydb.sql

Se il numero di record e' elevato l'import dei dati puo' richiedere parecchio tempo. Per rendere piu' veloce il caricamento comando e' opportuno ricorrere ai settaggi: SET AUTOCOMMIT=0; SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;

Dalla versione 8.0.21 e' possibile utilizzare un nuovo trucco quando si effettua un caricamento iniziale pesante: ALTER INSTANCE DISABLE INNODB REDO_LOG . Attenzione pero': se il DB si blocca e' necessario inizializzarlo nuovamente.

Un database contiene solo i dati... le grant sono mantenute nel database di nome mysql. Per migrare le grant relative a USERNAME puo' essere utile:
mysqldump --extended-insert=FALSE mysql | grep USERNAME > grant_user.sql

A me piace anche questo script che con un trucco estrae tutti i database utili e gli utenti:

CONN="-uroot -pxyz"
SQL="SET @@group_concat_max_len = 1000000; SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema','sys')"
LIST=`mysql ${CONN} -ANe"${SQL}"`
OPT="--single-transaction --routines --max_allowed_packet=512M --databases ${LIST}"
mysqldump ${CONN} ${OPT} | gzip > Data.sql.gz

SQL="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user not in('', 'root', 'repl')"
mysql ${CONN} -ANe"${SQL}" | mysql ${CONN} -AN | sed 's/$/;/g' > Grants.sql

Il file generato deve essere controllato con attenzione e puo' quindi essere usato sul sistema di destinazione con mysql... source grant_user.sql Occhio: ricordarsi di dare poi il comando FLUSH PRIVILEGES;

Con la versione 5.7 lo script non funziona piu'... la SHOW GRANTS [NdE volutamente (ultima riga)] non riporta la password! Basta aggiungere la creazione delle utenze, lo script completo diventa:

CONN="-uroot -pxyz"
SQL="SET @@group_concat_max_len = 1000000; SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema','sys')"
LIST=`mysql ${CONN} -ANe"${SQL}"`
OPT="--single-transaction --routines --max_allowed_packet=512M --databases ${LIST}"
mysqldump ${CONN} ${OPT} | gzip > Data.sql.gz

SQL="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user not in('', 'root', 'repl')"
mysql ${CONN} -ANe"${SQL}" | mysql ${CONN} -AN | sed 's/$/;/g' > Grants.sql

SQL="SELECT CONCAT('SHOW CREATE USER ''',user,'''@''',host,''';') FROM mysql.user WHERE user not in('', 'root', 'repl')"
mysql ${CONN} -ANe"${SQL}" | mysql ${CONN} -AN | sed 's/$/;/g' > CreateUsers.sql

Gestione spazi

La domanda piu' frequente: "Come si libera spazio su InnoDB?"
Non si libera spazio su InnoDB. Punto.

Nel seguito si vedra' il perche' e come risolvere i problemi di spazio su MySQL. Ma la discussione e' diversa per ogni Engine e richiede qualche nozione sul loro funzionamento. Alla fine c'e' anche un cenno al partitioning... continuate a leggere!

InnoDB

InnoDB utilizza un file ibdata1 per i dati/tabelle InnoDB sia interni (InnoDB ha un suo data dictionary) che degli utenti. Il file InnoDB non puo' mai essere ridotto di dimensione.
InnoDB utilizza il paradigma MVCC per mantenere letture consistenti alle transazioni committate. In pratica di ogni tabella esistono contemporaneamente piu' versioni all'interno dei file ibdataX.

Tutto cio' premesso quando si cancellano dati da una tabella in realta', grazie all'MVCC, lo spazio richiesto da una tabella aumenta! Terminate le transazioni lo spazio resta allocato alla tabella in oggetto e, se frammentato, non viene piu' utilizzato. In questo caso un'OPTIMIZE risolve il problema.

Se non si usa il file_per_table anche effettuando una DROP di una tabella lo spazio non viene liberato al file system. Lo spazio della tabella cancellata resta a disposizione di tutte le tabelle InnoDB.
Anche utilizzando l'opzione file_per_table, il data dictionary, gli UNDO ed i temporanei di InnoDB sono mantenuti sulla partizione di sistema [NdA non e' piu' cosi' nella versione 8.0, che pero' e' ancora poco diffusa]: quindi transazioni di lunga durata o query pesanti possono fare crescere pesantemente ibdata1.
Come si puo' controllare l'allocazione di spazio? Non c'e' un modo semplice e diretto: ls -l, SHOW ENGINE INNODB STATUS \G, innochecksum -S ibd_file_copy, ... sono i comandi piu' utili.

Ritornando alla domanda iniziale: "Come si libera spazio su InnoDB"? La stessa domanda puo' essere posta in modi diversi: How to shrink InnoDB, How to purge ibdata1 file in MySQL, ...
Non si libera spazio su InnoDB! Si fa un backup, si ricostruisce da zero il database e si ricaricano i dati.

Ma c'e' un trucco: non c'e' bisogno di "ricostruire" tutto il DB. I passi semplificati sono:

• Backup completo (meglio fare anche un backup fisico)
• Cancellazione di tutti i DB InnoDB (oppure di tutti i DB tranne mysql e performance schema)
• Si spegne il DB
• Si cancellano ibdataX e ib_logX
• Opzionale: si imposta innodb_file_per_table [NdA occhio alla versione MySQL]
• Si riavvia MySQL (MySQL automaticamente ricrea i file di InnoDB "vuoti")
• Si ricaricano i dati

Avete usato il trucco e si sono corrotte le tabelle innodb_%_stats, slave%, ...?
Probabilmente state usando la versione 5.6 ed avete incrociato un bug... Niente paura: potete ricostruire manualmente le tabelle!

MyISAM

MyISAM utilizza file ISAM per la memorizzazione delle tabelle. E' veloce, efficiente e molto semplice (pero' non gestisce le transazioni e non supporta i constraint). Per ogni tabella vengono utilizzati tre file: il .frm contiene la definizione della tabella, il file .MYD per i dati ed il file .MYI per gli indici. File e directory possono essere spostati liberamente, possono essere utilizzati link simbolici su altri file system, possono essere cancellati e ricreati. MySQL non se ne accorge e lo spazio liberato viene immediatamente rilasciato al file system.
In effetti MySQL non si accorge neanche quando sono corrotti i dati/indici su MyISAM: la volta successiva in cui MySQL accede ad una tabella MyISAM corrotta restituisce un errore. Per correggere gli errori... basta un CHECK TABLE: continuate a leggere!

Partitioning

Il partitioning e' terribilmente comodo per svecchiare i dati e liberare spazio.

Se si utilizza il partitioning con InnoDB il comando ALTER TABLE t1 OPTIMIZE PARTITION p0 in realta' lavora sull'intera tabella (Bug #11751825, Bug #42822). E' pero' possibile lavorare per partizione con ALTER TABLE t1 REBUILD PARTITION p0; ALTER TABLE t1 ANALYZE PARTITION p0 .

Nelle prime versioni il partitioning era piu' efficiente su MyISAM che su InnoDB, nella versione 5.7 InnoDB ha implementato in modo nativo sull'Engine il partitioning rendendolo piu' effciente. Nella versione 8.0 NON e' piu' supportato il partitioning su MyISAM.

Il modo migliore per svecchiare una vecchia partizione e crearne una nuova e' con i passi:

Recovery

Non dovrebbe succedere ma in qualche caso i database si corrompono... Le ragioni piu' frequenti sono: file system full, errore umano, crash del server e bug MySQL (quest'ultima causa molto meno frequente delle precedenti). Qualunque sia la ragione si puo' tentare un recovery che e' differente a seconda degli Engine utilizzati.

Diagnosticare il problema di solito e' facile perche' gli errori di MySQL sono sempre molto espliciti. Oltre che all'applicazione i principali errori vengono riportati anche nel file di log (default hostname.err). Per la cura... bisogna usare i comandi giusti. E' necessaria attenzione perche' i comandi di DDL usano lock esclusivi e qualche comando puo' cancellare qualche dato: generalmente e' consigliato un backup prima di operare. [NdA chiarisco meglio: e' necessario un backup prima di effettuare qualsiasi operazione di recovery a meno che i dati contenuti la base dati siano inutili e si voglia solo provare a giocare con il recupero dati]

Con MyISAM e' possibile effettuare sia il controllo che la correzione delle tabelle in errore. Procedendo in ordine di gravita' vediamo come intervenire dal caso piu' semplice a quello piu' grave.
Con qualche statement SQL a database attivo (o con il phpMyAdmin) si risolve il 90% dei problemi che occorrono su tabelle specifiche (eg. tabelle in stato crashed):

USE database_name
CHECK TABLE [QUICK | FAST ...] table_name
REPAIR TABLE ...

mysqlcheck --all-databases --port=3306 --host=mydb.xenialab.it -s -p

mysqladmin shutdown
myisamchk --silent --force */*.MYI

Se anche questo non risolve bisogna ripartire dai backup. Nei casi peggiori si ricaricano i dati partendo direttamente dai file .MYD !

Con InnoDB il comportamento e' differente poiche' vengono gestite le transazioni e, all'avvio di MySQL, vengono effettuati tutti i controlli e le eventuali correzioni del caso (rollforward e rollback). Inoltre InnoDB ha un suo data dictionary (ovviamente allineato con quello MySQL).
Quando le tabelle principali sono su InnoDB sono utilizzabili, ed a volte consigliati, tool specifici di terze parti in particolare per i backup a caldo ed il PITR.
Per importare velocemente da SQL un DB contenente tabelle InnoDB e' opportuno usare il comando:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
SOURCE dump_file_name
COMMIT;

In qualche caso si puo' disallineare il data dictionary di InnoDB rispetto a quello MySQL e/o ai file presenti sul file system. Non e' grave ma vanno rimessi a posto!
Per riallinearli ci sono alcuni trucchi come quello di creare una tabella con lo stesso nome e le stesse colonne su un altro database, copiarne il file .frm nella directory corretta e quindi cancellare le tabelle con la DROP. Se sono state create tabelle intermedie (eg. durante una ALTER TABLE) queste hanno nomi strani non richiamabili da SQL [NdE iniziano con #] ma e' possibile comunque cancellate con questa sintassi: DROP TABLE `#mysql50##sql-101_8356c`;
Se il DB va in crash... e' possibile forzare il recovery di InnoDB con:

[mysqld]
innodb_force_recovery = 4

DBA: Sicurezza

Al termine dell'installazione l'utente root locale non ha password, e' concesso l'accesso a tutti gli utenti locali e tutti possono accedere al DB test. Lo script di installazione di MySQL consiglia di assegnare le password: fatelo!
[NdE la versione 5.6 genera una password amministrativa di default in /root/.mysql_secret e costringe a cambiarla al primo accesso]

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h hostname password 'new-password'

La crittografia utilizzata per le password da MySQL nelle versioni precedenti alla 4.1 e' notoriamente modesta. Non utilizzatela!

Quando un host esagera con i tentativi di accesso sbagliati MySQL lo blocca! Il limite e' impostato dalla variabile max_connect_errors (default 10) e serve a proteggere il DB da brute force attack. [NdE la versione 5.6 ha come default 100 e consente di controllare gli errori] Per resettare i valori e riabilitare gli host il comando e' mysqladmin flush-hosts.

Una base dati puo' essere forzata anche attraverso istruzioni SQL non correttamente filtrate dalle applicazioni: SQL Injection.
Se non sono gestite correttamente le stringhe di input ed i parametri si puo' alterare il comportamento dell'applicazione. Un esempio? Inserire ' or 1=1 -- oppure ' OR '1'='1 in una form web. Dal punto di vista sintattico MySQL permette da molti linguaggi (eg. PHP) l'esecuzione di statement multipli rendendo particolarmente efficace l'hacking delle applicazioni. Per evitare le Injection l'applicazione deve trattare correttamente tutti i caratteri. Ecco come fare (in PHP):

function sql_quote( $value )
{
    if( get_magic_quotes_gpc() )    {
          $value = stripslashes( $value );    }
    if( function_exists( "mysql_real_escape_string" ) )    {
          $value = mysql_real_escape_string( $value );    }
    else    {
          $value = addslashes( $value );    }
    return $value;
}

Se si utilizzano applicazioni Open Source o di terze parti, per evitare l'SQL Injection e' fondamentale mantenere aggiornata l'applicazione ed installare tutte le patch di sicurezza. Sono molto diffuse su applicazioni CMS e la maggioranza di esse utilizza una base dati MySQL. Per controllare la versione dei principali tipi di applicazioni CMS, operando sulla base dati e non sul web server, e' possibile utilizzare lo script myCMSver.sh delle MySQL Utilities.

L'auditing non e' disponibile nella versione community di MySQL. Ma qualcosa si puo' fare lo stesso per rispondere ai diversi requisiti di legge sulla sicurezza!
Non e' un trigger di logon... ma e' possibile forzare l'esecuzione di una serie di comandi SQL al momento della connessione di un utente con il parametro init_connect. Tale parametro puo' essere sfruttato per eseguire un logging delle sessioni o personalizzare il login. Il parametro non e' attivo per gli utenti DBA (su cui si puo' utilizzare MySQL Proxy).

Locking

Le modalita' di locking in MySQL dipendono dall'Engine utilizzato. Con SHOW FULL PROCESSLIST si possono controllare eventuali statement SQL in attesa di un lock. Ma non molto di piu', per esempio non si vede la sessione che detiene il lock. Il trucco c'e'... ma dipende dall'Engine!

• InnoDB ha una gestione del locking sofisticata che consente il lock a livello di riga. In casi particolari (eg. utilizzo di indici differenti da primary key) InnoDB effettua una lock escalation con un Gap Lock o un Next Key Lock che possono portare a situazioni di deadlock.
  Per verificare i lock e' possibile utilizzare il comando SHOW [ENGINE] INNODB STATUS\G. Analizzare l'output del comando non e' banale... Un trucco: per disporre di maggiori informazioni, come se gia' non bastasse l'enorme output restituito, si debbono creare le seguenti tabelle con i comandi:

  CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;
  CREATE TABLE innodb_monitor(a int) ENGINE=INNODB;

  Richiami successivi a SHOW INNODB STATUS avranno un dettaglio maggiore. Dalla versione 5.6.16 vanno usati i comandi:

  set GLOBAL innodb_status_output=ON;
  set GLOBAL innodb_status_output_locks=ON;

  Dalla versione 5.5 sono state introdotte nuove viste che consentono un'analisi dei lock con l'SQL (eg. innodb_lock_waits) sicuramente piu' comprensibile. Un esempio si trova in questo documento.

• MyISAM gestisce il locking a livello di tabella solamente. Quindi non e' consigliato come Engine in caso di un elevato livello di concorrenza. Se la concorrenza e' bassa il lock a livello di tabella e' semplice e veloce. Crescendo pero' con la concorrenza il lock a livello di tabella diventa il bottleneck per le transazioni.
  Per verificare i lock attivi su MyISAM l'unico modo e' utilizzare il comando mysqladmin debug che riporta nel file di log .err lo stato del sistema e le catene di attesa dei lock.

Varie ed eventuali

Questa sezione contiene appunti sparsi che non ho ancora avuto tempo di organizzare per bene...

Vi e' un ampio insieme di variabili che possono essere utilizzate per configurare MySQL. Alcune hanno validita' per sessione ed altre sono globali. Molte di esse sono dinamiche e possono essere variate in qualsiasi momento senza richiedere la ripartenza del server. La sintassi e l'utilizzo sono molto semplici:

SHOW VARIABLES LIKE '%log%';
select @@slow_query_log_file;
SET GLOBAL slow_query_log_file='/tmp/queryTest.log'
...

SET SQL_LOG_BIN = {0 | 1}
SET SQL_LOG_OFF = {0 | 1}
SET GLOBAL GENERAL_LOG =  {'ON' | 'OFF'} /*50112 La gestione del GQLog e' dinamica a partire dalla 5.1 */
SET FOREIGN_KEY_CHECKS = {0 | 1}
SET UNIQUE_CHECKS = {0 | 1}

Generalmente MySQL viene installato come servizio. Per attivarlo e' quindi sufficiente il comando:

# [sudo] service mysql start

Usate MySQL su MS-Windows? Nonostante tutto... funziona!
C'e' solo qualche piccola differenza (a parte le tabelle ed i database che sono "case insensitive", le performance inferiori, i limiti sul numero di thread paralleli, il numero di connessioni supportate piu' basso, il kill delle sessioni che non funziona in tutti i casi, ... dovuti all'OS): si attiva e disattiva come servizio da pannello di controllo, il file di configurazione e' my.ini, puo' servire impostare la variabile lower_case_table_names ...

Usate MySQL su Linux? Funziona sempre!
Se avete una database serio e' opportuno un minimo di tuning di MySQL. Ci sono moltissime pagine sull'argomento... L'importante e' non andare in conflitto con l'utilizzo della memoria sul sistema.

sync
echo 3  > /proc/sys/vm/drop_caches

Datatype

MySQL offre un'ampia scelta di Datatype. Nella definizione di una tabella e' opportuno scegliere il tipo di dato che consenta di rappresentare tutti i valori necessari con il minor utilizzo di spazio.
Attenzione: alcuni datatype hanno limiti diversi a seconda dell'Engine utilizzato.

Sui datatype per le stringhe MySQL e' sempre stato molto ricco poiche' consente di definire a livello di singola colonna il character set e la collation. Attenzione pero' alle scelte!
Il default storico sono latin1 e latin1_swedish_ci rispettivamente (che personalmente ho sempre mantenuto per rispetto a tutte le svedesi ;-). Il character set utf8 NON e' un utf8 completo (e richiede "solo" 3 byte), molto piu' completo e' utf8mb4 disponibile dalla versione 5.5.
Le collation di default sono "_ci" ovvero non fanno differenza tra mauiuscoli e minuscoli: per questo la comparazione di stringhe in MySQL non fa differenza (eg. utf8_general_ci). Ma se si utilizzano collation "sensitive" le comparazioni e gli ordinamenti si comportano in modo molto diverso (eg. utf8_bin)!

Per l'assegnazione di valori sequenziali su un campo chiave si utilizza tipicamente il datatype SERIAL (ovvero un BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE) che e' molto efficiente. L'ultimo valore assegnato ad un AUTO_INCREMENT e' restituito dalla funzione LAST_INSERT_ID() che puo' essere richiamata piu' volte ma viene aggiornata ad ogni assegnazione di chiave.

create table dept (deptno serial primary key, city char(20));
create table emp (empno serial primary key, deptno int, name char(20));

insert into dept(city) values ('TORINO');
insert into emp(deptno,name) values (@lastDeptno:=LAST_INSERT_ID(),'BIANCHI');
insert into emp(deptno,name) values (@lastDeptno,'ROSSI');

Oltre al datatype sequence sono molto comode le variabili MySQL. Ad esempio un progressivo puo' essere facilmente realizzato con:

select @prog:=0;
select @prog:=@prog+1, ename, sal from emp;

Sempre sfruttando le variabili e' possibile "simulare" la funzionalita' di una SEQUENCE (cfr. Oracle):

-- Preparazione:
-- Creazione della tabella delle "sequence", una sola tabella basta per ogni "sequence" che viene individuata dal campo "name"
-- L'INSERT successiva assegna il valore iniziale alla "sequence": SQ_ordini. Al primo accesso si otterra' il codice 69
CREATE TABLE sequence (name CHAR(10) PRIMARY KEY,  last_val INT UNSIGNED NOT NULL);
INSERT INTO sequence (name, last_val) VALUES ('SQ_ordini', 68);

-- Utilizzo da parte dei programmi:
-- Raccolta di un nuovo valore da 'SQ_ordini'
UPDATE sequence SET last_val=@val:=last_val+1 WHERE name='SQ_ordini';
-- Utilizzo del valore...
SELECT @val;
INSERT INTO ordini (id, importo, fornitore) VALUES (@val, 500000, 'XeniaLAB srl');

L'esempio e' molto semplice ma... funziona sempre! Poiche' l'operazione di incremento e di assegnazione del valore viene effettuato all'interno di un'unica istruzione SQL (l'UPDATE), l'operazione e' atomica e quindi NON e' soggetta a "race condition". Insomma detto in parole semplici non puo' succedere che due utenti diversi ottengano lo stesso valore della "sequence".
L'esempio non sfrutta alcuna funzionalita' avanzata di MySQL ed e' quindi utilizzabile con qualsiasi Engine e praticamente in tutte le versioni MySQL (occorre solo fare attenzione ai lock per non rallentare gli accessi).

MySQL NON supporta in modo nativo i millisecondi per i campi TIME, DATETIME e TIMESTAMP. C'e' una "feature request" aperta da anni sull'argomento (http://bugs.mysql.com/bug.php?id=8523). La gestione dei millisecondi e' stata inserita nella versione 5.6 di MySQL [NdE disponibile in beta 4Q2011 e GA a febbraio 2013].
Per le versioni precedenti e' pero' possibile creare una libreria in C / C++ che raccoglie il tempo di sistema e definire una funzione MySQL che lo restituisce il tempo con la precisione in millisecondi come stringa e quindi trattarlo poi come occorre alla logica applicativa. Nella nota gia' citata e' riportato un esempio, perfettamente funzionante, per la creazione della libreria e la definizione della funzione MySQL.
Sempre sulle date MySQL fornisce diverse funzioni di conversione.
Non funzionano le conversioni con i timezone? Forse perche' non sono popolate le tabelle mysql.time_zone e simili [NdA chissa' perche' non viene fatto all'installazione]. Basta caricarli con:
 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

La cassetta degli attrezzi

Ogni buon DBA ha una serie di tool che utilizza per risolvere problemi o anche solo per semplificare qualche attivita'. Anche per lavorare con MySQL e' opportuno farsi una "cassetta degli attrezzi" adatta!

Sono molti i programmi Client per accedere ai dati. Ad esempio SQuirreL consente di accedere a qualsiasi base dati raggiungibile JDBC.
Vi sono anche ottimi strumenti di amministrazione: MySQL Administrator e' il programma di amministrazione Client/Server distribuito da mysql AB. phpMyAdmin e' il tool piu' utilizzato per la gestione di basi dati MySQL, realizzato in PHP si distingue per una semplice e completa interfaccia web; SQLyog e' un tool molto completo di recente disponibile anche in una versione enterprise.

Per gli amministratori il primo consiglio e' quello di installare MySQL su un sistema operativo serio. Intendo un sistema operativo che fornisca strumenti di analisi, che non cambi in modo impredicibile, che sia configurabile e stabile, che sia completamente documentato, che implementi in modo accettabile lo stack TCP-IP, ... Per quello che mi riguarda, gli unici OS che soddisfano a tali condizioni, almeno tra quelli che conosco a sufficienza, sono tutti gli ambienti Unix (ovviamente anche e soprattutto Linux/GNU).
I comandi piu' utili, per controllare il sistema che ospita una base dati MySQL, sono: ps che riporta i processi attivi, netstat per evidenziare lo stato delle connessioni di rete, fuser e, soprattutto, lsof che riportano i file aperti per ogni processo, trace che consente di seguire le chiamate a sistema e fare un debug dei casi piu' complessi (trace, truss, tusc, strace sono i nomi con cui viene chiamata questa utility su Linux/GNU, Solaris, HP-UX, AIX, ...), snoop che traccia lo scambio di pacchetti in rete (tcpdump e' il comando su Linux), sar vmstat iostat... per raccogliere e mantenere le statistiche di utilizzo del sistema, ... Alcuni dei comandi citati sono descritti nella pagina Comandi Unix poco noti.

Altri strumenti necessari sono (alcuni vengono riportati con maggior dettaglio in Software libero): un tool di generazione di carico ed analisi delle prestazioni (eg. Grinder), qualche utility di gestione (eg. Putty, VNC, TightVNC ), ... Se il numero di sistemi ed ambienti da monitorare e' elevato risulta utile un controllo automatico (eg. Nagios), anche se, per chi ha acquisito una licenza Enterprise, e' molto efficace e completo l' Enterprise Monitor [NdE descritto anche in questo documento]. Piu' recente, molto completo e disponibile anche in versione Community, e' MySQLWorkbench. Per i piu' smaliziati, ma e' raro possa davvero servire: uno sniffer per controllare lo scambio dei pacchetti in rete (eg. Ethereal), ...

Anche se la configurazione di MySQL e' abbastanza semplice e vi sono diversi comandi per controllarne lo stato (eg. SHOW VARIABLES, SHOW STATUS LIKE) il tuning di una base dati MySQL richiede esperienza e tempo. Da questo punto di vista vi sono diverse utility che vengono in aiuto al DBA (nel mio personale ordine di preferenza): phpMyAdmin (eg. Advisor), lo script Perl MySQLTuner, i Percona Tools for MySQL, lo script shell mysql-tuning-primer, lo script Perl mysqlreport, ... da ultimo my2 Collector.

Un genio dei database [NdA immodestamente il sottoscritto] mantiene una serie di script SQL molto utili per fotografare la situazione di un database MySQL; gli script sono costantemente aggiornati alle versioni piu' recenti [NdE scrivere script SQL e' quello che come fanno tutti i DBA... non c'e' ragione di credersi un genio ;]

Per ultimo, ma non da ultimo, la documentazione su web e' fondamentale. Da questo punto di vista quanto MySQL fornisce e' completo e facilmente consultabile.

Abbracadabbra

Firewall
La configurazione delle regole Firewall per MySQL e' molto semplice: dai client ci si collega sulla porta definita (default 3306). A volte pero' non basta... Per eccesso di zelo (ma e' utile per evitare alcuni tipi di attacchi) alcuni firewall mantengono una cache delle sessioni aperte, purtroppo per un periodo di tempo limitato. MySQL non ha un parametro di keepalive come quello di Oracle sull'SQL*Net... Per evitare il problema si puo' pero' agire a livello di sistema operativo (eg. echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time su un Linux 2.6).
Altra possibilita' e' quella di utilizzare un tunnel SSH:

Reset della password di root
Dimenticare la password di root impedisce di collegarsi alla base dati con privilegi di amministrazione. Che fare? Ci sono due alternative che richiedono entrambe l'accesso al server che ospita la base dati ed il riavvio del servizio:

• Script di avvio
  Con il parametro --init-file =/tmp/reset.sql di mysqld e' possibile avviare la base dati eseguendo uno script all'avvio. Lo script deve contenere:

  UPDATE mysql.user SET password=PASSWORD('xyz') WHERE User='root'; FLUSH PRIVILEGES;
  

• Skip grant
  Con il parametro --skip-grant-tables di mysqld o l'impostazione skip-grant-tables=1 in my.cnf si esclude ogni controllo di sicurezza! Va utilizzato lo stretto necessario per correggere la situazione con una UPDATE mysql.user SET password ... e quindi riavviare il Server MySQL togliendo il parametro di skip.

Gateway Oracle-MySQL
E' possibile configurare Oracle in modo da far accedere a tabelle MySQL via DBLINK. Lo strumento utilizzato e' Oracle Heterogeneous Services che si puo' connettere a MySQL con un driver ODBC. Per la configurazione si deve seguire la documentazione Oracle (per chi ha accesso a Metalink e' utile la nota 260981.1) e quella per la configurazione del driver (eg. Easysoft) senza dimenticare di: creare il catalogo HS con lo script .../rdbms/admin/caths.sql, impostare nel file sqlnet.ora il parametro sqlnet.authentication_services=(NONE), copiare il file hs init con nome initSID.ora e porlo nella directory $ORACLE_HOME/hs/admin. Non tutti i datatype e solo pochi statement SQL proprietari sono supportati... la documentazione riporta (nelle note scritte in piccolo) tutti i dettagli e come aggirare alcuni problemi. Occhio anche alle librerie: a seconda delle versioni e delle piattaforme sono da utilizzare path differenti (eg. 32bit). In qualche caso e' anche necessario effettuare un relink dei client (eg. sqlplus).
Gateway MySQL-Oracle? Mettetevi l'animo in pace, non esiste ancora!

MySQL Proxy
Con MySQL Proxy e' possibile effettuare logging, SQL injection, filtering, ... Tuttavia di default e' in ascolto sulla porta 4040 mentre il database MySQL e' in ascolto sulla porta 3306.
Ora supponiamo di dover attivare il proxy per un'emergenza su un sistema di produzione senza poter modificare i client e neppure interromperli... Come fare? Cosi':

Ritorno al futuro
In qualche grave caso di corruzione dati e' possibile ottenere l'errore InnoDB: is in the future!. Naturalmente non si puo' fare nulla: il log sequence number (LSN) corrente e' inferiore a quanto presente nei blocchi dati e bisogna effettuare un restore.
Ma se il recupero e' urgente e si puo' rischiare di corrompere i dati si puo' provare a ritornare al futuro con:

Replica MySQL
La replica su MySQL e' molto robusta poiche' basata sul semplice concetto di replica degli statement. I casi d'errore sono limitati ed i principali workaround sono semplici (e.g. FAQ Amministrazione MySQL Replication). Se si hanno altri errori da quelli previsti tipicamente e' necessario ricostruire lo slave partendo da un backup. Ma se l'errore e' dovuto ad un binlog corrotto o perso, rischiando di perdere (o ripetere) qualche transazione, e' possibile recuperare velocemente puntando all'inizio del binlog successivo:

Titolo: MySQL Tips and Tricks
Livello: Esperto (4/5)
Data: 25 Dicembre 2007
Versione: 1.0.24 - 14 Febbraio 2020
Autore: mail [AT] meo.bogliolo.name