Содержание

Как канонично организовать структуру таблиц в базе данных MySQL? — Хабр Q&A

Добрый день!

Меня интересует вопрос того, как правильно организовать структуру таблиц в базе данных MySQL, если с её помощью надо описать следующую условную ситуацию:

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

Есть некоторое число заводов, однако не все из них подразделяются на цеха.

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

Самое незамысловатое решение, конечно же, это создать таблицу работников:

CREATE TABLE `worker` (
  `id` INT UNSIGNED NOT NULL ,
  `plant_id` INT NOT NULL,
  `department_id` INT NOT NULL
) ENGINE = InnoDB;

таблицу заводов:

CREATE TABLE `plant` (
  `id` INT UNSIGNED NOT NULL
) ENGINE = InnoDB;

и таблицу цехов:

CREATE TABLE `department` (
  `id` INT UNSIGNED NOT NULL,
  `plant_id` INT NOT NULL,
) ENGINE = InnoDB;

Однако данное решение содержит ряд неприятных моментов:

  • неработающий работник будет содержать в себе plant_id = NULL, department_id = NULL (хотя второе прямо вытекает из первого)
  • работник, работающий на заводе, где нет цехов, будет содержать в себе department_id = NULL (хотя, по большому счету, это свойство целого завода, а не конкретного работника)
  • работник, работающий на заводе, где есть цеха, буде содержать в себе plant_id = x, department_id = y, а в это же время в таблице department будет id = y, plant_id = x (т.е. будет некоторая избыточность)
Могли бы посоветовать каноничный, наиболее нормализованный и эталонный вариант решения данной задачи?

И если у цехов разных заводов будут разные свойства, их также не хотелось бы хранить в одной таблице. Как должны быть при этом организованы таблица(-ы) цехов, и как на них должен ссылаться завод и работник?

Спасибо.

Привилегии пользователя MySQL ▷ Права и управление. Как дать права на базу данных MySQL

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

Содержание

Права для пользователей MySQL

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

CREATE — позволяет создавать новые базы данных и таблицы

DROP — позволяет удалять базы данных или таблицы

INSERT — позволяет добавлять строки к таблице.

UPDATE — позволяет изменять содержание строк таблиц. Не путать с ALTER, которая позволяет изменять саму структуру таблиц (количество строк/столбцов, типы столбцов).

DELETE — противоположна INSERT — позволяет удалять строки из таблицы.

ALTER — позволяет изменять структуру таблиц. Требует CREATE и INSERT привилегии.

SELECT — позволяет читать (выводит строки) таблицы, используя выборки по столбцам и/или по некоторым арифметическим и логическим критериям.

GRANT OPTION — позволяет назначить конкретные права определенному пользователю (также и отобрать). Возможно дать/отобрать только те права, которыми назначающий сам располагает.

LOCK TABLES — блокирует таблицу на время искусственного внесения в нее изменений (администрирование), чтобы данные внутри нее не могли измениться своим естественным путем (во время рабочего процесса).

REFERENCES — позволяет создавать связь между таблицами по внешнему ключу.

EVENT — дает право на создание/изменение/удаление заданий для планировщика

TRIGGER — позволяет создавать/изменять/удалять триггеры (привязываемые к определенным таблицам), которые при выполнении операций DELETE, UPDATE или INSERT совершают дополнительные действия.

INDEX — привилегия даёт право добавлять/удалять индексы к (из) таблицам. Сами индексы назначаются вручную, и дают возможность сэкономить время на поиске строк.

CREATE TEMPORARY TABLES — позволяет создавать временные таблицы на время сессии.

CREATE VIEW — позволяет создать некоторое представление в виде таблицы, которая фактически не существует как единая и содержит лишь данные других таблиц. К примеру, в этом представлении можно собрать определенные сгруппированные данные из трёх таблиц (оператором SELECT) и по факту собранные данные будут лишь ссылаться на данные этих 3-х таблиц и также будут объединением, не нуждающимся в собственной таблице.

SHOW VIEW — позволяет проверить каким запросом (из каких данных состоит) создано определенное представление, заданное с помощью CREATE VIEW

