MySQL: Руководство администратора

4.10 Репликация

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

Репликация может помочь резко ускорить работу и повысить ошибкоустойчивость. Для ошибкоустойчивости Вы можете иметь две системы и обращаться к копии, если Вы имеете проблемы с главной системой. Быстродействие улучшается за счет переброски части запросов, не модифицрующих данные, на второй сервер. Конечно, это работает только, если такие запросы доминируют, но это нормальный случай.

Начиная с Version 3.23.15, MySQL поддерживает одностороннюю репликацию. Один сервер действует как главный (master), остальные как подчиненные (slave). Обратите внимание, что один сервер может выполнять обе роли сразу. Главный сервер хранит двоичный файл регистрации модификаций (подробности в разделе "4.9.4 Двоичный протокол изменений ") и индексный файл к двоичным файлам регистрации, чтобы следить за циклическим сдвигом файла регистрации. Подчиненный докладывает главному о том, где он остановился после последней успешной модификации. обновляет свои данные, блокирует базу данных и ждет новых обновлений.

Обратите внимание, что если Вы копируете базу данных, все модификации для этой базы данных должны быть выполнены через главный сервер!

Другая польза от использования репликации в том, что главный сервер может заниматься только обслуживанием запросов, а резервирование можно выполнять на подчиненном. Подробности в разделе "4.4.1 Резервирование баз данных".

4.10.2 Краткий обзор реализации

Репликация в MySQL основана на сервере, следящем за всеми изменениями для базы данных (модификации, удалении и т.д) в двоичном файле регистрации, и подчиненном сервере (их может быть несколько штук), читающем сохраненные запросы из двоичного файла регистрации так, чтобы подчиненный мог выполнять те же самые запросы на своей копии данных.

Очень важно понять, что двоичный файл регистрации всего лишь запись, начинающаяся с фиксированной отметки во времени (момент, когда Вы запустили двоичную регистрацию). Любые подчиненные системы, которых Вы установили, будут нуждаться в копиях всех данных с главного сервера потому, что существовал момент, когда Вы запустили двоичную регистрацию на нем. Если Вы запускаете подчиненные системы не с теми данными, которые были на главном сервере на момент начала протоколирования, все это провалится.

Запущенный подчиненный сервер будет связываться с главным и получать данные по изменениям. Если связь оборвалась, подчиненный будет пытаться ее восстановить через каждые master-connect-retry секунд.

Каждый подчиненный сам следит, где именно прекратились обновления. Главный сервер не знает ни того, сколько подчиненных серверов работают, ни тем более того, какой из них чем занят.

4.10.3 Как установить репликацию

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

Удостоверьтесь, что Вы имеете свежую MySQL на главной и всех подчиненных машинах. Используйте версию 3.23.29 или выше. Предыдущие выпуски использовали различный двоичный формат файла регистрации и имели ошибки, которые были исправлены в более новых выпусках. Пожалуйста, не сообщайте об ошибках, пока не проверите это, проблема присутствует в последнем выпуске.
Установите специального пользователя replication на главной системе с привилегией FILE и разрешением соединяться от имени всех подчиненных систем. Если пользователь только делает репликацию (что очень рекомендуется!), Вы не должны предоставлять ему дополнительные привилегии. Например, чтобы создать пользователя с именем repl, который может обращаться к Вашему главному компьютеру с любого другого компьютера, Вы могли бы использовать эту команду:
```
GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '<password>';
```
Завершите сервер MySQL на главной и подчиненных системах:
```
mysqladmin -u root -p<password> shutdown
```
Скопируйте все данные с главного сервера. Самое простое, что тут можно предложить, (на системах Unix), это использовать команду tar для изготовления архива всего каталога с данными. Точное расположение этого каталога данных зависит от Вашей установки.
```
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
```
Windows-пользователям рекомендуется использовать для этих целей WinZip или что-то подобное, впрочем, версии tar есть под всеми известными мне ОС. Windows тут не исключение.
В файле my.cnf на главном сервере добавьте строки log-bin и server-id=unique number в секцию [mysqld]. Очень важно, что идентификаторы подчиненных отличаются от идентификатора главной системы.
```
[mysqld]
log-bin
server-id=1
```
Перезапустите сервер MySQL на главной системе.
На подчиненных машинах в файл my.cnf надлежит дописать:
```
master-host=<hostname of the master>
master-user=<replication user name>
master-password=<replication user password>
master-port=<TCP/IP port for master>
server-id=<some unique number between 2 and 2^32-1>
```
Значения в <> следует заменить на соответствующие Вашей системе. server-id должен быть свой для каждой машины, участвующей в репликации. Если Вы не определяете идентификатор сервера, он будет установлен в 1, если Вы не определили master-host, иначе он будет установлен в значение 2. Обратите внимание, что в случае вычеркивания идентификатора server-id главный сервер откажется от подключений от всех подчиненных машин, а они не смогут связаться с ним.
Копируйте сохраненные данные в каталог данных на Вашем подчиненном сервере. Удостоверитесь, что привилегии на файлах и каталогах правильны. Пользователь, от имени которого выполняется MySQL должен иметь право читать и писать эти файлы.
Перезапустите подчиненную систему (или системы, если их несколько).

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

