Mysql мониторинг запросов. Мониторинг MySQL с помощью PRTG

Разбираясь как работает та или иная CMS приходится использовать различные инструменты, облегчающие работу.
Наиболее интересная тема - это работа с баз(ой|ами) данных. Естественно для изучения запросов и результатов запросов нужно использовать что-то универсальное. Что-то, что будет работать стабильно как с известным движком, так и с самописной системой.
Предположим у вас оказалась система управления контентом и вам необходимо посмотреть как реализовано добавление новых пользователей или смена паролей.

Большинство инструментов позволяющих мониторить работу с БД являются платными [раз , два ]. Я хотел что-то более легкое и удобное, поэтому выбрал mysql-proxy. Хотя возможности утилиты гораздо шире чем мне требуется, я опишу лишь основное. Работает как под Windows, так и под Unix системами.

Скачать можно тут: dev.mysql.com/downloads/mysql-proxy
Первым делом нужно определиться с выбором дистрибутива. На данный момент новейшей версией является «MySQL Proxy 0.8.3 alpha», в качестве ОС у меня стоит Windows 7, поэтому все тесты будут на ней.

После нажатия на кнопку «Download» вас попросят авторизироваться либо зарегистрировать, но снизу есть ссылка для скачивания без лишних действий. (В репозиториях Ubuntu и Debian есть готовые пакеты, так что: sudo apt-get install mysql-proxy)

Хочу заметить, что при размере в 7.9Мб в дистрибутив входит Lua с поддержкой основых модулей.

После скачивания архива его нужно распаковать в удобную для вас директорию, возьмем для примера C:\mysql-proxy
Для запуска приложения нужно определиться с параметрами.

В данном контексте многое зависит от того, что у вас установлено. В качестве LAMP я использую Winginx, прочитать об этой связке можно тут: winginx.ru
По умолчанию MySQL работает на 3306 порту, его мы трогать не будем.
Нам нужно изменить порт для подключения php к базе. Для этого необходимо поправить php.ini
Найдем строку «mysql.default_port» и установим порт (по умолчанию 4040). Для более универсальной работы измените и «mysqli.default_port» на 4040

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

Вариант 1. Мониторинг запросов.

Для простого мониторинга необходимо использовать Lua скрипт. Как я уже говорил Lua идет в комплекте, так что ничего нового устанавливать не надо.
Создадим простой скрипт view.lua в директории C:\mysql-proxy\ с содержимым:

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then print(string.sub(packet, 2)) end end

Теперь можно проверить результат.
Для удобства создадим в директории C:\mysql-proxy файл view.bat c содержимым:

C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script=C:\mysql-proxy\view.lua --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

Proxy-backend-addresses - адрес MySQL сервера на который будем проксировать запрос.

Запустив вэб-сервер и выполнив какие либо запросы к базе можете увидеть такое:

Запросы отображаются, хорошо.

Вариант 2. Мониторинг запросов и запись в файл.

Для записи запросов в файл будем использовать штатные возможности Lua.
Создадим файл view-write.lua в директории C:\mysql-proxy\ с содержимым:

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write(string.sub(packet, 2) .. "\n") file:close() print(string.sub(packet, 2)) end end
и bat файл - «view-write.bat»
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-write.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

Результат после выполнения запросов (по адресу «C:\mysql-proxy\sql-log.txt»)

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

Вариант 3. Запросы и результат

По той-же схеме создаём скрипт «view-result.lua»:
function read_query(packet) if packet:byte() == proxy.COM_QUERY then print("Query: " .. string.sub(packet, 2)) local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Query: " .. string.sub(packet, 2) .. "\n") file:close() proxy.queries:append(2, string.char(proxy.COM_QUERY) .. string.sub(packet, 2), {resultset_is_needed = true}) proxy.queries:append(1, packet, {resultset_is_needed = true}) return proxy.PROXY_SEND_QUERY end end function read_query_result(inj) if inj.id == 1 then for row in inj.resultset.rows do local i = 1 local fields = {} while row[i] do if row[i] == row then break end local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i] .. "\n") file:close() print("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i]) i = i + 1 end end return proxy.PROXY_IGNORE_RESULT end end

И view-result.bat
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-result.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

