Столбцы

В табл. 6.1 указаны размерности стандартных типов данных MySQL. Значения некоторых типов всегда занимают фиксированный объем памяти. Например, размерность столбцов типа INTEGER всегда составляет 4 байта. Столбцы типа CHAR могут иметь размерность от 0 до 255, но в момент создания таблицы под них отводится фиксированный объем памяти. Существуют также столбцы переменной размерности. Например, столбцы типа VARCHAR и BLOBинтерпретируются в соответствии с их содержимым.

Таблица 6.1. Размерности стандартных типов данных.
Тип	Размерность
BIGINT	8 байтов
BLOB, TEXT	длина содержимого + 2 байта
CHAR	указанное число байтов
DATE	3 байта
DATETIME	8 байтов
DECIMAL (длина, точность)	длина + 1 байт, если точность равна 0; в противном случае — длина + 2 байта
DOUBLE	8 байтов
DOUBLE PRECISION	8 байтов
ENUM	1 байт, если в перечислении менее 255 элементов; в противном случае — 2 байта
FLOAT	4 байта
FLOAT (длина)	4 байта, если длина <= 24; в противном случае — 8 байтов
INT	4 байта
INTEGER	4 байта
LONGBLOB, LONGTEXT	длина + 2 байта
MEDIUMBLOB, MEDIUMTEXT	длина + 2 байта
MEDIUMINT	3 байта
NUMERIC (длина, точность)	длина + 1 байт, если точность равна 0; в противном случае – длина + 2 байта
REAL	8 байтов
SET	1, 2, 3, 4 или 8 байтов, в зависимости от количества элементов множества
SMALLINT	2 байта
TIME	3 байта
TIMESTAMP	4 байта
TINYBLOB, TINYTEXT	длина + 2 байта
TINYINT	1 байт
VARCHAR (длина)	длина содержимого + 1 байт
YEAR	1 байт

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

Значения столбцов типа DECIMAL и NUMERIC хранятся в строковом виде, что позволяет обеспечить точность представления десятичных чисел. Каждой цифре соответствует один символ, еще по одному символу отводится на знаковый разряд и десятичную точку.

Блокировки таблиц

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

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

Можно заблокировать таблицу таким образом, чтобы разрешить другим потокам обращаться к ней для чтения. Это называется блокировкой чтения. Блокировка записи гарантирует текущему потоку монопольный доступ к таблице. Запросы на чтение откладываются до тех пор, пока не будут сняты все блокировки записи. Эту установку можно изменить с помощью флагов инструкций либо путем задания специальных серверных переменных. Для SQL-инструкций создаются две очереди. Чтобы программа MySQL начала извлекать инструкции из очереди на чтение, очередь на запись должна быть пуста. При наличии флага LOW_PRIORITY инструкции DELETE, INSERT и UPDATEпомещаются в очередь на чтение, т.е. они получают такой же приоритет, что и инструкции SELECT. Флаг HIGH_PRIORITY переводит инструкцию SELECT в очередь на запись.

Индексы

В MySQL индексы хранятся в виде двоичных деревьев. Деревья перестраиваются по мере вставки записей. Это означает, что каждый индекс вызывает небольшое снижение производительности. Как правило, индексы повышают скорость операций выборки за счет снижения скорости операций записи. Тем не менее, наличие индекса еще не гарантирует никакого ускорения. Нужно соотносить их с теми запросами, которые планируется выполнять. Чтобы понять, насколько эффективным окажется тот или иной индекс, пользуйтесь инструкцией EXPLAIN.

Определения индексов хранятся в frm-файле а сами индексируемые значения — в файле с расширением .MYI. Если индексный файл отсутствует на момент запуска сервера, он будет автоматически воссоздан. Таким образом, при создании резервных копий можно не заботиться об индексах в целях экономии места. Позднее, в процессе восстановления базы данных, программа MySQL создаст индексы заново на основании схемы таблицы.

Индексы способны повысить производительность инструкций, связанных с поиском записей. Они ускоряют процесс сравнения столбцов при выполнении операций объединения. Кроме того, они помогают находить минимальное и максимальное значения столбца и ускоряют выполнение инструкций SELECT с предложением ORDER BY.

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

CREATE TABLE car (

Make CHAR(32) NOT NULL,

Model CHAR(32) NOT NULL,

Introduced YEAR,

PRIMARY KEY (MAKE,Model)

);

Листинг 6.2. (html, txt)

У таблицы car имеется составной первичный ключ. В запросе, который показан в листинге 6.3, индекс будет использован, так как столбец Make является самым левым компонентом индекса.

SELECT * FROM car WHERE Make='Ford';

Листинг 6.3. (html, txt)

А вот в следующем запросе (листинг 6.4) этого не произойдет, поскольку правило очередности столбцов не соблюдается.

SELECT * FROM car WHERE Model='Pinto';

Листинг 6.4. (html, txt)

В листинге 6.5 индекс также не используется, из-за того, что самый левый компонент индекса нельзя применить к каждой записи. Если бы в предложении WHERE стоял оператор AND, а не OR, все было бы наоборот.

SELECT * FROM car WHERE Make='Ford' OR Model='Impala';

Листинг 6.5. (html, txt)

Следующий запрос (листинг 6.6) является правильным с точки зрения использования индекса. В данном случае просмотр значений столбца осуществляется слева направо.

SELECT * FROM car WHERE Make LIKE 'F%';

Листинг 6.6. (html, txt)

В листинге 6.7 индекс не используется, потому что просмотр значений столбца осуществляется справа налево (метасимвол % стоит вначале).

SELECT * FROM car WHERE Make LIKE '%d';

Листинг 6.7. (html, txt)

Дескрипторы файлов

Сервер MySQL представляет собой один процесс со множеством потоков. Для каждого сеанса подключения к серверу создается свой поток. Каждому потоку требуется один или несколько дескрипторов файлов, чтобы он мог осуществлять чтение и запись таблиц. Операционная система ограничивает количество файловых дескрипторов, доступных процессу. Это число может быть самым разным. Например, в AIX оно равно 2000 по умолчанию, а в Solaris — всего лишь 64. В Linux лимит по умолчанию составляет 1024 дескриптора. В Windows NT и 2000 видимый предел отсутствует.

