corn / mypdoms
Pure-PHP clone-implementation of mysqlnd_ms built on top of PDO
Installs: 7 295
Dependents: 0
Suggesters: 0
Security: 0
Stars: 2
Watchers: 3
Forks: 3
Open Issues: 0
Requires
- ext-pdo: *
README
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.
Table of Contents
- Configuring
- Establishing a Connection
- Differences from PDO
- Query Routing
- SQL Hints
- Slave Selection
- Prepared Statements
Configuring
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 serverhost
- 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 exceptmaster
)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.
Establishing a Connection
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:
- The
host
in the DSN should be the name of one of your configurations (in the above example,my_site_1
ormy_site_2
) - The
port
in the DSN is ignored if supplied - If you supply database credentials (
$username
and$passwd
) in both the constructor and in your config (setConfig
), the constructor wins- It's recommended you supply your credentials in
setConfig
, to prevent any possible credential leakage e.g. in stack traces
- It's recommended you supply your credentials in
- Any connection
$options
you supply will be used for establishing connections to the master and to all slave connections
Here'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]);
Differences from PDO
MyPDOMS
is a subclass of PDO
so PDO's documentation also applies to MyPDOMS
with these core differences:
- The differences noted above in the Establishing a Connection section
- The
lastUsedHost
property contains the name of the last host that was sent a query (e.g.master
orslave_1
) - These methods will always be sent to the master connection:
beginTransaction
commit
rollBack
inTransaction
lastInsertId
quote
- Although
quote
does not result in any network I/O, it's always called on the master connection
- Although
- These methods will be sent to the connection named by
lastUsedHost
: getAvailableDrivers
will always return['mysql']
- Calling
setAttribute
will result in this sequence of events:- The attribute and value you passed in will be stored internally in the
MyPDOMS
object - The attribute will be set on the master connection
- The attribute will be set on any established slave connections
- When a new slave connection is established, all previously-set attributes will be immediately set on it
- Returns
true
iff all connections returnedtrue
whensetAttribute
was called on them
- The attribute and value you passed in will be stored internally in the
- Calling
getAttribute
will return the value from the internal cache, not from aPDO
connection object prepare
,query
, andexec
will route to a connection based on the criteria noted in Query Routing- You can set the property
alwaysUseMaster
to true to always use the master connection, despite wherever the query would ordinarily be routed
Query Routing
Queries will be routed to either the master or to a slave depending on this sequence of checks:
- Leading comments in the SQL will be checked for SQL Hints
- If
HINT_MASTER
is found then the query will be sent to the master - If
HINT_SLAVE
is found then the query will be sent to a slave - If
HINT_LAST_USED
is found then the query will be sent to the last used connection
- If
- If the first SQL-word is one of
INSERT
,UPDATE
,DELETE
,REPLACE
,LOAD
,ALTER
,CREATE
,DROP
,RENAME
, orTRUNCATE
then the query will be sent to the master - If the first SQL-word is
SELECT
and the query ends inFOR UPDATE
then the query will be sent to the master - If none of the above match, then the query will be sent to a slave
Note: 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
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- This may be the master, if the master was last used
- If the last used server was a slave, then the query will be sent to that slave
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); }
Slave Selection
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.
Prepared Statements
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