В процессе разработки иногда приходится выполнять часть операций вручную через командную строку. Данная статья содержит основные команды для взаимодействия с MySQL.
- Подключение к MySQL;
- Работа с базами данных;
- Работа с пользователями;
- Работа с данными;
- Экспорт и импорт дампа;
- Дампы на удаленных машинах.
Подключение к MySQL
Для подключения к локальной БД с пользователем «root» без пароля достаточно прописать:
mysql -u root
В примере выше не указывается ни пароль, ни хост. В таком виде она часто используется при разработке на сборках с единственным пользователем по умолчанию. К примеру, на OpenServer’e. Этого пользователя попросту лень менять :), но на продакшене, конечно, следует использовать отдельного пользователя с урезанными правами по отношению к root’у. В таких случаях при подключении необходимо указывать дополнительные параметры.
Подключение без указания конкретной БД:mysql -h IP_ADDRESS -u USER_NAME -p
mysql -h IP_ADDRESS -u USER_NAME DATABASE_NAME -p
- -h — хост, IP-адрес или домен;
- -u — имя пользователя;
- -p — пароль, вводится после нажатия ENTER;
Если были указаны корректные данные, то приглашение командной строки изменится на «mysql>» или что-то подобное (часто вместо самой MySQL используются её ответвления MariaDB и Percona).
Для отключения от MySQL используется команда:
EXIT
Или её синоним:
QUIT
Работа с базами данных
В исходниках программы старайтесь указывать все операторы баз данных в верхнем регистре, а всё остальное — наименования баз данных, таблиц, столбцов и т.д., в нижнем. Это повышает читабельность запросов в вашем коде. Например:
SELECT id, name FROM customer WHERE status = 100;
Вывод списка имеющихся БД:
SHOW DATABASES;
Каждая сборка MySQL имеет несколько стандартных баз данных, они хранятся рядом с вашими и выполняют технические функции, хотя с ними тоже можно работать.
- information_schema — хранилище метаданных о вашей базе данных, таких как наименование других баз данных, количестве столбцов, их таких и т.д.
- performance_schema — быстрая версия «information_schema«.
- mysql — еще одно хранилище служебной информации.
Выбор базы данных для работы:
USE database_name;
После выбора базы данных все команды по работе с данными будут относиться к ней и приглашение командной строки тоже обновится.
Создание новой базы данных с кодировкой «utf8_general_ci» (это не совсем кодировка, это скорее способ сравнения символов):
CREATE DATABASE new_database COLLATE utf8_general_ci;
Удаление базы данных:
DROP DATABASE new_database;
Работа с пользователями
Список пользователей и информация о них хранится в системной базе данных «mysql» в таблице «user». Из неё можно выбирать данные также как и из других баз, например:
SELECT USER FROM mysql.user;
При работе с разными проектами для каждого из них желательно создавать разных пользователей, так мы минимизируем возможные потери от взломов или плохо написанного кода. Создание пользователей состоит из двух этапов. Первый — добавление записи о пользователе в БД. Второй — наделение созданного пользователя полномочиями.
Создание пользователя:
CREATE USER 'new_user_name'@'localhost' IDENTIFIED BY 'new_user_password';
В приведенном примере пользователь будет иметь право подключаться с сервера «localhost». Если требуется дать пользователю право подключаться к базе данных с других хостов (удаленных), то вместо «localhost» необходимо указать символ процента — «%». Чтобы пользователь имел право подключаться и с «localhost» и с удаленных хостов, то необходимо создать две записи с «localhost» и с «%».
Обновление пароля пользователя:
SET PASSWORD FOR 'new_user_name'@'localhost' = PASSWORD('new_user_password_2');
Выделение привилегий пользователю:
GRANT ALL PRIVILEGES ON * . * TO 'new_user_name'@'localhost';
Пример выше содержит два важных элемента. Первый — список привилегий, в данном случае была использована настройка «ALL PRIVILEGES». Она заменяет список всех привилегий, т.е. «SELECT», «CREATE», «INSERT», «UPDATE», «DELETE», «DROP» и так далее. Второй момент — список баз данных и таблиц на которые выдаются указанные привилегии. В примере выше указаны все базы данных и все таблицы — две звездочки(*.*).
Если вы указываете привилегии не через команду «GRANT» (т.е. вручную), то для вступления изменений в силу необходимо оповестить об этом MySQL вот так:
FLUSH PRIVILEGES;
Вывод привилегий пользователя:
SHOW GRANTS FOR new_user_name;
Сброс всех привилегий пользователя:
REVOKE ALL PRIVILEGES ON *.* FROM 'new_user_name'@'localhost';
Удаление пользователя:
DROP USER new_user_name@localhost;
Работа с данными
Отображение списка таблиц:
SHOW TABLES;
Отображение структуры таблицы:
DESCRIBE TABLE_NAME;
Операции с данными выглядят и работают также, как и в исходниках программ.
Создание таблицы:CREATE TABLE customer ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age TINYINT UNSIGNED NOT NULL );
DROP TABLE TABLE_NAME;
SELECT name, address FROM client WHERE TYPE = "verified";
INSERT INTO customer (name, age) VALUES ('John Connor', 15);
UPDATE customer SET age = 20 WHERE name = 'John Connor';
Экспорт и импорт дампа
Для создания дампа используется отдельная утилита — mysqldump. А импортировать дампы можно через утилиту mysqlimport или основным клиентом — mysql. У всех этих утилит множество настроек с которыми лучше ознакомиться в документации.
Экспорт всей базы данных:
mysqldump -h IP_ADDRESS -u USER_NAME -p DATABASE_NAME > dump.sql
Экспорт структуры базы данных (т.е. без данных):
mysqldump -h IP_ADDRESS -u USER_NAME -p --no-data DATABASE_NAME < dump.sql
Экспорт одной таблицы базы данных с данными:
mysqldump -h IP_ADDRESS -u USER_NAME -p DATABASE_NAME TABLE_NAME > dump.sql
Экспорт структуры одной таблицы базы данных (т.е. без данных):
mysqldump -h IP_ADDRESS -u USER_NAME -p DATABASE_NAME TABLE_NAME --no-data < dump.sql
Импорт базы данных через mysql:
mysql -h IP_ADDRESS -u USER_NAME -p DATABASE_NAME < dump.sql
Импорт базы данных через mysql с выводом результатов выполнения запросов:
SOURCE path/TO/dump/dump.sql
Иногда бывает необходимо посмотреть запрос на создание выбранной таблицы прямо в консоли, делается это вот так:
SHOW CREATE TABLE TABLE_NAME;
Дампы на удаленных машинах
Экспорт дампа из удаленной БД на локальную машину:
ssh USER_NAME@HOST_IP mysqldump -u DB_USER -p DB_NAME > dump.sql
Импорт дампа в БД на удаленной машине с локальной машины:
ssh USER_NAME@HOST_IP mysql -u DB_USER -p DB_NAME < dump.sql
Другие команды
Вывод общей информации о базе данных:
STATUS
Отображение списка выполняемых процессов:
SHOW PROCESSLIST;
Полезные ссылки
- dev.mysql.com/doc/refman/8.0/en — официальная документация.
- dev.mysql.com/doc/refman/8.0/en/mysqldump.html — официальная документация по утилите mysqldump;
- itreviewchannel.ru/zagruzka-i-skachivanie-fajlov-po-ssh — загрузка и скачивание файлов по ssh.
Добавить комментарий
Для отправки комментария вам необходимо авторизоваться.