Differences

This shows you the differences between two versions of the page.

--- doc:appunti:linux:sa:mysql [2013/08/15 08:41] – [Comandi utili] niccolo
+++ doc:appunti:linux:sa:mysql [2023/03/27 11:31] (current) – [Log delle query] niccolo
@@ Line 18: / Line 18: @@
 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 =====
@@ Line 51: / Line 58: @@
 Inizia ad usare un particolare database:
 <code>CONNECT dbname;</code>
 Visualizza gli utenti database esistenti:
@@ Line 60: / Line 66: @@
 Crea un nuovo database con encoding UTF-8 oppure ISO-8859-1:
-<code>
+<code sql>
 CREATE DATABASE dbname DEFAULT CHARACTER SET 'utf8';
 CREATE DATABASE dbname DEFAULT CHARACTER SET 'latin1';
@@ Line 74: / Line 80: @@
 </code>
-Crea un utente con password e assegna privilegi su un 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 **''%''**:
-<code>
+<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 dbuser@localhost IDENTIFIED BY 'some_passwd';
+GRANT ALL ON dbname.* TO 'dbuser1'@'localhost' IDENTIFIED BY 'some_passwd';
-GRANT ALL ON dbname.* TO oratio@'%' IDENTIFIED BY 'another_passowrd';
+GRANT ALL ON dbname.* TO 'dbuser2'@'%' IDENTIFIED BY 'another_passowrd';
+GRANT ALL ON dbname.* TO 'some_user'@'some_host';
 FLUSH PRIVILEGES;
 </code>
@@ Line 84: / Line 101: @@
 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 (**%**).
-Attenzione, in Debian Wheezy viene creato un **utente anonimo**, ma senza password, che impedisce le connessioni via TCP:
+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>
@@ Line 96: / Line 126: @@
 DELETE FROM user WHERE user = '' AND host = 'localhost';
 FLUSH PRIVILEGES
-<code>
+</code>
-È possibile anche manipolare direttamente la tabella interna degli utenti:
+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>
+<code sql>
 CONNECT mysql;
 INSERT INTO user VALUES ('hostname', 'username',
@@ Line 108: / Line 138: @@
 Rimuove un utente:
-<code>
+<code sql>
 CONNECT mysql;
 DELETE FROM user WHERE User='dummy';
@@ Line 115: / Line 145: @@
 Cambia password a utente database:
-<code>
+<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>
@@ Line 204: / Line 247: @@
 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 =====
@@ Line 213: / Line 282: @@
 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**.