Monday, February 17, 2020

MySQL InnoDB Cluster Replication via Router to Slave Single Node using SSL

This article is written to share how to setup SSL Replication between MySQL InnoDB Cluster and Slave single node via MySQL Router.

It is for Demo Purpose ONLY.

The video below shows the Replication working between Primary Node failover in the MySQL InnoDB Cluster.   The Replication switches to another Primary Node via the MySQL Router.

The General Steps as follows :
Setup as follows (Demo only)
Virtual Machine 1
1. A working MySQL InnoDB Cluster

Virutal Machine 2
2. A working MySQL Node as Slave
3. A working MySQL Router setup on Slave Node to point to the MySQL InnoDB Cluser on VM1.

The key part is to ensure the "key files" to be the same on each node of the MySQL InnoDB Cluster.

For the InnoDB Cluster on VM1 setup :
For example with MySQL InnoDB Cluster in the demo setup :
   Node1 DataDir : /home/mysql/data/3310
   Node2 DataDir : /home/mysql/data/3320
   Node3 DataDir : /home/mysql/data/3330

Copy all the *.pem files from datadir of Node1 to Node2 and Node3 datadir.  Thereafter restarting all the servers and bringing the InnoDB Cluster ONLINE ensures the keys are the same for ALL nodes.

For the SINGLE node slave in DR (VM2), the setup as follows
  Setup MySQL Router to connect to the  MySQL InnoDB Cluster on VM1.  So that the 6446 as primary node connection is always going into the RW node.

  Setup the MySQL Server as standalone Server on DR with proper setup (empty or restored backup from MySQL Server on InnoDB Cluster.)
   Copy the public key file (public_key.pem) file from the VM1 with those Server setup to VM2.   (e.g. the location of the file as /opt/download/lab/keys/public_key.pem)

  The MySQL Replication Channel can be setup as follows (Because the Router is sitting on the server VM2 and the port number is 6446.  The replication is setup to connect to ROUTER with the master_public_key defined with the copied "public_key.pem" from VM1.

mysql -uroot -h127.0.0.1 -P<the port of the slave>  << EOL1
change master to
for channel 'channel1';

start slave for channel 'channel1';
Once the server is setup properly, the replication channel is started. 

Thanks for reading.

No comments:

Post a Comment