Как в Windows подключиться к MySQL в командной строке

Делать запросы к базам данных MySQL можно не только через PHP. В комплекте с MySQL поставляется консольный клиент для подключения в серверу MySQL. Консольный — это означает, что у программы нет оконного интерфейса, а есть только интерфейс командной строки, в котором обращение к базам данных выполняется с помощью SQL запросов.

Команды для подключения к MySQL в командной строке, а уж тем более сами SQL запросы идентичны и для MariaDB. В дальнейшем для краткости я буду писать «MySQL», но везде я имею в виду «MySQL или MariaDB», поскольку в данном случае разницы между ними нет.

Подключившись через командную строку к СУБД MySQL можно выполнять самые разные задачи: просматривать базы данных и их таблицы, отправлять запросы к базам данных и получать результаты выполнения этих запросов.

Для подключения к серверу MySQL используется команда клиентская программа, которая называется mysql.exe. Её нужно запустить, но сделать нужно не двойным кликом, как обычную программу, а из командной строки.

Начнём с того, что нажмём сочtтание клавиш Win+r и выполним

Откроется командная строка.

Теперь нам нужно перейти в папку с файлом mysql.exe. Эта папка bin в директории, где установлена ваша СУБД MySQL. К примеру, у меня MariaDB, которая установлена в папке C:\Server\bin\mariadb\. Следовательно, интересующий нас файл находится в каталоге C:\Server\bin\mariadb\bin\. Для перехода в этот каталог используется команда cd следующим образом:

В этой команде замените C:\Server\bin\mariadb\bin\ на правильный для вашей системы путь

Теперь запустим файл mysql.exe. Необязательно указывать расширение файла .exe — система сама догадается, что мы имеем ввиду. Также нам нужно использовать опцию -u и -p. После первой опции нужно указать имя пользователя — root. После второй опции — пароль для пользователя. В моём случае пароль не установлен, поэтому я запускаю без опции -p:

Может показаться, что мало что изменилось, но новое приглашение командной строки

говорит о том, что мы подключены к серверу MySQL. Точнее, в моём случае мы подключены к серверу MariaDB.

Чтобы посмотреть список баз данных, введём команду:

Чтобы последующие запросы отправлялись к определённой базе данных (например, к test), используется команда USE:

Чтобы посмотреть таблицы в выбранной базе данных запустите команду:

Создадим какую-нибудь таблицу:

И вновь посмотрим содержимое базы данных test:

Если вы уже работали с базами данных MySQL через PHP, то вы можете использовать любые запросы, которые используете в PHP.

Чтобы выйти выполните команду

или нажмите Ctrl+d.

Для продолжения знакомства рекомендуется изучить материал:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На этом всё. Но вы можете помочь проекту. Даже небольшая сумма поможет нам писать больше полезных статей.

Если статья помогла или понравилась, пожалуйста поделитесь ей в соцсетях.

Записки IT специалиста

Технический блог специалистов ООО»Интерфейс»

• Главная
• Основы администрирования MySQL при помощи командной строки

Основы администрирования MySQL при помощи командной строки

Популярная система управления базами данных MySQL широко применяется для различных нужд, в первую очередь как стандарт де-факто в области интернет хостинга. Не менее широко распространен пакет для управления данной СУБД — phpMyAdmin. Без сомнения, это хороший, удобный продукт, но очень часто случается так, что навыки работы с этим продуктом полностью заменяют навыки работы с самой СУБД. Поэтому в данном материале мы решили познакомить наших читателей с тем, как выполнять основные задачи администрирования MySQL из командной строки.

Как могли заметить наши постоянные читатели, мы последовательно выступаем против применения начинающими разного рода панелей управления, какими бы удобными и распространенными они не были. В тоже время мы не отрицаем их существование и сами с удовольствием используем в повседневной деятельности.

Но есть одно большое отличие: специалист, умеющий работать с командной строкой, используя панель, не заменяет ей умение работать с продуктом, а только облегчает себе выполнение повседневных задач. А новичок, привыкший совершать все действия в панели, в случае ее недоступности впадает в тихую панику, ведь теперь нужно вводить какие-то «заклинания» в эту непонятную черную консоль.

На самом деле работать с СУБД на уровне командной строки совсем несложно, а часть задач по администрированию проще и удобнее выполнять именно в ней. Сразу оговоримся, под администрированием мы подразумеваем именно администрирование сервера СУБД, а не самих баз данных. С ними, конечно, тоже можно работать из командной строки, но лучше использовать для этого более подходящие инструменты.

