User Tools

Site Tools


doc:appunti:linux:sa:mysql

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
doc:appunti:linux:sa:mysql [2013/10/29 11:46] – [Comandi utili] niccolodoc: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''**. 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 ===== ===== Speciale Debian =====
Line 53: Line 60:
  
 Visualizza gli utenti database esistenti: Visualizza gli utenti database esistenti:
-<code sql>+<code>
 CONNECT mysql; CONNECT mysql;
 SELECT * FROM user; SELECT * FROM user;
Line 88: Line 95:
 GRANT ALL ON dbname.* TO 'dbuser1'@'localhost' IDENTIFIED BY 'some_passwd'; 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 'dbuser2'@'%' IDENTIFIED BY 'another_passowrd';
-GRANT ALL ON *.* TO 'some_user'@'some_host';+GRANT ALL ON dbname.* TO 'some_user'@'some_host';
 FLUSH PRIVILEGES; FLUSH PRIVILEGES;
 </code> </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 (**%**). 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: **ATTENZIONE**, in Debian Wheezy viene creato un **utente anonimo**, ma senza password, che impedisce le connessioni via TCP:
Line 108: Line 128:
 </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 sql> <code sql>
Line 129: Line 149:
 SET PASSWORD FOR root=PASSWORD('secret'); SET PASSWORD FOR root=PASSWORD('secret');
 SET PASSWORD FOR dbuser@10.0.1.2=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> </code>
  
Line 214: Line 247:
 password=MySecret password=MySecret
 </file> </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 ===== ===== Log delle query =====
Line 223: Line 282:
 general_log             = 1 general_log             = 1
 </file> </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**.
 +
doc/appunti/linux/sa/mysql.1383043601.txt.gz · Last modified: 2013/10/29 11:46 by niccolo