Mysql – fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

mariadbmaster-slave-replicationMySQLmysqld-multi

I'm getting the following 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'

I've tried setting this up on 3 different machines now and initially, all seemed well, but when I went back to it, I had the same error, so I experimented on 2 other machines, both giving me the same.

Below are my cnf files;

my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
!includedir /etc/mysql

my_master.cnf

# MASTER MySQL database server configuration file created by master-slave-setup

# this is read by the standalone daemon and embedded servers
[server]

[mysqld1]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld_master.pid
socket      = /var/run/mysqld/mysqld_master.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql_master
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error_master.log

server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
binlog_do_db        = hn
innodb_flush_log_at_trx_commit  = 1
sync_binlog                 = 1
binlog-format               = ROW

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

my_slave.cnf

# SLAVE MySQL database server configuration file created by master-slave-setup

# this is read by the standalone daemon and embedded servers
[server]

[mysqld2]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld_slave.pid
socket      = /var/run/mysqld/mysqld_slave.sock
port        = 3307
basedir     = /usr
datadir     = /var/lib/mysql_slave
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error_slave.log
server-id       = 2
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
binlog_do_db        = hn
#binlog_ignore_db   = exclude_database_name
relay-log       = /var/log/mysql/relay-bin
#relay-log-index        = /var/log/mysql/relay-bin.index
#master-info-file   = /var/log/mysql/master.info
#relay-log-info-file    = /var/log/mysql/relay-log.info

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

my_multi.cnf

# MYSQLD_MULTI MySQL database server configuration file created by master-slave-setup

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass

Now I have tried changing the bingo_do_db to individual databases, as per the above and also left it commented out, but wasn't sure I wanted to be replicating the mysql database.

I'm using mysqld_multi to start and stop the instances and believe I have setup the users correctly.

I've added the following to my slave instance;

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_USER='replication',
MASTER_PASSWORD='replication',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=1652;

The show slave status\G is as follows;

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 127.0.0.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-000009
          Read_Master_Log_Pos: 1652
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin-000009
             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: 1652
              Relay_Log_Space: 248
              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: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

UPDATE:- the below is a copy of the logs which may help;

root@dl-sl-1:/var/log/mysql# dir
error_master.log  mysql-bin.000005  mysql-bin.000011  mysql-bin.index
error_slave.log   mysql-bin.000006  mysql-bin.000012  relay-bin.000004
mysql-bin.000001  mysql-bin.000007  mysql-bin.000013  relay-bin.index
mysql-bin.000002  mysql-bin.000008  mysql-bin.000014  replication.txt
mysql-bin.000003  mysql-bin.000009  mysql-bin.000015
mysql-bin.000004  mysql-bin.000010  mysql-bin.000016
root@dl-sl-1:/var/log/mysql# cat mysql-bin.index
/var/log/mysql/mysql-bin.000001
/var/log/mysql/mysql-bin.000002
/var/log/mysql/mysql-bin.000003
/var/log/mysql/mysql-bin.000004
/var/log/mysql/mysql-bin.000005
/var/log/mysql/mysql-bin.000006
/var/log/mysql/mysql-bin.000007
/var/log/mysql/mysql-bin.000008
/var/log/mysql/mysql-bin.000009
/var/log/mysql/mysql-bin.000010
/var/log/mysql/mysql-bin.000011
/var/log/mysql/mysql-bin.000012
/var/log/mysql/mysql-bin.000013
/var/log/mysql/mysql-bin.000014
/var/log/mysql/mysql-bin.000015
/var/log/mysql/mysql-bin.000016

I'm also getting the below when I make changes with CHANGE MASTER TO…

Last_SQL_Error: Error 'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication'@'%' IDENTIFIED BY 'replication''

which I hadn't noticed before. This is pretty much on a clean install.
Any and all help would be greatly appreciated.

Best Answer

You are running Master & Slave on same machine with port 3306 & 3307 respectively.

So, you need to login on slave use below syntax : mysql -u<<user>> -p <<password>> -P 3307

Then set replication :

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT='3306',
MASTER_USER='replication',
MASTER_PASSWORD='replication',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=1652;