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

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

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

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.%

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

Примечание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 пробел даже опытным людям не даст подсказку, что оно не работает. А самое главное, что ошибки-то тоже не выдаёт.

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) Информация о MASTER’e

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

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

http://www.percona.com/blog/2015/03/09/5-free-handy-tools-for-monitoring-and-managing-mysql-replication/

9) Tungsten Replicator

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