Чтобы не исчерпать лимит ресурсов, MySQL хранит кэш файловых дескрипторов всех соединений. По умолчанию размер кэша составляет 64 позиции. В случае переполнения кэша MySQL закрывает самый старый дескриптор, освобождая место для нового. В периоды высокой активности пользователей работа программы может замедляться из-за необходимости часто закрывать и открывать файлы. Пока сервер не прекратит работу или буфер не будет принудительно очищен, файловые дескрипторы остаются открытыми. Если активность настолько высока, что все дескрипторы, находящиеся в кэше, открыты, программа временно увеличивает размер кэша.

Размер кэша дескрипторов можно задать другим, но не забывайте об ограничении, которое накладывается операционной системой. Правда, ее собственный лимит тоже можно изменить. Для этого существует, например, команда unlimit. Еще один способ — перекомпиляция ядра.

Чтобы получить доступ к таблице, поток должен иметь в своем распоряжении дескриптор ее файла данных. Все потоки совместно владеют дескриптором индексного файла. Таким образом, когда три потока одновременно извлекают данные из таблицы, используются четыре дескриптора. Если таблица дважды указана в предложенииFROM, например в случае операции самообъединения, программа MySQL вынуждена открывать отдельный дескриптор для каждой ссылки на таблицу.

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

Системная память

В MySQL специальные буферы и кэши применяются для самых разных целей. Их размеры можно задавать в конфигурационном файле или в командной строке запуска сервера. У каждого потока есть свой стек, буфер приема входных данных от клиента и буфер результатов запроса. Размер стека задается серверной переменнойthread_stack, а размеры обоих буферов — переменной net_buffer_length. Последняя определяет начальные размеры буферов, так как они могут увеличиваться в случае необходимости, например, при обработке столбцов типа BLOB или TEXT.

Все потоки совместно используют индексный буфер. Его размер определяется переменной key_buffer_size. В операциях объединения, проходящих без участия индексных столбцов, используется отдельный буфер (переменнаяjoin_buffer_size), как и в операциях сканирования таблиц (переменная record_buffer).

Если для выполнения операции объединения требуется временная таблица, она создается как резидентная (типHeap). Максимальный размер таких таблиц определяется переменной tmp_table_size. После превышения этого предела таблица преобразуется в формат MyISAM. В любом случае временные таблицы удаляются по окончании операции.

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

По умолчанию вся информация, управляемая сервером mysqld, содержится в так называемом каталоге данных MySQL (MySQL data directory). В нем хранятся все базы данных и файлы состояния с информацией о функционировании сервера. Пользователь, выполняющий функции администратора MySQL, должен знать структуру этого каталога и уметь использовать его в своей повседневной работе.

В этой лекции даются исчерпывающие ответы на следующие вопросы.

• Как определить месторасположение каталога данных. Это необходимо для эффективного управления его содержимым.

• Как организуется и обеспечивается доступ к базам данных и таблицам сервера. Эта информация необходима для создания расписания операций превентивной поддержки и восстановления поврежденных таблиц после сбоя.

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

• Как изменить месторасположение каталога данных по умолчанию или отдельных баз данных.

Знание этих вопросов важно для управления размещением дискового пространства системы. Это может быть необходимо для распределения данных по физическим дискам или перемещения данных в файловых системах с целью освобождения пространства на одном из дисков. Данная информация пригодится и при планировании размещения новых баз данных.

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

Размещение каталога данных

По умолчанию местоположение для каталога данных устанавливается при компиляции сервера. Обычно при инсталляции с исходной дистрибуции устанавливается каталог /usr/local/var, при инсталляции из двоичной дистрибуции — /usr/local/mysql/data, а при инсталляции из файла RPM -- /var/lib/mysql.

Размещение каталога данных можно задать и явным образом при запуске сервера. Для этих целей применяется опция --datadir= /path/to/dir. Она оказывается весьма кстати, если каталог данных необходимо разместить в месте, отличном от того, которое указывается по умолчанию.

Администратор MySQL обязательно должен знать, где находится каталог данных. При запуске нескольких серверов следует записать местоположение всех каталогов данных. Если же размещение каталога неизвестно (например, из-за того, что предыдущий администратор плохо вел свои записи), его можно определить несколькими методами.

• Воспользоваться командой mysqladmin variables для получения пути к каталогу данных непосредственно с сервера. На компьютере, работающем под управлением ОС UNIX, результат ее ввода будет выглядеть примерно так:

• % mysqladmin variables

• +-------------------+-----------------+

• | Variable name | Value |

• +-------------------+-----------------+

• | back log | 5 |

• | connect timeout | 5 |

• | basedir | /var/local/ |

• | datadir | /usr/local/var/ |

• ...

Из приведенных выше результатов видно, что каталог данных размещается в каталоге /usr/local/var/сервера.

На компьютере, работающем под управлением ОС Windows, результаты ввода этой же команды будут выглядеть следующим образом.

% mysqladmin variables

+-------------------+-----------------+

| Variable name | Value |

+-------------------+-----------------+

| back log | 5 |

| connect timeout | 5 |

| basedir | c: \mysql\ |

| datadir | c: \mysql\data\ |

...

Если на компьютере запущено несколько серверов, каждый из них использует свой порт TCP/IP и разъем. Чтобы получить информацию о каталоге данных от каждого сервера, достаточно подключиться с помощью опций --port и --socket к соответствующему порту и разъему.

% mysqladmin --port=port_num variables

% mysqladmin --socket=/path/to/socket variables

Команду mysqladmin можно запускать на любом компьютере, который подключен к серверу. Для подключения к серверу с удаленного компьютера применяется опция --host=host_name.

% mysqladmin --host=host_name variables

С компьютера, работающего под ОС Windows, можно подключиться к работающему через именованный канал серверу Windows NT с помощью опции --pipe, активизирующей соединение по именованному каналу, и опции --socket=pipe_name, определяющей имя канала.

С:\> mysqladmin --pipe --socket=pipe_name variables

• Воспользоваться командой ps для вывода командной строки исполняемого процесса mysql. Попробуйте одну из указанных ниже команд (в зависимости от версии ps, поддерживаемой системой) и поищите переменную--datadir в выводимых результатах.

• % ps axww | grep mysql ps BSD—UNIX

• % ps -ef | grep mysql ps системы System V

Команда ps особенно полезна при запуске на одном компьютере нескольких серверов, поскольку позволяет узнать месторасположение сразу всех каталогов данных. Недостаток этого метода заключается в том, что команду ps обязательно нужно запускать на главном компьютере. Кроме того, она будет бесполезна, если переменная --datadir не описана явным образом в командной строке mysql.

