@ Карта сайта News Автора!

Bog BOS: MySQL: установка, настройка, описание

apache inn MySQL nntpcache Cyrus IMAP exim Squid ssh syslog tacacs ProFTPD wu-ftpd xntpd

Последние изменения:
2024.11.06: sysadmin: настройка TCP/IP в Linux: виртуальный интерфейс и виртуальный мост
2024.10.25: sysadmin: Linux VFS, атрибуты, расширенные атрибуты, ACL
2024.10.22: sysadmin: Монтирование файловых систем: bind, shared и OverlayFS

Последнее изменение файла: 2024.07.02
Скопировано с www.bog.pp.ru: 2024.11.11

Bog BOS: MySQL: установка, настройка, описание

MySQL - это реляционная (SQL) СУБД без излишеств, зато быстрая.

Основные сведения

MySQL - это реляционная (SQL) СУБД, которая поначалу позиционировалась как СУБД для интернет-сайтов, то есть простая и быстрая при извлечении данных. Постепенно появились транзакции (методы хранения Berkley DB и InnoDB (ACID)), внешние ключи и хранимые процедуры. Стандарты: подмножество ANSI/ISO SQL:2003 с расширениями, ODBC levels 0-3.51. Предусматриваются различные режимы совместимости (начиная с версии 4.1 можно изменять динамически).

Лицензия - GPL/LGPL (документация не под GPL!). Но в случае извлечения прибыли от MySQL фирма - MySQL AB, Швеция - мягко намекает на оплату поддержки). Для хостинга лицензия не нужна, но клиенты должны иметь возможность убедиться, что все установлено правильно (предлагается давать доступ на чтение к установленным исходникам?). Можно купить коммерческую лицензию для использования в своих продуктах. Со временем лицензия ужесточается, а стоимость поддержки увеличивается. По состоянию на июнь 2007 имеется 2 версии СУБД: Enterprise Server (от $600 в год) и Community Server (бесплатен, но заплатки с задержкой и с неудобствами). MySQL AB была продана Sun Microsystems, Sun Microsystems был продан Oracle, основатель сделал форк и назвал его MariaDB.

Написана на C и C++. Базовая платформа: Solaris 2.7-2.9 (SPARC), FreeBSD, Linux (SuSE, Red Hat, ReiserFS, ядро 2.4, x86), но работает также в AIX, BSDI, DEC Unix, FreeBSD (4.x, native threads, Linux threads), HP-UX, Linux (glibc 2.0.7+), Mac OS X, NetBSD, Novell NetWare 6.0, OpenBSD, OS/2, SGI Irix, Solaris (2.5, native threads), SunOS (4.x, MIT-pthreads), SCO OpenServer, SCO UnixWare, Tru64, Windows 9x, Windows NT, Netware 6.0.

Состоит из многопотокового сервера (mysqld), клиентской библиотеки (в версии 4.0 появилась возможность встраивать сервер в приложение) и множества утилит. Одна из утилит - mysql - представляет собой клиентское приложение для полного доступа к возможностям сервера (отправляет SQL-запросы и выдаёт ответ на терминал). Для запуска mysqld можно использовать программы mysql.server и mysqld_safe (mysqld_multi для запуска нескольких серверов). Первоначально мимикрировала под mSQL (включая интерфейс утилит и API). Имеются API для C, C++, Java (JDBC), Eiffel, Perl, PHP, Python, Ruby, Tcl. Поддержка ODBC 2.5 с расширениями.

Варианты скомпилированных пакетов - MySQL Standard (MySQL Pro - коммерческая лицензия), MySQL Max (дополнительные, не до конца отлаженные функции, слит в основную ветвь с 5.0.37), MySQL Classic (коммерческая лицензия, без InnoDB). Вариант, скомпилированный с помощью icc, работает на 20% быстрее.

Методы хранения: ISAM (устарел и удален в версии 5.0), MyISAM (по умолчанию; в 3-5 раз быстрее, чем методы с поддержкой транзакций; LOCK TABLE; myisamchk), Berkley DB (BDB, есть поддержка транзакций), InnoDB (есть поддержка транзакций, foreign keys), HEAP (временные таблицы в памяти, записи только фиксированной длины). Таблицы MyISAM в виде B-tree со сжатием индекса (myisampack, только чтение). До 32 (64 в версии 4.1.2) индексов на таблицу. До 64KB на запись (LONGBLOB, LONGTEXT?). До 16 колонок на индекс. Длина индекса до 500 (1000 в версии 4.1.2) байт. Записи переменной длины. Есть примеры использования MySQL с 60000 таблиц и 5 миллиардами строк. Размер таблицы ограничивается максимальным размером файла в файловой системе (при создании таблицы размером более 4 ГБ необходимо специально это указать). MERGE позволяет работать с несколькими однотипными таблицами как с одной.

Поддержка кодировок и правил сравнения koi8-r и cp1251. В версии 4.1 появилась поддержка Unicode (UTF8, UCS2) на уровне БД, таблицы или поля. UTF8 в соответствии с RFC 3629, но число байт в символе не более 3 (RFC 2279 - до 6, RFC 3629 - до 4). Клиент может иметь свою кодировку (кроме UCS2) и правила сортировки. Имеется полнотекстовый поиск слов и фраз с использованием булевых операций и ранжированием результата. Метаданные (например, имена таблиц) хранятся начиная с версии 4.1 в UTF-8.

Клиенты могут соединяться по TCP/IP (можно использовать только, если никто не подслушивает) или Unix socket. В версии 4.0 появилась поддержка SSL (хотя и раньше можно было организовать защищенный канал с помощью OpenSSH) и возможность ограничить использование ресурсов. Парольная защита (пароли шифруются перед пересылке, однако, это не увеличивает безопасность). Система паролей и привилегий носит следы исторического развития, что сильно уменьшает доверие к ней. Реализована репликация БД. Отсутствует memory leak (проверено Purify и Valgrind), если не считать системных библиотек. Схема БД (INFORMATION_SCHEMA) в версии 5.0.

