Юникс и другие

В продолжение прошлого поста про основы работы с MySQL Proxy по многочисленным просьбам аудитории публикуем продолжение :) На этот раз - более подробный разбор настройки и использования MySQL Proxy

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

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

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

• Передать его дальше неизмененным (по умолчанию)
• Исправить грамматические ошибки (CRATE DATAABSE, знакомо, не правда ли?)
• Отфильтровать запрос по неким правилам (или совсем отклонить его)
• Исправить запрос (например добавить более стойкий пароль или заполнить пустые значения)
• Добавить “забытые” значения (автокоммит включен, а пользователь послал запрос BEGIN WORK? Вы можете поместить перед ним SET AUTOCOMMIT = 0)
• И это далеко не все, полный список возможностей ограничен лишь вашей фантазией!

Также вы можете выполнять операции над результатами запросов:

• Добавлять, удалять и модифицировать записи в результате запроса. Хотите замаскировать пароль или скрыть конфиденциальные данные от неавтоизованного пользователя?
• Строить свои собственные result set’ы, включая имена столбцов. Например, если вы позволяете пользователю вводить его собственные SQL-команды, то вы можете показать предварительный результат
• Игнорировать результат, то есть ничего не отсылать клиенту
• Хотите большего? Ничего невозможного: смотрите примеры и экспериментируйте!

Ключевые понятия
MySQL Proxy построен на базе объектно-ориентированной архитектуры. Основной класс имеет три публичных метода и вы можете переопределить их в своих lua-сценариях.

• connect_server(): - вызывается при соединении, при помощи этого метода вы можете оперировать параметрами соединения. Именно этот метод используется при построении распределения нагрузки (load balancing)
• read_query(packet): - этот метод вызывается перед отправкой запроса на сервер. Вы можете изменить оригинальный запрос, добавить свои подзапросы или запросы или решить вообще ничего не отправлять на сервер, а отдать клиенту собственный result set (Например в ответ на SELECT * FROM big_table вы можете вернуть Таблица big_table содержит более 20 миллионов записей. Вы не забыли условие WHERE?)
• read_query_result(injection_packet) - эта функция выполняется перед отправкой результата запроса клиенту. Вы можете проигнорировать result set, изменить его или отправить клиенту неизмененным

Комбинация этих трех методов обеспечит вам прекрасную маневренность при управлении сервером (или серверами) MySQL

Установка
Установки Proxy предельно проста. Дистрибутив содержит в себе один бинарный файл (с версии 0.5.1 также несколько lua-скриптов для примера). Вы можете распаковать его и поместить туда, куда вам больше нравится. Или воспользоваться пакетным менеджером вашего дистрибутива (например, RPM или apt-get).
Если ваш дистрибутив не содержит готового пакета с Proxy или вы хотите поэксперементировать с опциями сборки, то вы можете получить исходные тексты из репозитория Subversion и собрать Proxy самостоятельно. Для этого необходимо выполнить несколько простых действий:

./autogen.sh
./configure && make
sudo make install
# will copy the executable to /usr/local/sbin

Простой перехват запроса
В качестве нашего первого примера давайте просто сделаем преобразование запроса с добавлением метки типа “Я здесь был” :)

1. Создайте lua-сценарий с листингом, приведенным ниже и сохраните его под именем first_example.lua
2. Предположим, что ваш сервер находится на той же физической машине, что и клиент
3. С другой консоли присоединитесь к вашему прокси-серверу и выполните какой-нибудь запрос (используйте порт 4040 вместо порта сервера 3306)

 -- first_example.lua
 function read_query(packet)
   if string.byte(packet) == proxy.COM_QUERY then
     print("Hello world! Seen the query: " .. string.sub(packet, 2))
   end
 end

# starting the proxy
$ mysql-proxy –proxy-lua-script=first_example.lua -D
# from another console, accessing the proxy
$ mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040 -e ‘SHOW TABLES FROM test’

