Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a master (m) - slave (s1) setup using mysql 5.1.45

When I try to add a second slave (s2) the slave lags behind and never catches up on the sync.

Even after having synced the s2 with the whole system offline and there were (Seconds_Behind_Master = 0) after a few hours the s2 gets out of sync.

Strange is that s1 is always on sync.

any ideas?

SHOW SLAVE STATUS \G  (on slave2)
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: xxx.xxx.xxx.xxx
              Master_User: xxxx_xxxx5
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.013165
      Read_Master_Log_Pos: 208002803
           Relay_Log_File: xxxxxxxxxx-relay-bin.000100
            Relay_Log_Pos: 1052731555
    Relay_Master_Log_File: mysql-bin.013124
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: xxxxxxxxx
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1052731410
          Relay_Log_Space: 44233859505
          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: 69594
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:

iperf results between servers:

M -> s2
[ ID] Interval       Transfer     Bandwidth
[  5]  0.0-10.0 sec    502 MBytes    420 Mbits/sec
[ ID] Interval       Transfer     Bandwidth
[  4]  0.0-10.0 sec  1.05 GBytes    902 Mbits/sec

M -> s1
[ ID] Interval       Transfer     Bandwidth
[  4]  0.0-10.0 sec    637 MBytes    534 Mbits/sec
[ ID] Interval       Transfer     Bandwidth
[  5]  0.0-10.0 sec    925 MBytes    775 Mbits/sec

vmstat for s2

 vmstat
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ 
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0    268 126568 199100 22692944    0    0   100   836    8   81  1  0 96  3

vmstat 2 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0    268 1150144 197128 21670808    0    0   100   835    9   81  1  0 96  3  0
0  0    268 1144464 197160 21674940    0    0   644  3096 1328 1602  0  0 97  2  0
0  2    268 1140680 197176 21679624    0    0   846  5362 1002 1567  0  0 98  2  0
0  1    268 1135332 197192 21685040    0    0   960  3348  850 1193  0  0 98  1  0
0  0    268 1130776 197204 21688752    0    0   576  2894  978 1232  0  0 98  2  0
0  0    268 1127060 197264 21693556    0    0   586  5202 1075 1505  0  0 97  3  0
0  0    268 1122184 197272 21698412    0    0   896  1160  614  727  0  0 98  1  0
0  0    268 1118532 197300 21702780    0    0   586  5070 1279 1708  0  0 93  6  0
0  0    268 1114000 197324 21705820    0    0   402  1522  947  942  0  0 95  4  0
0  0    268 1109708 197336 21710188    0    0   704  9150 1224 2109  0  0 97  2  0

top output on s2

top - 14:44:25 up 16:36,  1 user,  load average: 1.62, 1.47, 1.42
Tasks: 140 total,   1 running, 139 sleeping,   0 stopped,   0 zombie
Cpu0  :  2.9%us,  1.1%sy,  0.0%ni, 73.8%id, 21.8%wa,  0.0%hi,  0.4%si,  0.0%st
Cpu1  :  0.8%us,  0.3%sy,  0.0%ni, 95.5%id,  3.3%wa,  0.0%hi,  0.0%si,      0.0%st
Cpu2  :  0.6%us,  0.3%sy,  0.0%ni, 97.7%id,  1.4%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  0.5%us,  0.2%sy,  0.0%ni, 98.9%id,  0.4%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  0.0%us,  0.0%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  :  0.0%us,  0.0%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  0.0%us,  0.0%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  0.0%us,  0.0%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  24744184k total, 24005508k used,   738676k free,   199136k buffers
Swap:  1050616k total,      268k used,  1050348k free, 22078920k cached

Any ideas?

Is there any chance that the Mysql version is the culprit of all this in conjuction with the nearly 5 fold increase in traffic to the master ?

If that is the case then why s1 syncs and not s2?

Any ideas if 5.6.x solves similar probs ?

share|improve this question
    
found this: mysqlha.blogspot.com –  didierc Mar 29 '13 at 8:51
    
from the link above, obviously this is a hard problem. Without more details about your setup, it's likely to be very difficult to figure out what is the problem. –  didierc Mar 29 '13 at 8:53
    
I have tryied 2 seperate s2 setups as far as hardware is concerned. I did this in order to take the hardware issue out of the question –  sonaht Mar 29 '13 at 9:01
    
what is the status on SLAVE Thread IO and SLAVE Thread SQL on S2? –  Mannoj Mar 29 '13 at 9:46
    
add the show slave status above –  sonaht Mar 29 '13 at 9:53

2 Answers 2

The answer to this is very straightforward. The two slaves must have the same server_id. I wrote about this 2 years ago (Screwed up replication by sharing server ids). In that post, I quoted Baron Schwartz's blog Pop quiz: how can one slave break another slave.

The quick-and-dirty solution ? Change the second slave's server_id. For example, if the master's server_id is 1000 and first slave's server_id is 1001, go to the second slave and run the following:

mysql> SET GLOBAL server_id = 1002;

This will fix it right then and there.

Then, go to the second slave and change the server_id in the my.cnf

[mysqld]
server_id = 1002

Give it a Try !!!

share|improve this answer

For this we might need to have a look into the system to fix this. But you may try this procedure.

First bring it back in sysnc with the master by taking full dump if possible, and once it is done and sec behind master is 0, then at that point run this command on master

show master status

make a note of the log position. Now let it go out of sync and second behind master go far once you are stisfied it is out of sync issue following command on the slave.

stop slave reset slave flush logs `set global innodb_file_per_table=1';

then

change master to master_user= 'slave1';start slave; use above command with user and password as per need basis.....

This may sort it out. If not I would be happy to look into it if you wish..

Thanks.

Masood

share|improve this answer
    
What is the point of running the above statements? This is a 50 GB db and such tests cost actually a whole nights work as well as getting the system ofline for fast backups and full network bandwidth. Please explain th epoint behind these statements... thank you –  sonaht Mar 31 '13 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.