В результате получаем полное логирование запросов и ответов в читаемом виде

Разбираясь как работает та или иная CMS приходится использовать различные инструменты, облегчающие работу.
Наиболее интересная тема - это работа с баз(ой|ами) данных. Естественно для изучения запросов и результатов запросов нужно использовать что-то универсальное. Что-то, что будет работать стабильно как с известным движком, так и с самописной системой.
Предположим у вас оказалась система управления контентом и вам необходимо посмотреть как реализовано добавление новых пользователей или смена паролей.

Большинство инструментов позволяющих мониторить работу с БД являются платными [раз , два ]. Я хотел что-то более легкое и удобное, поэтому выбрал mysql-proxy. Хотя возможности утилиты гораздо шире чем мне требуется, я опишу лишь основное. Работает как под Windows, так и под Unix системами.

Скачать можно тут: dev.mysql.com/downloads/mysql-proxy
Первым делом нужно определиться с выбором дистрибутива. На данный момент новейшей версией является «MySQL Proxy 0.8.3 alpha», в качестве ОС у меня стоит Windows 7, поэтому все тесты будут на ней.

После нажатия на кнопку «Download» вас попросят авторизироваться либо зарегистрировать, но снизу есть ссылка для скачивания без лишних действий. (В репозиториях Ubuntu и Debian есть готовые пакеты, так что: sudo apt-get install mysql-proxy)

Хочу заметить, что при размере в 7.9Мб в дистрибутив входит Lua с поддержкой основых модулей.

После скачивания архива его нужно распаковать в удобную для вас директорию, возьмем для примера C:\mysql-proxy
Для запуска приложения нужно определиться с параметрами.

В данном контексте многое зависит от того, что у вас установлено. В качестве LAMP я использую Winginx, прочитать об этой связке можно тут: winginx.ru
По умолчанию MySQL работает на 3306 порту, его мы трогать не будем.
Нам нужно изменить порт для подключения php к базе. Для этого необходимо поправить php.ini
Найдем строку «mysql.default_port» и установим порт (по умолчанию 4040). Для более универсальной работы измените и «mysqli.default_port» на 4040

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

Вариант 1. Мониторинг запросов.

Для простого мониторинга необходимо использовать Lua скрипт. Как я уже говорил Lua идет в комплекте, так что ничего нового устанавливать не надо.
Создадим простой скрипт view.lua в директории C:\mysql-proxy\ с содержимым:

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then print(string.sub(packet, 2)) end end

Теперь можно проверить результат.
Для удобства создадим в директории C:\mysql-proxy файл view.bat c содержимым:

C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script=C:\mysql-proxy\view.lua --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

Proxy-backend-addresses - адрес MySQL сервера на который будем проксировать запрос.

Запустив вэб-сервер и выполнив какие либо запросы к базе можете увидеть такое:

Запросы отображаются, хорошо.

Вариант 2. Мониторинг запросов и запись в файл.

Для записи запросов в файл будем использовать штатные возможности Lua.
Создадим файл view-write.lua в директории C:\mysql-proxy\ с содержимым:

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write(string.sub(packet, 2) .. "\n") file:close() print(string.sub(packet, 2)) end end
и bat файл - «view-write.bat»
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-write.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

Результат после выполнения запросов (по адресу «C:\mysql-proxy\sql-log.txt»)

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

Вариант 3. Запросы и результат

По той-же схеме создаём скрипт «view-result.lua»:
function read_query(packet) if packet:byte() == proxy.COM_QUERY then print("Query: " .. string.sub(packet, 2)) local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Query: " .. string.sub(packet, 2) .. "\n") file:close() proxy.queries:append(2, string.char(proxy.COM_QUERY) .. string.sub(packet, 2), {resultset_is_needed = true}) proxy.queries:append(1, packet, {resultset_is_needed = true}) return proxy.PROXY_SEND_QUERY end end function read_query_result(inj) if inj.id == 1 then for row in inj.resultset.rows do local i = 1 local fields = {} while row[i] do if row[i] == row then break end local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i] .. "\n") file:close() print("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i]) i = i + 1 end end return proxy.PROXY_IGNORE_RESULT end end

И view-result.bat
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-result.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