CREATE ROUTINE — позволяет создать процедуру, которая является набором заготовленным набором SQL-команд.

ALTER ROUTINE — позволяет изменить процедуру, созданную посредством CREATE ROUTINE.

EXECUTE — позволяет вызывать готовые процедуры.

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

CREATE TABLESPACE (admin) — позволяет создавать/изменять/удалять пространства таблиц. Само это пространство является логическим и не связано со структурой БД или схемой. Оно декларирует расположение объектов БД на физических носителях и используется для оптимизации системы БД.

CREATE USER (admin) — позволяет создавать/изменять/переименовывать/удалять пользователей баз данных.

PROCESS (admin) — разрешает доступ к информации о потоках (процессах) исполняющихся на сервере.

PROXY (admin) — позволяет войти пользователем под видом другого пользователя. Используется администратором для проверки/отладки прав доступа у необходимого пользователя.

RELOAD (admin) — разрешает использование оператора FLUSH, который чистит кеш MySQL

REPLICATION CLIENT (admin) — позволяет выполнять операции SHOW MASTER STATUS, SHOW SLAVE STATUS и SHOW BINARY LOG.

REPLICATION SLAVE (admin) — данная привилегия необходима пользователям ведомого сервера БД, чтобы этот сервер мог подключаться к ведущему серверу в роли ведомого. Без этой привилегии ведомые сервера не смогут запрашивать обновления баз данных и таблиц у ведущего сервера.

SHOW DATABASES (admin) — позволяет выполнять оператор SHOW DATABASES. Пользователи, не имеющие подобной привилегии, при выполнении данного оператора смогут лишь увидеть базы данных к которым у них есть какие-либо права.

SHUTDOWN (admin) — привилегия позволяет выполнить оператор SHUTDOWN, выключающий MySQL сервер.

SUPER (admin) — привилегия, дающая право на множество операций:

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

ALL (admin) — пользователю, получившему данную привилегию, автоматически назначаются все права в рамках уровня привилегий (возможных привилегий в принципе, согласно контексту выдачи привилегий). Не назначается только привилегия GRANT OPTION в данном случае.

Назначение прав для пользователей MySQL в панелях управления хостингом

Рассмотрим далее процедуру назначения прав пользователям MySQL в таких панелях управления хостингом, как:

  • DirectAdmin
  • cPanel
  • ISPmanager
  • Webuzo

DirectAdmin

На главной странице DirectAdmin из под уровня пользователя в меню Your Account переходим в раздел MySQL Management:

Далее нам необходимо перейти в конкретную базу данных, чтобы управлять пользователями, имеющими доступ к ней или создать новую посредством ссылки Create new Database:

Тут мы можем как создать нового пользователя для данной базы путем перехода по Create New Database User, так и привязать к ней существующего,. Следует отметить, что нет специально отведенного интерфейса для управления пользователями. Он доступен только посредством перехода через какую-либо базу данных. Чтобы дать пользователю права — переходим по ссылке modify privileges

:

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

После этого произойдет переход на страницу подтверждения сохранения. Всё, права выданы.

cPanel

На главной странице cPanel нам необходимо найти раздел Базы данных в нем перейти по Базы данных MySQL:

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

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

Раздел Текущие базы данных обновится:

Создаем пользователя:

Раздел Текущие пользователи обновится:

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

и добавить необходимого пользователя к необходимой базе данных:

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

Кнопка «Все права» эквивалентна привилегии ALL, описанной в начале руководства, и назначит все возможные права пользователю в контексте принадлежности пользователя определенной группе пользователей на уровне всего MySQL сервера.

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

Готово. Пользователь назначен базе данных.

ISPmanager Lite 5

При входе в ISPmanager в роли какого-либо пользователя необходимо перейти в Инструменты -> Базы данных из левого меню.

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

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

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

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

По-умолчанию при создании пользователя и БД назначаются все права. Изменяем те, которые нам необходимо и сохраняем результаты. После этого Вас вернет на страницу управления пользователями баз данных.

Webuzo