Если Вы забыли установить идентификатор (server-id) для подчиненного, Вы получите следующую ошибку в файле регистрации ошибок:

Warning: one should set server_id to a non-0 value if master_host
is set. The server will not act as a slave.

Если Вы забыли это сделать для главного сервера, подчиненный связаться с ним не сможет.

Если подчиненный не способен копировать данные (неважно по какой причине), Вы найдете сообщения об ошибках в файле регистрации ошибок на подчиненном.

Если подчиненный копировал хоть что-то, Вы найдете файл master.info в том же самом каталоге, где и файл регистрации ошибок. Файл master.info используется подчиненным, чтобы следить, что из двоичного файла регистрации главного сервера он уже обработал. Не удаляйте и не правьте файл, если Вы не знаете точно, что и зачем Вы делаете. Даже если знаете, лучше пользуйтесь командой CHANGE MASTER TO.

4.10.4 Возможности и проблемы репликации

Что поддерживается, а что еще нет? Здесь я рассматриваю краткий перечень проблем и возможностей репликации. Итак, начнем:

Репликация будет выполнена правильно с AUTO_INCREMENT, LAST_INSERT_ID и значениями TIMESTAMP.
RAND() в модификациях не копируется правильно. Используйте RAND(some_non_rand_expr), если Вы копируете модификации с RAND(). Вы можете, например, использовать UNIX_TIMESTAMP() как аргумент для RAND().
Вы должны использовать один и тот же набор символов (--default-character-set) на главной и подчиненных машинах. Если это не так, Вы можете получать ошибки дублирования ключей на подчиненном потому, что ключ, который расценен как уникальный на главной системе, не может быть таким в другом наборе символов.
LOAD DATA INFILE будет обработан правильно, пока файл все еще существует на главной машине во время распространения модификации. Зато LOAD LOCAL DATA INFILE будут пропущены.
Модифицировать запросы, которые используют переменные пользователя, не всегда безопасно с точки зрения репликации (в текущей версии).
Команды FLUSH не сохраняются в двоичном файле регистрации и из-за этого не копируются на подчиненные машины. Это обычно не проблема, поскольку FLUSH ничего не меняет в данных. Но если Вы модифицируете таблицы привилегий MySQL непосредственно без того, чтобы использовать инструкцию GRANT, и затем Вы копируете базу данных привилегий MySQL, Вы должны отдать команду FLUSH PRIVILEGES на подчиненных, чтобы новые версии привилегий вступили в законную силу.
Временные таблицы, созданные в версии 3.23.29 копируются правильно за исключением случая, когда Вы выключаете подчиненный сервер (но не процесс), когда некоторые временные таблицы открыты и используются в последующих модификациях. Чтобы уладить дело с этой проблемой, выключайте подчиненный сервер командой SLAVE STOP, затем проверьте переменную Slave_open_temp_tables, чтобы увидеть, является ли она 0, потом скомандуйте mysqladmin shutdown. Если переменная не равна 0, перезапустите подчиненный процесс SLAVE START и попробуйте еще. В версии 4.0 обещали как-то это уладить, но пока так и не сделали. В более ранних версиях временные таблицы вообще не копируются правильно. Я рекомендую, чтобы Вы или нарастили вычислительные возможности, или выполнили SET SQL_LOG_BIN=0 на всех клиентах перед всеми запросами с участием временных таблиц.
MySQL поддерживает только один главный и много подчиненных серверов. В версии 4.x обещали доделать голосующий алгоритм, чтобы автоматически сменить главную систему, если что-то идет неправильно с текущей. Также обещали представить процессы-агенты, чтобы помочь выполнению балансировки загрузки, посылая запросы выбора различным подчиненным серверам. Ничего этого на момент написания книги сделано так и не было. Мечты, мечты, где ваша сладость...
Начиная с версии 3.23.26, можно безопасно подключить системы в круговой главно-подчиненной связи с допуском log-slave-updates. Обратите внимание, что ряд запросов не будет корректно функционировать в этом виде установки, если ваш код пользователя не учитывает потенциальных проблем, которые могут произойти от модификаций, которые происходят в различной последовательности на разных серверах. Это означает, что Вы можете делать установку подобно следующему:
```
A -> B -> C -> A
```
Эта установка будет работать только, если Вы не делаете противоречивые модификации между таблицами. Другими словами, если Вы вставляете данные A и C, Вы никогда не должны вставлять строку в A, который может иметь противоречивый ключ со вставкой строки в C. Вы не должны также модифицировать строки на двух серверах, если порядок, в котором применяются модификации, может быть неправильным. Обратите внимание, что формат файла регистрации изменился в версии 3.23.26 так, что pre-3.23.26 его не поймет.
Если запрос на подчиненном сервере получает ошибку, подчиненный процесс завершится, и сообщение появится в .err файле. Вы должны затем соединиться с подчиненным вручную, установить причину ошибки (например, несуществующая таблица) и выполнить команду sql SLAVE START (доступна с весрии 3.23.16). В версии 3.23.15 Вы должны перезапустить сервер,
Если подключение к главному серверу оборвалось, подчиненный повторит его немедленно и затем, в случае сбоя, каждые master-connect-retry (по умолчанию 60) секунд. Из-за этого можно безопасно завершать работу главного сервера, а затем перезапускать его через некоторое время.
Выключение подчиненного сервера также безопасно, поскольку он сам следит, что он скопировал, а что нет. Ваши средства обеспечения отказоустойчивости работы будут значительно увеличиваться, если Вы имеете хороший UPS.
Если главный сервер слушает на ненормативном порте, Вы должны определить это параметром master-port в файле my.cnf.
В версии 3.23.15 все таблицы и базы данных будут копироваться всегда. При работе в версии 3.23.16 Вы можете ограничивать репликацию набором баз данных директивами replicate-do-db в my.cnf или исключать набор баз данных директивой replicate-ignore-db. Обратите внимание, что вплоть до версии 3.23.23 имелась ошибка, которая не давала правильно работать с LOAD DATA INFILE, если Вы использовали это в базе данных, которая была исключена из репликации.
Начиная с версии 3.23.16, SET SQL_LOG_BIN=0 выключит двоичный протокол на главном сервере, а SET SQL_LOG_BIN=1 все вернет на свои места. Для этого Вы должны иметь привилегию process.
Начиная с версии 3.23.19, Вы можете очистить буфер репликации и начать заново командами FLUSH MASTER и FLUSH SLAVE. В версии 3.23.26 они переименованы в RESET MASTER и RESET SLAVE соответственно, чтобы разъяснить то, что они делают. Старые варианты FLUSH тем не менее все еще работают для совместимости.
Начиная с версии 3.23.21, Вы можете использовать LOAD TABLE FROM MASTER для сетевого резервирования и начала репликации. На момент написания книги, эта функция была нестабильной и находилась в стадии отладки.
Начиная с версии 3.23.23, Вы можете изменять данные главного сервера, а также корректировать позицию файла регистрации с помощью команды CHANGE MASTER TO.
Начиная с версии 3.23.23, Вы сообщаете главной системе, что модификации в некоторых базах данных не должны регистрироваться в двоичном файле регистрации с помощью команды binlog-ignore-db.
Начиная с версии 3.23.26, Вы можете использовать replicate-rewrite-db, чтобы сообщить подчиненному о том, что надо применять модификации из одной базы данных на главной системе к базе данных с другим именем на подчиненном.
Начиная с версии 3.23.28, Вы можете использовать PURGE MASTER LOGS TO log-name, чтобы избавиться от старых файлов регистрации в то время, как подчиненный продолжает работать.

