Путеводитель по репликации баз данных / хабр

Наш пример

Рис. 01: Настройка репликации главного подчиненного устройства в MariaDB

В этой статье будут использоваться следующие IP-адреса серверов db1 и db2:

192.168.2.5 (db1) — главный сервер MariaDB
192.168.2.6 (db2) — подчиненный сервер MariaDB

Я собираюсь предположить, что оба db1 и db2 являются совершенно новыми серверами. На сервере нет существующей базы данных.

Что вам нужно для установки master-slave-кластера MariaDB?

Минимум два сервера (могут быть облачные или физические)
Частная сеть (LAN / VLAN) между серверами
VPN между двумя центрами обработки данных, если устанавливается между двумя IDC
Ubuntu Linux 16.04 LTS на обоих серверах

Типы репликации в SQL Server

Транзакционная репликация. Как говорит название, каждая транзакция или изменение данных в рамках транзакции на издателе будет посылаться подписчику почти в реальном времени с минимальной задержкой, зависящей от пропускной способности сети и ресурсов сервера. Транзакционная репликация использует агента читателя журнала для чтения изменения данных из журналов транзакций в базе данных издателя. Она также использует агента распределения для применения изменений на подписчике. Иногда может использоваться агент снимка для получения исходных данных снимка всех реплицированных статей. Публикация транзакционной репликации может подпадать под следующие категории:
- Стандартная транзакционная репликация — подписчик представляет собой базу данных только на чтение с точки зрения транзакционной репликации. Любые изменения, выполняемые кем угодно в базе данных подписчика не будут отслеживаться и обновляться в базе данных издателя. Стандартная транзакционная репликация часто называется просто транзакционной репликацией.
- Транзакционная репликация с обновляемыми подписками является расширением стандартной транзакционной репликации, которая отслеживает изменения данных, имеющих место на подписчике. Всякий раз, когда изменения происходят на обновляемой подписке, они будут сначала распространяться на издателя, а затем по другим подписчикам.
- Одноранговая репликация является расширением стандартной транзакционной репликации. Она распространяет транзакционно согласованные изменения почти в реальном времени по множеству экземпляров сервера.
- Двунаправленная репликация является расширением стандартной транзакционной репликации, которая позволяет двум серверам (ограничение только на 2 сервера — отсюда название «двунаправленная») обмениваться изменениями данных между собой с каждым из серверов, работающих как издатель (для пересылки изменений на другой сервер) или как подписчик (для получения изменений с другого сервера).
Репликация слиянием — поддерживает регистрацию изменений данных, происходящих как на издателе, так и на подписчике, и распространяет их на другой сервер. Репликация слиянием требует наличия столбца ROWGUID в таблицах статей, вовлеченных в репликацию слиянием. Она использует триггеры для захвата изменений данных на издателе и подписчике. Кроме того, доставляет изменения на серверы, когда оба и издатель, и подписчик, подключены к сети. Репликация слиянием использует агента слияния для репликации изменений данных на издателе и подписчике.
Репликация снимками — как говорит название, репликация снимками не опирается ни на журналы транзакций, ни на триггеры для регистрации изменений. Она берет снимок статей, участвующих в публикации и применяет его на подписчике с записями, имеющимися на время создания снимка. Репликация снимками использует агента снимка, чтобы взять снимок издателя, и использует агента распространения, чтобы применить эти записи на подписчике.

Установить MySQL

Репозитории CentOS 7 по умолчанию не включают пакеты MySQL, поэтому мы будем устанавливать MySQL из официального репозитория Yum. Чтобы избежать проблем, мы установим одну и ту же версию MySQL 5.7 на оба сервера.

Установите MySQL на главный и подчиненный серверы:

После завершения установки запустите службу MySQL и включите ее автоматический запуск при загрузке с помощью:

Когда сервер MySQL запускается в первый раз, для пользователя root MySQL создается временный пароль. Чтобы найти пароль, используйте следующую команду grep :

Запустите команду чтобы установить новый пароль root и повысить безопасность экземпляра MySQL:

Введите временный пароль root и ответьте (да) на все вопросы.

Новый пароль должен состоять как минимум из 8 символов и содержать как минимум одну заглавную букву, одну строчную букву, одну цифру и один специальный символ.

Шаг 4. Настройка главного сервера MariaDB.

Введите следующие команды на главном сервере db1. Вам нужно отредактировать файл /etc/mysql/my.cnf, запустите:

$ sudo vi /etc/mysql/my.cnf