Webuzo состоит из 2-х панелей: администраторская и пользовательская. Переходим в пользовательскую панель и на главной странице выбираем Manage Databases

На открывшейся странице мы можем:

  • увидеть список существующих баз данных [Database(s)];
  • создать новую базу данных [Create Database];
  • увидеть список существующих пользователей баз данных [Database User(s)];
  • создать пользователя баз данных и назначить его определенной базе данных [Add User To Database]

Если целевой базы данных пока что не существует, то переходим в Create Database и создаем новую базу данных:

Если все же целевая база данных уже существует, то в управлении базами данных нам необходимо перейти в Add User To Database и создать нового пользователя БД или указать какого-либо существующего для его привязки к базе данных:

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

При успешном изменении прав в текущем окне появится надпись Database Privileges Updated. Задача выполнена.

Navicat for MySQL | Инструмент для разработки и администрирования баз данных MySQL

Navicat Cloud

Navicat предоставляет пользователям облачный сервис Navicat Cloud. Сохраните в облаке Ваши настройки подключения и запросы, чтобы иметь к ним доступ в режиме реального времени в любое время и в любом месте. С Navicat Cloud каждая минута Вашего дня может быть продуктивной. Узнать больше >

Безопасное соединение

Navicat позволяет создавать безопасные сеансы связи SSH посредством SSH-туннелирования, обеспечивая строгую аутентификацию и надёжно зашифрованное соединение между двумя хостами. Метод аутентификации может использовать пароль или ключевую пару (открытый и закрытый ключи). Также Navicat имеет возможность HTTP-туннелирования, позволяя обойти ограничения провайдеров на прямые соединения с их серверами баз данных.

Дизайнер объектов

Вы можете создавать, модифицировать и разрабатывать объекты базы данных при помощи профессиональных инструментов для дизайна любых объектов баз данных, таких как таблицы, представления, функции / процедуры и события. Вам больше не придётся писать сложные SQL-скрипты для создания или редактирования объектов, и Вы будете четко понимать, над чем Вы сейчас работаете.

Просмотрщик таблиц

Отображение в виде сетки

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

Извлечение данных по внешнему ключу

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

Конструктор / редактор SQL

Конструктор SQL

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

Завершение кода

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

Полезные инструменты

Древовидный фильтр объектов

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

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

iOS

Совместимость: Требуется iOS 7.0 и выше. Совместим с iPhone, iPad и iPod touch.
Это приложение оптимизировано для iPhone 5, iPhone 5C, iPhone 5S, iPhone 6, iPhone 6 Plus, iPhone 6S, iPhone 6S Plus, iPhone SE, iPhone 7, iPhone 7 Plus, iPhone 8, iPhone 8 Plus, iPhone X, iPhone XR, iPhone XS и iPhone XS Max.

Только Navicat for MySQL, Navicat for MariaDB и Navicat for PostgreSQL.
(Другая продукция скоро появится в продаже.)

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

Windows

Microsoft Windows Vista, Windows 7, Windows 8, Windows 8.1, Windows 10, Server 2008, Server 2012, Server 2016, Server 2019

macOS

Mac OS X 10.11 El Capitan, macOS 10.12 Sierra, macOS 10.13 High Sierra, macOS 10.14 Mojave, macOS 10.15 Catalina

Linux

Debian 9 и выше, Ubuntu 16.04 и выше, CentOS 7 и выше, Fedora 26 и выше, Linux Mint 18 и выше

Шпаргалка по командам MySQL ::

Управление пользователями

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

#mysql> SELECT User,Host FROM mysql.user;

Список прав у пользователя [email protected]

#mysql> SHOW GRANTS FOR [email protected];

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

#mysql> CREATE USER ‘user’@’localhost’ IDENTIFIED BY ‘secret’;

Добавим выбранные привилегии для всех таблиц БД dbname пользователю ‘user’@’localhost’

#mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON dbname.* TO ‘user’@’localhost’;

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

#mysql> GRANT ALL PRIVILEGES ON dbname.* TO ‘user’@’localhost’;

Удаление прав пользователя ‘user’@’localhost’ для БД dbname:

#mysql> REVOKE ALL ON dbname.* FROM ‘user’@’localhost’;

Перезагрузка привилегий

#mysql> FLUSH PRIVILEGES;

Новый пароль для root

#mysqladmin -uroot password ‘secret’

.
Управление базой данных

Создание базы данных

#mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;

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

#mysqladmin -u root -p create dbname

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

#mysqladmin -u root -p drop dbname

.
Cтатистика

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

#mysql> SHOW DATABASES;

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

#mysql> SHOW TABLES;

Статистика по работе сервера

#mysql> SHOW GLOBAL STATUS;

.
Оптимизация
При помощи команды mysqlcheck можно выполнять проверку, оптимизацию и исправление ошибок.

Поверка на ошибки БД dbname

#mysqlcheck -p dbname

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

#mysqlcheck -Aor -p

аргументы:
-p – использовать пароль
-A – проверять все базы данных
-r – ремонтировать БД
-o – оптимизировать БД

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

#mysqlcheck —repair —analyze —optimize —all-databases —auto-repair -u root -pSECRET

Установим mysqltuner, который показывает статистику по работающему mysql-серверу и дает полезные советы по оптимизации.

#cd /usr/local/bin
#wget http://mysqltuner.pl/mysqltuner.pl
#chmod +x mysqltuner.pl

Запустим

#/usr/local/bin/mysqltuner.pl

При запуске скрипт спросит логин и пароль для root’a. После корректной авторизации будет выведена статистика и разные советы по правке конфигурационного файла MySQL.

Дамп (резервная копия)

Дамп базы данных

#mysqldump -u root -p dbname > dump.sql

Дамп выбранных баз

#mysqldump -u root -p -B dbname1 dbname2 > dump.sql

Дамп всех баз

#mysqldump -u root -p -A > dump.sql

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

#mysqldump -u root -p —no-data dbname > database.sql

Другие опции
—add-drop-table — добавляет команду DROP TABLE перед каждой командой CREATE TABLE
—add-locks — добавляет команду LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы
—no-create-db, -n — не добавлять команду CREATE DATABASE, которая добавляется при использовании параметров —databases и —all-databases
—no-data, -d — дампить только структуру таблиц
—no-create-info, -t — не создавать команду CREATE TABLE
—skip-comments — не выводить комментарии.
—compact — использовать компактный формат
—create-options — добавляет дополнительную информацию о таблице в команду CREATE TABLE: тип, значение AUTO_INCREMENT и т.д. Не нужные опции можно вырезать с помощью sed.
—extended-insert, -e — применение команды INSERT с многострочным синтаксисом (повышает компактность и быстродействие операторов ввода)
—tables — дампить только таблицы из списка, следующего за этим параметром, разделитель — пробел

Применение дампа (импорт)

#mysql -uroot -p dbname1

Определение кодировки файла
file —mime-encoding dump.sql
Конвертирование из кодировки latin1 в utf8
mysqldump —add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname

Восстановление root-пароля

#service mysqld stop
#mysqld_safe —skip-grant-tables &
#mysql

#mysql> UPDATE mysql. user SET Password=PASSWORD(‘secret’) WHERE User=’root’;
#mysql> FLUSH PRIVILEGES;
#mysql> \q

#service mysqld restart

Спонсор: Хостинг HostiMan

Утилита mysqldump и шпаргалка по параметрам / Мастерская интернет-разработчика

7 июня 2009 г. MyISAM MySQL InnoDB Бэкап

Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

Создание дампа

Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:

mysqldump -uroot -h82.82.82.82 -p database > database.sql

где:

  • -u или -–user=… — имя пользователя
  • -h или —host=… — удаленный хост (для локального хоста можно опустить этот параметр)
  • -p или —password — запросить пароль
  • database — имя базы данных
  • database. sql — файл для дампа


Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр —databases (или сокращенно -B), пример:

mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:

mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

Развертывание дампа

Перенаправляем поток в обратную сторону и развертываем базу данных:

mysql -uroot -h82.82.82.82 -p database < database.sql

Или через mysql-console:

mysql> use database;
mysql> source database.sql

