Jak spouštět dotazy MySQL z příkazového řádku Linuxu
Pokud máte na starosti správu databázového serveru, čas od času může být potřeba spustit dotaz a pečlivě jej prozkoumat. I když to můžete udělat z MySQL/MariaDB shellu, tento tip vám umožní spustit MySQL/MariaDB dotazy přímo z příkazového řádku Linuxu A uložit výstup do souboru pro pozdější kontrolu (to je zvláště užitečné, pokud dotaz vrací mnoho záznamů).
Pojďme se podívat na několik jednoduchých příkladů spuštění MYSQL dotazů přímo z příkazového řádku, než se pustíme do pokročilejších dotazů.
Nastavení ukázkových databází
Než se pustíme do příkazů, nastavme si ukázkové databáze, se kterými budeme v tomto průvodci pracovat, abyste mohli sledovat postup a procvičovat tyto techniky na vlastním systému.
Vytvoření databáze tecmintdb
Nejprve vytvořme databázi tecmintdb a tabulku tutorials:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"
Dále, k vytvoření databázové tabulky s názvem tutorials v databázi tecmintdb, spusťte níže uvedený pří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
Ověřte, zda byla data vložena:
sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"
Vytváření databáze zaměstnanců
Nyní vytvořme složitější databázi employees s více vzájemně propojenými tabulkami, toto je databáze, kterou použijeme pro pokročilejší příklady dotazů:
sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"
Vytvořte tabulku 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
Vytvořte tabulku 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
Vytvořte tabulku departments pro složitější spojení:
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
Vytvořte tabulku dept_emp pro propojení 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
Ověřte, že je vše nastaveno správně:
sudo mysql -u root -p -e "USE employees; SHOW TABLES;"
Nyní máte obě databáze nastavené s ukázkovými daty a můžete sledovat všechny příklady v této příručce. Databáze tecmintdb je ideální pro jednoduché dotazy, zatímco databáze employees vám umožní procvičovat složitější operace, jako jsou spojení a agregace.
Základní provádění dotazů
Chcete-li zobrazit všechny databáze na vašem serveru, můžete zadat následující příkaz:
sudo mysql -u root -p -e "show databases;"
Dále, pro vytvoření databázové tabulky s názvem tutorials v databázi tecmintdb, spusťte níže uvedený pří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));"
Ukládání výsledků dotazů MySQL do souboru
Použijeme následující příkaz a výstup přesměrujeme do příkazu tee, následovaný názvem souboru, kam chceme výstup uložit.
Pro ilustraci použijeme databázi s názvem employees a jednoduché spojení mezi tabulkami employees a salaries. Ve vašem případě jednoduše napište SQL dotaz mezi uvozovky a stiskněte Enter.
Všimněte si, že budete vyzváni k zadání hesla pro uživatele databáze:
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
Zobrazte výsledky dotazu pomocí příkazu cat.
cat queryresults.txt
Spouštění dotazů MySQL/MariaDB z příkazového řádku
S výsledky dotazu v textových souborech můžete záznamy snáze zpracovávat pomocí jiných příkazových nástrojů. Teď, když jste viděli základy, pojďme prozkoumat některé pokročilejší techniky, které vaši práci s databází přes příkazový řádek učiní ještě efektivnější.
Formátování výstupu pro lepší čitelnost
Výchozí formát tabulky je skvělý pro prohlížení v terminálu, ale někdy potřebujete jiné formáty. Výsledky můžete zobrazit ve svislém formátu, což je obzvlášť užitečné při práci s tabulkami, které mají mnoho sloupců:
sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"
\G na konci zobrazuje každý řádek svisle místo v tabulce, takže místo stísněné vodorovné tabulky dostanete něco jako:
*************************** 1. row ***************************
emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
last_name: Facello
gender: M
hire_date: 1984-06-02
Export do formátu CSV
Když potřebujete importovat výsledky dotazů do tabulkových aplikací nebo jiných nástrojů, CSV formát je vaše nejlepší volba:
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
Tento příkaz přesměruje výstup přes sed pro nahrazení tabulátorů čárkami, čímž vytvoří správný CSV soubor, který se bez problémů otevře v Excel, LibreOffice Calc nebo v jakémkoli jiném tabulkovém programu.
Spouštění dotazů bez výzev k zadání hesla
Pokud automatizujete úkoly databáze pomocí cron jobů nebo skriptů, nechcete zadávat hesla ručně při každém spuštění, a právě zde přicházejí ke slovu konfigurační soubory MySQL.
Vytvořte soubor na ~/.my.cnf se svými přihlašovacími údaji:
[client]
user=root
password=your_password_here
Pak to zabezpečte tak, aby to mohl číst jenom vy:
chmod 600 ~/.my.cnf
Nyní můžete spouštět dotazy bez přepínače -p a bez vyzvání k zadání:
mysql -e "SHOW DATABASES;"
Jen si pamatujte, že ukládání hesel do nezakódovaných textových souborů má bezpečnostní důsledky, takže tento způsob používejte jen na serverech, kde máte kontrolu nad přístupem, a zvažte použití bezpečnějších autentizačních metod MySQL pro produkční prostředí.
Provádění složitých víceřádkových dotazů
Někdy jsou vaše dotazy příliš složité na to, aby je šlo napsat v jediném příkazovém řádku, zejména když pracujete s více spojeními, poddotazy nebo složitými podmínkami.
Své SQL můžete uložit do souboru a spustit 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
Teď to spusť.
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
Tento přístup udržuje vaše dotazy organizované a znovupoužitelné a můžete je spravovat verzemi pomocí gitu stejně jako jakýkoli jiný kód.
Hromadné zpracování více dotazů
Pokud potřebujete spustit několik souvisejících dotazů a každý výsledek uložit zvlášť, můžete to napsat do skriptu:
#!/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 jako skript, udělejte jej spustitelným pomocí chmod +x a budete mít opakovaně použitelný nástroj pro dávkové dotazy.
Sledování dlouho běžících dotazů
Když spouštíte dotazy, které mohou chvíli trvat, chcete vidět postup nebo alespoň vědět, že stále pracují.
Spojte svůj dotaz s výstupem 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
Pro ještě delší dotazy je spusťte na pozadí a sledujte seznam procesů 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"'
Tento příkaz spustí váš dotaz na pozadí a zároveň každých 5 sekund zobrazí seznam procesů, takže můžete vidět, že stále běží a kolik pokroku bylo dosaženo.
Filtrování a zpracování výsledků
Jakmile máte výsledky dotazu v textovém souboru, můžete je dále zpracovávat pomocí standardních nástrojů Linuxu. Zde je několik užitečných vzorů:
Spočítejte počet výsledných řádků (bez záhlaví):
tail -n +2 queryresults.txt | wc -l
Vyjměte konkrétní sloupce pomocí awk:
awk '{print $1, $3}' queryresults.txt
Hledat konkrétní vzory ve výsledcích:
grep -i "engineering" dept_distribution.txt
Seřadit výsledky podle číselného sloupce:
tail -n +2 queryresults.txt | sort -k3 -n
Práce se speciálními znaky a velkými datovými sadami
Když vaše data obsahují speciální znaky, tabulátory nebo nové řádky, výchozí výstup může být nepřehledný, proto použijte možnosti --batch a --raw pro čistší výstup:
sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt
U dotazů, které vrací miliony řádků, se můžete setkat s problémy s pamětí; místo načítání všeho do paměti, přenášejte výsledky postupně:
sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz
Možnost --quick říká MySQL, aby načítal řádky jeden po druhém místo toho, aby ukládal celý výsledný soubor do paměti, a použití gzip k zpracování dat průběžně komprimuje výstup a šetří tak místo na disku.
Vytváření rychlých záloh databáze
I když se to technicky nejedná o spouštění dotazů, můžete použít podobné techniky příkazového řádku k rychlému vytváření výpisů databáze pomocí příkazu mysqldump.
sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz
Nebo zálohovat jen konkrétní tabulky:
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Plánování automatizovaných zpráv
Spojte vše, co jsme probrali, a vytvořte automatizované denní zprávy pomocí cron za pomoci následujícího 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"
Přidejte to do cronu, aby se spouštělo každý den v 6 hodin ráno:
0 6 * * * /usr/local/bin/generate_db_report.sh
Shrnutí
Sdíleli jsme několik tipů pro Linux, které byste jako správce systému mohli považovat za užitečné při automatizaci vašich každodenních úkolů na Linuxu nebo při jejich snadnějším provádění.
Hlavní myšlenkou je, že nemusíte vždy spouštět MySQL shell nebo používat těžké grafické nástroje k práci s databázemi; příkazový řádek vám poskytuje rychlost, možnosti automatizace a schopnost integrovat databázové operace do vašich stávajících shell skriptů a pracovních postupů.
Máš nějaké další tipy, které bys chtěl sdílet s ostatními členy komunity? Pokud ano, prosím sdílej je pomocí níže uvedeného formuláře pro komentáře. :