Каждая БД хранится в отдельном каталоге файловой системы в каталоге данных (/usr/local/mysql/data/), таблица занимает один или несколько файлов, кроме таблиц типа InnoDB, которые размещаются в общем табличном пространстве. Имена каталогов соответствуют именам БД. Таблице MyISAM соответствуют 3 файла: имя-таблицы.frm (описание формата таблицы), имя-таблицы.MYD (содержимое таблицы), имя-таблицы.MYI (индексы таблицы). Кстати, число открытых дескрипторов в программе ограниченно (1024). Машинонезависимый формат хранения (хотя формат плавающей точки должен совпадать на этих архитектурах). Таблице MERGE соответствуют 2 файла: имя-таблицы.frm (описание формата таблицы), имя-таблицы.MRG (список имён MyISAM таблиц). Таблице BDB соответствуют 2 файла: имя-таблицы.frm (описание формата таблицы), имя-таблицы.db (содержимое таблицы и индексы). Таблицы типа HEAP хранятся в оперативной памяти. Таблицы InnoDB могут храниться в одном файле или нескольких файлах (обычно в каталоге данных) или разделах диска без использования файловой сиситемы. В каталоге БД для таблиц InnoDB хранятся только описания формата (имя-таблицы.frm) таблиц. Каталог данных содержит также конфигурационный файл (my.cfg), файл с номером процесса (имя-хоста.pid), файл с сообщениями об ошибках (имя-хоста.err), журналы (запись всех SQL-команд: .log, -slow.log, -bin.index, -bin.номер), сертификаты и ключи SSL. Если несколько серверов имеют доступ к одному каталогу данных одновременно (или сервер и утилиты типа myisamchk), то необходимо обеспечить блокировку файлов.

Стабильность подсистем (по состоянию версии 4.1.10): ISAM - стабильная (намечена к удалению), MyISAM - стабильная, C API - стабильная (буфер до 16МБ), mysql(,admin,show,dump,import) - стабильные, Basic SQL - стабильная, оптимизатор - стабильная, блокировка (одновременный доступ нескольких процессов, не клиентов) - gamma (проблемы в некоторых версиях Linux, рекомендуется --skip-locking), нити в Linux - рекомендуется --skip-locking и использовать не более 1000 одновременных соединений, DBD - стабильная, MyODBC - стабильная, репликация - стабильная (4.1), BDB - стабильная (4.1), автоматическое восстановление MyISAM - gamma, массовая вставка в MyISAM - альфа, InnoDB - стабильная (3.23.49), полнотекстовый поиск - стабильная (4.1).

Активный жизненный цикл каждой версии (развитие и исправление всех ошибок) ограничивается 2 годами с момента зрелости версии (General Availability). В течении следующих 3 лет исправляются проблемы с безопасностью и самые гадкие ошибки. Версия 3.23 больше не поддерживается (3.23.32 в январе 2001). Дополнительное время для 4.0 заканчивается 2008-12-31 (4.0.12 в марте 2003), для версии 4.1 - 2009-12-31 (4.1.7 в октябре 2004). Версия 5.0 достигла зрелости 2005-12-13 (5.0.15), разделение на Community Server и Enterprise. Версия 5.1 достигла зрелости 2008-11-14 (5.1.30). Версия 5.5 достигла зрелости 2008-11-14 (5.5.8).

Расширения и несоответствия относительно ANSI SQL (сервер может работать в различных режимах, в т.ч. в режиме совместимости с ANSI):

Отсутствующие возможности ANSI SQL:

Установка и настройка MariaDB 10.3 на Rocky Linux 8.9 из пакетов

В RHEL 8 (Rocky Linux) модуль mariadb имеет потоки 10.3 и 10.5, но некоторые пакеты имеют зависимость от потока по умолчанию, т.е. 10.3.

Установка сервера:

Установка и настройка MariaDB 10.5.9 на CentOS 7.9 вместо родного MariaDB 5.5.68 из пакетов SCL

В CentOS 7 поставляется довольно старая версия MariaDB 5.5, но имеется возможность почти безболезненно поставить MariaDB 10.5 из SCL.

Предварительно подключить репозитории SCL, если не было сделано ранее: "yum install centos-release-scl"; отредактировать /etc/yum.repos.d/CentOS-SCLo-scl[-rh].repo.

Обновление через удаление:

Установка и настройка MariaDB 5.5.41 (5.5.60, 5.5.68) на CentOS 7.1 (CentOS 7.5, 7.9) из пакетов (x86_64)

В CentOS 7.1 вместо MySQL поставляется MariaDB 5.5.Y, который разбит на следующие пакеты: mariadb (клиент и общие программы), mariadb-libs (/etc/my.cnf, /etc/my.cnf.d/, /usr/lib64/mysql/libmysqlclient*, /usr/share/mysql/charsets), mariadb-bench (каталог /usr/share/sql-bench), mariadb-test (каталог /usr/share/mysql-test), mariadb-server (/etc/my.cnf.d/server.cnf, /etc/logrotate.d/mariadb, /usr/bin/innochecksum, /usr/bin/myisam*, /usr/bin/mysql*, /usr/bin/perror, /usr/bin/replace, /usr/bin/resolve_stack_dump, /usr/bin/resolveip, /usr/lib/systemd/system/mariadb.service, /usr/lib64/mysql/ (здесь есть auth_pam, auth_socket, ha_innodb, ha_sphinx, server_audit)), mariadb-devel (/usr/bin/mysql_config, /usr/include/mysql/, /usr/lib64/mysql/libmysqlclient.so), mariadb-embedded, mariadb-embedded-devel, mysql++, mysql++-devel, mysql++-manuals, mysql-proxy, mysql-proxy-devel, mysql-utilities, mysqlreport, mysqltuner, mysql-connector-odbc-5.2.5, MySQL-python, perl-DBD-MySQL. А где клиенты для старых версий?