В результате получаем полное логирование запросов и ответов в читаемом виде

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

Преимущества MySQL

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

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

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

Производитель-
ность MySQL: основа для ряда других служб

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

Что делает PRTG полноценным средством мониторинга MySQL

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

PRTG позволяет постоянно отслеживать доступность вашей базы данных MySQL . С помощью PRTG вы сократите время простоев базы данных и сможете немедленно реагировать на перебои в ее работе. Результатом будет рост удовлетворенности клиентов и повышение продуктивности сотрудников.

Комплексное средство мониторинга MySQL от PAESSLER отслеживает не только доступность баз данных, но и другие данные о производительности MySQL. Для этого PRTG автоматически опрашивает базы данных и измеряет время , необходимое для установления соединения, выполнения запроса SQL, обработки транзакции и разрыва соединения. На панели мониторинга PRTG отображается понятная статистика общего времени запросов .

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

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

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

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

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

Оповещение может выполняться в форме сообщения электронной почты, SMS или push-уведомления на смартфон. Вы будете информированы своевременно, до эскалации проблемы. Благодаря API-интерфейсу PRTG можно также создавать пользовательские уведомления и автоматически выполнять скрипты или HTTP-действия.

Наслаждайтесь ощущением полного контроля с помощью PRTG!

PRTG позволяет постоянно следить за
базами данных MySQL даже в дороге

Программа PRTG устанавливается за несколько минут и совместима с большинством мобильных устройств.

PRTG контролирует для вас работу этих и многих других производителей и приложений

Переход на PRTG осуществляется легко и безопасно

На рынке существует множество средств для мониторинга серверов MySQL. PRTG обладает следующими преимуществами.

Нет скрытой
платы

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

• Полная версия PRTG на 30 дней
• После 30 дней – бесплатная версия
• Для расширенной версии – коммерческая лицензия

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

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

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

Настройка сенсора MySQL производится легко и не требует много времени.

1. Создание запроса SQL

Самостоятельно определяемые запросы SQL позволяют отслеживать различные ключевые показатели эффективности, например:

• количество посетителей веб-сайта;
• показатели продаж в интернет-магазине;
• открытые заявки в службе поддержки клиентов.

Возможности практически безграничны. Единственное требование: необходимо задать соответствующую инструкцию SQL.

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

SELECT count(idorder), sum(value), avg(value), GROUP_CONCAT(customer SEPARATOR ", ") from test.order

2. Сохранение скрипта

Сохраните запрос SQL в отдельном файле на жестком диске. Убедитесь, что файлу задано расширение.sql и выбран следующий путь:

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mysql

3. Создание сенсора в PRTG

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

Щелкните «Добавить сенсор» на устройстве, которое получит сенсор, выполните поиск по строке MySQL и выберите сенсор MySQL-v2-Sensor. В последующем диалоговом окне введите имя сенсора и имя базы данных, которая будет отслеживаться.

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

Сенсор PRTG будет добавлен после закрытия диалогового окна. Он будет выглядеть примерно следующим образом.

PRTG делает вашу жизнь проще!

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

PRTG экономит время

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

PRTG бережет нервы

Установить PRTG очень просто. Начало работы с PRTG или переход с другого инструмента для мониторинга происходит беспроблемно благодаря функции автоматического обнаружения и готовым шаблонам для многочисленных устройств.

PRTG экономит деньги

PRTG стоит своих денег. 80 % наших клиентов говорят о том, что смогли добиться экономии в сфере управления сетью . А затраты на лицензии PRTG окупаются в среднем всего за несколько недель.

Компания Paessler AG провела обширный опрос свыше 600 ИТ-отделов по всему миру, в которых используется PRTG.
Целью этого опроса была оптимизация и тонкая настройка нашего программного обеспечения для мониторинга работы сети
которая позволила бы лучше удовлетворить потребности администраторов.
По результатам нашего опроса более 95 % участников были готовы порекомендовать PRTG или уже сделали это.

Многократно премированное программное обеспечение

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

PRTG — это комплексное средство мониторинга
баз данных

