MySQL Workbench: установка и настройка для работы с базами данных

Введение

При помощи SQL-скриптов легко создать базу данных (БД) из нескольких таблиц. В ней удобно ориентироваться, а всю схему можно держать в голове. Однако такой формат БД встречается только в небольших или учебных проектах.

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

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

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

Что такое MySQL Workbench

Несмотря на название, он может работать не только с MySQL, но и с другими СУБД — например, Oracle Database, Microsoft SQL Server и PostgreSQL. Одна из главных особенностей Workbench — визуальное моделирование. Оно позволяет увидеть все таблицы и связи между ними в наглядном виде, что сильно упрощает проектирование БД. Перейдем к практике — начнем с установки и первоначальной настройки инструмента.

Создание облачной СУБД MySQL

Перед началом работы с Workbench нужно установить и запустить MySQL. Cоздадим новый кластер БД на платформе Selectel. Для этого выполним следующие шаги.

1. Перейдем в панель управления.

2. В левой части окна выберем Облачная платформа.

3. В открывшемся окне перейдем во вкладку Базы данных.

4. Нажмем Создать кластер.

5. Зададим имя кластера и выберем параметры. Рассмотрим лишь некоторые, остальные можно конфигурировать по умолчанию или на свое усмотрение. СУБД — MySQL sync 8, Конфигурация нод — Flex: 2 vCPU, 4 ГБ RAM и 32 ГБ места на локальном диске. 

6. В разделе Сеть выберем публичную подсеть, чтобы мы могли подключаться к кластеру из интернета. Размер подсети — по умолчанию. После конфигурирования нажмем кнопку Создать кластер.

6. Проверим, что кластер создан — через несколько минут он должен отобразится во вкладке Базы данных в статуте Active. Открываем кластер по клику на его название.

Для организации БД в новом кластере нужно создать нового пользователя. Для этого перейдем на вкладку Пользователи и нажмем кнопку Создать пользователя.

Зададим имя и пароль, который важно сохранить в безопасное место. Позже его можно будет изменить, но не посмотреть. Нажмем Создать.

Теперь подготовим БД, в которой будем создавать таблицы. Перейдем во вкладку Базы данных и нажмем Создать базу данных.

Введем имя для новой  БД и нажмем Создать.

Дадим пользователю доступ к созданной БД.

Готово! Наш удаленный сервер MySQL готов к работе.

Установка MySQL Workbench и подключение  к кластеру

Подготовка к установке

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

Системные требования

Актуальная версия на момент написания текста — MySQL Workbench 8.0. О нюансах поддержки и важных обновлениях можно узнать на официальном сайте MySQL. Разберем требования к ОС и аппаратной платформе. 

Важно отметить, что инструмент может работать и на более ранних версиях ОС, но стабильность в этом случае не гарантирована. 

Подготовка операционной системы

На Windows выполните проверку через Центр обновлений, на MacOS — через раздел Обновление ПО. На Linux  используйте команды в зависимости от дистрибутива. Например, в Ubuntu нужно ввести команду sudo apt update для проверки сведений об актуальных пакетах и sudo apt upgrade -y для установки обновлений.

Настройка прав доступа

На Windows: убедитесь, что у вас есть права администратора для установки. На Linux или macOS — используйте sudo для выполнения команд с повышенными привилегиями.

Установка

Установим MySQL Workbench. Перейдем на страницу загрузки, выберем нашу операционную систему. 

Нажимаем кнопку Download, и на следующем экране нам предложат войти в аккаунт Oracle или создать его. Можно нажать кнопку No thanks, just start my download, чтобы отказаться и загрузить установщик.

Запускаем скачанный файл, во всех диалоговых окнах оставляем значения по умолчанию. После установки запускаем MySQL Workbench. 

Подключение к кластеру

При первом запуске MySQL Workbench нас встретит приветственное окно. Для начала подключимся к нашему кластеру: нажимаем на кнопку + около раздела MySQL Connections. Далее — откроется окно настройки.

1. Указываем имя соединения, в нашем случае — Selectel MySQL Server. Это позволит различать подключения, если вы работаете с несколькими серверами.2. Перейдем к созданному кластеру, во вкладке Подключение скопируем его IP-адрес и вставим в Workbench в поле Hostname. В поле Port прописываем 6033, как указано в параметрах подключения на платформе Selectel. Вводим Username, в нашем случае — isolde.

