Distribute records on different MySQL databases - MySQL Proxy alternative

Question

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!

score 0 · Answer 1 · answered Jun 20 '14 at 23:20

This site seems to offer the service you're looking for (for a price).

http://www.sqlparser.com/

It lets you parse and modify queries and results. However what you're looking to do seems like it will only require a couple lines of code to distinguish between different user id's, so even though mysql-proxy is still in alpha your needs are simple enough that I would just use the proxy.

Alternatively, you could user whatever server-side language you're using to grab their user.id info, and then create a mysql connection to the appropriate database based on that info. Here's some php I scrabbled together which in spirit does what I think you're looking to do.

</php
    // grab user.id from wherever you store it
    $userID = get_user_id($clientUserName);
    $userpass = get_user_pass($clientUserName);

    if ($userID % 4 == 0) { // every 4th user
        $db = new mysqli('localhost', $clientUserName, $userPass, 'db4');
    }

    else if ($userID % 3 == 0) { // every 3th user
        $db = new mysqli('localhost', $clientUserName, $userPass, 'db3');
    }

    else if ($userID % 2 == 0) { // every 2nd user
        $db = new mysqli('localhost', $clientUserName, $userPass, 'db2');
    }

    else  // every other user
        $db = new mysqli('localhost', $clientUserName, $userPass, 'db1');
    }

    $db->query('SELECT * FROM ...;');

?>

Distribute records on different MySQL databases - MySQL Proxy alternative

1 Answers1