We have Master Slave Mysql Setup, Master & slave hardware configuration is same (8 Core,30 GB Memory). These servers are Hosted on AWS.
My slave frequently is getting behind master . The current value for Seconds_Behind_Master is 114701, Not sure on how to get this sync master & slave Immediately. I can see that .
Master status - mysql-bin.000262 | 514064392
Slave Status - mysql-bin.000258 | 285395655
Below is my my.cnf configuration from Mysql Slave.
[mysql]
CLIENT
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
GENERAL
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /srv/mysql/mysql.pid
skip_name_resolve
MyISAM
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
SAFETY
max-allowed-packet = 16M
max-connect-errors = 1000000
DATA STORAGE
datadir = /srv/mysql/
BINARY LOGGING
log-bin = /srv/mysqllogs/mysql-bin
max_binlog_size = 512M
expire-logs-days = 4
sync-binlog = 1
REPLICATION
server-id = 2
read-only = 1
log-slave-updates = 1
relay-log = /srv/mysqllogs/relay-bin
slave-net-timeout = 60
sync-master-info = 1
sync-relay-log = 1
sync-relay-log-info = 1
slave-skip-errors = 1062
CACHES AND LIMITS
tmp-table-size = 128M
max-heap-table-size = 128M
query-cache-type = 1
query-cache-size = 512M
max-connections = 4096
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
#slave_parallel_workers = 3
INNODB
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table = 1
innodb-buffer-pool-size = 24G
LOGGING
log-error = /srv/mysqllogs/mysql-error.log
#general_log_file = /srv/mysqllogs/mysql.log
#general_log = 1
log_warnings = 2
#log-queries-not-using-indexes = 1
#slow-query-log = 1
#slow-query-log-file = /srv/mysqllogs/mysql-slow.log
#tmpdir = /media/ephemeral0/tmp/
Could some one please help me in identifying & resolving the issue.