Mysql slave остановлен и не удалось инициализировать журнал ретрансляции

Пару дней назад я настроил сервер слейва MySQL (Mysql Community Server 8.0.21). Пока мы используем его только для резервного копирования с помощью mysqldump. Я заметил, что он использует слишком много памяти, но меня это не беспокоило, потому что я использовал несколько параметров конфигурации, которые автоматически регулируют количество используемой памяти, и VPS был выделен для сервера MySQL (если я плачу за всю память, почему бы не использовать её, верно?).

Сегодня, с утра, я зашел в Zabbix и заметил, что слейв не работает, поэтому я вошел в него по ssh, перезапустил MySQL и попытался запустить слейва с помощью START SLAVE. И вот что я получил:

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Не уверен, что вызвало проблему с самого начала (это мой my.cnf)

[client]
#    Используется только для конкретных случаев
port                            = 3306      # Остановленный порт
socket                          = /var/run/mysqld/mysqld.sock

[mysql]
#    Настройки клиента
auto-rehash                                 # Автозаполнение

[mysqld]
#    Настройки сервера
pid_file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/run/mysqld/mysqld.sock
datadir                         = /var/lib/mysql
log_error                       = /var/log/mysql/error.log
user                            = mysql
bind_address                    = 0.0.0.0   # Слушает на всех адресах

#    Общие настройки сервера
max_allowed_packet              = 32M       # Максимальный размер пакета.
max_connections                 = 2000      # Максимум соединений
open_files_limit                = 10000     # Максимум открытых файлов
tmp_table_size                  = 64M       # Ограничение размера таблицы в памяти
max_heap_table_size             = 64M       # Ограничение размера таблицы в памяти
tmpdir                          = /tmp      # Каталог /tmp/
default_storage_engine          = InnoDB    # Дефолтный движок
skip_name_resolve                           # Отключает разрешение DNS

#     Настройки бинов
log_bin                         = mysql-bin # Файл бинарного лога
relay-log                       = mysql-relay-bin
log-slave-updates               = 1         # Журнал обновлений на слейве
read-only                       = 1         # Только для чтения
binlog-format                   = mixed     # Формат
server_id                       = 2         # Идентификатор сервера для лога
max_binlog_size                 = 256M      # Максимальный размер бинарного лога
binlog_expire_logs_seconds      = 604800    # Максимальное время бинарного лога
innodb_flush_log_at_trx_commit  = 1         # Защита данных
sync_binlog                     = 1         # Только для репликации

#    Специфические настройки InnoDB
innodb_dedicated_server         = ON        # Автоконфигурация InnoDB
innodb_io_capacity              = 2000      # Сколько записей в секунду
innodb_read_io_threads          = 64        # Потоки чтения
innodb_write_io_threads         = 64        # Потоки записи
innodb_thread_concurrency       = 0         # Автоопределение потоков

#    Журнал медленных запросов 
slow_query_log                  = 1         # Хранит медленные запросы
long_query_time                 = 1.0       # Время запроса

#    Функции в MySQL
log_bin_trust_function_creators = 1;        # Позволяет созданные функции

Я не знаю, как это исправить. Должен ли я делать полный дамп еще раз? Как я могу предотвратить это в будущем?

ОБНОВЛЕНИЕ:

Это результат SHOW SLAVE STATUS:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: IP_ADDR
                  Master_User: USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 178887867
               Relay_Log_File: pergamum-relay-bin.000029
                Relay_Log_Pos: 178888082
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 13124
                   Last_Error: Slave failed to initialize relay log info structure from the repository
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 178887867
              Relay_Log_Space: 0
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 13124
               Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: bef45e1b-99d6-11ea-a355-3e2547e4f083
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 200819 09:58:32
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ОБНОВЛЕНИЕ

Как спрашивали, вот переменные с обоих серверов.

МАСТЕР

GLOBAL STATUS:
https://gist.github.com/IamRichter/ef4993bbf65883baa366ff30d73b9644
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/dbf08facd364548529b07bc0cbd6b2e6

СЛЕЙВ

GLOBAL STATUS:
https://gist.github.com/IamRichter/fcdff1111ed52fc859ebac46a2dbfc27
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/53c5638fb3c6064fd51eab332660f07f

Увы, ПЕРЕМЕННЫЕ/СТАТУС не показывают никаких очевидных причин проблемы, с которой вы сталкиваетесь. Возможно, высокий buffer_pool_size слишком сильно загружает оперативную память, но я в этом сомневаюсь. Вот мои анализы (первичный, затем реплика):