• Если MySQL инсталлировалась из исходной дистрибуции, месторасположение каталога данных можно получить из информации о конфигурации. Так, например, месторасположение каталога указывается в элементе верхнего уровня Makefile. Однако будьте осторожны, поскольку позиция каталога является вMakefile значением переменной localstatedir, а не datadir, как ожидают многие. Кроме того, если дистрибуция размещается на смонтированной сетевой файловой системе NFS и используется для установки MySQL на несколько компьютеров, в информации конфигурации отражаются данные только для компьютера, на котором система устанавливалась последней. Вполне возможно, что им окажется не тот компьютер, для которого необходимы данные.

• Если все предыдущие методы вам не подходят, можно воспользоваться командой find для поиска файлов базы данных. Приведенная ниже команда ищет все файлы .frm (описания), являющиеся частью инсталляций MySQL:

• % find / -name "*.frm" -print

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

Структура каталога данных

Каталог данных MySQL содержит все управляемые сервером базы данных и таблицы. Они организованы в структуру простого дерева, что позволяет, в свою очередь, воспользоваться преимуществами иерархической структуры файловых систем ОС UNIX и Windows.

• Каждой базе данных соответствует подкаталог, расположенный внутри каталога данных.

• Таблицам базы данных соответствуют файлы, размещенные внутри каталога базы данных.

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

Как обеспечивается доступ к данным сервера MySQL

Каждый элемент внутри каталога данных находится под управлением MySQL-сервера mysqld. Клиентские программы никогда не обращаются к данным напрямую. Точку взаимодействия, с помощью которой осуществляется доступ к базам данным, обеспечивает сервер, работающий в качестве промежуточного звена между клиентскими программами и данными (рис. 7.1).

Рис. 7.1.  Сервер является промежуточным звеном между клиентскими программами и данными

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

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

• В одном каталоге данных запущено несколько серверов. Как правило, для управления всеми базами данных используется один сервер, хотя можно запустить сразу несколько серверов. Если это делается для обеспечения доступа к нескольким отдельным каталогам данных, то проблем с взаимодействием различных серверов не возникает. Можно задать нескольким серверам один каталог данных (хотя удачным такое решение назвать сложно). В таком случае необходимо обязательно убедиться, что системы обеспечивают надежную блокировку файлов друг от друга. Иначе серверы начнут взаимодействовать некорректно. Кроме того, при одновременном запуске нескольких серверов в одном каталоге регистрационные файлы будут содержать беспорядочные данные, а не ценную информацию.

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

Представление баз данных

Каждая управляемая сервером MySQL база данных имеет свой собственный каталог. Он представлен в виде подкаталога каталога данных и имеет такое же название, как и собственно база. Так, например, базе данных my_dbбудет соответствовать каталог базы данных DATADIR/my_db.

Такое представление значительно упрощает понимание предназначения и принципов работы некоторых операторов обработки баз данных. Так, оператор create database db_name создает пустой подкаталог db_name в каталоге данных, устанавливая права владения и режим, которые обеспечивают доступ только для пользователя сервера MySQL (UNIX-пользователя, работающего на сервере). Аналогичных результатов создания базы данных пользователь сервера может добиться и вручную, введя следующие команды.

% mkdir DATADIR/db_name Создает каталог базы данных

% chmod 700 DATADIR/db_name Делает его доступным только для пользователя сервера MySQL

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

Также легко можно реализовать и команду DROP database. Команда drop database db_name удаляет из каталога данных подкаталог db_name вместе со всеми расположенными в нем файлами. Тех же результатов можно достичь с помощью команды:

% rm -rf DATADIR/db_name

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

Команда show databases на самом деле выводит список названий подкаталогов каталога данных. Некоторые системы управления базами данных поддерживают специальную таблицу со списком всех баз данных. В MySQL такой таблицы нет. Благодаря простоте структуры список баз является списком подкаталогов каталога данных. Следовательно, и необходимость в подобной таблице отсутствует.

Представление таблиц баз данных

Каждая таблица представлена в каталоге базы данных в виде трех файлов: файла формы (описания), файла данных и файла индексов. Основное имя файла соответствует названию таблицы, а его расширение отражает тип файла. Краткое описание расширений представлено в табл. 7.1. По расширениям файлов данных и индексов можно определить, используется ли в таблице старый формат ISAM или новый MyISAM.

Таблица 7.1. Типы файлов MySQL
Тип файла	Расширение имение файла	Содержимое файла
Файл формы	frm	Описывает структуру таблицы (столбцы, типы столбцов, индексы и т.п.)
Файл данных	ISD (ISAM)или MYD (MyISAM)	Содержит данные таблицы, т.е. его строки
Файл индексов	ISM (ISAM)или MYI (MyISAM)	Содержит дерево индексов для каждого файла данных. Этот файл существует независимо от того, имеются в таблице индексы или нет

При выполнении оператора CREATE TABLE tblname, определяющего структуру таблицы, сервер создает файл tblname.frm с внутренней кодировкой структуры. Кроме того, создаются также файлы данных и индексов с информацией об отсутствии записей и индексов. (Если оператор create table включает спецификации индексов, в файле индексов они отражаются соответствующим образом.) Параметры владельца и режима файлов таблицы устанавливаются такими, чтобы обеспечить доступ только пользователю сервера MySQL.

При исполнении оператора alter table расшифровывает файл tbl_name.frm и изменяет файлы данных и индексов с учетом определенных оператором структурных изменений. Такие же операции имеют место и при выполнении операторов create index и drop index, поскольку они рассматриваются сервером как эквивалентные операторуALTER table. В процессе выполнения оператора drop table из каталога базы данных удаляются все три представляющих таблицу файла.

Пользователь не может вручную создать или изменить таблицу, хотя имеется возможность удалить ее — для этого достаточно удалить три соответствующих файла. Так, например, эквивалентом оператора drop table my_tbl для текущей базы данных my_tbl может быть команда:

% rm -f DATADIR/my_db/my_tbl. *

Вывод оператора SHOW tables mydb представляет собой простой список имен (без расширений) FRM-файлов каталога базы данных my_db. Как уже отмечалось ранее, некоторые СУБД поддерживают специальный реестр со списком всех таблиц баз данных. В MySQL такой реестр не нужен, поскольку список таблиц легко определяется благодаря структуре каталога данных.

Ограничения операционной системы на имена баз данных и таблиц

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

• Имена могут включать буквы и цифры текущего набора символов, а также символы подчеркивания и доллара ("_" и "$").

