我在 aws 上有 2 个从属设备和 1 个主设备，1 个从属设备已经与主设备同步，但第 2 个从属设备在后面创建了巨大的从属 lg，经过调查我发现，这个从属设备以 KB 为单位创建了大量中继日志，但之前它创建的中继日志以 100MB 为单位，当中继日志以 MB 为单位时，它工作正常，但当它以 KB 为单位时，它开始滞后。
从属描述：RAM：32 GB Binlog 格式：两侧均为 ROW。数据分区和中继日志位于同一驱动器中。
主从机之间的数据传输率为 40 MB。
mysql-slave> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
奴隶落后了。
My.cnf
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
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
basedir = /opt/mysql/server-5.6
#datadir = /var/lib/mysql/datadir
datadir = /data/mysql/data/
#tmpdir = /tmp
#lc-messages-dir = /usr/share/mysql
lc-messages-dir = /opt/mysql/server-5.6/share
skip-external-locking
#log-slave-updates
performance_schema=0
key_buffer_size = 64M
##open-files-limit = 40000
#
#
max_allowed_packet = 1G
thread_stack = 192K
thread_cache_size = 32
#query_cache_size = 256M
#query_cache_type = 1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
##myisam-recover = BACKUP
#table_cache = 1024
table_open_cache = 512
log_error = /data/mysql/data/mysqllog.err
slave-skip-errors = 1062
#
read_only=on
########Newamit#################
skip-name-resolve
skip-federated
skip-slave-start
#max_allowed_packet=1G
max_connect_errors=100000
#query_cache_size=256M
#query_cache_type=1
sort_buffer_size=2M
join_buffer_size=2M
innodb_doublewrite=OFF
##myisam_sort_buffer_size=8M
myisam_sort_buffer_size=4M
read_buffer_size=512K
#sort_buffer_size=20M
read_rnd_buffer_size=512K
max_connections=200
tmp_table_size=64M
server_id=151
relay-log=/data/binlog/relayLog/relay-log
auto_increment_increment=1
auto_increment_offset=2
#max_binlog_size=100M
interactive_timeout=600
#read_only=on
#tmpdir = /var/lib/mysql/mytmp/
tmpdir = /data/mysql/mytmp
#innodb_log_group_home_dir =/var/lib/mysql/iblogs
innodb_log_group_home_dir = /data/mysql/iblogs
innodb_log_files_in_group=3
innodb_log_file_size=500M
innodb_log_buffer_size=32M
#innodb_buffer_pool_instances=3
#innodb_thread_concurrency=8
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
binlog_format=row
#innodb_flush_log_at_trx_commit=2
#innodb_buffer_pool_size=25G
#innodb_buffer_pool_size=11G
innodb_buffer_pool_size=12G
innodb_additional_mem_pool_size=84M
#innodb_file_io_threads=8
innodb_read_io_threads=10
innodb_write_io_threads=20
#innodb_thread_concurrency=2
#innodb_lock_wait_timeout=50
#lock_wait_timeout=400
lower_case_table_names=0
log_output=FILE
query_cache_min_res_unit=8192
innodb_file_per_table=1
##datadir=/var/lib/mysql/daatdir
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 5M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer =50M
!includedir /etc/mysql/conf.d/
答案1
您可以在滞后的从属服务器上尝试以下操作：
STOP SLAVE IO_THREAD;
这将停止通常会导致延迟的 IO_THREAD
另请检查所有 mysql 服务器是否都是同一版本。