[mysql] Некоторые аспекты репликации

Описывать настройку репликации, здесь не буду, так как в интернете полно разных описаний. Здесь расскажу о некоторых тонкостях и аспектах работы репликации. То есть о тех вещах, о которых обычно умалчивают авторы при написании статей о репликациях.

1) Тип репликации.

Начиная с версии 5.1 доступно стал доступен новый тип репликации – row (а вместе с ним сразу тип MIXED). До этого был только 1 – statement (установлен по умолчанию). Отличаются они следующим образом: statement работает по принципу пересылки запросов (то есть на slave пересылается запрос, который был выполнен на master’e и который привел к тому, что база данных изменилась). В ROW-режиме в бинлог пишутся не запросы, а уже измененные этими запросами данные (впрочем, запросы, изменяющие схемы данных (DDL), все равно пишутся как есть). Событие в ROW-режиме представляет собой:
• одну строку данных — для команд INSERT и DELETE. Соответственно, для INSERT пишется вставленная строка, для DELETE — удаленная
• две строки — BEFORE и AFTER — для UPDATE.

Можно сказать, что ROW работает по принципу rsync, то есть делает полностью идентичные копии. В режиме row достаточно просто подключить slave и запустить репликацию – он сам создаст необходимые базы и наполнит их данными.

Есть кое-какие нюансы:

– триггеры на слейве выполняются только при использовании STATEMENT.
– режим репликации MIXED: при этом сервер сам переключается между STATEMENT и ROW репликацией (как он это делает – непонятно)
– при использовании row репликации, лог будет значительно больше и нельзя будет инспектировать лог с помощью утилиты mysqlbinlog
– в версиях 5.1 была неразбериха по поводу того, какой тип использовать по умолчанию: здесь можно посмотреть какие версии что используют

Что бы установить типа репликации, выставляем нужное значение переменной:

binlog-format=STATEMENT

2) Репликация и логи.

Когда включена репликация, master записывает все изменения в бинарные логи bin-log, которые потом стягивает slave и пишет эти изменения в файл relay-log, а затем оттуда вносит изменения в базу.

Рассмотрим, ключевые моменты:

• Master_Log_File: имя файла на master’e с которого идет чтение репликации в данный момент.
• Read_Master_Log_Pos: позиция в логе на master’e с которого идет чтение репликации в данный момент.
• Relay_Log_File: имя relay log на slav’e в которИй пишется в данный момент
• Relay_Log_Pos: позиция, транзакции, которая уже записалась в relay log file на slave.
• Relay_Master_Log_File: имя файла на master’e, который содержит последние данные.
• Exec_Master_Log_Pos: позиция, данные до которой уже записаны может использоваться для CHANGE MASTER TO.

Подробнее здесь

3) Цепочка master->slave1->slave2->…

Такого плана репликация называется деревовидной. Настройки ничем не отличаются от обычных настроек, только нужно на всех slave1/slave2/… включать одновременно и бинарные и relay логи. Но это ещё не всё. Дело в том, что при репликации slave не пишет в бинарные логи изменения, полученные с master’a. Соответственно, бинарные журналы будут пусты, а значит репликация slave1->slave2->… уже работать не будет. Иными словами нам нужно включить запись обновлений на slave не только в relay-log, а и в bin-log. Для этого добавляем параметр в конфиг каждого slave’a, который будет является master’om для нижестоящего:

log-slave-updates = 1

4) Исправления ошибок.

