Configurazione e uso MySQL

• MySQL vs PostgreSQL
• MySQL replica master-master

L'installazione predefinita di Debian (Lenny) consente l'accesso al database come utente root senza password! Per impostare una password dell'utente database root si usa:

mysqladmin -u root password SuperSecret

I database presenti sono contenuti in /var/lib/mysql/ (una directory per ogni db). Per creare e rimuovere database si usa:

mysqladmin -u root -p create databasename
mysqladmin -u root -p drop databasename

Il server MySQL sta in ascolto sulla porta TCP 3306, nell'installazione standard Debian (Lenny) è in ascolto solo su localhost, per collegarlo anche agli altri indirizzi IP bisogna commentare la riga di bind-address contenuta in /etc/mysql/my.cnf.

Con Debian più recenti, ad esempio Debian 11 Bullseye, è installato il motore MariaDB ed è possibile utilizzare uno snippet di configurazione a parte, ad esempio creando il file /etc/mysql/mariadb.conf.d/99-local.cnf con:

[mysqld]
bind-address = 0.0.0.0

L'installazione di default Debian prevede che il demone ascolti solo su Unix socket, per accettare anche connessioni via TCP/IP si deve commentare skip-networking in /etc/mysql/my.cnf.

Alcune operazioni di manutenzione (mysqladmin, mysqlcheck) sul database vengono fatte con opportune credenziali generate al momento dell'installazione del pacchetto e salvate in /etc/mysql/debian.cnf.

Lancia l'interprete SQL (MySQL client):

$ mysql -u root -p

Uscire dalla sessione SQL:

EXIT

Visualizza i database esistenti, le tabelle del database corrente, le colonne e gli indici di una tabella:

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM tablename;
SHOW INDEX FROM tablename;

Inizia ad usare un particolare database:

CONNECT dbname;

Visualizza gli utenti database esistenti:

CONNECT mysql;
SELECT * FROM user;

Crea un nuovo database con encoding UTF-8 oppure ISO-8859-1:

CREATE DATABASE dbname DEFAULT CHARACTER SET 'utf8';
CREATE DATABASE dbname DEFAULT CHARACTER SET 'latin1';

ATTENZIONE Ancora nella versione 5.0.51a MySQL usa come charset predefinito il Latin-1

Per vedere character set e collation di un database, connettersi ad esso e:

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

Creazione di un utente con password. Attenzione che per il controllo di accesso viene fatto un match sia sul nome utente che sull'host di provenienza, per il nome host eventualmente usare il carattere jolly %:

CREATE USER 'dbuser'@'hostname' IDENTIFIED BY 'secret_password';
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'another_password';

Anche il controllo di accesso viene fatto sull'accoppiata nome utente e host.

In una sola istruzione si può creare un utente con password e assegnare privilegi su un database:

CONNECT mysql;
GRANT ALL ON dbname.* TO 'dbuser1'@'localhost' IDENTIFIED BY 'some_passwd';
GRANT ALL ON dbname.* TO 'dbuser2'@'%' IDENTIFIED BY 'another_passowrd';
GRANT ALL ON dbname.* TO 'some_user'@'some_host';
FLUSH PRIVILEGES;

La prima GRANT crea un utente con accesso solo da localhost e password some_password con tutti i privilegi, la seconda invece non pone restrizioni sull'host di provenienza (%).

Per vedere i grant concessi ad un utente:

SHOW GRANTS FOR 'username'@'localhost';

Altrimenti per vedere chi può accedere ad un database:

CONNECT mysql;
SELECT * FROM db WHERE Db = 'dbname';

ATTENZIONE, in Debian Wheezy viene creato un utente anonimo, ma senza password, che impedisce le connessioni via TCP:

mysql -u dbuser -h localhost --password=secret --protocol=TCP dbname
ERROR 1045 (28000): Access denied for user 'dbuser'@'localhost' (using password: YES)

Per consentire la connessione via TCP/IP è necessario rimuovere tale utente:

DELETE FROM USER WHERE USER = '' AND host = 'localhost';
FLUSH PRIVILEGES

Sarebbe possibile anche manipolare direttamente la tabella interna degli utenti, ma è opportuno controllare la struttura della tabella prima di procedere! Infatti - ad esempuio - la tabella user ha una struttura differente in MariaDB 10.