PRTG поддерживает не только MySQL, но также подходит для мониторинга прочего программного обеспечения баз данных. В программе имеются готовые сенсоры для всех основных сред баз данных, включая Microsoft SQL, Oracle SQL и PostgreSQL. Кроме того, наш сенсор ADO SQL можно использовать почти с любой другой системой управления базами данных. Этот сенсор позволяет отслеживать любой источник данных, который доступен через OLE DB или ODBC.

PRTG: Революционнoe программное обеспечение для мониторинга сети

Используя API , вы можете приспосабливать PRTG индивидуально и динамически для ваших конкретных потребностей:

• HTTP API: доступ к данным мониторинга сети и управления объектами с помощью HTTP-запросов
• Индивидуальные сенсоры: вы можете добавить собственные сенсоры для индивидуального мониторинга сети
• Индивидуальные уведомления: вы можете добавить собственные уведомления для отправки предупреждений во внешние системы
• Новый сенсор REST API: Мониторит практически все, что поддерживает XML и JSON

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

Интерфейс администрирования

MySQL и клоны по умолчанию распространяются без графического интерфейса. В самых простых случаях с несколькими базами для управления достаточно командной строки, когда же количество серверов, баз и админов, их обслуживающих, переваливает за десяток, необходимость в GUI становится более очевидной. Oracle предлагает свою разработку - единый инструмент для разработчиков баз данных и администраторов MySQL Workbench для Windows, Linux, OS X. Это мощная среда с большими возможностями, позволяющая визуально проектировать, создавать базы данных и управлять ими. Доступны все инструменты для настройки серверов, администрирования учетных записей, бэкапа и восстановления, аудита и простого мониторинга состояния. Также с его помощью можно легко выполнить миграцию с других СУБД - MS SQL Server, Sybase ASE, PostreSQL и прочих. Возможности к тому же можно расширить при помощи плагинов. Интерфейс не локализован. Функций очень много, поэтому некоторое время придется потратить, чтобы освоиться, хотя в общем среда удобная. Версия Community (OSS) Edition распространяется по лицензии GNU GPL. Установка проблем не вызывает, доступны пакеты под разные дистрибутивы Linux, Windows и OS X. Для Red Hat / CentOS лучше воспользоваться в EPEL, в Ubuntu APT репозиторием разработчика .

$ wget -c http://dev.mysql.com/get/mysql-apt-config_0.2.1-1ubuntu14.04_all.deb $ sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb $ sudo apt-get install mysql-workbench

Очень популярен среди хостеров phpMyAdmin , позволяющий выполнить в интуитивной среде большинство операций по управлению базами данных, работе с таблицами, индексами, правами доступа, настройку репликации, экспорт информации, бэкап/восстановление, просматривать статистику и так далее. При этом остается возможность непосредственного ввода любых SQL-запросов. Поддерживается управление несколькими серверами. Все достаточно интуитивно, и с администрированием может справиться пользователь без особой подготовки, с любого устройства, где есть браузер. В Сети множество инструкций и примеров по использованию phpMyAdmin. Установка из репозитория пакетов проблем не вызывает, в качестве веб-сервера можно использовать не только Apache, но и более легкие nginx или lighttpd. Некоторые панели управления хостингом вроде cPanel и Plesk имеют поддержку phpMyAdmin.

Пользователи Windows наверняка оценят HeidiSQL , поддерживающий управление MySQL, MS SQL и PostgreSQL (пока экспериментально) и распространяемый под Open Source лицензией. Программа имеет очень удобный интерфейс, поддерживает подключение сразу к нескольким серверам, которые доступны в одном окне, это упрощает операции по экспорту/импорту данных. Доступно создание и редактирование баз, таблиц, управление привилегиями, экспорт таблиц (CSV, HTML, XML, SQL, ...), поиск, оптимизация, мониторинг. При написании запросов помогает автодополнение. Поддерживается командная строка, возможно подключение по SSH-тоннелю. Есть Portable-версия, используя Wine, его можно запустить и в *nix / OS X.

Для тех, кому не подошли описанные продукты, в интернете можно найти большое количество аналогов: SQLyog , dbForge Studio for MySQL , TOra , SQL Buddy и другие.

Утилиты мониторинга

