Архив метки: mysql

Логирование всех запросов к mysql

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

Для этого необходимо подключиться к нашему серверу и:

  1. Создать файл лога и выдать на него права для пользователя, под которым работает mysql.
  2. Подключиться к mysql-серверу с правами root и выполнить:
    SET GLOBAL log_output = "FILE";
    
    SET GLOBAL general_log_file = "/path/to/your/logfile.log";
    
    SET GLOBAL general_log = 'ON';
  3. С этого момента в лог попадают абсолютно все запросы. Проверяем необходимый нам функционал.
  4. Если сервер довольно загружен — не забываем сразу выключить логирование:
    SET GLOBAL general_log = 'OFF';
  5. Открываем лог-файл и ищем там наши запросы.

Блокирование процесса MySQL-сервера в оперативной памяти. Memlock на FreeBSD.

Бывают случаи, когда надо запретить операционной системе сервера переносить данные определенного сервиса/приложения в swap. Т.е. все остальное можно, хоть и нежелательно, а вот какое-нибудь одно ну совсем нельзя. Для этого во всех (ну или почти во всех) современных unix системах существует системный вызов memlockall — он отвечает как раз за «блокировку» данных в оперативной памяти. Можно, конечно, долго рассуждать о том, что если swap на сервере становится нужен — пора апгрейдить сервер/снижать нагрузку на него. Так и обстоят дела в идеальном мире.

Но наш мир далек от совершенства и в ПО, в том числе системном, тоже бывают ошибки. Я столкнулся с одной пренеприятнейшей ситуацией на сервере под управлением FreeBSD 9.2, с корнем на ZFS. Ситуация состояла в том, что система никак не хотела освобождать память из-под ARC-кэша, когда она была необходима приложениям и предпочитала сбрасывать данные других приложений в swap. Все бы ничего, но самым «жрущим» процессом всегда оказывался MySQL, половину которого она и умудрялась выдавить в swap.

А потом начиналось самое интересное: в те лохматые времена, когда устанавливался этот сервер, про размещение swap на zfs никто ничего плохого не говорил. А потом выяснили, что внезапно при активном чтении/записи из свапа (например, при внезапно возросшей нагрузке с истребованием данных mysql, которые упали в свап и вытеснением других приложение туда же) может возникнуть deadlock. Выглядит это очень весело, но не в том случае, если сервер не имеет удаленного управления и стоит в далеком дата-центре, в который надо ехать, да еще и согласовав свой приезд с сотрудниками заранее. С виду сервер остается живым — отвечает на пинги, принимает пакеты на открытые порты, но не отдает никаких данных. При подключении клавиатуры бодро выкидывает в консоль сообщение о подключении девайса, но не реагирует на нее и, более того, не пишет никакие логи. Помогает только reset.

Если у вас действительно не хватает памяти на сервере, то установка «—memlock» не поможет и будет даже вредной. При исчерпании свободной памяти и отсутствии возможности «вынести» кого-нибудь в swap, система с большой долей вероятности просто убьет самый объемный процесс, которым окажется как раз mysql

Впрочем, я отвлекся. Целью этого повествования является как раз способ принуждения системы «убрать руки» от памяти, выделенной для MySQL. Для этого существует параметр запуска —memlock. Первое решение, которое приходит в голову — прописать в /etc/rc.conf строчку:

mysql_args="--memlock"

Прописываем, перезапускаем mysql и видим (с помощью запроса show variables like ‘%lock%’), что переменная locked_in_memory выставлена в OFF.

Начинаем думать и читать мануалы… Оказывается, для успешного выполнения вызова memlockall (который и выполняется при указании параметра —memlock), необходимы root-привилегии. Но мы же не хотим, чтобы сервер работал от имени root? В случае с FreeBSD без редактирования стартового скрипта mysql, к сожалению, не обойтись — пользователь ‘mysql’ туда «захардкожен» и его придется сменить ручками. Открываем /usr/local/etc/rc.d/mysql-server и вместо mysql_user=»mysql» вписываем mysql_user=»root». Далее обязательно удаляем из строки:

command_args="-c -f /usr/local/bin/mysqld_safe --defaults-extra-file=${mysql_optfile} --user=${mysql_user} --datadir=${mysql_dbdir} --pid-file=${pidfile} ${mysql_args}"

кусочек «—user=${mysql_user}». Не забываем, что в rc.conf у нас остался mysql_args=»—memlock». И, наконец, для того, чтобы пользователь после запуска сменился на mysql, указываем в файле /etc/my.cnf (по умолчанию его нет в системе) следующее:

[mysqld]
user=mysql

Важно указать это именно в /etc/my.cnf — этот файл читается первым, а mysql в целях безопасности применяет только первую указанную опцию user. После проделанных манипуляций перезапускаем mysql-server командой:

/usr/local/etc/rc.d/mysql-server restart

и проверяем переменную «locked_in_memory» — она должна быть ON. Также, вы сразу заметите увеличение количества wired memory в выводе top на объем, занятый mysql.

В моем случае это проблему решило и, как ни странно, система перестала уходить в swap — память при необходимости отбиралась у ARC, хотя других изменений не производилось.

P.S. Главное не забыть об этих изменениях после обновления mysql — скрипт может быть перезаписан в процессе обновления.

Сброс пароля root в mysql без рестарта сервиса

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

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

