Cum să rulezi interogări MySQL din linia de comandă Linux
Dacă sunteți responsabil de administrarea unui server de baze de date, din când în când poate fi necesar să rulați o interogare și să o inspectați cu atenție. Deși puteți face acest lucru din shell-ul MySQL/MariaDB, acest sfat vă va permite să executați interogările MySQL/MariaDB direct folosind linia de comandă Linux ȘI să salvați rezultatul într-un fișier pentru inspectare ulterioară (aceasta este deosebit de util dacă interogarea returnează multe înregistrări).
Să analizăm câteva exemple simple de rulare a interogărilor MYSQL direct din linia de comandă înainte de a trece la o interogare mai avansată.
Configurarea bazelor de date exemplu
Înainte să trecem la comenzi, hai să configurăm bazele de date de exemplu cu care vom lucra pe tot parcursul acestui ghid, astfel încât să poți urma și să exersezi aceste tehnici pe propriul tău sistem.
Crearea bazei de date tecmintdb
Mai întâi, să creăm baza de date tecmintdb și tabelul tutorials:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"
Apoi, pentru a crea un tabel în baza de date numit tutorials în baza de date tecmintdb, rulați comanda de mai jos:
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ți dacă datele au fost introduse:
sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"
Crearea bazei de date a angajaților
Acum, să creăm o bază de date employees mai complexă, cu mai multe tabele relaționate, aceasta este baza de date pe care o vom folosi pentru exemplele de interogări mai avansate:
sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"
Creează 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
Creați tabelul 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
Creează tabelul departments pentru join-uri mai complexe:
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ți tabela dept_emp pentru a lega employees de 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ți dacă totul este configurat corect:
sudo mysql -u root -p -e "USE employees; SHOW TABLES;"
Acum aveți ambele baze de date configurate cu date de probă și puteți urmări toate exemplele din acest ghid. Baza de date tecmintdb este perfectă pentru interogări simple, în timp ce baza de date employees vă permite să exersați operațiuni mai complexe, precum join-uri și agregări.
Executarea interogării de bază
Pentru a vizualiza toate bazele de date de pe serverul dvs., puteți folosi următoarea comandă:
sudo mysql -u root -p -e "show databases;"
Apoi, pentru a crea un tabel în baza de date numit tutorials în baza de date tecmintdb, rulați comanda de mai jos:
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));"
Salvarea rezultatelor interogării MySQL într-un fișier
Vom folosi următoarea comandă și vom direcționa output-ul către comanda tee, urmată de numele fișierului în care dorim să stocăm output-ul.
Pentru ilustrație, vom folosi o bază de date numită employees și o simplă asociere între tabelele employees și salaries. În cazul tău, pur și simplu tastează interogarea SQL între ghilimele și apasă Enter.
Rețineți că veți fi solicitat să introduceți parola pentru utilizatorul bazei de date:
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
Vizualizați rezultatele interogării cu ajutorul comenzii cat.
cat queryresults.txt
Execută interogări MySQL/MariaDB din linia de comandă
Cu rezultatele interogărilor în fișiere text simple, poți procesa înregistrările mai ușor folosind alte utilitare din linia de comandă. Acum că ai văzut elementele de bază, să explorăm câteva tehnici mai avansate care vor face ca lucrul cu baza ta de date din linia de comandă să fie și mai eficient.
Formatarea rezultatului pentru o mai bună lizibilitate
Formatul implicit al tabelei este ideal pentru vizualizare în terminal, dar uneori ai nevoie de formate diferite. Poți afișa rezultatele în format vertical, ceea ce este deosebit de util atunci când lucrezi cu tabele care au multe coloane:
sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"
\G de la sfârșit afișează fiecare rând vertical în loc de a fi într-un tabel, așa că în loc să vezi un tabel orizontal înghesuit, obții ceva de genul:
*************************** 1. row ***************************
emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
last_name: Facello
gender: M
hire_date: 1984-06-02
Exportare în format CSV
Când trebuie să imporți rezultatele interogărilor în aplicații de tip foaie de calcul sau alte instrumente, formatul CSV este cea mai bună alegere:
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
Acest lucru redirecționează ieșirea prin sed pentru a înlocui taburile cu virgule, creând un fișier CSV corespunzător care se deschide fără probleme în Excel, LibreOffice Calc sau orice alt software de calcul tabelar.
Executarea interogărilor fără solicitări de parolă
Dacă automatizezi sarcini de baze de date cu joburi cron sau scripturi, nu vrei să introduci parole manual de fiecare dată, aici intervin fișierele de configurare MySQL.
Creează un fișier la ~/.my.cnf cu datele tale de autentificare:
[client]
user=root
password=your_password_here
Apoi securizeaz-o astfel încât doar tu să o poți citi:
chmod 600 ~/.my.cnf
Acum poți rula interogări fără flag-ul -p și fără a fi solicitat:
mysql -e "SHOW DATABASES;"
Țineți minte, stocarea parolelor în fișiere text simple are implicații de securitate, așa că folosiți această metodă doar pe servere unde controlați accesul și luați în considerare utilizarea metodelor de autentificare mai sigure ale MySQL pentru mediile de producție.
Executarea interogărilor complexe pe mai multe linii
Uneori, interogările tale sunt prea complexe pentru a fi scrise într-o singură linie de comandă, mai ales atunci când ai de-a face cu mai multe join-uri, subinterogări sau condiții complexe.
Poți să pui SQL-ul într-un fișier și să îl execuți:
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
Acum execută-o.
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
Această abordare îți menține interogările organizate și reutilizabile, iar tu le poți controla versiunea cu git la fel ca orice alt cod.
Procesarea în lot a mai multor interogări
Dacă trebuie să rulezi mai multe interogări înrudite și să salvezi fiecare rezultat separat, poți scrie un 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
Salvează asta ca un script, fă-l executabil cu chmod +x și vei avea un instrument de interogare batch reutilizabil.
Monitorizarea interogărilor de durată lungă
Când rulezi interogări care ar putea dura ceva timp, vrei să vezi progresul sau cel puțin să știi că încă funcționează.
Combină interogarea ta cu rezultatul stării:
(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
Pentru interogări și mai lungi, rulează-le în fundal și monitorizează lista de procese 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"'
Aceasta rulează interogarea ta în fundal în timp ce afișează lista proceselor la fiecare 5 secunde, astfel încât să poți vedea că încă funcționează și cât progres a făcut.
Filtrarea și procesarea rezultatelor
Odată ce aveți rezultatele interogării într-un fișier text, puteți folosi instrumente standard Linux pentru a le procesa în continuare. Iată câteva modele utile:
Numărați numărul de rânduri de rezultate (fără a include antetul):
tail -n +2 queryresults.txt | wc -l
Extrageți coloane specifice folosind awk:
awk '{print $1, $3}' queryresults.txt
Caută modele specifice în rezultate:
grep -i "engineering" dept_distribution.txt
Sortează rezultatele după o coloană numerică:
tail -n +2 queryresults.txt | sort -k3 -n
Gestionarea caracterelor speciale și a seturilor mari de date
Când datele tale conțin caractere speciale, taburi sau linii noi, ieșirea implicită poate deveni dezordonată, așa că folosește opțiunile --batch și --raw pentru o ieșire mai curată:
sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt
Pentru interogările care returnează milioane de rânduri, s-ar putea să întâmpinați probleme de memorie; în loc să încărcați totul în memorie, transmiteți rezultatele în flux:
sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz
Opțiunea --quick spune MySQL să preia rândurile unul câte unul în loc să încarce întregul set de rezultate în memorie, iar trecerea datelor prin gzip comprimă ieșirea în timp real, economisind spațiu pe disc.
Crearea copiilor de rezervă rapide ale bazei de date
Deși tehnic vorbind nu rulează interogări, poți folosi tehnici similare de linie de comandă pentru a crea rapid copii de siguranță ale bazei de date cu comanda mysqldump.
sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz
Sau să faci backup doar pentru anumite tabele:
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Programarea rapoartelor automate
Combinați tot ce am acoperit pentru a crea rapoarte zilnice automate folosind cron cu ajutorul următorului 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"
Adaugă-l în cron pentru a rula zilnic la ora 6 dimineața:
0 6 * * * /usr/local/bin/generate_db_report.sh
Rezumat
Am împărtășit câteva sfaturi pentru Linux pe care, în calitate de administrator de sistem, le-ai putea găsi utile atunci când vine vorba de automatizarea sarcinilor tale zilnice în Linux sau de realizarea lor mai ușor.
A concluzie importantă aici este că nu trebuie întotdeauna să pornești shell-ul MySQL sau să folosești unelte GUI grele pentru a lucra cu bazele tale de date; linia de comandă îți oferă viteză, posibilități de automatizare și capacitatea de a integra operațiunile bazei de date în scripturile și fluxurile de lucru existente.
Ai alte sfaturi pe care ai dori să le împărtășești cu restul comunității? Dacă da, te rugăm să le împărtășești folosind formularul de comentarii de mai jos. :