Hace un par de días configuré un servidor MySQL esclavo (Mysql Community Server 8.0.21). Hasta ahora solo lo usamos para realizar copias de seguridad usando mysqldump. Me di cuenta de que estaba usando demasiada memoria, pero no me importó porque usé algunos parámetros de configuración que regulan automáticamente la cantidad de memoria que usaría, y el VPS estaba dedicado al servidor MySQL (si pago por el toda la memoria, ¿por qué no usarla, verdad?).
Hoy, a primera hora de la mañana busqué mi Zabbix y noté que el esclavo no estaba funcionando, así que entré en él, reinicié MySQL e intenté iniciar el esclavo usandoINICIAR ESCLAVO. Y esto es lo que obtuve:
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
No estoy seguro de qué causó el problema en primer lugar (este es mi my.cnf)
[client]
# Usado apenas para casos especificos
port = 3306 # Porta parada
socket = /var/run/mysqld/mysqld.sock
[mysql]
# Configurações do cliente
auto-rehash # Auto completar
[mysqld]
# Configuração do servidor
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 # Ouve todos os endereços
# Configurações genericas do servidor
max_allowed_packet = 32M # Tamanho maximo do pacote.
max_connections = 2000 # Maximo de coneções
open_files_limit = 10000 # Maximo de arquivos abertos
tmp_table_size = 64M # Limite tamanho tabela em mem
max_heap_table_size = 64M # Limite tamanho tabela em mem
tmpdir = /tmp # Diretorio /tmp/
default_storage_engine = InnoDB # Engine default
skip_name_resolve # Desabilita resolução DNS
# Configurações de log binario
log_bin = mysql-bin # Arquivo de log binario
relay-log = mysql-relay-bin
log-slave-updates = 1 # Log de update no slave
read-only = 1 # Apenas leitura
binlog-format = mixed # Formato
server_id = 2 # Identifica servidor para log
max_binlog_size = 256M # Tamanho maximo log binario
binlog_expire_logs_seconds = 604800 # Max tempo log binario
innodb_flush_log_at_trx_commit = 1 # Proteção de dados
sync_binlog = 1 # Somente pra replicação
# Configurações especificas do InnoDB
innodb_dedicated_server = ON # Autoconf InnoDB
innodb_io_capacity = 2000 # Quantas escritas por segundo
innodb_read_io_threads = 64 # Threads de leitura
innodb_write_io_threads = 64 # Threads de escrita
innodb_thread_concurrency = 0 # Auto dectecta threads
# Slow query log
slow_query_log = 1 # Guarda queries lentas
long_query_time = 1.0 # Tempo query
# Funções dentro do Mysql
log_bin_trust_function_creators = 1; # Permite funções criadas
No tengo ni idea de cómo arreglarlo. ¿Debería hacer todo el volcado otra vez? ¿Cómo puedo evitar que esto vuelva a suceder?
ACTUALIZAR:
Este es el resultado de laMOSTRAR ESTADO DE ESCLAVO:
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)
ACTUALIZAR
Como se preguntó, aquí están las variables de ambos servidores.
MAESTRO
GLOBAL STATUS:
https://gist.github.com/IamRichter/ef4993bbf65883baa366ff30d73b9644
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/dbf08facd364548529b07bc0cbd6b2e6
ESCLAVO
GLOBAL STATUS:
https://gist.github.com/IamRichter/fcdff1111ed52fc859ebac46a2dbfc27
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/53c5638fb3c6064fd51eab332660f07f
Respuesta1
Por desgracia, las VARIABLES/ESTADO no muestran ninguna razón obvia para el problema que tiene. Tal vezel alto buffer_pool_size está exprimiendo demasiado la RAM, pero lo dudo. Aquí están mis análisis (Primario, luego Réplica):
Observaciones:
- Versión: 8.0.20
- 16 GB de RAM
- Tiempo de actividad = 2d 02:30:41
- No estás ejecutando Windows.
- Ejecutando la versión de 64 bits
- Parece que estás ejecutando InnoDB en su totalidad (o en su mayor parte).
Las cuestiones más importantes:
Aumente table_open_cachesi el sistema operativo lo permite. ¿Por qué tantas mesas?
Creo (sin ninguna prueba) que no es prudente usar nada que no sea "2" porque innodb_log_files_in_group MariaDB ya lo está ignorando; No he oído hablar de Oracle.
Hay algunos indicios de consultas mal formuladas. Verhttp://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlogpara analizar el registro lento.
¿Tenías alguna razón para tenerlo binlog_format=MIXED?
Detalles y otras observaciones:
( Opened_tables ) = 452,365 / 181841 = 2.5 /sec-- Frecuencia de apertura de tablas -- aumentar table_open_cache (ahora 3995)
( Table_open_cache_overflows ) = 448,306 / 181841 = 2.5 /sec
-- Es posible que sea necesario aumentar table_open_cache (ahora 3995)
( Table_open_cache_misses ) = 452,365 / 181841 = 2.5 /sec
-- Es posible que sea necesario aumentar table_open_cache (ahora 3995)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Cantidad de trabajo para los limpiadores de páginas cada segundo. -- "InnoDB: page_cleaner: el bucle previsto tomó 1000 ms ..." puede solucionarse reduciendo lru_scan_ Depth: considere 1000/innodb_page_cleaners (ahora 4). También verifique el intercambio.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5-- innodb_page_cleaners -- Se recomienda configurar innodb_page_cleaners (ahora 4) en innodb_buffer_pool_instances (ahora 8)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: el bucle previsto tardó 1000 ms..." puede solucionarse reduciendo lru_scan_ Depth
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 2,102,149 / 7377368 = 28.5%-- Solicitudes de escritura que debían llegar al disco -- Verifique innodb_buffer_pool_size (ahora 12884901888)
( innodb_log_files_in_group ) = 9-- Número de archivos de registro de InnoDB -- 2 es probablemente el único valor razonable. Un número grande puede causar problemas de rendimiento.
( 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-- Ratio -- (ver acta)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 181,841 / 60 * 1024M / 1773668352 = 1,834-- Minutos entre rotaciones de registros de InnoDB. A partir de 5.6.8, esto se puede cambiar dinámicamente; asegúrese de cambiar también my.cnf. -- (La recomendación de 60 minutos entre rotaciones es algo arbitraria). Ajuste innodb_log_file_size (ahora 1073741824). (No se puede cambiar en AWS).
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC- Cómo InnoDB debería pedirle al sistema operativo que escriba bloques. Sugiera O_DIRECT u O_ALL_DIRECT (Percona) para evitar el doble almacenamiento en búfer. (Al menos para Unix). Consulte chrischandler para conocer las advertencias sobre O_ALL_DIRECT.
( Innodb_row_lock_time_avg ) = 1,886-- Tiempo promedio para bloquear una fila (milisegundos) -- Consultas posiblemente conflictivas; posiblemente escaneos de tablas.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Si se deben registrar todos los interbloqueos. -- Si estás plagado de puntos muertos, activa esto. Precaución: si tiene muchos puntos muertos, esto puede escribir mucho en el disco.
( max_connections ) = 2,000-- Número máximo de conexiones (hilos). Afecta varias asignaciones. -- Si max_connections (ahora 2000) es demasiado alto y varias configuraciones de memoria son altas, podría quedarse sin RAM.
( Handler_read_rnd_next / Com_select ) = 46,219,107,293 / 7120289 = 6,491-- Promedio de filas escaneadas por SELECT. (aprox.) - Considere aumentar read_buffer_size (ahora 131072)
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (358564 + 419704 + 462 + 0) / 2006877 = 0.388-- Declaraciones por confirmación (asumiendo todo InnoDB) -- Bajo: podría ayudar a agrupar consultas en transacciones; Alto: las transacciones largas ponen a prueba varias cosas.
( Select_scan ) = 4,386,291 / 181841 = 24 /sec-- escaneos completos de tablas -- Agregar índices/optimizar consultas (a menos que sean tablas pequeñas)
( Select_scan / Com_select ) = 4,386,291 / 7120289 = 61.6%-- % de selecciones que realizan un análisis completo de la tabla. (Puede dejarse engañar por las rutinas almacenadas). - Agregar índices/optimizar consultas.
( binlog_format ) = binlog_format = MIXED-- DECLARACIÓN/FILA/MIXTO. -- ROW es preferido por 5,7 (10,3)
( Aborted_clients ) = 175,398 / 181841 = 0.96 /sec-- Hilos bloqueados debido a wait_timeout -- Incremento de wait_timeout (ahora 28800); Se amable, usa desconectar.
( Connections ) = 3,544,963 / 181841 = 19 /sec-- Conexiones -- Aumentar el tiempo de espera (ahora 28800); utilizar la agrupación?
Anormalmente pequeño: Archivos_abiertos = 3
Anormalmente grande: Com_do = 0,02 /HR Com_release_savepoint = 2,3 /HR Com_rollback_to_savepoint = 0,11 /seg 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 /seg Com_show_triggers = 0,11 /seg Create_tmp_files = 20 /seg Handler_read_key = 139335 /seg Handler_read_next = 316713 /seg Handler_read_rnd = 46465 /seg Handler_save punto = 2,3 /HR Handler_savepoint_rollback = 0,11 /seg Innodb_buffer_pool_read_requests = 1095747 /seg Innodb_num_open_files = 3996 Innodb_system_rows_inserted = 0,97 /HR Innodb_system_rows_read = 1,99e+7 Innodb_system_rows_updated = 0,095 /seg Open_table_definitions = 2000 Select_full_range_join = 2,1 /seg 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 = 1024 max_length_for_sort_data = 4096
Cadenas anormales: default_authentication_plugin = caching_sha2_password event_scheduler = ENCENDIDO innodb_dedicated_server = ENCENDIDO innodb_fast_shutdown = 1 optimizador_trace = habilitado = desactivado, one_line = desactivado optimizador_trace_features = greedy_search = activado, range_optimizer = activado, rango dinámico = activado, repetido_subselect = activado esclavo_rows_search_algorithms = INDEX_SCAN, HASH_SCAN
sf1030756-M
Observaciones:
- Versión: 8.0.21
- 6 GB de RAM
- Tiempo de actividad = 5 días 23:59:45
- ¿Estás seguro de que se trataba de MOSTRAR ESTADO GLOBAL? -- ¿O esta réplica simplemente no está ocupada?
- No estás ejecutando Windows.
- Ejecutando la versión de 64 bits
- Parece que estás ejecutando InnoDB en su totalidad (o en su mayor parte).
Las cuestiones más importantes:
Al igual que con la Primaria -- table_open_cache, innodb_log_files_in_group,binlog_format
Consultar por intercambio. innodb_buffer_pool_sizeEs bastante grande para los 6 GB de RAM.
Baje max_connectionsa 100: no ha utilizado más de 5 hasta ahora; el actual 2000 es una carga para la RAM.
Detalles y otras observaciones:
( table_open_cache ) = 3,995-- Número de descriptores de tabla para almacenar en caché -- Varios cientos suelen ser buenos.
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 56,436 / (1107115 + 56436) = 4.9%-- Efectividad de table_open_cache. -- Aumente table_open_cache (ahora 3995) y verifique table_open_cache_instances (ahora 16).
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Cantidad de trabajo para los limpiadores de páginas cada segundo. -- "InnoDB: page_cleaner: el bucle previsto tomó 1000 ms ..." puede solucionarse reduciendo lru_scan_ Depth: considere 1000/innodb_page_cleaners (ahora 4). También verifique el intercambio.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5-- innodb_page_cleaners -- Se recomienda configurar innodb_page_cleaners (ahora 4) en innodb_buffer_pool_instances (ahora 8)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: el bucle previsto tardó 1000 ms..." puede solucionarse reduciendo lru_scan_ Depth
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 25,132 / 57003 = 44.1%-- Solicitudes de escritura que debían llegar al disco -- Verifique innodb_buffer_pool_size (ahora 5368709120)
( innodb_log_files_in_group ) = 5-- Número de archivos de registro de InnoDB -- 2 es probablemente el único valor razonable. Un número grande puede causar problemas de rendimiento.
( 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-- Ratio -- (ver acta)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 518,385 / 60 * 512M / 22224896 = 208,704-- Minutos entre rotaciones de registros de InnoDB. A partir de 5.6.8, esto se puede cambiar dinámicamente; asegúrese de cambiar también my.cnf. -- (La recomendación de 60 minutos entre rotaciones es algo arbitraria). Ajuste innodb_log_file_size (ahora 536870912). (No se puede cambiar en AWS).
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC- Cómo InnoDB debería pedirle al sistema operativo que escriba bloques. Sugiera O_DIRECT u O_ALL_DIRECT (Percona) para evitar el doble almacenamiento en búfer. (Al menos para Unix). Consulte chrischandler para conocer las advertencias sobre O_ALL_DIRECT.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Si se deben registrar todos los interbloqueos. -- Si estás plagado de puntos muertos, activa esto. Precaución: si tiene muchos puntos muertos, esto puede escribir mucho en el disco.
( max_connections ) = 2,000-- Número máximo de conexiones (hilos). Afecta varias asignaciones. -- Si max_connections (ahora 2000) es demasiado alto y varias configuraciones de memoria son altas, podría quedarse sin RAM.
( (Com_show_create_table + Com_show_fields) / Questions ) = (8605 + 17405) / 313218 = 8.3%- Marco travieso: dedicar mucho esfuerzo a redescubrir el esquema. -- Quejarse con el proveedor externo.
( tmp_table_size ) = 64M-- Límite en el tamaño deMEMORIAtablas temporales utilizadas para admitir SELECT: disminuya tmp_table_size (ahora 67108864) para evitar quedarse sin RAM. Quizás no más de 64 millones.
( Select_full_join / Com_select ) = 8,884 / 123363 = 7.2%-- % de selecciones que son combinaciones sin índice -- Agregue índices adecuados a las tablas utilizadas en JOIN.
( Select_scan / Com_select ) = 107,352 / 123363 = 87.0%-- % de selecciones que realizan un análisis completo de la tabla. (Puede dejarse engañar por las rutinas almacenadas). - Agregar índices/optimizar consultas.
( Com_admin_commands / Queries ) = 6,755 / 320422 = 2.1%-- Porcentaje de consultas que son comandos de "administrador". -- ¿Qué está sucediendo?
( binlog_format ) = binlog_format = MIXED-- DECLARACIÓN/FILA/MIXTO. -- ROW es preferido por 5,7 (10,3)
( log_slow_slave_statements ) = log_slow_slave_statements = OFF-- (5.6.11, 5.7.1) De forma predeterminada, las declaraciones replicadas no aparecerán en el registro lento; esto hace que se muestren. -- Puede resultar útil en el registro lento ver escrituras que podrían estar interfiriendo con las lecturas de réplica.
( thread_cache_size / Max_used_connections ) = 28 / 5 = 560.0%
-- No hay ninguna ventaja en tener un caché de subprocesos mayor que el número probable de conexiones. Desperdiciar espacio es la desventaja.
( thread_stack * max_connections ) = (286720 * 2000) / 6144M = 8.9%-- Asignación mínima de memoria para max_connections. -- Menor max_connections (ahora 2000)
Anormalmente pequeño: Bytes_recibidos = 58 /seg Com_insert = 0 Com_update = 0 Create_tmp_files = 0.035 /HR Handler_update = 2.2 /HR Innodb_buffer_pool_write_requests = 0.11 /seg Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2.27 _escrito / Innodb_dblwr_writes = 1 Innodb_pages_created = 1 /HR Innodb_rows_deleted + Innodb_rows_inserted = 0 Innodb_rows_inserted = 0 Innodb_rows_updated = 0 Seleccionar_rango = 0 Seleccionar_rango / Com_select = 0
Anormalmente grande: Com_do = 0,014 /HR Com_show_create_func = 0,15 /HR Com_slave_start = 0,028 /HR Innodb_num_open_files = 3996 Innodb_system_rows_read = 1,91e+7 Innodb_system_rows_updated = 2,2 /HR Open_table_definitions = 2000 back_log / max_connections = 1 00.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 optimizador_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
Cadenas anormales: default_authentication_plugin = caching_sha2_password event_scheduler = ENCENDIDO innodb_dedicated_server = ENCENDIDO innodb_fast_shutdown = 1 optimizador_trace = habilitado = desactivado, one_line = desactivado optimizador_trace_features = greedy_search = activado, range_optimizer = activado, rango dinámico = activado, repetido_subselect = activado solo lectura = activado esclavo_rows_search_algorithms = IN DEX_SCAN, HASH_SCAN
sf1030756-S