How To: MySql replication between two clusters. capture data changes

Here , I just setup two VMs and following tutorial Installing and testing Mysql NDB Cluster on Centos Tutorial. On each Server, there is one NDB cluster with one API Node and two Data Nodes and one mgmt node.
  before we kick off the replication, first make sure the /etc/hosts file has the right entry for both Hosts. Otherwise you may get the error in mysql like no access.

101123  5:55:16 [Warning] IP address '192.168.30.22' could not be resolved: getnameinfo() returned error (code: -3). 101123  5:55:56 [Warning] IP address '192.168.30.22' could not be resolved: getnameinfo() returned error (code: -3).

Configuration changes on HOST Master.

On HostA mysql instance, create one user that will be used by slave server hostb to pull the replication information.

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'hostb' IDENTIFIED BY 'pass';

Chagne the /etc/my.cnf , add options to enable binlog logging on row level, and assign one server-id.

[mysqld] ndbcluster ndb-connectstring=localhost log-bin binlog-format=row server-id=1

backup the db using the mysql dump and copy to Slave server for the initial restore. (ALL DB objects will be covered in the mysqldump , like view , storeprocedue)

mysqldump –u root yourdatabase –master-data=1> ini.sql

Restart the Master mysqld daemon. that’s all the  changes.

Configuration changes on HOST Slave Server.

Assign one explicit server-id in the my.cnf

[mysqld] ndbcluster ndb-connectstring=localhost server-id=2

restore the db from the master backup

mysql –u root ourdatabase<init.sql

Change the master to the appropriate host.

CHANGE MASTER TO MASTER_HOST='hosta', MASTER_PORT=3306, MASTER_USER='repluser', MASTER_PASSWORD='pass';

start the slave service>.

slave start

 

Now any change will be synced to slave server.

if you have any problem to sync the data. always check the /usr/local/mysql/data/yourhostname.err

there are some system tables to maintain the binlog and replication status.

for example, in the master server mysqld which we enable the binarylog.
you can always query mysql.ndb_binlog_index.

 

here we have two log entries which represent two update in my case. If we want to replay the first one  in slave server. just stop the slave replication and change the master log file position.

here the log will be replayed and duplicate record will inserted into the table.

If we just want to capture Master change and push the change to other db store besides mysql.

we can change the slave server table engine to blackhole, which will never store any data.  then turn on the query logging.
now all changes on master data will be pushed to slave server , we can tell the change from the query log.