rigacci.org

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Configurazione e uso MySQL ======

  * **[[http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL|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:

<code>
mysqladmin -u root password SuperSecret
</code>

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

<code>
mysqladmin -u root -p create databasename
mysqladmin -u root -p drop databasename
</code>

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:

<file>
[mysqld]
bind-address = 0.0.0.0
</file>

===== Speciale Debian =====

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**''.









===== Comandi utili =====

Lancia l'interprete SQL (MySQL client):
<code>$ mysql -u root -p</code>

Uscire dalla sessione SQL:
<code>EXIT</code>

Visualizza i **database** esistenti, le **tabelle** del database corrente, le **colonne** e gli **indici** di una tabella:
<code sql>
SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM tablename;
SHOW INDEX FROM tablename;
</code>

Inizia ad usare un particolare database:
<code>CONNECT dbname;</code>

Visualizza gli utenti database esistenti:
<code>
CONNECT mysql;
SELECT * FROM user;
</code>

Crea un nuovo database con encoding UTF-8 oppure ISO-8859-1:
<code sql>
CREATE DATABASE dbname DEFAULT CHARACTER SET 'utf8';
CREATE DATABASE dbname DEFAULT CHARACTER SET 'latin1';
</code>

**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:

<code sql>
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
</code>

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 **''%''**:

<code sql>
CREATE USER 'dbuser'@'hostname' IDENTIFIED BY 'secret_password';
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'another_password';
</code>

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:

<code sql>
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;
</code>

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:

<code sql>
SHOW GRANTS FOR 'username'@'localhost';
</code>

Altrimenti per vedere chi può accedere ad un database:

<code sql>
CONNECT mysql;
SELECT * FROM db WHERE Db = 'dbname';
</code>

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

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

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

<code sql>
DELETE FROM user WHERE user = '' AND host = 'localhost';
FLUSH PRIVILEGES
</code>

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**.

<code sql>
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;
</code>

Rimuove un utente:
<code sql>
CONNECT mysql;
DELETE FROM user WHERE User='dummy';
FLUSH PRIVILEGES;
</code>

Cambia password a utente database:
<code sql>
CONNECT mysql;
SET PASSWORD FOR root=PASSWORD('secret');
SET PASSWORD FOR dbuser@10.0.1.2=PASSWORD('secret');
</code>

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**:

<code>
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... |                       |                       |
+-----------+-----------+----------------+-----------------------+-----------------------+
</code>

Da riga di comandi esegue il dump completo di un database su un file di testo:
<code>
$ mysqldump --host=localhost --user=dbuser --password --add-drop-table dbname > dbname.dmp
</code>

Lancia l'interprete MySQL ed esegue il restore del dump:
<code>
$ mysql --host=localhost --user=dbuser --password dbname < dbname.dmp
</code>

Dal **prompt mysql** esegue uno **script SQL** contenuto in un file:
<code>
mysql> \. /path/to/file.sql
</code>


===== Restore selettivo di un database =====

Se si ha un dump generato con **%%mysqldump --all-databases%%** potrebbe essere necessario fare il restore selettivo di un solo database. Una ricetta che si trova diffusamente in rete, ma che è davvero poco efficiente, consiste nel filtrare l'intero dump con il comando **sed** intercettando nelle istruzioni SQL l'inizio e la fine del database.

Questo comando estrae dal dump compresso il singolo database e lo scrive in un dump SQL non compresso:

<code bash>
zcat mysql-dump.sql.gz \
    | sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' \
    > dbname-dump.sql
</code>

===== Visualizzare gli errori =====

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

<code>
mysql> \W
</code>

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:

<code>
mysqlbinlog /var/log/mysql/mysql-bin.000421 | less
</code>

Un caso di errore in una tabella, corretto:

<code sql>
SELECT * FROM stat_systems;
-- Got error 127 from table handler
REPAIR TABLE stat_systems;
</code>

===== Spostare la datadir =====

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

<code>
datadir = /var/lib/mysql
</code>

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:

<code>
/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
</code>


===== Recuperare password root dimenticata =====

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

  - Take down the mysqld server. You must be either the Unix root user or the same user mysqld runs as to do this.
  - Restart mysqld with the ''**%%--skip-grant-tables%%**'' option (or add it into ''**/etc/mysql/my.cnf**'', section **''%%[mysqld]%%''**).
  - Set a new password within the SQL shell:<code>
mysql> CONNECT mysql;
mysql> UPDATE user SET Password=PASSWORD('mynewpasswd') WHERE User='root';
mysql> FLUSH PRIVILEGES;</code>
  - Now you can stop mysqld and restart it normally.

===== Connessione senza digitare la password =====

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:

<file>
[client]
password=MySecret
</file>

===== Accesso root senza password =====

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**:

<code>
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 |             |
</code>

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

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

===== Log delle query =====

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

<file>
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
</file>

È possibile anche attivarlo da console:

<code sql>
SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';
SET GLOBAL general_log = 1;
</code>

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:

<code sql>
SHOW GLOBAL VARIABLES LIKE 'general_log_file';
</code>

===== Riparazione di tabelle corrotte =====

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

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

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:

<code sql>
CONNECT mysql
CHECK TABLE help_topic;
</code>
<code>
+------------------+-------+----------+---------------------------------------------------------------+
| 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                                                       |
+------------------+-------+----------+---------------------------------------------------------------+
</code>

Dal prompt della shell si può tentare la riparazione:

<code>
mysqlrepair mysql help_topic
</code>

===== Event Scheduler were found damaged =====

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

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

In effetti dal prompt SQL si riscontrava lo stesso problema:

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

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):

<code>
mysqlcheck --all-databases --check-upgrade --auto-repair
mysql_upgrade
systemctl restart mariadb.service
</code>
===== Utenti e privilegi =====

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.

===== Encoding del database e delle tabelle =====

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:

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

+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4                    |
+----------------------------+
</code>

<code>
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             |
+--------------------+
</code>

===== Errore "Tablespace is missing for a table" =====

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 **[[https://medium.com/@badalnaik/mariadb-mysql-restore-database-from-frm-and-ibd-files-6ea95269fba2|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**.