Как и любое приложение, СУБД требует постоянного наблюдения за своей работой, чтобы в случае проблем легко можно было найти узкое место. Общую информацию о работе MySQL можно получить при помощи стандартного клиента mysqladmin. Запросы вроде SHOW QUERY LOG, SHOW PROCCESSLIST, SHOW VARIABLES, SHOW GLOBAL STATUS и другие редко дают четкую картину, так как медленные запросы есть всегда, но они не обязательно влияют на работу сервиса. Есть еще утилита mysqldumpslow, которая анализирует данные slow.log и выводит самые частые медленные запросы.

Версия Enterprise предлагает специальный инструмент MySQL Enterprise Monitor, который предоставляет в реальном времени информацию о производительности и доступности всех баз данных MySQL. Кроме того, для большинства систем мониторинга, включая Open Source Nagios, Cacti, Zabbix, Ganglia, доступны специальные плагины. Например, Nagios . Каждый из плагинов должен быть правильно настроен, чтобы представить подробную информацию о том, что происходит в MySQL, а это нередко требует опыта. Разворачивать полноценную систему мониторинга в том случае, когда задача только контроль СУБД, не всегда рационально. В этом случае на помощь приходят специализированные инструменты.

К сожалению, весьма популярный mytop уже более двух лет не развивается (хотя это не значит, что его нельзя использовать), но у него есть прекрасная и более функциональная замена. Начиная с версии MySQL 3.23.41 в состав InnoDB входит InnoDB Monitor innotop , некий аналог юниксовского top для этой СУБД. Innotop выводит в удобном виде информацию по внутреннему состоянию InnoDB, которая может пригодиться при настройке производительности. Вариантов запуска утилиты много. Самый простой - выполнить от имени определенного пользователя без параметров

$ innotop -u root -p password

По умолчанию подключение производится к локальному серверу, но можно указать любой узел или мониторить сразу несколько серверов. Список всех команд (Shift + клавиша) можно получить, нажав клавишу вопроса. Например, выведет список всех текущих запросов. Параметр --write позволяет сохранить данные соединения в файл.innotop/innotop.conf:

$ innotop --write

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

Появившийся в 2009 году проект mycheckpoint за несколько лет стал фактически стандартным инструментом для мониторинга MySQL. Причина популярности - это уникальный подход. Для хранения и запроса используется база данных, в которую (одна операция INSERT) собираются данные о метриках мониторинга, статистики и переменных MySQL, информации об ОС Linux (состояние ОЗУ и swap, нагрузка и прочее). Также в базу попадают результаты пользовательских запросов. Собранную информацию легко просмотреть при помощи простых SELECT-запросов. При этом администратор получает наглядные графики (на основе Google Chart API), отчеты и метрики, которые генерируются на лету. Для вывода HTML отчетов может использоваться собственный веб-сервер. Также могут быть настроены предупреждения, отсылаемые по email. Возможен мониторинг удаленной системы и нескольких серверов.

Написан на Python, поэтому установка сложностей не вызывает. Разработчики предлагают deb, rpm и tar.gz пакет и сырцы. В Ubuntu следует ввести следующие команды:

$ sudo apt-get install python-mysqldb $ sudo dpkg -i mycheckpoint-231-1.deb

После чего потребуется создать базу данных для хранения информации:

$ mysql -uroot -ppassword mysql> CREATE DATABASE mycheckpoint; mysql> GRANT ALL PRIVILEGES ON mycheckpoint.* TO "user"@"localhost" IDENTIFIED BY "password";

Для сбора и вывода данных используется утилита mycheckpoint, которую можно запускать вручную или через cron. Параметры подключения к MySQL- и SMTP-серверу указываются также в командной строке:

$ mycheckpoint --user=user --password=password --host=server --port=3306

Или записываются в конфигурационный файл (по умолчанию /etc/mycheckpoint.cnf):

*/5 * * * * mycheckpoint --defaults-file=/root/mycheckpoint.cnf

Теперь можем просматривать собранную информацию и генерировать отчеты при помощи SQL-запросов.

$ mysql mycheckpoint -e "SELECT html FROM sv_report_html_brief" --silent --raw > ./checkpoint_report.html

