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

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, а затем оттуда вносит изменения в базу.

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