Основы работы с MySQL Workbench: быстрый старт, управление схемой данных

Веб-разработчик растёт вместе с проектами, которые он создаёт и развивает. С ростом проектов увеличивается сложность программной части, неизбежно возрастает количество обрабатываемых ею данных, а так же сложность схемы данных. Общение с другими вебщиками показывает, что огромной популярностью среди нас пользуются базы данных MySQL, а для управления ими — небезызвестный PHPMyAdmin. Переходя от маленьких проектов к большим, от cms к фреймворкам, многие, как и я, остаются верны MySQL. Однако для проектирования сложной базы данных с большим количеством таблиц и связей, возможностей PHPMyAdmin катастрофически не хватает. Поэтому я решил написать обзор MySQL Workbench — замечательной бесплатной десктопной программы для работы с MySQL.

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

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

Должен сказать, что программа действительно великолепная. Она позволяет быстро и с удовольствием накидывать схемы данных проекта, проектировать сущности и связи между ними, безболезненно внедрять изменения в схему и так же быстро и безболезненно синхронизировать её с удалённым сервером. А графический редактор EER-диаграмм, напоминающих забавных таракашек, позволяет увидеть общую картину модели данных и насладиться её лёгкостью и элегантностью 🙂 После первой же пробы этот инструмент становится незаменимым помощником в боевом арсенале веб-программиста.

Скачать MySQL Workbench

Дистрибутив MySQL Workbench доступен на этой странице. Самая свежая версия программы на момент написания статьи — Version 6.1. Перед скачиванием требуется выбрать одну из следующих платформ:

• Microsoft Windows (доступны MSI Installer и ZIP архив)
• Ubuntu Linux
• Fedora
• Red Hat Enterprise Linux / Oracle Linux
• Mac OS X

После выбора платформы вам предлагают зарегистрироваться или авторизоваться в Oracle. Если не хотите, внизу есть ссылка «No thanks, just start my download» — жмите на неё 😉

Начало работы

Стартовый экран программы отражает основные направления её функциональности — проектирование моделей баз данных и их администрирование:

В верхней части экрана находится список подключений к MySQL серверам ваших проектов, а список последних открытых моделей данных — в нижней части экрана. Работа обычно начинается с создания схемы данных или загрузки существующей структуры в MySQL Workbench. Приступим к работе!

Создание и редактирование модели данных

Для добавления модели нажимаем плюсик рядом с заголовком «Models» или выбираем «File → New Model» (Ctrl + N):

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

Добавление и редактирование таблицы

Список баз данных проекта и список таблиц в пределах базы данных будет располагаться во вкладке «Physical Schemas». Чтобы создать таблицу, дважды кликаем на «+Add Table»:

Откроется удобный интерфейс для редактирования списка полей и их свойств. Здесь мы можем задать название поля, тип данных, а так же установить для полей различные атрибуты: назначить поле первичным ключом (PK), пометить его Not Null (NN), бинарным (BIN), уникальным (UQ) и другие, установить для поля авто-инкремирование (AI) и значение по умолчанию (Default).

Управление индексами

Добавлять, удалять и редактировать индексы таблиц можно во вкладке «Indexes» интерфейса управления таблицей:

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

Связи между таблицами

Установка внешних ключей и связывание таблиц возможно только для таблиц InnoDB (эта система хранения данных выбирается по умолчанию). Для управления связями в каждой таблице находится вкладка «Foreign Keys»:

Для добавления связи открываем вкладку «Foreign Keys» дочерней таблицы, вводим имя внешнего ключа и выбираем таблицу-родителя. Далее в средней части вкладки в графе Column выбираем поле-ключ из дочерней таблицы, а в графе Referenced Column — соответствующее поле из родительской таблицы (тип полей должен совпадать). При создании внешних ключей в дочерней таблице автоматически создаются соответствующие индексы.

В разделе «Foreign Key Options» настраиваем поведение внешнего ключа при изменении соответствующего поля (ON UPDATE) и удалении (ON DELETE) родительской записи:

• RESTRICT — выдавать ошибку при изменении / удалении родительской записи
• CASCADE — обновлять внешний ключ при изменении родительской записи, удалять дочернюю запись при удалении родителя
• SET NULL — устанавливать значение внешнего ключа NULL при изменении / удалении родителя (неприемлемо для полей, у которых установлен флаг NOT NULL!)
• NO ACTION — не делать ничего, однако по факту эффект аналогичен RESTRICT

В приведённом примере я добавил к дочерней таблице UserProfile внешний ключ для связи с родительской таблицей User. При редактировании поля userId и удалении позиций из таблицы User аналогичные изменения будут автоматически происходить и со связанными записями из таблицы UserProfile.

Наполнение таблицы базовыми данными

При создании проекта в базу данных часто нужно добавлять стартовые данные. Это могут быть корневые категории, пользователи-администраторы и т.д. В управлении таблицами MySQL Workbench для этого существует вкладка «Inserts»:

Как видно из примера, в случае, если перед записью в базу данных к данным нужно применить какую-то функцию MySQL, это делается с помощью синтаксиса \func functionName(‘data’), например, \func md5(‘password’).

После ввода данных необходимо сохранить их в локальную базу данных нажатием на кнопку «Apply Changes».

Создание EER диаграммы (диаграммы «сущность-связь»)

Для представления схемы данных, сущностей и их связей в графическом виде в MySQL Workbench существует редактор EER-диаграмм. Для создания диаграммы в верхней части экрана управления базой данных дважды кликаем на иконку «+Add Diagram»:

В его интерфейсе можно создавать и редактировать таблицы, добавлять между ними связи различных типов. Чтобы добавить уже существующую в схеме таблицу на диаграмму, просто перетащите её из панели «Catalog Tree».

Для экспорта схемы данных в графический файл выберите «File → Export», а затем один из вариантов (PNG, SVG, PDF, PostScript File).

Импорт существующей схемы данных (из SQL дампа)

Если у нас уже есть схема данных, её можно без труда импортировать в MySQL Workbench для дальнейшей работы. Для импорта модели из SQL файла выбираем «File → Import → Reverse Engineer MySQL Create Script. «, после чего выбираем нужный SQL файл и жмём «Execute >»

В MySQL Workbench так же предусмотрен импорт и синхронизация модели данных нарямую с удалённым сервером. Для этого потребуется создать подключение удалённого доступа к MySQL, о которых я расскажу в продолжении данного обзора.

Демо-проект из статьи доступен для скачивания по этой ссылке. Желаю успехов и красивых таракашек схем!

Основы работы с MySQL Workbench: подключение удалённого сервера и синхронизация с ним

В первой части обзора программы 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 код:

Вчитавшись в этот код, начинаешь думать, что программа пытается решить эту проблему самостоятельно. Парадокс в том, что для решения проблемы эти строки нужно удалить:

Ошибка «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 работать с сетью.

Решение: закомментировать данную опцию:

Конец

Итак, мой обзор программы MySQL Workbench в двух частях (кстати, первую часть можно найти тут) подходит к концу. Буду рад, если читатель почерпнет для себя в нём что-то полезное или интересное 🙂