Log delle query di MySQL e recupero dati istantaneo

E' possibile far sė che MySQL tenga traccia di tutte le query di comando eseguite su ogni database. Accoppiando questa feature con un meccanismo di backup, č possibile ripristinare un database danneggiato per riportarlo al momento presente.

Innanzitutto si prevede di effettuare un backup periodico dei databases; questo può essere eseguito da cron, garantendo così il salvataggio dei dati almeno fino all'ultimo backup.
Quando viene installato di default, MySQL prevede di eseguire un log esclusivamente delle fasi di avvio e fermata, e di eventuali errori del server. In genere, questi log vengono salvati in /var/lib/mysql/mysqld.log.
Una volta installato MySQL, si modifichi il file /etc/my.cnf come segue:
[mysqld]
datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
    log-update=/var/log/mysql/queries
log-long-format
...

[mysql.server]
  ...  
  
[safe_mysqld]
  err-log=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
  ...
Sono evidenziate nello script le parti da modificare. Come si vede, si è preferito far scrivere i log di MySQL su una cartella a parte, /var/log/mysql, un po' per analogia con altri server "importanti", come Samba o Apache, ma anche per il numero di logs che MySQL creerà, come vedremo tra poco.
Occorre ora creare la cartella e darle i permessi necessari:
mkdir /var/log/mysql
  chown mysql:mysql /var/log/mysql
  chmod 775 /var/log/mysql
Poichè abbiamo spostato la directory dei logs, dovremo cambiare anche il file che MySQL crea per la rotazione dei suoi log standard; la sua posizione dipende dalla distribuzione. Se MySQL è stato installato da pacchetti RPM, si troverà in /etc/logrotate.d/mysql. Basterà cambiare la riga che indica la posizione del file di log, da
  ...
/var/lib/mysql/mysqld.log {
    ....
a
...
/var/log/mysql/mysqld.log {
   ....
(N.B. magari, se avete impostato una password per l'utente root di MySQL, modificate anche la riga più oltre che esegue lo scarico dei logs, inserendovi anche la password:
  if test -n "`ps acx|grep mysqld`"; then
      /usr/bin/mysqladmin --password=pwd_di_root flush-logs
fi
)
Ora provate a lanciare il server MySQL (o a fermalo e riavviarlo, se già attivo) ed eseguire alcune query di comando (INSERT, DELETE, UPDATE...) sui databases. Nella cartella /var/log/mysql si creerà un file queries.001 con un contenuto di questo genere:
  # /usr/sbin/mysqld, Version: 4.0.15-standard-log at 040301 14:17:16
  # Time: 040301 18:38:28
  # User@Host: pippo[pippo] @ localhost []
  USE dbProva;
  UPDATE Download SET IDuser=149, Descrizione="Inserimento automatico dati ", Updated="04-2-20" WHERE IDDown=7;
  # Time: 040301 18:50:59
  # User@Host: root[root] @ localhost []
  ALTER TABLE Download ADD vrs CHAR(10);
  # Time: 040301 19:02:13
  # User@Host: pippo[pippo] @ localhost []
  UPDATE Download SET IDuser=149, Descrizione="Aggiornamento database", Updated="04-2-28", vrs="1.0.5" WHERE IDDown=8;
  # Time: 040301 19:02:43
  # User@Host: rt[rt] @ localhost []
  DELETE FROM Download WHERE IDDown=7;
  .....
Ecco quindi il meccanismo di recupero fino al tempo presente: da un backup recuperate la situazione dei databases fino ad un certo momento; quindi fate eseguire a MySQL queste query per aggiungere i comandi che riportano il database fino all'ultima modifica eseguita. Per questo è  sufficiente
mysql -p < /var/log/queries.00n
dove n è il numero del log di query da eseguire.
  E' opportuno sincronizzare da cron i backups e l'aggiornamento dei log: questo può essere fatto con uno script che:  
1. Arresti il server MySQL (per impedire modifiche dagli utenti durante il backup);  
2. Esegua il backup dei dati;  
3. Rilanci il server MySQL.
In tal modo l'ultimo backup, seguito dall'esecuzione dell'ultimo log delle query, ripristinerà integralmente i databases.
C'è un'ultima cosa: ad ogni riavvio, MySQL crea un file di log queries.00n con n numero progressivo. Se non si vuole mantenere traccia di tutti questi logs (e si vuole evitare l'affollamento del disco), è bene creare una rotazione di questi. Farlo con logrotate è un po' un problema, perchè la numerazione progressiva di logrotate rischia di creare conflitto con quella di MySQL. Si può allora fare uno script in bash di questo tipo:
file /usr/bin/mysqlrotate:
  #!/bin/bash
  MYPATH="/var/log/mysql/queries"
  MAX=9
  if [ -e $MYPATH.00$MAX ]
 then
    rm $MYPATH.001
    N=2
    while [ $N -le $MAX ]
     do
       M=`expr $N - 1`
       mv $MYPATH.00$N $MYPATH.00$M
       N=`expr $N + 1`
     done
 fi
Questo script mantiene fino a 9 logs successivi; basta modificare la variabile MAX per averne di pių. Poichè MySQL crea un nuovo log ad ogni riavvio, può essere opportuno inserirlo direttamente nello script di avvio di MySQL stesso, in genere /etc/init.d/mysql; basterà cercare il punto del case di start, ed aggiungere prima di ogni altra cosa la chiamata allo script di rotazione dei log:
....
  case "$mode" in
   'start')
# Start daemon
     /usr/bin/mysqlrotate
     if test -x $bindir/mysqld_safe
       then
  ...

Privacy Policy