Return to Level1Techs.com

MariaDB Replication: One configuration working, an identical one not working

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)

I’m thinking this is my problem:

Frustrating because the default is ON for version > 8.0.4, but all of mine are set to OFF :thinking:.