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.