1) Запускаем второй экземпляр mysql-сервера. Как это сделать, я писал ранее здесь. Разница только в том, что сейчас не надо копировать базу mysql. Необходимо подготовить конфиги и запустить команду

mysql_install_db --datadir=/dbdata/datadir/ --user=mysql

2) Логинимся на новый сервер под учеткой root.

3) Копируем таблицу user из базы данных mysql старого сервера на новый.

4) Заставляем сервер переоткрыть таблицы командой

flush tables;

5) Меняем пароль для пользователя root:

update mysql.user set password=PASSWORD('I-will-never-forget-root-password') where user like 'root';

6) Выполняем еще раз команду flush privileges.

7) Пробуем залогиниться повторно с новым паролем.

8) Если все ок, то останавливаем второй сервер (не основной!) и копируем файлы таблицы user из базы данных в обратном направлении (т.е. на основной сервер).

9) Осталось заставить сервер перечитать таблицу с пользователями. Это можно сделать, послав процессу сигнал SIGHUP. Делается это командой

kill -1 12345

где, 12345 — pid процесса mysqld.

Вот и всё, пароль изменен, сервер не выключался. Но все же, храните пароли где-нибудь в хорошо зашифрованном месте…

Восстановление данных из горячего InnoDB-бэкапа без перезапуска сервера

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

В случае с MyISAM-таблицами все обстоит совсем просто. Если сделать «flush tables with read lock», то их можно даже скопировать на файловом уровне. Хоть официально это и не поддерживается, но чаще всего работает «на ура». С InnoDB-таблицами все намного сложнее…

Для восстановления нам понадобится mysql-сервер той же версии, как и на «боевом» сервере. Если бэкапы делались при помощи снэпшотов и они еще не удалены с сервера, то на «боевом» сервере их восстановить будет даже проще — локально скопировать несколько файлов куда проще, чем доставать из архива и копировать по сети. Итак, для восстановления базы из горячего бэкапа в случае InnoDB-таблиц нам понадобится:

1) Копируем файл конфигурации с рабочего варианта (/etc/my.cnf в /etc/my2.cnf) и заменяем в нем: порт, который слушает mysql (3306) на любой другой (3307, например); сокет, который слушает mysql (/tmp/mysql.sock на /tmp/mysql2.sock); pid-файл (mysql.pid на mysql2.pid); директорию с файлами БД (/var/db/mysql на /var/db/mysql2).

2) Копируем в /var/db/mysql2 файлы из корня бэкапа (т.е. ib_logfile0, ib_logfile1 и др). Копируем из бэкапа директории performance_schema и mysql, а также директорию с базой данных, которую мы хотим восстановить.

3) Проверяем права на директорию /var/db/mysql2. Они должны быть такими же, как и на директорию /var/db/mysql.

4) Запускаем вторую копию mysql командой:

mysqld_safe --defaults-file=/etc/my2.cnf &

5) Запускаем mysqldump с необходимыми ключами (набор которых зависит от размера БД, количества памяти на сервере и т.д.). Если вы не знаете, какие ключи вам нужны, можете запускать его совсем без ключей. Главное — указать сокет:

mysqldump -S /tmp/mysql2.sock database_to_dump > /path/to/save/dump

6) Если дамп прошел успешно — гасим вторую копию mysql командой:

mysqladmin -S /tmp/mysql2.sock shutdown

После всего этого у нас будет дамп базы данных на руках. А с ним уже можно делать все, что душе угодно :)

Спасение сервера от медленных запросов mysql

Медленные запросы к mysql могут возникать по разным причинам: недостаточная производительность процессора, неоптимизированные запросы, криво настроенный сервер, большое количество «случайных» операций чтения и др. Иногда, во имя спасения всего сервера, легче «заставить» сервер отказаться от выполнения долгах запросов. К сожалению, mysql-сервер не умеет ограничивать максимальное время выполнения запроса. Поэтому приходится искать альтернативные пути. А именно, убивать запросы, которые выполняются дольше, чем мы хотим. Конечно, в таком случае работа веб-приложения завершится с ошибкой и, скорее всего, пользователь увидит HTTP 500, но бывают такие случаи, когда лучше отдать одному пользователю HTTP 500, чем всем остальным показать таймаут.

Итак, встречайте: очередной тул от percona — pt-kill! На FreeBSD ставится вот отсюда: /usr/ports/databases/percona-toolkit . Pt-kill — это такая маленькая, но полезная утилитка, которая умеет опрашивать mysql-сервер раз в n секунд и убивать медленные запросы. В общем-то, на этом ее функционал не ограничивается, но в данный момент нас интересует именно эта часть.

Для начала, можно проверить работу pt-kill в «тестовом» режиме. Вместо завершения соединений, он будет просто показывать те запросы, которые подходят под его условия. Для этого надо запустить его примерно такой командой:

pt-kill --busy-time 60 --print

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

И, если вы все же уверены в том, что все эти запросы необходимо убивать, можете запустить его следующим образом:

pt-kill --busy-time 60 --print --kill

В таком режиме он будет выводить на экран и убивать по одному медленному запросу за один раз (самому «старому»). Если надо убивать их все сразу (допустим, одновременно их появилось 10 штук), надо указать ключ —victims all. И, наконец, если вы хотите, чтобы он работал в фоне, смело убирайте —print и добавляйте —daemonize.

pt-kill --busy-time 60 --kill -- victims all --daemonize

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