Лекция 17 -2013_2014 • Подробнее о типах таблиц в My. SQL • Установка My. SQL сервера • Переустановка сервера • Работа с учетными записями пользователей My. SQL Server • Привилегии и права • Режимы кодировки и сравнения 1
Основные движки Maria. DB My. SQL — два кита: движки Inno. DB и My. ISAM. Maria. DB использует свои собственные движки. Aria пришел на замену My. ISAM, более производителен благодаря построчному кэшированию и оптимизированному формату упаковки данных, транзакционный. Maria. DB использует улучшенные форматы хранения данных, поэтому быстрее восстанавливается после сбоев. Принадлежащий Oracle движок Inno. DB заменен на Xtra. DB, разработку компании Percona. Для обратной совместимости с My. SQL движок Xtra. DB в Maria. DB называется Inno. DB. 2
Подробнее о типах таблиц в My. SQL My. ISAM – платформо-независимый тип таблиц. Не транзакционные. Для My. ISAM характерно наличие трех файлов: *. MYI – индексные файлы, *. MYD – данные, *. frm – схема таблицы. My. SQL хранит счетчик подключений к таблице My. ISAM. Когда таблица закрывается, счетчик сбрасывается в нуль. Это используется для диагностики повреждений таблиц. Для автоинкрементных столбцов таблиц My. ISAM программа My. SQL ведет внутренний счетчик, значения этого столбца никогда не используются повторно. 3
Подробнее о типах таблиц в My. SQL Таблицы My. ISAM могут быть фиксированные, динамические либо сжатые. Таблица имеет записи фиксированной длины, если в ней нет столбцов типа VARCHAR, BLOB или TEXT. Фиксированный тип таблиц: • очень быстрый; • легко кешируется; • легко восстанавливается после повреждения; • не нужна дефрагментация; • требуют больше места, чем динамические таблицы. 4
Подробнее о типах таблиц в My. SQL Все записи таблицы будут динамическими, если в ней есть столбцы типа VARCHAR, BLOB или TEXT. Динамический тип таблиц: • все текстовые столбцы – динамические; • при хранении каждой строке ставится в соответствие «карта » ряда, где отмечены пустые строки и столбцы с нулевым числовым значением длины; • занимают меньше места, чем фиксированные; • если строки становятся большими – они разбиваются на фрагменты и хранятся отдельно; • может возникать фрагментация дискатяжело восстанавливать поврежденные таблицы; • по мере распределения записей по файлу время поиска данных возрастает. 5
Подробнее о типах таблиц в My. SQL Сжатые таблицы имеют формат Read Only. В них могут храниться как фиксированные так и динамические таблицы. Каждая запись сжимается отдельно с применением отдельной хэш-таблицы для каждого столбца. Сжатая таблица создается утилитой myisampack. С помощью утилиты myisamchk можно преобразовать сжатую таблицу обратно в фиксированный или динамический формат. 6
Подробнее о типах таблиц в My. SQL Таблицы типа myisam переносимы. База данных должна содержать таблицы одного типа, или этот вопрос должен быть хорошо продуман. БД с таблицами myisam представляет собой отдельную директорию с именем базы данных и файлами *. myi, *. myd, *. frm для каждой таблицы. БД располагается в директории, установленной при инсталляции сервера. По умолчанию: Windows XP – c: Documents and SettingsAll UsersApplication. DataMy. SQLData Windows 7, 8 – c: Program DataMy. SQLData Папки Application. Data и Program Data – скрытые. Чтобы их увидеть, нужно установить в свойствах папки опцию 7 «Показывать скрытые файлы и папки»
Подробнее о типах таблиц в My. SQL Inno. DB – транзакционные. Данные Inno. DB таблиц всех пользователей хранятся в больших совместно используемых файлах. Размер файлов определяется в конфигурационном файле. Таблицы Inno. DB блокируются на уровне записей. На случай отмены транзакций ведется журнал транзакций. Он подвержен внутренней ротации. Имеет ряд ограничений на количество столбцов и на индексирование. Ряд недостатков устранен в последних версиях My. SQL. 8
Подробнее о типах таблиц в My. SQL Heap (Memory) – не транзакционные таблицы. Таблицы Heap хранятся в памяти, доступ к ним осуществляется чрезвычайно быстро. Для поиска записей применяется хэш-таблица. Резидентные таблицы не могут иметь столбцы типа BLOB или TEXT. Нельзя использовать флаг AUTO_INCREMENT. Можно создавать индексы, но нельзя индексировать столбцы, допускающие значения NULL. Записи резидентных таблиц имеют фиксированную длину. Доступ к резидентным таблицам имеют все пользователи. Эти таблицы уничтожаются при 9 выключении сервера.
Подробнее о типах таблиц в My. SQL Пример 1. Создание таблицы в памяти для суммирования количества подключений с определенного IP адреса в указанный промежуток времени. Create table TEST engine=memory Select Ip, sum(Down. Loads) as Down From Log_table Where (dt>= 2015 -02 -01) and (dt<= 2015 -02 -28) Group by Ip; 10
Подробнее о типах таблиц в My. SQL Merge – не транзакционный тип. В таблице типа Merge группируется несколько таблиц My. ISAM одинаковой структуры. Программа My. SQL создает файл с расширением *. MRG, в котором содержится список таблиц с их структурой. Недостатки объединенных таблиц: • в них нельзя вставлять записи; • извлечение данных из объединенных таблиц осуществляется медленнее, чем из таблиц других типов. 11
Система привилегий сервера My. SQL Идентификатор (ID) доступа – это имя пользователя. Ключевое слово USER – Идентификатор доступа. Основная функция системы привилегий My. SQL – аутентификация пользователя и ассоциирование его с привилегиями базы данных. Дополнительные функции системы привилегий: 1. обслуживания анонимного пользователя; 2. предоставление привилегий для LOAD DATA INFILE; 3. предоставление привилегий для администрирования.
Система привилегий сервера My. SQL При соединении с сервером My. SQL личность устанавливается по имени хоста и имени пользователя. Управление доступом в My. SQL: 1. Этап 1: сервер проверяет, имеется ли у личности, запросившей соединение, разрешение на это. 2. Этап 2: если разрешение имеется, сервер проверяет каждый запрос, чтобы убедиться, что у личности имеется достаточно привилегий для его выполнения.
Система привилегий сервера My. SQL Сервер использует таблицы user, db и host из БД mysql для управления доступом. Имя таблицы user db host Поля контекста Host User Db Db Password User Поля Select_priv … привилегий Наличие/отсутствие привилегии – 1 или 0 в соответствующем столбце привилегий каждой системной таблицы.
Система привилегий сервера My. SQL Привилегии доступа могут быть установлены для отдельной таблицы и даже столбика. На втором этапе управления доступом при верификации запросов сервер может дополнительно обратиться за этими привилегиями к таблицам tables_priv и columns_priv. Использование сервером таблиц привилегий: 1. По полям host, user, рassword таблицы user – разрешить подсоединение или отказать в нем. Условие соединения – нахождение в таблице user записи, в которой имя хоста, имя пользователя и пароль совпадают с введенными в запросе на подсоединение к серверу.
Система привилегий сервера My. SQL Таблицы db и host используются совместно: 1. Поля таблицы db определяют, каким пользователям, при подсоединении с каких хостов разрешен доступ к каким базам данных. 2. Таблица host используется в качестве расширения таблицы db в случае, если необходимо применить некоторую запись из таблицы db к разным хостам. Таблицы tables_priv и columns_priv подобны таблице db, но областью их действия является уровень таблиц и столбцов.
Аутентификация пользователя Значения в полях таблицы user : 1. В поле host имя хоста | IP-адрес | 'localhost‘; 2. В поле host можно использовать шаблоны `%' и `_'; 3. Значение '%' в поле host – любое имя хоста; 4. В поле user запрещено использовать шаблонные символы, но пустое значение разрешено; 1. Поле рassword может быть пустым.
Установка My. SQL сервера Дистрибутив My. SQL: http: //dev. mysql. com/downloads. Файлы с расширением msi – инсталляторы. My. SQL GUI Tools 5. 0 r 17 – My. SQL Migration Toolkit, My. SQL Administrator, My. SQL Query Browser. Файл с расширением ZIP предполагает установку с командной строки
Установка My. SQL сервера Флажок Firewall; Режим кодировки Задать пароль root; для
My. SQL System Tray Monitor включен Остановить или стартовать сервис My. SQL
Переустановка My. SQL сервера 1. Через Configuration Wizart 1. Через Tray Monitor
Переустановка My. SQL сервера 2. Control Panel. Удалить ПО My. SQL; 3. В папке Program. Files удалить папку My. SQL. 4. Windows 7 C: Program. Data Сделать папку видимой, поменяв свойства папки, из папки My. SQLData вырезать и сохранить БД пользователей с My. Isam таблицами. Очистить. Windows XP 5. Установить My. SQL сервер и GUI Tools
Учетные записи пользователей, My. Sql. Admin 1. Входим как root, создаем новую учетную запись, localhost, создаем БД, все права только на эту БД, Не разрешаем создавать новые учетные записи
Учетные записи пользователей, My. Sql. Admin 1. Входим как новый юзер, обращение к функциям администратора вызывает ошибку. Не имеем права.
Учетные записи пользователей, My. Sql. Admin 1. Входим как новый юзер, обращаемся к своей БД. Работаем только с ней. На создание новых БД прав не имеем.
Учетные записи пользователей, My. Sql. Admin Входим как root, создаем учетную запись, даем вход с localhost, Хотим дать права на создание БД. Tools Options включить возможность работать с глобальными привилегиями и с привилегиями объектов
Учетные записи пользователей, My. Sql. Admin Теперь для новой учетной записи даем глобальные привилегии.
Учетные записи пользователей, My. Sql. Admin Входим с логином, имеющим глобальные права. Создаем БД.
Учетные записи пользователей. Права. Пример 1. Создание новых пользователей, команда GRANT: GRANT ALL PRIVILEGES ON *. * TO Ivanov@localhost IDENTIFIED BY 'some_pass‘ WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *. * TO Ivanov@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; Создание нового суперпользователя Ivanov. Он может подсоединяться к серверу откуда угодно с паролем some_pass.
Учетные записи пользователей. Права. Отмена привилегий, команда REVOKE: Пример 2. Убрать все привилегии. REVOKE ALL PRIVILEGES, GRANT OPTION FROM Ivanov; Чтобы использовать команду REVOKE нужно иметь глобальные CREATE USER или UPDATE привилегии. Команда REVOKE удаляет привилегии, но не удаляет учетную запись. Чтобы удалить пользователя необходимо применить DELETE или DROP USER команды.
Учетные записи пользователей. Права. Пример 3. Дать права юзеру 'newuser‘ с localhost только к базе данных 'wordpress': GRANT ALL PRIVILEGES ON wordpress. * TO 'newuser'@'localhost'; Пример 4. Дать все права пользователю к БД с любых ip-адресов GRANT ALL PRIVILEGES ON `<БД>`. * TO `<имя>`@`%` WITH GRANT OPTION; Пример 5. Забрать у пользователя право доступа к БД с любых ip -адресов и дать только локальный доступ: REVOKE ON `<БД>`. * FROM `<имя>`@`%`; FLUSH PRIVILEGES; GRANT ON `<БД>`. * TO `<имя>` @ `localhost`; Пример 6. Удалить пользователя DROP USER 'newuser'@'localhost';
Учетные записи пользователей. Права. Привилегии вступают в силу: 1. При запуске сервера My. SQL; 2. Немедленно, если изменения внесены в таблицы командами GRANT, REVOKE или SET PASSWORD; • Отложенным образом, если изменения в таблицы внесены вручную командами INSERT, UPDATE и т. д. , необходимо запускать оператор FLUSH PRIVILEGES, mysqladmin flush-privileges или mysqladmin reload, чтобы указать серверу на необходимость перезагрузить таблицы привилегий.
Режимы кодировки и сравнения. 1. Кодировка и сравнение могут быть установлены для БД, отдельной таблицы, столбца. Правила для My. SQL: а. Если указано CHARACTER SET Х и COLLATE У, то работает Charset x и Collation Y; б. Если указано CHARACTER SET Х без COLLATE, то устанавливается Charset x и Collation по умолчанию, т. е. Latin 1_swedish_ci; в. Если не указано ни CHARACTER SET, ни COLLATE, то устанавливаются значения по умолчанию, т. е. CHARACTER SET Latin 1 COLLATE Latin 1_swedish_ci 33
Режимы кодировки и сравнения. 2. Пример 3. Create table t 1 ( c 1 char(10) character set latin 1 ) Default character set latin 1 collate latin 1_danish_ci; Столбик имеет character set latin 1 и collate по умолчанию. Установки для сравнения столбика берутся из БД, будет установлено сравнение collate latin 1_ swedish _ci. 34
Режимы кодировки и сравнения. 3. Пример 4. Create table t 1 ( c 1 char(10) ) Default character set latin 1 collate latin 1_danish_ci; Здесь для столбика не установлено ни character set ни collate. В этом случае My. SQL просматривает установки, сделанные для таблицы и принимает их для столбика. 35
Режимы кодировки и сравнения. 4. Пример 5. Create database d 1 default character set latin 2 collate latin 2_czech_ci; Use d 1; Create table t 1 ( c 1 char(10) ); Здесь нет установок character set и collate ни для столбика, ни для таблицы. My. SQL просматривает установки, сделанные для базы данных и принимает их для таблицы и столбика. 36
Установка режимов кодировки и сравнения для соединения. 1. Режимы кодировки и сравнения относятся как к серверу, так и к клиенту. Сервер: character_set_server и collation_server БД : character_set_database, collation_ database. Дополнительные переменные кодировки и сравнения участвут в обработке трафика для соединения между клиентом и сервером. 37
Установка режимов кодировки и сравнения для соединения. 2. а. В какой кодировке запрос покидает клиента? Серверная переменная character_set_client б. В какую кодировку сервер переводит запрос после получения? Переменные character_set_connection и collation_ connection. Сервер конвертирует запрос из character_set_client в character_set_connection и collation_ connection. в. В какую кодировку сервер переводит результирующий набор данных и сообщения об ошибках перед тем как отправить его назад клиенту? Переменная character_set_results. 38
Установка режимов кодировки и сравнения для соединения. 3. Set Set Set Names ‘charset_name’; character set ‘charset_name’; Names ‘X’ эквивалентен трем операторам: character_set_client = x; character_set_results = x; character_set_connection = x; эта установка также устанавливает collation_connection = x 39
Установка режимов кодировки и сравнения для соединения. 4. Set character set ‘X’ подобен Set Names, но кодировку соединения устанавливает к той, которая принята по умолчанию для БД: Set character_set_client = x; Set character_set_results = x; Set character_set_connection = @@character_set_database; эта установка также устанавливает collation_connection = @@ collation _database; 40
UTF 8 для метаданных Метаданные – это данные о данных. Операторы: SHOW DATABASES; SHOW [OPEN] TABLES [FROM db_name]; SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name]; SHOW TABLE STATUS [FROM db_name]; SHOW GRANTS FOR user; Метаданные в My. SQL хранятся в Utf 8 формате 41
Скачать презентацию Лекция 17 -2013_2014 Подробнее о типах таблиц
Лекция17_2014_2015.ppt