Ну, а если у нас gz-архив к примеру, то:

zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database

Пример использование некоторых параметров

Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:

mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql

Или нам нужна только структура, без данных:

mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql

Примеры навеяны постом Александра Макарова — http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Делаем дамп только триггеров, процедур и событий:

mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p database | gzip > ~/database.sql.gz

Шпаргалка по параметрам

Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.


—add-drop-database

Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.
—add-drop-table

Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.
—add-locks

Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
—all-databases, -A

Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
—allow-keywords

Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.
—comments, -i

Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.
—compact

Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments.
—compatible=name

Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.
—complete-insert, -c

Используется полная форма оператора INSERT (с именами столбцов).
—create-options

Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.
—databases, -B

Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.
—delayed

Использовать команду INSERT DELAYED при вставке строк.
—delete-master-logs

На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data».
—disable-keys, -K

Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.
—extended-insert, -e

Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
—flush-logs, -F

Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.
—force, -f

Продолжать даже если в процессе создания дампа произошла ошибка.
—hex-blob

Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0x616263.
—ignore-table=db_name.tbl_name

Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.
—insert-ignore

Добавляет ключевое слово IGNORE в оператор INSERT.
—lock-all-tables, -x

Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.
—lock-tables, -l

Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.
—no-autocommit

Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.
—no-create-db, -n

Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров —databases и —all-databases.
—no-data, -d

Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.
—opt

Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset. Начиная с MySQL 4.1, параметр —opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров —skip-opt
—order-by-primary

Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.
—port, -P

Номер TCP порта, используемого для подключения к хосту.
—protocol={TCP|SOCKET|PIPE|MEMORY}

Параметр позволяет задать протокол подключения к серверу.
—quick, -q

Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.
—quote-names, -Q

Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.
—replace

Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.
—result-file=/path/to/file, -r /path/to/file

Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.
—routines, -R

Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.
—single-transaction

Параметр создает дамп в виде одной транзакции.
—skip-comments

Данный параметр позволяет подавить вывод в дамп дополнительной информации.
—socket=/path/to/socket, -S /path/to/socket

Файл сокета для подсоединения к localhost.
—tab=/path/, -T /path/

При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров —fields-xxx и —lines-xxx.
—tables

Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.
—triggers

Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.
—events, -E

Создается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL.
—tz-utc

при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах.
—verbose, -v

Расширенный режим вывода. Вывод более детальной информации о работе программы.
—version, -V

Вывести информацию о версии программы.
—where=’where-condition’, -w ‘where-condition’

Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.
—xml, -X

Представляет дамп базы данных в виде XML.
—first-slave, -x

Блокирует все таблицы во всех базах данных.
—debug=…, -#

Отслеживать прохождение программы (для отладки).
—help

Вывести справочную информацию и выйти из программы.

Еще пару слов о бекапе в MySQL

mysqlhotcopy для MyISAM

Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:

# mysqlhotcopy db_name /path/to/dir

Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.

xtrabackup для InnoDB

Для InnoDB есть xtrabackup, рекомендую посмотреть!
UPD: XtraBackup — резервное копирование для innoDB

Бин-лог и репликации

Для репликации «mysqldump» не предназначена, для этого есть бин-лог (—log-bin):

# mysqlbinlog binlog.[0-9]* | mysql

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

Резервирование данныс в MySQL 6.x

С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:

MySQL :: Справочное руководство MySQL 8.0 :: 13.7.7.14 SHOW DATABASES Заявление

13.7.7.14 ПОКАЗАТЬ БАЗЫ ДАННЫХ Заявление
  ПОКАЗАТЬ {БАЗЫ ДАННЫХ | СХЕМЫ}
    [LIKE ' pattern ' | ГДЕ  выражение ]  

ПОКАЗАТЬ БАЗЫ ДАННЫХ перечисляет базы данных на хосте сервера MySQL. ШОУ SCHEMAS является синонимом SHOW. БАЗЫ ДАННЫХ . КАК предложение, если оно присутствует, указывает, какие имена баз данных должны соответствовать.В WHERE Предложение может использоваться для выбора строк используя более общие условия, как описано в Раздел 26.55, «Расширения операторов SHOW».

