1

I have added new slave host to master for replication, but missing some of the parameters.

mysql> show slave status\G  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting to reconnect after a failed master event read  
                  Master_Host: 10.10.2.12    
                  Master_User: slave_user    
                  Master_Port: 3306    
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000071   
          Read_Master_Log_Pos: 754916280  
               Relay_Log_File: mysqld-relay-bin.000001    
                Relay_Log_Pos: 4  
        Relay_Master_Log_File: mysql-bin.000071  
             Slave_IO_Running: Connecting    
            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: 754916280  
              Relay_Log_Space: 120  
              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: NULL  
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: 212  
                  Master_UUID:  
             Master_Info_File: /var/lib/mysql/master.info  
                    SQL_Delay: 0  
          SQL_Remaining_Delay: NULL  
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
           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   
1 row in set (0.00 sec)  

I have followed the step below still no luck. I am getting the same error after creating and allowing the user GRANT privileges. let me know if we are missing something.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';  
Query OK, 0 rows affected (0.08 sec)  

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'10.10.2.16';  
Query OK, 0 rows affected (0.03 sec)  

Look at this, isn't this strange.

[root@host ~]# netstat -tulpn | grep -i mysql   
tcp        0      0 :::3306                     :::*                        LISTEN      2135/mysqld  

After changing the log position, we are getting this error.

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000071',   MASTER_LOG_POS=754916280;

mysql> show slave status\G  
*************************** 1. row ***************************  
               Slave_IO_State: Queueing master event to the relay log  
                  Master_Host: 10.10.2.12  
                  Master_User: slave_user  
                  Master_Port: 3306  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000076  
          Read_Master_Log_Pos: 488743659  
               Relay_Log_File: mysqld-relay-bin.000002  
                Relay_Log_Pos: 557  
        Relay_Master_Log_File: mysql-bin.000071  
             Slave_IO_Running: Yes  
            Slave_SQL_Running: No  
              Replicate_Do_DB:  
          Replicate_Ignore_DB:  
           Replicate_Do_Table:  
       Replicate_Ignore_Table:  
      Replicate_Wild_Do_Table:  
  Replicate_Wild_Ignore_Table:  
                   Last_Errno: 1146  
                   Last_Error: Error executing row event: 'Table   'example.tracker' doesn't exist'  
                 Skip_Counter: 0  
          Exec_Master_Log_Pos: 754916567  
              Relay_Log_Space: 5112867650  
              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: NULL  
Master_SSL_Verify_Server_Cert: No  
                Last_IO_Errno: 0  
                Last_IO_Error:  
               Last_SQL_Errno: 1146  
               Last_SQL_Error: Error executing row event: 'Table   'example.tracker' doesn't exist'     
  Replicate_Ignore_Server_Ids:  
             Master_Server_Id: 212     
                  Master_UUID:       
             Master_Info_File: /var/lib/mysql/master.info       
                    SQL_Delay: 0    
          SQL_Remaining_Delay: NULL     
      Slave_SQL_Running_State:       
           Master_Retry_Count: 86400     
                  Master_Bind:       
      Last_IO_Error_Timestamp:         
     Last_SQL_Error_Timestamp: 160127 04:51:22      
               Master_SSL_Crl:    
           Master_SSL_Crlpath:     
           Retrieved_Gtid_Set:        
            Executed_Gtid_Set:       
                Auto_Position: 0          
1 row in set (0.00 sec)        

Adding the output for mysqlbinlog

 [root@host mysql]# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000089 | tail -n 5 | head -n 1  
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 541414465, event_type: 116  
ERROR: Could not read entry at offset 184469: Error in log format or read error.  
DELIMITER ;  

[root@host mysql]# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000086 | tail -n 5 | head -n 1
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: -736930139, event_type: 71
ERROR: Could not read entry at offset 668651590: Error in log format or read error.
DELIMITER ;
1

1 Answer 1

1

When running this command mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000004 | tail -n 5 | head -n 1 it gives an error. After investigation we found that last week there was power failure, because of which replication isn't happening. So the workaround is to take a new backup of master and restore the replication.

8
  • I thought that command pipeline looked familiar. :) Note that you need to substitute mysql-bin.000004 with the actual binlog file you're trying to read. Jan 27, 2016 at 21:54
  • Michael that is just an example mine is 000089 which is corrupted.
    – AReddy
    Jan 28, 2016 at 3:22
  • Okay, just making sure... you didn't show the error output, so I wasn't clear if you'd had a genuine error or just the error that would have been generated if you had used that as-is with no log 000004. Jan 28, 2016 at 13:44
  • Michael I have edited the question. please have a look
    – AReddy
    Jan 28, 2016 at 13:50
  • yep, looks like corrupt logs. Jan 28, 2016 at 14:08

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.