1	$sudo vi/etc/mysql/my.cnf

Настройте клиент MariaDB (добавьте в раздел ) для использования SSL:

ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

1
2
3

ssl-ca=/etc/mysql/ssl/ca-cert.pem

ssl-cert=/etc/mysql/ssl/client-cert.pem

ssl-key=/etc/mysql/ssl/client-key.pem

Set bind address, то есть прослушивание только частного IP-адреса db1 192.168.2.5 (убедитесь, что вы заменили IP 192.168.2.5 на ваш реальный IP-адрес)

bind-address = 192.168.2.5

1	bind-address=192.168.2.5

Отметьте этот сервер как главный сервер. Это число должно быть уникальным:

server-id = 1

1	server-id=1

Убедитесь, что на сервере установлены binlogs, т.е. убедитесь, что log_bin и lob_bin_index установлены следующим образом:

log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index

1 2	log_bin=/var/log/mysql/mariadb-bin log_bin_index=/var/log/mysql/mariadb-bin.index

Настройте сервер MariaDB (добавьте в раздел ), использовать SSL:

ssl
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

1
2
3
4

ssl

ssl-ca=/etc/mysql/ssl/ca-cert.pem

ssl-cert=/etc/mysql/ssl/server-cert.pem

ssl-key=/etc/mysql/ssl/server-key.pem

Сохраните и закройте файл. Перезапустите сервер MariaDB, запустите:

$ sudo systemctl restart mysql.service

1	$sudo systemctl restart mysql.service

Настроить репликацию

Введите следующую команду на сервере master db1 :

$ mysql

$mysql

ИЛИ

$ mysql -u root -p

1	$mysql-uroot-p

Вы должны создать пользователя MySQL на главном сервере (db1). Синтаксис:

grant replication slave on *.* TO {username}@'{ip_of_db2_server}’ identified by ‘{password}’ REQUIRE SSL;

1	grant replication slave on .TO{username}@'{ip_of_db2_server}’identified by'{password}’REQUIRE SSL;

Например:

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘db_slave_usr’@’192.168.2.6’ IDENTIFIED BY ‘secretePassword’ REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
mysql> quit

1
2
3

mysql>GRANT REPLICATION SLAVE ON *.*TO’db_slave_usr’@’192.168.2.6’IDENTIFIED BY’secretePassword’REQUIRE SSL;

mysql>FLUSH PRIVILEGES;

mysql>quit

Узнать статус мастера

Введите в командной строке следующую команду:

$ mysql -u root -p

1	$mysql-uroot-p

После открытия оболочки MySQL введите:

mysql> SHOW MASTER STATUS;

1	mysql>SHOW MASTER STATUS;

Результат выполнения:

Рис. 03: узнать статус мастера и записать позицию

Запишите файл и данные о расположении подчиненного сервера с именем db2.

Настроить подчиненную базу данных

Измените файл конфигурации Slave Местоположение файла соответствует местоположению основного файла

Примечание: я использую yum для установки здесь, поэтому файл конфигурации по умолчанию находится в / etc. Рекомендуется создать резервную копию файла конфигурации при изменении двух вышеуказанных файлов. Измените следующую конфигурацию:

Примечание. Поле log_bin необходимо определять в соответствии с реальной ситуацией. Необходимо найти файл журнала базы данных. По умолчанию это / var / log.
Перезагрузите базу данных

перезапуск mysql:
Инициализировать базу данных
Скопируйте резервную копию базы данных с главного на подчиненный сервер
Восстановить данные резервной копии в подчиненную базу данных
Заставить подчиненного и главного установить соединение

Примечание. Значения lMASTER_LOG_FILE = ‘mariadb-bin.000002 и MASTER_LOG_POS = 1974 получены из приведенного выше SHOW MASTER STATUS. Пока можно выполнить репликацию главный-подчиненный. Давайте проверим.

Знать, что резервировать

Для правильного резервирования базы данных необходимо знать, где хранятся файлы данных, используются ли простые или сложные транзакции и где находятся журналы транзакций

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

Одинаковое представление новых и измененных данных для всех пользователей обеспечивается двумя разными способами, называемых моделями согласованности. Строгая согласованность гарантирует, что все пользователи одновременно видят одни и те же данные. Большинство реляционных СУБД следуют этой модели. Слабая согласованность (или «согласованность в конечном счете») гарантирует, что все пользователи будут видеть один и тот же атрибут, но лишь по истечении некоторого времени. Показательным примером может служить система доменных имен интернет (DNS). В базе данных, которая хранит доменные адреса, информация заменяется довольно долго, по мере того, как истекает время жизни имеющихся записей. Процесс может занять до 72 часов.

