Comment exécuter des requêtes MySQL depuis la ligne de commande Linux

Si vous êtes responsable de la gestion d'un serveur de bases de données, de temps en temps, vous devrez peut-être exécuter une requête et l'inspecter attentivement. Bien que vous puissiez le faire depuis le shell MySQL/MariaDB, ce conseil vous permettra d'exécuter les requêtes MySQL/MariaDB directement depuis la ligne de commande Linux ET de sauvegarder le résultat dans un fichier pour une inspection ultérieure (ce qui est particulièrement utile si la requête renvoie beaucoup d'enregistrements).

Examinons quelques exemples simples d'exécution de requêtes MYSQL directement depuis la ligne de commande avant de passer à une requête plus avancée.

Configuration des bases de données d'exemple

Avant de plonger dans les commandes, configurons les bases de données d'exemple avec lesquelles nous travaillerons tout au long de ce guide, afin que vous puissiez suivre et pratiquer ces techniques sur votre propre système.

Création de la base de données tecmintdb

Tout d'abord, créons la base de données tecmintdb et la table tutorials :

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

Ensuite, pour créer une table de base de données nommée tutorials dans la base de données tecmintdb, exécutez la commande ci-dessous :

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

Vérifiez que les données ont été insérées :

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

Créer la base de données des employés

Maintenant, créons une base de données employees plus complexe avec plusieurs tables liées, c'est la base de données que nous utiliserons pour les exemples de requêtes plus avancés :

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

Créez la table 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

Créez la table 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

Créez la table departments pour des jointures plus complexes :

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

Créez la table dept_emp pour relier employees à 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

Vérifiez que tout est correctement configuré :

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

Maintenant, vous avez configuré les deux bases de données avec des données d'exemple, et vous pouvez suivre tous les exemples de ce guide. La base de données tecmintdb est parfaite pour les requêtes simples, tandis que la base de données employees vous permet de pratiquer des opérations plus complexes comme les jointures et les agrégations.

Exécution de requête de base

Pour afficher toutes les bases de données sur votre serveur, vous pouvez exécuter la commande suivante :

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

Ensuite, pour créer une table de base de données nommée tutorials dans la base de données tecmintdb, exécutez la commande ci-dessous :

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

Enregistrer les résultats d'une requête MySQL dans un fichier

Nous utiliserons la commande suivante et redirigerons la sortie vers la commande tee suivie du nom de fichier où nous voulons stocker la sortie.

Pour l'illustration, nous utiliserons une base de données nommée employees et une jointure simple entre les tables employees et salaries. Dans votre propre cas, tapez simplement la requête SQL entre les guillemets et appuyez sur Entrée.

Notez que vous serez invité à entrer le mot de passe de l'utilisateur de la base de données :

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

Afficher les résultats de la requête à l'aide de la commande cat.

cat queryresults.txt

Exécuter des requêtes MySQL/MariaDB depuis la ligne de commande

Avec les résultats de la requête dans des fichiers texte simples, vous pouvez traiter les enregistrements plus facilement en utilisant d'autres utilitaires en ligne de commande. Maintenant que vous avez vu les bases, explorons quelques techniques plus avancées qui rendront votre travail en ligne de commande sur les bases de données encore plus efficace.

Formatage de la sortie pour une meilleure lisibilité

Le format de tableau par défaut est idéal pour la visualisation dans le terminal, mais parfois vous avez besoin de formats différents. Vous pouvez afficher les résultats au format vertical, ce qui est particulièrement utile lorsqu'il s'agit de tableaux contenant de nombreuses colonnes :

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

Le \G à la fin affiche chaque ligne verticalement au lieu de dans un tableau, donc au lieu de voir un tableau horizontal serré, vous obtenez quelque chose comme :

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

Exportation au format CSV

Lorsque vous devez importer les résultats d'une requête dans des applications de feuilles de calcul ou d'autres outils, le format CSV est votre meilleur choix :

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

Cela passe la sortie via sed pour remplacer les tabulations par des virgules, créant un fichier CSV approprié qui s'ouvre proprement dans Excel, LibreOffice Calc ou tout autre logiciel de tableur.

Exécuter des requêtes sans invite de mot de passe

Si vous automatisez les tâches de la base de données avec des tâches cron ou des scripts, vous ne voulez pas entrer manuellement les mots de passe à chaque fois, c'est là que les fichiers de configuration MySQL interviennent.

Créez un fichier à ~/.my.cnf avec vos identifiants :

