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

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

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

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

Программное обеспечение: MySQL Администратор

Для многих MySQL разработчиков, понятие администрирование базы данных, практически незнакомо. Для удобства администрирования MySQL серверов, MySQL AB разработало новый графический пользовательский интерфейс. С простым и подходящим названием «MySQL Administrator». Первый выход приложения состоялся ещё в прошлом году, но в нем присутствовали ошибки, которые приводили к краху системы. Я ждал следующего выпуска. Недавно, MySQL AB выпустила более стабильную версию, конечно, по прежнему есть вещи которые требуют доработки, но теперь его вполне можно использовать.

by Russell J.T. Dyer

перевод by: ilya

Для многих MySQL разработчиков, понятие администрирование базы данных, практически незнакомо. Для удобства администрирования MySQL серверов, MySQL AB разработало новый графический пользовательский интерфейс. С простым и подходящим названием «MySQL Administrator». Первый выход приложения состоялся ещё в прошлом году, но в нем присутствовали ошибки, которые приводили к краху системы. Я ждал следующего выпуска. Недавно, MySQL AB выпустила более стабильную версию, конечно, по прежнему есть вещи которые требуют доработки, но теперь его вполне можно использовать.

«MySQL Administrator» позволяет администратору очень просто настроить MySQL сервер. Вы можете управлять пользователями, настраивать соединения с сервером, потоки. Так же имеете возможность проверить производительность сервера, просмотреть логи отражающие активность базы, скорость запросов и ошибки. Некоторые опции предназначены для архивации базы или восстановления, если это необходимо. Если вы используете репликацию базы данных, «MySQL Administrator» предоставляет интерфейс для мониторинга событий. И наконец, вы имеете возможность проверить таблицы, и сделать изменения. Вы не можете делать запросы, однако, для этого можно использовать «MySQL Control Center», приложение с GUI интерфейсом. «MySQL Administrator», приложение сфокусированное на администрирование и анализе базы данных. И делает это очень хорошо.

Копию «MySQL Администратор» вы можете загрузить свободно с MySQL AB’s зеркала http://dev.mysql.com/downloads/administrator/. Версии доступны как под Linux так под Microsoft Windows. Тем не менее, «MySQL Администратор» работает только с версией MySQL 4.0 или выше, поэтому для ее использования, некоторым понадобится обновится. К тому же, у вас появляется гораздо больше возможностей с новой версией. Я рекомендую вам взглянуть на это перед окончательным обновлением. Небольшой тест между «MySQL Administrator» и использованием API скриптов.
Мой сервер стоит на Red Hat Linux, итак, я скачал полную версию. Это порядком 13.8 MB в архиве tar.gz. После закачки файла я переместился в свою /usr/local/ директорию и распаковал его:

Конечно же, вы можете поменять имя файла на свое. Следующим шагом, я сделал иконку на рабочем столе моего Gnom’a для удобного запуска «MySQL Administrator» который располагался в /usr/local/mysql-administrator/bin/. После этого, я кликнул по иконке на рабочем столе и стартовал программу. Диалоговой окно выглядело так как на рис. 1 :

Как вы видите, при старте вы должны ввести имя пользователя и пароль MySQL и имя хоста или IP адрес MySQL сервера. На моем сервере, я так же определил путь и имя файла к локальному сокету. Так как я запускал «MySQL Administrator» на своем сервере, я определил имя хоста как localhost. Однако, что бы запустить её удаленно, необходимо указать не только имя хоста (или домена) но так же и имя пользователя, который имеет права для удаленного доступа к MySQL базе. Для этого добавьте его в таблицу «user» в базу «mysql». Это можно сделать следующей командой с под mysqlclient’a с правами root:

Это означает, что пользователь «russell» получил все права на все базы данных и таблицы (т.к. *.*) с хоста с адресом 12.127.17.72. (между прочим, это не мой настоящий адрес). Если желаете, вы можете ограничить пользователя в правах, и предоставить доступ только к определенной базе данных, для этого замените первую звездочку на имя базы. Но в моем случае, пользователь должен был администрировать все MySQL БД. Так как администратор MySQL может удалить все таблицы, вы можете отменить эту привилегию введя:

REVOKE DROP ON *.* FROM russell@12.127.17.72;

