Search code examples
phpmysqlimysqlnd

How to make Mysqlnd_ms run each query on a different instance


I have an AWS RDS with 1 read replica (total 2 instances). I'm planning to install mysqlnd_ms plugin so that I can have my request pointed to the database servers in round robin fashion.

I have 2 basic questions which I couldn't find direct answers in documentation

  1. Using mysqlnd_ms, is there a way to say, not all select queries should point to slave(read replica). Each request should be alternatively pointed to the existing instances
  2. Suppose if we run 2 queries per request, then does each of the query points to 1 instance? This means it will be opening 2 database connections for each request? Am I understanding this correctly

Solution

  • Create a json file and mention the path in

    /etc/php.d/mysqlnd_ms.ini

    file as below

    mysqlnd_ms.config_file=/etc/mysqlnd_ms_cfg.json
    

    Add the following code in json file

    /etc/mysqlnd_ms_cfg.json

        {
    
            "myapp": {
                "master": {
                    "master_0": {
                        "host": "RDS master instance IP/doamin name",
                        "port": 3306
                    }
                },
                "slave": {
                    "slave_0": {
                        "host": "RDS master instance IP/doamin name",
                        "port": 3306
                    },
                    "slave_1": {
                        "host": "RDS read replica instance IP/doamin name",
                        "port": 3306
                    }
                },
                "filters": [
                    "roundrobin"
                ]
            }
        }