title: How do I address a MySQL replication slave error 1236
body: <p>This question follows up on my prior (unresolved) one: <a href="https://stackoverflow.com/questions/64624609/mysql-master-slave-replication-connection-failure">MySQL master-slave replication connection failure</a></p>
  <p>I decided to setup the simplest replication possible following <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html</a>. The idea is get anything working and then incrementally change to the needed settings. Cliff notes version: a 1236 error occurs. The end of this question lists why common sources of 1236 errors do not seem to be applicable.</p>
  <p>The master is MySQL 5.7.32 and is used only by me for this replication test. The replication database is <code>testdb</code> where <code>testdb</code> is the simplest possible. It contains no tables or data. The same issue occurs when <code>testdb</code> contains one simple table with 5 rows each consisting of 2 short VARCHARs and 2 INTs.</p>
  <p>Replica is MySQL 8.0.</p>
  <p>replication user: <code>repl_user</code>
  repl_user permission on <code>master</code>:</p>
  <pre><code>mysql&gt; select host,user,Repl_slave_priv,Repl_client_priv from mysql.user where user='repl_user';
  +---------------------------------+-----------+-----------------+------------------+
  | host                            | user      | Repl_slave_priv | Repl_client_priv |
  +---------------------------------+-----------+-----------------+------------------+
  | 127.0.0.1                       | repl_user | Y               | N                |
  | localhost                       | repl_user | Y               | N                |
  | %                               | repl_user | Y               | N                |
  | slave_ip                        | repl_user | Y               | N                |
  +---------------------------------+-----------+-----------------+------------------+
  </code></pre>
  <p>I can log into the master MySQL from the replica computer:</p>
  <pre><code>&gt;mysql -u repl_user -h master_ip -p -P 3306
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 15
  Server version: 5.7.32-log MySQL Community Server (GPL)
  </code></pre>
  <p>Dumped the relevant database on the master:</p>
  <pre><code>mysqldump -u root -p --databases testdb --master-data &gt; master_testdb_dump20201110.sql
  </code></pre>
  <p>Inserted it in the replica:</p>
  <pre><code>mysql -u root -p &lt; master_testdb_dump20201110.sql
  </code></pre>
  <p>On the master:</p>
  <pre><code>mysql&gt; SHOW MASTER STATUS;
  +-------------------+----------+--------------+------------------+-------------------+
  | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +-------------------+----------+--------------+------------------+-------------------+
  | mysqld-bin.000002 |      488 |              |                  |                   |
  +-------------------+----------+--------------+------------------+-------------------+
  </code></pre>
  <pre><code>mysql&gt; SHOW BINARY LOGS;
  +-------------------+-----------+
  | Log_name          | File_size |
  +-------------------+-----------+
  | mysqld-bin.000001 |       177 |
  | mysqld-bin.000002 |       488 |
  +-------------------+-----------+
  2 rows in set (0.00 sec)
  </code></pre>
  <p>On the replica:</p>
  <pre><code>
      CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='correct password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=488, master_port=3306;
      Query OK, 0 rows affected, 2 warnings (0.01 sec)
      start replica;
      show replica status\G
      *************************** 1. row ***************************
                   Replica_IO_State:
                        Source_Host: host_ip
                        Source_User: repl_user
                        Source_Port: 3306
                      Connect_Retry: 60
                    Source_Log_File: mysql-bin.000002
                Read_Source_Log_Pos: 488
                     Relay_Log_File: replica_name-relay-bin.000001
                      Relay_Log_Pos: 4
              Relay_Source_Log_File: mysql-bin.000002
                 Replica_IO_Running: No
                Replica_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_Source_Log_Pos: 488
                    Relay_Log_Space: 156
                    Until_Condition: None
                     Until_Log_File:
                      Until_Log_Pos: 0
                 Source_SSL_Allowed: No
                 Source_SSL_CA_File:
                 Source_SSL_CA_Path:
                    Source_SSL_Cert:
                  Source_SSL_Cipher:
                     Source_SSL_Key:
              Seconds_Behind_Source: NULL
      Source_SSL_Verify_Server_Cert: No
                      Last_IO_Errno: 13114
                      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:
        Replicate_Ignore_Server_Ids:
                   Source_Server_Id: 4
                        Source_UUID: e4e01e90-1e22-11eb-84b1-0015175ac510
                   Source_Info_File: mysql.slave_master_info
                          SQL_Delay: 0
                SQL_Remaining_Delay: NULL
          Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
                 Source_Retry_Count: 86400
                        Source_Bind:
            Last_IO_Error_Timestamp: 201110 07:53:50
           Last_SQL_Error_Timestamp:
                     Source_SSL_Crl:
                 Source_SSL_Crlpath:
                 Retrieved_Gtid_Set:
                  Executed_Gtid_Set:
                      Auto_Position: 0
               Replicate_Rewrite_DB:
                       Channel_Name:
                 Source_TLS_Version:
             Source_public_key_path:
              Get_Source_public_key: 0
                  Network_Namespace:
      1 row in set (0.00 sec)
  
  </code></pre>
  <p>The Relay_Log_File does exist on the replica and is a non-empty binary file.</p>
  <p>I did a tail -f on /var/log/mysql/mysql.log on both the master and replica:</p>
  <p>Replica log:</p>
  <pre><code>2020-11-10T15:58:04.075427Z        11 Query     CHANGE MASTER TO MASTER_HOST = 'master_ip', MASTER_USER = 'repl_user', MASTER_PASSWORD = &lt;secret&gt;, MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 488
  2020-11-10T15:58:07.411303Z        11 Query     start replica
  2020-11-10T15:58:07.413325Z        12 Connect Out       repl_user@master_ip:3306
  2020-11-10T15:58:10.750305Z        11 Query     show replica status
  </code></pre>
  <p>Master log:</p>
  <pre><code>2020-11-10T15:58:07.413034Z        20 Connect   repl_user@replica_ip on  using TCP/IP
  2020-11-10T15:58:07.413483Z        20 Query     SELECT UNIX_TIMESTAMP()
  2020-11-10T15:58:07.413744Z        20 Query     SELECT @@GLOBAL.SERVER_ID
  2020-11-10T15:58:07.413935Z        20 Query     SET @master_heartbeat_period= 30000001024
  2020-11-10T15:58:07.414132Z        20 Query     SET @master_binlog_checksum= @@global.binlog_checksum
  2020-11-10T15:58:07.414321Z        20 Query     SELECT @master_binlog_checksum
  2020-11-10T15:58:07.414529Z        20 Query     SELECT @@GLOBAL.GTID_MODE
  2020-11-10T15:58:07.414731Z        20 Query     SELECT @@GLOBAL.SERVER_UUID
  2020-11-10T15:58:07.414917Z        20 Query     SET @slave_uuid= 'a1932c42-9d74-11e7-ba23-0015175696ac'
  2020-11-10T15:58:07.415394Z        20 Binlog Dump       Log: 'mysql-bin.000002'  Pos: 488
  2020-11-10T16:05:39.836945Z        14 Query     select host,user,Repl_slave_priv,Repl_client_priv from mysql.user where user='repl_user'
  
  </code></pre>
  <p>Master my.cnf:</p>
  <pre><code>[mysqld]
  default_authentication_plugin=mysql_native_password
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  symbolic-links=0
  general_log_file        = /var/log/mysql/mysql.log
  general_log             = 1
  server-id=4
  log-bin=mysqld-bin
  bind-address=0.0.0.0
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1
  max_allowed_packet=64M
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  user=mysql
  skip-slave-start
  </code></pre>
  <p>Replica my.cnf:</p>
  <pre><code>[mysqld]
  default_authentication_plugin=mysql_native_password
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  symbolic-links=0
  general_log_file        = /var/log/mysql/mysql.log
  general_log             = 1
  server-id=1
  log-bin=mysqld-bin
  bind-address=0.0.0.0
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1
  max_allowed_packet=64M
  skip-slave-start
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  user=mysql
  </code></pre>
  <p><em><strong>Addressing possible failure modes</strong></em></p>
  <p>@petergd pointed out <a href="https://www.percona.com/blog/2014/10/08/mysql-replication-got-fatal-error-1236-causes-and-cures/" rel="nofollow noreferrer">https://www.percona.com/blog/2014/10/08/mysql-replication-got-fatal-error-1236-causes-and-cures/</a> on possible 1236 causes and cures. (I've read many other pages too and MySQL documentation; I spend a couple days on this one simple setup.)</p>
  <ol>
  <li><p><code>max_allowed_packet</code> is set uniformly on both master and replica. 64M is the default value and greatly exceeds the largest row (as there are no rows of data).</p>
  </li>
  <li><p>&quot;This error occurs when the slave server required binary log for replication no longer exists on the master database server.&quot; The above posts demonstrate that the binary log does exist. I've also manually verified the existence of <code>mysqld-bin.000002</code> by issuing a <code>ls /var/lib/mysql</code></p>
  </li>
  <li><p>Missing binary files doesn't seem to be the case. Log expiration is not enabled in my.cnf. <code>Source_Log_File</code> is present on the server in the <code>/var/lib/mysql</code> directory. <code>Relay_Log_File</code> and
  <code>Relay_Source_Log_File</code> are present on the replica in the <code>/var/lib/mysql</code> directory.</p>
  </li>
  </ol>