После первого заявления, которое давало пользователю Russell все привилегии, эта, запрещает пользователю удалять таблицы.

Настройки Сервера

Рис. 2 Иконки

После того, как вы заполните диалоговое окно и подсоединитесь к MySQL серверу, появится главное окно программы. По левому краю (как на рис.2 ) находятся иконки, которые отображают доступные опции администратора:

«Server Information» предоставляет общую информацию о состоянии сервера а так же некоторую информацию о нем. (т.к. версия MySQL, IP адрес сервера) а так же информацию о пользователе, который запустил «MySQL Administrator».

Опция «Server Control» отображает в правой панели логи сервера а так же кнопку остановки MySQL сервера, все это работает замечательно, но нет возможности перезапустить сервер. Правда, для этого можно использовать командную строку.

Третий пункт, это «Startup Variables» — очень удобен. Особенно если вы не знаете как настроить логи или оптимальную производительность MySQL сервера.

Опция «Startup Variabls» представлена в виде панели с закладками. (см рис. 3).

Первая закладка «General Parameters» предоставляет форму для изменения местонахождения файлов базы данных MySQL сервера. Так же вы можете изменить количество максимально доступной памяти используемой для обработки запросов, которая иногда приводит к увеличению скорости при обработке запросов. Многие опции для оптимизации работы памяти доступны в закладке «Performance». Изменения сделанные в этой опции отражаются в «/etc/my.cnf». Поэтому вы должны перезапустить MySQL сервер с тем, что бы новые значения вступили в силы. Вот тут то, кнопка перезапуска была бы очень удобной.

Лог файлы, пользователи и статус сервера

Рис. 3 Закладки

Опция «Log Files» (см рис. 4)

предоставляет форму для определения пути и имени лог файла. Если логирование по прежнему не включено, «MySQL Administrator» сделает это за вас, путем добавления необходимых строк в my.cnf в секцию [mysqld]. Один небольшой недостаток (если вы чистюля), в том, что приложение вставляет комментарии после каждой записи в конфигурационный файл. Записи неуклюжи, а комментарии ненужные. Тем не менее, если удалить комментарий используя редактрок, например «vi», то «MySQL Administrator» не будет их добавлять, даже при дальнейших изменениях.

Для просмотра лог файлов в приложение служит следующая секция — «Server Logs». Она не делает каких бы то ни было изменений, как например «Startup Variables», даже если перезапустить MySQL сервер и приложение. Я использовал кнопку для обзора (т.к. «выбор файла») и выбора подходящего лог файла. Если вы установили директорию для лог файла, убедитесь что вы дали права на запись пользователю mysql , используя команды chown и chmod. Иначе, MySQL не сможет создавать лог файлы или обновлять их.

Одной, на мой взгляд, из хороших вещей в «MySQL Administrator», это то, что предлагается графический интерфейс для просмотра и редактирования пользовательских привилегий. Я получал множество сообщений об ошибки, когда выбирал закладку «User Administration» , наверно, кто-то не мог получить таблицу с сервера. После нажатия кнопки «ОК», я ещё раз 10 получал сообщение об ошибки и в конечном счете получил доступ к панели «User Administration». Я конечно постараюсь вытерпеть это до следующего релиза. Проблема, из-за которой эта опция становилась недоступной состояла в указание имени хоста для пользовательских привилегий. В соответствии со справкой (этот раздел был достаточно хорошо описан, остальные по минимуму) каждому пользователю из списка сопоставлялся список хостов. Похоже, что они не нашли удобного способа настроить привилегии пользователя глобально (когда хост указывается в виде %). Когда я добавлял нового пользователя или изменял хост с которого возможен доступ (основанный на IP адресе) приложение каждый раз выдавало ошибку или завершало работу при нажатие кнопки «принять». Это может показаться нехорошим, но, так как это единственная опция которую я не смог использовать, я думаю что «MySQL Administrator» вполне пригодна для использования. Разработчики по прежнему работают усердно, и в скором времени обещают исправить эти ошибки.

Опция «Server Connections» предназначена для отображения пользователей которые взаимодействуют с БД. Вы можете наблюдать, что каждый пользователь делает с базой в настоящий момент. А также имеете возможность отключить пользователя или просто убить процесс, для этого достаточно навести курсор на интересующий вас процесс
и нажать кнопку «Kill Thread». То же самое можно делать и для пользователей из «User Connections». Однако, если вы отключили пользователя, он может восстановить
соединение когда сгенерирует очередной SQL запрос.

