В процессе разработки иногда приходится выполнять часть операций вручную через командную строку. Данная статья содержит основные команды для взаимодействия с 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.
Комментарии (0)