Как массово убить запросы в MySQL?

mysqladmin pr | grep 'SELECT a.id, a.name' | awk '{print $2}'  | xargs -n1 mysqladmin kill

Как пропустить позицию в бинарном логе при репликации в MySQL

Для того, чтобы пропустить текущую позицию обработки релей-лога на слейве в MySQL, необходимо остановить слейв (STOP SLAVE), задать, сколько позиций (команд) необходимо пропустить (например, одну: SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;) и запустить слейв заново (START SLAVE).

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

Ускорение перезапуска MySQL

Столкнулись с долгим временем перезапуска MySQL сервера. Приходится ждать минут 10 а то и более. Хорошего тут вообще ничего, поскольку в это время приложения недоступно.
Как выяснилось, для InnoDB движка MySQL перед перезагрузкой сбрасывает буфер изменений на диск, что занимает значительное время.
Можно и принудительно завершить работу базы даже в момент синхронизации буферов через kill -9. При этом данные не потеряются, поскольку будут восстановлены из бинарного лога при следующем запуске сервера. Но все же существует способ значительно ускорить перезагрузку MySQL сервера.
mysql> set global innodb_max_dirty_pages_pct = 0;
Данной командой мы сообщаем серверу, что необходимо свести к минимуму кол-во не синхронизированных страниц буффера.
После этого нужно ждать и мониторить значение этой переменной, например в консоли:
$ mysqladmin ext -i 2 | grep dirty
В идеале, нужно дождаться нуля.
Следует заметить, что чем больше у вас размер буфера (innodb_buffer_pool_size), тем больше будет "грязных" страниц.

Как очистить файлы бинарного лога в MySQL

Для очистки таких файлов существует специальная команда PURGE BINARY LOGS.

Она имеет два формата:

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

В первом случае удаляются все файлы, до указанного. В примере mysql-bin.010 удален не будет.

Во втором удаляются файлы до определенной даты

Таким образом можно существенно освободить место в разделе, где храняться эти файлы. Бывает неоходимо в том случае, если нужно сделать ALTER TABLE или REPAIR TABLE над большой таблицей.

Восстановление репликации после сбоя

Если команда не может быть выполнена на слейве или же выполнена с ошибкой, то репликация приостанавливается. Бинарный лог на мастере при этом продолжает вестить. Более того, этот бинарный лог передается slave-базе данных, которая записывает его в так называемый relay-log. Таким образом, на слейв передаются те запросы, которые должны быть на нем выполены, но реально они не выполняются, а только "складируются".

Гарантией того, что слейв продолжает получать бинарный лог со стороны мастера служит то, что обновяется поле Read_Master_Log_Pos в выборке SHOW SLAVE STATUS. И это значание должно быть равным значению Position в выборке SHOW MASTER STATUS на мастере.

Командой SHOW SLAVE STATUS можем посмотреть ошибку и запрос, который ее вызвал, название файла relay-лога и его позицию в момент возникновения ошибки. Например:

Relay_Log_File: localunix-relay-bin.000012
Relay_Log_Pos: 251

Смотрим файл:
mysqlbinary localunix-relay-bin.000012 | less

и находим строку
# at 251

Чуть ниже будет видна команда, которая вызвала ошибку. После устранения причины возникновения ошибки, даем команду START SLAVE, и наш слейв возобновит обработку relay-лога с 251-ой позиции.

Если, например, мы не хотим выполнять текущую команду, а "перепрыгнуть" через нее, тогда нужно остановить слейв - STOP SLAVE, воспользоваться командой CHANGE MASTER TO RELAY_LOG_POS=$position; и запустить слейв заново - START SLAVE; Значение $position - это следующая позиция в релей-бин-логе на слейве.

Кроме того, содержимое binary/relay лога можно напрямую послать на выполнение mysql серверу:

shell> mysqlbinlog log_file | mysql -h server_name

Важно: Команда CHANGE MASTER удаляет все накопленные на слейве relay лог файлы и создает новые, если не указаны опции RELAY_LOG_FILE или RELAY_LOG_POS.

Управление репликацией в MySQL

Статус slave сервера отображается командой SHOW SLAVE STATUS;
Ведение бинарного лога ведется с самого старта сервера. Можно отключить ведение бин. лога командой SET sql_log_bin=0; (полезно для временного отключения, чтобы SQL команда не попала на слейвы). Запуском и остановкой slave-режима можно упралять с помощью команд START SLAVE и STOP SLAVE.

После команды STOP SLAVE, слейв сервер перестает принимать бинарный лог с мастера и любая статистика, которая видна по команде SHOW SLAVE STATUS, замораживается.
После команды START SLAVE, с мастера подтягивается весь бинарный лог начиная с позиции, которая была указана в поле Read_Master_Log_Pos, и автоматически выполняются все полученные команды.

Содержимое бинарного лога можно просмотреть с помощью команды mysqlbinlog. Например: mysqlbinlog ./mysql-bin.000012. Эта консольная утилита поддерживает немало полезных опций. Файлы бинарных логов постоянно ротируются, и для того, чтобы посмотреть текущий файл в который происходит запись, нужно воспользоваться SQL командой SHOW MASTER STATUS; помимо файла, в результате отобразится текущая позиция бинарного лога. Все бинарные логи на мастере можно увидеть с помощью команды SHOW BINARY LOGS;

Командой \h master в консоле MySQL можно увидеть все SQL команды, которые относятся к master-режиму.
Командой \h slave в консоле MySQL можно увидеть все SQL команды, которые относятся к slave-режиму.

Как работает репликация в MySQL

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

В бинарный лог попадают не только запросы на изменение данных в таблицах, но и такие, как CREATE, DROP, ALTER, TRUNCATE и .т.д. Таким образом создание, удаление, модификация таблиц, триггеров и хранимых процедур так же журналируются.
В момент добавления, содержимое бинарного лога отправляется slave-серверу, который просто выполняет очередной запрос (INSERT, UPDATE, ALTER и т.д.) у себя в окружении в соответствующей базе данных.

Одновременно одна база данных может быть подчененной (slave) только для одной master. Таким образом, чтобы получить MASTER-MASTER репликацию, нужно настроить обе как master и slave друг для друга.