User Tools

Site Tools


doc:appunti:linux:sa:mysql

This is an old revision of the document!


Configurazione e uso MySQL

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.

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

$ 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';
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 (%).

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

È possibile anche manipolare direttamente la tabella interna degli utenti:

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');

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

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

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;

Spostare la datadir

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

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:

  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.

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:

[client]
password=MySecret

Log delle query

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
doc/appunti/linux/sa/mysql.1383043442.txt.gz · Last modified: 2013/10/29 11:44 by niccolo