Если в процессе репликации на slave возникли ошибки такого плана:

Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`ivision4/programmgroup`, CONSTRAINT `c_programmgroup_

Попробуйте выполнить:

slave stop; set global sql_slave_skip_counter=1; slave start;

а для версии 5.6.Х:

stop slave; set global sql_slave_skip_counter=1; start slave;

Если ошибок будет много, рекомендуется поставить вместо 1, нужное число, либо несколько раз выполнить команду.
Эта команда разрешает пропустить ошибку и пойти далее.

Если возникает такая ошибка:

1062 Duplicate entry '...' for key 1,

тогда нужно в конфиге my.cnf выставить переменную

slave-skip-errors = 1062

Ошибка

Error Lock wait timeout exceeded; try restarting transaction

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

Для GTID-репликации можно использовать рекомендации, описанные здесь

5) Реплицируем нужные БД.

(начиная с версии 5.7 эти параметры можно менять налету)

Если нужно реплицировать только некоторые базы или все, кроме некоторых, то сначала обязательно прочитайте документацию про использование параметров binlog-do-db, binlog-ignore-db, replicate-do-db, replicate-ignore-db так как при некоторых моментах репликация может не пройти. К примеру, у меня при использовании параметра binlog-do-db репликация вообще не шла! Оставил только replicate-ignore-db и всё нормально.

Пару слов хочется сказать о параметрах replicate-ignore-db и replicate-wild-ignore-table. И тот и тот можно применять для пропуска репликаций нужных баз, но, при установке только параметра

replicate-ignore-db = test

то эта опция не будет работать (то есть база test будет в этом случае реплицироваться), если Вы используете перекрестные модификации базы данных. Если нужно исключить полную репликацию нужной базы, использовать лучше параметр replicate-wild-ignore-table:

replicate-wild-ignore-table = test.%

Так же, важно понимать, что при использовании параметра replicate-ignore-db может не среплицироваться и нормальный запрос, если, он был сделан в контекста базы, которая указана, несмотря на то, что вы явно указываете базу в запросе.

Я на всякий случай использую оба параметра.

Примечание1

Есть и другая сторона медали: можно натолкнуться на баг, при котором mysql “крешиться”. Это связано с использованием опции replicate-wild-ignore-table (http://bugs.mysql.com/bug.php?id=66915) . Этот баг перешёл в разряд “приватных” и неясно, будет пофиксен или нет. Или фикс будет доступен только в Enterprice версиях. Очень неплохо об этом написано http://ronaldbradford.com/blog/when-is-a-crashing-mysql-bug-not-a-bug-2012-08-15/

Данный баг отсутствует в версии 5.6.10 (а может и во всей 5.6.Х – версии). Так что, можно обновиться.

В этом случае, если репликация стартует вместе с mysql, то мы получаем циклический креш. Временный вариант решения – отключить старт репликации (в конфиге добавляем опцию skip-slave-start) при старте mysql, пропустить ошибку и стартонуть репликацию дальше:

mysql>set global sql_slave_skip_counter=1; slave start;

Примечание2

Есть нюансы (которые не сразу видно) при использовании replicate-ignore-db, а именно следующее. Если вам нужно добавить несколько баз в игнор, то документация говорит, что нужно для каждой базы добавлять replicate-ignore-db. Но некоторые “личности” могут схитрить и сделать в конфиге так:

replicate-ignore-db = test1, test2

Ошибки это не вызовет, но и работать это тоже не будет. При этом, если посмотреть через show slave status\G то разница между

replicate-ignore-db = test1, test2

и

replicate-ignore-db = test1
replicate-ignore-db = test2

будет выглядеть так:

Replicate_Ignore_DB: mysql, test

и

Replicate_Ignore_DB: mysql,test

Разница в 1 пробел даже опытным людям не даст подсказку, что оно не работает. А самое главное, что ошибки-то тоже не выдаёт.

Примечание3

А ещё есть неочевидный момент с типом репликации: row/statement. При использовании фильтров replicate-do-db в statement может не реплицироваться запрос. Объяснение и примеры здесь

6) Отставание репликации.

Что бы узнать, насколько репликация отстаёт от master’a – посмотрите на вывод show slave status\G; и найдите Seconds_Behind_Master. Если он 0 или близок к этому – значит всё нормально. Если он растёт – значит где-то есть задержки. НО! Если вы только запустили репликацию и база у вас слишком часто обновляется, то значение может расти и со временем оно будет уменьшаться до 0 (если же нет – значит нужно искать узкие места).

Для проверки того, что отставание действительно 0, можно сравнить значения в выводе команды show slave status /G:

Master_Log_File=Relay_Master_Log_File
Read_Master_Log_Pos=Exec_Master_Log_Pos

Поиск узких мест дело очень щепетильное и порой даже невозможное. Но есть небольшие рекомендации по этому поводу, если конечно у вас используется InnoDB.

– увеличьте значения параметров innodb_buffer_pool_size (отвечает за размер буфера в памяти, который используется для хранения кешированных данных и индеков) и innodb_log_file_size (и попробовать поэкспериментировать с параметром innodb_log_files_in_group) до размеров, где-то 40-50% от оперативной памяти.

– так же можно увеличить параметр innodb_additional_mem_pool_size и innodb_thread_concurrency

– установить значение параметра innodb_flush_method=O_DIRECT, которое отключает двойную буфферизацию (используется на Linux, BSD, Solaris)

– добавить параметр skip-innodb_doublewrite, который отключит двойную запись для InnoDB

7) autoincrement

Если вы планируете разворачивать master–master репликацию, то нужно что бы не было дублирующих данных. Идея какая: на одном мастере будут чётные ID, на втором – нечётные. Это делается так:

– на master1:

auto-increment-increment = 2
auto-increment-offset   = 1

– на master2:

auto-increment-increment = 2
auto-increment-offset = 2

Первый параметр отвечает, на сколько делать autoincremen (в нашем случае на 2: 1, 3, 5, …). Второй – на сколько сдвинуть autoincrement. В нашем случае, на master1 номера будут идти 1,3,5,.., а на master2 – 2,4,6… так как был сдвиг на 2 (auto-increment-offset = 2), то есть нумерация начинается с 2.

8 ) Информация о MASTER’e

Как получить информацию о master’e (login, pass, host,..), если у вас есть доступ к серверу? Очень просто. Информация храниться в файле $datadir/master.info, где datadir – путь к базе. В этом файле мы найдём логин, пароль (в открытом виде!), адрес master’a и другую полезную информацию.

9) Полезные ссылки

http://www.percona.com/blog/2015/03/09/5-free-handy-tools-for-monitoring-and-managing-mysql-replication/
http://mysqlhighavailability.com/mysql-replication-defaults-after-5-7/
https://www.percona.com/blog/2018/05/03/slave-performance-too-slow-with-row-based-events-causes-and-workarounds/

10) Tungsten Replicator

Можно вместо стандартной репликации использовать Tungsten Replicator, который имеет преимущества над стандартным репликатором (multi-master, паралельная репликация,…)

11) SQL и IO thread

Вот как они выглядят, когда смотрим slave status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Что это такое? IO отвечает за считывание данных с master’a, а SQL – собственно за само исполнение на slav’e. Они могут работать независимо друг от друга и их тоже можно запускать независимо друг от друга:

mysql> START SLAVE IO_THREAD;
mysql> START SLAVE SQL_THREAD;

Зачем может быть надо запускать их отдельно друг от друга? Если у вас в данный момент база сильно нагружена, то можно запустить считывание репликации, но пока не применять.

12) Репликация с разными типами данных

https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

13) Восстановление реплик из соседних

Мало кто знает, что server_id играет очень важную роль в репликации и не только потому, что бы отделять сервера между собой, а так же этот параметр учитывается при применении запросов пришедших от master’a. Иными словами, если вы восстановили реплику из master’a, то запросы от этого же master’a потом будут игнорироваться на восстановленой реплике из-за server_id, который совпадает с master’ом, при условии, что старые server_id не менялись. Если вы после восстановления реплики изменили server_id и на master’e и на реплике, то проблем у вас не должно быть. Но как же выйти из ситуации, когда нельзя изменить server_id? Для этого нужно выставить на реплике параметр

replicate-same-server-id=1

и перезапустить репликацию.