Hogyan futtassunk MySQL lekérdezéseket a Linux parancssorból

Ha egy adatbázis-szerver kezeléséért felelsz, előfordulhat, hogy időnként le kell futtatnod egy lekérdezést és alaposan ellenőrizned kell azt. Bár ezt megteheted a MySQL/MariaDB shellből is, ez a tipp lehetővé teszi, hogy MySQL/MariaDB lekérdezéseket közvetlenül a Linux parancssorból futtass, ÉS az eredményt fájlba mentsd későbbi áttekintés céljából (ez különösen akkor hasznos, ha a lekérdezés sok rekordot ad vissza).

Nézzünk meg néhány egyszerű példát arra, hogyan futtathatunk MYSQL lekérdezéseket közvetlenül a parancssorból, mielőtt áttérnénk egy bonyolultabb lekérdezésre.

Példaadatbázisok beállítása

Mielőtt belemennénk a parancsokba, állítsuk be azokat a példaadatbázisokat, amelyekkel a teljes útmutató során dolgozni fogunk, így követni tudod a lépéseket és gyakorolhatod ezeket a technikákat a saját rendszereden.

A tecmintdb adatbázis létrehozása

Először hozzuk létre a tecmintdb adatbázist és a tutorials táblát:

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

Ezután a tecmintdb adatbázisban a tutorials nevű adatbázistábla létrehozásához futtassa az alábbi parancsot:

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

Ellenőrizze, hogy az adatok be lettek-e szúrva:

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

Az alkalmazottak adatbázisának létrehozása

Most hozzunk létre egy összetettebb employees adatbázist több kapcsolódó táblával, ezt az adatbázist fogjuk használni a haladóbb lekérdezési példákhoz:

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

Hozd létre az employees táblát:

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

Hozd létre a salaries táblát:

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

Hozd létre a departments táblát a bonyolultabb összekapcsolásokhoz:

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

Hozza létre a dept_emp táblát, hogy összekapcsolja a employees és a departments táblákat:

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

Ellenőrizze, hogy minden megfelelően van-e beállítva:

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

Most már mindkét adatbázist beállítottad mintaadatokkal, és követheted az útmutató összes példáját. A tecmintdb adatbázis tökéletes egyszerű lekérdezésekhez, míg az employees adatbázis lehetőséget ad bonyolultabb műveletek, például összekapcsolások és aggregációk gyakorlására.

Alapvető lekérdezés végrehajtása

Az összes adatbázis megtekintéséhez a szerverén a következő parancsot adhatja ki:

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

Ezután a tecmintdb adatbázisban a tutorials nevű adatbázistábla létrehozásához futtassa az alábbi parancsot:

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

MySQL lekérdezés eredményeinek mentése fájlba

A következő parancsot fogjuk használni, és a kimenetet a tee parancshoz irányítjuk, majd megadjuk a fájlnevet, ahová el szeretnénk menteni a kimenetet.

Illusztrációként egy employees nevű adatbázist fogunk használni, valamint egy egyszerű összekapcsolást az employees és a salaries táblák között. A saját esetében egyszerűen írja be az SQL lekérdezést a idézőjelek közé, majd nyomja meg az Entert.

Vegye figyelembe, hogy meg kell adnia az adatbázis felhasználó jelszavát:

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

Tekintse meg a lekérdezés eredményeit a cat parancs segítségével.

cat queryresults.txt

Futtass MySQL/MariaDB lekérdezéseket parancssorból

A lekérdezés eredményeit egyszerű szöveges fájlokban tárolva könnyebben feldolgozhatja a rekordokat más parancssori eszközökkel. Most, hogy megismerted az alapokat, nézzünk meg néhány fejlettebb technikát, amelyek még hatékonyabbá teszik a parancssori adatbázis-kezelést.

Az eredmények formázása a jobb olvashatóság érdekében

Az alapértelmezett táblázatformátum nagyszerű a terminálban való megtekintéshez, de néha különböző formátumokra van szükség. Az eredményeket függőleges formátumban is kiadhatja, ami különösen hasznos, ha sok oszlopot tartalmazó táblázatokkal dolgozik:

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

A \G a végén minden sort függőlegesen jelenít meg a táblázat helyett, így a zsúfolt vízszintes táblázat helyett ilyesmit kapsz:

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

Exportálás CSV formátumba

Ha lekérdezési eredményeket kell importálnia táblázatkezelő alkalmazásokba vagy más eszközökbe, a CSV formátum a legjobb választás:

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

Ez a kimenetet a sed programon keresztül vezeti, hogy a tabulátorokat vesszőkre cserélje, létrehozva egy megfelelő CSV fájlt, amely tisztán megnyitható Excel-ben, LibreOffice Calc-ban vagy bármely más táblázatkezelő szoftverben.

Lekérdezések futtatása jelszóbekérés nélkül

Ha adatbázis-feladatokat automatizálsz cron feladatokkal vagy skriptekkel, nem szeretnél minden alkalommal manuálisan jelszót megadni, és erre való a MySQL konfigurációs fájlok használata.

