MySQL Slave застрял на одном журнале бина + позиция бина журнала на 17+ часов
tl;dr: Репликация остановлена на определенном бинлоге и позиции, и я не знаю почему
У меня есть настройка репликации MySQL с MySQL 5.5.
Эта настройка репликации никогда не отставала и всегда была надежной.
Этим утром я заметил, что Раб отстает от Мастера на 17 часов.
Проводя дополнительные исследования, похоже, проблема с SQL_Thread.
Текущий главный файл журнала, в соответствии с ведомым (через SLAVE STATUS), является mysql-bin.001306 @ позиция 20520499, Это соответствует MASTER STATUS вывод от мастера.
Тем не мение, SLAVE STATUS показывает, что Relay_Master_Log_File Сейчас mysql-bin.001302 с Exec_Master_Log_Pos из 36573336, Relay_Master_Log_File ни Exec_Master_Log_Pos продвинулся вообще, пока я следил за ними сегодня утром.
Глядя в binlogs на master, это заявление, расположенное на mysql-bin.001302@3657336:
# at 36573053
#170221 14:33:48 server id 1 end_log_pos 36573130 Query thread_id=96205677 exec_time=0 error_code=0
SET TIMESTAMP=1487716428/*!*/;
BEGIN
/*!*/;
# at 36573130
# at 36573213
#170221 14:33:48 server id 1 end_log_pos 36573213 Table_map: `database-name`.`table-name` mapped to number 5873
#170221 14:33:48 server id 1 end_log_pos 36573309 Write_rows: table id 5873 flags: STMT_END_F
### INSERT INTO `database-name`.`table-name`
### SET
### @1='xxxxxxxx'
### @2=6920826
### @3='xxxxxxxx'
### @4='GET'
### @5='address'
### @6=2017-02-21 14:40:24
### @7=2017-02-21 14:40:24
# at 36573309
#170221 14:33:48 server id 1 end_log_pos 36573336 Xid = 1668637037
COMMIT/*!*/;
# at 36573336
Примерно в это же время вчера я выполнил несколько больших запросов, чтобы перенести данные в новую таблицу. Процесс выглядел примерно так;
mysql> insert into tmp_table ( select <rows> from origin table ); -- 44 million rows
mysql> insert into dest_table ( select * from tmp_table ); -- 44 million rows
В двух рассматриваемых таблицах не было первичного или уникального ключа, что, как я читал, могло быть проблемой. Однако, хотя база данных + таблица, показанная в записи binlog выше, является здесь таблицей назначения - показанная запись вставки не была сгенерирована во время миграции.
Если вы зашли так далеко, вы заслуживаете интернет-очков.
На данный момент, я не уверен, что еще нужно рассмотреть или где еще искать, чтобы найти причину остановки журнала. Любое понимание приветствуется.
Благодарю.
Для справки, вот MASTER STATUS а также SLAVE STATUS вывод на момент публикации:
МАСТЕР СТАТУС
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.001306 | 20520499 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
СТАТУС РАБА
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master-host
Master_User: replication-user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001306
Read_Master_Log_Pos: 20520499
Relay_Log_File: relay-bin.002601
Relay_Log_Pos: 36573482
Relay_Master_Log_File: mysql-bin.001302
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 36573336
Relay_Log_Space: 3565987462
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 63435
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
1 ответ
Я был на правильном пути здесь с большими транзакциями запроса со вчерашнего дня.
После переноса данных я выполнил инструкцию DELETE в исходной таблице, чтобы избавиться от перенесенных строк.
Эти таблицы просто полны данных отслеживания и, следовательно, не имеют первичных или уникальных ключей.
Из-за того, как работает репликация на основе ROW, подчиненное устройство вместо выполнения идентичного оператора DELETE, которое было выполнено на главном сервере, выполняет оператор DELETE для каждой строки, что в итоге выглядит примерно так:
DELETE FROM table WHERE colA=foo AND colB=bar AND colC=baz....etc
И, поскольку нет индекса, соответствующего этому запросу, однопотоковый поток SQL-репликации выполнил 40 миллионов + операторов удаления (или... пытался), на выполнение которых потребовалось много времени из-за всех проверок, которые должны были быть выполнены. сделано для идентификации каждой строки (в то время таблица имела размер около 80 миллионов строк).
В конце концов я справился с этим, остановив подчиненную нить (STOP SLAVE) пропуск одной подчиненной транзакции (SET GLOBAL sql_slave_skip_counter = 1;) и перезапуск ведомой нити (START SLAVE).
Это привело к тому, что мои Master и Slave не синхронизировались с рассматриваемой здесь таблицей, но я смог использовать природу репликации на основе строк, чтобы восстановить синхронизацию, выполнив на Master следующее:
mysql> CREATE TABLE table_tmp; -- with the same schema as 'table' (SHOW CREATE TABLE table;)
mysql> RENAME TABLE table TO table_bak, table_tmp TO table;
mysql> INSERT INTO table ( SELECT * FROM table_bak );
mysql> DROP TABLE table_bak;
Поскольку УДАЛЕНИЕ было выполнено на Мастере, ВСТАВКА здесь вставила только те записи, которые я хотел сохранить (удаленные исчезли). И поскольку репликация на основе строк вставляет каждую строку отдельно, а не выполняет один и тот же оператор INSERT INTO...SELECT, в ведомой таблице были заполнены только нужные данные. Затем последующий оператор DROP TABLE удаляет таблицу на ведомом устройстве, не обращаясь к каждой строке отдельно.
Предостережение заключается в том, что, поскольку основная версия таблицы все еще содержит 30-40 миллионов строк... ВСТАВКА и последующая репликация заканчивают тем, что на некоторое время блокируют ваше ведомое устройство (дублируя проблему выше), но это гораздо более короткая задержка (в итоге это заняло около 20 минут) из-за того, что mysql не нужно сканировать в базе данных строки для удаления.
Я надеюсь, что это может помочь кому-то в будущем. Извините, что наматывается, надеюсь, что это было информативно и полезно.