MySQL Replication

Репликация MySQL: Организация и настройка

Репликация MySQL позволяет синхронизировать данные между несколькими БД для повышения эффективности, безопасности и скорости работы. СУБД MySQL поддерживает репликацию "из коробки", но перед её использованием каждый раз требуется конфигурировать параметры базы. Программа Handy Backup обеспечивает репликацию MySQL с помощью последовательно созданных задач бэкапа и восстановления базы.

Small Windows LogoСкачать Handy Backup

Версия 8.1.3 от 6 августа 2020. 106 MB

Обеспечение репликации данных MySQL

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

  • MySQL репликация Master-Master для СУБД MySQL
    До того как произвести репликацию, вы можете переместить базы данных основного сервера на физический или виртуальный сервер, поддерживающий работу в подчинённом (Slave) режиме с копией БД.
  • Обеспечение для MySQL Master-Slave репликации сервера
    Для увеличения производительности вашего веб-сайта или приложения рекомендуется установить несколько подчинённых (Slave) баз данных: это увеличивает доступность и скорость доступа к данным за счёт использования другого механизма хранения данных.
  • Сохранение важных данных на подчинённых (Slave) серверах
    Важно понимать, что репликация MySQL не может заменить регулярного резервного копирования: эффективным путём здесь является комбинирование обоих подходов – резервное копирование данных со slave-серверов MySQL.

Организация репликации MySQL

При конфигурировании репликации MySQL в режиме mater-slave важно понимать принципы работы соответствующего механизма. В MySQL существуют два основных механизма репликации данных:

  • Операторная репликация (Statement-based replication) записывает все запросы SQL, сделанные к главной (Master) БД, и воспроизводит их на подчинённых (Slave) серверах. Этот метод быстр, но не очень точен: например, если вы вставляете в поле таблицы случайное значение, полученное с помощью функции MySQL RAND(), результат, хранимый в разных копиях БД, будет различаться.
  • Построчная репликация (Row-based replication) подсчитывает все изменения в главной (Master) БД и автоматически изменяет записи в таблицах подчинённой (Slave) БД всякий раз при обнаружении новых данных. Этот подход значительно медленнее, чем операторная репликация MySQL, но предоставляет высокую мобильность данных и намного эффективнее влияет на производительность Slave серверов.

Как можно заметить, оба типа репликации предполагают, что у вас есть две версии БД, которые поддерживаются в синхронизированном состоянии. С Handy Backup вы можете легко синхронизировать ваши базы данных, сохранив их на главном (Master) сервере и восстановив на Slave-сервере. Узнать подробнее о бэкапе MySQL.

Конфигурирование дополнительных (Slave) баз данных MySQL

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

Конфигурирование репликации MySQL

Конфигурирование репликации MySQL

В базовой комплектации серверы MySQL поставляются с несколькими утилитами хранения данных, самые популярные из которых – MyISAM и InnoDB.

  • MyISAM – быстрый и нетребовательный к ресурсам алгоритм, но он не поддерживает транзакции и имеет блокировку доступа на уровне таблиц. До версии MySQL 5.5 это был основной механизм хранения данных. Его основной недостаток в том, что он не позволяет производить множество одновременных операций при обновлении таблиц.
  • InnoDB – безопасный по транзакциям механизм хранения, поддерживающий блокировку на уровне отдельных строк, подтверждения и откаты транзакций, кэширование индекса и многие другие полезные функции. Во многих аспектах он превосходит по производительности все остальные механизмы хранения данных СУБД MySQL.

Конфигурирование репликации Master-to-Slave подразумевает, что вы собираетесь использовать БД на Slave-серверах только для чтения. Для этой цели MyISAM привлекательнее, поэтому ваши Slave-серверы должны использовать именно его.

Схема репликации MySQL с основного на починенный сервер

Схема репликации MySQL с основного на подчиненный сервер

