Ведомое устройство MySQL застряло в журнале одного бункера + позиция журнала бункера более 17 часов
tl; dr: Репликация остановлена на определенном бинарном журнале и позиции, и я не уверен, почему
у меня есть установка репликации MySQL с MySQL 5.5.
Эта репликационная установка никогда не отставала и всегда была надежной.
Этим утром я заметил, что Раб отставал от Мастера на 17 часов.
Проведя дополнительные исследования, он выглядит как проблема с SQL_Thread.
Текущий главный файл журнала, согласно ведомому (через СОСТОЯНИЕ ПОДЧИНЕННОГО ), - это mysql-bin.001306 @ position 20520499 . Это соответствует выводу MASTER STATUS главного устройства.
Однако, СОСТОЯНИЕ ПОДЧИНЕНИЯ показывает, что Relay_Master_Log_File в настоящее время является mysql-bin.001302 с Exec_Master_Log_Pos из 36573336. Ни Relay_Master_Log_File , ни Exec_Master_Log_Pos вообще не продвинулись, пока я их отслеживал сегодня утром.
Заглядывая в бинлоги на главном сервере, это утверждение, расположенное по адресу 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
Две рассматриваемые таблицы не имели первичного или уникального ключа, что, как я читал, могло быть проблемой. Однако, хотя таблица «база данных +», показанная в записи бинарного журнала выше, является здесь целевой таблицей - показанная запись вставки не была создана во время миграции.
Если вы зашли так далеко, вы заслуживаете очков в Интернете.
На данный момент я не уверен, что еще нужно учесть или где еще искать причину остановки журнала. Любое понимание приветствуется.
Спасибо.
Для справки, вот результаты 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)
Я был на правильном пути здесь с большими транзакциями запросов, сделанными вчера.
После переноса данных я выполнил оператор 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 ).
Это привело к тому, что мои Мастер и Подчиненный не синхронизировались в рассматриваемой здесь таблице - но я смог использовать природу репликации на основе строк, чтобы вернуть ее в синхронизацию, выполнив на Мастере следующее:
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;
Поскольку DELETE был выполнен на Мастере, INSERT здесь вставлял только те записи, которые я хотел сохранить (удаленные пропали). И, поскольку репликация на основе строк вставляет каждую строку отдельно, а не выполняет тот же оператор INSERT INTO ... SELECT, таблица Slave была заполнена только желаемыми данными. Затем последующий оператор DROP TABLE удаляет таблицу на ведомом устройстве, не обращаясь к каждой строке индивидуально.
Предостережение заключается в том, что, поскольку в главной версии таблицы все еще было 30-40 миллионов строк ... INSERT и последующие репликация заканчивается тем, что ваше ведомое устройство на некоторое время блокируется (дублирование проблемы, описанной выше), но это гораздо более короткая остановка (в итоге составила около 20 минут) из-за того, что mysql не должен сканировать базу данных для удаления строк.
I надеюсь, это может кому-то помочь в будущем. Приносим извинения, но надеюсь, что это было информативным и полезным.