Если мы вернемся к предыдущей консоли, то увидим, что прокси успешно перехватил запросы:

Hello world! Seen the query: select @@version_comment limit 1
Hello world! Seen the query: SHOW TABLES FROM test

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

Комментарий
До версии 0.5.0 для использования lua-сценариев, вы должны были запускать прокси с параметром –proxy-profiling, иначе функции read_query и read_query_result просто не работали. Начиная с версии 0.5.1 этот параметр не обязателен. Теперь эти опции включены по умолчанию. Напротив, если вы используете MySQL Proxy только для балансировки нагрузки, то рекомендуется стартовать прокси-сервер с опцией –proxy-skip-profiling

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

second_example.lua

 function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     print ("received " .. query)
     local replacing = false
     -- matches "CRATE" as first word of the query
     if string.match(string.upper(query), '^%s*CRATE') then
         query = string.gsub(query,'^%s*%w+', 'CREATE')
         replacing = true
     -- matches "SLECT" as first word of the query
     elseif string.match(string.upper(query), '^%s*SLECT') then
         query = string.gsub(query,'^%s*%w+', 'SELECT')
         replacing = true
     end
     if (replacing) then
         print("replaced with " .. query )
         proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
         return proxy.PROXY_SEND_QUERY
     end
   end
 end

Не забываем запустить прокси с параметром –proxy-lua-script=second_example.lua и присоединиться к нему из mysql-клиента:

$ mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 48
 Server version: 5.0.37-log MySQL Community Server (GPL)

 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 mysql> use test
 Database changed
 mysql> CRATE TABLE t1 (id int);         # Notice: TYPO!
 Query OK, 0 rows affected (0.04 sec)

 mysql> INSERT INTO t1 VALUES (1), (2);
 Query OK, 2 rows affected (0.01 sec)
 Records: 2  Duplicates: 0  Warnings: 0

 mysql> SLECT * FROM t1;                 # Notice: TYPO!
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 +------+
 2 rows in set (0.00 sec)

Великолепно! Я сделал свои типичные ошибки, но, тем не менее, запросы прекрасно выполнились.. Давайте посмотрим отчет

received select @@version_comment limit 1
received SELECT DATABASE()
received CRATE TABLE t1 (id int)
replaced with CREATE TABLE t1 (id int)
received INSERT INTO t1 VALUES (1), (2)
received SLECT * FROM t1
replaced with SELECT * FROM t1

Первые два запроса выполнил клиент.. А затем запросы с типичными ошибками были переписаны: CRATE был успешно изменен на CREATE, а SLECT превратился в SELECT
Это достаточно простой скрипт, но он демонстрирует большие возможности и безграничный простор для экспериментов!

“Инъекции” в запросы
Далее мы рассмотрим уникальный механизм внедрения данных в запросы: query-инъекции. По мере надобности proxy может создавать очереди запросов, и после присвоения уникального ID каждому запросу, отсылать их на сервер.

На картинке сервер получает три запроса и, конечно же, возвращает три result set’а. Когда мы используем механизм инъекций, обработку результатов берет на себя функция прокси: read_query_result, в которой вы можете сопоставить наборы данных с идентификаторами. Например, для ID 2 и 3 вы только извлекаете из значения и определяете влияние основного запроса на сервер. Посколько вы используете эти значения только для внутренних вычислений, вы не отсылаете их клиенту, и это, собственно, правильно, так как клиент их и не ожидает.

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

Макросы
Макросы - это лишь один из способов применения “переписывания запроса”, но они являются одним из самых ярких примеров использования proxy. Вы можете изменить язык SQL или адаптировать его “под свой вкус”. Например, многие предпочитают использовать в командной строке cd и ls, вместо USE db; и SHOW TABLES;. Используя MySQL Proxy это возможно! Давайте попробуем написать свой первый макрос.