Вы видите только те базы данных, для которых у вас есть какие-то привилегия, если у вас нет глобального SHOW БАЗЫ ДАННЫХ привилегия. Вы также можете получить этот список, используя команда mysqlshow .

Если сервер был запущен с --skip-show-database вариант, вы не может использовать этот оператор вообще, если у вас нет ПОКАЗАТЬ БАЗЫ ДАННЫХ привилегия.

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

Информация о базе данных также доступна на INFORMATION_SCHEMA SCHEMATA таблица. Видеть Раздел 26.31, «Таблица INFORMATION_SCHEMA SCHEMATA».

Осторожность

Поскольку любая статическая глобальная привилегия считается привилегией для всех баз данных любая статическая глобальная привилегия позволяет пользователю чтобы увидеть все имена баз данных с SHOW БАЗЫ ДАННЫХ или изучив SCHEMATA таблица INFORMATION_SCHEMA , кроме баз данных, были ограничены на уровне базы данных частичными отзывами.


3.5 Использование mysql в пакетном режиме

3.5 Использование mysql в пакетном режиме

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

  оболочка> mysql < командный файл   

Если вы используете mysql под Windows и у вас некоторые специальные символы в файле, вызывающие проблемы, вы можете сделай это:

  C: \> mysql -e "исходный файл , командный файл "  

Если вам нужно указать параметры подключения в командной строке, команда может выглядеть так:

  оболочка> mysql -h  host  -u  user  -p < batch-file 
Введите пароль: ********  

Когда вы используете mysql таким образом, вы создаете файл сценария, а затем выполнить сценарий.

Если вы хотите, чтобы сценарий продолжался, даже если некоторые из операторов в нем возникают ошибки, вы должны использовать --force параметр командной строки.

Зачем нужен скрипт? Вот несколько причин:

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

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

  • Пакетный режим также может быть полезен при разработке запроса, особенно для многострочных операторов или последовательности из нескольких операторов. Если вы ошиблись, вы этого не сделаете. придется все перепечатать. Просто отредактируйте свой скрипт, чтобы исправить ошибка, затем скажите mysql выполнить ее очередной раз.

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

      оболочка> mysql < командный файл  | более  
  • Вы можете записать вывод в файл для дальнейшей обработки:

      оболочка> mysql < командный файл > mysql.из  
  • Вы можете распространять свой сценарий среди других людей, чтобы они также можно запускать операторы.

  • Некоторые ситуации не позволяют интерактивное использование, например, когда вы запускаете запрос из задания cron . В В этом случае вы должны использовать пакетный режим.

Формат вывода по умолчанию отличается (более кратким) при запуске mysql в пакетном режиме, чем когда вы его используете интерактивно.Например, вывод SELECT DISTINCT вид ОТ питомца выглядит так при mysql запускается в интерактивном режиме:

  + --------- +
| виды |
+ --------- +
| птица |
| кошка |
| собака |
| хомяк |
| змея |
+ --------- +  

Вместо этого в пакетном режиме результат выглядит так:

  видов
птица
Кот
собака
хомяк
змея  

Если вы хотите получить интерактивный формат вывода в пакетном режиме, используйте mysql -t .Для вывода на выход выполняемые операторы используйте mysql -v .

Вы также можете использовать сценарии из приглашения mysql с помощью команды source или \. команда:

  mysql> исходный код  filename ;
mysql> \.  имя файла   

См. Раздел 4.5.1.5, «Выполнение операторов SQL из текстового файла» для получения дополнительной информации.

Список таблиц и их структура с помощью клиента командной строки MySQL

Клиент командной строки MySQL позволяет выполнять запросы sql из интерфейса командной строки.В этом посте рассматривается, как отображать таблицы в конкретной базе данных и описывать их структуру. Это продолжение серии о клиенте командной строки MySQL. Предыдущие сообщения включают использование инструмента командной строки MySQL и выполнение запросов из командной строки MySQL.

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

 mysql> show tables;
 

(mysql> — это командная строка, а «показать таблицы;» — это фактический запрос в приведенном выше примере).