• Длина имен не может превышать 64 символа.

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

Во-первых, в именах баз данных и таблиц можно использовать только разрешенные для имен файлов символы. Так, например, символ "$" разрешается правилами MySQL, однако в некоторых операционных системах его нельзя применять в именах файлов. Такого рода ограничения отсутствует в ОС UNIX и Windows. Наиболее значительные проблемы могут возникнуть в момент ссылки на имена баз данных при выполнении задач администрирования непосредственно из оболочки. Предположим, например, что база данных имеет имя $my_db. В этом случае всякая ссылка на имя базы может интерпретироваться как ссылка на переменную:

% Is $my_db

my_db: Undefined variable.

Чтобы избежать возможных недоразумений, нужно либо вообще избегать использования символа "$", либо использовать кавычки, подтверждая его специальное значение:

% Is my_db

% Is '$my_db'

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

Во-вторых, несмотря на то, что MySQL разрешает задавать для баз данных и таблиц имена длиной до 64 символов, на самом деле их длина ограничивается максимально возможной длиной имен файлов. В большинстве случаев эта проблема как таковая отсутствует, хотя в некоторых UNIX-системах System V все еще существует старое ограничение в 14 символов. В таком случае имя таблицы должно содержать не более 10 символов, поскольку четыре остальных позиции отводится под точку и трехсимвольное расширение.

В-третьих, на присвоение имен базам данных и таблицам оказывает влияние также чувствительность используемой файловой системы к регистру символов. Если буквы нижнего и верхнего регистров операционной системой воспринимаются по-разному (как, например, в ОС UNIX), имена my_tbl и My_tbl будут указывать на разные таблицы. Если же регистр не играет никакой роли (как, например, в Windows), my_tbl и My_tbl окажутся разными названиями одной и той же таблицы. Об этом следует помнить при разработке базы данных, которую впоследствии планируется перенести на другую платформу.

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

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

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

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

Так, например, каталог базы данных, включающей 10000 таблиц, содержит 30000 файлов. При открытии большого количества таблиц замедление выполнения операций открытия становится достаточно заметным. (Особенно это относится к файловым системам Linux ext2 и Solaris.) Если же эта проблема приобретает действительно угрожающие масштабы, возможно, имеет смысл пересмотреть структуру своих таблиц в соответствии со спецификой работы приложений и соответствующим образом их реорганизовать. Тщательно подумайте, действительно ли необходимо такое большое число таблиц. Иногда приложения генерируют их безо всякой на то необходимости. Много таблиц с аналогичными структурами могут генерироваться приложениями, которые создают отдельную таблицу для каждого пользователя. Чтобы объединить такие таблицы в одну, достаточно добавить столбец, который идентифицирует пользователя владельца строки. Если в результате таких действий число таблиц значительно уменьшится, производительность приложения возрастет.

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

• Ограничение дискового пространства. Объединение таблиц приводит к уменьшению их числа (уменьшая, в свою очередь, время открытия), но сопровождается добавлением дополнительных столбцов (занимая дополнительное дисковое пространство). В результате складывается достаточно распространенный компромисс между временем обработки и свободным пространством, и администратор должен решить, какой фактор более важен. Если на первом плане стоит скорость, возможно, придется пожертвовать дополнительным дисковым пространством. Если же объемы дисков сильно ограничены, придется использовать большее количество таблиц и немного дольше ждать при открытии.

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

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

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

В последнее время намечается тенденция к ослаблению ограничений на размеры таблиц. Так, например, если в ОС IBM AIX 4.1 существовало ограничение на размер файла 2 Гбайта, то в ОС IBM AIX 4.2 оно увеличилось до приблизительно 64 Гбайт. Внутренний лимит на размер таблицы в MySQL в новых версиях также увеличивается. Так, если во всех предшествующих версии 3.23 системах он составляет 4 Гбайта, то в 3.23 был поднят до приблизительно 9 миллионов терабайт. Данные табл. 7.2 позволяют оценить, как внутренний лимит на размер таблицы в MySQL сопоставляется с ограничением файловой системы AIX. Подобные сопоставления можно применять и для других операционных систем.

Таблица 7.2. Сопоставление ограничений MySQL и операционной системы
Версия MySQL	Версия AIX	Максимальный размер таблицы	Ограничивающий фактор
MySQL 3 22 22	AIX 4 1	2 Гбайт	Максимальный размер файла AIX 2 Гбайта
MySQL 3 22 22	AIX 4 2	4 Гбайт	Максимальный размер таблицы MySQL — 4 Гбайта
MySQL 3 23	AIX 4.1	2 Гбайт	Максимальный размер файла AIX 2 Гбайта
MySQL 3 23 11	AIX 4 2	64 Гбайт	Максимальный размер таблицы MySQL — 64 Гбайта

Файлы состояния MySQL

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

Сервер записывает ID-номер своего процесса (Process ID — PID) в PID- файл при запуске и удаляет этот файл при завершении работы. Именно с помощью PID-файла сервер позволяет находить себя работающим процессам. Так, например, если во время завершения работы системы запустить сценарийmysql.server для завершения работы и сервера MySQL, данный сценарий обратится к PID-файлу. Это обращение позволит определить, какому процессу отправить команду на завершение работы.

Таблица 7.3.
Тип файла	Имя по умолчанию	Содержимое файла
ID-номер процесса	HOSTNAME, pid	ID-номер процесса сервера
Журнал ошибок	HOSTNAME, err	События запуска и завершения работы, а также записи об ошибках
Общий журнал	HOSTNAME, log	События подключения /отключения и информация о запросах
Журнал обновлений	HOSTNAME, nnn	Текст всех запросов, изменяющих содержимое или структуру таблицы

Журнал ошибок создается сценарием safe_mysqld. Впоследствии именно в этот журнал перенаправляются все стандартные сообщения об ошибках сервера. Другими словами, журнал содержит все сообщения, записываемыесервером в stderr, и существует, только если сервер запускается с помощью вызова сценария safe_mysqld. (Это более предпочтительный метод запуска сервера, поскольку safe_mysqld перезапускает сервер при сбое в работе из-за ошибки.)

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

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

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

990509 7:34:09 492 Connect paul@localhost on test

492 Query show databases

492 Query show tables

492 Field List tbl_l

492 Field List tbl 2

990509 7:34:22 492 Query CREATE TABLE my_tbl (val INT)

990509 7:34:34 492 Query INSERT INTO my_tbl VALUE(A)

