The following URL can be a good starting point to understand what this feature is about.
https://www.slideshare.net/Grypyrg/mysql-connectors-8019-dns-srv
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-dns-srv.html
https://learn.hashicorp.com/consul/security-networking/forwarding
MySQL Router in MySQL InnoDB Cluster setup offers the transparent access to the backend database servers. However, the application has to know the IP Address of the Router in order to get access to the InnoDB Cluster.
In general, MySQL Router can be installed together with the application. By doing this, the application can access to 'localhost' / 127.0.0.1 as IP address to the MySQL Router.
If the MySQL Router is installed on separated machine or VM, there is external service to discover the ROUTER IP address and also about the health check with the Router(s).
To achieve external access to Router instead of 'localhost' access within the same box deployment with the application, there are couple of options
1. Load Balancer
2. Virtual IP failover
3. Hard coded in application about the IPs of MySQL Routers
4. New!! - Using DNS SRV support from MySQL Connector
(other options such as VM respawn, etc...)
This article is written to share a DEMO setup of DNS SRV support in MySQL Connector/J 8.0.19 with Consul.
The DEMO setup with Consul is not the scope of this article. In general, it provides a simple testing idea with the DNS SRV support with Connector/J in 8.0.19.
Environment
1. MySQL InnoDB Cluster
A running 3 node InnoDB Cluster.
For example :
MySQL [primary ssl] JS> dba.getCluster().status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "primary:3310",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"primary:3310": {
"address": "primary:3310",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"primary:3320": {
"address": "primary:3320",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"primary:3330": {
"address": "primary:3330",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "primary:3310"
}
2. MySQL Router running on 2 VMs - port = 6446 as RW routing
a. IP 192.168.56.41:6446
b. IP 192.168.56.42:6446
[mysql@virtual-42 lab]$ mysql -udemo -pdemo -h192.168.56.41 -P6446 -e "select @@hostname, @@port;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+--------+
| @@hostname | @@port |
+----------------------+--------+
| virtual-41.localhost | 3310 |
+----------------------+--------+
[mysql@virtual-42 lab]$ mysql -udemo -pdemo -h192.168.56.42 -P6446 -e "select @@hostname, @@port;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+--------+
| @@hostname | @@port |
+----------------------+--------+
| virtual-41.localhost | 3310 |
+----------------------+--------+
3. Consul and Register service router.service.consul to point to the 2 VM Router Services.
4. Java Application connecting to Router Service
1. Consul Download and Setup
Refer to https://www.consul.io/docs/install/index.html
a. Download the binary for the platform
b. Assuming this is Linux box environment, as demo : copy the binary "consul" to /sbin (as root)
2. Create the service json /etc/consul.d/router.json
# cat /etc/consul.d/router.json
{
"services" : [
{
"id": "router1",
"name": "router",
"tags": [ "rw" ],
"address": "192.168.56.41",
"port": 6446,
"checks": [
{
"args": ["/demo/consul/check1-6446.sh"],
"interval": "10s"
}
]
},
{
"id": "router2",
"name": "router",
"tags": [ "rw" ],
"address": "192.168.56.42",
"port": 6446,
"checks": [
{
"args": ["/demo/consul/check2-6446.sh"],
"interval": "10s"
}
]
}
]
}
2. Health Check Script for Services (/demo/consul/check1-6446.sh and /demo/consul/check2-6446.sh)
Creating the health check script as (Assuming mysql program is in the path)
<check1-6446.sh>
#! /bin/bash
mysql -ugradmin -pgrpass -hprimary -P6446 -e "select 1;" > /dev/null 2>&1
if [ $? != 0 ]
then
exit 2
fi
<check2-6446.sh>
#! /bin/bash
mysql -ugradmin -pgrpass -hsecondary -P6446 -e "select 1;" > /dev/null 2>&1
if [ $? != 0 ]
then
exit 2
fi
3. Startup consul agent with root (so that it listens to port=53 instead of 8600)
The default DNS PORT is 8600. However DNS service is to be run on port 53. Connector search for DNS Service which has to be running on port 53.
Options
a. consul running on port 53
b. forwarding consul service to DNS services (Please check on other resources : e.g. https://learn.hashicorp.com/consul/security-networking/forwarding )
Note : This article is demo only. consul is used but there are other choices.
# consul agent -dev -enable-script-checks -dns-port=53 -config-dir=/etc/consul.d &
Once the agent is started, you can find the services running as
# curl http://127.0.0.1:8500/v1/health/checks/router
[
{
"Node": "virtual-42.localhost",
"CheckID": "service:router1",
"Name": "Service 'router' check",
"Status": "passing",
"Notes": "",
"Output": "",
"ServiceID": "router1",
"ServiceName": "router",
"ServiceTags": [
"rw"
],
"Type": "script",
"Definition": {},
"CreateIndex": 12,
"ModifyIndex": 14
},
{
"Node": "virtual-42.localhost",
"CheckID": "service:router2",
"Name": "Service 'router' check",
"Status": "passing",
"Notes": "",
"Output": "",
"ServiceID": "router2",
"ServiceName": "router",
"ServiceTags": [
"rw"
],
"Type": "script",
"Definition": {},
"CreateIndex": 13,
"ModifyIndex": 15
}
]
4. Change the /etc/resolv.conf
search localhost
nameserver 127.0.0.1
domain consul
5. Test the Java Application
a. Make sure the Connector/J 8.0.19 driver is downloaded.
b. Sample Source Attached -
c. To run :
java -cp ./mysql-connector-java-8.0.19.jar:. LBDemo01
The output is simply like this
java -cp ./mysql-connector-java-8.0.1jar:. LBDemo01
Spawned threads : 3
By killing/stopping one of the ROUTER, the application can continue to run.
Sample Java Application - LBDemo01.java
cat LBDemo01.java
// package mysql.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.mysql.cj.jdbc.Driver;
public class LBDemo01 {
private static long ttltime=0;
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String database = "test";
private static String baseUrl = "jdbc:mysql+srv://demo:demo@router.service.consul";
private static String user = "demo";
private static String password = "demo";
public static void main(String[] args) throws Exception {
createTable();
ArrayList<Thread> threads = new ArrayList<Thread>(10);;
for (int i=0;i<3;i++) {
Thread t = new Thread(new Repeater());
t.start();
threads.add(t);
}
System.out.println("Spawned threads : " + threads.size());
for(int i=0;i<threads.size();i++) {
((Thread) threads.get(i)).join();
}
System.out.println("Finished - " + ttltime);
}
private static void createTable() throws ClassNotFoundException, SQLException {
Connection c = getNewConnection();
try {
c.setAutoCommit(false);
Statement s = c.createStatement();
s.executeUpdate("create table if not exists test.mytable (f1 int auto_increment not null primary key, f2 varchar(200)) engine=innodb;");
c.commit();
} catch (SQLException e) {
e.printStackTrace();
}
c.close();
}
static Connection getNewConnection( ) throws SQLException, ClassNotFoundException {
java.util.Properties pp = new java.util.Properties();
pp.setProperty("user", user);
pp.setProperty("password", password);
// black list for 60seconds
pp.setProperty("loadBalanceBlacklistTimeout", "60000");
pp.setProperty("autoReconnect", "false");
Class.forName(driver);
return DriverManager.getConnection(baseUrl, pp);
}
static void executeSimpleTransaction(Connection c, int conn, int trans){
try {
c.setReadOnly(false);
c.setAutoCommit(false);
Statement s = c.createStatement();
s.executeUpdate("insert into test.mytable (f2) values ('Connection: " + conn + ", transaction: " + trans +"');" );
c.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static class Repeater implements Runnable {
public void run() {
for(int i=0; i < 1000; i++){
try {
Connection c = getNewConnection();
long mystart, myend, myttl=0;
for(int j=0; j < 10; j++){
// To register the start time
mystart = System.currentTimeMillis();
executeSimpleTransaction(c, i, j);
// To time the execution time and save it onto the totaltime
myend = System.currentTimeMillis();
myttl += (myend - mystart);
incTTL(myttl);
Thread.sleep(Math.round(100 * Math.random()));
}
c.close();
Thread.sleep(200);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public synchronized static void incTTL(long m) {
ttltime += m;
}
}
No comments:
Post a Comment