MySQL Connector/J loadbalance feature not working when user credentials changed
Posted by: Shayaan Munshi
Date: September 07, 2018 07:27AM

I am using the MySQL Connector/J loadbalance feature with two MySQL databases. The requirement here is to recycle data on each database one by one to achieve zero downtime. So weekly or monthly data on each database will be recycled.

First approach
The first approach we took is by changing the port of each instance to recycle the data

Stopped mysql instace(localhost:3306)
Changed the port
Started mysql instance
Recycled the data
Stopped mysql instance
Changed the port back to 3306 as per jdbc loadbalance URL
Started mysql intance back
Repeated Steps 1- 7 for mysql intance 3307
This is working like a charm however we are restricted to use this approach.

Second approach
Second approach is to change the user credentials of each database one by one.

removed user abcd mysql instace(localhost:3306)
Restart service mysql instace(localhost:3306)
Recycled the data with some other user credentials
Added user user: abcd password: abcd back
Restart service mysql instace(localhost:3306)
This approach is not working and non of the queries are being executed after changing the credentials and restarting the service. None of the queries are executed.

Do I need to add some more parameters as per MySQL reference https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html?

Is there any other way I can achieve Zero downtime without changing the port and using loadbalance feature of JDBC?

Options: ReplyQuote


Subject
Written By
Posted
MySQL Connector/J loadbalance feature not working when user credentials changed
September 07, 2018 07:27AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.