MySQL: Slave could not parse relay log event entry

При использованиии MySQL Master-Slave (или же Master-Master) репликации бывает ситуация что по разным причинам версия слейва ниже чем версия мастера.
Если при этом используется опция binlog_format=MIXED, в результате возможна ситуация когда мастер запишет в свой binlog запись вида:
BINLOG 'XMawShMEAAAARwAAAEp5UwUAAGkAAAAAAAAACHNhblahblahblah'
Которую слейв не сможет выполнить, ибо он ее попросту “еще не умеет”.
Первой ласточкой того, что это произошло станет сообщение “SLAVE DOWN” от мониторинговой системы и запись в MySQL-логе слейва сродни этой:
090917 14:55:51 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 71, event_type: 19
090917 14:55:51 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
090917 14:55:51 [ERROR] Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' o
n the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL
code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0
090917 14:55:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin-us1.000022' position 17007149

Попытки пропустить неугодные записи старым добрым методом:
slave stop;
set global sql_slave_skip_counter=1;
slave start;
в данном случае не помогают.
Как вариант решения предлагаю:

1. Останавливаем slave: “SLAVE STOP;”
2. “Выуживаем” из текста ошибки имя бинлога и позицию, на которой произошла ошибка.
   В данном случае это bin-us1.000022 и позиция 17007149
3. На мастере выполняем запрос “FLUSH LOGS;”
4. Делаем дамп бинлога начиная с найденной позиции:
   mysqlbinlog --start-position=17007149 bin-us1.000022  >  bin-us1-from17007149.sql
5. Загружаем полученный дамп на слейве с ключем “–force”
   mysql --force -uuser -ppassword database < bin-us1-from17007149.sql
6. Выполняем на слейве “FLUSH LOGS;”
7. В data-директории находим relay-log с максимальным номером и размером около 100 байт (в моем случае это mysqld-relay-bin.000037)
8. В той же директории в файле relay-log.info
   первую строку заменяем на имя только что найденного relay-log-а, во вторую вписываем 0, в третью – следующий после “испорченного” бинлог, в четвертую – 0
   Получится что-то вроде:
   /var/lib/mysql/mysqld-relay-bin.000037
0
bin-us1.000023
0
9. Дальше на слейве выполняем:
   CHANGE MASTER TO MASTER_LOG_FILE='bin-us1.000023', MASTER_LOG_POS=0;
SLAVE START;
10. Ну и потом чтоб удостоверится что все “завелось”
    SHOW SLAVE STATUS\G

Должно работать.
Подозреваю что пункты 4 и 5 можно обьеденить в один, более простой и элегантный.
Но как это сделать – не гуглил.

Оригинал

Репликация MySQL на уже работающем сервере

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

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

На главном сервере.

Надо поправить файл /etc/mysql/my.cnf:

[mysqld]

# Необходимо открыть доступ к серверу по сети,

# что бы slave имел возможность подключиться

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

bind-address            = 0.0.0.0

server-id               = 1

log_bin                 = /var/log/mysql/mysql-bin.log

expire_logs_days        = 10

max_binlog_size         = 100M

# Исключим ненужные БД

#binlog_do_db           = include_database_name

binlog_ignore_db        = information_schema

binlog_ignore_db        = mysql

binlog_ignore_db        = test

# Это рекомендуется для более стабильной работы репликации баз на InnoDB

innodb_flush_log_at_trx_commit=1

sync_binlog=1

Перезапускаем mysql:

/etc/init.d/mysql restart

Выдаем slave права на репликацию:

mysql -u root

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.0.0.2' IDENTIFIED BY 'slave_password';

FLUSH PRIVILEGES;

Посмотреть статус можно командой:

SHOW MASTER STATUS;

+------------------+----------+--------------+-------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

+------------------+----------+--------------+-------------------------------+

| mysql-bin.000015 |       98 |              | information_schema,mysql,test |

+------------------+----------+--------------+-------------------------------+

1 row in set (0.00 sec)

Далее делаем дамп существующих баз данных. Ключи –single-transaction –master-data –flush-logs должны глобально заблокировать на время дампа базу данных на запись и вписать в дамп номер и позицию лога, с которых надо начать репликацию на slave. Это обеспечит точность совпадения данных на главном и slave серверах, даже если с главным сервером в этот момент идет работа и записываются данные.

mysqldump -u root --single-transaction --master-data --flush-logs --databases db1 db2 db3 > ALL_DB.sql

Если посмотреть внутрь ALL_DB.sql, то там должна присутствовать строка:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=98;

Далее копируем дамп на slave:

scp ALL_DB.sql 10.0.0.2:

Продолжаем на втором сервере.

Убеждаемся что там нет остатков настроек репликации, в принципе, если на сервере нет нужных данных, не помешает переустановить mysql с очисткой папок БД.

Так как начиная с версии MySQL 5.2 параметры доступа для репликации убираются из my.cnf сразу перейдем на использование такой схемы.

Правим файл /etc/mysql/my.cnf:

[mysqld]

# ID slave должен отличаться от главного сервера

server-id               = 2

replicate-ignore-db     = information_schema

replicate-ignore-db     = mysql

replicate-ignore-db     = test

relay-log               = /var/log/mysql/mysqld-relay-bin.log

Перезапускаем сервер:

/etc/init.d/mysql restart

Поправим файл ALL_DB.sql:

Дописываем к строке

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=98;

параметры доступа:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=98, MASTER_HOST='10.0.0.1', MASTER_USER='replication',

MASTER_PASSWORD='slave_password', MASTER_CONNECT_RETRY=60;

Загружаем данные:

mysql -u root < ALL_DB.sql

Проверяем состояние репликации, она НЕ должна быть запущена (Slave_IO_Running, Slave_SQL_Running: no, no):

mysql -u root

show slave status;

Запускаем репликацию (теперь должно быть Slave_IO_Running, Slave_SQL_Running: yes, yes):

START SLAVE;

show slave status;

Для проверки можно попробовать перезапустить mysql и проверить состояние репликации еще раз:

/etc/init.d/mysql restart

mysql -u root

show slave status;

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

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