MySQL Slave is almost 1 day behind
Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius 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: 96662972 Relay_Log_Space: 2211376614 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: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Slave is almost 1 day behind
A few things to keep in mind: 1: the master may have several threads feeding into the binlog at a time, but a slave only executes in a single thread. Are you throwing more stuff at the slave in multiple mysql threads? 2: is there something else going on with the slave box? some big backup or gzip or something that would chew up cycles? any big mysql query or update going on? 3: have you checked the disks on your slave. Whenever I notice a slave falling behind for an extended period of time, I ask the sys admins to check the disk drives - if you're using some kind of RAID, they can become degraded. 4: you might also check the slave's mysql error log to see if there's any hint there. On Fri, Mar 26, 2010 at 9:45 AM, Steven Staples sstap...@mnsi.net wrote: Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius 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: 96662972 Relay_Log_Space: 2211376614 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: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MySQL Slave is almost 1 day behind
Also, run SHOW PROCESSLIST, to see what sql's are running on slave. This will give u any idea if any huge dml was run on master and its getting sync'd with slave. regards anandkl On Fri, Mar 26, 2010 at 8:25 PM, Jim Lyons jlyons4...@gmail.com wrote: A few things to keep in mind: 1: the master may have several threads feeding into the binlog at a time, but a slave only executes in a single thread. Are you throwing more stuff at the slave in multiple mysql threads? 2: is there something else going on with the slave box? some big backup or gzip or something that would chew up cycles? any big mysql query or update going on? 3: have you checked the disks on your slave. Whenever I notice a slave falling behind for an extended period of time, I ask the sys admins to check the disk drives - if you're using some kind of RAID, they can become degraded. 4: you might also check the slave's mysql error log to see if there's any hint there. On Fri, Mar 26, 2010 at 9:45 AM, Steven Staples sstap...@mnsi.net wrote: Good day :) We've had our master/slave server running for a while now, and just yesterday, we started getting behind. Not entirely sure what happened, but it is getting further and furhter behind. (master server) mysql show master status\G *** 1. row *** File: mysql-bin.000280 Position: 58090245 Binlog_Do_DB: admin_server,baf,freeradius,radius Binlog_Ignore_DB: 1 row in set (0.00 sec) (slave server) mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.101 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000280 Read_Master_Log_Pos: 55208258 Relay_Log_File: backup-relay-bin.000530 Relay_Log_Pos: 96663109 Relay_Master_Log_File: mysql-bin.000259 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: admin_server,baf,freeradius,radius 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: 96662972 Relay_Log_Space: 2211376614 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: 77473 1 row in set (0.00 sec) Now, we are logging the freeradius packets into mysql, and like I said, it has been running fine, up until yesterday. Any idea how the slave would get this far behind, and not be generating any errors? It is my understanding, that the slave only does update/insert/delete queries, so even if there was a lot of select queries on the master, the slave wouldn't see them. We are not running any queries on the slave (it was set up for backup purposes, so we could stop the slave and backup completely), and we haven't done a backup on the slave in a couple of days (yeah, i know... bad bad) so there is really no reason for this. Can anyone help/assist/point me in the right direction to figure out how to catch the slave back up to the master? The master is not being overloaded, it is keeping up no problem, and the backup server is 8x the server than the application server, so it shoulnd't even be an i/o or cpu issue. Please help! :) Thanks in advance Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
