1

I configured two MariaDB instance in Master-Slave configuration. As a test, I tried to see what happens when I manually purge binlogs. To my amazement, I have seen that even if I get an error message on the slave, the replica still works! Is this normal? This is the status after the show slave status command:

             Master_User: root
              Master_Port: 3306
            Connect_Retry: 10
          Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 2314
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 535
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: No
        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: 2314
          Relay_Log_Space: 1119
          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: 1236
            Last_IO_Error: Got fatal error 1236 from master when reading data from 
            binary log: 'binlog truncated in the middle of event; consider out of 
           disk space on master; the first event 'mysql-bin.000001' at 2314, the last 
           event read from 'mysql-bin.000001' at 2314, the last byte read from 
           'mysql-bin.000001' at 2317.'
           Last_SQL_Errno: 0
           Last_SQL_Error:

As far as I can see Slave_SQL_Running is still active as opposed to Slave_IO_Running. Is he the one still running the replication? What happens if I don't reset Slave_IO_Running correctly and how can I possibly reset it?

Thank you in advance!

1

Purging the last binlog on the Primary is risky.

The data in the replication stream is sent to the Replica(s) at the same time it is added to the last binlog on the Primary.

If the network goes down, the binlog is vital for providing the replication stream to the Replicas when the network comes back up.

I like to keep enough binlogs to hold a week's worth of data, and hope that a human can get involved in time to repair whatever is broken within a week. That way, the Replicas, though delayed, won't be missing any data.

0

Yes, replicas function fully even though they aren't replicating.

"IO" is the communication with the master where new binary logs are fetched. "SQL" is the application of these fetched binary logs on the tables and everything in the server.

At the moment you have lost everything beyond the master 'mysql-bin.000001' at 2314.

If what is after that point isn't significant you can use CHANGE MASTER TO to the binary log file/position on the master that does exist:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_LOG_FILE='mysql-bin.000002',
   MASTER_LOG_POS=4;
START SLAVE;

MASTER_LOG_POS=4 just skips the few bytes of the header.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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