Создание Result set’a: команды shell из mysql-клиента
Proxy получает запрос от клиента и возвращает набор данных, в большинстве случаев так и происходит. Передача запроса на сервер, получение набора данных, возврат результатов клиенту. Но что если мы должны вернуть данные, которые сервер не может нам предоставить? Мы должны самостоятельно построить результирующий набор данных, состоящий из имен столбцов и двумерного массива с данными.

Основы создания result set’а
Например, если вы хотите вернуть предупреждение об устаревшей команде, вы можете использовать result set наподобие нижеследующего:

 proxy.response.resultset = {
     fields = {
         {
            type = proxy.MYSQL_TYPE_STRING,
            name = "deprecated feature",
         },
         {
            type = proxy.MYSQL_TYPE_STRING,
            name = "suggested replacement",
         },
     },
     rows = {
          {
             "SHOW DATABASES",
             "SHOW SCHEMAS"
          }
     }
 }
 -- and then, send it to the client
 return proxy.PROXY_SEND_RESULT

В результате клиент получит следующий result set:

+---------------------+-----------------------+
| deprecated feature  | suggested replacement |
+---------------------+-----------------------+
| SHOW DATABASES      | SHOW SCHEMAS          |
+---------------------+-----------------------+

Как видите, мы можем построить любой, необходимый нам, результирующий набор данных. За примерами обращайтесь к Jan Kneschke

Системый командный интерпретатор в mysql-клиенте
А теперь нечто совсем другое! Давайте посмотрим, как можно использовать только что полученные нами знания для выполнения команд оболочки через прокси. Как говорилось выше (и как вы уже могли заметить :)) поведение прокси-сервера может быть изменено при помощи lua-скриптов. LUA является полноценным языком программирования, поэтому вы можете делать на нем все, что угодно. В том числе и исполнять shell-команды. Связав этот факт с возможностью построения собственных result set’ов мы пришли к тому, что неплохо было бы обращаться к оболочке напрямую из командной строки mysql-клиента, возвращая данные в виде классического result set’а

Наш сценарий будет использовать следующий синтаксис для выполнения команд оболочки:

SHELL command

Например:
SHELL ls -lh /usr/local/mysql/data

1. Загрузите учебный сценарий
2. Запустите прокси
3. Соединитесь с прокси

$ /usr/local/sbin/mysql-proxy –proxy-lua-script=shell.lua -D

# from a different console
$ mysql -U USERNAME -pPASSWORD -h 127.0.0.1 -P 4040

Убедитесь, что все работает нормально

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 49
 Server version: 5.0.37-log MySQL Community Server (GPL)

 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 mysql> use test
 Database changed
 mysql> show tables;
 +----------------+
 | Tables_in_test |
 +----------------+
 | t1             |
 +----------------+
 1 row in set (0.00 sec)

 mysql> select * from t1;
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 +------+
 2 rows in set (0.00 sec)

Отлично, обычные операции работают нормально. Теперь протестируем наши расширенные возможности:

 mysql> shell df -h;
 +--------------------------------------------------------+
 | df -h                                                  |
 +--------------------------------------------------------+
 | Filesystem            Size  Used Avail Use% Mounted on |
 | /dev/md1               15G  3.9G  9.7G  29% /          |
 | /dev/md4              452G  116G  313G  27% /app       |
 | tmpfs                 1.7G     0  1.7G   0% /dev/shm   |
 | /dev/md3              253G  159G   82G  67% /home      |
 | /dev/md0               15G  710M   13G   6% /var       |
 +--------------------------------------------------------+
 6 rows in set (0.00 sec)

Привет, шелл! :) Теперь вы можете работать намного эффективнее…

 mysql> shell grep key_buffer /usr/local/mysql/my.cnf;
 +-----------------------------------------+
 | grep key_buffer /usr/local/mysql/my.cnf |
 +-----------------------------------------+
 | key_buffer=2000M                        |
 +-----------------------------------------+
 1 row in set (0.00 sec)

