Come eseguire query MySQL dalla linea di comando di Linux

Se sei responsabile della gestione di un server di database, di tanto in tanto potresti aver bisogno di eseguire una query e controllarla attentamente. Anche se puoi farlo dalla shell di MySQL/MariaDB, questo suggerimento ti permetterà di eseguire le query di MySQL/MariaDB direttamente utilizzando la riga di comando di Linux E di salvare l'output in un file per un'ispezione successiva (questo è particolarmente utile se la query restituisce molti record).

Diamo un'occhiata ad alcuni semplici esempi di esecuzione di query MYSQL direttamente dalla riga di comando prima di passare a query più avanzate.

Configurazione di database di esempio

Prima di addentrarci nei comandi, configuriamo i database di esempio con cui lavoreremo durante questa guida, in modo che tu possa seguire e praticare queste tecniche sul tuo sistema.

Creazione del database tecmintdb

Per prima cosa, creiamo il database tecmintdb e la tabella tutorials:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"

Successivamente, per creare una tabella del database chiamata tutorials nel database tecmintdb, esegui il comando qui sotto:

sudo mysql -u root -p tecmintdb << 'EOF'
CREATE TABLE IF NOT EXISTS tutorials (
    tut_id INT NOT NULL AUTO_INCREMENT,
    tut_title VARCHAR(100) NOT NULL,
    tut_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tut_id)
);

INSERT INTO tutorials (tut_title, tut_author, submission_date) VALUES
('Getting Started with Linux', 'John Smith', '2024-01-15'),
('Advanced Bash Scripting', 'Sarah Johnson', '2024-02-20'),
('MySQL Database Administration', 'Mike Williams', '2024-03-10'),
('Apache Web Server Configuration', 'Emily Brown', '2024-04-05'),
('Python for System Administrators', 'David Lee', '2024-05-12'),
('Docker Container Basics', 'Lisa Anderson', '2024-06-18'),
('Kubernetes Orchestration', 'Robert Taylor', '2024-07-22'),
('Linux Security Hardening', 'Jennifer Martinez', '2024-08-30');
EOF

Verifica che i dati siano stati inseriti:

sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"

Creazione del database dei dipendenti

Ora, creiamo un database employees più complesso con più tabelle correlate, questo è il database che useremo per gli esempi di query più avanzati:

sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"

Crea la tabella employees:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS employees (
    emp_no INT NOT NULL AUTO_INCREMENT,
    birth_date DATE NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    gender ENUM('M','F') NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no)
);

INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES
(10001, '1953-09-02', 'Georgi', 'Facello', 'M', '1984-06-02'),
(10002, '1964-06-02', 'Bezalel', 'Simmel', 'F', '1984-11-21'),
(10003, '1959-12-03', 'Parto', 'Bamford', 'M', '1984-08-28'),
(10004, '1954-05-01', 'Chirstian', 'Koblick', 'M', '1984-12-01'),
(10005, '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1984-09-15'),
(10006, '1953-04-20', 'Anneke', 'Preusig', 'F', '1985-02-18'),
(10007, '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1985-03-20'),
(10008, '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1984-07-11'),
(10009, '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18'),
(10010, '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1984-08-24');
EOF

Crea la tabella salaries:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS salaries (
    emp_no INT NOT NULL,
    salary INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, from_date),
    FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE
);

INSERT INTO salaries (emp_no, salary, from_date, to_date) VALUES
(10001, 60117, '1984-06-02', '1985-06-02'),
(10001, 62102, '1985-06-02', '1986-06-02'),
(10001, 66074, '1986-06-02', '9999-01-01'),
(10002, 65828, '1984-11-21', '1985-11-21'),
(10002, 65909, '1985-11-21', '9999-01-01'),
(10003, 40006, '1984-08-28', '1985-08-28'),
(10003, 43616, '1985-08-28', '9999-01-01'),
(10004, 40054, '1984-12-01', '1985-12-01'),
(10004, 42283, '1985-12-01', '9999-01-01'),
(10005, 78228, '1984-09-15', '1985-09-15'),
(10005, 82507, '1985-09-15', '9999-01-01'),
(10006, 40000, '1985-02-18', '1986-02-18'),
(10006, 43548, '1986-02-18', '9999-01-01'),
(10007, 56724, '1985-03-20', '1986-03-20'),
(10007, 60605, '1986-03-20', '9999-01-01'),
(10008, 46671, '1984-07-11', '1985-07-11'),
(10008, 48584, '1985-07-11', '9999-01-01'),
(10009, 60929, '1985-02-18', '1986-02-18'),
(10009, 64604, '1986-02-18', '9999-01-01'),
(10010, 72488, '1984-08-24', '1985-08-24'),
(10010, 74057, '1985-08-24', '9999-01-01');
EOF

