MySQL

Introduzione
Qui sono descritti i comandi principali che vengono usati regolarmente per gestire un database MySQL.

Connessione
Per connettersi da locale come utente privilegiato: mysql -u root -p -S /percorso/mysql.sock

dove -u Nome dell'utente -p Richiede di inserire la password -S Socket file

Per connettersi da remoto come utente non privilegiato: mysql -u nome_utente -p -h server.dominio.com -P porta nome_database

dove: -u Nome dell'utente -p Richiede di inserire la password -h DB server -P Porta su cui ascolta il DB

Per disconnettersi: mysql> quit

DataBase
Per vedere i database disponibili dopo essersi connessi: mysql> show databases ;

Per selezionare il database su cui operare: mysql> use nome_database ;

Per creare una nuova banca dati: mysql> create database nome_database ;

Per cancellarla: mysql> drop database nome_database ;

Tabelle
Per vedere le tabelle presenti nel database selezionato in precedenza: mysql> show tables ;

Per aggiornare le statistiche: mysql> analyze table nome_tabella ;

Per eseguire una riorganizzazione: mysql> optimize table nome_tabella ;

Per vedere lo stato delle tabelle: mysql> show table status [{FROM | IN} nome_database] [LIKE 'pattern' | WHERE expr] ;

Statement File
Per eseguire degli statement presenti in un file, dopo essersi connessi e selezionato un database: mysql> source /percorso/nome_file.sql ;

Index
Creazione index create index nome_indice on nome_tabella (nome_colonna);

Cancellazione index drop index nome_indice on nome_tabella;

Stored Procedures
Vedere una stored procedure show create procedure nome_stored_procedure;

Chiamare, eseguire una stored procedure CALL nome_stored_procedure('arg1','arg2');

Dump
Per eseguire un dump di una intera banca dati il comando da esequire e' molto simile a quello utilizzato per connettersi: mysqldump -u root -p -S socket_file nome_database > /percorso/nome_file.sql

Per eseguire un dump comprensivo di store procedures: mysqldump -u root -p -R -S socket_file nome_database > /percorso/nome_file.sql

Per eseguire un dump con drop table: mysqldump -u root -p -S socket_file --add-drop-table nome_database > /percorso/nome_file.sql

Per eseguire un dump senza dati (solo le ddl delle tabelle contenute): mysqldump -u root -p -R -S socket_file --no-data nome_database > /percorso/nome_file.sql

Per importare il dump creato nel passaggio precedente: mysql -u root -p -S socket_file nome_database < /percorso/nome_file.sql

Una volta lanciato il comando bisogna inserire la password, anche se non si vede il prompt che la richiede.

Utenti
Per creare un utente con pieni poteri su un intero database: mysql> GRANT ALL PRIVILEGES ON `nome_database`.* TO  ' nome_utente ' @ ' %.dominio.com '  IDENTIFIED BY  ' password_utente '  ; mysql> FLUSH PRIVILEGES ;

Per creare un utente che possa effetuare il backup (mysqldump) di un database: mysql> GRANT USAGE ON *.* TO  ' nome_utente ' @ ' localhost '  IDENTIFIED BY  ' password_utente '  ; mysql> GRANT SELECT, LOCK TABLES ON `mysql`.* TO  ' nome_utente ' @ ' localhost '  ; mysql> GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `nome_database`.* TO  ' nome_utente ' @ ' localhost '  ; mysql> FLUSH PRIVILEGES ;

Per visualizzare i permessi di un utente: mysql> show grants for  ' nome_utente ' @ ' %.dominio.com '  ;

Per visualizzare quali utenti possono accedere ad un determinato database: mysql> use mysql ; mysql> select Db, User from db where Db=nome_database ;

Per cambiare la password di un utente, utilizzando il plugin di autenticazione mysql_native_password: mysql> ALTER USER  ' nome_utente ' @ ' %.dominio.com '  IDENTIFIED WITH 'mysql_native_password' BY  ' nuova_password ' ;

Per listare tutti gli utenti presenti: mysql> select user, host from mysql.user ;

Per cancellare un utente: mysql> drop user  ' nome_utente ' @ ' %.dominio.com '  ;

Processi
Per listare i processi attivi: mysql> show FULL PROCESSLIST ; +---+--+--+--+-+--+---+---+ | Id   | User     | Host                     | db           | Command | Time | State | Info                  | +---+--+--+--+-+--+---+---+ | 47085 | utente  | server.dominio.com:30622 | nome_db      | Sleep   |    7 |       | NULL                  | | 47092 | root    | localhost                | NULL         | Query   |    0 | NULL  | show FULL PROCESSLIST | +---+--+--+--+-+--+---+---+ 2 rows in set (0.00 sec)

Per fermare un processo: mysql> KILL 47085;

Numero Massimo di Connessioni
Per listare il numero massimo di connessioni accettate: mysql> SHOW VARIABLES LIKE 'max_connections'; +-+---+ | Variable_name  | Value | +-+---+ | max_connections | 151  | +-+---+ 1 row in set (0.00 sec)

Per listare il numero massimo di connessioni utilizzate: mysql> SHOW STATUS LIKE 'max_used_connections'; +--+---+ | Variable_name       | Value | +--+---+ | Max_used_connections | 152  | +--+---+ 1 row in set (0.01 sec)

Per cambiare (temporaneamente) il numero massimo di connessioni accettate: mysql> SET GLOBAL max_connections = 300;

Per cambiare (permanentemente) il numero massimo di connessioni accettate: vi /etc/my.cnf max_connections = 300

Binary Log
Per listare i log presenti: mysql> show binary logs ; +---+---+---+ | Log_name     | File_size | Encrypted | +---+---+---+ | binlog.000025 |      178 | No        | | binlog.000026 |      199 | No        | | binlog.000027 |      178 | No        | | binlog.000028 |      178 | No        | | binlog.000029 |      178 | No        | | binlog.000030 |      199 | No        | +---+---+---+

Per cancellare i log: mysql> purge binary logs to 'binlog.000030' ;

E' possibile cancellare automaticamente i log piu' vecchi di n giorni: mysql> show variables like 'expire_logs_days' ; +--+---+ | Variable_name   | Value | +--+---+ | expire_logs_days | 0    | +--+---+

mysql> set global expire_logs_days=7 ; mysql> show variables like 'expire_logs_days' ; +--+---+ | Variable_name   | Value | +--+---+ | expire_logs_days | 7    | +--+---+