4.10.5 Опции репликации в файле my.cnf

Если Вы пользуетесь репликацией, используйте минимум MySQL Version 3.23.30 (а лучше выше). Старые версии работают, но глюков там хватает...

На главной и подчиненной системах нужно использовать опцию server-id. Она устанавливает уникальный идентификатор. Вы должны выбрать уникальное значение в диапазоне от 1 до 2^32-1. Например: server-id=3

Следующая таблица показывает параметры, которые Вы можете использовать для главного (MASTER) сервера:

Опция	Описание
`log-bin=filename`	Записывать в двоичный файл регистрации модификаций filename. Обратите внимание, что, если Вы задаете этот параметр с расширением (например, `log-bin=/mysql/logs/replication.log`) версии до 3.23.24 не будут работать корректно, если Вы делаете `FLUSH LOGS`. Проблема была исправлена в версии 3.23.25. Если Вы используете этот вид имени файла регистрации, команды `FLUSH LOGS` будут игнорироваться. Чтобы очистить файл регистрации, выполните `FLUSH MASTER` на главной системе и `FLUSH SLAVE` на всех подчиненных. В версии 3.23.26 и в более поздних Вы должны использовать `RESET MASTER` и `RESET SLAVE`
`log-bin-index=filename`	Поскольку пользователь мог бы выдавать команду `FLUSH LOGS` Вы должны знать, который файл регистрации является в настоящее время активным. Эта информация сохранена в двоичном индексном файле регистрации. Значение по умолчанию `hostname`.index. Вы можете использовать эту опцию, если Вы хотите переопределить значение. Например: `log-bin-index=db.index`.
`sql-bin-update-same`	Если задано, то установка `SQL_LOG_BIN` автоматически установит в такое же значение и `SQL_LOG_UPDATE` и наоборот.
`binlog-do-db=database_name`	Сообщает главной системе, что она должна регистрировать модификации в двоичном файле регистрации, если текущая база данных 'database_name'. Все прочие базы данных игнорируются. Обратите внимание, что, если Вы используете эту опцию, Вы должны гарантировать, что делаете модификации только в текущей базе данных. Например: `binlog-do-db=some_database`.
`binlog-ignore-db=database_name`	Сообщает главной системе, что модификации, где текущая база данных является database_name, НЕ должны быть сохранены в двоичном файле регистрации. Обратите внимание, что, если Вы используете эту опцию, Вы должны гарантировать, что делаете модификации только в текущей базе данных. Например: `binlog-ignore-db=some_database`

