0

(Hi; sorry for my English) My question is: how can I restart MySQL replication on a slave server. At first sight everything seems to be normal but when I check with 

SHOW SLAVE STATUS\G

besides Relay_Log_Space, Read_Master_Log_Pos and Seconds_Behind_Master which continue to increase. Other data remain fixed for several days. I tried STOP/START SLAVE; but nothing has changed.

root@localhost [mysql]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: slave_replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000103
          Read_Master_Log_Pos: 197827259
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 149672940
        Relay_Master_Log_File: mysql-bin.000029
             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: 305160334
              Relay_Log_Space: 65277287216
              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: 1724670
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: 3464562
                  Master_UUID: 727b7e74-325f-11e6-9b54-faa1cd739fa9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
Improve this question
2
  • I see you have 65GB in Relay logs. Question: Did you perform a LOAD DATA INFILE on the master about 20 days ago ???
    – RolandoMySQLDBA
    Commented Jul 18, 2016 at 18:43
  • Thanks for your response and the answer is no!
    – Abdel
    Commented Jul 19, 2016 at 12:40

1 Answer 1

Reset to default
1

Slave_SQL_Running_State: Reading event...

Suggests that it's working on a large event. If you have MIXED or Row based binary logging and have changed a large amount of data on the master you might end up with a large event or series of large events. Use the mysqlbinlog tool to see what's going on with your relaylog. The master might show a concentration of binary logs over a small period of time (on the filesystem)

Improve this answer
2
  • Actually I changed the binlog_format from STATEMENT to MIXED about 20 days ago...
    – Abdel
    Commented Jul 19, 2016 at 13:02
  • have you checked what eroomydna told you ?
    – simplifiedDB
    Commented Jun 27, 2017 at 5:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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