Practical record of MySQL 5.6 master-slave error reporting

1. Problem symptoms

Version: MySQL 5.6, using the master-slave replication structure configured using the traditional binlog file & pos method.

After the instance is restarted, the master-slave replication error is shown in the figure above.

2. Error meaning

The error is divided into 2 parts.

Part I

• Client requested master to start replication from position > file size;
• the first event 'mysql-bin.000398' at 163800795,the last event read from './mysql-binlog.000398' at 4,the last byte read from './mysql-bin.000398' at 4'

Part I

This part comes from the DUMP thread function of the main library

mysql_binlog_send
 ->sender.run()
  ->Binlog_sender::init
    ->Binlog_sender::check_start_file

 if ((file = open_binlog_file(&cache, m_linfo.log_file_name, &errmsg)) < 0) 
 {
  set_fatal_error(errmsg);
  return 1;
 }

 size = my_b_filelength(&cache);
 end_io_cache(&cache);
 mysql_file_close(file, MYF(MY_WME));

 if (m_start_pos > size)
 {
  set_fatal_error("Client requested master to start replication from "
          "position > file size");
  return 1;
 }

The key is the two values ​​​​m_start_pos and size, where m_start_pos comes from the position that needs to be read from the library. And size is the size of this binlog file, so it is easy to understand that if the POS point required by the IO thread is larger than the size of this binlog file, then it is naturally wrong.

Part 2

This part also comes from the DUMP thread

mysql_binlog_send
 ->sender.run()
   ->Binlog_sender::init
   ->while (!has_error() && !m_thd->killed)
   #If normal, start looping and reading binlog events here. If there is an error, continue with the following logic. #If there is a reading error, report an error my_snprintf(error_text, sizeof(error_text),
         "%s; the first event '%s' at %lld, "
         "the last event read from '%s' at %lld, "
         "the last byte read from '%s' at %lld.",
         m_errmsg,
         m_start_file, m_start_pos, m_last_file, m_last_pos,
         log_file, my_b_tell(&log_cache));

Here we mainly look at m_start_pos and m_last_pos. In fact, m_start_pos is the position information that needs to be read from the slave library, which is consistent with the previous error report, and m_last_pos comes from the dump thread, which is the last read position. Obviously, it is not read once here, so the position is the initial pos 4.

3. Possible causes

After analysis, I think the most likely reason is related to sync_binlog.

If we do not set it to 1, the OS cache may not be flushed to disk. If the main library server crashes and restarts directly, this problem may easily occur.

After a quick Google search, I found that most of these errors are caused by server crashes and sync_binlog not being set to 1.

This also proves our statement.

Finally, I checked that the main database of the problem database was indeed not set to double 1.

Through this small case, we have more deeply realized the importance of setting up double 1s.

Summarize

This is the end of this article about MySQL 5.6 master-slave error reporting. For more relevant MySQL 5.6 master-slave error reporting content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!