Issue getting the read/write split to work with PHP mysqlnd_ms

Question

I'm trying to setup the PHP mysqlnd_ms extension and I'm having some issues. So far here is what I have done:

-compiled PHP from source while enabling mysqlnd

-Installed mysqlnd_ms (If I run pecl info mysqlnd_ms I do get the information about the mysqlnd_ms plugin):

$ sudo pecl install mysqlnd_ms

-modified PHP.ini (and restarted it afterwards):

mysqlnd_ms.enable=1
mysqlnd_ms.disable_rw_split=0 ; for good measure
mysqlnd_ms.config_file=mysqlnd_ms_config.ini

-created mysqlnd_ms_config.ini with following content (which is given as example in the official doc)

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "127.0.0.1",
                "port": "3306"
            }
        },
        "slave": {
            "slave_0": {
                "host": "localhost",
                "socket": "\/var\/run\/mysqld\/mysqld.sock"
            }
        }
    }
}

According to the documentation:

The plugin executes read-only statements on the configured MySQL slaves, and all other queries on the MySQL master. Statements are considered read-only if they either start with SELECT, the SQL hint /ms=slave/, or if a slave had been chosen for running the previous query and the query starts with the SQL hint /ms=last_used/. In all other cases, the query will be sent to the MySQL replication master server

So at this point I'm expecting my SELECT statements to be sent to the slave and other statements (such as UPDATE) to be sent to the master.

I wrote a little script to test the setup:

$socket = '/var/run/mysqld/mysqld.sock';
$dbname = 'mysqlnsmstest';
$user = 'root';
$pass = 'root';

$mysql = new PDO("mysql:unix_socket=$socket;dbname=$dbname", $user, $pass);

$result = $mysql->query('SELECT * FROM mytable');
foreach($result as $row) {
    print_r($row);
}

$count = $mysql->exec("UPDATE mytable SET field='test' WHERE id=2");
echo "Nb rows affected: $count\n";

The queries are properly executed BUT they are all sent to the slave (I know this because if I sniff traffic with $ tcpdump -ni any port 3306 I don't see anything, whereas if I make manual queries on 3306 they do show up in tcpdump).

Using comments such as /*ms=slave*/ and /*ms=master*/ makes no difference. The general_log does show that there isn't any other comments that could confuse mysqlns_ms:

121118 19:14:40    36 Connect   root@localhost on mysqlnsmstest
           36 Query /*ms=slave*/SELECT * FROM mytable
           36 Query /*ms=master*/UPDATE mytable SET field='test' WHERE id=2
           36 Quit  

I tried using a non-loopback IP (such as my 10.0.0.56 local one) to "force" connection over the network but it makes no difference.

$pdo->getAttribute(PDO::ATTR_CLIENT_VERSION) is giving me "mysqlnd 5.0.10 - 20111026 - $Id: b0b3b15c693b7f6aeb3aa66b646fee339f175e39 which shows mysqlnd is being used.

What am I missing in order for the read/write splitting to work (any suggestion welcome)?

Update:
I found a better way than tcpdump to check whether read/write split is working:

print_r(mysqlnd_ms_get_last_used_connection($pdo));
Array
(
    [scheme] => unix:///var/run/mysqld/mysqld.sock
    [host_info] => Localhost via UNIX socket
    [host] => 
    [port] => 3306
    [socket_or_pipe] => /var/run/mysqld/mysqld.sock
    [thread_id] => 48
    [last_message] => Rows matched: 1  Changed: 0  Warnings: 0
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
)

Answer 1

Please, make sure to reference the PECL/mysqlnd_ms documentation section that defines the servers when trying to establish the connection. You should pass the section name - "myapp" in your example - as a host to the the connect function of the PHP MySQL API you want to use. Something like this:

$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');

Or, in your case something like:

$mysql = new PDO("mysql:host=myapp;dbname=$dbname", $user, $pass);

Do not use:

$mysql = new PDO("mysql:unix_socket=$socket;dbname=$dbname", $user, $pass);

This will connect to the very server that is listening on the socket $socket.