Сервер:

memlock срабатывает только под root. Требуется доработка (а может молотком "/usr/sbin/setcap 'cap_ipc_lock=+ep' /usr/libexec/mysqld"?):

Установка и настройка MySQL 5.1.61 на CentOS 6.2 из пакетов (x86_64)

В CentOS 6.X MySQL 5.1.Y разбит на следующие пакеты: mysql (клиент и общие программы), mysql-libs (/etc/my.cnf, /usr/lib64/mysql/libmysqlclient*, /usr/share/mysql/charsets), mysql-bench (каталог /usr/share/sql-bench), mysql-test (каталог /usr/share/mysql-test), mysql-server, mysql-devel (/usr/include/mysql/, /usr/lib64/mysql/*.a), mysql++, mysql++-devel, mysql-embedded, mysql-embedded-devel, mysql-mmm, mysql-mmm-agent, mysql-mmm-monitor, mysql-mmm-tools, mysql-proxy, mysql-proxy-devel, mysql-utilities, mysqlreport, mysqltuner, mysql-connector-odbc-3.51 (/usr/lib64/libmyodbc3), MySQL-python, perl-DBD-MySQL. А где клиенты для старых версий?

Сервер:

Установка и настройка MySQL 5.0.22/5.0.45 на CentOS 5.0/5.1/5.2/5.3 из пакетов (x86_64)

В CentOS 5.X MySQL 5.0.Y разбит на следующие пакеты: mysql (клиент и общие программы), mysql-bench (каталог /usr/share/sql-bench), mysql-test (каталог /usr/share/mysql-test), mysql-server, mysql-devel (/usr/include/mysql/, /usr/lib64/mysql/*.a), mysql-connector-odbc-3.51 (/usr/lib64/libmyodbc3), perl-DBD-MySQL. А где клиенты для старых версий?

Сервер:

Установка и настройка MySQL 4.1.22 на RHEL 4.7 из пакетов

В RHEL 4.7 MySQL 4.1.22 разбит на следующие пакеты: mysql (клиент и общие программы), mysql-bench (каталог /usr/share/sql-bench), mysql-sersver (утилиты собраны статически; сервер с ключами --with-readline, --with-vio, --with-openssl, --without-debug, --enable-shared, --localstatedir=/var/lib/mysql (данные), --with-unix-socket-path=/var/lib/mysql/mysql.sock, --with-mysqld-user="mysql", --with-extra-charsets, --with-innodb, --with-isam, --with-berkeley-db, --enable-local-infile, --enable-large-files=yes, --enable-largefile=yes, --enable-thread-safe-client), mysql-devel (/usr/include/mysql/, /usr/lib/mysql/*.a), mysqlclient10 (3.23.58), perl-DBD-MySQL.

Сервер:

Установка и настройка MySQL 4.1.20 на CentOS 4.4 из пакетов

В CentOS 4.4 MySQL 4.1.20 разбит на следующие пакеты: mysql (клиент и общие программы), mysql-bench (каталог /usr/share/sql-bench), mysql-sersver (утилиты собраны статически; сервер с ключами --with-readline, --with-vio, --with-openssl, --without-debug, --enable-shared, --localstatedir=/var/lib/mysql (данные), --with-unix-socket-path=/var/lib/mysql/mysql.sock, --with-mysqld-user="mysql", --with-extra-charsets, --with-innodb, --with-isam, --with-berkeley-db, --enable-local-infile, --enable-large-files=yes, --enable-largefile=yes, --enable-thread-safe-client), mysql-devel (/usr/include/mysql/, /usr/lib/mysql/*.a), mysqlclient10 (3.23.58), perl-DBD-MySQL.

Сервер:

Установка и настройка MySQL 4.1.10a (4.1.13) на CentOS 3.4 (RHEL3, RH9 и прочие с ядром 2.4) и перенос на CentOS 4 из исходных текстов; MySQL 4.1.16/4.1.18 на CentOS 4.2

Сервер

Собранный сервер продолжил работу после перевода компьютера на CentOS 4.0 после установки пакета совместимости (compat-libstdc++).

Клиент (собирался в RH9 и FC3):

Опции ./configure

mysql.server (запуск MySQL при загрузке компьютера)

Скрипт предназначен для /etc/rc.d/init.d (и линки в 0,2,3,5,6 не забыть). Соответственно первый параметр start или stop (посылается сигнал процессу, вместо выполнения "mysqladmin shutdown"). Пытается определить (весьма запутанно) базовый каталог и каталог с данными с помощью my[sql]_print_defaults (предварительно пытаясь её найти хоть где-нибудь). Далее уточняет базовый каталог, каталог с данными, каталог с программами из глобального конфигурационного файла /etc/my.cnf (секции [mysqld], [server], [mysql.server] и [mysql_server]). Определяет местонахождение конфигурационного файла сервера (/usr/local/mysql/data/my.cnf). Ищет и запускает mysqld_safe (safe_mysqld) в базовом каталоге. Если нужны какие-либо специфические опции mysqld_safe, то их можно указать здесь. В конфигурационном файле /etc/my.cnf могут содержаться переменные:

Установка для систем Red Hat (с SystemV init) выглядит так:

mysqld_safe (safe_mysqld)

Обычно запускается скриптом mysql.server. mysqld_safe запускает mysqld и перезапускает его, если тот упадёт. Проверяет, что не был запущен ранее. Останавливает зависшие процессы. Запускать под root. Пытается определить где что лежит (понимает только стандартные конфигурации, иначе его надо запускать из BASEDIR). Собирает опции из секций mysqld, server и mysqld_safe (safe_mysqld) конфигурацинных файлов и командной строки (м.б. модифицированных опциями --no-defaults (не использовать конфигурационный файл сервера), --defaults-file=имя-файл (использовать указанный файл вместо конфигурационного файла сервера) и --defaults-extra-file=имя-файла (использовать указанный файл в дополнение к конфигурационному файлу сервера)). Передает их ("улучшив" некоторые из них) mysqld. Свои опции:

Есть закоментированные проверки всех таблиц перед запуском mysqld. Пользователь по умолчанию - mysql. Пытается запустить mysqld под "[nohup] [nice]".

Разбор параметров и конфигурационных файлов

Опции берутся в следующем порядке (последующие затирают предыдущие):

Действуют на mysql, mysqld, mysqladmin, mysqlimport, mysqldump, myisamchk, myisampack, mysql.server. Файл делится на секции (группы). Секция начинается со строки, в которой указано имя секции в квадратных скобках. Имя секции соответствует имени программы на которую она действует. Комментарии начинаются с символов '#' или ';'. Любая длинная опция программы (начинающаяся с двух минусов, запустите программу с ключом --help) может быть использована здесь (минусы надо отбросить). Пробелы вокруг первого символа '=' и в конце строки выжимаются, можно использовать апострофы и кавычки обычным образом, а также \n, \t, \s, \\, \r, \b. Секция с именем client позволяет задать опции, общие для всех клиентских программ (host, user, password). В версии 4.0.12 появилась возможность создавать секции с именем вида mysqld-4.0. Переменные устанавливаются строкой:

set-variable = имя=значение

Начиная с версии 4.0.2 можно просто писать: имя-переменной=значение, в т.ч. и в командной строке; вместо подчёркиваний в именах можно использовать тире. Переменные бывают глобальные и сессионные. Значение глобальных переменных устанавливается изначально по умолчанию и могут быть измененны опциями командной строки или конфигурационного файла (можно использовать суффиксы K, M и G). Некоторые глобальные переменные можно изменить оператором SET GLOBAL (необходимо иметь привилегию SUPER). Максимальное значение переменной можно ограничить при запуске опцией --maximum-имяпеременной. Сессионные переменные устанавливаются при подсоединении клиента равными значению соответствующей глобальной переменной. Некоторые сессионные переменные можно изменять оператором SET SESSION. Посмотреть текущее значение переменной (сессионной?) можно с помощью оператора SHOW VARIABLES. Полный список переменных приведён в главе 5.2.3. Имеются также переменные состояния, которые можно только смотреть оператором SHOW STATUS.

Опцию, имеющую булевское значение можно устанавливать различными методами (4.0.2):

Если имя опции предварить строкой --loose- (4.0.2), то программа выдаёт предупреждение, если не распознает опцию вместо аварийного завершения (полезно для написания скриптов, совместимых с различными версиями).

Проверить "собранные" из всех мест опции можно с помощью ключа --print-defaults или утилиты my_print_defaults (имена секций указываются в качестве параметоров).

mysqld (сервер БД)

Вместо ключей запуска можно использовать конфигурационные файлы (секции mysqld и server, для встроенного сервера - server, embedded и имя-приложения_SERVER).

Опции:

Завершить работу сервера можно послав процессу сигнал SIGTERM или запустив утилиту "mysqladmin shutdown" (необходимы привилегии SHUTDOWN).

Сервер может работать в различных режимах для совместимости со стандартом ANSI или другими серверами (--ansi, --sql-mode). Начиная с 4.1 режимы могут быть различными для каждого клиента. Основные режимы синтаксиса и реакции на ошибки (режимы 5.0 опущены, так как их там очень много ;):

Составные режимы:

Права доступа

Аутентификация пользователя производится по имени (до 16 символов), паролю (м.б. пустым) и хосту или его IP. Большинство клиентских программ по умолчанию используют mysql-имя, совпадающее с unix-именем, но это можно изменить с помощью ключа --user=. Пароль можно задать:

Вся информация о правах хранится в БД с именем mysql. Никто не должен иметь к ней доступа на чтение (см. про пароли).

Используются таблицы:

Проверка права на подсоединение к серверу (аутентификация): mysql-клиент предъявляет имя пользователя, сервер определяет имя (или IP) хоста клиента (или localhost для обращения через unix-socket). По данной паре (адрес/имя) ищется строка в таблице user. Предварительно таблица сортируется по полям (сначала по Host, потом по User) так, что наиболее специфичные строки оказываются первыми, наименее специфичные - последними (какая строка с шаблоном более специфичная - не сказано). Если строка не найдена, то соединение отвергается. Если - найдена, то сверяется пароль.

Проверка прав при исполнении каждого запроса (авторизация): таблица db сортируется по полям Host, Db и User, таблица host по полям Host и Db, таблицы tables_priv и columns_priv по полям Host, Db и User от наиболее специфичного к наименее. Для административных запросов и доступа к файлам проверяется только таблица user. Для прочих запросов в начале проверяется таблица user - а нет ли у данного пользователя прав доступа на "глобальном" уровне. Если есть - операция разрешается. Если нет, то проверяются права доступа к конкретной БД с конкретного хоста (по пересечению таблиц Db и Host с учетом шаблонов и пустых полей). Если их достаточно, то доступ дается. Если недостаточно, то к объединению "глобальных" прав и прав БД/хост добавляются права, извлеченные из таблиц tables_priv и columns_priv. Если и этого не хватает, то увы...

Права доступа читаются mysqld (и не читаются при "ручном" изменении БД mysql):

"Глобальные" привилегии и изменения пароля вступают в силу только при следующем соединении. Изменения в доступе к БД - при следующей команде use. Изменения в доступе к таблицам и колонкам - при следующем запросе.

Все команды, введенные в клиенте mysql (а в других?) попадают в .mysql_history (того unix-пользователя, который запускал mysql). А также в различные журналы сервера и в выдачу команды SHOW PROCESSLIST.

Изменение пароля: SET PASSWORD [FOR имя[@имя-хоста]] = PASSWORD('новый пароль') или mysqladmin password.

Создание пользователя или добавление привилегий: GRANT тип-привилегии [(список-столбцов)] [, тип-привилегии [(список-столбцов)] ...] ON { имя-таблицы | * | *.* | имя-БД.* } TO имя-пользователя[@имя-хоста] [ IDENTIFIED BY 'пароль' ] [, имя-пользователя[@имя-хоста] [ IDENTIFIED BY 'пароль' ] ... ] [REQUIRE SSL-параметры] [WITH {GRANT OPTION | MAX_QUERIES_PER_HOUR число | MAX_UPDATES_PER_HOUR число | MAX_CONNECTION_PER_HOUR число}]

Пароль задаются непосредственно или в хешированном виде (необходимо добавить ключевое слово PASSWORD после BY).

Формат параметров SSL соединения:

Создание пользователя без привилегий (5.0.3): CREATE USER имя-пользователя[@имя-хоста] [ IDENTIFIED BY 'пароль' ], ...

Изъятие привилегий: REVOKE тип-привилегии [(список-столбцов)] [, тип-привилегии [(список-столбцов)] ...] ON { имя-таблицы | * | *.* | имя-БД.* } FROM имя-пользователя[@имя-хоста] [, имя-пользователя[@имя-хоста] ... ]

Отнять все привилегии: REVOKE ALL PRIVILEGES, GRANT OPTION FROM имя-пользователя[@имя-хоста] [, имя-пользователя[@имя-хоста] ...

Удаление пользователя (до 5.0.1 удаляет только запись в таблице user): DROP USER имя-пользователя[@имя-хоста][, ...]

Удаление пользователя вместе с привилегиями:

SHOW GRANTS FOR ...;
REVOKE ...;
DELETE FROM mysql.user WHERE User='...' AND Host='...';
FLUSH PRIVILEGES;

Переименование пользователя (5.0.2): RENAME USER старое-имя TO новое-имя [, ...]

Типы привилегий:

Для столбцов можно задавать только INSERT, SELECT и UPDATE. Для таблиц - INSERT, SELECT, UPDATE, CREATE, DROP, DELETE, GRANT, INDEX, ALTER. Использование "*.*" означает задание глобальных привилегий. Использование "*" означает задание привилегий для текущей БД (если текущей БД нет, то глобальные).

SHOW GRANTS FOR имя@хост;

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

GRANT и REVOKE не работают с таблицей host (она вообще не используется в большинстве установок).

Запутанность системы и хеширование пароля на стороне клиента производят тяжёлое впечатление.

Для упрощения жизни можно использовать утилиты mysqladmin и mysqlaccess. Узнать имя, под которым система вас аутентифицировала можно с помощью функции CURRENT_USER().

Не храните пароли и тому подобную информацию в БД в открытом виде.

Использование SSL

Предварительно требуется ознакомиться с OpenSSL.

Опции командной строки и конфигурационного файла

Для примера опишу как использовать SSL соединения в ситуации с собственным CA. Кладём самоподписанный корневой сертификат (см. как его делать) в файл ca.crt в директории данных. Создаём незашифрованный ключ сервера и кладём его в файл mysql.key (обязательно права доступа - mysql:mysql, 400!). Создаём запрос на подпись сертификата сервера и посылаем его на подпись бюро сертификации (а скорее всего сами себе ;). Подписанный сертификат кладём в файл mysql.crt. Теперь в секцию mysqld конфигурационного файла дописываем следующие строчки и перезапускаем сервер:

ssl-ca=/usr/local/mysql/data/ca.crt
ssl-cert=/usr/local/mysql/data/mysql.crt
ssl-key=/usr/local/mysql/data/mysql.key

Создаём клиента

GRANT ALL ON test.* TO test IDENTIFIED BY 'test' REQUIRE ISSUER '...' AND SUBJECT '...';

На клиентской машине кладём в домашний каталог файл ca.crt, Создаём ключ клиента и кладём его в файл test.key (права доступа - test:test, 400). Создаём запрос на подпись сертификата клиента и посылаем его на подпись бюро сертификации (а скорее всего сами себе ;). Подписанный сертификат кладём в файл test.crt. Теперь в файл .my.cnf дописываем в секцию client следующие строчки:

ssl-ca=/home/test/ca.crt
ssl-cert=/home/test/test.crt
ssl-key=/home/test/test.key

Не забываем про сетевой экран.

После соединения по TCP/IP (клиент mysql спрашивает пароль пользователя MySQL и парольную фразу приватного ключа SSL) проверяем заработал ли SSL:

mysql> \s
...
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

Шифруются даже соединения через сокеты.

Локализация и интернационализация

Сервер и клиентские программы могут работать с данными в различных кодировках и с различными правилами сравнения/сортировки (MyISAM, HEAP/MEMORY и InnoDB). Кодировка по умолчанию задаётся при сборке (при запуске ./configure) ключом --with-charset= (по умолчанию - latin1; binary, cp1251, koi8r, ucs2, utf8, ...). Правила сортировки по умолчанию задаются при сборке ключом --with-collation= (по умолчанию - latin1_swedish_ci; ascii_general_ci, latin1_bin, latin1_general_ci, latin1_general_cs, koi8r_general_ci, koi8r_bin, cp1251_general_ci, cp1251_bin, cp1251_general_cs, utf8_bin, utf8_general_ci (быстрее), utf8_unicode_ci (соответствует стандарту), ucs2_general_ci, ucs2_bin, uc2_unicode_ci, ...; где _ci - нечувствительный к регистру, _cs - чувствительный к регистру, _bin - побайтное сравнение). Полный список допустимых кодировок задаётся при сборке ключом --with-extra-charsets= (полезные опции: all, none, complex (многобайтовые кодировки)). Однобайтовые кодировки могут добавляться при запуске добавлением файла с описанием кодировки в специальный каталог. (ключ сервера/клиента: --character-sets-dir=). Запросы SHOW CHARACTER SET и SHOW COLLATION выдают полный список допустимых кодировок и правил сортировки.

При запуске сервера можно изменить кодировку и правила сортировки по умолчанию ключами --character-set-server= (--default-character-set=) и --collation-server= (--default-collation). Текущую кодировку сервера и правила сортировки можно узнать или изменить с помощью переменных character_set_server и collation_server. Каждая БД имеет кодировку и правила сортировки, которые задаются при создании или изменении БД (по умолчанию наследуются текущие характеристики сервера), файл db.opt в каталоге БД. Кодировка и правила сортировки для текущей БД (USE) хранятся в переменных character_set_database и collation_database. Каждая таблица имеет кодировку и правила сортировки, которые задаются при создании или изменении таблицы (по умолчанию наследуются характеристики БД). Каждая текстовая (CHAR, VARCHAR, TEXT) колонка имеет кодировку и правила сортировки, которые задаются при создании или изменении таблицы (по умолчанию наследуются характеристики таблицы).

Кодировка и правила сортировки по умолчанию со стороны клиента задаются ключами --character-sets-dir= и --default-character-set=. Текущая кодировка запросов клиента определяется переменной character_set_client. Получив запрос, сервер преобразует его в кодировку character_set_connection. При сравнениях с константами используются правила collation_connection (установить collation_connection опцией не удалось, срабатывает только default-character-set, но он устанавливает сортировку по умолчанию). Результат преобразуется в кодировку character_set_result перед отправкой (специальная кодировка NULL для отмены перекодировки). Итого: запрос от клиента преобразуется из character_set_client в character_set_connection, извлечённые значения преобразуются из своих кодировок в character_set_connection, вся обработка идёт в кодировке character_set_connection с правилами сравнения collation_connection, результат преобразуется из character_set_connection в character_set_result. Запрос "SET NAMES 'кодировка'" сразу устанавливает все переменные (неявно выполняется при соединении). Запрос "SET CHARACTER SET кодировка" устанавливает переменные character_set_client и character_set_result, а переменные character_set_connection и collation_connection устанавливает в соответствии с характеристиками текущей БД. Клиент не может использовать ucs2.

Каждая текстовая строка имеет кодировку и правила сортировки ( [_кодировка] 'строка' [COLLATE имя-правила-сортировки]). По умолчанию наследуются из character_set_connection и collation_connection. Какое правило сортировки будет использовано в сложных выражениях (содержащих элементы с разными правилами) - дело тонкое (и будет изменено в 4.1.11). Следует учитывать, что метаданные (БД mysql) хранятся в utf8 (переменная character_set_system), строковые константы по умолчанию имеют тип latin1 и перемешивать всё это надо осторожно. Результат функций работы со строками с одним параметром наследует кодировку и правила сортировки от аргумента. Имеются некоторые возможности автоматического преобразования, но ещё недоделанные (читай: правила игры будут ещё меняться).

Язык сообщений об ошибках задаётся при запуске сервера ключом --language=russian.

От правил сортировки зависит построение индексов, так что если кодировка или правила сортировки для составляющих индекса изменились, то индекс необходимо перестроить (например, myisamchk -r -q --set-character-set=).

Если в БД занесены тексты в UTF-8, а таблицы размечены в latin1:

mysqldump --skip-set-charset --no-data --default-character-set=latin1 имя-БД > /tmp/имя-БД.sql.dump
vim /tmp/имя-БД.sql.dump # %subst/latin1/utf8/
mysqldump --skip-set-charset --no-create-info --default-character-set=latin1 имя-БД > /tmp/имя-БД.data.dump
drop database имя-БД
create database имя-БД CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql default-character-set=utf8  < /tmp/имя-БД.sql.dump
mysql default-character-set=utf8  < /tmp/имя-БД.data.dump

Базовый синтаксис

Константы

Имена (идентификаторы)

Если в имени есть спецсимволы (не буква, не цифра, не '_', не '$') или оно совпадает с зарегистрированным словом (глава 9.6), то его (простое имя) надо заключать в обратные апострофы (или кавычки в режиме ANSI). Нельзя использовать в именах байты 0x00 или 0xFF (привет маленькому "я" в cp1251!), апостроф (до 4.1) и кавычку (до 4.1). Имена не должны оканчиваться пробелом. Имена хранятся в БД (.frm и таблицы привилегий) в utf8. Имена БД и таблиц чувствительны к регистру в Unix и нечувствительны в MS Windows. Имена колонок нечувствительны везде. Алиасы на таблицы чувствительны везде, алиасы на колонки нечувствительны везде. Имеется также системная переменная lower_case_table_names, однако разработчики рекомендуют везде использовать строчные буквы для идентификаторов и не создавать себе проблем.

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

Имя переменной начинается с '@', может содержать буквы, цифры, '_', '$', '.'. Чувствительность к регистру меняется от версии к версии, так что рекомендуется везде использовать строчные латинские буквы. Первоначальное значение - NULL тиа строка. Может содержать целое (64 бита), вещественное или строку (кодировка и тип сортировки наследуются от выражения). Можно использовать в тех местах, где разрешается использование выражений (не числовых констант как во фразе LIMIT!). Действует для текущего соединения. Не рекомендуется устанавливать и использовать переменную в одном и том же запросе (например, переменая в SELECT вычисляется в момент посылки результата, этот эффект нелегко учесть для новичка). Установка:

Системные переменные

Системные переменные бывают глобальные и сессионные (список в главе 5.2.3). При запуске сервера значения глобальных переменных устанавливаются по умолчанию (можно изменить заданием опций). Некоторые глобальные переменные можно изменять запросом SET GLOBAL (требуются привилегии SUPER):

Посмотреть значения глобальных переменных можно запросом SHOW GLOBAL VARIABLES [LIKE 'шаблон'] или SELECT @@global.имя-переменной.

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

Посмотреть значения переменых можно запросом SHOW [SESSION | LOCAL] VARIABLES [LIKE 'шаблон'] или SELECT @@[session.]имя-переменной.

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

Комментарии

Типы данных (в колонках)

M - ширина поля при отображении (максимально - 255). D - число знаков в дробной части (не более M-2 и 30). p - точность представления.

Операторы SQL

Функции для SELECT и WHERE

Между именем функции и скобкой не должно быть пробела. Операндами могут быть константы, имена колонок и переменные.

Интерактивный текстовый клиент mysql

Программа mysql позволяет подсоединиться с серверу MySQL и выполнить последовательность SQL-запросов с получением ответа на экран терминала (эмулятора терминала). Имеется редактирование командной строки с автодополнением имён (readline) с поддержкой истории (~/.mysql_history, переменая окружения MYSQL_HISTFILE). Вместо терминала может принимать SQL запросы со стандартного ввода. Вывод может быть переназначен в файл. Параметры задаются ключами, переменными окружения и в конфигурационном файле стандартным способом. Секции конфигурационного файла: mysql и client. Кроме глобального (/etc/my.cnf) и серверного (/usr/local/mysql/data/my.cnf) конфигурационных файлов можно использовать личный файл $HOME/.my.cnf

Основные ключи (они же опции конфигурационного файла):

После соединения вводимые операторы SQL (должны завершаться разделителем, обычно ';') передаются серверу, результаты выводятся на экран (возможно, с использованием more или less).

Дополнительные команды имеют длинную (не требуется завершать разделителем) и короткую формы (нельзя завершать разделителем):

При задании строки приглашения можно использовать спецификации формата (при задании приглашения в конфигурационном файле или переменной окружения необходимо учитывать, что там действуют свои правила использования '\'):

Журналы

Журнал ошибок. Содержит сообщения о запуске и остановке сервера, а также о грубых ошибках. Лежит в каталоге данных под именем `hostname`.err. Имя можно задать ключом --log-error=имя-файла. По запросу FLUSH LOGS старый файл переименовывается (добавляется суффикс -old) и создаётся новый.

Журнал запросов. Содержит описания всех клиентских соединений и тексты всех SQL запросов (в порядке поступления, а не исполнения). Запись в него включается ключом --log[=имя-файла]. По умолчанию, файл лежит в каталоге данных под именем `hostname`.log. По запросу FLUSH LOGS файл переоткрывается (если предварительно переименовать файл, то откроется новый файл со старым именем).

Журнал изменений (удалён в 5.0.0). Содержит тексты всех SQL запросов, изменяющих данные (после выполнения запроса, но до снятия блокировок). Запись в него включается ключом --log-update[=имя-файла]. По умолчанию, файл лежит в каталоге данных под именем `hostname`.номер (номер увеличивается при каждом запуске или сбросе журналов).

Двоичный журнал изменений. Содержит информацию о всех изменениях данных в двоичном формате, совместим с транзакциями, запись делается после выполнения запроса (для таблиц с транзакциями - после COMMIT, если объём изменений превышает binlog_cache_size, то создаётся временный файл; max_binlog_cache_size ограничивает максимальный размер транзакции, которую можно записать в журнал; статистика в переменных Binlog_cache_use и Binlog_cache_disk_use), но до снятия блокировок. Запись в него включается ключём --log-bin[=имя-файла]. По умолчанию, файл лежит в каталоге данных под именем `hostname`-bin.номер (номер увеличивается при каждом запуске или сбросе журналов или достижении размера max_binlog_size). Можно удалить все или некоторые из них запросом RESET MASTER или PURGE MASTER LOGS. Также хранится таблица журналов в файле `hostname`-bin.index (меняется ключом --log-bin-index=имя-файла). Какие записи включать можно ограничить ключами --binlog-do-db=имя-БД (проверяется не реальная изменяемая БД, а текущая, установленная с помощью USE) и --binlog-ignore-db=имя-БД (также учитывается лишь имя текущей БД). Глобальная переменная sync_binlog позволяет синхронизовать запись в БД и двоичный журнал с указанным шагом. Чтобы откатка транзакций выполнялась после аварийного рестарта одинаково для двоичного журнала и таблиц InnoDB необходимо дополнительно указывать ключи --innodb-safe-binlog и обеспечить сброс буферов на диск после каждой транзакции (авторы явно не уверены, что даже это поможет во всех случаях). Утилита mysqlbinlog преобразует двоичный журнал в запросы SQL в текстовом виде. Замедляет работу сервера на 1%. При использовании двоичного журнала параллельные вставки исполняются как обычные. Формат в версиях 3.x, 4.x и 5.x отличается.

Журнал медленных запросов. В него записываются все SQL запросы, выполнение которых заняло больше, чем long_query_time секунд. Запись в него включается ключом --log-slow-queries[=имя-файла]. Ключ --log-queries-not-using-indexes добавляет в него все запросы, не использующие индексы. По умолчанию, файл лежит в каталоге данных под именем `hostname`-slow.log. По запросу FLUSH LOGS файл переоткрывается (если предварительно переименовать файл, то откроется новый файл со старым именем). Скрипт mysqldumpslow (perl) облегчает разбор получившегося журнала.

Файл mysql-log-rotate содержит пример конфигурации для logrotate.

Утилиты администрирования

Все утилиты имеют ключ --help, обработка опций и параметров осуществляется согласно общим правилам.

mysqladmin - выполнение административных задач

В качестве параметров задаются опции и команды. Опции:

Команды (имя можно сокращать до минимально различимого):


mysqldump - сохранение БД

Утилита позволяет вывести содержимое таблиц или БД в виде SQL запросов для переноса на другой сервер или резервирования. При вызове указываются имя БД и имена таблиц (или имена БД в опции --databases или опция --all-databases), а также опции в стандартном формате:


mysqlhotcopy - копирование таблиц MyISAM

Скрипт (perl) mysqlhotcopy позволяет копировать таблицы MyISAM командами cp и scp (т.е. очень быстро) без остановки сервера (используются запросы LOCK TABLES и FLUSH TABLES). Опции читаются из секций client и mysqlhotcopy конфигурационного файла. Необходимы права для чтения файлов БД, привилегию SELECT для таблиц и привилегию RELOAD. В качестве параметров указывается перечень БД и имя каталога для сохранения. После имени БД можно указать точку и регулярное выражение в слешах, ограничивающее имена копируемых таблиц. Опции:


mysqlimport - загрузка текстовых файлов в таблицы

Утилита mysqlimport предоставляет интерфейс для запроса LOAD DATA INFILE. В качестве параметров указывается имя БД и список файлов. Имя таблицы определяется по имени файла (суффикс обрезается). Опции соответствуют возможностям запроса LOAD DATA INFILE (см. описание):


mysqlshow - выдача информации о БД и таблицах

Утилита mysqlshow предоставляет интерфейс для запроса SHOW. В качестве параметров указывается имя БД, имя таблицы, имя колонки (все параметры можно опустить - будет выдан список БД). Можно указывать шаблоны (% и _). Опции:


mysqlaccess - создание пользователей и управление привилегиями

Скрипт (perl) mysqlaccess позволяет проверить наличие прав доступа для набора хост-пользователь-БД, что упрощало работу с привилегиями в старых версиях. В новых версиях MySQL GRANT/REVOKE сильно облегчил жизнь и скрипт выглядит заброшенным.


myisamchk - проверка и исправление таблиц

Перед запуском рекомендуется скопировать файлы. Возможно стоит вместо myisamchk использвать SQL запросы CHECK, REPAIR, OPTIMIZE, ANALYZE напрямую или через утилиту mysqlcheck, иначе придётся останавливать сервер или обеспечивать взаимную блокировку (и FLUSH TABLES или mysqladmin flush-tables), а ведь вы запускаете mysqld с ключом --skip-external-locking. При вызове указываются имена файлов, содержащих таблицы (.MYI), и опции в стандартном формате:


mysqlcheck - проверка и исправление таблиц

Утилита позволяет проверять, исправлять и оптимизировать таблицы MyISAM без остановки сервера (представляет собой интерфейс к SQL запросам CHECK, REPAIR, OPTIMIZE, ANALYZE). При вызове указываются имя БД и имена таблиц (или имена БД в опции --databases или опция --all-databases), а также опции в стандартном формате:


myisampack - сжатие таблиц

Утилита позволяет сжимать таблицы MyISAM (обычно вдвое). После сжатия таблицы можно использовать только для чтения. Необходимо останавливать сервер или обеспечивать взаимную блокировку (и FLUSH TABLES или mysqladmin flush-tables), а ведь вы запускаете mysqld с ключом --skip-external-locking. Расспаковка осуществляется утилитой myisamchk. После сжатия таблицы необходимо воссоздать индексы, например:

myisamchk -rq --sort-index --analyze имя-таблицы.MYI
mysqladmin flush-tables
При вызове указываются имена файлов, содержащих таблицы (.MYI), и опции в стандартном формате:

mysqlbinlog - обработка двоичных журналов

Утилита преобразует указанные двоичные журналы в последовательность SQL запросов (в виде комментариев записываются также времена, номера нитей и т.д.). Двоичный журнал может быть получен непосредственно от [удалённого] сервера. Опции:


isamchk

isamlog


perror - толкование сообщений об ошибках

Утилита perror по номеру ошибки выдаёт текст сообщения.

phpMyAdmin

phpMyAdmin позволяет администрировать БД MySQL (создавать, удалять и редактировать поля, таблицы и базы) с помощью веб-интерфейса используя Apache и PHP (4.3.0/5.2.5 или новее, желательно наличие GD2 и zlib). Установка phpMyAdmin 2.6.3-pl1 (2.10.0.2-all-languages, 2.11.4-all-languages, 2.11.5.1-all-languages-utf-8-only) на Apache2 с авторизацией по SSL:

Резервирование данных

Так как каждая таблица представляет собой отдельный файл, то резервирование данных легко осуществляется обычным копированием файлов. Однако предварительно необходимо либо остановить сервер, либо заблокировать запись в таблицу (LOCK TABLES) и сбросить индексы (FLUSH TABLES) перед копированием (и не забыть разблокировать таблицу (UNLOCK TABLES) после копирования).

Резервирование на уровне SQL запросов можно сделать с помощью SELECT INTO или BACKUP TABLE.

Полное копирование каждой базы можно осуществить с помощью утилиты mysqldump (получается файл в формате команд SQL, которые можно пропустить через mysql непосредственно или предварительно обработав) или perl-скрипта mysqlhotcopy (только MyISAM).

Обновления относительно последнего mysqldump можно получить запустив сервер с записью двоичного журнала (--log-bin; FLUSH LOGS начинает новый журнал в серии).

Ещё есть возможность оперативного дублирования БД (репликация) на вспомогательный сервер (slave).

Ссылки

@ Карта сайта News Автора!

Bog BOS: MySQL: установка, настройка, описание

apache inn MySQL nntpcache Cyrus IMAP exim Squid ssh syslog tacacs ProFTPD wu-ftpd xntpd

Последние изменения:
2024.11.06: sysadmin: настройка TCP/IP в Linux: виртуальный интерфейс и виртуальный мост
2024.10.25: sysadmin: Linux VFS, атрибуты, расширенные атрибуты, ACL
2024.10.22: sysadmin: Монтирование файловых систем: bind, shared и OverlayFS



Copyright © 1996-2024 Sergey E. Bogomolov; www.bog.pp.ru