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

MySQL Replication

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

Программа Handy Backup обеспечивает репликацию MySQL с помощью последовательно созданных задач бэкапа и восстановления базы.

Скачать Handy Backup

Версия 8.1.1 от 16 декабря 2019
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.

Handy Backup Office Expert

Версия 8.1.1 от 16 декабря 2019. 106 MB
Программа резервного копирования Handy Backup. 9200 RUB за лицензию

Handy Backup Office Expert

Решение Office Expert идеально подходит для репликации баз данных MySQL. Бесплатная полнофункциональная пробная версия - 30 дней!

Конфигурирование дополнительных (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-сервере.
Скачать Handy Backup

Версия 8.1.1 от 16 декабря 2019
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 ₽

Видеоурок

В нижеприведённом видео вы можете изучить, как правильно сохранять и восстанавливать базы данных MySQL, используя стандартную Windows-версию ПО HandyBackup. Это видео не посвящено непосредственно репликации серверов MySQL, но иллюстрирует общие принципы работы.

Внимание: для выполнения видеоурока, пожалуйста, скачайте и установите Handy Backup на свой компьютер!

Скачать Handy Backup

Версия 8.1.1 от 16 декабря 2019
106 MB

Handy Backup — надёжное и удобное решение для репликации ваших баз данных MySQL!
Попробуйте бесплатно полнофункциональную пробную версию в течение 30 дней!

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