Differences

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

--- doc:appunti:linux:sa:mysql [2017/07/07 09:40] – [Log delle query] niccolo
+++ doc:appunti:linux:sa:mysql [2025/03/12 14:55] (current) – [Restore selettivo di un database] 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 94: / 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 (**%**).
-Per vedere chi può accedere ad un database:
+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>
@@ Line 115: / Line 128: @@
 </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 sql>
@@ Line 136: / Line 149: @@
 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 153: / Line 179: @@
 </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 =====
@@ Line 221: / Line 259: @@
 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 237: / Line 301: @@
 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**.