Наблюдения:

• Версия: 8.0.20
• 16 ГБ ОЗУ
• Время работы = 2д 02:30:41
• Вы не работаете на Windows.
• Запущена 64-битная версия
• Вы, похоже, используете полностью (или в основном) InnoDB.

Более важные проблемы:

Увеличьте table_open_cache, если ОС позволит. Почему так много таблиц??

Я думаю (без каких-либо доказательств), что неразумно использовать что-либо кроме “2” для innodb_log_files_in_group; MariaDB уже игнорирует это; я не слышал о Oracle.

Есть несколько указаний на плохо сформулированные запросы. См. http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog для анализа медленных журналов.

У вас была какая-то причина для использования binlog_format=MIXED?

Детали и другие наблюдения:

( Opened_tables ) = 452,365 / 181841 = 2.5 /sec — Частота открытия таблиц
— увеличьте table_open_cache (сейчас 3995)

( Table_open_cache_overflows ) = 448,306 / 181841 = 2.5 /sec
— Возможно, необходимо увеличить table_open_cache (сейчас 3995)

( Table_open_cache_misses ) = 452,365 / 181841 = 2.5 /sec
— Возможно, необходимо увеличить table_open_cache (сейчас 3995)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 — Объем работы для очистителей страниц каждую секунду.
— “InnoDB: page_cleaner: 1000ms intended loop took …” может быть исправлено снижением lru_scan_depth: Учитывайте 1000 / innodb_page_cleaners (сейчас 4). Также проверьте на свопинг.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 — innodb_page_cleaners
— Рекомендуется установить innodb_page_cleaners (сейчас 4) в innodb_buffer_pool_instances (сейчас 8)

( innodb_lru_scan_depth ) = 1,024
— “InnoDB: page_cleaner: 1000ms intended loop took …” может быть исправлено снижением lru_scan_depth

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 2,102,149 / 7377368 = 28.5% — Запросы на запись, которые должны были попасть на диск
— Проверьте innodb_buffer_pool_size (сейчас 12884901888)

( innodb_log_files_in_group ) = 9 — Количество файлов логов InnoDB
— 2, вероятно, единственное разумное значение.
Большое количество может вызвать проблемы с производительностью.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,773,668,352 / (181841 / 3600) / 9 / 1024M = 0.00363 — Соотношение
— (см. минуты)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 181,841 / 60 * 1024M / 1773668352 = 1,834 — Минуты между ротациями логов InnoDB Начиная с 5.6.8, это можно менять динамически; не забудьте также изменить my.cnf.
— (Рекомендация в 60 минут между ротациями несколько произвольна.) Регулируйте innodb_log_file_size (сейчас 1073741824). (Невозможно изменить в AWS.)

( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC — Как InnoDB должен просить ОС записать блоки. Предлагаю O_DIRECT или O_ALL_DIRECT (Percona), чтобы избежать двойного буферизирования. (По крайней мере для Unix.) См. chrischandler для предостережения о O_ALL_DIRECT

( Innodb_row_lock_time_avg ) = 1,886 — Среднее время блокировки строки (миллисек)
— Возможно, конфликтующие запросы; возможно, сканирование таблицы.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF — Записывать ли все взаимные блокировки.
— Если вас мучают взаимные блокировки, включите это. Осторожно: если у вас много взаимных блокировок, это может записывать много на диск.

( max_connections ) = 2,000 — Максимальное количество соединений (потоков). Влияет на различные назначения.
— Если max_connections (сейчас 2000) слишком высок, и различные настройки памяти высоки, вы можете столкнуться с нехваткой ОЗУ.

( Handler_read_rnd_next / Com_select ) = 46,219,107,293 / 7120289 = 6,491 — Среднее количество строк, проверенных при SELECT. (приблизительно)
— Рассмотрите возможность увеличения read_buffer_size (сейчас 131072)

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (358564 + 419704 + 462 + 0) / 2006877 = 0.388 — Операции на Commit (предполагается, что все InnoDB)
— Низкое значение: может помочь сгруппировать запросы вместе в транзакции; Высокое значение: длинные транзакции нагружают различные компоненты.

( Select_scan ) = 4,386,291 / 181841 = 24 /sec — полное сканирование таблицы
— Добавьте индексы / оптимизируйте запросы (если они не маленькие таблицы)

( Select_scan / Com_select ) = 4,386,291 / 7120289 = 61.6% — % выборок, выполняющих полное сканирование таблицы. (Может быть введёно в заблуждение сохранёнными процедурами.)
— Добавьте индексы / оптимизируйте запросы

( binlog_format ) = binlog_format = MIXED — STATEMENT/ROW/MIXED.
— ROW предпочитается в 5.7 (10.3)

( Aborted_clients ) = 175,398 / 181841 = 0.96 /sec — Потоки, сбросившие соединение из-за wait_timeout
— Увеличьте wait_timeout (сейчас 28800); будьте добры, используйте отключение

( Connections ) = 3,544,963 / 181841 = 19 /sec — Соединения
— Увеличьте wait_timeout (сейчас 28800); используйте пул соединений?

Аномально малые:
Open_files = 3

Аномально большие:
Com_do = 0.02 /HR
Com_release_savepoint = 2.3 /HR
Com_rollback_to_savepoint = 0.11 /sec
Com_savepoint = 2.3 /HR
Com_show_charsets = 0.44 /HR
Com_show_create_db = 2.3 /HR
Com_show_create_func = 2.5 /HR
Com_show_create_proc = 1.5 /HR
Com_show_create_trigger = 0.12 /HR
Com_show_function_status = 2.5 /HR
Com_show_procedure_status = 2.5 /HR
Com_show_table_status = 0.11 /sec
Com_show_triggers = 0.11 /sec
Created_tmp_files = 20 /sec
Handler_read_key = 139335 /sec
Handler_read_next = 316713 /sec
Handler_read_rnd = 46465 /sec
Handler_savepoint = 2.3 /HR
Handler_savepoint_rollback = 0.11 /sec
Innodb_buffer_pool_read_requests = 1095747 /sec
Innodb_num_open_files = 3,996
Innodb_system_rows_inserted = 0.97 /HR
Innodb_system_rows_read = 1.99e+7
Innodb_system_rows_updated = 0.095 /sec
Open_table_definitions = 2,000
Select_full_range_join = 2.1 /sec
Select_full_range_join / Com_select = 5.5%
back_log / max_connections = 100.0%
innodb_max_dirty_pages_pct_lwm = 10
innodb_read_io_threads = 64
innodb_undo_tablespaces = 2
innodb_write_io_threads = 64
max_error_count = 1,024
max_length_for_sort_data = 4,096

Аномальные строки:
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
innodb_dedicated_server = ON
innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN

sf1030756-M

Наблюдения:

• Версия: 8.0.21
• 6 ГБ ОЗУ
• Время работы = 5д 23:59:45
• Вы уверены, что это был SHOW GLOBAL STATUS? — Или эта реплика просто не занята??
• Вы не работаете на Windows.
• Запущена 64-битная версия
• Вы, похоже, используете полностью (или в основном) InnoDB.

Более важные проблемы:

Как и в первичном — table_open_cache, innodb_log_files_in_group, binlog_format

Проверьте наличие свопинга. innodb_buffer_pool_size довольно велик для 6 ГБ ОЗУ.

Уменьшите max_connections до 100 — Вы не превышали 5 соединений до сих пор; текущие 2000 — это дополнительная нагрузка на ОЗУ.

Детали и другие наблюдения:

( table_open_cache ) = 3,995 — Количество дескрипторов таблиц для кэширования
— Несколько сотен обычно хорошо.

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 56,436 / (1107115 + 56436) = 4.9% — Эффективность table_open_cache.
— Увеличьте table_open_cache (сейчас 3995) и проверьте table_open_cache_instances (сейчас 16).

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 — Объем работы для очистителей страниц каждую секунду.
— “InnoDB: page_cleaner: 1000ms intended loop took …” может быть исправлено снижением lru_scan_depth: Учитывайте 1000 / innodb_page_cleaners (сейчас 4). Также проверьте на свопинг.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 — innodb_page_cleaners
— Рекомендуется установить innodb_page_cleaners (сейчас 4) в innodb_buffer_pool_instances (сейчас 8)

( innodb_lru_scan_depth ) = 1,024
— “InnoDB: page_cleaner: 1000ms intended loop took …” может быть исправлено снижением lru_scan_depth

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 25,132 / 57003 = 44.1% — Запросы на запись, которые должны были попасть на диск
— Проверьте innodb_buffer_pool_size (сейчас 5368709120)

( innodb_log_files_in_group ) = 5 — Количество файлов логов InnoDB
— 2, вероятно, единственное разумное значение.
Большое количество может вызвать проблемы с производительностью.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 22,224,896 / (518385 / 3600) / 5 / 512M = 5.7e-5 — Соотношение
— (см. минуты)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 518,385 / 60 * 512M / 22224896 = 208,704 — Минуты между ротациями логов InnoDB Начиная с 5.6.8, это можно менять динамически; не забудьте также изменить my.cnf.
— (Рекомендация в 60 минут между ротациями несколько произвольна.) Регулируйте innodb_log_file_size (сейчас 536870912). (Невозможно изменить в AWS.)

( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC — Как InnoDB должен просить ОС записать блоки. Предлагаю O_DIRECT или O_ALL_DIRECT (Percona), чтобы избежать двойного буферизирования. (По крайней мере для Unix.) См. chrischandler для предостережения о O_ALL_DIRECT

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF — Записывать ли все взаимные блокировки.
— Если вас мучают взаимные блокировки, включите это. Осторожно: если у вас много взаимных блокировок, это может записывать много на диск.

( max_connections ) = 2,000 — Максимальное количество соединений (потоков). Влияет на различные назначения.
— Если max_connections (сейчас 2000) слишком высок, и различные настройки памяти высоки, вы можете столкнуться с нехваткой ОЗУ.

( (Com_show_create_table + Com_show_fields) / Questions ) = (8605 + 17405) / 313218 = 8.3% — Непослушная структура — тратит много усилий на повторное открытие схемы.
— Пожалуйтесь поставщику 3-й стороны.

( tmp_table_size ) = 64M — Ограничение на размер MEMORY временных таблиц, используемых для поддержки SELECT
— Уменьшите tmp_table_size (сейчас 67108864), чтобы избежать нехватки ОЗУ. Возможно, не более чем 64M.

( Select_full_join / Com_select ) = 8,884 / 123363 = 7.2% — % выборок, которые являются объединениями без индекса
— Добавьте подходящие индексы к таблицам, используемым в объединениях.

( Select_scan / Com_select ) = 107,352 / 123363 = 87.0% — % выборок, выполняющих полное сканирование таблицы. (Может быть введёно в заблуждение сохранёнными процедурами.)
— Добавьте индексы / оптимизируйте запросы

( binlog_format ) = binlog_format = MIXED — STATEMENT/ROW/MIXED.
— ROW предпочитается в 5.7 (10.3)

( log_slow_slave_statements ) = log_slow_slave_statements = OFF — (5.6.11, 5.7.1) По умолчанию, реплицированные операторы не будут отображаться в медленном журнале; это заставляет их отображаться.
— Это может быть полезно в медленном журнале, чтобы увидеть записи, которые могут мешать чтениям реплики.

( thread_cache_size / Max_used_connections ) = 28 / 5 = 560.0%
— Нет смысла делать кэш потоков больше, чем ваше вероятное количество соединений. Потеря пространства – это недостаток.

( thread_stack * max_connections ) = (286720 * 2000) / 6144M = 8.9% — Минимальное назначение памяти для max_connections.
— Уменьшите max_connections (сейчас 2000)

Аномально малые:
Bytes_received = 58 /sec
Com_insert = 0
Com_update = 0
Created_tmp_files = 0.035 /HR
Handler_update = 2.2 /HR
Innodb_buffer_pool_write_requests = 0.11 /sec
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2.27
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 1
Innodb_pages_created = 1 /HR
Innodb_rows_deleted + Innodb_rows_inserted = 0
Innodb_rows_inserted = 0
Innodb_rows_updated = 0
Select_range = 0
Select_range / Com_select = 0

Аномально большие:
Com_do = 0.014 /HR
Com_show_create_func = 0.15 /HR
Com_slave_start = 0.028 /HR
Innodb_num_open_files = 3,996
Innodb_system_rows_read = 1.91e+7
Innodb_system_rows_updated = 2.2 /HR
Open_table_definitions = 2,000
back_log / max_connections = 100.0%
innodb_max_dirty_pages_pct_lwm = 10
innodb_read_io_threads = 64
innodb_undo_tablespaces = 2
innodb_write_io_threads = 64
max_error_count = 1,024
max_length_for_sort_data = 4,096
optimizer_trace_offset = –1
performance_schema_error_size = 4,772
performance_schema_max_cond_classes = 100
performance_schema_max_mutex_classes = 300
performance_schema_max_rwlock_classes = 60
performance_schema_max_stage_classes = 175
performance_schema_max_statement_classes = 218
performance_schema_max_thread_classes = 100

Аномальные строки:
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
innodb_dedicated_server = ON
innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
read_only = ON
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN

sf1030756-S