Category Archive Mysql

Автор:human

Mysql репликация и переключение на новый мастер

Настройка репликации MySQL, аварийное переключение slave->master

  • Увеличение производительности СУБД путем подключения к ней серверов для адаптации к возрастающей нагрузке. Производительность растет за счет выделения одного сервера преимущественно для модификации данных (master) и остальных для чтения данных (slaves). Данное решение особенно эффективно для веб-приложений: у данной категории приложений большая часть запросов к СУБД — запросы на чтение.
  • Онлайн бэкап — данные передаются в режиме онлайн на резервные/slave-сервера. При отказе основного сервера на одном из резервных/slave серверов имеется «свежая» копия данных.
  • Организация эффективного резервного копирования: бэкап делается с резервного сервера без прерывания /замедления работы основного сервера СУБД. При необходимости для осуществления целостного бинарного бэкапа СУБД (в особенности InnoDB) можно остановить на необходимое время резервный сервер, выполнить бэкап и запустить резервный сервер снова.
  • Обеспечение высокой доступности: при выходе из строя одного из серверов СУБД система продолжает обрабатывать запросы.

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

 

Надежность репликации

Для обеспечения максимальной надежности репликации рекомендуется установить параметры MySQL следующим образом:
innodb_flush_log_at_trx_commit = 1

Примечание: Установка именно таких параметров может привести к общему снижению производительности системы.

Для повышения производительности можно использовать такие параметры (чревато потерей данных нескольких транзакций в момент аварии на базе данных):

Динамический hostname

Если у настраиваемого сервера динамический IP-адрес/hostname, рекомендуется явно задать параметры:

Привилегии

Для работы репликации учетные записи основного и резервных master/slave-серверов должны иметь, кроме стандартных, также привилегии:

Учетные записи

Временные зоны

Если сервера СУБД кластера расположены в разных дата-центрах, необходимо настроить на них единую временную зону.

 

Администрирование репликации

Репликация после настройки работает надежно и требует минимального администрирования. Тем не менее, рекомендуется периодически проверять ее состояние утилитами мониторинга операционной системы (nagios, zabbix, monit, linux-ha).

В маловероятном случае возникновения ошибки на slave-сервере рекомендуется его переинициализировать — заново залить на него данные с основного сервера. Для этого нужно его Прекратить использовать, а затем Начать использовать в разделе Репликация (Настройки > Веб-кластер > Репликация).

Резервное копирование

Можно свободно останавливать slave-сервера, в т.ч. для осуществления логического и целостного бинарного резервного копирования средствами MySQL и операционной системы. При этом не прерывается работа основного сервера СУБД.

 

Переключение slave->master в случае отказа master

В случае отказа основного (master) сервера СУБД, необходимо вручную или автоматически скриптом переключить кластер на другой master-сервер СУБД. Для этого обычно slave-сервер, хранящий последние реплицированные данные, переводят в режим основного.

Общая схема этой процедуры такова:

  1. Закрываем доступ клиентов к веб-приложениюЕсли используется двухуровневая конфигурация (фронтэнд nginx — бэкэнд apache и т.п.), рекомендуется на фронтэнде отключить доступ к бэкэнду (веб-приложению) и отдавать при обращении клиентов к кластеру информационную страницу о регламентных работах.
  2. Останавливаем на всех slave-серверах поток получения обновлений бинарного лога с основного (master) сервера:

    Ждем, пока от потока выполнения команд slave (SQL_THREAD) не появится сообщение "Has read all relay log; waiting for the slave I/O thread to update it", говорящее о том, что slave-сервер выполнил все команды из relay-лога в своей базе. Сразу останавливать slave командой STOP SLAVE не рекомендуется, т.к. не все SQL-команды могут быть выполнены из relay-лога (по причине отставания и т.п.), а при переключении slave на новый мастер, relay-лог будет очищен и, возможно, потеряется часть «непроигранных» данных.
  3. Подготовка нового master-сервераУбеждаемся, что на slave-сервере, который мы хотим сделать master-сервером, бинарный лог ведется и не логируются запросы из master:

    Полностью останавливаем slave — потоки чтения бинарного лога и выполнения SQL-команд:

    Команда RESET MASTER необходима для очистки бинарного лога нового master, иначе, если в бинарном логе будут записи (устаревшие и т.п.), они проиграются на подключаемых к нему slave-серверах. Такое возможно, если сервер был master с включенным бинарным логом, потом стал slave и перестал использовать бинарный лог, потом снова переводится в режим master.
    Итак, новый master подготовлен, у него очищен бинарный лог, и он готов обрабатывать запросы.
  4. Переключение slave-серверов на новый master-серверНа всех slave-серверах выполняем:

    В момент выполнения на slave

    очищается

    slave-сервера, а позиция c которой читается бинарный лог master-сервера устанавливается, если не задано иное, в значение по умолчанию: первый файл бинарного лога, 4 позиция (т.е. в самое начало бинарного лога master-сервера).

  5. Переключаем веб-приложение на новый master-серверНеобходимо настроить кластер на использование нового master-сервера.
  6. Открываем доступ клиентов к веб-приложениюЕсли используется двухуровневая конфигурация (фронтэнд nginx — бэкэнд nginx+php-fpm и т.п.), на фронтэнде убираем информационную страницу о регламентных работах и переключаем запросы на бэкэнд (веб-приложение).В итоге кластер использует новый master-сервер.

