Jak uruchamiać zapytania MySQL z linii poleceń Linuksa

Jeśli jesteś odpowiedzialny za zarządzanie serwerem baz danych, od czasu do czasu może być konieczne uruchomienie zapytania i dokładne jego sprawdzenie. Chociaż można to zrobić z poziomu konsoli MySQL/MariaDB, ten poradnik pozwoli Ci uruchamiać zapytania MySQL/MariaDB bezpośrednio z linii poleceń Linuxa ORAZ zapisywać wyniki do pliku do późniejszej analizy (jest to szczególnie przydatne, gdy zapytanie zwraca wiele rekordów).

Przyjrzyjmy się kilku prostym przykładom uruchamiania zapytań MYSQL bezpośrednio z wiersza poleceń, zanim przejdziemy do bardziej zaawansowanych zapytań.

Konfigurowanie przykładowych baz danych

Zanim przejdziemy do poleceń, skonfigurujmy przykładowe bazy danych, z którymi będziemy pracować w tym przewodniku, abyś mógł śledzić i ćwiczyć te techniki na własnym systemie.

Tworzenie bazy danych tecmintdb

Najpierw utwórzmy bazę danych tecmintdb i tabelę tutorials:

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

Następnie, aby utworzyć tabelę bazy danych o nazwie tutorials w bazie danych tecmintdb, uruchom poniższe polecenie:

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

Sprawdź, czy dane zostały wstawione:

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

Tworzenie bazy danych pracowników

Teraz stwórzmy bardziej złożoną bazę danych employees z wieloma powiązanymi tabelami, będzie to baza danych, której użyjemy do bardziej zaawansowanych przykładów zapytań:

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

Utwórz tabelę 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

Utwórz tabelę 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

Utwórz tabelę departments do bardziej złożonych połączeń:

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

Utwórz tabelę dept_emp, aby połączyć employees z 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

Sprawdź, czy wszystko jest poprawnie ustawione:

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

Teraz masz skonfigurowane obie bazy danych z przykładowymi danymi i możesz śledzić wszystkie przykłady w tym przewodniku. Baza danych tecmintdb jest idealna do prostych zapytań, natomiast baza danych employees pozwala ćwiczyć bardziej zaawansowane operacje, takie jak łączenia i agregacje.

Podstawowe wykonywanie zapytań

Aby wyświetlić wszystkie bazy danych na swoim serwerze, możesz wydać następujące polecenie:

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

Następnie, aby utworzyć tabelę bazy danych o nazwie tutorials w bazie danych tecmintdb, uruchom poniższe polecenie:

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

Zapis wyników zapytań MySQL do pliku

Użyjemy następującego polecenia i przekażemy jego wynik do polecenia tee, a następnie do pliku, w którym chcemy przechować wynik.

Dla ilustracji użyjemy bazy danych o nazwie employees oraz prostego połączenia między tabelami employees i salaries. W swoim przypadku po prostu wpisz zapytanie SQL między cudzysłowami i naciśnij Enter.

Zauważ, że zostaniesz poproszony o wprowadzenie hasła dla użytkownika bazy danych:

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

Wyświetl wyniki zapytania za pomocą polecenia cat.

cat queryresults.txt

Uruchamianie zapytań MySQL/MariaDB z wiersza poleceń

Dzięki wynikom zapytań w plikach tekstowych można łatwiej przetwarzać rekordy za pomocą innych narzędzi w wierszu poleceń. Teraz, gdy poznałeś podstawy, przyjrzyjmy się bardziej zaawansowanym technikom, które sprawią, że praca z bazą danych w wierszu poleceń będzie jeszcze bardziej wydajna.

Formatowanie wyników dla lepszej czytelności

Domyślny format tabeli jest świetny do przeglądania w terminalu, ale czasami potrzebujesz innych formatów. Możesz wyświetlać wyniki w formacie pionowym, co jest szczególnie przydatne przy pracy z tabelami, które mają wiele kolumn:

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

\G na końcu wyświetla każdy wiersz pionowo zamiast w tabeli, więc zamiast widzieć ciasną poziomą tabelę, otrzymujesz coś takiego:

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

Eksportowanie do formatu CSV

Gdy potrzebujesz zaimportować wyniki zapytania do arkuszy kalkulacyjnych lub innych narzędzi, format CSV będzie najlepszym wyborem:

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

To przesyła wynik przez sed w celu zamiany tabulatorów na przecinki, tworząc prawidłowy plik CSV, który otwiera się bez problemu w Excel, LibreOffice Calc lub w dowolnym innym oprogramowaniu arkusza kalkulacyjnego.

Uruchamianie zapytań bez monitów o hasło

Jeśli automatyzujesz zadania bazy danych za pomocą zadań cron lub skryptów, nie chcesz za każdym razem ręcznie wprowadzać haseł, w tym właśnie pomagają pliki konfiguracyjne MySQL.