Hozz létre egy fájlt a ~/.my.cnf helyen a hitelesítő adataiddal:

[client]
user=root
password=your_password_here

Ezután biztosítsd úgy, hogy csak te tudd olvasni:

chmod 600 ~/.my.cnf

Most már futtathatsz lekérdezéseket a -p kapcsoló nélkül, és anélkül, hogy bármilyen kérést kapnál:

mysql -e "SHOW DATABASES;"

Csak ne feledd, hogy a jelszavak sima szöveges fájlokban történő tárolása biztonsági kockázattal jár, ezért csak olyan szervereken használd ezt a módszert, ahol te ellenőrzöd a hozzáférést, és a termelési környezetben érdemes a MySQL biztonságosabb hitelesítési módszereit alkalmazni.

Bonyolult, több soros lekérdezések végrehajtása

Néha a lekérdezéseid túl bonyolultak ahhoz, hogy egyetlen parancssorban írd őket, különösen, ha több összekapcsolást, al-lekérdezést vagy összetett feltételeket kezelsz.

Az SQL parancsokat egy fájlba helyezheted, majd végrehajthatod őket:

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

Most hajtsd végre.

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

Ez a megközelítés rendezetten és újrahasznosíthatóan tartja a lekérdezéseidet, és a git segítségével verziókezelheted őket, akárcsak bármely más kódot.

Tömeges feldolgozás több lekérdezésre

Ha több kapcsolódó lekérdezést kell futtatnod, és az eredményeket külön-külön el szeretnéd menteni, létrehozhatsz egy szkriptet:

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

Mentsd el ezt egy szkriptként, tedd futtathatóvá a chmod +x paranccsal, és máris van egy újrahasználható batch lekérdező eszközöd.

Hosszú futásidejű lekérdezések figyelése

Amikor olyan lekérdezéseket futtatsz, amelyek eltarthatnak egy ideig, látni szeretnéd a folyamatot, vagy legalább tudni, hogy még mindig működnek.

Kombinálja a lekérdezést az állapotkimenettel:

(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

Még hosszabb lekérdezések esetén futtassa őket a háttérben, és figyelje a MySQL folyamatlistát:

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"'

Ez a háttérben futtatja a lekérdezésed, miközben 5 másodpercenként megjeleníti a folyamatlistát, így láthatod, hogy még mindig dolgozik, és mennyi előrehaladást ért el.

Eredmények szűrése és feldolgozása

Miután lekérdezési eredményeket kaptál egy szöveges fájlban, standard Linux-eszközöket használhatsz azok további feldolgozására. Íme néhány hasznos minta:

Számolja meg az eredmény sorainak számát (a fejlécet nem számítva):

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

Specifikus oszlopok kinyerése awk segítségével:

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

Keresés konkrét mintákra az eredményekben:

grep -i "engineering" dept_distribution.txt

Eredmények rendezése egy numerikus oszlop szerint:

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

Speciális karakterek és nagyméretű adathalmazok kezelése

Ha az adataid speciális karaktereket, tabulátorokat vagy új sorokat tartalmaznak, az alapértelmezett kimenet rendezetlenné válhat, ezért a tisztább kimenet érdekében használd a --batch és --raw opciókat:

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

Nagyszámú sorokat visszaadó lekérdezések esetén előfordulhat, hogy memóriaproblémákba ütközöl; ahelyett, hogy mindent betöltenél a memóriába, folyamatosan olvasd a találatokat:

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

A --quick opció azt mondja a MySQL-nek, hogy egyesével kérje le a sorokat ahelyett, hogy az egész eredményhalmazt pufferelné, és a gzip-en keresztüli csőbe vezetés a kimenetet azonnal tömöríti, így helyet takarít meg a lemezen.

Gyors adatbázismentések létrehozása

Bár ez technikailag nem lekérdezések futtatása, hasonló parancssori technikákat használhatsz gyors adatbázis-mentések létrehozására a mysqldump parancs segítségével.

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

Vagy mentheted csak az egyes táblákat:

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

Automatizált jelentések ütemezése

Kombináljuk mindazt, amit eddig áttekintettünk, hogy automatikus napi jelentéseket készítsünk a cron segítségével a következő bash szkript felhasználásával.

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

Add hozzá a cronhoz, hogy minden nap 6-kor fusson:

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

Összefoglaló

Megosztottunk néhány Linux-tippet, amelyeket rendszeradminisztrátorként hasznosnak találhatsz a napi Linux-feladatok automatizálásához vagy azok könnyebb elvégzéséhez.

A legfontosabb üzenet itt az, hogy nem mindig kell elindítanod a MySQL shellt vagy nehéz GUI eszközöket használnod az adatbázisaid kezeléséhez; a parancssor gyorsaságot, automatizálási lehetőségeket és azt a képességet adja, hogy az adatbázis-műveleteket integrálhasd a meglévő shell szkripteidbe és munkafolyamataidba.

Van még más tippje, amit szeretne megosztani a többi közösségi taggal? Ha igen, kérjük, tegye meg az alábbi megjegyzés űrlap segítségével. :