0

I received an error during MariaDB replication that I'm not sure how to fix. Replication is a new topic to me and I acquired these databases and their architecture through a job, so I've done some research on my own. I'm not really a DBA but can work my around writing queries and stuff. Upon running show slave status on the master server, I get the following (truncated to relevant parts I think):

[
  {
    "Master_Log_File": "mysql-bin.000021",
    "Read_Master_Log_Pos": 358,
    "Relay_Log_File": "mysql-relay-bin.000004",
    "Relay_Log_Pos": 555,
    "Relay_Master_Log_File": "mysql-bin.000021",
    "Slave_IO_Running": "No",
    "Slave_SQL_Running": "Yes",
    "Seconds_Behind_Master": null,
    "Last_IO_Errno": 1236,
    "Last_IO_Error": "Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'",
    "Last_SQL_Errno": 0,
    "Last_SQL_Error": "",
    "Master_Server_Id": 2,
    "Using_Gtid": "No",
    "Parallel_Mode": "conservative",
    "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",
  }
]

Correct me if I'm wrong, but, according to my research the slave server is looking for the log file named: mysql-bin.000021. When I run show binary logs I see no file even close to this number on the master server (truncated):

[
  {
    "Log_name": "mysql-bin.004089",
    "File_size": 1073743701
  },
  {
    "Log_name": "mysql-bin.004090",
    "File_size": 1073742421
  },
  {
    "Log_name": "mysql-bin.004091",
    "File_size": 1073742076
  },
  {
    "Log_name": "mysql-bin.004092",
    "File_size": 1073742143
  },
  ...
  ... // truncating
  ...
  {
    "Log_name": "mysql-bin.004160",
    "File_size": 821591301
  }

I think this means that replication hasn't been working for a while, right? Since the log file it's looking for is so behind all of the ones that exist on the server?

How can I resolve this? Can I just point the slave server to read from the most recent log file on the master? Or would it be best at this point to take a dump of the master server and rebuild the slave server entirely?

EDIT: running show slave status on slave server:

Slave_IO_State: Waiting for master to send event
Master_Log_File: mysql-bin.004161
Read_Master_Log_Pos: 223529346
Relay_Log_File: mysql-relay-bin.007660
Relay_Log_Pos: 153648997
Relay_Master_Log_File: mysql-bin.004161
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Exec_Master_Log_Pos: 163496960
Seconds_Behind_Master: 179
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Using_Gtid: No
9
  • show slave status should be run on the replica/slave and not hte master. How did you provision the replica and determine the log file name and position to use in the change master to command?
    – danblack
    Nov 29, 2021 at 4:35
  • @danblack oh! Ok that makes sense. I'll run that now and post the results. I didn't actually set up the master/slave relationship myself so I haven't ran any change master to commands yet. Nov 29, 2021 at 4:43
  • @danblack I've added the output of show slave status on the replica server. Nov 29, 2021 at 5:08
  • So apart from a small delay its going ok? If this is an ongoing problem look at increasing slave_parallel_threads.
    – danblack
    Nov 29, 2021 at 5:10
  • @danblack to be honest with you, I thought that the 1236 error that was shown when running slave status on the master server meant that replication had stopped working. Does this indicate that replication is indeed working? Nagios is used for monitoring, and we received an error a couple of weeks ago that the server had lost connection and was 1000+ seconds behind the master. Does this output mean that it has caught up? Nov 29, 2021 at 5:16

0

Your Answer

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