MariaDB - Relay log read failure: Could not parse relay log event entry

Getting the following error when checking slave status

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

I am using Bitnami's MariaDB with Replication where I have 1 Master and 2 Slave nodes out of which one node seems corrupted and the above error is appearing.

Explaining the steps that I took post which the node stopped working

There was a system_user query that was stuck, tried killing it but unfortunately it was not getting deleted.
Restarted the slave node so that all queries get refreshed.
Post server restart, MySQL stopped working and was giving a socket error.

Please help me with the solution guys. Not able to get through this error

Solution

I have solved same issue with bellow steps.

To check the current slave status execute command:

show slave status\G

You should see result similar to this:

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: provisioner-peer
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql-bin.000149
           Read_Master_Log_Pos: 919065590
                Relay_Log_File: mysql-relay-bin.000450
                 Relay_Log_Pos: 884188250
         **Relay_Master_Log_File: mysql-bin.000149**
              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: 1594
                    Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by run
ning 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or
 slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                  Skip_Counter: 0
           **Exec_Master_Log_Pos: 884187951**
               Relay_Log_Space: 919067911
               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: 1594
                Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by run
ning 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or
 slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 287
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.000 sec)

Important values you should notice are Relay_Master_Log_File and Exec_Master_Log_Pos. You will need them to correctly restart replication on your slave.

To restart replication execute following commands:

STOP SLAVE;

RESET SLAVE;

CHANGE MASTER TO master_log_file='mysql-bin.000149', master_log_pos=884187951;

START SLAVE;

To check if replication is working again, execute again command:

show slave status\G

Before you will call your slave as synced, check value of parameter Seconds_Behind_Master from the status command. In our case I have seen value (7971 seconds):

Seconds_Behind_Master: 7971

Within next few minutes was replication synced again with master and replication lag was 0s

Note : For safety backup your database first