Еще один способ переключения slaves на нового master

На мастере

На слейве 1

Optional: CHANGE MASTER TO MASTER_HOST=»;

На слейве 2

Автор:human

О работе с функцией GROUP_CONCAT.

О работе с функцией GROUP_CONCAT.

Помнится меня на собеседовании в одну компанию спросили, как получить данные при группировке в строку…. ответа я не знал, конечно в тот же вечер я все узнал и понял как это легко))

Недавно мне надо было написать запрос как раз с использованием этой функции, и решил написать об этом статью.

И так, представим две таблицы, author(авторы) и books(книги авторов).

Заполним данные, ниже представлены две эти таблицы:

Таблица авторов:

Заполняем таблицу:

Заполняем таблицу:

Сделаем выборки :

Обратите внимание, во второй выборке я специально добавил дублирующееся поле (Шерлок Холмс) и также поле с NULL записью, для чего это я сделал объясню позже.

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

В итоге мы получил все книги по всем авторам:

А мне бы получить записи авторов, т.е. две записи, в нашем случае, и чтобы в поле books были все книги автора…

Это можно сделать применив как раз ф-цию GROUP_CONCAT().
Схема ф-ции:

Для того, чтобы расписать каждую из команд, я напишу пример:

Как видно после использование этой ф-ции я получил то, что хотел — дву строчки и в поле books все книги автора.

Теперь распишем каждую команду.
Начну с того, что группировке поддается только поле отличные от NULL, т.е. записи с содержимыми типа NULL не будет в поле books.

DISTINCT — позволяет выбрать не повторяющиеся значения, т.е. книга с дублирующим названием будет откинута, для этого я ввел дважды название книги Шерлок Холмс;
ORDER BY col_name ASC | DESC — позволяет сортировать данные;
SEPARATOR — позволяет разделить данные через нужный вам делитель, по-умолчанию это запятая «,» (без кавычек)

Также эта функция относится к групповым функциям, поэтому использование её без оператора GROUP BY не желательно, иначе в нашем примере получим одну строку с первым автором в таблице и всеми книгами которые есть в таблице книг.

Есть еще одно ограничение. Кол-во символов которые будут отображены в сгруппированном поле по-умолчанию 1024, если надо больше, то использую переменную group_concat_max_len устанавливаем своё значение:
синатксис:

пример:

Автор:human

Создание триггера на удаление данных из таблицы

Создание триггера на удаление данных из таблицы.

Создадим тестовую таблицу, на события в которой будеи реагировать.

Создадим таблицу, в которую будем писать изменения на удаления.

Создадим триггер на удаление из таблицы

Пробуем создать и удалить запись из таблицы

Удаление триггера

Для удаления триггера используется, как обычно оператор DROP, пример

где
schema_name — название БД,
trigger_name — название триггера

Список созданных триггеров

Показать триггер можно с помощью команды

Это полная часть команды, в основном пользуются командами:

Изменение триггера

А вот команды по изменению триггера я не нашел и был удивлен, почитал форумы, так и есть — её просто нет ((

Просмотр триггеров

или

Вот что мы увидем, когда выполним команду

Автор:human

Репликация MySQL без простоя

MASTER. Настройка репликации без простоя

Настройка конфигов

Рестартим mysql

Настройка slave server

Заливаем dump в пустую базу данных

Заходим в консоль mysql и запускаем репликация

Яндекс.Метрика