3. Нажимаем кнопку Test Connection, в следующем диалоговом окне вводим пароль, который создавали в панели управления. Нажимаем ОК.

4. Если подключение прошло проверку, то увидим окно подтверждения с информацией. Нажимаем ОК и закрываем предыдущее окно.

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

В левой части экрана перейдем на вкладку Schemas. Мы увидим базу данных, которую создавали ранее в интерфейсе Selectel.

Работа с таблицами

Создание таблиц

Рассмотрим два разных способа, как это сделать. Сначала создадим две таблицы: справочник товаров и справочник магазинов. Затем создадим еще одну таблицу, в которой будет храниться связь о том, в каких магазинах какие товары продаются.

1. Дважды кликнем по названию схемы, чтобы она выделилась жирным шрифтом. Это значит, что теперь все действия по умолчанию мы выполняем в ней. 

2. В панели инструментов нажимаем на иконку создания новой таблицы. Откроется новое окно.

3. Заполним несколько полей.

• Название таблицы — products.
• Комментарии рекомендуем указывать. Наша схема будет состоять всего из трех таблиц, в ней не запутаешься. Однако когда базы данных разрастаются или доступ к ним получают другие пользователи, комментарии помогают ориентироваться.

Поля — создадим два. Первое — id, идентификатор товара. Выбираем тип INT, отмечаем галочки PK (Primary Key), NN (Not Null) и AI (Auto Increment). Тем самым мы указали, что это поле — первичный ключ, а его значение не может быть пустым и должно генерироваться автоматически. Второе поле — name, название товара. Выбираем тип VARCHAR, устанавливаем длину в 45 символов и отмечаем галочку NN, чтобы у всех товаров было название.

4. Нажимаем кнопку Apply. Workbench отобразит скрипт, сгенерированный для создания этой таблицы. Перед применением его можно проверить или изменить.

Затем по такому же принципу создаем вторую таблицу — справочник магазинов.

Заполнение таблиц

Теперь наполним справочники данными. 1. Нажмем правой кнопкой мыши на таблицу products и выберем пункт Select Rows. 

2. Открылось окно, в верхней части которого можно написать SQL-запрос. В нижнем блоке отобразится результат. Добавлять новые записи в таблицу можно при помощи SQL или графического представления.

3. Воспользуемся графическим интерфейсом и создадим несколько строк в нижней части окна. Достаточно указать названия товаров, а идентификатор заполнится автоматически. Нажмем кнопку Apply.

4. Workbench снова покажет скрипт перед тем, как применить действия. Его можно проверить или изменить.

5. Добавим несколько записей в таблицу stores аналогичным способом.

Изменение определения таблицы в БД

Используем графический интерфейс Workbench, чтобы внести изменения в таблицы. Для этого выполним щелчок правой кнопкой мыши по таблице и нажмем Alter Table.

Теперь во вкладке Table мы можем изменить опции, добавить или удалить столбцы. Для применения изменений нужно нажать кнопку Apply.

Связь таблиц 

Чаще всего таблицы связаны друг с другом — например, каталог товаров и наличие в конкретных магазинах или список работников и их графики. Бывает три типа связей.

• Один к одному, 1:N. Одна запись из таблицы соответствует только одной записи из другой. Например, нам нужно знать, у каких сотрудников есть автомобиль. Тут подойдет связь «один к одному»: у конкретного работника либо есть машина, либо нет. 
• Один ко многим, 1:M. Одна запись из таблицы соответствует нескольким из другой. Пример: таблица с факультетами и список студентов. Чтобы понять, кто в каком учится, для каждого из факультетов будет несколько связей, которые ведут к обучающимся.
• Многие ко многим, M:N. Несколько значений из одной таблицы соответствует нескольким из другой. Например, чтобы сопоставить таблицу с учителями и таблицу с учениками, подойдет именно эта связь: преподаватель учит сразу несколько человек, которые также обучаются сразу у нескольких преподавателей.

Создание модели в GUI

Перейдем к другому, более наглядному способу работы с БД — моделям. Модель (ER-модель, ER-диаграмма) — это способ графического представления таблиц и связей между ними. Здесь все таблицы будто нарисованы на листе бумаги, а стрелки между ними показывают взаимосвязи. 

Таблицы можно легко добавлять, изменять, удалять и перемещать. Например, вот схема небольшого, но реального проекта. В крупных проектах схемы БД могут быть во много раз больше.

Модель можно создать несколькими способами.