CONNECT mysql;
INSERT INTO USER VALUES ('hostname', 'username',
    PASSWORD('secret'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
FLUSH PRIVILEGES;

Rimuove un utente:

CONNECT mysql;
DELETE FROM USER WHERE USER='dummy';
FLUSH PRIVILEGES;

Cambia password a utente database:

CONNECT mysql;
SET PASSWORD FOR root=PASSWORD('secret');
SET PASSWORD FOR dbuser@10.0.1.2=PASSWORD('secret');

La password è memorizzata storicamente nel campo Password della tabella user, ma versioni più recenti del motore MySQL (ad esempio MariaDB 10) possono usare plugin aggiuntivi e le informazioni staranno nei campi plugin e authentication_string:

SELECT Host, User, Password, plugin, authentication_string FROM user;
+-----------+-----------+----------------+-----------------------+-----------------------+
| Host      | User      | Password       | plugin                | authentication_string |
+-----------+-----------+----------------+-----------------------+-----------------------+
| localhost | root      | *CAE6919BF3... |                       |                       |
| localhost | user1     |                | mysql_native_password | *1472E83A1E...        |
| localhost | user2     | *B4C990D89F... |                       |                       |
+-----------+-----------+----------------+-----------------------+-----------------------+

Da riga di comandi esegue il dump completo di un database su un file di testo:

$ mysqldump --host=localhost --user=dbuser --password --add-drop-table dbname > dbname.dmp

Lancia l'interprete MySQL ed esegue il restore del dump:

$ mysql --host=localhost --user=dbuser --password dbname < dbname.dmp

Dal prompt mysql esegue uno script SQL contenuto in un file:

mysql> \. /path/to/file.sql

Dentro la shell mysql si può abilitare la visualizzazione dei warning generati dalle query (ad esempio durante delle insert da file SQL):

mysql> \W

Altrimenti i file di log di MySQL contengono solo gli errori generati dal server. I file sono in formato binario, e sono suddivisi (probabilmente in base a una dimensione max?). Per vederne il contenuto:

mysqlbinlog /var/log/mysql/mysql-bin.000421 | less

Un caso di errore in una tabella, corretto:

SELECT * FROM stat_systems;
-- Got error 127 from table handler
REPAIR TABLE stat_systems;

Nel file di configurazione /etc/mysql/my.cnf si modifica la riga

datadir = /var/lib/mysql

Conviene lasciare la vecchia datadir /var/lib/mysql vuota, senza trasformarla in un link simbolico alla nuova. Questo perché, almeno nell'installazione Debian di MySQL 4.1.14-6, viene comunque creato il link simbolico /var/lib/mysql/DATADIR.link che punta all'effettiva datadir. Questo nel caso del link confonderebbe il comando mysqlcheck eseguito ad ogni reboot, che invia una mail di falso allarme:

/usr/bin/mysqlcheck: Got error: 1102: Incorrect database name 'DATADIR.link' when selecting the database

 Improperly closed tables are also reported if clients are accessing
 the tables *now*. A list of current connections is below.

+----+------------------+-----------+----+---------+------+-------+------------------+
| Id | User             | Host      | db | Command | Time | State | Info             |
+----+------------------+-----------+----+---------+------+-------+------------------+
| 5  | debian-sys-maint | localhost |    | Query   | 0    |       | show processlist |
+----+------------------+-----------+----+---------+------+-------+------------------+
Uptime: 2  Threads: 1  Questions: 9  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 0  Queries per second avg: 4.500

If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

1. Take down the mysqld server. You must be either the Unix root user or the same user mysqld runs as to do this.
2. Restart mysqld with the --skip-grant-tables option (or add it into /etc/mysql/my.cnf, section [mysqld]).
3. Set a new password within the SQL shell:

   mysql> CONNECT mysql;
   mysql> UPDATE user SET Password=PASSWORD('mynewpasswd') WHERE User='root';
   mysql> FLUSH PRIVILEGES;

4. Now you can stop mysqld and restart it normally.

Può essere comodo collegarsi al database senza dover digitare la password (ad esempio per operazioni automatiche di backup). Un sistema accettabile è quello di salvare nel file $HOME/.my.cnf la password necessaria, in chiaro:

[client]
password=MySecret

Debian 9 Stretch installa il motore MariaDB 10.1.37 con una speciale configurazione, per cui l'utente Unix root può connettersi al database senza digitare una password.

Con questa query si verifica che l'utente root non ha una password, ma ha attivo il plugin unix_socket:

MariaDB [(none)]> USE mysql;
MariaDB [mysql]> SELECT user, host, password, plugin FROM user;                    
+--------------+-----------+-------------------------------------------+-------------+
| user         | host      | password                                  | plugin      |
+--------------+-----------+-------------------------------------------+-------------+
| root         | localhost |                                           | unix_socket |
| oneuser      | localhost | *45E0F461E35A27C4D8A76967B2B7BB57C839ADE1 |             |
| anotheruser  | localhost | *BB3DD405A9249EF5FBB1823E2D89F996F73CC9BD |             |

Per ripristinare il funzionamento con richiesta di password è necessario impostarla e rimuovere il plugin:

USE mysql;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
UPDATE user SET plugin='' WHERE User = 'root';
FLUSH PRIVILEGES;

Per avere il log di tutte le query eseguite sul database si attiva in /etc/mysql/my.cnf:

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

È possibile anche attivarlo da console:

SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';
SET GLOBAL general_log = 1;

Abilitare il logging solo per lo stretto necessario, per evitare consumo di risorse. Impostare general_log = 0 per fermare il logging.

Per vedere le impostazini correnti:

SHOW GLOBAL VARIABLES LIKE 'general_log_file';

Può capitare che una tabella risulti corrotta (crash della macchina?), di solito si scopre durante il dump a causa di un errore del tipo:

mysqldump: Error 1194: Table 'help_topic' is marked as crashed and
  should be repaired when dumping table `help_topic` at row: 510

Si deve capire qual'è il database in questione (nell'esempio si tratta del database mysql) e dal prompt SQL si verifica che effettivamente ci sia un errore:

CONNECT mysql
CHECK TABLE help_topic;

+------------------+-------+----------+---------------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                      |
+------------------+-------+----------+---------------------------------------------------------------+
| mysql.help_topic | check | warning  | Size of indexfile is: 19456      Should be: 17408             |
| mysql.help_topic | check | warning  | Size of datafile is: 491820       Should be: 490640           |
| mysql.help_topic | check | error    | Found key at page 7168 that points to record outside datafile |
| mysql.help_topic | check | error    | Corrupt                                                       |
+------------------+-------+----------+---------------------------------------------------------------+

Dal prompt della shell si può tentare la riparazione:

mysqlrepair mysql help_topic

È capitato un caso in cui un mysqldump generava il seguente errore (MariaDB 5.5.64 su CentOS 7.7):

mysqldump: Couldn't execute 'show events':
    Cannot proceed because system tables used by Event
    Scheduler were found damaged at server start

In effetti dal prompt SQL si riscontrava lo stesso problema:

CONNECT mysql;
SHOW EVENTS;
ERROR 1577 (HY000): Cannot proceed because system tables used
    by Event Scheduler were found damaged at server start

Dalla shell Unix sono stati eseguiti i seguenti comandi che hanno risolto il problema (non si sa se sono tutti e tre necessari, ma fino all'esecuzione del terzo il problema non era risolto):

mysqlcheck --all-databases --check-upgrade --auto-repair
mysql_upgrade
systemctl restart mariadb.service

Queste sono le tabelle mysql che contengono informazioni circa i GRANT:

• user User accounts, global privileges, and other non-privilege columns.
• db Database-level privileges.
• tables_priv Table-level privileges.
• columns_priv Column-level privileges.
• procs_priv Stored procedure and function privileges.

Pare che ancora nel 2020 MySQL (MariaDB 10.3) crei le tabelle con encoding Latin1. Ecco come verificare l'encoding del database e di una tabella:

SELECT default_character_set_name FROM information_schema.SCHEMATA
    WHERE schema_name = 'database_name';

+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4                    |
+----------------------------+

SELECT CCSA.character_set_name FROM information_schema.TABLES T,
    information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
    WHERE CCSA.collation_name = T.table_collation AND T.table_schema = 'database_name'
    AND T.table_name = 'table_name';

+--------------------+
| character_set_name |
+--------------------+
| latin1             |
+--------------------+

Può capitare con l'engine InnoDB che il file contenente una tabella sparisca (errore sul filesystem, mancato restore, ecc.). In tal caso nella directory /var/lib/mysql/dbname/ si può trovare il file tablename.frm ma manca il relativo tablename.idb.

Ovviamente i dati contenuti nella tabella sono persi, ma dovrebbe essere possibile ricostruire la struttura dal file frm. Nella pagina MariaDB/MySQL — Restore Database From .frm And .ibd Files c'è una ricetta che però richiede il tool mysqlfrm. Si tratta di uno script Python che veniva distribuito con il pacchetto mysql-utilities ma solo nella vecchia Debian 9 Stretch.