Tengo 2 servidores MySQL:
Servidor maestro: mysql versión 5.7.14
Servidor esclavo: mysql versión 5.7.14 en el contenedor Docker (del hub oficial de Docker).
Replicación GTID.
Tengo dos problemas:
- Los binlogs crecen muy rápido. Establecí un límite de rotación de 2 días, pero esto no ayuda: cada día la carpeta binlog aumenta al menos dos veces (el primer día 25 Gb, el segundo 50, el tercero 80, etc.).
- El servidor esclavo aumenta los "segundos detrás del maestro".
Servidores en red local (100 mbit/s), discos SSD, tamaño de base de datos cercano a 40Gb.
Cloné db para esclavo con Percona Xtrabackup.
¿Quizás la configuración de los servidores no es correcta y necesito arreglar algo?
Este es el archivo Master my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
max_allowed_packet = 256M
log-error = /var/log/mysql/error.log
symbolic-links=0
!includedir /etc/mysql/conf.d/
innodb_buffer_pool_size=15000M
innodb_buffer_pool_instances=1
sql_mode = ''
slow_query_log = 1
slow_query_log_file = '/var/log/mysql/slow.log'
long_query_time = 1
log_queries_not_using_indexes = 0
#skip-grant-tables
default_week_format = 1
skip-name-resolve
sort_buffer_size=4M
join_buffer_size=4M
innodb_sort_buffer_size=4M
tmp_table_size=5000M
max_heap_table_size=5000M
[mysqld]
server-id = 1
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON
log-slave-updates
log_bin = /var/log/mysql/mysql-bin.log
performance_schema_max_digest_length = 8192
max_digest_length = 8192
max_binlog_size= 1G
expire_logs_days = 2
binlog-ignore-db=check_passport
replicate-ignore-db=check_passport
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
key_buffer_size = 16M
innodb_flush_method = O_DIRECT
max_connections = 200
#innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G
Este es my.cnf del servidor esclavo.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
sql_mode = ""
character_set_server = utf8
collation_server = utf8_general_ci
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-host-cache
skip-name-resolve
skip-external-locking
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet = 256M
#key_buffer_size = 16M
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 256M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
max_connections = 136
query_cache_size = 0
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
expire_logs_days = 2
max_binlog_size = 1G
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysqld]
server-id = 2
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
skip_slave_start
log_slave_updates = 0
read_only = ON
innodb_file_per_table = ON
#innodb_buffer_pool_size = 3G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
max_binlog_size = 1G
#max_relay_log_size = 1G
#relay_log_space_limit = 20G
relay_log_recovery = ON
expire_logs_days = 2
#slave-parallel-workers = 0
binlog-ignore-db=check_passport
replicate-ignore-db=check_passport
replicate-ignore-table=gfk.application_insurance
replicate-ignore-table=gfk.archive_client_building
replicate-ignore-table=gfk.comments_passwords
replicate-ignore-table=gfk.date_interval
Salida deMOSTRAR ESTADO DE ESCLAVO\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.004720
Read_Master_Log_Pos: 518759418
Relay_Log_File: mysql-relay-bin.000188
Relay_Log_Pos: 213202356
Relay_Master_Log_File: mysql-bin.004703
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: check_passport
Replicate_Do_Table:
Replicate_Ignore_Table: gfk.application_insurance,gfk.date_interval,gfk.archive_client_building,gfk.comments_passwords
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 213202143
Relay_Log_Space: 18773097825
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/lib/master_cert/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /var/lib/master_cert/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /var/lib/master_cert/client-key.pem
Seconds_Behind_Master: 14488
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
Master_UUID: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:62276836-70424802
Executed_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:1-67413143
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Respuesta1
¿Es práctico que la Primaria tenga binlog_ignore = check_passport? Si es así, y si hay mucho tráfico sobre esa base de datos, eso reduciría "mucho" los binlogs.
Es grande DELETEso UPDATEsocupa mucho espacio en el binlog, lo que hace que el binlog sea enorme. (Ejemplo: actualizar cada fila de una tabla de un millón de filas). Proporcione detalles; Pueden existir soluciones alternativas importantes.
¿Cuántos subprocesos de réplica se ejecutan en la réplica? Esto impacta Seconds_behind_master. (Es probable que más subprocesos, hasta cierto límite, disminuyan "detrás").
innodb_flush_log_at_trx_commit = 1--> cambiar a 2. Esto sacrifica algo de robustez (en caso de falla), pero mejora el rendimiento.
¿Cuánta RAM hay en cada uno? Veo un buffer_pool más grande en el Primario. Generalmentela réplica debería ser la máquina más robusta.
100Mbs parece poco; ¿Puedes comprobar si está saturado?
Datos nuevos diariamente
Aquí hay una manera eficiente de reemplazartodolos datos en una tabla:
CREATE TABLE new LIKE real;LOAD DATA INFILE INTO new ...- o lo que sea necesario- `RENOMBRAR TABLA real A antiguo, nuevo A REAL;
DROP TABLE old;
El paso 2 es la parte más lenta.
El paso 3 es muy rápido; es el único momento que tu mesa no está disponible.
No hay uso intensivo de binlog UPDATEo DELETE.
Respuesta2
Encontré una solución para mi caso.
En primer lugar, busco qué bases de datos y tablas generan binlogs tan grandes (qué tablas no tienen claves primarias o únicas):
SELECT t.table_schema,t.table_name,engine
FROM information_schema.tables t
INNER JOIN information_schema .columns c
on t.table_schema=c.table_schema
and t.table_name=c.table_name
and t.table_schema not in ('performance_schema','information_schema','mysql')
GROUP BY t.table_schema,t.table_name
HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;
Entonces:
- La velocidad de mi red ya es de 1 Gbit/s, todo está bien.
- Excluyo de la replicación y el registro de la base de datos que se recrea todos los días.
- Aumento la RAM esclava a 23 Gb (como en la maestra)
- Muevo binlogs en esclavo de SSD a HDD; está bien; la velocidad del HDD es suficiente.
- Configuré log_slave_updates = 1, porque mi esquema de replicación es maestro>esclavo>esclavo.
¡Y estos pasos resuelven mis problemas! Por ahora mis binlogs no aumentan más de 10Gb.