2

My scenario is the following:

Right now I am using one big MySQL database with multiple tables to store user data. Many tables contain auto increment columns.

I would like to split this into 2 or more databases. The distribution should be done by user_id and is determined (cannot be randomized). E.g. user 1 and 2 should be on database1, user 3 on database2, user 4 on database3.
Since I don't want to change my whole frontend, I would like to still use one db adapter and kind of add a layer between the query generation (frontend) and the query execution (on the right database). This layer should distribute the queries to the right database based on the user_id.

I have found MySQL Proxy which sounds exactly like what I need. Unfortunately, it's in alpha and not recommended to be used in a production environment.
For php there is MySQL Native Driver Plugin API which sounds promising but then I need a layer that supports at least php and java.

Is there any other way I can achieve my objectives? Thanks!

0

You can take a look into MySQL Fabric (Official Doc) but it requires more db server

I have tried this tool only in R&D env for testing a basic HA

It supports some sharding scenarios

Here some high level pros and cons

Pros:

  • setup
  • sharding
  • write/read
  • basic HA

Cons:

  • fabric node is the SPOF
  • no multi master
  • rewrite software in order to use the fabric driver

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.