Если база данных распределена по сотне узлов, то получение ее согласованного снимка будет непростой задачей, как и восстановление. Слабо согласованная база данных реплицируется между многими узлами, так что она, на первый взгляд, не нуждается в резервном копировании. Однако, это не так: репликация защищает от выхода из строя узла, но не человеческой ошибки.

Несколько распространенных методов Redis Master-Slave репликации

Один хозяин и два слуги A (B, C), один хозяин и два раба
Проходящий огонь (децентрализованный) A-B-C, B является и главным узлом (главным узлом C), и подчиненным узлом (подчиненным узлом A)
Анти-пассивный мастер (после того, как мастер-узел отключен, вручную обновите подчиненный узел до мастер-узла) и сторожевой режим (после того, как мастер-узел не работает, подчиненный узел автоматически обновляется до мастер-узла)

На этот раз в основном вводятся операции одного хозяина и двух слуг, а также операция на основе анти-гостей. За сторожевым режимом напишите специальную статью для ознакомления!

Настроить главный сервер

Первый шаг — настроить главный сервер MySQL. Внесем следующие изменения:

Настройте сервер MySQL на прослушивание частного IP-адреса .
Установите уникальный идентификатор сервера.
Включите двоичное ведение журнала

Для этого откройте файл конфигурации MySQL и раскомментируйте или установите следующее:

master:/etc/mysql/mysql.conf.d/mysqld.cnf

После этого перезапустите службу MySQL, чтобы изменения вступили в силу:

Следующим шагом будет создание нового пользователя репликации. Войдите на сервер MySQL как пользователь root, набрав:

Изнутри командной строки MySQL выполните следующие SQL-запросы, которые создадут пользователя и предоставят пользователю привилегию :

Убедитесь, что вы изменили IP-адрес на свой подчиненный IP-адрес. Вы можете назвать пользователя как хотите.

Находясь в командной строке MySQL, выполните следующую команду, которая выведет двоичное имя файла и позицию.

Обратите внимание на имя файла mysql-bin.000001 и позицию 629. Эти значения понадобятся вам при настройке подчиненного сервера

Эти значения, вероятно, будут другими на вашем сервере.

Фильтруй базар

Возможно, для публикации вам нужен поднабор таблицы как отдельной статьи. Это называется фильтрацией данных. Фильтрация данных позволяет избавиться от конфликтов репликации, когда несколько сайтов имеют право обновлять данные. Ты можешь фильтровать таблицы вертикально, горизонтально или смешанно для создания отфильтрованной порции данных.

Вертикальный фильтр содержит поднабор колонок таблицы. Только реплицированные колонки отображаются подписчику. Для примера, можно использовать вертикальный фильтр для публикации всех колонок кроме «Заработная плата» в таблице «Работники».

Горизонтальный фильтр содержит поднабор строк таблицы. Подписчик получает только этот поднабор строк. Если ненужно реплицировать информацию о левых доходах, то ее можно отфильтровать запросом.

Возможно подписание на публикацию с помощью Push или Pop метода. Метод Push обычно используется в приложениях, которые должны отправлять изменения подписчику как можно быстрее после изменения. Этот метод более предпочтителен для публикаций требующих высокую защищенность и где высокая загрузка процессора у дистрибьютора не влияет на производительность.

Метод Pop более подходит в публикациях с меньшей защищенностью и может поддерживать большое количество подписчиков, например подписчики Internet.

Configuring the Master

Enable binary logging if it’s not already enabled. See Activating the Binary Log and Binary log formats for details.
Give the master a unique . All slaves must also be given a server_id. This can be a number from 1 to 232-1, and must be unique for each server in the replicating group.
Specify a unique name for your replication logs with . If this is not specified your host name will be used and there will be problems if the hostname ever changes.
Slaves will need permission to connect and start replicating from a server. Usually this is done by creating a dedicated slave user, and granting that user permission only to replicate (REPLICATION SLAVE permission).

Example Enabling Replication for MariaDB

Add the following into your my.cnf file and restart the database.

log-bin
server_id=1
log-basename=master1
binlog-format=mixed

The server id is a unique number for each MariaDB/MySQL server in your network.
binlog-format specifies how your statements are logged. This mainly affects the size of the binary log that is sent between the Master and the Slaves.

Then execute the following SQL with the command line client:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

Example Enabling Replication for MySQL