Следующая таблица показывает параметры, которые Вы можете использовать для подчиненной (SLAVE) системы:

Опция	Описание
`master-host=host`	Имя хоста или IP-адрес главной системы. Если не задано, подчиненный не будет работать вообще. Например: `master-host=joker.botik.ru`.
`master-user=username`	Пользователь под именем которого надо связываться с главной системой. Пользователь должен иметь привилегию `FILE`. Если не задано, принимается пользователь `test`. Например: `master-user=alexmv`.
`master-password=password`	Пароль для пользователя из предыдущего параметра. По умолчанию принимается пустой пароль. Например: `master-password=pupsik`.
`master-port=portnumber`	Порт для связи с главной системой. Если не задан, принимается компилируемая установка `MYSQL_PORT`. Если Вы не меняли опции `configure`, это будет 3306. Например: `master-port=3306`.
`master-connect-retry=seconds`	Число секунд, которые подчиненный будет бездействовать перед повторением попытки восстановить связь с главной системой в случае ее обрыва. Значение по умолчанию 60. Например: `master-connect-retry=60`.
`master-ssl`	Включить поддержку SSL. Например: `master-ssl`.
`master-ssl-key`	Имя файла с ключем SSL для главной системы. Например: `master-ssl-key=SSL/master-key.pem`.
`master-ssl-cert`	Имя файла с сертификатом SSL для главной системы. Например: `master-ssl-key=SSL/master-cert.pem`.
`master-info-file=filename`	Расположение файла, который хранит данные о ходе процесса репликации. Значение по умолчанию master.info в каталоге данных. Например: `master-info-file=master.info`.
`replicate-do-table=db_name.table_name`	Сообщает, чтобы подчиненная система ограничила репликацию определенной таблицей. Чтобы определять больше, чем одну таблицу, используйте директиву несколько раз, по одному для каждой таблицы. Это будет работать для модификаций с перекрестной базой данных, в отличие от `replicate-do-db`. Например: `replicate-do-table=some_db.some_table`.
`replicate-ignore-table=db_name.table_name`	Сообщает, чтобы подчиненная система не копировала определенную таблицу. Чтобы определять больше, чем одну таблицу, используйте директиву несколько раз, по одному для каждой таблицы. Это будет работать для модификаций с перекрестной базой данных, в отличие от `replicate-ignore-db`. Например: `replicate-ignore-table=db_name.some_table`.
`replicate-wild-do-table=db_name.table_name`	Сообщает, чтобы подчиненная система ограничила репликацию таблицами, которые соответствуют определенному образцу подстановочных знаков. Чтобы определять больше, чем одну таблицу, используйте директиву несколько раз, по одному для каждой таблицы. Это будет работать для модификаций с перекрестной базой данных. Например: `replicate-wild-do-table=foo%.bar%` будет копировать только модификации к таблицам во всех базах данных, которые начинаются с foo и только для таблиц, имена которых начинаются с bar.
`replicate-wild-ignore-table=db_name.table_name`	Противоположно предыдущей опции.
`replicate-ignore-db=database_name`	Сообщает, чтобы подчиненная система не копировала определенную базу данных. Чтобы определять больше, чем одну базу, используйте директиву несколько раз, по одному для каждой. Эта опция не будет работать, если Вы используете перекрестные модификации базы данных. Если Вы нуждаетесь в перекрестных модификациях базы данных, следует удостовериться, что Вы имеете версию 3.23.28 или позже, и использовать `replicate-wild-ignore-table=db_name.%`. Например: `replicate-ignore-db=some_db`.
`replicate-do-db=database_name`	Сообщает, чтобы подчиненная система ограничила репликацию определенной базой данных. Чтобы определять больше, чем одну базу, используйте директиву несколько раз, по одному для каждой. Эта опция не будет работать, если Вы используете перекрестные модификации базы данных, то есть под запретом оказываются запросы типа `UPDATE some_db.some_table SET foo='bar'` при выборе иной база или не выбрано никакой. Если Вы нуждаетесь в перекрестных модификациях базы данных, следует удостовериться, что Вы имеете версию 3.23.28 или позже, и использовать `replicate-wild-do-table=db_name.%`. Например: `replicate-do-db=some_db`.
`log-slave-updates`	Сообщает, чтобы подчиненная система регистрировала модификации из подчиненного процесса в двоичном файле регистрации. По умолчанию Off.
`replicate-rewrite-db=from_name->to_name`	Обновлять базу данных с именем, отличным от первоначального. Например: `replicate-rewrite-db=master_db_name->slave_db_name`.
`skip-slave-start`	Сообщает, чтобы подчиненная система не запускалась как подчиненная. Пользователь может запустить это позже с помощью `SLAVE START`.
`slave_read_timeout=#`	Число секунд, которое надо ждать перед разрывом связи.