990509 7:34:38 492 Query DROP TABLE my_tbl

990509 7:34:40 492 Quit

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

use test;

CREATE TABLE my_tbl (val INT);

INSERT INTO my_tbl VALUE(A);

DROP TABLE my_tbl;

Для получения расширенной формы журнала обновлений используется опция --log-long-format. В расширенном журнале предоставляется информация также об отправителе и времени поступления запроса. Эти данные занимают, конечно, больше места на диске, однако позволяют узнать, кто и что пытался сделать. Сопоставлять информацию о событиях в общем журнале и журнале обновлений для этого не нужно. Для уже описанной выше сессии расширенный журнал обновлений будет выглядеть таким образом:

# Time: 9905097:43:42

# User@Host: paul [paul] @ localhost []

use test;

CREATE TABLE my_tbl (val INT);

# User@Host: paul [paul] @ localhost {]

INSERT INTO my_tbl VALUE(A);

# Time: 9905097:43:43

# User@Host: paul [paul] 0 localhost П

DROP TABLE my_tbl;

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

990509 7:47:24 4 Query UPDATE user SET

Password=PASSWORD("secret")

WHERE user="root"

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

% chmod 700 DATADIR

Запустите эту команду, зарегистрировавшись в качестве пользователя-владельца каталога данных. Не забудьте также зарегистрироваться под именем этого пользователя на сервере, иначе команда не только запретит другим пользователям доступ к каталогу данных (что и требуется), но также закроет базы данных для сервера (чего допустить ни в коем случае нельзя).

Файлы состояния размещаются на верхнем уровне каталога данных вместе с каталогами баз данных. Поэтому иногда пользователи беспокоятся, что имена файлов состояния могут конфликтовать с именами баз данных (например, при выполнении сервером оператора SHOW databases). Этого бояться не стоит. Информация о событиях и состояниях хранится в файлах, а базы данных записаны в каталогах, что позволяет исполняемым программам легко отличить их, вызвав команду stat(). (Именно таким образом их различает сервер.) Просматривая каталог данных с помощью опции ls -1, пользователь может отличить файлы состояния от каталогов баз данных, определив первую букву данных в режиме: ' - ' или ' d':

% ls –l DATADIR

total 31

drwxrwx-- 1 mysqladm mysqlgrp 1024 May 8 13:22 blgdb

drwxrwx-- 2 mysqladm mysqlgrp 1024 Dec 15 22:34 mysql

-rw-rw--- 1 mysqladm mysqlgrp 64 May 9 20:11 pit-vlper. 001

-rw-rw-r- 1 mysqladm mysqlgrp 24168 May 9 20:11 pit-vlper. err

-rw-rw--- 1 mysqladm mysqlgrp 4376 May 9 20:11 pit-vlper. log

-rw-rw-r- 1 mysqladm mysqlgrp 5 May 9 20:11 pit-vlper. pld

drwxrwx-- 7 mysqladm mysqlgrp 512 Sep 10 1998 sql-bench

drwxrwx-- 2 mysqladm mysqlgrp 512 May 9 07:34 test

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

Более детально о поддержке файлов регистрации и способах работы с ними рассказывалось в лекции 2 "Общее администрирование MySQL".

Перемещение содержимого каталога данных

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

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

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

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

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

• Некоторые операционные системы хранят PID-файлы сервера в отдельном каталоге, например /var/run. Возможно, для большей согласованности работы системы администратор пожелает разместить в этой папке и PID-файлы MySQL.

Методы перемещения

Существует два способа перемещения компонентов каталога данных.

• Определение опции загрузки сервера с помощью командной строки или в группе [mysqld] конфигурационного файла.

• Перемещение элементов и создание в исходном каталоге символической связи (symbolic link), указывающей на новое местоположение.

Ни один из приведенных методов не является универсальным для переноса информации. В табл. 7.4 отмечается, какие компоненты каталога данных можно перемещать и какой метод следует для этого использовать. Если применяется первый метод, можно задать опции в глобальном конфигурационном файле /etc/my.cnf (C:\my.cnf на компьютерах, работающих под управлением ОС Windows). В последних версиях ОС Windows этот файл может располагаться в системной папке (С:\Windows).

Таблица 7.4. Обзор методов перемещения
Перемещаемый компонент	Применяемый метод перемещения
Целый каталог данных	Опция запуска или символическая связь
Каталоги отдельных баз данных	Символическая связь
Отдельные таблицы баз данных	Символическая связь
PID-файл	Опция запуска
Файл общего журнала	Опция запуска
Файл журнала обновлений	Опция запуска

Для перемещения можно также применить файл my.cnf, расположенный в каталоге данных по умолчанию, однако делать это не рекомендуется. Если ваша цель — переместить весь каталог данных, необходимо оставить этот каталог нетронутым на старой позиции, чтобы разместить в нем конфигурационный файл со ссылкой на "реальный" каталог данных. Это может привести к путанице. Для определения опций сервера лучше воспользоваться конфигурационным файлом /etc/my.cnf.

Определение эффекта перемещения Прежде чем приступать к перемещению каких-либо компонентов, настоятельно рекомендуется убедиться, что эта операция приведет к желаемому эффекту. Для получения информации о пространстве диска некоторые пользователи предпочитают использовать команды du, df и ls -l, хотя этот выбор, в первую очередь, определяется правильным пониманием структуры используемой файловой системы. В приведенном ниже примере существует едва заметная ловушка, в которую можно попасться при перемещении каталога данных. Предположим, что каталог данных /usr/local/var планируется переместить в каталог/var/mysql, поскольку согласно выводу команды df файловая система /varсодержит больше свободного пространства. % df /usr/var Filesystem 1k-blocks Used Avail Capacity Mounted on /dev/wd0s3e 396895 292126 73018 80% /usr /dev/wd0s3f 1189359 1111924 162287 15% /var Сколько же пространства освободится в файловой системе /usr в результате перемещения каталога данных? Чтобы вычислить этот объем, воспользуемся командой du -s и посмотрим, сколько этот каталог занимает: % cd /usr/local/var % du -s 133426 Как видно, этот каталог занимает чуть более 130 Мбайт, которые можно освободить в /usr. Однако можно ли этот прием реализовать на самом деле? Запустите команду df в каталоге данных: % df /usr/local/var Filesystem 1k-blocks Used Avail Capacity Mounted on /dev/wd0s3f 1189359 1111924 162287 15% /var Что же получается? При запросе объема свободного пространства в файловой системе, содержащей каталог/usr/local/var, команда df отображает свободный объем в /var. Почему так? Ответ на этот вопрос дает командаIs -1: % is -l /usr/local lrwxrwxr 1 root wheel 10 Dec 11 23:46 var -> /var/mysql Из результатов выполнения этой команды видно, что /usr/local/var является символической связью с/var/mysql. Другими словами, каталог данных уже перемещен в файловую систему /var и включает указывающую на нее символическую связь. Соответственно, никакой выгоды перемещение каталога данных из /usr в /var не принесет. Суть этого примера состоит в том, что несколько действий по определению эффекта перемещения могут показать нецелесообразность подобного перемещения. Такая предосторожность позволяет вовремя остановиться и не тратить уйму времени на перемещение только для того, чтобы затем понять, что достичь нужной цели невозможно.

