Mysql / MariaDB

Note varie su mysql e dintorni

Questo script può aiutare: https://github.com/major/MySQLTuner-perl

Uno strumento semplice di backup per mysql e` "automysqlbackup", il cui pacchetto e` disponibile in Debian. Dopo averlo installato, modificare /etc/default/automysqlbackup per adattarlo alle proprie esigenze. La configurazione di default salva tutti i database una volta al giorno, tenendo anche una copia alla settimana e una al mese. L'unica cosa da cambiare e` il percorso di salvataggio che e` definito con il parametro "BACKUPDIR", nel caso in cui quello di default non vada bene.

MariaDB ha adottato per l'utente "root" una autenticazione "alla postgres" che non prevede una password ma richiedere di essere l'utente "root" in console.

Per creare un utente "amministratore" accessibile con il classico metodo di username e password, usabile per esempio da phpmyadmin, possiamo usare questi comandi:

• Dal prompt di root, entriamo in mysql

  mysql

• Dentro mysql creiamo un utente che possa connettersi da localhost usando una password e abbia tutti i privilegi:

  CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password-di-admin';
  GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
  flush privileges;
  \q

• Ora vediamo se funziona: (inserire la password quando richiesta)

   mysql -u admin -p

Per creare un account utente e un database ad esso associato (come fa per default phpmyadmin):

• Dal prompt di root, entriamo in mysql

  mysql mysql

• Dentro mysql creiamo un database (occhio al nome, niente spazi o "-", solo underscore), creiamo un utente e contestualmente gli diamo i permessi sul db (occhio al fatto che nome_database è seguito da punto asterisco nella grant)

  CREATE DATABASE nome_database;
  CREATE USER 'nome-utente'@'localhost' IDENTIFIED BY 'password-dell-utente';
  GRANT ALL PRIVILEGES ON nome_database.* TO 'nome-utente'@'localhost';
  flush privileges;
  \q

• Ora vediamo se funziona: (inserire la password quando richiesta)

   mysql -u nome-utente -p  nome_database

• Per fare un backup:

  mysqldump databasename  > filedidump.sql

• Per fare un restore: (occorre avere già creato il database)

  mysql -u USERNAME -p DATABASENAME < filedidump.sql

Il problema si crea tipicamente con Drupal che crea mostri nel databse.

Se mysqldump esce con un errore come questo mysqldump: Error 2013: Lost connection to server during query when dumping table `batch` at row: 4 mentre fa il dump, si puo` tentare cosi`:

mysqldump --max-allowed-packet=1024M databasename  > filedidump.sql

Se reimportando un dump che ha record enormi si incontrano errori, si può tentare impostando nella config (in mariadb è in /etc/mysql/mariadb.conf.d/50-server.cnf) questa riga:

max_allowed_packet=1G

Se si fanno continue modifiche sostanziali ai dati del db, i binary log (usati dalla replica e anche per recovery) possono diventare enormi, molto più grandi del database stesso. Nelle versioni recenti di Mysql il default per la conservazione è di 30 giorni, e questo può creare problemi di spazio disco notevoli. Se non ci servono (niente replica, per dire) possiamo dire a Mysql di tenerne solo 3 giorni o anche meno. Per farlo dobbiamo modificare la configurazione (sotto la sezione [mysqld]) impostando un valore più ridotto di tempo, così:

[mysqld] 
binlog_expire_logs_seconds = 259200 

Riavviando mysql lui automaticamente ripulisce la roba vecchia e quindi libera disco, e da ora in avanti limita a 3 giorni la conservazione.

Volendo possiamo vedere l'elenco dei log usati e cancellarli (una tantum pero`, poi tornano a crescere) anche "live" senza riavviare nulla, da dentro la console di mysql, in questo modo:

mysql> SHOW BINARY LOGS;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000054 | 1073742466 | No        |
| binlog.000055 | 1075367661 | No        |
| binlog.000056 |  338137146 | No        |
| binlog.000057 |   70352785 | No        |
| binlog.000058 |    6424784 | No        |
| binlog.000059 | 1077293478 | No        |
| binlog.000060 |  816381276 | No        |
| binlog.000061 |       7499 | No        |
+---------------+------------+-----------+
8 rows in set (0.00 sec)

Possiamo quindi cancellarli fino ad un certo punto indicando il nome del file (preso dall'elenco di prima) fino al quale vogliamo cancellare i log, così:

mysql> PURGE BINARY LOGS TO 'binlog.000060'; 

Qui si spiega come configurare due macchine come master e slave, partendo dalla supposizione che il database master sia vuoto. Se non lo e`, occorre sincronizzare master e slave prima di avviare la replicazione.

Sul server "master", in my.cnf, occorre:

• togliere il commento dalle righe che riguardano il server-id e log_bin (sono una di fianco all'altra). Il server-id puo` essere qualsiasi numero che non sia duplicato negli slave e non sia zero.
• consentire la connessione a mysql via lan, quindi in Debian commentare la riga che dice bind-address = 127.0.0.1.
• impostare il campo binlog_ignore_db o il campo binlog_do_db in modo da replicare solo i database che ci servono (e da non replicare quelli che farebbero danni se fosser replicati). Una impostazione decente per replicare tutto potrebbe essere:

binlog_ignore_db        = information_schema,mysql,phpmyadmin

Sul server slave (o sui server slave) occorre:

• togliere il commento dalla riga che riguarda il server-id. Il server-id puo` essere qualsiasi numero che non sia duplicato nell'insieme master ed eventuali altri slave e non sia zero.
• Facoltativamente (ma e` consigliato per il recovery da crash o corruzioni) scommentare la riga del log_bin (come sul master).
• consentire la connessione a mysql via lan, quindi in Debian commentare la riga che dice bind-address = 127.0.0.1.
• IMPORTANTE: cambiare percorso alla tmpdir, impostandola a un path dove un riavvio della macchina NON cancelli i dati, quindi non su /tmp. Potrebbe per esempio essere messa a /var/spool/mysql (che va creata a chownata a mysql.)

Dopo aver riavviato i due mysql sul master e sullo slave, procedere SUL MASTER con la creazione dell'utente che verra` usato dagli slave per la replicazione (uno per tutti quanti o anche uno per ogni slave, a discrezione). Questo utente deve disporre del privilegio di replicazione e deve avere accesso ovviamente dall' ip della/delle macchina/e slave. Se usate phpmyadmin esiste una funzione apposta "replicazione" che indica lo stato della replicazione e consente di creare un utente per la replicazione. Di fatto phpmyadmin crea l'utente cosi`:

CREATE USER 'replicazione'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'replicazione'@'%' IDENTIFIED BY 'password';

A questo punto, procedere SULLO SLAVE a configurare il medesimo per l'accesso al master, si puo` sempre fare da phpmyadmin sullo slave alla voce "replicazione", inserendo uno username, una password e un ip per il server master (l'utente e` quello che abbiamo appena creato sul server master, appunto). Oppure si puo` fare a mano con il comando:

<code> mysql> CHANGE MASTER TO

1. > MASTER_HOST='master_host_name',
2. > MASTER_USER='replication_user_name',
3. > MASTER_PASSWORD='replication_password',
4. > MASTER_LOG_FILE='recorded_log_file_name',
5. > MASTER_LOG_POS=recorded_log_position;

}}}

Fatto questo, occorre riavviare mysql sullo slave e dovremmo essere connessi.