Конечно, тот же результат можно было бы получить из запроса SHOW VARIABLES;, но наша задача - показать, что в клиенте mysql мы имеем полноценный shell!

 mysql> shell free -m;
 +---------------------------------------------------------------------------+
 | free -m                                                                   |
 +---------------------------------------------------------------------------+
 |              total       used       free     shared    buffers     cached |
 | Mem:          3280       1720       1560          0          9       1006 |
 | -/+ buffers/cache:        704       2575                                  |
 | Swap:         8189          2       8186                                  |
 +---------------------------------------------------------------------------+
 4 rows in set (0.08 sec)

Неплохо. Но все это лежит на нашем же сервере.. А как насчет работы с сетью? Для примера мы посмотрим последние 20 записей на Планете MySQL

   wget -q -O - http://www.planetmysql.org/rss20.xml  \
      | perl -nle 'print $1 if m{}’ \
      |head -n 21 | tail -n 20;

Да, длинновато немного, но это реально работает!

 mysql> shell last_planet.sh;
 +-------------------------------------------------------------------------------------+
 | last_planet.sh                                                                      |
 +-------------------------------------------------------------------------------------+
 | Top 5 Wishes for MySQL                                                              |
 | Open Source ETL tools.                                                              |
 | MySQL Congratulates FSF on GPLv3                                                    |
 | Query cache is slow to the point of being unusable - what is being done about that. |
 | About 'semi-unicode' And 'quasi Moon Stone'                                         |
 | My top 5 MySQL wishes                                                               |
 | Four more open source startups to watch                                             |
 | More on queue... Possible Solution...                                               |
 | MySQL as universal server                                                           |
 | MySQL Proxy. Playing with the tutorials                                             |
 | Open source @ Oracle: Mike Olson speaks                                             |
 | Quick musing on the "Queue" engine.                                       |
 | Distributed business organization                                                   |
 | Ideas for a MySQL queuing storage engine                                            |
 | MySQL Test Creation Tool Design Change                                              |
 | Queue Engine, and why this won' likely happen...                                    |
 | What?s your disk I/O thoughtput?                                                    |
 | My 5+ Wish List?                                                                    |
 | Top 5 best MySql practices                                                          |
 | Packaging and Installing the MySQL Proxy with RPM                                   |
 +-------------------------------------------------------------------------------------+
 20 rows in set (1.48 sec)

Доступ к командной строке и Internet из mysql-клиента… Впечатляет :)

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

Например:

 mysql> shell ls *.lua*;
 +---------------------+
 | ls *.lua*           |
 +---------------------+
 | first_example.lua   |
 | first_example.lua~  |
 | second_example.lua  |
 | second_example.lua~ |
 +---------------------+
 4 rows in set (0.03 sec)

 mysql> shell rm *~;
 Empty set (0.00 sec)

 mysql> shell ls *.lua*;
 +--------------------+
 | ls *.lua*          |
 +--------------------+
 | first_example.lua  |
 | second_example.lua |
 +--------------------+
 2 rows in set (0.01 sec)

Будьте осторожны!!!
Помните, что таким способом вы получаете доступ к командному интерпретатору хоста на котором запущен proxy, а не сервер баз данных. Это очень частая ошибка!!!

Выборочное логирование
Я оставил этот пример напоследок, поскольку (судя по моему опыту) он является наиболее интересным и даже имеет практическое применение! В MySQL 5.1 у вас есть возможность вести такие логи средствами самой СУБД, а если у вас версия 5.0, то proxy поможет вам :)
Для помещения запросов в общий лог-файл просто поместите следующий код в файл simple_logs.lua или скачайте его с MySQL Forge.

 local log_file = 'mysql.log'
 local fh = io.open(log_file, "a+")

 function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     fh:write( string.format("%s %6d -- %s \n",
         os.date('%Y-%m-%d %H:%M:%S'),
         proxy.connection["thread_id"],
         query))
     fh:flush()
   end
 end

