Командная строка MySQL

Небольшая и простая памятка по работе с сервером MySQL из командной строки.

Небольшая и простая памятка по работе с сервером MySQL из командной строки.

Подключение к MySQL через консоль

Получить информацию об установленной версии MySQL

mysql -V

Для подключения к mysql в консоли наберите команду

mysql -h you_sql_server -u user_name -p
  • h — хост c MySQL. Если подключаемся с локальной машины, параметр можно опустить
  • u — имя пользователя MySQL (root или другой пользователь MySQL)
  • p — пароль, который будет предложено ввести после нажатия enter

Приглашение командной строки изменится, это значит, сервер MySQL ждёт от вас команд.

mysql>

Запросы должны оканчиваться точкой с запятой. Длинные запросы удобно разбивать enter-ом для перехода на новую строку, а после полного написания запроса поставить точку с запятой и выполнить его.

Для отключения от MySQL нужно написать exit или (в unix-системах) нажать комбинацию клавиш ctrl+с.

Для вывода всех баз данных на сервере используйте команду show databases.

SHOW DATABASES;

Выберите нужную базу данных командой use.

USE db_name;

Теперь можно вводить запросы.

Чтобы подключиться к MySQL и сразу выбрать нужную базу

mysql -u user_name -h host_name db_name -p

Полезные команды MySQL

Показать все таблицы выбранной базы данных в текущей БД.

SHOW TABLES;

Показать все таблицы базы данных db_name.

SHOW TABLES FROM db_name;

Показать список столбцов в таблице table_name в текущей БД

SHOW COLUMNS FROM table_name;

Показать список столбцов в таблице table_name из БД db_name

SHOW COLUMNS FROM table_name FROM db_name;

Вывести структуру нужной таблицы

DESCRIBE table_name;

Показать структуру таблицы, будет выведен sql-запрос на её создание через «CREATE TABLE».

SHOW CREATE TABLE table_name;

Вывести значения системных переменных.

SHOW VARIABLES;

Показать список выполняющихся в настоящий момент запросов.

SHOW PROCESSLIST;

Общая статистика MySQL.

SHOW STATUS;

Статистика по всем таблицам в базе db_name.

SHOW TABLE STATUS FROM db_name;

Что бы выполнять запросы к MySQL из консоли, не всегда требуется предварительно подключаться к mysql. Параметр -e позволяет исполнить команду, вывести результат на экран, после чего отключиться от сервера MySQL. Например, можно вывести список таблиц базы данных.

mysql -uroot -e 'SHOW TABLES' db_name && echo done

Управление базами данных

Создание базы данных из консоли сервера

$ mysqladmin -u root -p create db_name

Удаление базы данных из консоли сервера

mysqladmin -u root -p drop db_name

Создание базы данных db_name из консоли MySQL

CREATE DATABASE db_name COLLATE utf8_general_ci;

Удаление базы данных db_name из консоли MySQL

DELETE DATABASE db_name;

Замена в поле одной подстроки на другую

UPDATE table SET field=replace(field,'original string','new string');

Работа с пользователями

Вывести список пользователей

SELECT User,Host FROM mysql.user;

Показать список прав пользователя user

SHOW GRANTS FOR user_name FROM db_name;

Создать нового пользователя

CREATE USER 'user'@'host' IDENTIFIED BY'password';

host — здесь имя хоста, доменное имя или ip адрес, с которого пользователь сможет подключаться к серверу, например user@localhost.

Чтобы создаваемый пользователь смог подключаться к серверу MySQL с любого IP адреса или хоста (за исключением localhost), можно использовать символ процента, вот так

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Чтобы разрешрешить пользователю подключаться вообще со всех хостов, придётся создать для него две учётные записи.

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Добавить указанные привилегии для таблиц БД db_name пользователю user@localhost

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX 
ON db_name.* TO 'user'@'localhost';

Чтобы изменить права пользователю, иногда удобно сначала сбросить все права

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';

А затем установить нужные с помощью GRANT, как было показано выше.

Добавить все привилегии для таблиц БД db_name пользователю user@localhost

GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';

Удаление привилегий пользователя user@localhost для БД db_name:

REVOKEALLON db_name.* FROM 'user'@'localhost';

Сделать из пользователя суперпользователя и дать полный доступ ко всем БД на сервере

GRANT ALL ON *.* TO 'user'@'localhost';

Удалить пользователя user@localhost

DROP USER user@localhost;

Перезагрузить привилегии

FLUSH PRIVILEGES;

Изменить пароль пользователя в консоли MySQL

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('new_password');

FLUSH PRIVILEGES;

или так

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='user_name';

Установить пароль для пользователя user в консоли сервера.

$ mysqladmin -uuser_name password'password'

Исправление и оптимизация баз данных в MySQL

Чтобы исправить ошибки в поврежденной таблице, в консоли MySQL выполните:

> REPAIR TABLE table_name;

Если же в БД много поврежденных таблиц можно воспользоваться командой mysqlcheck.

Проверить db_name на ошибки.

$ mysqlcheck -p db_name

Восстановление и оптимизация всех БД

$ mysqlcheck -Aor -p

Аргументы:

  • p – использовать пароль
  • -A, —all-databases – проверять все базы данных
  • -o, —optimize – оптимизировать
  • -r, —repair – восстанавливать повреждённые
  • —auto-repair – автоматическое восстановление

Бэкап MySQL из командной строки

Экспорт базы MySQL

$ mysqldump -u username -p db_name > dump.sql

Дамп нескольких баз

$ mysqldump -u username -p -B db_name1 db_name2 > dump.sql

Дамп всех баз на сервере

$ mysqldump -u username -p -A > dump.sql

Дамп только структуры базы, без данных

$ mysqldump -u username -p --no-data db_name > database.sql

Дамп структуры одной таблицы mysql, без данных:

$ mysqldump -u username -p -h host db_name table_name --no-data > /path/dump.sql

Развернуть базу данных MySQL из дампа

$ mysql -u username -p db_name < dump_to_restore.sql