2

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.

1 Answer 1

0

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;
3
  • 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...
    – plisken
    Commented Feb 9, 2021 at 8:26
  • do one thing instead of 127.0.0.1, specify server ip address. Commented Feb 9, 2021 at 8:43
  • this just gives error along the lines of 'error connecting to master '[email protected]:3306''. I've updated original post with some additional information.
    – plisken
    Commented Feb 9, 2021 at 10:19

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.