Конфигурирование Slave-серверов в Handy Backup может производиться так:

  1. Плагин MySQL создаёт набор дамп-файлов, по одному на каждую таблицу MySQL. Каждый дамп-файл содержи все SQL-запросы, необходимые для создания и заполнения данными каждой конкретной таблицы.
  2. Формат дамп-файлов легко читается и модифицируется вручную, как текстовые файлы. Найдите оператор CREATE TABLE в начале каждого файла и измените параметр ENGINE=InnoDB на ENGINE=MyISAM.
  3. Теперь вы можете восстановить БД из дамп-файлов на Slave-сервере.
Small Windows LogoСкачать Handy Backup

Версия 8.1.3 от 6 августа 2020. 106 MB

Важно запомнить или записать, какие механизмы хранения использовались в оригинале в таблицах Master-сервера. Если Master-сервер упадёт, вам будет необходимо восстановить данные на нём в обратном порядке (детали описаны в нижеприведённом разделе "Проблемы восстановления").

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

Настройка резервного копирования на Slave-сервере

Общая проблема резервного копирования MySQL заключается в том, что процесс бэкапа отрицательно влияет на производительность. В этом случае репликация MySQL весьма выгодна: она не только разгружает основную БД, но также ускоряет и упрощает резервное копирование.

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

Резервное копирование MySQL на Slave-сервере

Резервное копирование MySQL на Slave-сервере

Проблемы восстановления

Восстановление баз данных MySQL в режиме репликации Master-to-Slave может представлять известные трудности. Эта операция отличается от типичного восстановления MySQL тем, что работает с множеством БД вместо одной.

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

Если Slave-сервер подвергся аварийной остановке, вам необходимо просто повторить на нём настройку БД в Slave-режиме, описанную ранее. Сделайте копию базы данных на Master-сервере, смените механизм хранения, восстановите БД на Slave-сервере и разрешите репликацию; этого будет достаточно.

Восстановление Master сервера

Гораздо сложнее восстановление в случае аварии или падения Master-сервера. Во многих случаях вы не сможете использовать ваши БД в режиме "только для чтения", поэтому просто переключите один из Slave-серверов в режим Master. Чтобы сделать это, выполните следующие операции:

  1. Выберите Slave-сервер MySQL для перевода в Master-режим.
  2. Используйте операторы STOP SLAVE и RESET MASTER, чтобы запустить выбранную базу в автономном режиме.
  3. На всех остальных Slave-серверах используйте оператор STOP SLAVE IO_THREAD, чтобы завершить необработанные операции синхронизации с предыдущим Master-сервером.
  4. Далее используйте оператор CHANGE MASTER TO с верными параметрами, и выполните операцию START SLAVE, чтобы запустить процесс репликации с нового Master-сервера на Slave-серверы.

Внимание! Использование Slave-сервера в качестве Master-сервера является временной мерой, так как механизм хранения данных MyISAM оптимизирован для чтения и очень медленно исполняет операции изменения и добавления данных. Поэтому рекомендуется восстановить функциональность предыдущего Master-сервера так быстро, как только возможно.

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

Совет: Для эффективного сохранения копий MySQL попробуйте наше ПО – скачайте Handy Backup прямо сейчас!

  1. Модифицируйте дамп-файл, созданный программой, изменив механизм хранения данных в таблицах на тот, что использовался в базе данных Master-сервера.
  2. Выполните процедуру восстановления.
  3. Повторите шаги 1-4, как если бы Master-сервер (предыдущий Slave) упал и у вас возникла бы необходимость установить восстановленную БД в качестве новой Master.

Информация о продукте

Для резервного копирования, восстановления и репликации баз данных MySQL вам понадобится одно из решений Handy Backup, предназначенных для использования в коммерческих целях:

Handy Backup Office Expert

Решение Office Expert подходит для репликации базы MySQL на одном сервере.

Стоимость: 9200 ₽

Handy Backup Server Network

Решение поддерживает репликацию баз данных с несколькими компьютерами, на которых установлен сервер MySQL. Версия Server Network содержит Панель Управления и один Сетевой Агент для сервера.

Стоимость: 37000 ₽

Кто использует наше решение для резервного копирования?