Crea la tabella departments per join più complessi:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS departments (
    dept_no CHAR(4) NOT NULL,
    dept_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE KEY (dept_name)
);

INSERT INTO departments (dept_no, dept_name) VALUES
('d001', 'Marketing'),
('d002', 'Finance'),
('d003', 'Human Resources'),
('d004', 'Production'),
('d005', 'Development'),
('d006', 'Quality Management');
EOF

Crea la tabella dept_emp per collegare employees ai departments:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS dept_emp (
    emp_no INT NOT NULL,
    dept_no CHAR(4) NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, dept_no),
    FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments(dept_no) ON DELETE CASCADE
);

INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date) VALUES
(10001, 'd005', '1984-06-02', '9999-01-01'),
(10002, 'd005', '1984-11-21', '9999-01-01'),
(10003, 'd004', '1984-08-28', '9999-01-01'),
(10004, 'd004', '1984-12-01', '9999-01-01'),
(10005, 'd003', '1984-09-15', '9999-01-01'),
(10006, 'd005', '1985-02-18', '9999-01-01'),
(10007, 'd004', '1985-03-20', '9999-01-01'),
(10008, 'd005', '1984-07-11', '9999-01-01'),
(10009, 'd006', '1985-02-18', '9999-01-01'),
(10010, 'd006', '1984-08-24', '9999-01-01');
EOF

Verifica che tutto sia configurato correttamente:

sudo mysql -u root -p -e "USE employees; SHOW TABLES;"

Ora hai configurato entrambi i database con dati di esempio e puoi seguire tutti gli esempi in questa guida. Il database tecmintdb è perfetto per query semplici, mentre il database employees ti permette di esercitarti con operazioni più complesse come join e aggregazioni.

Esecuzione di query di base

Per visualizzare tutti i database sul tuo server, puoi eseguire il seguente comando:

sudo mysql -u root -p -e "show databases;"

Successivamente, per creare una tabella del database chiamata tutorials nel database tecmintdb, esegui il comando qui sotto:

sudo mysql -u root -p -e "USE tecmintdb; CREATE TABLE tutorials(tut_id INT NOT NULL AUTO_INCREMENT, tut_title VARCHAR(100) NOT NULL, tut_author VARCHAR(40) NOT NULL, submissoin_date DATE, PRIMARY KEY (tut_id));"

Salvare i risultati di una query MySQL in un file

Useremo il seguente comando e indirizzeremo l'output al comando tee seguito dal nome del file in cui vogliamo memorizzare l'output.

Per illustrazione, useremo un database chiamato employees e un semplice join tra le tabelle employees e salaries. Nel tuo caso, basta digitare la query SQL tra le virgolette e premere Invio.

Nota che ti verrà chiesto di inserire la password per l'utente del database:

sudo mysql -u root -p -e "USE employees; SELECT DISTINCT A.first_name, A.last_name FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE hire_date < '1985-01-31';" | tee queryresults.txt

Visualizza i risultati della query con l'aiuto del comando cat.

cat queryresults.txt

Esegui query MySQL/MariaDB da riga di comando

Con i risultati delle query in file di testo semplice, puoi elaborare i record più facilmente utilizzando altri strumenti da riga di comando. Ora che hai visto le basi, esploriamo alcune tecniche più avanzate che renderanno il tuo lavoro con il database da riga di comando ancora più efficiente.