4.10.6 Команды SQL, относящиеся к репликации

Реликация может контролироваться через интерфейс SQL.

Команда	Описание
`SLAVE START`	Запустить подчиненный (Slave) поток.
`SLAVE STOP`	Выключить подчиненный (Slave) поток.
`SET SQL_LOG_BIN=0`	Отключает регистрацию модификации, если пользователь имеет привилегию process.
`SET SQL_LOG_BIN=1`	Заново запускает регистрацию модификации, если пользователь имеет привилегию process.
`SET SQL_SLAVE_SKIP_COUNTER=n`	Пропустить следующие `n` событий от главной системы. Работает только, если подчиненный не запущен, иначе выдает ошибку. Полезно при ремонте сбоев.
`RESET MASTER`	Удаляет все двоичные файлы регистрации, перечисленные в индексном файле, очищая индексный файл binlog. В версиях до 3.23.26 называлась `FLUSH MASTER`
`RESET SLAVE`	Подчиненный забывает позицию репликации в главных файлах регистрации. В версиях до 3.23.26 называлась `FLUSH SLAVE`
`LOAD TABLE tblname FROM MASTER`	Загрузить копию таблицы с главной системы на подчиненную
`CHANGE MASTER TO master_def_list`	Изменяет параметры для значений, определенных в `master_def_list` и перезапускает подчиненный процесс. `master_def_list` представляет собой разделенный запятыми список `master_def`, где `master_def` один из элементов следующего перечня: `MASTER_HOST`, `MASTER_USER`, `MASTER_PASSWORD`, `MASTER_PORT`, `MASTER_CONNECT_RETRY`, `MASTER_LOG_FILE`, `MASTER_LOG_POS`. Например: CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4; Вы должны определить только значения, которые должны быть изменены. Значения, которые Вы опускаете, останутся теми же самыми, за исключением того случая, когда Вы изменяете главный компьютер или порт. В этом случае подчиненный считает, что, так как Вы соединяетесь с другим главным компьютером или иным портом, главная система поменялась. Следовательно, старые значения файла регистрации и позиции больше неприменимы и будут автоматически сброшены к пустой строке и 0 соответственно (это значения начала). Обратите внимание, что, если Вы перезапускаете подчиненный сервер, то он будет помнить последний главный сервер. Если это не нужно, Вы должны удалить файл `master.info` прежде, чем выполнить перезапуск, и подчиненный будет читать данные на главный сервер из файла `my.cnf` или из командной строки.
`SHOW MASTER STATUS`	Обеспечивает информацию состояния binlog главной системы.
`SHOW SLAVE STATUS`	Обеспечивает информацию состояния существенных параметров подчиненной системы.
`SHOW MASTER LOGS`	Вносит в список двоичные протоколы на главной системе. Доступно с версии 3.23.28. Вы должны использовать эту команду до `PURGE MASTER LOGS TO`.
`PURGE MASTER LOGS TO logname`	Удаляет все файлы регистрации репликаций, которые перечислены в индексе файла регистрации как находящиеся до определенного файла регистрации, и затем удаляет их из индекса файла регистрации так, чтобы данный файл регистрации теперь стал первым. Доступно с версии 3.23.28. Пример: PURGE MASTER LOGS TO mysql-bin.010 Эта команда ничего не будет делать, если Вы имеете дело с активным подчиненным сервером, который в настоящее время читает один из файлов регистрации, которые Вы пробуете удалять. Команда безопасна для выполнения в то время, когда все подчиненные копируют данные. Сначала выясните, какие файлы обрабатывает каждый подчиненный командой `SHOW SLAVE STATUS`, затем выясните очередь протоколов на главной системе командой `SHOW MASTER LOGS`, найдите самый старый протокол на подчиненных, зарезервируйте все подготовленные к удалению протоколы, если считаете это необходимым, и удалите все старые протоколы.

