CODEWITHSUNDEEP
mysql
Niklas Riecken
Niklas Riecken

Reputation: 299

Replicate NDBCluster to InnoDB

We operate a NDBCluster Version 8 and want to replicate a database in this cluster into a "standalone" InnoDB Database System. I followed this guide to implement the replication: http://johanandersson.blogspot.com/2012/09/mysql-cluster-to-innodb-replication.html but it seems, I have done something wrong and I don't know, how to debug it. I know the guide is very old, but I also read the chapter about the NDB Replication on MySQL https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-replication.html

Current behavior

The replication I set up by now replicates every statement like "Create Database" or "Create Table", even "INSERT" and "UPDATE" statements, as long as the NDBCLUSTER as Engine is not involved. I.e.: I can create a new Database in the cluster and it will be perfectly replicated to the slave. I can insert new datarows to a InnoDB table that I created on the cluster (I know, it is not actually saved in the cluster) and those data rows will be replicated to the slave. Inserting new data rows to a NDBCLUSTER table will result in no replication at all. If I execute the statement "SHOW MASTER STATUS" on the SQL Node in the Cluster before the insert to a NDBCLUSTER table and afterwords, I can see, that the Binlog Position has not changed at all.

Wanted behavior

I want the Replication to replicate data from tables with the NDBCLUSTER Engine to my Slave, that runs on InnoDB. I know there are limitations and we have to be carefull how to structure tables, that everything is compatible, etc.

Current Setup

We operate a MySQL NDB Cluster with 2 Management Nodes, 2 SQL Nodes and 4 Data Nodes and we want to replicate to one InnoDB Server

Setup

All Systems have Version 8 installed, run on Ubuntu 18.04 and all Systems have the Cluster Version of the MySQL Server installed. On of the SQL Nodes is chosen to do the replication. The config (/etc/my.cnf) of the Slave in the mysqld section is:

server-id=101
log-slave-updates=0
log-bin=binlog
binlog-format=MIXED
slave-exec-mode=IDEMPOTENT
expire-logs-days=5

The config of the Master (/etc/my.cnf) in the mysqld configuration (besides some cluster specific configuration) is:

binlog-format=ROW
log-bin=binlog
server_id=120

They are both at the same Position regarding the Binlog file.

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |    10547 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.175.110
                  Master_User: replicationUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 10547
               Relay_Log_File: innodb-1-relay-bin.000005
                Relay_Log_Pos: 365
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
[...]

Any Help in how to solve this Situation is appreciated!

Upvotes: 0

Views: 564

Answers (1)

Niklas Riecken
Niklas Riecken

Reputation: 299

Since MySQL Version 8.0.16 in addition to "log-bin" also a second variable has to be set in the my.cnf of the master & slave:

ndb-log-bin=ON

See https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-options-variables.html#sysvar_ndb_log_bin for more information.

Upvotes: 0

Related Questions