Запустите прокси и соединитесь с ним из нескольких различных клиентов. Сценарий будет записывать запросы в файл с именем mysql.log После завершения всех сессий файл будет выглядеть примерно так:

 2007-06-29 11:04:28     50 -- select @@version_comment limit 1
 2007-06-29 11:04:31     50 -- SELECT DATABASE()
 2007-06-29 11:04:35     51 -- select @@version_comment limit 1
 2007-06-29 11:04:42     51 -- select USER()
 2007-06-29 11:05:03     51 -- SELECT DATABASE()
 2007-06-29 11:05:08     50 -- show tables
 2007-06-29 11:05:22     50 -- select * from t1
 2007-06-29 11:05:30     51 -- show databases
 2007-06-29 11:05:30     51 -- show tables
 2007-06-29 11:05:33     52 -- select count(*) from user
 2007-06-29 11:05:39     51 -- select count(*) from columns

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

Приятно, что вам не надо перезагружать сервер для включения логирования… Все, что необходимо, это перенаправить все запросы с порта 3306 (стандартного порта mysql-сервера) на порт 4040 (стандартный порт MySQL Proxy). И вам не нужно трогать сервер или какие-то приложения.. Одно просто правило для iptables поможет вам:

sudo iptables -t nat -I PREROUTING \
-s ! 127.0.0.1 -p tcp \
–dport 3306 -j \
REDIRECT –to-ports 4040

Теперь у вас включено логирование, а приложения-клиенты и сервер даже не заметили этого!! После того, как потребность в логировании отпадет просто удалите правило при помощи ключа -D и остановите прокси

sudo iptables -t nat -D PREROUTING \
-s ! 127.0.0.1 -p tcp \
–dport 3306 -j \
REDIRECT –to-ports 4040

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

Попробуем это реализовать. Это сценарий уже побольше предыдущего

 -- logs.lua
 assert(proxy.PROXY_VERSION >= 0x00600,
  "you need at least mysql-proxy 0.6.0 to run this module")

 local log_file = os.getenv("PROXY_LOG_FILE")
 if (log_file == nil) then
   log_file = "mysql.log"

 end

 local fh = io.open(log_file, "a+")
 local query = "";

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

 function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     query = string.sub(packet, 2)
     proxy.queries:append(1, packet )
     return proxy.PROXY_SEND_QUERY
   else
       query = ""

   end
 end

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

 function read_query_result (inj)
   local row_count = 0
   local res = assert(inj.resultset)
   local num_cols = string.byte(res.raw, 1)
   if num_cols > 0 and num_cols < 255 then
     for row in inj.resultset.rows do
       row_count = row_count + 1
     end
   end
   local error_status =""
   if res.query_status and (res.query_status < 0 ) then
       error_status = "[ERR]"

   end
   if (res.affected_rows) then
       row_count = res.affected_rows
   end
   --
   -- write the query, adding the number of retrieved rows
   --
   fh:write( string.format("%s %6d -- %s {%d} %s\n",
     os.date('%Y-%m-%d %H:%M:%S'),
     proxy.connection["thread_id"],
     query,
     row_count,
     error_status))
   fh:flush()
 end

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

 2007-06-29 16:41:10     33 -- show databases {5}
 2007-06-29 16:41:10     33 -- show tables {2}
 2007-06-29 16:41:12     33 -- Xhow tables {0} [ERR]
 2007-06-29 16:44:27     34 -- select * from t1 {6}
 2007-06-29 16:44:50     34 -- update t1 set id = id * 100 where c = 'a' {2}
 2007-06-29 16:45:53     34 -- insert into t1 values (10,'aa') {1}
 2007-06-29 16:46:07     34 -- insert into t1 values (20,'aa'),(30,'bb') {2}
 2007-06-29 16:46:22     34 -- delete from t1 {9}

Первая, вторая и четвертая строчки описывают, что сервер вернул, соотвественно, пять, две и шесть строк в ответ. Третья говорит об ошибке. В пятой строки мы видим, что команда UPDATE затронула две строки. Остальные строки описывают “последствия” команд INSERT и DELETE

Оригинал: http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html