4.10.7 FAQ оп репликации

Q:Почему я иногда вижу больше, чем один процесс Binlog_Dump на главном сервере после того, как я перезапустил подчиненный ему сервер?

A: Binlog_Dump непрерывный процесс, который обрабатывается следующим способом:

Подхватываются обновления.
Если обновлений пока нет, вызывает pthread_cond_wait() и ждет появления обновлений или сигнала на завершение.
При пробуждении проверяет, что его разбудило. Если это не сигнал на завершение, продолжает цикл Binlog_dump.
Если имеется некоторая фатальная ошибка, завершает цикл.

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

Вышеупомянутая проблема не должна присутствовать в версии 3.23.26 и более поздних версиях. В версии 3.23.26 добавили идентификатор server-id на каждый сервер репликации, и теперь все старые процессы уничтожаются, когда новый процесс репликации соединяется с того же самого подчиненного сервера.

Q: Как обеспечить ротацию файлов регистрации?

A: В версии 3.23.28 Вы должны использовать команду PURGE MASTER LOGS TO после определения того, которые файлы регистрации могут быть удалены, и их предварительного факультативного резервирования. В более ранних версиях процесс намного сложнее и не может быть безопасно выполнен без выключения всех подчиненных систем. Вы должны остановить подчиненные системы, отредактировать двоичный индексный файл регистрации, удалить все старые файлы регистрации, перезапустить главную систему, запустить подчиненные системы и затем удалить старые журналы.

Q: Как проводить апгрейд при работающей репликации?

A: Если Вы апгрейдитесь с версий pre-3.23.26, Вы должны только блокировать главные таблицы, выполнить FLUSH MASTER на главной системе и FLUSH SLAVE на остальных для перезапуска протоколов. После этого надо перезапустить новые вресии на всех системах.

После версии 3.23.26 можно иметь разные версии MySQL на главной и подчиненной системах (не ниже 3.23.26). Дело в том, что теперь протокол связи для репликации утвержден и не меняется каждую версию.

Q: Какие проблемы я должен знать при установке двухсторонней репликации?

A: MySQL в настоящее время не поддерживает протокол блокировки между главным и подчиненным, чтобы гарантировать такую репликацию. На сегодняшний день при попытке такой репликации можно получить противоречивые таблицы, так что не следует этого делать.

Вы должны также понять, что двухсторонняя репликация фактически не улучшает эффективность работы.

Q: Как я могу использовать репликацию, чтобы улучшить эффективность моей системы?

A: Вы должны установить одну систему как главную, направить на нее все процессы записи и настроить так много подчиненных, как только можно, распределяя чтение между главной и подчиненными системами. Вы можете также запустить подчиненных с опциями --skip-bdb, --low-priority-updates и --delay-key-write-for-all-tables, чтобы получить рост быстродействия. В этом случае подчиненный использует не транзакционные таблицы MyISAM вместо таблиц BDB, чтобы получить большее быстродействие.