В настроенной мной тестовой базе данных это возвращает следующее:

 + ---------------- +
| Tables_in_test |
+ ---------------- +
| что-то |
| something_else |
+ ---------------- +
2 ряда в наборе (0,00 сек)
 

Это показывает нам, что в базе данных есть две таблицы, которые называются «something» и «something_else». Мы можем показать структуру таблицы с помощью команды «desc», например, для таблицы «something»:

 mysql> desc something;
 

Моя тестовая таблица базы данных возвращает такой результат, показывающий, что есть 4 столбца и какие они типы и т. Д.:

 + -------------- + -------- ---------- + ------ + ----- + ------------------- + ------ ---------- +
| Поле | Тип | Null | Ключ | По умолчанию | Экстра |
+ -------------- + ------------------ + ------ + ----- + - ----------------- + ---------------- +
| something_id | int (10) без знака | НЕТ | PRI | NULL | auto_increment |
| имя | варчар (50) | НЕТ | | NULL | |
| значение | варчар (50) | НЕТ | | NULL | |
| ts_updated | отметка времени | ДА | MUL | CURRENT_TIMESTAMP | |
+ -------------- + ------------------ + ------ + ----- + - ----------------- + ---------------- +
4 ряда в наборе (0. 00 сек)
 

Наконец, вы можете показать индексы из конкретной таблицы следующим образом:

 mysql> показать ключи от чего-то;
 

Моя тестовая база данных имеет два индекса (они помечены в столбце «key» из вывода «desc something» выше как PRI и MUL). Результат выполнения приведенной выше команды выглядит следующим образом:

 + ----------- + ------------ + ------------ + -------------- + -------------- + ----------- + ------- ------ + ---------- + -------- + ------ + ------------ + --- ------ +
| Стол | Non_unique | Key_name | Seq_in_index | Column_name | Сортировка | Мощность | Под_часть | Упаковано | Null | Index_type | Комментарий |
+ ----------- + ------------ + ------------ + ----------- --- + -------------- + ----------- + ------------- + ----- ----- + -------- + ------ + ------------ + --------- +
| что-то | 0 | ПЕРВИЧНЫЙ | 1 | something_id | А | 2 | NULL | NULL | | BTREE | NULL |
| что-то | 1 | ts_updated | 1 | ts_updated | А | NULL | NULL | NULL | | BTREE | NULL |
+ ----------- + ------------ + ------------ + ----------- --- + -------------- + ----------- + ------------- + ----- ----- + -------- + ------ + ------------ + --------- +
2 ряда в наборе (0.00 сек)
 

Сводка

Клиент командной строки MySQL полезен для выполнения запросов, а также для отображения таблиц в базе данных MySQL, структуры этих таблиц и индексов в этих таблицах, как описано в этом сообщении.

Как использовать DESCRIBE и EXPLAIN в MySQL?

В MySQL операторы DESCRIBE и EXPLAIN являются синонимами, используемыми либо для получения информации о структуре таблицы, либо для планов выполнения запросов.

1.Для описания таблицы:

Несмотря на то, что операторы DESCRIBE и EXPLAIN являются синонимами, оператор DESCRIBE больше используется для получения информации о структуре таблицы, а оператор EXPLAIN используется для получения плана выполнения запроса.

Оператор DESCRIBE — это ярлык для оператора SHOW COLUMN :

эквивалентно этому заявлению SHOW COLUMN :

  ПОКАЗАТЬ КОЛОННЫ ИЗ имя_таблицы;
  

Или вы также можете использовать краткую форму описания:

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

Вместо DESCRIBE или DESC вы можете использовать оператор EXPLAIN, который работает так же:

2. Описание плана выполнения запроса

Мы часто используем EXPLAIN . Он предоставляет информацию о том, как ваша база данных SQL выполняет запрос.

EXPLAIN работает с операторами SELECT , DELETE , INSERT , REPLACE и UPDATE . Также требуется привилегия SELECT для любых таблиц или представлений, к которым осуществляется доступ, включая любые базовые таблицы представлений.Для представлений EXPLAIN также требует привилегии SHOW VIEW.

  EXPLAIN SELECT * FROM имя_таблицы;
  

