Accessing to a MariaDB Galera Cluster on Ubuntu 16.04 from SpringBoot app

Question

I've installed a MariaDB Galera Cluster on Ubuntu 16.04 in 2 nodes.

Here the info from node1:

root@localhost:~# sudo more /etc/mysql/conf.d/galera.cnf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.143.81,192.168.148.75"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.143.81"
wsrep_node_name="Node1"

and Here the info from node2:

mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.143.81,192.168.148.75"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.148.75"
wsrep_node_name="Node2”

I am accessing the DB from a Java app. with these properties:

spring.datasource.url=jdbc:mysql://192.168.143.81:3306/can_peris_db
spring.datasource.username=peris
spring.datasource.password=3nRam0nD3L3s0l1v3s
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=true
hibernate.dialect=org.hibernate.dialect.MySQLDialect

Everything is working fine and the data is replicated in both DBs.. but when I shut down the server 192.168.143.81 I got the error java.net.NoRouteToHostException: No route to host (Host unreachable)

Is there a way to connect automatically to the host that is available ?

Answer 1

You need to set up a floating IP and install Keepalived

Answer 2

You have a couple of options. Take a look to the MariaDB JDBC connector documentation.

For example, if you want to have just a failover scheme you should set the jdbc url in this way:

jdbc:mariadb:sequential://mainHost,failoverHost/your_database?autoReconnect=true

The "sequential" mode means the application will try to connect always to the first host and switch to the failover node only if the first node is not available.

Hope this helps.