Рис. 5 Архивация

Если вы просматриваете активность вашего сервера использую графический интерфейс, то вы так же можете наслаждаться опцией «Health». Которая показывает график, использования трафика на одной панели. На другой графически изображена загрузка памяти при обработки SQL запроса. В этой опции присутствует ещё 2 закладки, одна для отображения переменных сервера, а другая показывает статус сервера. Это очень удобно, если вы администрируете большую базу и хотите оптимально настроить её производительность. Правда вы не можете изменять эти переменные из этой панели, для этого используйте опцию «Startup Variables» или одну из других опций.

Если вы хотите сделать архив вашей базы, то опция «Backup» поможет вам в этом. Нажатием на кнопку «New Project» , вы можете задать инструкции для архивирования базы. В левой части панели, у вас будут отображается все базы, которые есть на сервере. Выберете базу, которую вы хотите архивировать и добавьте её в список баз данных подлежащие архивированию. После того, как вы выбрали все базы, просто нажмите на кнопку «Save Project» что бы сохранить список архивируемых баз. Для ручного запуска архивации БД, кликните на кнопку «Start Backup». В открывшемся диалоговом окне введите имя дамп файла (mysqldump) и путь к нему, где он будет сохранен. Закладка «Schedule Backup» предоставляет вам планировщика для автоматического выполнения архивации БД из «cron». Так как опция использует mysqldump, то все опции для него могут быть установлены в закладке «Advanced Options».

Для восстановления БД, вам поможет опция «Restore Backup». Как правило, вы не сможете увидеть ни одного файла с архивом БД для восстановления. Для этого нажмите на кнопку «Change Path» и укажите «MySQL Administrator»‘у путь, где располагается ваш дамп файл. Опций связанных с дамп файлом очень мало, так что вы просто запустите его. Все опции, которые вам нужны, устанавливаются при создании дамп файла. «MySQL Administrator» не дает возможности восстановить отдельную дату или таблицу не переписав все содержимое. Для этого нужно использовать текстовый редактор и править дамп файл.

Если вы используете репликацию «MySQL Administrator» предлагает вам опцию «Replication Status». Она не предоставляет много информации, но это всего лишь начало.
Я надеюсь в будущем она будет отображать больше информации.

Последняя опция в «MySQL Administrator» — Catalogs. Эта опция для проверки и модификации таблиц в БД. После выбора БД в левой части панели (игнорируя сообщения об ошибки), в правой, вы увидите список таблиц принадлежащих этой БД. (см. рис. 6)

Список, возле таблиц, предоставляет вам различную информацию о таблицах, тип, количество рядов, размер рядов, и т.д. Нажав на «Indices», он отобразит вам все индексы для всех таблиц в БД.

Так же, в этой опции, если вы 2-ы нажмете на имени таблицы (см. рис. 7), откроется окно, в котором вы сможете поменять структуру таблицы.

Вы можете изменять тип колонок, а так добавлять или удалять их. Так же предоставляется возможность, при желании, изменить индекс или тип (с MyISAM на InnoDb) таблицы.
Вы даже сможете добавлять или удалять целые таблицы используя этот интерфейс. Единственное что вы не сможете сделать, я говорил уже раньше, использовать запросы к БД. Так как это не административные функции.

Рис. 7 Редактор таблиц

Общее представление

Если MySQL хочет конкурировать на рынке с такими продуктами как Microsoft Access, тогда ему потребуется хороший графический интерфейс, как у «MySQL Control Center» и «MySQL Administrator». Конечно, при работе с «MySQL Administrator» обнаруживаются некоторые ошибки, но не смотря на это, более 90 % работает стабильно. Если все эти искажения зависели от моего сервера, то приложение возможно вообще не имеет ошибок.
Конечно, для конкурировании с Microsoft, все эти проблемы должны будут ликвидированны.

В общем, «MySQL Администратор» готовое для использования приложение и разработчики проделали большую работу для этого, особенно, принимая то факт, что
у них по прежнему 1-я версия. Я рекомендую всем, кто предпочитает использовать графический интерфейс для администрирования использовать «MySQL Администратор» и сообщать о всех ошибках в MySQL AB.