Q: Как модифицировать клиентский код для использования всех прелестей репликации?

A: Для начала писать правильно. Куски клиентов, отвечающие за связь с базой данных, должны быть абстрактными и модульными. В этом случае надо только изменить реализацию Вашего доступа к базе данных так, чтобы читать из некоторого подчиненного или главного сервера, но писать всегда на главный. Если Ваш код не имеет этого уровня абстракции, установка репликации подтолкнет Вас к написанию правильного кода. Можно начать с создания библиотеки с функциями:

safe_writer_connect()
safe_reader_connect()
safe_reader_query()
safe_writer_query()

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

Вы должны затем преобразовать Ваш код пользователя, чтобы использовать эту библиотеку. Это может быть сначала болезненным и страшным процессом, но со временем окупится. Код будет намного проще, и добавление параметров поиска неисправностей будет тривиально. Вы будете должны изменить только одну или две-три функции. Если готового кода много, можно попробовать автоматизировать преобразование с помощью утилиты Monty's replace, которая входит в стандартный дистрибутив MySQL, или написать свой скрипт на Perl. Хотелось бы надеяться, что Ваш код следует за некоторым распознаваемым образцом.

Q: В каких случаях и насколько репликация может ускорить работу моего сервера MySQL?

A: Репликация MySQL наиболее полезна для системы с частым чтением и относительно редкой записью. Чтобы определить, сколько подчиненных систем нужно, следует опытным путем для наиболее типичных запросов установить время чтения (max_reads) и время записи (max_writes). Пример ниже покажет Вам довольно упрощенное вычисление того, что Вы можете добиться с помощью репликации для нашей предполагаемой системы.

Скажем, наша загрузка системы состоит на 10% из записи (90% чтение), и мы определили, что max_reads=1200-2*max_writes, и связь линейна. Допустим, что все машины имеют одинаковый канал связи. Значит, мы имеем для каждой системы (N подчиненных и одна главная):

reads=1200-2*writes.

reads=9*writes/(N+1) (чтения разделены, но запись-то все равно идет на все серверы сразу через систему репликации).

9*writes/(N+1)+2*writes=1200.