Перемещение каталога данных

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

Таблица 7.5. Синтаксис перемещения каталога данных
Метод	Синтаксис
Командная строка	--datadir=/path/to/dir
Конфигурационный файл опций	[mysqld] datadir=/path/to/dir

Перемещение баз данных

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

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

% mysqladmin -u root -p shutdown

Enter password: ********

% cd DATADIR

% tar cf - bigdb | (cd /var/db; tar xf -)

% mv bigdb bigdb.orig

% ln -s /var/db/bigdb .

% safe_mysqld &

Меры предосторожности при перемещении

Необходимо завершить работу сервера перед выполнением операции перемещения и запустить его снова впоследствии. При перемещении некоторых компонентов (например, каталога базы данных) можно, хотя и не рекомендуется, оставить сервер в рабочем состоянии. В этом случае следует убедиться, что сервер не обращается к перемещаемой базе данных. Не забудьте также выполнить оператор flush tables перед перемещением базы данных, чтобы сервер закрыл все открытые файлы таблиц. Игнорирование этих моментов может привести к повреждению таблиц.

Для выполнения всех этих команд необходимо зарегистрироваться в качестве владельца каталога данных. Как видите, исходный каталог данных переименовывается для безопасности в bigdb.orig. После проверки правильности работы сервера с перемещенной базой данных его можно удалить:

% rm -rf bigdb.orig

Перемещение таблиц баз данных

Перемещение отдельных таблиц баз данных — далеко не самая лучшая идея. В случае необходимости эту операцию можно реализовать посредством переноса файлов таблиц на новую позицию и установки символических связей в исходном каталоге данных, указывающих на новое местоположение. Однако если впоследствии выполнить операторы alter table или optimize table, изменения внесены не будут.

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

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

Перемещение файлов состояния

Перемещение PlD-файла, общего журнала и журнала обновлений осуществляется с помощью символических связей. Как уже отмечалось ранее, журнал ошибок создается сценарием safemysqld и поэтому не может перемещаться куда-либо (если, конечно, для этого не прибегнуть к редактированию safemysqld).

Для записи файла состояния в новую позицию завершите работу сервера и перезапустите его, точно определив посредством соответствующей опции новое местоположение. Синтаксис командной строки и файла опций для каждого файла состояния представлен в табл. 7.6.

Таблица 7.6. Синтаксис перемещения файлов состояния
Метод	Синтаксис
Командная строка	-pid-file=pidfile -log=logfile -log-update=updatefile
Файл опций	[mysqld] pid-file=pidfilee log=logfile log-update=updatefile

Удаление перемещенной базы данных

Удалить базу данных можно с помощью оператора drop database, хотя в старых версиях MySQL с удалением перемещенной базы данных могут возникнуть проблемы. Таблицы такой базы данных будут удалены правильно. Ошибка возникает при попытке сервера удалить каталог базы данных поскольку он является лишь символической связью, а не реальным каталогом. Администратор MySQL должен самостоятельно удалить каталог базы данных и указывающую на него связь. Эта проблема устранена в MySQL версии 3 23 и выше.

Если определить имя файла состояния, указав полный путь, то файл будет создан в определенной этим путем позиции. Во всех остальных случаях файл создается в каталоге данных. Так, например, при определении опции --pid-file=/var/run/mysqld.pid PID-файл mysqld.pid будет создан в каталоге /var/run. Если же определена опция--pid-file=mysqld.pid, этим файлом окажется файл DATADIR/mysqld.pid.

При определении имени журнала обновлений без расширения MySQL будет создавать последовательные имена каждый раз при открытии этого журнала. Эти имена будут дополняться расширением nnn, где nnn — следующий не используемый существующим файлом журнала обновлений номер (например, update.001, update.002 и тп). Чтобы избежать создания подобных имен сервером, достаточно определить имя с явным расширением.

В MySQL имеется несколько журналов, позволяющих узнать, что происходит внутри mysqld:

Журнал	Описание
Журнал ошибок	В нем хранятся ошибки запуска, работы или завершения работыmysqld
Журнал isam	В нем хранится информация обо всех изменениях таблиц ISAM. Используется только при отладке кода isam
Общий журнал запросов	В нем хранится информация об установленных соединениях и выполненных запросах
Журнал обновлений log	В нем хранятся все команды, меняющие данные; в скором времени выйдет из употребления
Бинарный журнал обновлений	В нем хранятся все меняющие что-либо команды. Используется для репликации
Журнал медленных запросов	В нем хранятся все запросы, на выполнение которых ушло больше времени, чем указано в переменнойlong_query_time (или запросы, не использовавшие индексов)

Все файлы журналов хранятся в каталоге с данными mysqld. С помощью команды FLUSH LOGS можно заставитьmysqld открыть файлы журналов снова (или — в некоторых случаях — переключиться на новый файл).

Журнал ошибок

Журнал ошибок содержит информацию о том, когда запускается и останавливается mysqld, а также все критические ошибки, обнаруженные в процессе работы.

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

Все ошибки mysqld записывает в stderr, который сценарий safe_mysqld перенаправляет в файл с именем'hostname'.err (в Windows mysqld сохраняет его в каталоге \mysql\data\mysql.err).

В некоторых ОС в журнал включается распечатка части стека погибшего mysqld. С помощью этой информации можно определить причину сбоя.

Начиная с MySQL 4.0.10 можно указать, где именно mysqld должен сохранять журнал ошибок, с помощью опции -log-error[=filename]. Если имя файла не задается, то тогда mysqld будет использовать mysql-data-dir/'hostname'.err на Unix и \mysql\data\mysql.err на windows.

