Como Executar Consultas MySQL a partir da Linha de Comando do Linux

Se você é responsável por gerir um servidor de base de dados, de vez em quando poderá precisar executar uma consulta e analisá-la cuidadosamente. Embora possa fazer isso a partir do shell MySQL/MariaDB, esta dica permitirá executar as consultas MySQL/MariaDB diretamente utilizando a linha de comando do Linux E gravar a saída num ficheiro para inspeção posterior (isto é particularmente útil se a consulta retornar muitos registos).

Vamos ver alguns exemplos simples de execução de consultas MYSQL directamente a partir da linha de comandos antes de passarmos para uma consulta mais avançada.

Configuração de Bases de Dados de Exemplo

Antes de mergulharmos nos comandos, vamos configurar as bases de dados de exemplo com as quais iremos trabalhar ao longo deste guia, para que possa acompanhar e praticar estas técnicas no seu próprio sistema.

A criar a base de dados tecmintdb

Primeiro, vamos criar a base de dados tecmintdb e a tabela tutorials:

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

De seguida, para criar uma tabela de base de dados chamada tutorials na base de dados tecmintdb, execute o comando abaixo:

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

Verifique se os dados foram inseridos:

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

Criar a base de dados de funcionários

Agora, vamos criar uma base de dados employees mais complexa com várias tabelas relacionadas, esta é a base de dados que usaremos para os exemplos de consultas mais avançadas:

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

Crie a tabela 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

Crie a tabela 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

Crie a tabela departments para junções mais complexas:

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

Crie a tabela dept_emp para ligar employees a 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

Verifique se tudo está configurado corretamente:

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

Agora já tem ambas as bases de dados configuradas com dados de exemplo, e pode acompanhar todos os exemplos deste guia. A base de dados tecmintdb é perfeita para consultas simples, enquanto a base de dados employees permite praticar operações mais complexas, como junções e agregações.

Execução Básica de Consultas

Para ver todas as bases de dados no seu servidor, pode emitir o seguinte comando:

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

De seguida, para criar uma tabela de base de dados chamada tutorials na base de dados tecmintdb, execute o comando abaixo:

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

Guardar os Resultados de uma Consulta MySQL num Ficheiro

Vamos usar o seguinte comando e encaminhar a saída para o comando tee seguido do nome do ficheiro onde queremos armazenar a saída.

Para ilustração, vamos usar uma base de dados chamada employees e um join simples entre as tabelas employees e salaries. No seu caso, basta escrever a consulta SQL entre aspas e pressionar Enter.

Note que lhe será solicitado que introduza a palavra-passe do utilizador da base de dados:

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

Veja os resultados da consulta com a ajuda do comando cat.

cat queryresults.txt

Executar Consultas MySQL/MariaDB a partir da Linha de Comandos

Com os resultados da consulta em ficheiros de texto simples, pode processar os registos mais facilmente utilizando outras ferramentas de linha de comando. Agora que já viu o básico, vamos explorar algumas técnicas mais avançadas que tornarão o trabalho com bases de dados na linha de comando ainda mais eficiente.

Formatar Saída para Melhor Legibilidade

O formato de tabela por defeito é ótimo para ver no terminal, mas às vezes é necessário utilizar formatos diferentes. Pode apresentar os resultados no formato vertical, que é particularmente útil quando se trata de tabelas com muitas colunas:

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

O \G no final mostra cada linha verticalmente em vez de numa tabela, assim, em vez de ver uma tabela horizontal apertada, obtém algo como:

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

Exportar para o formato CSV

Quando precisar de importar resultados de consultas para aplicações de folhas de cálculo ou outras ferramentas, o formato CSV é a sua melhor opção:

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

Isto canaliza a saída através do sed para substituir tabulações por vírgulas, criando um ficheiro CSV adequado que abre corretamente no Excel, LibreOffice Calc ou qualquer outro software de folhas de cálculo.

Executar Consultas Sem Solicitações de Palavra-Passe

Se está a automatizar tarefas de base de dados com jobs do cron ou scripts, não vai querer inserir senhas manualmente todas as vezes; é aí que entram os ficheiros de configuração do MySQL.

