3

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)
ks_1010
  • 121
  • 1
  • 2
  • 8
  • 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 TABLE`s 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
  • @gertvdijk it took 8 hours to prepare the backup. the slave: 16 CPUs 64 gb memory the master: 12 CPUs 32 gb memory show full processlist; does show the slave updating. – ks_1010 Dec 14 '12 at 21:06

1 Answers1

1

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.

Community
  • 1
Ladadadada
  • 26,337
  • 7
  • 59
  • 90
  • 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