Как выполнять запросы MySQL через терминал Linux
Если вы отвечаете за управление сервером баз данных, вам может время от времени понадобиться выполнить запрос и тщательно его изучить. Это можно сделать в оболочке MySQL/MariaDB, но этот совет позволит вам выполнять запросы MySQL/MariaDB напрямую из командной строки Linux и сохранять вывод в файл для последующего изучения (это особенно полезно, если запрос возвращает много записей).
Давайте рассмотрим несколько простых примеров выполнения MYSQL запросов непосредственно из командной строки, прежде чем переходить к более сложным запросам.
Настройка примеров баз данных
Прежде чем мы перейдём к командам, давайте настроим примеры баз данных, с которыми мы будем работать в этой статье, чтобы вы могли следовать инструкциям и практиковаться в использовании этих методов в своей системе.
Создание базы данных andreyexdb
Сначала создадим базу данных andreyexdb и таблицу tutorials:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS andreyexdb;"
Затем, чтобы создать таблицу базы данных с именем tutorials в базе данных andreyexdb, выполните следующую команду:
sudo mysql -u root -p andreyexdb << '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
("Начало работы с Linux", "Andrey Ex", "2013-02-25"),
("Расширенные сценарии Bash", "Max Level", "2016-03-21"),
("Администрирование базы данных MySQL", "Alex Dell", "2018-11-23"),
...
EOF
Убедитесь, что данные были вставлены:
sudo mysql -u root -p -e "USE andreyexdb; SELECT * FROM tutorials;"
Создание базы данных сотрудников
Теперь давайте создадим более сложную employees базу данных с несколькими связанными таблицами. Эту базу данных мы будем использовать для более сложных примеров запросов.
sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"
Создайте таблицу 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'), ... EOF
Создайте таблицу 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'), ... EOF
Создайте таблицу departments для более сложных объединений:
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', 'Маркетинг'),
('d002', 'Финансы'),
('d003', 'Управление персоналом'),
('d004', 'Производство'),
('d005', 'Развитие'),
('d006', 'Управление качеством');
EOF
Создайте таблицу dept_emp для связи employees с 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'),
...
EOF
Убедитесь, что всё настроено правильно:
sudo mysql -u root -p -e «USE employees; SHOW TABLES;»
Теперь у вас есть обе базы данных с примерами данных, и вы можете следовать всем примерам из этого руководства. База данных andreyexdb идеально подходит для простых запросов, а база данных employees позволяет выполнять более сложные операции, такие как объединения и агрегирование.
Базовое выполнение запросов
Чтобы просмотреть все базы данных на вашем сервере, выполните следующую команду:
sudo mysql -u root -p -e "show databases;"
Затем, чтобы создать таблицу базы данных с именем tutorials в базе данных andreyexdb, выполните следующую команду:
sudo mysql -u root -p -e "USE andreyexdb; 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 в файл
Мы воспользуемся следующей командой и перенаправим вывод в команду tee, указав имя файла, в который мы хотим сохранить вывод.
Для наглядности мы будем использовать базу данных с именем employees и простое объединение таблиц employees и salaries. В вашем случае просто введите SQL-запрос в кавычках и нажмите Enter.
Обратите внимание, что вам будет предложено ввести пароль пользователя базы данных.
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
Просмотрите результаты запроса с помощью команды cat.
cat queryresults.txt
Выполнение запросов MySQL/MariaDB из командной строки
Благодаря тому, что результаты запросов сохраняются в виде обычных текстовых файлов, вы можете легко обрабатывать записи с помощью других утилит командной строки. Теперь, когда вы ознакомились с основами, давайте рассмотрим более продвинутые методы, которые сделают работу с базой данных из командной строки еще более эффективной.
Форматирование вывода для удобства чтения
Формат таблицы по умолчанию отлично подходит для просмотра в терминале, но иногда вам могут понадобиться другие форматы. Вы можете выводить результаты в вертикальном формате, что особенно удобно при работе с таблицами, содержащими множество столбцов.
sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"
\G в конце выводит каждую строку вертикально, а не в виде таблицы, поэтому вместо тесной горизонтальной таблицы вы увидите что-то вроде этого:
*************************** 1. row ***************************
emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
last_name: Facello
gender: M
hire_date: 1984-06-02
Экспорт в формат CSV
Если вам нужно импортировать результаты запроса в приложения для работы с электронными таблицами или другие инструменты, лучше всего использовать формат CSV:
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
Вывод проходит через sed, который заменяет табуляцию запятыми, в результате чего создается правильный CSV-файл, который без проблем открывается в Excel, LibreOffice Calc или любой другой программе для работы с электронными таблицами.
Выполнение запросов без запроса пароля
Если вы автоматизируете задачи базы данных с помощью заданий cron или скриптов, вам не нужно каждый раз вводить пароль вручную. В этом случае на помощь приходят файлы конфигурации MySQL.
Создайте файл ~/.my.cnf с вашими учётными данными:
[client] user=root password=your_password_here
Затем защитите его, чтобы только вы могли его читать:
chmod 600 ~/.my.cnf
Теперь вы можете выполнять запросы без флага -p и без запроса:
mysql -e "SHOW DATABASES;"
Просто помните, что хранение паролей в обычных текстовых файлах небезопасно, поэтому используйте этот подход только на серверах, доступ к которым вы контролируете, а для рабочих сред рассмотрите возможность использования более безопасных методов аутентификации MySQL.
Выполнение сложных многострочных запросов
Иногда запросы слишком сложны, чтобы их можно было записать в одну командную строку, особенно если речь идёт о нескольких объединениях, подзапросах или сложных условиях.
Вы можете поместить SQL-запрос в файл и выполнить его:
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
Теперь выполните это.
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
Такой подход позволяет упорядочить запросы и использовать их повторно, а также управлять их версиями с помощью git, как и любым другим кодом.
Пакетная обработка нескольких запросов
Если вам нужно выполнить несколько связанных запросов и сохранить каждый результат отдельно, вы можете написать для этого скрипт:
#!/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 "Выполняющийся запрос $((i+1))..."
mysql -u root -p -e "USE employees; ${QUERIES[$i]}" > "${FILENAMES[$i]}"
echo "Результаты, сохраненные в ${FILENAMES[$i]}"
done
Сохраните это как скрипт, сделайте его исполняемым с помощью chmod +x, и у вас будет инструмент для пакетных запросов, который можно использовать повторно.
Мониторинг длительных запросов
Когда вы выполняете запросы, которые могут занять некоторое время, вам нужно видеть прогресс или хотя бы знать, что они все еще выполняются.
Объедините запрос с выводом статуса:
(sudo mysql -u root -p -e "USE employees; SELECT COUNT(*) FROM large_table WHERE complex_condition;" && echo "Запрос выполнен в $(date)") | tee query_log.txt
Для более длинных запросов запускайте их в фоновом режиме и отслеживайте список процессов 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"'
Запрос выполняется в фоновом режиме, а список процессов обновляется каждые 5 секунд, чтобы вы могли видеть, что запрос все еще выполняется и какой прогресс достигнут.
Фильтрация и обработка результатов
Получив результаты запроса в текстовом файле, вы можете использовать стандартные инструменты Linux для их дальнейшей обработки. Вот несколько полезных шаблонов:
Подсчитайте количество строк с результатами (без заголовка):
tail -n +2 queryresults.txt | wc -l
Извлечение определённых столбцов с помощью команды awk:
awk '{print $1, $3}' queryresults.txt
Поиск определённых закономерностей в результатах:
grep -i «инженерия» dept_distribution.txt
Отсортируйте результаты по числовому столбцу:
tail -n +2 queryresults.txt | sort -k3 -n
Работа со специальными символами и большими наборами данных
Если ваши данные содержат специальные символы, табуляцию или переводы строки, вывод по умолчанию может быть неаккуратным. Чтобы сделать вывод более чистым, используйте параметры --batch и --raw:
sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt
При выполнении запросов, возвращающих миллионы строк, могут возникнуть проблемы с памятью. Вместо того чтобы загружать всё в память, передавайте результаты по потоку:
sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz
Опция --quick указывает MySQL извлекать строки по одной, а не буферизировать весь набор результатов. Передача данных через gzip сжимает вывод на лету, экономя место на диске.
Создание быстрых резервных копий базы данных
Хотя технически это не выполнение запросов, вы можете использовать аналогичные методы командной строки для быстрого создания дампов базы данных с помощью команды mysqldump.
sudo mysqldump -u root -p employees | gzip > employees_backup_$(дата +%Y%m%d).sql.gz
Или создайте резервную копию только для определённых таблиц:
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Планирование автоматических отчётов
Объедините всё, что мы рассмотрели, для создания автоматических ежедневных отчётов с помощью cron и следующего bash-скрипта.
#!/bin/bash
REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/var/reports/daily_stats_${REPORT_DATE}.txt"
{
echo "Отчет о статистике базы данных — ${REPORT_DATE}"
echo "=========================================="
echo
echo "Общее количество сотрудников:"
mysql -e "USE employees; SELECT COUNT(*) FROM employees;"
echo
echo "Новые сотрудники в этом месяце:"
mysql -e "USE employees; SELECT COUNT(*) FROM employees WHERE MONTH(hire_date) = MONTH(CURRENT_DATE()) AND YEAR(hire_date) = YEAR(CURRENT_DATE());"
echo
echo "Распределение по отделам:"
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_FILE"
Добавьте его в cron, чтобы он запускался ежедневно в 6 утра:
0 6 * * * /usr/local/bin/generate_db_report.sh
Выводы
Мы поделились несколькими советами по Linux, которые могут пригодиться вам как системному администратору, когда дело доходит до автоматизации повседневных задач в Linux или упрощения их выполнения.
Главный вывод заключается в том, что для работы с базами данных не всегда нужно запускать оболочку MySQL или использовать сложные инструменты с графическим интерфейсом. Командная строка обеспечивает скорость, возможности автоматизации и интеграции операций с базами данных в существующие сценарии оболочки и рабочие процессы.
Редактор: AndreyEx
