Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I started a MySQL slave using innobackupexand the Read_Master_Log_Pos: and the Relay_Log_Pos: are updating however the seconds behind master keeps on increasing (it's at Seconds_Behind_Master:496637 currently and increasing).

Any ideas on how to fix this?

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.8.25.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.005021
          Read_Master_Log_Pos: 279162266
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 378939436
        Relay_Master_Log_File: mysql-bin.004997
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 378939290
              Relay_Log_Space: 26048998487
              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: 497714
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)
share|improve this question
    
even tried doing this SET GLOBAL innodb_flush_log_at_trx_commit = 0 ; –  ks_1010 Dec 11 '12 at 21:35
    
Could you add the output of SHOW SLAVE STATUS\G to your question? –  Ladadadada Dec 11 '12 at 22:00
2  
How old was the innobackupex backup? Is the master being written to a lot by multiple clients? replication in MySQL is single-threaded unfortunately. How much different is the hardware in terms of performance? What are the bottleneck indications? I/O, CPU, memory, etc. What differences do you have in performance settings/tuning? (e.g. buffer pool size, log buffer size, etc.). What kind of statements is it processing? (read with mysqlbinlog on the master) ALTER TABLEs could take very long and make the Seconds_Behind_Master a very rough estimate. –  gertvdijk Dec 11 '12 at 22:15
    
Taking incremental backups, the Full Backup was taken 5 days back and incremental 3 days back. The Master in being written to a by a lot of clients. The new slave has better HW than the master, haven't made any changes in setting/tuning the slave though, what needs to be changed ? –  ks_1010 Dec 11 '12 at 22:40
    
How long did it take to apply the incrementals to the full and prepare the backup (--apply-log)? How large are the binary logs? Please be more quantitative. "better HW" doesn't mean it performs better. A heavy loaded MySQL performs bad out of the box - you should tune it. Show some output of show full processlist; - it might give you an indication on what takes it so long. Enable slow query logging. "What could be changed" - start looking at the difference in configuration. Too much stuff left to try that would fit in a single comment... –  gertvdijk Dec 14 '12 at 20:24
show 1 more comment

1 Answer

In particular, pay attention to Slave_IO_Running: No and Slave_SQL_Running: No.

I suspect your slave SQL is not running and your slave IO is. If you have something in Last_SQL_Error:, you will need to fix the error before continuing. The specifics of how to deal with each of the different possible errors are probably worth a whole question on their own. Maybe on DBA.SE

If there's no error or after you have fixed the error, execute START SLAVE; and watch the output of SHOW SLAVE STATUS\G until you get another error or the slave catches up.

share|improve this answer
    
Thanks for your reply. added the show slave status output, its hows the Slave_IO_Running: Yes and Slave_SQL_Running: Yes, also there's no error on Starting the Slave. –  ks_1010 Dec 11 '12 at 22:33
add comment

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.