CODEWITHSUNDEEP
mysqlreplicationmaster-slave
EricC
EricC

Reputation: 1439

mysql replication with multiple slave instances on a host, relay bin keeps changing

I have 2 slave mysql instances running on a host, which replicates data from a master on a different host. All instances are mysql 5.6. The relay bin log for the slave instances keep on changing even though there is no change from the master. The following is the show slave status\G for slave1:

           Slave_IO_State: Waiting for master to send event
          Master_Log_File: mysql-bin.000008
      Read_Master_Log_Pos: 210424
           Relay_Log_File: mysqld1-relay-bin.000878
            Relay_Log_Pos: 354
    Relay_Master_Log_File: mysql-bin.000008
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
                     ... : ...

A while later slave1's Relay_Log_File changed,

           Slave_IO_State: Waiting for master to send event
          Master_Log_File: mysql-bin.000008
      Read_Master_Log_Pos: 210424
           Relay_Log_File: mysqld1-relay-bin.001374
            Relay_Log_Pos: 354
    Relay_Master_Log_File: mysql-bin.000008
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
                     ... : ...

but no change was made to the master, slave1 or slave2. Same thing is happening for slave2. I suspect the two slaves are somehow sharing config/setup which they are not supposed to, but I was not able to determine what went wrong. The my.cnf file has

# The MySQL server mysqld1
[mysqld1]
user            = mysql
port            = 3301
pid-file        = /usr/local/mysql1/data/mysqld1.pid
socket          = /tmp/mysql1.sock
datadir         = /usr/local/mysql1/data
log-bin=mysql-bin
binlog_format=mixed
gtid-mode=on
disable-gtid-unsafe-statements=true
log-slave-updates=true # needed to enable gtid
sync-master-info = 1
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id       = 2
innodb_data_home_dir = /usr/local/mysql1/data
innodb_log_group_home_dir = /usr/local/mysql1/data
innodb_flush_log_at_trx_commit = 1
sync_binlog=1

[mysqld2]
user            = mysql
port            = 3302
pid-file        = /usr/local/mysql2/data/mysqld2.pid
socket          = /tmp/mysql2.sock
datadir         = /usr/local/mysql2/data
log-bin=mysql-bin
binlog_format=mixed
gtid-mode=on
disable-gtid-unsafe-statements=true
log-slave-updates=true # needed to enable gtid
sync-master-info = 1
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id       = 3
innodb_data_home_dir = /usr/local/mysql2/data
innodb_log_group_home_dir = /usr/local/mysql2/data
innodb_flush_log_at_trx_commit = 1
sync_binlog=1

Has anyone encountered something like this? Would running multiple instances this way require custom naming of relay logs? Thanks.

Upvotes: 2

Views: 5093

Answers (1)

EricC
EricC

Reputation: 1439

It turned out the log-bin name needs to be different for the two slaves. I have changed them from

[mysqld1]
log-bin=mysql-bin
[mysqld2]
log-bin=mysql-bin

to

[mysqld1]
log-bin=slave1-bin
[mysqld2]
log-bin-slave2-bin

The sample configuration from Replication and auto-failover made easy with MySQL Utilities gave hints about this.

Upvotes: 2

Related Questions