Ako spúšťať MySQL dotazy z príkazového riadku Linuxu

Ak máte na starosti správu databázového servera, čas od času budete možno musieť spustiť dotaz a starostlivo ho preskúmať. Hoci to môžete urobiť z prostredia MySQL/MariaDB, tento tip vám umožní vykonávať MySQL/MariaDB dotazy priamo z príkazového riadku Linuxu a zároveň uložiť výstup do súboru na neskoršiu kontrolu (čo je obzvlášť užitočné, ak dotaz vráti veľa záznamov).

Pozrime sa na niekoľko jednoduchých príkladov spúšťania MYSQL dotazov priamo z príkazového riadku, než sa presunieme k pokročilejšiemu dotazu.

Nastavenie ukážkových databáz

Skôr než sa pustíme do príkazov, nastavme si príkladové databázy, s ktorými budeme pracovať počas tohto návodu, aby ste mohli postupovať spolu a cvičiť tieto techniky na vlastnom systéme.

Vytváranie databázy tecmintdb

Najprv vytvoríme databázu tecmintdb a tabuľku tutorials:

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

Ďalej, na vytvorenie tabuľky databázy s názvom tutorials v databáze tecmintdb, spustite nasledujúci príkaz:

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

Overte, či boli údaje vložené:

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

Vytváranie databázy zamestnancov

Teraz vytvorme zložitejšiu databázu employees s viacerými prepojenými tabuľkami, toto je databáza, ktorú použijeme pre pokročilejšie príklady dotazov:

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

Vytvorte tabuľku 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

Vytvorte tabuľku 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

Vytvorte tabuľku departments pre zložitejšie spojenia:

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

Vytvorte tabuľku dept_emp na prepojenie employees s 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

Skontrolujte, či je všetko nastavené správne:

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

Teraz máte obe databázy nastavené s ukážkovými dátami a môžete sledovať všetky príklady v tomto návode. Databáza tecmintdb je ideálna pre jednoduché dotazy, zatiaľ čo databáza employees vám umožní precvičovať zložitejšie operácie ako spojenia a agregácie.

Základné vykonávanie dotazu

Ak chcete zobraziť všetky databázy na vašom serveri, môžete použiť nasledujúci príkaz:

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

Ďalej, na vytvorenie tabuľky databázy s názvom tutorials v databáze tecmintdb, spustite nasledujúci príkaz:

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

Ukladanie výsledkov MySQL dotazu do súboru

Použijeme nasledujúci príkaz a jeho výstup presmerujeme do príkazu tee, za ktorým bude názov súboru, kam chceme výstup uložiť.

Na ilustráciu použijeme databázu s názvom employees a jednoduché spojenie medzi tabuľkami employees a salaries. Vo vašom prípade stačí zadať SQL dotaz medzi úvodzovkami a stlačiť Enter.

Všimnite si, že budete vyzvaní na zadanie hesla pre používateľa databázy:

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

Prezrite si výsledky dotazu pomocou príkazu cat.

cat queryresults.txt

Spúšťanie dotazov MySQL/MariaDB z príkazového riadku

S výsledkami dotazu v obyčajných textových súboroch môžete záznamy spracovávať ľahšie pomocou iných príkazových nástrojov. Teraz, keď ste videli základy, poďme preskúmať niektoré pokročilejšie techniky, ktoré urobia vašu prácu s databázou v príkazovom riadku ešte efektívnejšou.

Formátovanie výstupu pre lepšiu čitateľnosť

Predvolený formát tabuľky je skvelý na prezeranie v termináli, ale niekedy potrebujete iné formáty. Výsledky môžete zobraziť vo vertikálnom formáte, čo je obzvlášť užitočné pri práci s tabuľkami, ktoré majú veľa stĺpcov:

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

\G na konci zobrazí každý riadok vertikálne namiesto v tabuľke, takže namiesto toho, aby ste videli stiesnenú horizontálnu tabuľku, dostanete niečo ako:

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

Exportovanie do formátu CSV

Keď potrebujete importovať výsledky dopytov do tabuľkových aplikácií alebo iných nástrojov, formát CSV je tá najlepšia voľba:

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

Toto presmeruje výstup cez sed, aby nahradil tabuátory čiarkami, čím vytvorí správny súbor CSV, ktorý sa dá ľahko otvoriť v Excel, LibreOffice Calc alebo v inom tabuľkovom programe.

Spúšťanie dotazov bez výziev na zadanie hesla

Ak automatizujete úlohy databázy pomocou cron úloh alebo skriptov, nechcete zakaždým manuálne zadávať heslá, práve tu prichádzajú na rad konfiguračné súbory MySQL.

Vytvorte súbor na ~/.my.cnf s vašimi prihlasovacími údajmi:

[client]
user=root
password=your_password_here

Potom ho zabezpečte tak, aby ho mohol čítať len vy:

chmod 600 ~/.my.cnf

Teraz môžete spúšťať dotazy bez príznaku -p a bez toho, aby ste boli vyzvaní:

mysql -e "SHOW DATABASES;"

Len si pamätajte, že ukladanie hesiel do súborov v obyčajnom texte má bezpečnostné dôsledky, takže tento prístup používajte iba na serveroch, kde kontrolujete prístup, a zvážte použitie bezpečnejších autentifikačných metód MySQL pre produkčné prostredia.

Vykonávanie zložitých viacriadkových dotazov

Niekedy sú vaše dotazy príliš zložité na to, aby sa dali napísať v jednom príkazovom riadku, najmä keď pracujete s viacerými spojeniami, poddotazmi alebo zložitými podmienkami.

Svoj SQL môžete uložiť do súboru a spustiť ho:

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

Teraz to vykonaj.

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

Tento prístup udržiava vaše dopyty usporiadané a znovupoužiteľné, a môžete ich spravovať pomocou verzionovania v gite rovnako ako akýkoľvek iný kód.

Hromadné spracovanie viacerých dotazov

Ak potrebujete spustiť niekoľko súvisiacich dopytov a výsledok každého uložiť zvlášť, môžete to napísať ako skript:

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

Uložte to ako skript, spravte ho spustiteľným pomocou chmod +x a budete mať možné znovu použiteľný nástroj na hromadné dotazy.

Monitorovanie dlhodobých dotazov

Keď spúšťate dotazy, ktoré môžu trvať dlhšie, chcete vidieť priebeh alebo aspoň vedieť, že sú stále aktívne.

Skombinujte svoj dopyt s výstupom stavu:

(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

Pre ešte dlhšie dotazy ich spúšťajte na pozadí a sledujte zoznam procesov 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"'

Toto spustí váš dotaz na pozadí a zároveň každých 5 sekúnd zobrazí zoznam procesov, takže môžete vidieť, že stále pracuje a koľko pokroku dosiahol.

Filtrovanie a spracovanie výsledkov

Keď budete mať výsledky dotazu v textovom súbore, môžete použiť štandardné nástroje Linuxu na ich ďalšie spracovanie. Tu je niekoľko užitočných vzorov:

Spočítajte počet výsledkových riadkov (bez hlavičky):

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

Extrahujte konkrétne stĺpce pomocou awk:

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

Vyhľadajte konkrétne vzory vo výsledkoch:

grep -i "engineering" dept_distribution.txt

Zoradiť výsledky podľa číselného stĺpca:

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

Spracovanie špeciálnych znakov a veľkých dátových súborov

Keď vaše údaje obsahujú špeciálne znaky, tabuľátory alebo nové riadky, predvolený výstup môže byť chaotický, preto použite možnosti --batch a --raw pre čistejší výstup:

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

Pre dotazy, ktoré vracajú milióny riadkov, môžete naraziť na problémy s pamäťou, namiesto načítania všetkého do pamäte si výsledky streamujte:

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

Možnosť --quick príkazuje MySQL načítavať riadky jeden po druhom namiesto toho, aby uchovával celý výsledný súbor v pamäti, a presmerovanie cez gzip komprimuje výstup priebežne, čím šetrí miesto na disku.

Vytváranie rýchlych záloh databázy

Hoci toto technicky neznamená vykonávanie dotazov, môžete použiť podobné techniky príkazového riadku na vytvorenie rýchlych záloh databázy pomocou príkazu mysqldump.

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

Alebo zálohovať len konkrétne tabuľky:

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

Plánovanie automatizovaných správ

Spojte všetko, o čom sme doteraz hovorili, aby ste vytvorili automatizované denné správy pomocou cron s pomocou nasledujúceho bash skriptu.

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

Pridajte ho do cronu, aby sa spúšťal každý deň o 6:00 ráno:

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

Zhrnutie

Zdieľali sme niekoľko tipov pre Linux, ktoré by ste ako systémový administrátor mohli považovať za užitočné pri automatizácii svojich každodenných úloh v Linuxe alebo ich jednoduchšom vykonávaní.

Hlavné zhrnutie je, že nemusíte vždy spúšťať MySQL shell alebo používať náročné grafické nástroje na prácu s databázami; príkazový riadok vám poskytuje rýchlosť, možnosti automatizácie a schopnosť integrovať databázové operácie do vašich existujúcich shell skriptov a pracovných postupov.

Máš nejaké ďalšie tipy, ktoré by si chcel/a zdieľať s ostatnou komunitou? Ak áno, prosím urob to pomocou formulára na komentáre nižšie. :