Configurazione e uso MySQL

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

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.

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.

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

Crea un utente con password e assegna privilegi su un database:

CONNECT mysql;
GRANT ALL ON dbname.* TO dbuser@localhost IDENTIFIED BY 'some_passwd';
GRANT ALL ON dbname.* TO oratio@'%' IDENTIFIED BY 'another_passowrd';
FLUSH PRIVILEGES;

La prima GRANT crea un utente con accesso da hostname, password, e tutti i privilegi, la seconda invece non pone restrizioni sull'host di provenienza (%).

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

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;

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

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.

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