Formattare l'output per una migliore leggibilità

Il formato tabellare predefinito è ottimo per la visualizzazione nel terminale, ma a volte sono necessari formati diversi. È possibile visualizzare i risultati in formato verticale, particolarmente utile quando si trattano tabelle con molte colonne:

sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"

Il \G alla fine visualizza ogni riga verticalmente invece che in una tabella, quindi invece di vedere una tabella orizzontale stretta, ottieni qualcosa del genere:

*************************** 1. row ***************************
    emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
 last_name: Facello
    gender: M
 hire_date: 1984-06-02

Esportazione in formato CSV

Quando hai bisogno di importare i risultati delle query in applicazioni per fogli di calcolo o in altri strumenti, il formato CSV è la scelta migliore:

sudo mysql -u root -p -e "USE employees; SELECT first_name, last_name, hire_date FROM employees WHERE hire_date < '1985-01-31';" | sed 's/\t/,/g' > employees.csv

Questo indirizza l'output attraverso sed per sostituire le tabulazioni con le virgole, creando un file CSV corretto che si apre senza problemi in Excel, LibreOffice Calc o in qualsiasi altro software di fogli di calcolo.

Eseguire query senza richieste di password

Se stai automatizzando le attività del database con cron job o script, non vuoi inserire le password manualmente ogni volta; è qui che entrano in gioco i file di configurazione di MySQL.

Crea un file in ~/.my.cnf con le tue credenziali:

[client]
user=root
password=your_password_here

Poi proteggilo in modo che solo tu possa leggerlo:

chmod 600 ~/.my.cnf

Ora puoi eseguire query senza il flag -p e senza ricevere richieste:

mysql -e "SHOW DATABASES;"

Ricorda solo che memorizzare le password in file di testo semplice ha implicazioni sulla sicurezza, quindi utilizza questo metodo solo su server di cui controlli l'accesso e considera l'uso dei metodi di autenticazione più sicuri di MySQL per gli ambienti di produzione.

Esecuzione di query complesse su più righe

A volte le tue query sono troppo complesse per essere scritte in un'unica riga di comando, soprattutto quando hai a che fare con più join, sottoquery o condizioni complesse.

Puoi mettere il tuo SQL in un file ed eseguirlo:

cat > complex_query.sql << 'EOF'
USE employees;
SELECT 
    e.first_name,
    e.last_name,
    d.dept_name,
    s.salary
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date BETWEEN '1985-01-01' AND '1985-12-31'
    AND s.from_date = (
        SELECT MAX(from_date) 
        FROM salaries 
        WHERE emp_no = e.emp_no
    )
ORDER BY s.salary DESC
LIMIT 10;
EOF

Adesso eseguilo.

sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt

Questo approccio mantiene le tue query organizzate e riutilizzabili, e puoi anche gestirle con il controllo delle versioni usando Git proprio come qualsiasi altro codice.

Elaborazione batch di più query

Se hai bisogno di eseguire diverse query correlate e salvare ogni risultato separatamente, puoi creare uno script:

#!/bin/bash

QUERIES=(
    "SELECT COUNT(*) as total_employees FROM employees"
    "SELECT dept_name, COUNT(*) as employee_count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no GROUP BY dept_name"
    "SELECT YEAR(hire_date) as year, COUNT(*) as hires FROM employees GROUP BY YEAR(hire_date) ORDER BY year"
)

FILENAMES=(
    "total_count.txt"
    "dept_distribution.txt"
    "yearly_hires.txt"
)

for i in "${!QUERIES[@]}"; do
    echo "Running query $((i+1))..."
    mysql -u root -p -e "USE employees; ${QUERIES[$i]}" > "${FILENAMES[$i]}"
    echo "Results saved to ${FILENAMES[$i]}"
done

Salvalo come script, rendilo eseguibile con chmod +x e avrai uno strumento di query batch riutilizzabile.

Monitoraggio delle query di lunga durata

Quando esegui query che potrebbero richiedere un po' di tempo, vuoi vedere dei progressi o almeno sapere che stanno ancora funzionando.