writes=1200/(2+9/(N+1).

Если N=0, это означает, что мы не имеем никакой репликации, наша система может обрабатывать 1200/11, около 109 запросов на запись в секунду.

Если N=1, то наша система сможет обрабатывать уже до 184 запросов на запись в секунду.

Если N=8, дойдем до 400.

Если N=17, дойдем до 480.

В конечном счете, при приближении N к бесконечности (а нашего бюджета к минус бесконечности), мы сможем вплотную приблизиться к 600 запросам на запись в секунду, увеличивая производительность системы приблизительно в 5,5 раз. Однако, всего с 8 серверами удалось достичь роста почти в 4 раза.

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

Каково отношение чтения-записи в системе?
Если уменьшить число запросов на чтение, сервер сможет обработать больше запросов на запись. Сколько именно?
Какое максимальное число машин полностью загрузит Вашу сеть?

Q: Как я могу использовать репликацию, чтобы обеспечить высокую/избыточную доступность?

A: Надо поставить главный и подчиненный (или несколько подчиненных) серверы и написать скрипт, который мониторит главную систему. В случае проблем с ней, он должен перевести нагрузку на другую машину. Некоторые мои предложения:

Чтобы сообщить подчиненному. что он должен заменить главного, используйте команду CHANGE MASTER TO.
Хороший способ не трогать прикладные программы, это иметь динамическую запись в DNS для главной системы. В bind можно использовать nsupdate, чтобы динамически модифицировать Ваш DNS.
Вы должны запустить подчиненных с опцией log-bin, но без log-slave-updates. В этом случае подчиненный будет готов стать главным, как только Вы выдаете команды STOP SLAVE; RESET MASTER и CHANGE MASTER TO на других подчиненных системах. Это также поможет Вам захватить поддельные модификации, которые могут случаться из-за ошибок в настройке подчиненных.

4.10.8 Поиск неисправностей в репликации

Если все сделали правильно, но репликация не работает, сначала устраните фактор ошибки пользователя, проверяя следующее:

Главный сервер регистрирует в двоичный файл регистрации? Проверьте с помощью SHOW MASTER STATUS. Если все в норме, Position будет не нулевой. Если она равна нулю, проверьте опцию log-bin и настройку server-id.
Подчиненный запущен? Проверьте с помощью SHOW SLAVE STATUS. Ответ будет в столбце Slave_running. Если он отрицательный, проверьте протокол ошибок и параметры подчиненного.
Если подчиненный работает, связался ли он с главной системой? Выполните SHOW PROCESSLIST, найдите поток со значением system user в столбце User и none в столбце Host. Проверьте столбец State. Если там connecting to master, следует проверить привилегии для пользователя репликации на главной системе, имя главного хоста, настройки DNS, работает ли фактически главный сервер, достижим ли он с подчиненного и почитать протокол ошибок.
Если подчиненный работал, но затем остановился, рассмотрите вывод SHOW SLAVE STATUS и файлы регистрации ошибок. Это обычно случается, когда некоторый запрос нормально прошел на главной системе, но свалился на подчиненной. Это никогда не должно случиться, если Вы скопировали архив с главной системы правильно и никогда не изменяете данные относительно подчиненного вне подчиненного процесса. Если все и тут в порядке, значит, Вы нашли сбой в системе. Поздравляю! Как сообщить об этом событии, читайте ниже.
Если запрос успешно выполнился на главной системе, но отказывается выполняться на подчиненной, и полная синхронизация базы данных в данный момент невозможна, попробуйте следующее:
- Сначала посмотрите, имеется ли некоторая беспризорная запись в пути. Поймите, как она там оказалась, удалите ее и выполните SLAVE START.
- Если вышеупомянутое не работает, попробуйте понять, безопасно ли делать модификацию вручную (если необходимо) и затем игнорировать следующий запрос от главной системы.
- Если Вы решили, что Вы можете пропускать следующий запрос, выполните SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START;, чтобы пропустить запрос, который не использует auto_increment или last_insert_id, или задайте SET SQL_SLAVE_SKIP_COUNTER=2; SLAVE START; в противном случае. Причина особой обработки запросов с auto_increment/last_insert_id в том, что они берут два события в двоичном файле регистрации главной системы.
- Если Вы уверены в том, что подчиненный синхронизирован с главным сервером, и никто не модифицировал таблицы, сообщите об ошибке.
Удостоверьтесь, что Вы не сталкиваетесь со старой ошибкой при апгрейде.
Если все еще ничего не выходит, почитайте файлы регистрации ошибок. Если они большие, скомандуйте grep -i slave /path/to/your-log.err на подчиненном. Нет никакого универсального образца, чтобы искать на главном сервере, так как единственные ошибки, которые он регистрирует, общие ошибки системы, а этого мало.

Когда Вы определили, что у Вас все в норме, но репликация все равно не работает и работать не собирается, пришло время составить отчет об ошибке. Мы должны получить от Вас так много информации, насколько возможно, чтобы проследить ошибку. Пожалуйста, приложите некоторые усилия, готовя хороший отчет! Идеально, мы хотели бы иметь случай теста в формате, найденном в каталоге mysql-test/t/rpl* исходного текста. Если Вы представляете на рассмотрение случай в таком виде, Вы можете ожидать патч в течение нескольких дней.

Вторая возможность: программа, которая покажет ошибку на наших системах. Вы можете писать на Perl или на C.

Если Вы применяете один из вышеупомянутых способов, чтобы показать ошибку, используйте mysqlbug, чтобы подготовить отчет об ошибке и отправьте его на bugs@lists.mysql.com. Если Вы имеете фантом (проблему, которая происходит, но Вы не можете дублировать ее по желанию):

Выполните подчиненного с опциями log-slave-updates и log-bin: он будет хранить файл регистрации всех модификаций на подчиненном.
Сохраните все доказательства перед сбросом репликации. Доказательства, которые Вы должны будете собрать:
- Все двоичные регистрации главного сервера.
- Все двоичные регистрации подчиненного сервера.
- Вывод SHOW MASTER STATUS с главного сервера во время, когда Вы обнаружили проблему.
- Вывод SHOW SLAVE STATUS с главного сервера во время, когда Вы обнаружили проблему.
- Файлы регистрации ошибок на главном и на подчиненном серверах.
Используйте mysqlbinlog, чтобы исследовать двоичные файлы регистрации. Следующая команда должна быть полезна, чтобы найти запрос проблемы, например:
```
mysqlbinlog -j pos_from_slave_status \
            /path/to/log_from_slave_status | head
```

Как только Вы собрали доказательства по проблеме фантома, попробуйте сначала изолировать их в отдельном случае теста. Затем сообщите проблему на bugs@lists.mysql.com с настолько подробной информацией, насколько это возможно.