Работа с MySQL через командную строку

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