Если вы выполняете FLUSH LOGS старый файл будет сохранен с префиксом -old и mysqld создаст новый пустой журнал.

На старых версиях MySQL журнал ошибок велся скриптом mysqld_safe, который перенаправлял вывод в файл'hostname'.err. В старых версиях можно было изменить имя этого файла опцией -err-log=filename.

Если вы не указываете -log-error или используете опцию -console, то ошибки будут выводиться на stderr (на терминал).

В Windows вывод всегда пишется в .err-файл, если -console не была указана.

Общий журнал запросов

Если вы хотите знать обо всем, что происходит с mysqld, нужно запустить систему с ключом -log[=file]. После этого информация обо всех соединениях и запросах будет записываться в файл журнала (по умолчанию ему дается имя 'hostname'.log). Этот журнал может оказаться полезным, если вы подозреваете наличие ошибки в клиентском ПО и хотите выяснить, что, по мнению mysqld, клиент передал базе.

Старые версии скрипта mysql.server (с MySQL 3.23.4 по 3.23.8) передавали safe_mysqld опцию -log (включить общий журнал запросов). Если вам нужна большая производительность при запуске MySQL в промышленной эксплуатации, вы можете удалить опцию -log из mysql.server или поменять ее на -log-bin..

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

Журнал обновлений (update)

Обратите внимание: журнал обновлений (update) применялся в старых версиях и сейчас заменен бинарным журналом (binary). С этим журналом можно производить те же операции, что и с журналом обновлений.

При запуске с ключом -log-update[=file_name] mysqld создает журнал, в который заносятся все команды SQL, обновляющие данные. Если имя файла не задано, по умолчанию ему присваивается имя хоста. Если файлу присвоено имя, не содержащее пути доступа к нему, этот файл сохраняется в каталоге с данными. Если у имениfile_name нет расширения, mysqld даст файлу примерно такое имя: file_name.###, где ### — номер, увеличивающийся при каждом выполнении команд mysqladmin refresh, mysqladmin flush-logs, FLUSH LOGS или при перезапуске сервера.

Обратите внимание: чтобы вышеописанная схема могла работать, нельзя самостоятельно создавать файлы с тем же именем, что и у журнала обновлений, а также с некоторыми расширениями, которые могут быть восприняты как номер, в каталоге, использующемся для хранения этого журнала!

При запуске с ключами -log или -l mysqld создает общий журнал в файле с именем hostname.log, причем перезапуски и обновления не приводят к созданию нового файла журнала (хотя существующий при таких операциях закрывается и затем открывается вновь). В таком случае скопировать его (в Unix) можно так:

mv hostname.log hostname-old.log

mysqladmin flush-logs

cp hostname-old.log to-backup-directory

rm hostname-old.log

Журнал обновлений работает избирательно — в него попадают только те команды, которые действительно обновляют данные. Команда UPDATE или DELETE, выражение WHERE которой не находит совпадающих строк, в журнал не заносится — как и команды UPDATE, присваивающие столбцам те же значения, которые у них были до "обновления".

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