Еще один полезный инструмент, позволяющий контролировать, анализировать и при необходимости изменять обмен данными между MySQL-сервером и клиентским приложением, называется MySQL Proxy . Возможности у программы очень большие. Работает как под Windows, так и под *nix-системами. Установка проблем не вызывает, в настройках необходимо указать порт MySQL (по умолчанию 3306) и PHP (строка mysql.default_port в php.ini). Все запросы формируются на языке Lua, документация здесь хорошо помогает.

Аудит MySQL

Одна из проблем, связанных с эксплуатацией любого программного продукта, - его неправильная настройка. После установки присутствуют лишние демонстрационные учетные записи, тестовые базы, сами пользователи могут ставить простые пароли, которые легко подобрать. Решить эти проблемы можно лишь при помощи постоянного аудита, который к тому же будет обязателен, если производится обработка конфиденциальных/персональных данных (кредитные карты, медицинские записи и подобное). Требуется создать среду, соответствующую стандартам безопасности (SOX, HIPAA и прочим), при расследовании инцидентов, устранении неполадок. После установки нужно обязательно использовать mysql_secure_installation, который обеспечивает минимальный набор проверок, позволяющих скорректировать общие настройки безопасности.

Далее уже следует использовать инструменты и скрипты, о которых ниже. Задача аудита упрощается тем, что разработчики MySQL предлагают соответствующий API. Правда, в MySQL плагин audit_log доступен только для версии Enterprise . Разработчики Percona Server предлагают GPL альтернативу данному модулю, которая подходит для аудита MySQL и клонов. Две другие альтернативы, McAfee MySQL Audit Plugin и MariaDB Audit Plugin for MySQL , также справляются со своей задачей и позволяют производить аудит MariaDB, MySQL и Percona Server, но используют свой собственный формат журнала аудита, отличающийся от стандартного MySQL. Это потребует чуть больших первоначальных настроек. Применение плагинов простым назвать нельзя и подробно освещено в документации, которую все равно требуется прочитать, поэтому останавливаться не будем.

Кроме того, Патриком Карлссоном (Patrik Karlsson) представлен набор тестов для сетевого сканера Nmap, позволяющий протестировать сервер на наличие основных проблем безопасности. В Ubuntu после установки настройки хранятся в каталоге /usr/share/nmap/nselib/data и nmap/script, для проверки сервера необходимо указать параметры подключения и учетную запись.

$ nmap -p 3306 1.1.1.1 --script mysql-audit --script-args "mysql-audit.filename="/usr/share/nmap/nselib/data/mysql- cis.audit" ,mysql-audit.username="root",mysql- audit.password="password""

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

Percona Toolkit for MySQL

Штатные инструменты, поставляемые с MySQL, предоставляют лишь базовые возможности по администрированию, в результате многие операции приходится выполнять вручную. Это может быть проблемой, ведь уследить за всем очень сложно, и часто потребуется определенный опыт, да и легко допустить ошибку. Пакет Percona Toolkit for MySQL собрал наработки двух проектов - Maatkit и Aspersa - и предоставляет скрипты, позволяющие производить многие рутинные операции администрирования: проверять состояние репликации, собирать информацию, оптимизировать запросы, производить тюнинг сервера, архивировать/восстанавливать данные и многое другое. Всего более 4000 тестов и настроек. Пакет доступен для основных дистрибутивов Linux (в Ubuntu пакет percona-toolkit). После установки получим 32 утилиты, имя которых начинается с pt-*, назначение часто понятно из названия. При запуске можно задавать различные фильтры и форматировать вывод. Поэтому документацию почитать все равно придется, так как каждая утилита имеет большое количество параметров. Например, скрипт pt-summary выведет всю информацию по серверу, собранную /proc/cpuinfo, /proc/meminfo, mount, df и другими утилитами, pt-show-grants покажет все права пользователей СУБД, pt-query-digest позволяет строить отчеты, основанные на анализе логов, обработанных сервером запросов, а также информации, собранной processlist и tcpdump. Например, просмотр медленных запросов двумя способами:

$ pt-query-digest slow.log $ pt-query-digest --user=user --password=password --processlist --host=example.org