[client]
user=root
password=your_password_here

Puis sécurisez-le de manière à ce que vous seul puissiez le lire :

chmod 600 ~/.my.cnf

Vous pouvez maintenant exécuter des requêtes sans l'option -p et sans être invité :

mysql -e "SHOW DATABASES;"

N'oubliez pas, stocker des mots de passe dans des fichiers texte en clair a des implications sur la sécurité, donc utilisez cette approche uniquement sur des serveurs où vous contrôlez l'accès, et envisagez d'utiliser les méthodes d'authentification plus sécurisées de MySQL pour les environnements de production.

Exécution de requêtes complexes sur plusieurs lignes

Parfois, vos requêtes sont trop complexes pour être écrites en une seule ligne de commande, surtout lorsque vous traitez plusieurs jointures, sous-requêtes ou conditions complexes.

Vous pouvez mettre votre SQL dans un fichier et l'exécuter :

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

Exécute-le maintenant.

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

Cette approche permet de garder vos requêtes organisées et réutilisables, et vous pouvez les gérer en contrôle de version avec git comme n'importe quel autre code.

Traitement par lots de plusieurs requêtes

Si vous avez besoin d'exécuter plusieurs requêtes liées et de sauvegarder chaque résultat séparément, vous pouvez le 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

Enregistrez ceci sous forme de script, rendez-le exécutable avec chmod +x, et vous avez un outil de requête par lots réutilisable.

Surveillance des requêtes de longue durée

Lorsque vous exécutez des requêtes qui pourraient prendre un certain temps, vous voulez voir les progrès ou au moins savoir qu'elles fonctionnent toujours.

Combinez votre requête avec la sortie de statut :

(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

Pour des requêtes encore plus longues, exécutez-les en arrière-plan et surveillez la liste des processus 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"'

Cela exécute votre requête en arrière-plan tout en affichant la liste des processus toutes les 5 secondes, afin que vous puissiez voir qu'elle fonctionne toujours et combien de progrès elle a réalisés.

Filtrage et traitement des résultats

Une fois que vous avez les résultats de la requête dans un fichier texte, vous pouvez utiliser les outils standards de Linux pour les traiter davantage. Voici quelques modèles utiles :

Comptez le nombre de lignes de résultats (en excluant l'en-tête) :

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

Extraire des colonnes spécifiques en utilisant awk :

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

Rechercher des motifs spécifiques dans les résultats :

grep -i "engineering" dept_distribution.txt

Trier les résultats par une colonne numérique :

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

Gestion des caractères spéciaux et des grands ensembles de données

Lorsque vos données contiennent des caractères spéciaux, des tabulations ou des sauts de ligne, la sortie par défaut peut devenir désordonnée, utilisez donc les options --batch et --raw pour une sortie plus propre :

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

Pour les requêtes qui renvoient des millions de lignes, vous pourriez rencontrer des problèmes de mémoire ; au lieu de tout charger en mémoire, diffusez les résultats :

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

L'option --quick indique à MySQL de récupérer les lignes une par une au lieu de mettre en mémoire tampon l'ensemble du jeu de résultats, et le fait de passer par gzip compresse la sortie en temps réel, économisant de l'espace disque.

Créer des sauvegardes rapides de bases de données

Bien que cela ne consiste pas techniquement à exécuter des requêtes, vous pouvez utiliser des techniques similaires en ligne de commande pour créer rapidement des sauvegardes de base de données avec la commande mysqldump.

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

Ou sauvegarder seulement des tables spécifiques :

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

Planification des rapports automatisés

Combinez tout ce que nous avons vu pour créer des rapports quotidiens automatisés en utilisant cron avec l'aide du script bash suivant.

#!/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"

Ajoutez-le au cron pour qu'il s'exécute tous les jours à 6h du matin :

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

Résumé

Nous avons partagé plusieurs conseils Linux que vous, en tant qu'administrateur système, pourriez trouver utiles pour automatiser vos tâches Linux quotidiennes ou les effectuer plus facilement.

Le point clé ici est que vous n'avez pas toujours besoin de lancer le shell MySQL ou d'utiliser des outils GUI lourds pour travailler avec vos bases de données ; la ligne de commande vous offre rapidité, capacités d'automatisation et possibilité d'intégrer les opérations de base de données dans vos scripts shell et flux de travail existants.

Avez-vous d'autres conseils que vous aimeriez partager avec le reste de la communauté ? Si oui, veuillez le faire en utilisant le formulaire de commentaire ci-dessous. :