fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Question

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.

Answer 1

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;

• plisken: Thanks for that, already done that, but note that I did miss out the port in my explanation above, however if you look at my "show slave status\G" dump, you'll see it does indeed point to 127.0.0.1:3306. Really struggling with this one...
• JYOTI RAJAI: do one thing instead of 127.0.0.1, specify server ip address.
• plisken: this just gives error along the lines of 'error connecting to master '[email protected]:3306''. I've updated original post with some additional information.