CODEWITHSUNDEEP
phpmysqldatabase-designsharding
S1r-Lanzelot
S1r-Lanzelot

Reputation: 2266

Setting up MySql Sharding or circumventing it

I am working with a pre-existing bash file to set up a series of databases for a open source data server (Zotero) and I am running into a mysql structure I am not familiar with:

MASTER="mysql -h localhost -P 3307 -u root -pSomePassword"
SHARD="mysql -h localhost -P 3308 -u root -pSomePassword"
ID1="mysql -h localhost -P 3309 -u root -pSomePassword"
ID2="mysql -h localhost -P 3310 -u root -pSomePassword"
WWW="mysql -h localhost -P 3311 -u root -pSomePassword"
CACHE="mysql -h localhost -P 3312 -u root -pSomePassword"

echo "CREATE DATABASE zoterotest_master" | $MASTER
echo "CREATE DATABASE zoterotest_master" | $SHARD
echo "CREATE DATABASE zoterotest1" | $SHARD
echo "CREATE DATABASE zoterotest2" | $SHARD
echo "CREATE DATABASE zoterotest_ids" | $ID1
echo "CREATE DATABASE zoterotest_ids" | $ID2

It appears there are multiple instances of MySql for some sort of Shard/cluster design but when I look at the db configuration in the php code I see it's connecting to the same port... Now I am confused.

function DBConnectAuth($db) {
    if ($db == 'master') {
        $host = 'localhost';
        $port = 3306;
        $db = 'master';
        $user = 'root';
        $pass = 'SomePassword';
    }
    else if ($db == 'shard') {
        $host = false;
        $port = false;
        $db = false;
        $user = 'root';
        $pass = 'SomePassword';
    }
    else if ($db == 'id1') {
        $host = 'localhost';
        $port = 3306;
        $db = 'ids';
        $user = 'root';
        $pass = 'SomePassword';
    }

    else if ($db == 'id2') {
        $host = 'localhost';
        $port = 3306;
        $db = 'ids';
        $user = 'root';
        $pass = 'SomePassword';
    }

    else if ($db == 'www1') {
        $host = 'localhost';
        $port = 3306;
        $db = 'www';
        $user = 'root';
        $pass = 'SomePassword';
    }

    else if ($db == 'www2') {
        $host = 'localhost';
        $port = 3306;
        $db = 'www';
        $user = 'root';
        $pass = 'SomePassword';
    }

    else if ($db == 'cache') {
        $host = 'localhost';
        $port = 3306;
        $db = 'cache';
        $user = 'root';
        $pass = 'SomePassword';
    }

    else {
        throw new Exception("Invalid db '$db'");
    }
    return array('host'=>$host, 'port'=>$port, 'db'=>$db, 'user'=>$user, 'pass'=>$pass);
}

My question is, does this look like a typical Shard design? And how do I set it up in MySql - do I just spin up multiple instances on the the ports specified in the Bash or do I have to do something special?

I am tempted to just point everything in the Bash file to 3306 and update the DB shard tables to point to 3306 as well but I have a feeling it isn't that easy.

Upvotes: 0

Views: 291

Answers (1)

Rick James
Rick James

Reputation: 142298

Sharding on a single server makes very little sense. Sharding is usually done by having the same databases on similar servers, containing subsets of the data. Sharding provides scaling beyond what you can get from replication, clustering, etc.

Take, for example, a photo-sharing site with millions of users and billions of photos. That won't fit on a single server, but by splitting up the users, together with their photos. Small ancillary tables would either be copied or accessed on some non-sharded server/database.

Perhaps we disagree on the definition of "shard"? In which case, please explain what you mean by it.

Upvotes: 1

Related Questions