0

Say, I had created a product (a mobile application) which serves a lot of clients. Initially, I did not think about data compliance issues and stored all their data on one server. Now, these clients want data compliance in such a way that their data is stored on different servers. What is the best way to segregate this data and write it to different servers with minimal changes in the application so that I am compliant with my clients and also do not incur a lot of cost?

Can proxies/load balancers be used here?

We can assume that we are getting Client ID in every CRUD operation based on which we can write some rule on the proxy to divert the traffic to a database server designated for that Client. This would mean that before performing a CRUD operation on the database, the proxy would have to parse the incoming statement and route it accordingly.

Essentially, if I have 3 clients with ID 1, 2 and 3. I want the database operations to get routed to Database A for Client 1, Database B for Client 2 and Database C for Client 3.

I think something of this sort can be done with HAProxy, nginx, ProxySQL etc. but not too sure whether that would work or not.

Is this a viable option at scale or otherwise? If not, what are the other options where changes in the application are minimal.

EDIT: Schemas of Database A, Database B and Database C are exactly the same.

Shadow
  • 33,525
  • 10
  • 51
  • 64
MontyPython
  • 2,906
  • 11
  • 37
  • 58
  • create db details table, FK of clientID - load DB class using these details – treyBake Jul 10 '18 at 11:40
  • 2
    "Now, these clients want data compliance in such a way that their data is stored on different servers." " I am compliant with my clients and also do not incur a lot of cost?" I would bill the clients more which wants a separted server because off data compliance – Raymond Nijland Jul 10 '18 at 11:50
  • @RaymondNijland - Yes, I would. But say, if we had to come up with a plan with minimal cost to us. – MontyPython Jul 10 '18 at 11:51
  • The mobile application (android fo example) can't direclly access a MySQL database so i assume there is a webbased API which has acces to the database?.. If you get the ClientID in the webbased API you should be able to connect to a other database server there like @ThisGuyHasTwoThumbs said already. – Raymond Nijland Jul 10 '18 at 11:56
  • Use spring microservices. – Sumesh TG Jul 10 '18 at 11:56
  • @SumeshTG - Why not a proxy between the application and databases. That way no change will be made to the application. – MontyPython Jul 10 '18 at 11:58
  • 1
    I'd say this depends on your deployment situation. For my current company, spinning up separate AWS instances of the application with different in-domain DNS locations was pretty easy and we ended up spending virtually no time on it. Note that this is a web app, though; you might end up needing separate config files or other things (which we certainly needed) for a mobile app. – Dylan Brams Jul 10 '18 at 12:00
  • @DylanBrams - Have you come across someone who has done it filtering traffic via a proxy? Writing rules in nginx or HAProxy or maybe using ProxySQL for the diversion? – MontyPython Jul 10 '18 at 12:06
  • 1
    i still would change the webbased API to reroute the connection and queries.. a MySQL proxy is pretty hard to setup and configure how the qeuries should route especially when you don't have a unique id in your query.. you could add a comment and the end off very query with the client id like so `WHERE ... #ClientID 1` and use the comment client id to reroute but still.i would not advice to use this approach. – Raymond Nijland Jul 10 '18 at 12:07
  • 1
    @MontyPython I've HEARD of it, but all the applications I've worked on with database sharding managed it in code and settings rather than on the system side. If your database interaction layer is abstract enough and loads settings well, that approach allows you to serialize web servers with the same setup. I may just be a legendarily good programmer, so it's easier to give the problem to me than the hardware guys, but it often made more logistical sense to do the other way. – Dylan Brams Jul 10 '18 at 12:12
  • 1
    @MontyPython ALSO NOTE: you will probably get more 'programming oriented' answers on this site than on Server Fault or another Stack Exchange site. The approach you're looking for definitely seems more configuration than code to me, so I know less about it. – Dylan Brams Jul 10 '18 at 12:14
  • @DylanBrams - I'll try dba.stackexchange.com. This might get more traction over there. – MontyPython Jul 10 '18 at 12:24
  • 1
    Once you start talking proxy servers you're more in the SysAdmin realm, I think. I'd personally repost at ServerFault as well, see if anyone is interested. – Dylan Brams Jul 10 '18 at 12:26
  • Point taken. @DylanBrams – MontyPython Jul 10 '18 at 13:37

0 Answers0