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 вообще продвинулась, пока я наблюдал за ними сегодня утром.
Заглянув в журналы на главном сервере, это утверждение находится по адресу 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 не сканировал базу данных на предмет удаления строк.
Я надеюсь, что это может кому-то помочь в будущем. Извините, это запыхалось, надеюсь, оно было информативным и полезным.