Еще один популярный пакет - openark kit предлагает 14 утилит, позволяющих провести тестирование СУБД: проверять установки, проверять пароли (пустые, одинаковые, слабые), блокировать аккаунты, прерывать запросы, фильтровать записи в журнале, выводить статус репликации, исправлять кодировки и многое другое. Распространяется по лицензии BSD. Написан на Python и требует python-mysqldb. Для установки предлагаются deb- и rpm-пакеты и сырцы, процесс стандартный и проблем не вызывает.

Наиболее популярен скрипт комплекта oak-security-audit, предназначенный для периодического аудита аккаунтов, паролей, привилегий и прочих настроек сервера. В общем случае его можно запустить без параметров. По умолчанию используется режим наибольшего уровня контроля системы (соответствует --audit-level=strict):

$ oak-security-audit --socket=/var/run/mysqld/mysqld.sock --user=user --password=password

В результате получим отчет, все, что не помечено как Passed, требует пристального внимания. По умолчанию утилиты из openark kit (включая и mycheckpoint, о котором выше, того же разработчика) используют для подключения к СУБД сокет /var/run/mysqld/mysql.sock (это вшито в сам скрипт), в Ubuntu файл называется mysqld.sock. Чтобы не задавать каждый раз путь, можно использовать заранее подготовленный файл с настройками подключения и указывать при помощи -defaults-file. Как вариант: изменить настройки MySQL в my.cnf. Править скрипты неудобно, так как при обновлении они работать не будут. Следующий скрипт, oak-block-account, очень популярен у разного рода хостеров для временного отключения доступа к СУБД. Дело в том, что стандартный механизм СУБД не позволяет отключать временно аккаунт (что-то вроде REVOKE login ON . ), то есть если учетная запись есть, пользователь всегда может подключаться к базе данных. Можно, конечно, использовать что-то вроде

Mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM "USER"@"%";

но в случае восстановления прав придется помнить все настройки. И главное, если посмотреть права при помощи

Mysql> SHOW GRANTS FOR "USER"; GRANT USAGE ON *.* TO "USER"@"%" IDENTIFIED BY PASSWORD .....

мы убедимся, что такая команда не сбрасывает USAGE. Использование REVOKE USAGE фактически означает DROP USER. Проще изменить логин и пароль, но восстановление может быть проблемой. В общем, возни и рисков много. В случае использования oak-block-account учетная запись остается неизменной, ей просто задается временный пароль, поэтому подключиться с этой учетной записью нельзя.

$ oak-block-account --block --account-user=USER --account-host=example.org

Дополнительный параметр --kill позволит сбросить сразу все активные подключения. Восстановить работоспособность учетки также просто. Смотрим список аккаунтов и их статус:

$ oak-block-account --list

И включаем учетную запись:

$ oak-block-account --release --account-user=USER --account-host=example.org

Другие скрипты из openark kit позволяют упростить некоторые операции. Например, oak-chunk-update дает возможность выполнить большие операции UPDATE/DELETE без длительных блокировок, разбив задачу на небольшие куски. Скрипт oak-show-replication-status выводит состояние репликации, oak-kill-slow-queries удаляет запросы, выполняющиеся уже долгое время, oak-repeat-query выполняет запрос, пока не достигнет определенного условия (количество итераций, время).

Тюнинг MySQL

Оптимизация настроек очень тонкий процесс, ведь нужно на основании собранной статистики изменить только то, что действительно повлияет на производительность. Самым известным инструментом для MySQL является Perl-скрипт MySQLTuner , который доступен в репозиториях большинства дистрибутивов Linux. Он читает текущие настройки сервера и установки MySQL, после чего выдает рекомендации (только рекомендации) по их изменению. Если установка производилась при помощи пакетов, то достаточно ввести имя скрипта, иначе следует вызывать, указывая интерпретатор:

$ perl mysqltuner.pl

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

Оригинальный скрипт написан под *nix, но на CodePlex доступна адаптированная версия для Win. Альтернативой можно назвать MySQL Performance Tuning Primer Script , который выдает не такую наглядную информацию, но зато более «разговорчивый» о проблемах.

Вывод

Это, конечно, далеко не все must have инструменты, которые должны быть под рукой у администратора баз данных. Но это, наверное, тот необходимый минимум, который следует изучить. Кроме того, в процессе знакомства начинаешь больше понимать механизмы, заложенные в MySQL.