If you want to enable replication from MySQL to MariaDB, you can do it in almost the same way as between MariaDB servers. The main difference is that MySQL doesn’t support .

log-bin
server_id=1

Архитектура и методы репликации в SQL Server

Издатель (Publisher) — экземпляр базы данных источника, который публикует изменения данных, которые могут распределяться на другие базы данных. Данные от единственного издателя могут посылаться единственному подписчику или множеству подписчиков.
Подписчик (Subscriber) — экземпляр базы данных места назначения, куда мы распространяем изменения данных, происходящие в базе данных издателя. Подписчиком может быть либо экземпляр издателя, либо другой экземпляр на сервере издателя или другой сервер в том же местоположении или удаленном местоположении. Перед распределением изменений данных на экземпляр базы данных подписчика данные сохраняются в распределителе (Distributor).
Распределитель (Distributor) — это база данных, которая хранит журналы изменений, взятые из баз данных издателя. Если сервер включен как распределитель, он создаст системную базу данных с именем distribution database.

— Локальный распределитель- Удаленный распределитель- Push подписка- Pull подписка

Статьи (Articles) — основная единица репликации. Она указывает на любые изменения данных на этом объекте базы данных, или статье, которые будут реплицированы из издателя на подписчика. Статьей может быть таблица, представление, индексированное представление, хранимая процедура или определенная пользователем функция.
Публикации (Publications) — коллекция одной или более статей из базы данных на издателе.
Подписка (Subscription) определяет, какая публикация будет получена. Кроме того, она определяет, из какой публикации и по какому расписанию реплицируются данные. Подписка может быть либо Push, либо Pull (из публикации в подписку).
Агенты репликации (Replication Agents) — автономные программы, ответственные за отслеживание изменений и распределение данных от издателя к распределителю и подписчику. Все агенты репликации выполняются как задания (job) под управлением SQL Server Agent. Таким образом, этим можно управлять через SSMS в разделе SQL Server Agent Jobs или Replication Monitor. Имеются следующие типы агентов репликации:
Агент снимка (Snapshot Agent) — используется почти всеми типами репликации. Агент снимка выполняется на сервере, содержащем базу данных распределения. Он подготавливает схему и исходные данные всех статей, включенных в публикацию на издателе. Кроме того, он создает файлы снимка в папке снимков и записывает подробности синхронизации в базу данных распределения.
Агент читателя журнала (Log Reader Agent) — используется транзакционной репликацией. Целью является чтение изменений данных статей, доступных для репликации из журналов транзакций базы данных издателя и хранящихся в базе данных распределения. Агент читателя журнала выполняется на сервере распределителя.
Агент распределения (Distribution Agent) — используется транзакционной репликацией и репликацией снимка. Он применяет исходные файлы снимка и инкрементальные или доступные ожидающие транзакции из базы данных распределителя к базе данных подписчика. Агент распределения выполняется на сервере распределителя для подписок Push и на сервере подписчика для подписок Pull.
Агент слияния (Merge Agent) — используется только в репликации слиянием. Он применяет исходные файлы снимка и согласовывает дифференциальные или инкрементальные изменения на подписчике или издателе. Агент слияния выполняется на сервере распределителя для репликации Push и из сервера подписчика для подписок Pull.
Агент читателя очереди (Queue Reader Agent) — Агент читателя очереди используется транзакционной репликацией с опцией обновления очереди. Он перемещает обновления от подписчика на издателя. Агент читателя очереди выполняется из сервера распределителя.
Задания обслуживания репликации (Replication Maintenance Jobs ) — Как объяснялось ранее, все агенты репликации являются автономными программами, устанавливаемыми во время конфигурирования репликации. Они выполняются как задания (job) из раздела SQL Server Agent Jobs. Следует отметить несколько важных задач очистки распределения — Distribution Clean Up: Distribution, Agent History Clean Up: Distribution, and Expired Subscription Clean Up.

Еще о репликации Active Directory (AD)

Если хочешь узнать больше о репликации в Acive Directory, и нет проблем с английским, то рекомендую скачать следующий документ: www.certmag.com/bookshelf/C0617953.pdf. Это 92 страницы полезного и халявного чтива. Если и этого мало, то бегом на technet от Microsoft. Там информация изложена не так удобно и последовательно, но очень много хороших рекомендаций.

По Active Directory и репликации в частности могу посоветовать сайт only4gurus.com и конкретно ссылочка — http://www.only4gurus.com/v3/sitemap_active_directory.shtml. По репликации здесь можно найти хорошие презентации, рисунки которой были взяты за основу к данной статье. Я лишь перевел эти рисунки на мой родной русский и немного подкорректировал, чтобы они были нагляднее.