При желании обновить базу в соответствии с данными журналов обновлений можно воспользоваться следующей командой (при условии, что имена файлов журналов соответствуют форме file_name.###):

shell> ls -l -t -r file_name.[0-9]* | xargs cat | mysql

ls расставляет все файлы журналов в правильном порядке.

Эта возможность может пригодиться в случае, если возникнет необходимость (в результате серьезного сбоя) привести базу в соответствие с резервной копией и затем повторить все обновления, произошедшие с момента создания копии и до сбоя.

Бинарный журнал обновлений

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

Бинарный журнал применяется и при репликации подчиненного сервера(slave) с головного (master).

При запуске с ключом -log-bin[=file_name] mysqld создает файл журнала, в который вносятся данные обо всех обновляющих данные командах SQL. Если имя файла не задано, по умолчанию ему дается имя хоста с окончанием-bin. Если файлу присвоено имя, не содержащее пути доступа к нему, этот файл сохраняется в каталоге данных.

При вводе расширения в имя файла (например: -log-bin=filename.extension) это расширение удаляется без предупреждения.

К имени файла бинарного журнала программа mysqld прибавляет специальное расширение — номер, увеличивающийся при каждом выполнении команд mysqladmin refresh, mysqladmin flush-logs, FLUSH LOGS или перезапуске сервера. При достижении файлом журнала максимального размера, заданного в параметре max_binlog_size, автоматически создается новый. Все неактивные файлы бинарных журналов можно удалить командой RESET MASTER.

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

Опция	Описание
binlog-do- db=database_name	Указывает головному серверу что он должен журналировать обновления в двоичный журнал если текущая (т.е. выбранная) база данных — это 'database_name'. Остальные базы данных, особо не отмеченные, игнорируются. Имейте в виду, что если вы используете эту опцию, то вам следует делать обновления только в этой базе данных. (пример: binlog-do-db=some_database)
binlog-ignore- db=database_name	Заставляет отказаться от занесения в журнал обновлений определенной базы данных (пример:binlog-ignore-db=some_database)

Чтобы была возможность определить, какие файлы журналов используются в данный момент, mysqld создает и индексный файл, содержащий имена всех находящихся в работе файлов. По умолчанию ему присваивается то же имя, что и файлу журнала, но с расширением .index. Имя этого файла можно изменить с помощью параметра -log-bin-index=[filename].

При использовании репликации удалять старые файлы журналов не стоит до тех пор, пока вы не будете уверены в том, что они никогда не понадобятся ни одной зависимой базе. Добиться такого результата можно, запуская команду mysqladmin flush-logs раз в день и затем удаляя все журналы, созданные более 3 дней назад.

Работать с файлами бинарного журнала можно с помощью программы mysqlbinlog. Обновить MySQL в соответствии с записями в журнале можно так:

shell> mysqlbinlog log-file | mysql -h server_name

С помощью программы mysqlbinlog можно даже считывать файлы журнала прямо с удаленного сервера MySQL!

При запуске mysqlbinlog с ключом -help на экран выводится дополнительная информация по работе с этой программой.

При работе с настройками BEGIN [WORK] или SET AUTOCOMMIT=0 для резервного копирования нужно использоватьбинарный журнал, а не старый журнал обновлений.

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

Обновления нетранзакционных таблиц сохраняются в двоичном журнале немедленно после выполнения. Все обновления (UPDATE, DELETE или INSERT), изменяющие данные в транзакционных таблицах (например, BDB-таблицу), находятся в кэше до вызова COMMIT. В этот момент mysqld пишет всю транзакцию целиком в двоичный журнал перед тем, как выполнить COMMIT. Каждый поток при запуске будет создавать буфер размеромbinlog_cache_size для буферизации запросов. Если запрос превышает этот размер, тогда поток откроет временный файл для сохранения транзакции. Временный файл будет удален при выходе потока.

При запуске каждого потока создается буфер запросов, объем которого соответствует значению параметраbinlog_cache_size. Если запрос не помещается в буфере, поток создаст временный файл для кэша. Временный файл удаляется по завершении работы потока.

Параметр max_binlog_cache_size (по умолчанию 4Гб) позволяет ограничить общий объем памяти, используемой для кэширования мультитранзакционного запроса. Если транзакция больше этого — будет произведен откат.

При использовании журнала обновлений или бинарного журнала параллельные операции вставки будут преобразованы в нормальные операции вставки в командах CREATE ... SELECT и INSERT ... SELECT. Это сделано специально — для того, чтобы обеспечить возможность создания точной копии таблиц путем объединения резервной копии с журналом.

Журнал медленных запросов

При запуске с параметром -log-slow-queries[=file_name] mysqld создает файл журнала, в котором сохраняются данные обо всех командах SQL, на выполнение которых ушло больше времени, чем указано в значении параметра long_query_time. Время, уходящее на первоначальную блокировку таблиц, не входит во время исполнения запроса.

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

Если имя файла не задано, по умолчанию ему дается имя хоста с окончанием-slow.log. Если файлу присвоено имя, не содержащее пути доступа к нему, этот файл сохраняется в каталоге с данными.

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

При использовании ключа -log-long-format на экран выводятся и запросы, не работающие с индексами.

Обслуживание файлов журналов

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

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

В системе Linux (Red Hat) для этого можно использовать сценарий mysql-log-rotate. При установке MySQL с дистрибутива RPM этот сценарий устанавливается автоматически. Обратите внимание: использовать журнал для репликации необходимо с максимальной аккуратностью!

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

Заставить MySQL создать новый файл журнала можно с помощью команды mysqladmin flush-logs или SQL-командыFLUSH LOGS. При работе с MySQL версии 3.21 пользоваться можно только командой mysqladmin refresh.

Эта команда выполняет следующие действия.

• Если используется стандартный журнал (-log) или журнал медленных запросов (-log-slow-queries), файл журнала (mysql.log и `hostname`-slow.log по умолчанию) закрывается и открывается вновь.

• Если используется журнал обновлений (-log-update), файл журнала закрывается, после чего создается новый файл с большим номером.

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

shell> cd mysql-data-directory

shell> mv mysql.log mysql.old

shell> mysqladmin flush-logs

а затем сделать резервную копию файла mysql.old и удалить его.

Администратор должен обязательно резервировать базы данных на случай повреждения или потери данных. Только благодаря резервированию все таблицы могут быть восстановлены до прежнего состояния в случае сбоя в работе системы. Кроме того, не исключен вариант, когда резервирование может оказаться единственным путем отступления, если какой-либо неопытный пользователь случайно выполнит операторы DROP database или drop table. Иногда сбой может произойти по вине собственно администратора MySQL. Так, автору этих лекций известны случаи, когда администраторы разрушали файлы таблиц, пытаясь изменить их с помощью таких редакторов, как vi или emacs. Это далеко не самый лучший способ отредактировать таблицы.

Существует два основных способа резервирования баз данных: использование программы mysqldump и непосредственное копирование файлов базы данных (с помощью команд ср, tar или cpio). Каждый метод имеет свои преимущества и недостатки.

• Программа mysqldump тесно взаимодействует с сервером MySQL. Методы непосредственного копирования являются внешними по отношению к серверу и требуют проверки, чтобы клиенты не пытались изменить таблицы баз данных в процессе копирования. Эта же проблема возникает при использовании для резервирования баз данных средств резервирования файловой системы. Если в процессе резервирования кто-то из пользователей изменяет таблицы, их файлы окажутся несовместимыми и не подлежащими восстановлению. Разница между резервированием файловой системы и непосредственным копированием файлов заключается в том, что в первом случае имеется возможность управлять расписанием резервирования.

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

• Программа mysqldump создает простые текстовые файлы, которые можно легко переносить на другие компьютеры, даже с другой аппаратной архитектурой. Копируемые вручную файлы не могут переноситься на другие компьютеры, если, конечно, не используется специальный формат хранения MylSAM. ISAM-таблицы могут копироваться только между компьютерами с подобной архитектурой. Так, например, копирование файлов из системы Solaris на компьютере с процессором SPARC в систему Solaris на компьютер с процессором SPARC будет успешным, чего нельзя сказать о копировании файлов из системы Solaris на компьютере с процессором SPARC в систему Solaris на компьютер с процессором Intel. Впервые появившийся в версии MySQL 3.23 формат хранения MylSAM решает эту проблему, поскольку является независимым от архитектуры компьютера. Соответственно, скопированные файлы можно легко переносить на другой компьютер с любой архитектурой в одном из двух случаев: на втором компьютере также запущена СУБД MySQL версии 3.23 и более поздней либо файлы таблиц представлены в формате MylSAM, а не ISAM.

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

• Регулярно выполняйте резервирование. На этапе планирования разработайте расписание и четко его придерживайтесь.

• Обязательно активизируйте регистрацию обновлений (как это сделать, рассказывается в лекции "Ведение файлов журналов"). Журналы обновлений помогут восстановить базу данных после сбоя, вернее, после восстановления заархивированных файлов дадут возможность вернуть ее в состояние, в котором база данных находилась непосредственно перед сбоем. Для этого необходимо заново внести все изменения, сделанные с момента последнего резервирования, просто запустив запросы журнала обновлений.

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

• Используйте постоянную и легко понятную схему присвоения имен файлам архива. Имена типа backupl, backup2 и т.д. не несут никакой смысловой нагрузки, и когда приходит время восстанавливать информацию, много времени тратится на изучение их содержимого. Гораздо эффективней присваивать архивным файлам имена баз данных и дат резервирования. Например:

• % mysqldump samp_db > /usr/archives/mysql/samp_db.1999-10-02

• % mysqldump menagerie > /usr/archives/mysql/menagerie.1999-10-02

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

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

• Размещайте файлы архивов на отдельном диске. Это снизит вероятность переполнения этими файлами диска, содержащего каталог данных.

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