1

I have implemented Master Slave Replication.

The slave can't keep up with progress.

How can I find out the problem and optimize the config?

OS: CentOS 6.8
Mysql 5.6.35
Master => Slave 3-5 Mbps

           Slave_IO_State: Waiting for master to send event
              Master_Host: **.**.**.**
              Master_User: ********
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: binlog.000095
      Read_Master_Log_Pos: 297639485
           Relay_Log_File: mysqld-relay-bin.000050
            Relay_Log_Pos: 644337677
    Relay_Master_Log_File: binlog.000083
         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: 644337473
          Relay_Log_Space: 13252726053
          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: 119951 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
              Master_UUID: bada755f-d361-11e6-b53e-d4ae52b21cd5
         Master_Info_File: mysql.slave_master_info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: System lock
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set: bada755f-d361-11e6-b53e-d4ae52b21cd5:281172-14941959
        Executed_Gtid_Set: bada755f-d361-11e6-b53e-d4ae52b21cd5:1-11696260
            Auto_Position: 1
Improve this question
6
  • have you tested the connection speed between master and slave machine, and how much time it will take to copy anything between those two machines.
    – Ahmad Abuhasna
    Commented Mar 1, 2017 at 10:50
  • 3
    Possible duplicate of mysql 5.6 gtid replication slave stuck (system lock)?
    – tombom
    Commented Mar 1, 2017 at 12:28
  • This has nothing to do with slow performance. Your replication is stuck. See the line Slave_SQL_Running_State, it says "System lock". Have a look here for example.
    – tombom
    Commented Mar 1, 2017 at 12:30
  • What versions are Master and Slave? What OS?
    – Rick James
    Commented Mar 1, 2017 at 18:27
  • Hi Eric, first of all check comment from @tombom, need fix this (some time more easy - re-fresh slave from dump), than check next. Information still not answer for all - 3-5 mb more than enough for 10 transactions and extremely slow for 1M. RickJames also ask good question. As much more information in question - as much more easy understand source of error
    – a_vlad
    Commented Mar 2, 2017 at 2:47

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.