MySQL On air. Мониторим SQL запросы

  • Tutorial
image
Разбираясь как работает та или иная 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)
image

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

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

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

image

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

Вариант 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 сервера на который будем проксировать запрос.

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

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

Вариант 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»)
image

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

Вариант 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


В результате получаем полное логирование запросов и ответов в читаемом виде
image
Поделиться публикацией
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама
Комментарии 31
  • –6
    Потеря производительности Вас не беспокоит?
    • +18
      Производительность на локалхосте не беспокоит. Это лишь способ разобраться какие запросы присутствуют в том или ином контексте.
      • +12
        а) разбираешься как работает незнакомая система обычно не на боевом сервере
        б) если нужно найти где она теряется на боевом, то оно того стоит
      • 0
        Спасибо. Не знал о таком инструменте, извращался по другому.
        • +12
          Стоит упомянуть, что MySQL из коробки умеет логировать запросы
          • +2
            Преимущество в том, что нам не нужно лезть в конфиги, к тому-же прицепиться мы можем сразу к нескольким серверам. (--proxy-backend-addresses=localhost:3306 --proxy-backend-addresses=localhost:3307 --proxy-backend-addresses=localhost:xxx)
            Все в одной коробке.
            • +2
              Почему же сразу в конфиг, это делаеть можно и нужно на боевом прямо с клиента:

              устанавливаем куда писать лог
              set global general_log_file='/tmp/mysql_query.log';
              


              включаем лог
              set global general_log = 1;
              


              а через пару минут выключаем
              set global general_log = 0;
              


              Просто на боевом сервере за 10 минут он может нагнать файл размером в 100М и т.д.
            • 0
              Так Proxy же не только логировать их умеет. Lua — полноценный язык, можно делать с запросами что хочешь.
            • 0
              Скромно напомню о бинари логах…
              • 0
                :) Бинарные логи это сильно особено когда они row based или mixed.
                Кстати не подскажете как лучше всего прочитать row based записть в логе?
              • +5
                Думаю есть решение по проще — включить логирование на стороне MYSQL (http://dev.mysql.com/doc/refman/5.5/en/query-log.html)

                и TIle -F Вам в помощь
                • –1
                  Не всегда есть доступ к настройкам MySQL сервера.
                  • +5
                    На локалхосте?
                    • 0
                      Не только. Хоть автор и пишет, что подобное он проделывает на локальном ПК, но это актуально и для виртуального хостинга, например, когда приходится наблюдать тормоза сайта, а тп отвечает, что с их стороны всё ок и ресурсов достаточно. Логирование MySQL размеется не ведётся и включить его возможности нет. Переносить же сайт на локальный ПК не всегда есть возможность, а консоль всегда со мной. Мне это удобней.
                • +4
                  а что в бинари логи стали писать селекты?
                  • 0
                    А я так понимаю что это работает только при использовании mysql/mysqli? А как же PDO?
                    • +1
                      Это работает между клиентом и сервером. Т.е. неважно что там на клиенте — mysql, mysqli или PDO.
                      • –2
                        Вы в этом уверены или предполагаете? Настроки
                        mysql.default_port и mysqli.default_port не просто так, а такой настройки для pdo — нет.
                        Я просто уже успел проверить, WP видимо юзает mysql, т.к. стал отображаться только после установки mysql.default_port = 4040, остальные мои скрипты, которые работают через PDO — ничего не показывают :(
                        • +1
                          Я в этом уверен.
                          mysql.default_port это лишь номер порта по умолчанию, когда в параметре $server функции mysql_connect передается только имя хоста.
                          При создании экземпляра PDO можно также указать порт сервера в параметре $dsn.
                          Вообще это все немного разные уровни. Можно сказать, что mysql-proxy работает на более низком уровне. В конечном итоге что делают libmysql, mysqlnd и встроенный драйвер PDO? Устанавливают tcp-соединение с указанным сервером: портом, обмениваются данными через это соединение, соблюдая определенный протокол. В случае использования mysql-proxy они коннектятся к нему, передают теже самые данные, ничего не зная про прокси, они уверены, что общаются с сервером. mysql-proxy в свою очередь знает этот протокол и позволяет отобразить передаваемые данные, передает их серверу, возвращает клиентам ответы сервера.
                          • +1
                            Вы правы, у PDO своя политика. Отсюда 3 решения.
                            1) Размещаем на 3306 порту сам mysql-proxy (--proxy-address=3306 в параметрах запуска), сам MySQL вешаем на альтернативный порт, например 3307 и настраиваем proxy на адрес --proxy-backend-addresses=host:3307
                            2) При подключении PDO явно указываем порт $db = new PDO('dblib:host=your_hostname:4040;dbname=your_db;charset=UTF-8', $user, $pass);
                            3) Для мейнстрима: перенаправлять запросы с помощью чего-нибудь вроде iptables на 4040 порт
                            • 0
                              Попробовал 1 и 2 варианты — результат нулевой, к сожалению. Уж очень сильно хочется проверить своё приложение :(
                              • 0
                                В PDO есть то ли фича, то ли баг, что если в адресе сервера указан localhost, то соединение идет через unix socket, а не TCP. Лечится заменой localhost на 127.0.0.1
                                • 0
                                  О, вот это ценное замечание, вчера уже видел что оно не умеет через unix socket, попробую, спасибо!
                            • 0
                              Это просто автор привел привер. Сам прокси работает на стороне сервера как прокси — если вы у себя в подключении в PDO поменяете порт, все заработает
                        • 0
                          промазал
                          • 0
                            То что нужно. Если lua поддерживает регулярные выражения то можно более гибко выполнять логирование.
                            • 0
                              slow_query_log
                              slow_query_log_file=/var/log/mysql/slow_mysql.log
                              long_query_time=0
                              будет писать все запросы
                              потом можно легко проанализировать с помощью pt-query-digest
                              • +1
                                А тут мы можем фильтровать, что писать, а что нет. Да и вообще извращаться над тем, что писать как угодно.
                              • 0
                                оригинальное использование mysql-proxy
                                зачет
                                • +1
                                  Ай да Иван Ай да мужик.. Я как раз занимаюсь рефакторингом одной системы. Думал чтобы было бы круто увидеть что да как не лазив в исходники PHP…
                                  • 0
                                    Если кто-то зарегистрирован на форуме open-server.ru, предложите в пожеланиях добавить эту утилиту. Не могу создать акк, за… достала каптча, Максим Архипов наверное на ней зарабатывает.

                                    UPD: Хотя выше дали ссылку как «включить логирование на стороне MYSQL»…

                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.