MySQL Replication Stale

Question

I had some interesting problems with Maria DB.

1st I have max_connections in config under section [mysqld] but the value is always 214 (default) after restart. So the problem is I can't defined the value in config (server version 5.5.32-MariaDB-log)

2nd Is directly related to mysql replication: So I have simple replication setup master - slave master mysql 5.1.62-0ubuntu0.11.10.1-log slave 5.5.32-MariaDB-log

so i had the issue second time and i cant figure out how to debug that problem or by what can be caused

Exec_Master_Log_Pos: 101702714
Relay_Log_Space: 1591329

so after i ran show slave status; it show me those above values and 0 behind the master, after say 2 - 3 mins i ran agin show slave status; and i had still the same values and 0 behind. I did check other two slaves are working fine. Also there is no issue with server-id each server has different id. So any idea guys ?

thx

Answer 1

Ok i found the issue with first question there is the answer: there was problem with limit to open files so to resolve that issue i added those values to my.cnf

max-connections         = 950
open-files-limit        = 65535

thx

Ok i solved the 2nd issue too. In general there was issue with IO thread for replication. Due to some network problem or network latency.

In short explanation all looks this way, slave connect to the master and master sends binlogs events to the slave. and because this data is pushed from master rather than pulled by slave, there is a possibility that the replication channel could be broken. So the slave wouldn't notices until slave_net_timeout occur (default is 3600). Issue started around 21:50 fixed by self around 22:50. (look graph binary/relay log). In my case. Also when I ran processlist and u search for those process what are responsible for replication (ex. user "repl") u will find

290659     repl     hostname:56896   NULL    Binlog Dump     1215563 Has sent all binlog to slave; waiting for binlog to be updated  NULL

where this type of info means that the system is healthy, in not healthy system we should see

"Writing to net NULL" 

and i saw those things. Also I found it that around that time we had "close_waits" on Master DB what may suggest that the channel for replication been broken. mysql has parameter called slave_compressed_protocol (default OFF).

So after changing those default values to 120s and to enable compression, i am not seeing those issues unless there is bigger issue with network.

By changing

  master-retry-count=0

your slave will keep trying to reconnect to Master until success