Combina la tua query con l'output dello stato:

(sudo mysql -u root -p -e "USE employees; SELECT COUNT(*) FROM large_table WHERE complex_condition;" && echo "Query completed at $(date)") | tee query_log.txt

Per query ancora più lunghe, eseguirle in background e monitorare l'elenco dei processi di MySQL:

sudo mysql -u root -p -e "USE employees; SELECT * FROM massive_table;" > output.txt &
sudo watch -n 5 'mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -A 5 "SELECT"'

Questo esegue la tua query in background mentre mostra l'elenco dei processi ogni 5 secondi, così puoi vedere che sta ancora lavorando e quanto progresso ha fatto.

Filtraggio e elaborazione dei risultati

Una volta che hai i risultati della query in un file di testo, puoi usare gli strumenti standard di Linux per elaborarli ulteriormente. Ecco alcuni schemi utili:

Conta il numero di righe dei risultati (esclusa l'intestazione):

tail -n +2 queryresults.txt | wc -l

Estrai colonne specifiche usando awk:

awk '{print $1, $3}' queryresults.txt

Cerca modelli specifici nei risultati:

grep -i "engineering" dept_distribution.txt

Ordina i risultati in base a una colonna numerica:

tail -n +2 queryresults.txt | sort -k3 -n

Gestione dei caratteri speciali e dei grandi dataset

Quando i tuoi dati contengono caratteri speciali, tabulazioni o interruzioni di riga, l'output predefinito può diventare disordinato, quindi usa le opzioni --batch e --raw per un output più pulito:

sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt

Per le query che restituiscono milioni di righe, potresti incorrere in problemi di memoria; invece di caricare tutto in memoria, trasmetti i risultati in streaming:

sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz

L'opzione --quick indica a MySQL di recuperare le righe una alla volta invece di memorizzare l'intero set di risultati, e passare attraverso gzip comprime l'output al volo, risparmiando spazio su disco.

Creare rapide copie di backup del database

Anche se tecnicamente questo non consiste nell'eseguire query, puoi usare tecniche simili da linea di comando per creare rapidamente dump di database con il comando mysqldump.

sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz

O esegui il backup solo di tabelle specifiche:

sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz

Pianificazione dei report automatici

Combina tutto ciò che abbiamo trattato per creare report giornalieri automatici utilizzando cron con l'aiuto del seguente script bash.

#!/bin/bash
REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/var/reports/daily_stats_${REPORT_DATE}.txt"

{
    echo "Database Statistics Report - ${REPORT_DATE}"
    echo "=========================================="
    echo
    
    echo "Total Employees:"
    mysql -e "USE employees; SELECT COUNT(*) FROM employees;"
    echo
    
    echo "New Hires This Month:"
    mysql -e "USE employees; SELECT COUNT(*) FROM employees WHERE MONTH(hire_date) = MONTH(CURRENT_DATE()) AND YEAR(hire_date) = YEAR(CURRENT_DATE());"
    echo
    
    echo "Department Distribution:"
    mysql -e "USE employees; SELECT d.dept_name, COUNT(*) as count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no WHERE de.to_date = '9999-01-01' GROUP BY d.dept_name ORDER BY count DESC;"
    
} > "$REPORT_FILE"

echo "Report generated: $REPORT_FILE"

Aggiungilo a cron per eseguirlo quotidianamente alle 6 del mattino:

0 6 * * * /usr/local/bin/generate_db_report.sh

Riepilogo

Abbiamo condiviso diversi consigli su Linux che tu, come amministratore di sistema, potresti trovare utili per automatizzare le tue operazioni quotidiane su Linux o eseguirle più facilmente.

Il punto chiave qui è che non devi sempre avviare la shell di MySQL o usare strumenti GUI pesanti per lavorare con i tuoi database; la riga di comando ti offre velocità, capacità di automazione e la possibilità di integrare le operazioni sul database nei tuoi script di shell e flussi di lavoro esistenti.

Hai altri consigli che vorresti condividere con il resto della community? In tal caso, ti preghiamo di farlo utilizzando il modulo dei commenti qui sotto. :