На практике оператор EXPLAIN используется для оптимизации запросов. Если у вас медленный запрос, он может показать вам, где следует добавить индексы для ускорения, или проверить, объединяет ли оптимизатор таблицы в оптимальном порядке.


Нужен хороший инструмент с графическим интерфейсом для MySQL? TablePlus — это современный собственный инструмент с элегантным пользовательским интерфейсом, который позволяет одновременно управлять несколькими базами данных, такими как MySQL, PostgreSQL, SQLite, Microsoft SQL Server и другими.

Загрузить TablePlus для Mac . В любом случае это бесплатно!

Не на Mac? Загрузите TablePlus для Windows .

В Linux? Загрузить TablePlus для Linux

Требуется быстрое редактирование на ходу? Скачать TablePlus для iOS .

Как получить структуру базы данных в MySQL с помощью запроса

В следующем примере

детская площадка — это имя базы данных, а оборудование — имя таблицы

Другой способ — использовать SHOW-COLUMNS: 5.5 (имеется также для 5.5> )

  $ mysql -uroot -p <пароль> -h <хост> -P <порт> -e \
    «ПОКАЗАТЬ КОЛОННЫ ИЗ ДЕТСКОЙ ОБОРУДОВАНИЯ»
  

А на выходе:

  mysql: [Предупреждение] Использование пароля в интерфейсе командной строки может быть небезопасным.
+ ------- + ------------- + ------ + ----- + --------- + ---- ------------ +
| Поле | Тип | Null | Ключ | По умолчанию | Экстра |
+ ------- + ------------- + ------ + ----- + --------- + ---- ------------ +
| id | int (11) | НЕТ | PRI | NULL | auto_increment |
| тип | варчар (50) | ДА | | NULL | |
| квант | int (11) | ДА | | NULL | |
| цвет | варчар (25) | ДА | | NULL | |
+ ------- + ------------- + ------ + ----- + --------- + ---- ------------ +
  

Можно также использовать mysqlshow-client (также доступен для 5.5> ) как показано ниже:

  $ mysqlshow -uroot -p <пароль> -h <хост> -P <порт> \
    игровое оборудование
  

А на выходе:

  mysqlshow: [Предупреждение] Использование пароля в интерфейсе командной строки может быть небезопасным.
База данных: детская площадка Таблица: оборудование
+ ------- + ------------- + ------------------- + ------ + ----- + --------- + ---------------- + ----------------- ---------------- + --------- +
| Поле | Тип | Сортировка | Null | Ключ | По умолчанию | Экстра | Привилегии | Комментарий |
+ ------- + ------------- + ------------------- + ------ + ----- + --------- + ---------------- + ----------------- ---------------- + --------- +
| id | int (11) | | НЕТ | PRI | | auto_increment | выбрать, вставить, обновить, ссылки | |
| тип | варчар (50) | latin1_swedish_ci | ДА | | | | выбрать, вставить, обновить, ссылки | |
| квант | int (11) | | ДА | | | | выбрать, вставить, обновить, ссылки | |
| цвет | варчар (25) | latin1_swedish_ci | ДА | | | | выбрать, вставить, обновить, ссылки | |
+ ------- + ------------- + ------------------- + ------ + ----- + --------- + ---------------- + ----------------- ---------------- + --------- +
  

Как мне показать схему таблицы в базе данных MySQL?

Как показать схему таблицы в базе данных MySQL? — Переполнение стека

Присоединяйтесь к Stack Overflow , чтобы учиться, делиться знаниями и строить свою карьеру.

Спросил

Просмотрено 507k раз

Какая команда из консоли MySQL отображает схему любой данной таблицы?

HDJEMAI

7,5813939 золотых знаков5959 серебряных знаков8080 бронзовых знаков

Создан 30 сен.

ламблин

40.4k1919 золотых знаков9090 серебряных знаков127127 бронзовых знаков

1
  описать [db_name.] Table_name;
  

для форматированного вывода или

  показать создать таблицу [db_name.] Table_name;
  

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