0

I am trying to get a database that is the main database but replicates one database from another server to replicate both the replicated database and the local database to another slave.

SERVER1: master, only replicating one database, cdr. I do not have access to this server config or the database except as slave replication

SERVER2: main production database replicating the cdr database from SERVER1 with no issues This is an 8TB database with approximately 10k qps most of the time. The only time I can make changes to my.cnf and restart the database is late at night.

SERVER3: new server, trying to replicate from SERVER2 it's data and the cdr database from SERVER1

I did have the cdr database from SERVER2's replication from SERVER1 replicating to SERVER3 but broke it trying to reconfigure SERVER2 > SERVER3 to get the other databases replicating.

SERVER 1: Replicates one database cdr to SERVER2

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ahost
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.002513
          Read_Master_Log_Pos: 56488490
               Relay_Log_File: mysqld-relay-bin.004995
                Relay_Log_Pos: 56488638
        Relay_Master_Log_File: mariadb-bin.002513
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: cdr
          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: 56488490
              Relay_Log_Space: 56488840
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1000024
1 row in set (0.00 sec)

SERVER 2: Main production server, one database gets replicated from SERVER1, cdr and this works fine. I did manage to get cdr database replicating to SERVER3 from SERVER2 but broke it trying to get other databases like rdrs to replicate.

my.cnf

[mysqld]

server-id = 500
expire_logs_days = 3
slave-skip-errors = 1062,1051
log_slave_updates=ON
replicate_do_db=cdr
log_bin = /var/lib/mysql/mysql-bin

SERVER 3: Slave of SERVER2, should replicate everything from SERVER2 including the updates from SERVER1

my.cnf

[mysqld]
server-id=1222

replicate-do-db=rdrs
replicate_do_db=cdr
replicate-do-table = clover.acct
log_slave_updates = ON
#replicate_ignore_table=clover.testdata_skiprep

show slave status

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: bhost
                  Master_User: replicationv2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000462
          Read_Master_Log_Pos: 286428248
               Relay_Log_File: mysqld-relay-bin.000375
                Relay_Log_Pos: 81106343
        Relay_Master_Log_File: mysql-bin.000462
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: rdrs,cdr
          Replicate_Ignore_DB: 
           Replicate_Do_Table: clover.acct
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 286428248
              Relay_Log_Space: 286428823
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 500
1 row in set (0.00 sec)

I have been working on this for several days now and cannot get it figured out. I think it's something simple but not sure what's missing. I do see in SERVER3's relay logs the queries but they are not executing.

2
  • Are the two SLAVE STATUSs from Server2 and Server3, respectively? (It is unclear.) Is Server1 replicating from some other server?
    – Rick James
    Nov 3, 2021 at 22:39
  • Correct. That I am not sure of, we just replicate the one database but I will check with our client if they are replicating Server1 from another server. Nov 4, 2021 at 15:02

1 Answer 1

0

In order to have all of Server2 to be replicated to Server3:

Get rid of all binlog_(do/ignore) on Server2.

Get rid of all (replicate/binlog)_(do/ignore) on Server3.

2
  • Ok, on Server3 for now we are migrating one database at a time from Server2 and only want to replicate the databases that we get copied over. For now it's cdr and rdrs, then I'll add others as we go along. For Server2 there is not any binlog_(do/ignore) and on Server3 wouldn't I need to use replicate_do_db in order to replicate just the databases that we are currently migrating? We have 15 databases the Server2 so trying to do it one at a time. Nov 4, 2021 at 15:05
  • It worked. I removed Get rid of all binlog_(do/ignore) on Server2. Get rid of all (replicate/binlog)_(do/ignore) on Server3. and any of the databases that I do not want to replicate right now I have renamed them as I did have other databases with table structures that I did not want to replicate now. Replication is working on all of the databases that I wanted. Thanks for your help and quick reply. Nov 4, 2021 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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