С нуля. Создание модели «с чистого листа» подходит для случаев, когда Workbench используется со старта проекта. Тогда все таблицы можно создавать и изменять в модели.

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

Мы воспользуемся вторым способом: импортируем наши таблицы, а затем в одну из них добавим новое поле и создадим еще одну таблицу для связи.

1. В главном меню Workbench перейдем в раздел Database → Reverse Engineer. 

2. Выберем созданное ранее подключение и нажмем Next.

3. Оставляем параметры по умолчанию и нажимаем Next.

3. Укажем, что нужно импортировать нашу схему и нажмем Next.

4. Во всех последующих окнах оставляем значения по умолчанию, нажимая кнопку Next.

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

Мы уже знаем, как самим создавать таблицы в MySQL Workbench. Теперь посмотрим, как это можно сделать автоматически. Нам нужна еще одна таблица, в которой будет храниться связь: какой товар в каком магазине продается. Самый простой способ это сделать — использовать инструмент n:m (многие ко многим) Relationship. Выберем его на панели инструментов и по очереди выделим таблицы.

MySQL Workbench сам создаст новую таблицу, даст ей имя и добавит в поля.

Обратите внимание, что поля stores_id и products_id — одновременно первичные (primary key) и вторичные ключи(foreign key). Это означает, что в таблицу можно будет добавить только те товары и магазины, которые есть в справочниках. Помимо прочего, в ней нельзя будет создать две одинаковые записи. Это как раз то, что нам нужно.

Теперь добавим в справочник магазинов поле с адресом. Для этого дважды кликнем по таблице Stores. В нижней части окна появится область для редактирования полей аналогичная той, с которой мы работали при создании первых двух таблиц. Создадим новое поле Address.

Мы внесли изменения, которые хотели, но если посмотреть на схему, то увидим, что в таблицах ничего не поменялось. Нет ни новой таблицы, ни нового поля. Это из-за того, что модель не связана с БД явно.

В процессе импорта мы создали модель на основе существующей схемы, но они не связаны между собой. Схема может жить своей жизнью, а модель — своей. Чтобы перенести изменения из модели в схему, нужно сделать это явно.

1. В главном меню Workbench переходим в раздел Database → Synchronize Model, в открывшемся окне выбираем соединение.

2. Во всех дальнейших окнах оставляем значения по умолчанию и нажимаем кнопку Next.

3. Workbench проанализирует различия в модели и схеме и сгенерирует скрипты для обновления. На скриншоте видно, что будет создана одна новая таблица, а в существующую таблицу добавится новое поле.

4. Нажмем Apply и применим скрипт. Возвращаемся в схему и видим, что в ней появились наши изменения.

Импорт и экспорт данных

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

Экспорт, или дамп БД

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

1. В главном меню перейдем в раздел Server → Data Export. 

2. В открывшемся окне выберем схему и все таблицы в ней. Укажем путь, куда сохранить файлы и нажмем кнопку Start Export. 

3. На заключительном экране можно начать экспорт заново или остановить его, а также посмотреть логи и статус. 

Удаление таблиц из БД

Чтобы показать процесс восстановления, удалим все существующие таблицы. Для этого выделим их и в контекстном меню нажмем Drop 3 Tables.

В открывшемся диалоговом окне выберем вариант Drop Now. 

Что делает кнопка Review SQL?

В окне также есть кнопка Review SQL. Если хотите удалить таблицы с помощью SQL — нажмите ее и Workbench отобразит сгенерированный скрипт с использованием команды DROP TABLE.

Теперь у нас снова пустая схема, как было в самом начале.

Восстановление данных

Приступим к восстановлению данных. 

1. Через главное меню Workbench перейдем в раздел Server → Data Import. 

2. Выберем директорию, где сохранили дамп, отметим схему и все таблицы. Нажмем кнопку Start Import.

4. Обновим схему. Для этого в контекстном меню выберем пункт Refresh All. Важно: если пропустить этот этап, то  после окончания импорта мы не увидим таблицы.

5. Видим, что все таблицы восстановились. Проверим данные в них — все на месте. Импорт прошел успешно.

Этот сценарий может использоваться для создания бэкапов и их восстановления. Однако мы пользуемся СУБД на платформе Selectel, поэтому бэкапы создаются автоматически.

Заключение

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

Теперь вы можете подключаться к своим рабочим проектам или создавать архитектуры БД с нуля.