Crie um ficheiro em ~/.my.cnf com as suas credenciais:

[client]
user=root
password=your_password_here

Depois, proteja-o para que só você possa lê-lo:

chmod 600 ~/.my.cnf

Agora pode executar consultas sem a opção -p e sem ser solicitado:

mysql -e "SHOW DATABASES;"

Apenas lembre-se, guardar senhas em ficheiros de texto simples tem implicações de segurança, por isso use esta abordagem apenas em servidores onde controla o acesso, e considere utilizar os métodos de autenticação mais seguros do MySQL em ambientes de produção.

Executar Consultas Complexas de Múltiplas Linhas

Às vezes, as suas consultas são demasiado complexas para escrever numa única linha de comando, especialmente quando está a lidar com vários joins, subconsultas ou condições complexas.

Pode colocar o seu SQL num ficheiro e executá-lo:

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

Agora executa-o.

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

Esta abordagem mantém as suas consultas organizadas e reutilizáveis, e pode controlá-las em versões com o git como faria com qualquer outro código.

Processamento em Lote de Múltiplas Consultas

Se precisar de executar várias consultas relacionadas e guardar cada resultado separadamente, pode automatizar isso com um 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

Guarde isto como um script, torne-o executável com chmod +x e terá uma ferramenta de consulta em lote reutilizável.

Monitorização de Consultas de Longa Duração

Quando está a executar consultas que podem demorar, quer ver o progresso ou pelo menos saber que ainda estão a funcionar.

Combine a sua consulta com a saída de estado:

(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

Para consultas ainda mais longas, execute-as em segundo plano e monitorize a lista de processos do 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"'

Isto executa a sua consulta em segundo plano enquanto mostra a lista de processos a cada 5 segundos, para que possa ver que ainda está a funcionar e quanto progresso foi feito.

Filtrar e Processar Resultados

Depois de ter os resultados da consulta num ficheiro de texto, pode usar ferramentas padrão do Linux para os processar mais. Aqui estão alguns padrões úteis:

Conte o número de linhas de resultado (excluindo o cabeçalho):

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

Extrair colunas específicas usando awk:

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

Procurar padrões específicos nos resultados:

grep -i "engineering" dept_distribution.txt

Ordenar resultados por uma coluna numérica:

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

Manipulação de Caracteres Especiais e Grandes Conjuntos de Dados

Quando os seus dados contêm caracteres especiais, tabulações ou novas linhas, a saída padrão pode ficar desorganizada, por isso use as opções --batch e --raw para uma saída mais limpa:

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

Para consultas que retornam milhões de linhas, poderá deparar-se com problemas de memória; em vez de carregar tudo na memória, transmita os resultados em fluxo:

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

A opção --quick diz ao MySQL para recuperar as linhas uma de cada vez em vez de armazenar em buffer todo o conjunto de resultados, e enviar para gzip comprime a saída em tempo real, poupando espaço em disco.

Criar Cópias de Segurança Rápidas da Base de Dados

Embora isto não seja tecnicamente a execução de consultas, pode usar técnicas semelhantes de linha de comando para criar dumps rápidos de bases de dados com o comando mysqldump.

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

Ou faça backup apenas de tabelas específicas:

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

Agendar Relatórios Automáticos

Combine tudo o que abordámos para criar relatórios diários automáticos usando cron com a ajuda do seguinte 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"

Adicione-o ao cron para executar diariamente às 6 da manhã:

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

Resumo

Partilhámos várias dicas de Linux que, como administrador de sistemas, poderá achar úteis quando se trata de automatizar as suas tarefas diárias no Linux ou realizá-las de forma mais fácil.

A principal conclusão aqui é que nem sempre precisa de abrir o MySQL shell ou usar ferramentas GUI pesadas para trabalhar com as suas bases de dados; a linha de comandos oferece-lhe rapidez, capacidades de automatização e a possibilidade de integrar operações de base de dados nos seus scripts e fluxos de trabalho existentes.

Tem mais alguma dica que gostaria de partilhar com o resto da comunidade? Se sim, faça-o utilizando o formulário de comentários abaixo. :