MyPDOMS: A PDO extension for simple MySQL master/slave switching
MIT License
MyPDOMS is intended to be a drop-in replacement for most common tasks performed with mysqlnd_ms.
Requires PHP 5.6 or above.
While this is based on PDO, it only supports MySQL.
Before you instantiate an instance of MyPDOMS
, you need to configure it. Configuration is accomplished using the static
setConfig
method, which expects a single parameter of type array. The structure of the expected associative array is:
[config name]
- The name of a configuration. Configurations are collections of servers.
master
- Database configuration for your master server
host
- The host where this database is running (required)port
- The port on which this database is running (optional; defaults to 3306)username
- Database username (optional; defers to constructor argument if missing)password
- Database password (optional; defers to constructor argument if missing)slaves
- Contains your slave server database configurations
[slave name]
- Database configuration for a slave server (can be anything except master
)
host
- The host where this database is running (required)port
- The port on which this database is running (optional; defaults to 3306)username
- Database username (optional; defers to constructor argument if missing)password
- Database password (optional; defers to constructor argument if missing)So, for example, you might want to do this:
<?php
use Corn\MyPDOMS\MyPDOMS;
MyPDOMS::setConfig([
'my_site_1' => [
'master' => [
'host' => '127.0.0.1',
'port' => 3306,
'username' => 'my_site_user',
'password' => 'apples'
],
'slaves' => [
'slave_1' => [
'host' => '10.0.1.1',
'username' => 'slave_user',
'password' => 'readonly'
],
'slave_2' => [
'host' => '10.0.1.2',
'username' => 'slave_user',
'password' => 'readonly'
]
]
],
'my_site_2' => [
'master' => [
'host' => 'localhost',
'username' => 'my_site_2_user',
'password' => 'oranges'
]
]
]);
You don't need to supply any slave configuration. If you don't configure any slaves, then all queries will go to the master.
Slave connections are lazily-established, but a master connection is established when you construct a new MyPDOMS
instance. The constructor is identical to the standard PDO constructor
but with these caveats:
host
in the DSN should be the name of one of your configurations (in the above example, my_site_1
or my_site_2
)port
in the DSN is ignored if supplied$username
and $passwd
) in both the constructor and in your config (setConfig
), the constructor wins
setConfig
, to prevent any possible credential leakage e.g. in stack traces$options
you supply will be used for establishing connections to the master and to all slave connectionsHere's an example:
<?php
use Corn\MyPDOMS\MyPDOMS;
$db = new MyPDOMS('mysql:host=my_site_1;dbname=my_database;charset=utf8mb4', null, null, [MyPDOMS::ATTR_TIMEOUT => 5]);
MyPDOMS
is a subclass of PDO
so PDO's documentation also applies to MyPDOMS
with these core differences:
lastUsedHost
property contains the name of the last host that was sent a query (e.g. master
or slave_1
)beginTransaction
commit
rollBack
inTransaction
lastInsertId
quote
quote
does not result in any network I/O, it's always called on the master connectionlastUsedHost
:
getAvailableDrivers
will always return ['mysql']
setAttribute
will result in this sequence of events:
MyPDOMS
objecttrue
iff all connections returned true
when setAttribute
was called on themgetAttribute
will return the value from the internal cache, not from a PDO
connection objectprepare
, query
, and exec
will route to a connection based on the criteria noted in Query Routing
alwaysUseMaster
to true to always use the master connection, despite wherever the query would ordinarily be routedQueries will be routed to either the master or to a slave depending on this sequence of checks:
HINT_MASTER
is found then the query will be sent to the masterHINT_SLAVE
is found then the query will be sent to a slaveHINT_LAST_USED
is found then the query will be sent to the last used connectionINSERT
, UPDATE
, DELETE
, REPLACE
, LOAD
, ALTER
, CREATE
, DROP
, RENAME
, or TRUNCATE
then the query will be sent to the masterSELECT
and the query ends in FOR UPDATE
then the query will be sent to the masterNote: The routing logic does not check to see if a transaction is open, because all queries that can result in updates or locks are already automatically routed to the master.
SQL hints can be used to override the default query routing logic. These SQL hints are available, and should be prepended to queries in a comment:
MyPDOMS::HINT_MASTER
- Send this query to the masterMyPDOMS::HINT_SLAVE
- Send this query to a slaveMyPDOMS::HINT_LAST_USED
- Send this query to the server last used
Example:
<?php
use Corn\MyPDOMS\MyPDOMS;
$db = new MyPDOMS($dsn);
$db->query("/*" . MyPDOMS::HINT_MASTER . "*/SELECT 1"); // will be sent to the master even though it's a SELECT
If you want to replace mysqlnd_ms and not go back and update all your code, you can use this snippet:
<?php
use Corn\MyPDOMS\MyPDOMS;
if (!defined('MYSQLND_MS_MASTER_SWITCH')) {
define('MYSQLND_MS_MASTER_SWITCH', MyPDOMS::HINT_MASTER);
define('MYSQLND_MS_SLAVE_SWITCH', MyPDOMS::HINT_SLAVE);
define('MYSQLND_MS_LAST_USED_SWITCH', MyPDOMS::HINT_LAST_USED);
}
When a query is due to be routed to a slave, a slave is selected per-query. That is, slaves are not selected per-request but are selected every time a query is executed. Presently, the only supported selection mechanism is unweighted random, in which every query will be sent to a random slave, with all slaves having an equal probability of being chosen.
Example:
<?php
use Corn\MyPDOMS\MyPDOMS;
// Assume configured slaves are slave_{1-5}
$db = new MyPDOMS($dsn);
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_4
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_3
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_4
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_2
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_2
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_2
Different selection algorithms are expected to be added in later releases, but if you wish to define your own
selection algorithm, you may extend MyPDOMS
and override the
getSlave
method.
Both emulated and non-emulated prepared statements are fully supported, as they are assigned a connection at prepare-time.
That is, after a PDOStatement
is returned from prepare()
, the statement will always use the same database each time
it is executed.
Note: lastUsedHost
is updated when a statement is prepared, but not when it is executed. This means that the
following is possible:
<?php
use Corn\MyPDOMS\MyPDOMS;
$db = new MyPDOMS($dsn);
$stmt = $db->prepare("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_1
$db->query("SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_2
$stmt->execute(); // this is executed on slave_1 since it was prepared on slave_1
echo $db->lastUsedHost . "\n"; // slave_2
$db->query("/*" . MyPDOMS::HINT_LAST_USED . "*/ SELECT 1");
echo $db->lastUsedHost . "\n"; // slave_2