Mysql: Horizontal sharding

[nanom1t]

Hi,

I want to scale out my MySQL database into few servers using horizontal sharding. For example, I have 4 database servers (not replicas):

• Shard 1 (192.168.1.1)
• Shard 2 (192.168.1.2)
• Shard 3 (192.168.1.3)
• Shard 4 (192.168.1.4)

Now I want to connect to one of them depending on user_id (server_id = user_id % 4).

How can I pool mysql connections using Node.js mysql package? I've found only PoolCluster and Pooling connections in documentation. It seems that this package works only with one database or database replicas (master-slave).

Could anyone explain how to work with multiple database servers in Node.js?

Thanks

[sidorares]

Could anyone explain how to work with multiple database servers in Node.js?

There is nothing built in this driver (and probably won't be, it's goal is mainly low level protocol implementation)
You'd have to create 4 separate connections (or connection pools) and query one manually based on your data logic ( in your case, user_id )

[nanom1t]

@sidorares Thank you for the reply. So in general it should look like this for each query?

var mysql = require('mysql');

var connectionPool = {
    server1: mysql.createPool({ host: '192.168.1.1' ... });
    server2: mysql.createPool({ host: '192.168.1.2' ... });
    server3: mysql.createPool({ host: '192.168.1.3' ... });
    server4: mysql.createPool({ host: '192.168.1.4' ... }); 
};

var serverId = 'server' + (userId % 4); 

connectionPool[serverId].getConnection(function(err, connection) {
   // execute query
   connection.release();
});

Thanks

[sidorares]

So in general it should look like this for each query?

yes, this is how I'd do it

for simple queries you can also have .query() pool helper:

var serverId = 'server' + (userId % 4); 

connectionPool[serverId].query(sql, (err, rows) => {
  // no need to release, it's taken care of already
});