Управление базами данных и пользователями в MySQL

Если танцевать следует начинать от печки, то работу с СУБД следует начинать с создания баз данных и пользователей этих баз. Задачи по сути своей простые и прекрасно, а главное — просто, решаются из консоли. Для работы с сервером MySQL предназначена одноименная утилита mysql, работа с которой происходит в интерактивном режиме, поэтому вначале подключимся к серверу:

Где ключ -u задает имя пользователя, а -p указывает на аутентификацию по паролю, синтаксис команды позволяет указать пароль, вписав его без пробелов сразу после ключа, но в этом случае он сохранится в истории команд, что не совсем хорошо, поэтому лучше ввести пароль интерактивно. Выполнив эту команду, мы окажемся в среде MySQL, на что указывает изменившееся приглашение командной строки.

Работа в данной среде имеет свои особенности: каждая команда должна завершаться символом ; или \g, о чем, кстати, написано в первой строке приветствия. Выход из данного режима осуществляется командой:

Сразу об одной очень распространенной ошибке: забыли поставить точку с запятой в конце команды. Что делать? Ничего страшного, просто добейте недостающие символы в следующей строке.

Для начала посмотрим список баз:

Как говорится, без лишних подробностей, но для большинства административных задач этого достаточно:

Узнать список пользователей немного сложнее, он хранится в виде таблицы служебной базы mysql, поэтому пишем небольшой запрос:

Команда select выбирает указанные колонки user, host, опция from указывает откуда мы их выбираем, а именно из таблицы user базы данных mysql.

Первая колонка указывает пользователя, вторая — хост, на котором данному пользователю разрешены подключения, % — обозначает любое значение. Обратите внимание, что если ivanov имеет в настройках localhost, то при подключении к mysql по адресу 127.0.0.1 он не сможет получить доступ, это нужно учитывать, когда вы указываете данные подключения к серверу СУБД в своих приложениях.

Посмотреть владельцев баз можно следующим запросом:

Особых комментариев здесь не требуется. Например, ivanov имеет права на базы с префиксом ivanov_ при подключении через localhost, а andrey на базы с префиксом andrey_ на любом хосте. Посмотреть привилегии пользователя можно командой:

Имя пользователя и хост оборачиваем одинарными кавычками.

Первая строка сообщает, что у указанного пользователя отсутствуют привилегии (USAGE) на любую таблицу любой базы (*.*), вторая строка говорит о всех основных привилегиях для всех таблиц всех баз с префиксом ivanov_.

Подробный разбор системы прав MySQL выходит далеко за пределы данной статьи, скажем только что ALL PRIVELEGES дает пользователь все права на свои базы, но не позволяет управлять правами доступа для других пользователей. Для этого используется набор прав ALL PRIVELEGES WITH GRANT OPTION, который имеет по умолчанию root. Для простого пользователя такой набор прав избыточен.

Попробуем создать нового пользователя:

Синтаксис команды прост, мы указываем имя пользователя и хост, а также идентификационные данные в виде пароля. Все передаваемые значения оборачиваются в одинарные кавычки. Создав пользователя нужно задать ему права, это делается командой GRANT. Сначала явно лишим его привилегий на чужие базы:

Затем можем выставлять права по своему усмотрению, например, выдача полных прав на базы с шаблоном имени petrov_:

Обратите внимание, что шаблон следует обернуть символами грависа (`), которые расположены на клавише с русской буквой Ё.

Выдать права на отдельную базу можно так:

Для отбора прав служит команда REVOKE, которая имеет аналогичный синтаксис, только to (кому), заменяем на from (у кого). Например:

Для того чтобы MySQL сервер применил права следует заставить его перезагрузить кэш привилегий командой:

Также вам может понадобиться сменить пароль пользователя:

Переименовать его, причем переименование не обязательно обозначает смену именно имени пользователя, можно изменить как имя, так и хост, в любых комбинациях:

Ну и наконец удалить учетную запись:

Перейдем от пользователей к базам данных, в простейшем случае для создания новой базы достаточно команды:

Это создаст базу данных с кодовой страницей и кодировкой сравнения по умолчанию. Если сервер СУБД отдельно не настраивался, то такой кодировкой скорее всего будет latin1_swedish_ci, что в ряде случаев может вызвать проблемы, если не сейчас, то в дальнейшем, поэтому правилом хорошего тона будет явно указывать кодировку при создании базы. Для UTF-8 это будет так:

Для удаления базы используйте:

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

По мере активной работы MySQL базы могут фрагментироваться, а также содержать ошибки в данных таблиц. Мы не говорим сейчас о серьезных сбоях, такие ситуации следует рассматривать индивидуально, а о простейших ошибках, которые успешно устраняются средствами самой СУБД. Для проверки, ремонта и оптимизации удобно использовать утилиту mysqlcheck.

Для проверки базы данных выполните, где andrey_drupal8 — имя базы:

Сразу все базы можно проверить командой :

А так как весь вывод на экран скорее всего не поместится, то есть смысл перенаправить его команде less:

Обратите внимание, что less позволяет прокручивать вывод как вниз, так и вверх, используя стрелки, для выхода нажмите q.

Если в какой-либо из баз были обнаружены ошибки стоит попытаться их исправить, для этого укажите:

Для оптимизации используйте ключ —-optimize, оптимизировать можно отдельную базу или несколько, для этого перечислите их после ключа —databases:

а также сразу все:

Выгрузка и загрузка дампов БД MySQL

Еще одна распространенная задача при администрировании любой СУБД, дампы баз используются как для резервного копирования, так и для переноса или создания копий. Если резервное копирование — процесс автоматизированный, то создание копий для переноса на другой сервер или перед какими-то существенными вмешательствами в структуру базы приходится делать вручную.

А если дело касается больших баз, то тут phpMyAdmin плохой помощник, сказываются ограничения на время исполнения скриптов, размер загружаемого файла, доступную память и т.д. И если выгрузить большой дамп с его помощью еще можно, то вот загрузить обратно может и не получиться.

Начнем с создания дампов, для этих целей используется утилита mysqldump, синтаксис которой повторяет синтаксис mysqlcheck. Для выгрузки дампа используйте команду:

Чтобы выгрузить сразу несколько баз используйте ключ —databases или —all-databases для создания дампа сразу всех баз. Вывод команды следует направить в файл и указать его расположение, в нашем случае это файл drupal8.sql в домашней директории. Также можно передать вывод по конвейеру архиватору и получить сразу архив:

В целом мы не советуем использовать один дамп сразу для нескольких баз, лучшим вариантом будет свой дамп для каждой базы, в тоже время использование ключа —all-databases оправдано в случаях, когда вам надо быстро сделать резервную копию всего сервера, например, при обновлении или переустановке СУБД, чтобы можно было быстро восстановить информацию если что-то пойдет не так.

Для того чтобы восстановить базу нужно направить дамп на вход утилиты mysql, для одиночных дампов всегда следует указывать базу приемник, например:

В качестве приемника вовсе не обязательно должна выступать база источник, но учтите, что если база уже существует, все ее содержимое будет заменено содержимым дампа.

Для дампов, содержащих более одной базы просто укажите:

В этом случае каждая база будет загружена в свой источник, если база источник не существует, то она будет создана.

Как видим, создание и восстановление дампов при помощи командной строки происходит буквально в одну строку и гораздо проще, и быстрее, чем с использованием phpMyAdmin или подобных ему инструментов.

Восстановление забытого пароля root MySQL

Еще одна очень популярная задача. Скажем сразу, восстановить пароль суперпользователя MySQL, как и любого другого пользователя, нельзя, но можно его сменить. Для этого нужно обладать правами суперпользователя операционной системы. Сначала остановим службу СУБД:

Затем запускаем ее в безопасном режиме с пропуском таблиц привилегий:

Обратите внимание, что после выполнения данной команды приглашение командной строки пропадет, останется один мигающий курсор. Пишем прямо туда:

и попадаем в среду mysql с правами root без ввода пароля.

Нам кажется, что многие уже догадались, что делать дальше, однако выполнить set password в данном режиме не получится, поэтому надо идти другим путем. Вспоминаем, что информация о пользователях хранится в таблице user служебной БД mysql. После чего выполним следующий запрос:

Важно! В новых версиях MySQL вместо колонки password таблицы user используется колонка authentication_string, поэтому вместо set password следует указывать set authentication_string.

Данный запрос установит новый пароль newpassword для всех строк в колонке user которых значится root.

Обновим кэш привилегий:

Выйдем из режима:

Остановим службу и запустим в обычном режиме:

Надеемся, что после прочтения данной статьи вы приобретете начальные навыки работы с MySQL из командной строки и сможете уверенно себя чувствовать даже тогда, когда phpMyAdmin недоступен, а может быть даже оцените удобство консольных команд и будете все реже заходить в панель, предпочитая управлять сервером напрямую.

Дополнительные материалы:

Помогла статья? Поддержи автора и новые статьи будут выходить чаще:

Или подпишись на наш Телеграм-канал: