I’ve got a master server replicating with a slave server. But I can’t get that slave to be a master itself to replicate to a 3rd slave-only server.
Meaning, this is the configuration: Master (data) -> Slave/Master (backup) -> Slave (reporting).
The idea is that data is live data. Slave/Master is active backup. And Slave is for pulling one-off reports for reporting & the like so that active backup isn’t ever put in a situation where it can’t receive events because its tables are locked for an extended period of time.
data -> backup works fine.
backup -> reporting won’t replicate at all. I’ve configured backup and reporting identically, aside from who their Master is.
data is CentOS 7 with MariaDB 10.4.
backup & reporting are CentOS 8 with MariaDB 10.3.
data has this in its my.cnf (commented lines were stripped):
[server]
[mysqld]
log-bin=data-bin-log
server_id=101
log-basename=data-rep-log
report-host=data.domain.com
[galera]
[embedded]
[mariadb]
[mariadb-10.4]
backup & reporting have this in their my.cnf respectively (commented lines were stripped):
[server]
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server_id=201
log-bin=backup-binary-log
log-basename=backup-rep-log
report-host=backup.domain.com
replicate-do-db=data
[galera]
[embedded]
[mariadb]
[mariadb-10.3]
[server]
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server_id=301
log-bin=reporting-binary-log
log-basename=reporting-rep-log
report-host=reporting.domain.com
replicate-do-db=data
[galera]
[embedded]
[mariadb]
[mariadb-10.3]
I’ve created the same user in both data & backup for replication with the correct REPLICATION privilege. And both backup & reporting are using the same user/pass to do it.
On data for backup:
CREATE USER 'rep'@'backup.domain.com' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'backup.domain.com' IDENTIFIED BY 'pass';
On backup for reporting:
CREATE USER 'rep'@'reporting.domain.com' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'reporting.domain.com' IDENTIFIED BY 'pass';
On backup for data:
CHANGE MASTER TO MASTER_HOST='data.domain.com', MASTER_USER='rep', MASTER_PASSWORD='pass', MASTER_LOG_FILE='data-rep-log-bin.000008', MASTER_LOG_POS=263094418, MASTER_CONNECT_RETRY=10;
START SLAVE;
On reporting for backup:
CHANGE MASTER TO MASTER_HOST='backup.domain.com', MASTER_USER='rep', MASTER_PASSWORD='pass', MASTER_PORT=3306, MASTER_LOG_FILE='backup-rep-log-bin.000005', MASTER_LOG_POS=7708175, MASTER_CONNECT_RETRY=10;
START SLAVE;
The SQL threads are running on both backup & reporting, but reporting shows no new events being replicated while backup does show these events.
I get no errors. The SQL threads don’t stop. reporting just doesn’t replicate any records and seems to not receive any events.
data:
MariaDB > SHOW MASTER STATUS;
+------------------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------------+-----------+--------------+------------------+
| data-rep-log-bin.000009 | 154885934 | | |
+------------------------------+-----------+--------------+------------------+
1 row in set (0.000 sec)
backup:
MariaDB > SHOW SLAVE STATUS \G;
Slave_IO_State: Waiting for master to send event
Master_Host: data.domain.com
Master_User: rep-user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: data-rep-log-bin.000009
Read_Master_Log_Pos: 253617066
Relay_Log_File: backup-rep-log-relay-bin.000003
Relay_Log_Pos: 253617377
Relay_Master_Log_File: data-rep-log-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: data
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: 151617566
Relay_Log_Space: 225214941
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Using_Gtid: No
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 646362
1 row in set (0.000 sec)
MariaDB > SHOW MASTER STATUS \G;
File: backup-rep-log-bin.000005
Position: 7709291
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)
reporting:
MariaDB > SHOW SLAVE STATUS \G;
Slave_IO_State: Waiting for master to send event
Master_Host: backup.domain.com
Master_User: rep-user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: backup-rep-log-bin.000005
Read_Master_Log_Pos: 7709291
Relay_Log_File: reporting-rep-log-relay-bin.000004
Relay_Log_Pos: 564
Relay_Master_Log_File: backup-rep-log-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: data
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: 7709291
Relay_Log_Space: 1730
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Seconds_Behind_Master: 0
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 201
Using_Gtid: No
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 6
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.118 sec)
.