My set-up is as follows:
1x web-server (production)
1x database server (production)
mirror of the same
1x web-server (backup)
1x database server (backup) -- slave of the master via replication.
Now, let's not let this perfectly good 'backup' database server go to waste!
1. you need the remi repository
2. yum remove php-mysql
3. yum install pecl-mysqlnd_ms.x86_64 --enablerepo=remi
4. edit the /etc/php.d/mysqlnd_ms.ini:
; Enable mysqlnd_qc extension module
extension=mysqlnd_ms.so
mysqlnd_ms.enable = 1
mysqlnd_ms.force_config_usage = 1
mysqlnd_ms.ini_file = "/etc/php.d/mysqlnd_ms.json"
mysqlnd_ms.collect_statistics = 1
;mysqlnd_ms.multi_master = 0
mysqlnd_ms.disable_rw_split = 0
error_log=/tmp/php_errors.log
mysqlnd_ms.lazyConnection = 0
5. edit or create /etc/php.d/mysqlnd_ms.json
{
"project_name": {
"master": {
"master_0": {
"host": "master_server_IP",
"port": "3306",
"db": "your_project",
"user": "write",
"password": "write_password"
}
},
"slave": {
"slave_0": {
"host": "someIP",
"port": "3306",
"db": "your_project",
"user": "read_user",
"password": "read_password"
},
"slave_1": {
"host": "someIP",
"port": "3306",
"db": "your_project",
"user": "read_user",
"password": "read_password"
}
},
"filters" : [ "roundrobin"]
}
}
6. Note, I'm enforcing config usage, which basically means that the hostname in my application must match the group listed in my json configuration file. The nice thing about this is that now my username & password are not stored in code. mysqlnd_ms will sort that out. Just set it to match up with your /etc/php.d/mysqlnd_ms.json's project_name. (in this case literally 'project_name').
// application.ini
; Database configuration
resources.doctrine.dbal.connections.default.parameters.driver = "pdo_mysql"
resources.doctrine.dbal.connections.default.parameters.host = "project_name"
Now, getting Doctrine to work ... *this was not intuitive*:
"Warning: PDOStatement::execute(): (mysqlnd_ms) string escaping doesn't work without established connection in /data01/www/your_project/library/Doctrine/DBAL/Connection.php on line 613"
There are two workarounds to this. I'm not really happy with either, but here they are:
Option 1: disable lazy_connections. the downside to this is that a connection is opened to EVERY SINGLE database server listed.
Option 2: perform a single select 1 query into the database during bootstrapping.
// INSIDE application/bootstrap.php -- note I'm using ZF-boilerplate.
public function _initDoctrineConfiguration()
{
$this->bootstrap('Doctrine');
$connection = \Zend_Registry::getInstance()->get('em')->getConnection()->getWrappedConnection();
$connection->query("select 1");
}
Option 2 is definitely my preferred route because only 1 connection must be opened, and it will probably go to a slave server anyways.