Utwórz plik w ~/.my.cnf z danymi logowania:

[client]
user=root
password=your_password_here

Następnie zabezpiecz to, aby tylko ty mogłeś to przeczytać:

chmod 600 ~/.my.cnf

Teraz możesz uruchamiać zapytania bez użycia flagi -p i bez wyświetlania monitu:

mysql -e "SHOW DATABASES;"

Pamiętaj, że przechowywanie haseł w plikach tekstowych niesie ze sobą zagrożenia bezpieczeństwa, więc stosuj tę metodę tylko na serwerach, do których masz kontrolowany dostęp, i rozważ użycie bardziej bezpiecznych metod uwierzytelniania MySQL w środowiskach produkcyjnych.

Wykonywanie złożonych zapytań wieloliniowych

Czasami twoje zapytania są zbyt skomplikowane, aby napisać je w jednej linii poleceń, szczególnie gdy masz do czynienia z wieloma łączeniami, podzapytaniami lub skomplikowanymi warunkami.

Możesz umieścić swój SQL w pliku i go wykonać:

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 wykonaj.

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

To podejście pozwala utrzymać Twoje zapytania w porządku i nadać im możliwość ponownego użycia, a także możesz kontrolować ich wersje za pomocą Git, tak jak w przypadku każdego innego kodu.

Przetwarzanie wsadowe wielu zapytań

Jeśli musisz uruchomić kilka powiązanych zapytań i zapisać każdy wynik osobno, możesz to zautomatyzować w skrypcie:

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

Zapisz to jako skrypt, nadaj mu prawa do wykonywania poleceniem chmod +x, i masz wielokrotnego użytku narzędzie do wykonywania zapytań wsadowych.

Monitorowanie długotrwałych zapytań

Kiedy uruchamiasz zapytania, które mogą potrwać chwilę, chcesz widzieć postęp lub przynajmniej wiedzieć, że nadal działają.

Połącz zapytanie z wyjściem statusu:

(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

Dla jeszcze dłuższych zapytań uruchamiaj je w tle i monitoruj listę procesów 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"'

To uruchamia twoje zapytanie w tle, jednocześnie wyświetlając listę procesów co 5 sekund, dzięki czemu możesz zobaczyć, że nadal działa i jaki zrobiło postęp.

Filtrowanie i przetwarzanie wyników

Gdy już masz wyniki zapytań w pliku tekstowym, możesz użyć standardowych narzędzi Linuksa do dalszego przetwarzania. Oto kilka przydatnych wzorców:

Policz liczbę wierszy wyników (z wyłączeniem nagłówka):

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

Wyodrębnij określone kolumny przy użyciu awk:

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

Wyszukaj określone wzorce w wynikach:

grep -i "engineering" dept_distribution.txt

Sortuj wyniki według kolumny numerycznej:

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

Obsługa znaków specjalnych i dużych zbiorów danych

Gdy Twoje dane zawierają znaki specjalne, tabulatory lub nowe linie, domyślny wynik może być nieczytelny, więc użyj opcji --batch i --raw, aby uzyskać czyściejszy wynik:

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

W przypadku zapytań zwracających miliony wierszy możesz napotkać problemy z pamięcią; zamiast ładować wszystko do pamięci, przesyłaj wyniki strumieniowo:

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

Opcja --quick informuje MySQL, aby pobierał wiersze jeden po drugim zamiast buforować cały zestaw wyników, a przesyłanie przez gzip kompresuje dane na bieżąco, oszczędzając miejsce na dysku.

Tworzenie szybkich kopii zapasowych bazy danych

Chociaż technicznie rzecz biorąc nie jest to uruchamianie zapytań, można użyć podobnych technik w wierszu poleceń, aby szybko tworzyć zrzuty bazy danych za pomocą polecenia mysqldump.

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

Lub wykonaj kopię zapasową tylko wybranych tabel:

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

Harmonogram raportów automatycznych

Połącz wszystko, czego się nauczyliśmy, aby tworzyć automatyczne codzienne raporty za pomocą cron przy pomocy następującego skryptu 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"

Dodaj to do crona, aby uruchamiało się codziennie o 6 rano:

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

Podsumowanie

Podzieliliśmy się kilkoma wskazówkami dotyczącymi Linuksa, które jako administrator systemu możesz uznać za przydatne przy automatyzacji codziennych zadań w Linuksie lub ich łatwiejszym wykonywaniu.

Kluczową kwestią jest to, że nie zawsze musisz uruchamiać powłokę MySQL ani korzystać z ciężkich narzędzi GUI, aby pracować z bazami danych; wiersz poleceń daje Ci szybkość, możliwości automatyzacji oraz możliwość integracji operacji bazodanowych z istniejącymi skryptami powłoki i przepływami pracy.

Czy masz inne wskazówki, którymi chciałbyś się podzielić z resztą społeczności? Jeśli tak, prosimy, zrób to, korzystając z formularza komentarza poniżej. :