Мониторинг

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

На мастер-сервере после настройки репликации команда select * from pg_stat_replication; должна возвращать не пустые значения, а команда SELECT count(*) FROM pg_stat_replication возвращать минимум значение “1”.

На stabdby-сервере можно наблюдать за отставанием относительно мастера, для этого подойдут команды select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();. Но высчитывать здесь уже будет сложнее.

Поэтому рекомендую подключить комплексный шаблон мониторинга PostgreSQL через zabbix-agent2 в Zabbix 5+, где все вышеуказанные параметры (и множество других) учтены и позволят быть в курсе всех происходящих событий с базой и репликой.

Настройка master

Сперва нужно настроить ведущий сервер (master)

Расположение конфигурационного файла

postgresql.conf

можно получить выполнив

-bash-4.2$ su — postgres -c «psql -c ‘SHOW config_file;'»

Password:
config_file
————————————-
/var/lib/pgsql/data/postgresql.conf
(1 row)

vi /var/lib/pgsql/data/postgresql.conf

wal_level = hot_standby

max_wal_senders = 1

wal_keep_segments = 50

192.168.56.109 — IP-адрес сервера, на котором он будем слушать запросы Postgre;
wal_level

указывает, сколько информации записывается в WAL (Write Ahead Log — журнал операций, который используется для репликации);
max_wal_senders — количество планируемых слейвов;
hot_standby — определяет, можно или нет подключаться к postgresql для выполнения запросов в процессе восстановления;
hot_standby_feedback — определяет, будет или нет сервер slave сообщать мастеру о запросах, которые он выполняет.

Перезапустить postgresql от обычного пользователя

$ sudo systemctl restart postgresql

Снова залогиньтесь под postgres

sudo su — postgres

В файле
pg_hba.conf
добавьте
slave_ip

vi /var/lib/pgsql/data/pg_hba.conf

host replication postgres 192.168.56.110/32 trust

Теперь нужно сделать копию данных с мастера и отправить на слейв

Если вы вышли из пользователя postgres — залогиньтесь снова

su — postgres

Бэкап (я делаю с пустой базы, сразу после установки)

psql -c «SELECT pg_start_backup(‘replbackup’);»

pg_start_backup
——————
0/2000020
(1 row)

tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/data

psql -c «SELECT pg_stop_backup();»

Скорее всего появится ошибка

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
pg_stop_backup
—————-
0/20000E0
(1 row)

Так как скопирована будет вся директория, можно сказать, что мы пользуемся other means

Отправьте на слейв

scp /tmp/db_file_backup.tar andrei@192.168.56.110:/tmp/

Проверка компонент репликации

USE distribution
GO
exec sp_helpdistpublisher
GO
select * from MSpublisher_databases
GO

USE distribution
GO
exec sp_helpdistributor
GO
exec sp_helpdistributiondb
GO

Статьи

USE AdventureWorks
GO
-- Чтобы увидеть все статьи, доступные для публикации
exec sp_helparticle @publication = 'Adventureworks_pub'
GO
-- Чтобы увидеть все столбцы статьи для конкретной статьи публикации
exec sp_helparticlecolumns @publication = 'Adventureworks_pub', @article = 'Address'
GO
USE distribution
GO
SELECT * from MSArticles

USE AdventureWorks
GO
exec sp_helppublication
GO
USE distribution
GO
SELECT * FROM MSPublications

USE AdventureWorks
GO
exec sp_helpsubscription
GO
USE distribution
GO
SELECT * FROM MSsubscriptions
GO

Шаг 2. Права на репликацию

Далее необходимо создать профиль пользователя, из под которого будет происходить репликация. Для этого запускаем консоль:

mysql -u root -p

Далее создаем и назначаем права пользователю для реплики:

REPLICATION SLAVE — привилегия позволяющая подключиться к серверу т запросить обновлённые на мастере данные;
REPLICATION CLIENT — привилегия, позволяющая использовать статистику:
1. SHOW MASTER STATUS
2. SHOW SLAVE STATUS
3. SHOW BINARY LOGS

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave_user'@'10.1.%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Далее блокируем все таблицы в нашей базе данных:

USE newdatabase;
FLUSH TABLES WITH READ LOCK;

Проверяем статус Мастер-сервера:

SHOW MASTER STATUS;

Мы увидим что-то похожее на:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)