Kako pokretati MySQL upite iz Linux naredbenog retka
Ako ste zaduženi za upravljanje poslužiteljem baze podataka, s vremena na vrijeme možda ćete trebati pokrenuti upit i pažljivo ga pregledati. Iako to možete učiniti iz MySQL/MariaDB ljuske, ovaj savjet omogućit će vam da izvršavate MySQL/MariaDB upite izravno putem Linux naredbenog retka I spremite rezultat u datoteku za kasniji pregled (ovo je osobito korisno ako upit vraća mnogo zapisa).
Pogledajmo nekoliko jednostavnih primjera izvođenja MYSQL upita izravno iz naredbenog retka prije nego što prijeđemo na naprednije upite.
Postavljanje primjera baza podataka
Prije nego što se upustimo u naredbe, postavimo primjer baza podataka s kojima ćemo raditi kroz ovaj vodič, kako biste mogli pratiti i vježbati ove tehnike na vlastitom sustavu.
Kreiranje tecmintdb baze podataka
Prvo, stvorimo bazu podataka tecmintdb i tablicu tutorials:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"
Zatim, za kreiranje tablice baze podataka pod nazivom tutorials u bazi podataka tecmintdb, pokrenite sljedeću naredbu:
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
Provjerite je li podaci uneseni:
sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"
Izrada baze podataka zaposlenika
Sada ćemo kreirati složeniju bazu podataka employees s više povezanih tablica, ovo je baza podataka koju ćemo koristiti za naprednije primjere upita:
sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"
Kreirajte tablicu 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
Kreirajte tablicu 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
Kreirajte tablicu departments za složenije spajanje:
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
Napravite tablicu dept_emp za povezivanje 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
Provjerite je li sve ispravno postavljeno:
sudo mysql -u root -p -e "USE employees; SHOW TABLES;"
Sad ste postavili obje baze podataka sa primjerima podataka i možete pratiti sve primjere u ovom vodiču. Baza podataka tecmintdb savršena je za jednostavne upite, dok vam baza podataka employees omogućuje vježbanje složenijih operacija poput spajanja i agregacija.
Osnovno izvođenje upita
Da biste vidjeli sve baze podataka na svom poslužitelju, možete upotrijebiti sljedeću naredbu:
sudo mysql -u root -p -e "show databases;"
Zatim, za kreiranje tablice baze podataka pod nazivom tutorials u bazi podataka tecmintdb, pokrenite sljedeću naredbu:
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));"
Spremanje rezultata MySQL upita u datoteku
Koristit ćemo sljedeću naredbu i preusmjeriti izlaz na naredbu tee, nakon čega slijedi naziv datoteke u kojoj želimo spremiti izlaz.
Za ilustraciju ćemo koristiti bazu podataka pod nazivom employees i jednostavno spajanje između tablica employees i salaries. U vašem slučaju, samo upišite SQL upit između navodnika i pritisnite Enter.
Imajte na umu da ćete biti upitani da unesete lozinku za korisnika baze podataka:
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
Pogledajte rezultate upita pomoću naredbe cat.
cat queryresults.txt
Pokreni MySQL/MariaDB upite iz naredbenog retka
S rezultatima upita u običnim tekstualnim datotekama, možete lakše obrađivati zapise koristeći druge naredbene alate. Sad kad ste vidjeli osnove, istražimo neke naprednije tehnike koje će vaš rad s bazom podataka u naredbenom retku učiniti još učinkovitijim.
Formatiranje izlaza radi bolje čitljivosti
Zadani format tablice je izvrstan za pregled u terminalu, ali ponekad su potrebni različiti formati. Rezultate možete ispisati u vertikalnom formatu, što je posebno korisno kada radite s tablicama koje imaju mnogo stupaca:
sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"
\G na kraju prikazuje svaki redak okomito umjesto u tablici, pa umjesto da vidite zbijenu horizontalnu tablicu, dobivate nešto poput:
*************************** 1. row ***************************
emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
last_name: Facello
gender: M
hire_date: 1984-06-02
Izvoz u CSV format
Kada trebate uvesti rezultate upita u aplikacije za proračunske tablice ili druge alate, CSV format je vaš najbolji izbor:
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
Ovo prosljeđuje izlaz kroz sed kako bi se tabulatori zamijenili zarezima, stvarajući ispravnu CSV datoteku koja se može uredno otvoriti u Excel, LibreOffice Calc ili bilo kojem drugom softveru za proračunske tablice.
Pokretanje upita bez zahtjeva za lozinkom
Ako automatizirate zadatke baze podataka pomoću cron poslova ili skripti, ne želite svaki put ručno unositi lozinke, tu dolaze MySQL konfiguracijske datoteke.
Stvorite datoteku na ~/.my.cnf sa svojim vjerodajnicama:
[client]
user=root
password=your_password_here
Zatim ga osigurajte tako da ga samo vi možete pročitati:
chmod 600 ~/.my.cnf
Sada možete pokretati upite bez -p zastavice i bez prikazivanja upita za unos:
mysql -e "SHOW DATABASES;"
Samo zapamti, pohranjivanje lozinki u obične tekstualne datoteke ima sigurnosne implikacije, pa ovu metodu koristi samo na serverima kojima kontroliraš pristup, i razmotri korištenje sigurnijih metoda autentifikacije MySQL-a u produkcijskim okruženjima.
Izvršavanje složenih višerednih upita
Ponekad su vaši upiti previše složeni da bi ih se napisalo u jednoj naredbenoj liniji, posebno kada radite s višestrukim povezivanjima, podupitima ili složenim uvjetima.
Možete staviti svoj SQL u datoteku i izvršiti ga:
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
Sada to izvrši.
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
Ovaj pristup održava vaše upite organiziranima i višekratno upotrebljivima, a možete ih kontrolirati verzijom s git-om kao i svaki drugi kod.
Masovno obrađivanje više upita
Ako trebate izvršiti nekoliko povezanih upita i spremiti svaki rezultat zasebno, možete to skriptirati:
#!/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
Spremite ovo kao skriptu, učinite je izvršnom s chmod +x i imat ćete alat za višekratno korištenje batch upita.
Praćenje dugotrajnih upita
Kada pokrećete upite koji bi mogli potrajati, želite vidjeti napredak ili barem znati da još uvijek rade.
Kombinirajte svoj upit s ispisom statusa:
(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
Za još duže upite, pokrenite ih u pozadini i pratite MySQL popis procesa:
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"'
Ovo pokreće vaš upit u pozadini dok prikazuje popis procesa svakih 5 sekundi, tako da možete vidjeti da još uvijek radi i koliko je napredovalo.
Filtriranje i obrada rezultata
Kada imate rezultate upita u tekstualnoj datoteci, možete koristiti standardne Linux alate za daljnju obradu. Evo nekoliko korisnih obrazaca:
Prebrojite broj redaka rezultata (bez zaglavlja):
tail -n +2 queryresults.txt | wc -l
Izdvojite određene stupce koristeći awk:
awk '{print $1, $3}' queryresults.txt
Traži specifične obrasce u rezultatima:
grep -i "engineering" dept_distribution.txt
Sortiraj rezultate prema numeričkom stupcu:
tail -n +2 queryresults.txt | sort -k3 -n
Rukovanje posebnim znakovima i velikim skupovima podataka
Kada vaši podaci sadrže posebne znakove, tabove ili nove redove, zadani izlaz može postati neuredan, stoga koristite opcije --batch i --raw za čišći izlaz:
sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt
Za upite koji vraćaju milijune redaka, mogli biste naići na probleme s memorijom, umjesto da sve učitate u memoriju, prenosite rezultate u tok:
sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz
Opcija --quick govori MySQL-u da dohvaća retke jedan po jedan umjesto da pohranjuje cijeli rezultat u memoriju, a prosljeđivanje kroz gzip komprimira izlaz u letu, štedeći prostor na disku.
Izrada brzih sigurnosnih kopija baze podataka
Iako ovo tehnički nije pokretanje upita, možete koristiti slične tehnike iz naredbenog retka za brzo stvaranje sigurnosnih kopija baze podataka pomoću naredbe mysqldump.
sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz
Ili napravite sigurnosnu kopiju samo određenih tablica:
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Zakazivanje automatiziranih izvještaja
Kombinirajte sve što smo pokrivali kako biste stvorili automatizirana dnevna izvješća koristeći cron uz pomoć sljedećeg bash skripte.
#!/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"
Dodajte ga u cron da se pokreće svakodnevno u 6 ujutro:
0 6 * * * /usr/local/bin/generate_db_report.sh
Sažetak
Podijelili smo nekoliko Linux savjeta koji vam, kao sistemskom administratoru, mogu biti korisni kada je riječ o automatizaciji vaših svakodnevnih Linux zadataka ili njihovom lakšem izvršavanju.
Glavna poanta je da ne morate uvijek pokretati MySQL shell ili koristiti teške GUI alate za rad s bazama podataka; naredbeni redak vam pruža brzinu, mogućnost automatizacije i sposobnost integriranja operacija nad bazom podataka u postojeće shell skripte i radne procese.
Imate li još neki savjet koji biste htjeli podijeliti s ostatkom zajednice? Ako imate, molimo učinite to pomoću obrasca za komentare u nastavku. :