В первой части обзора программы MySQL Workbench я показал на примере основные принципы разработки модели данных и создания EER-диаграммы "сущность-связь". Пришло время применить MySQL Workbench в бою, поэтому сегодня я расскажу о создании подключения к удалённому серверу, выгрузке mwb модели на сервер, синхронизации обновлений схемы данных в процессе работы, а так же об управлении MySQL сервером с помощью программы MySQL Workbench.
Экспорт модели MySQL Workbench в SQL дамп
Самый быстрый путь для того, чтобы схема данных из MySQL Workbench попала на сервер - создание SQL дампа mwb модели. Для этого не потребуется создавать удалённое подключение в программе, однако этот способ хорош лишь в случае, если требуется однократная заливка структуры и базовых данных на сервер. Дальнейшая поддержка, обновление и синхронизация модели данных в этом случае будет весьма проблематична (хотя, в MySQL Workbench есть функционал и для этого, но он не входит в планы моего обзора). Итак, открываем нашу модель и выбираем "File → Export → Forward Engineer SQL CREATE Script..." (Ctrl + Shift + G):
Если требуется записать дамп в файл, указываем путь до файла в поле "Output SQL Script File" (если оставить поле пустым, SQL скрипт можно будет скопировать на последнем шаге в буфер обмена). Мы видим окно настроек экспорта. Настройки стандартные, чтобы понять их суть, достаточно перевести их названия. Поясню только то, что галочка "Generate INSERT Statements for Tables" включает в дамп базовые данные, располагающиеся во вкладке "Inserts" интерфейса редактирования таблиц модели. После нажатия "Next" мы видим список того, что вообще можно экспортировать. Для экспорта таблиц выбираем "Export MySQL Table Objects", а чтобы экспортировать их выборочно, нажимаем "Show Filter" и выбираем нужные нам таблицы:
Нажав "Next" мы увидим в окне готовый SQL скрипт, откуда сможем скопировать его в буфер обмена или же записать в какой-либо файл.
Путей, которыми структура и данные попадут к вам на сервер масса, а самый распространённый из них - импорт через PHPMyAdmin.
Создание удалённого подключения к серверу MySQL
А вот это уже необходимо для постоянной работы с сервером. Советую не лениться и всегда создавать эти подключения - они дают массу новых возможностей!
На стартовом экране нажимаем плюсик рядом с надписью или выбираем "Database → Manage Connections...", в открывшемся окне нажимаем кнопку "New". MySQL Workbench предлагает три способа подключения к серверу: прямое подключение через пользователя, которому разрешён удалённый доступ к MySQL (обычно доступ таких пользователей ограничивают по ip), socket / pipe подключение через файл сокета (для Unix) или pipe (для Windows), а так же подключение через ssh-туннель (требует наличие ssh доступа и юзера ssh и MySQL с соответствующими правами). Рассмотрим варианты подключения к удалённому серверу:
Через удалённого пользователя MySQL (Standard: TCP/IP)
В диалоговом окне создания подключения выбираем тип подключения "Standard: TCP/IP":
- В поле "Host" вводим адрес сервера MySQL или адрес сайта (если MySQL сервер находится на самом веб сервере)
- "Port" по умолчанию чаще всего 3306
- Вводим имя пользователя MySQL ("Username"), пароль ("Password") и имя базы данных ("Default Schema")
- После создания подключения нажимаем "Test Connection" и ждём сообщения "Connection parameters are correct."
Если что-то пошло не так, проверяем, включен ли у нашего пользователя удалённый доступ, а так же наличие ip адреса нашего компьютера в списке разрешённых для данного юзера.
Через SSH туннель (TCP/IP over SSH)
В диалоговом окне создания подключения выбираем тип подключения "Standard: TCP/IP over SSH":
- Тут нам придётся ввести адрес веб сервера ("SSH Hostname") (порт, если нужно, указывается через двоеточие, например, "linuxsc.pro:18752")
- Вводим логин и пароль SSH пользователя ("SSH Username" и "SSH Password")
- Адрес MySQL сервера нужно вводить относительно веб сервера, то есть в случае, если MySQL и веб сервер находятся на одной машине, в поле "MySQL Hostname" оставляем "127.0.0.1"
- "MySQL Server Port" по умолчанию 3306
- В поля "Username" и "Password" вводим логин и пароль пользователя MySQL
- Проверяем подключение при помощи кнопки "Test Connection"
Список доступных подключений можно просмотреть в диалоговом окне, выбрав "Database → Manage Connections..."
Синхронизация структуры данных
Для синхронизации структуры базы данных и локальной модели в MySQL Workbench существует специальный инструмент. Открыв нужную модель, выбираем "Database → Synchronize Model..." (Ctrl + Shift + G), после чего мы можем выбрать одно из сохранённых удалённых подключений и отредактировать его параметры. Жмём "Next" для подключения к базе данных:
После подключения к серверу и нажатия "Next" мы увидим список моделей (в левой колонке) и баз данных (в правой колонке), доступных для синхронизации:
Выбрав галочкой нужную базу и схему, нажимаем "Next", запуская процедуру сравнения структур удалённой базы данных и нашей модели:
После завершения процедуры мы можем увидеть список различий между нашей схемой данных и удалённой базой:
Тут мы можем настроить объединение таблиц: протолкнуть наши изменения на сервер ("Update Source"), втянуть в локальную модель конфигурацию с сервера ("Update Model") или игнорировать отличия ("Ignore"). При чём, доступен как вариант настройки для всей базы, так и отдельно для каждой таблицы. При выделении одной из таблиц и выборе способа объединения мы можем видеть SQL запросы, которые выполнятся в процессе синхронизации, а нажав "Next" - увидим полный стек этих запросов:
Просмотрев SQL запросы, нажимаем "Execute >", запуская этим выполнение синхронизации. Если всё пройдёт успешно, мы увидим такой отчёт:
В случае возникновения ошибок их лог отобразится в этом же диалоговом окне. Некоторые из них разобраны в конце этой статьи ;)
Выгрузка на сервер схемы и стартовых данных
Описанная выше синхронизация осуществляет лишь объединение структуры схемы данных удалённой базы и локальной модели, но никак не затрагивает стартовые данные, внесённые в модель ("Inserts"). Если требуется выгрузить их, выбираем "Database → Forward Engineer..." (Ctrl + G), затем выбираем одно из сохранённых ранее подключений (или создаём новое) и нажимаем "Next". В остальном механизм выгрузки аналогичен механизму экспорта mwb модели, описанному в начале статьи. Его можно так же использовать, если требуется простая выгрузка схемы данных на сервер без синхронизации.
Администрирование базы данных
MySQL Workbench может быть прекрасной альтернативой PHPMyAdmin и в администрировании данных. Потребуется лишь создать подключение к MySQL серверу. Поскольку я использую Workbench с самого старта разработки, настроенное подключение у меня уже имеется в наличии. Кликаем на нужное подключение на стартовом экране или выбираем "Database → Connect to Database..." (Ctrl + U) и выбираем подключение из списка.
Список доступных для редактирования баз данных можно увидеть в левой панельке в разделе "SCHEMAS". Открыв нужную базу данных, можно увидеть список таблиц в ней:
В правой же области мы можем видеть вкладки разных видов, к примеру, на скриншоте выше открыта вкладка составления SQL запроса, в которой есть масса полезных фишек и примочек, помогающих в его редактировании, которыми я, признаться, ни разу не пользовался :) Выполнять запросы можно комбинацией клавиш Ctrl + Enter.
Для просмотра, создания или редактирования записей нажимаем на нужную таблицу правой кнопкой и выбираем "Select Rows - Limit 1000" или выполняем нужный для выборки SQL запрос:
В открывшейся вкладке мы увидим симпатичный и человекопонятный грид, в котором интуитивно и без труда сможем работать с данными. Протолкнуть изменения в базу можно при помощи кнопки "Apply", а отменить - кнопкой "Cancel" в нижней части вкладки редактора.
Управление сервером, создание и восстановление бэкапов
Создать backup базы данных очень просто, выбрав Server → Data Export при наличии активного подключения к удалённому серверу:
Крутизна, да? :) Можно бэкапить как всю базу, так и по частям. Можно хранить все бэкапы удобно и упорядоченно. Блин, можно всё что хочешь!
Восстановить backup тоже проще простого. Выбираем Server → Data Import, находим нужный файл и не долго думая нажимаем "Start Import":
В общем, да, в MySQL Workbench есть целая куча всего для управления сервером баз данных: управление пользователями и их правами, управление настройками сервера (если у вашего пользователя есть соответствующий доступ). К примеру, выбрав Server → Server Status, мы можем посмотреть такую вот классную статистику нагрузки на MySQL:
Такую красивую статистику просто скушать хочется! Приятно смотреть на такую эффективную эффективность моих ключей и индексов :) А вот над буфером InnoDB нужно подумать.
Вместо заключения (о некоторых подводных камнях)
Вдохновившись, в заключении я хотел написать хвалебный отзыв о том, на сколько же крута MySQL Workbench. Однако уровень её крутизны, думаю, прослеживается на протяжении всего обзора, а вот о некоторых подводных неприятностях, с которыми мне пришлось столкнуться, стоит упомянуть.
Программа периодически подвисает или "падает"
Суть проблемы: некоторая неустойчивость MySQL Workbench прослеживалась у меня на разных компах с разными ОС. Видимо, разработчики о ней тоже знают - в процессе работы изменения записываются в *.bak файл, что не раз выручало меня после "падения".
Решение: почаще сохраняться ;) Так же практика показывает, что лучше не тыкать беспорядочно на всё подряд, а перед следующим действием ожидать завершения предыдущего.
Ошибка "Error 1292: Incorrect date / datetime value" при синхронизации
Суть проблемы: чаще всего такая ошибка возникает при попытке синхронизации с таблицей, в которой есть запись со значением '0000-00-00' или '0000-00-00 00:00:00' в полях типа DATE или DATETIME соответственно. В некоторых случаях настройки MySQL позволяют создавать такие записи, но не позволяют редактировать схему таблицы.
Решение: вообще, при синхронизации или экспорте данных MySQL Workbench добавляет специальные запросы, как бы оборачивая основной SQL код:
Вчитавшись в этот код, начинаешь думать, что программа пытается решить эту проблему самостоятельно. Парадокс в том, что для решения проблемы эти строки нужно удалить:
# Удалить эту строку из начала SQL кода
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
...
# Удалить эту строку из конца SQL кода
SET SQL_MODE=@OLD_SQL_MODE;
Ошибка "Error 1005: Can't create table '...' (errno: 150)" при синхронизации
Суть проблемы: обычно эта ошибка касается неправильной настройки внешних ключей (вкладка "Foreign Keys" в настройках таблиц). У меня она возникала в том случае, если я делал ключом поле с меткой NOT NULL, а в поведении внешнего ключа указывал SET NULL - это абсурд, ведь InnoDB не сможет установить значение NULL в поле, где такое значение запрещено.
Решение: внимательно следим за настройкой поведения внешних ключей. Если необходимо поведение SET NULL, у поля-ключа в дочерней таблице не должен стоять флаг NOT NULL.
Ошибка "Field ... can not be null" при выгрузке стартовых данных
Суть проблемы: независимо от настроек таблицы, все поля в "Inserts" имеют по умолчанию значение NULL, даже если такое значение не разрешено для данного поля. Соответственно, при выгрузке на сервер может возникнуть ошибка.
Решение: при добавлении стартовых данных следим за тем, чтобы значение NULL оставалось лишь в тех полях, где это разрешено. Если нужно сделать поле пустой строкой, делаем финт ушами: ставим в него курсор, нажимаем пробел, затем стираем его (во всяком случае, я не придумал ничего получше на такой случай :)).
Ошибка Сan't connect to MySQL server on ... (10061) при подключении
Суть проблемы: говорят, что может быть несколько причин. Я встечал такую ошибку в случае, если в файле my.cnf была установлена настройка "skip-networking" - по сути она не даёт MySQL работать с сетью.
Решение: закомментировать данную опцию:
# skip-networking
Конец
Итак, мой обзор программы MySQL Workbench в двух частях (кстати, первую часть можно найти тут) подходит к концу. Буду рад, если читатель почерпнет для себя в нём что-то полезное или интересное :)
Приятной разработки!