请确保您使用的是mysqlnd
https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlnd-uh.html
<?php /* * Example: Load Balancing of queries combined with sharding * * Scenario: * >You have many database servers. The server can be mirrored and hold the * the same data or you have partitioned your data in any way. For example, * you could keep passwords in a very much secured server and all other * data in replication slaves. Or, you have data that is replicated * to move it as close to the client as possible. Whatever it is, you * want to make sure that a certain server is connected, if the application * fetches the data. * * Of course, you do not want to touch the source code of your application. * Maybe you even can't do that because it is closed source. Whatever it is, * you prefer a solution a bit outside the application layer. Setting auto_prepend * to proxy functions from the database library is OK for you, though. * The one and only database library you trust is, what else, mysqlnd. * See also http://www.slideshare.net/nixnutz/the-power-of-mysqlnd-plugins et al. * * Solution: * You use mysqlnd_uh to proxy queries. If you detect a query that needs to be * rewritten and send via a proxy connection, you create a proxy connection to * send the query on it. Whenever the PHP scripts works on the connection in the * following you check if there is an active proxy connection for the connection. * If so, you make the proxy connection reply to the script. * Note: for production code you must hook more calls than shown in this example. * See PECL/mysqlnd_ms, PECL/mysqlnd_mc for more ideas and details. * * In general this code works with all PHP MySQL extensions: mysql, mysqli, PDO_MySQL. * But at the time of writing mysqlnd_uh does not offer functionality to convert * mysql and PDO_MYSQL connection objects into mysqlnd connection handles to be * used with the mysqlnd proxy. Thus, it makes only sense to use it with mysqli. * * NOTE: You must set connection properties manually at the end of the script. * Search for TODO! */ die("Please set connection properties before running! Assuming you have two databases test and phptest. Search for TODO!\n"); class load_balancer_query_sharding extends MySQLndUhConnectionData { protected $verbose; /* A list of proxy connection */ protected $connections; public function __construct($verbose) { $this->verbose = $verbose; $this->connections = array(); } /** * Parse SQL query and extract hints * */ public function parse_sql_hints($query) { $hints = array(); /* A fancy SQL hint parser - you can do better than I... */ if (preg_match("@^/\*(.*)\*/@ismU", $query, $matches)) { $parts = explode(",", $matches[1]); foreach ($parts as $part) { $tmp = explode("=", $part); if (isset($tmp[0])) $hints[trim($tmp[0])] = trim($tmp[1]); } } return $hints; } /** * Mysqlnd library callback: query() * * This function is called by the mysqlnd library (via the mysqlnd_uh extension) * whenever any of the PHP MySQL extensions (mysql, mysqli, PDO_MYSQL) runs a query * that is not a prepared statement. Don't be surprised to see PDO_MYSQL calling it. * PDO_MYSQL does not always use native prepared statement. PDO_MYSQL does lots of * client-side emulation - by default! * The function call is transparent from the point of view of a PHP MySQL application. * We are hooking the database library. */ public function query($res, $query) { $thread_id = $this->getThreadId($res); if ($this->verbose) { printf("\tquery() proxy called with query '%s'\n", $query); printf("\tquery has been send via connection %d\n", $thread_id); } $hints = $this->parse_sql_hints($query); if (isset($hints['db'])) { /* The application has hinted us that this is a query that shall be send to a shard. The actual hint used in this example is silly. In real life you would pass other information from the application to your sharding/load balancing layer. I am using "db" hint because it helps to show that the idea presented here really works. */ if ($this->verbose) printf("\tquery() proxy has detected a SQL hint that triggers load balancing/rewriting/sharding\n"); /* TODO: change before running to match your setup */ $mysqli = new mysqli('localhost', 'root', 'test', $hints['db'], 3306, '/tmp/mysql.sock'); if ($mysqli->connect_errno) { /* Your code would have some proper error handling, of course... */ die(sprintf("FIXME - PANIC, can't create proxy connection: [%d] %s\n", $mysqli->connect_errno, $mysqli->connect_error)); } $mysqlnd_conn = mysqlnd_uh_convert_to_mysqlnd($mysqli); $proxy = new MySQLndUHConnectionData(); /* A C based mysqlnd plugin would use connection plugin data to store this, see PECL/mysqlnd_ms. */ $this->connections[$thread_id] = array( 'mysqli' => $mysqli, /* need to copy here to increase refcount and prevent close */ 'mysqlnd_conn' => $mysqlnd_conn, 'proxy' => $proxy, 'active' => true, ); if ($this->verbose) printf("\tquery() proxy runs query on new proxy connection %d\n", $mysqli->thread_id); $ret = $this->connections[$thread_id]['proxy']->query( $this->connections[$thread_id]['mysqlnd_conn'], $query); } else { /* no rewriting, run query on existing connectioj */ if ($this->verbose) printf("\tquery() proxy has not rewritten the query\n"); $ret = parent::query($res, $query); } return $ret; } public function getFieldCount($res) { $thread_id = $this->getThreadId($res); /* Is there a proxy connection with an active query */ if ( isset($this->connections[$thread_id]) && $this->connections[$thread_id]['active']) { if ($this->verbose) printf("\tgetFieldCount() run on proxy connection\n"); return $this->connections[$thread_id]['proxy']->getFieldCount($this->connections[$thread_id]['mysqlnd_conn']); } if ($this->verbose) printf("\tgetFieldCount() run on original connection\n"); return parent::getFieldCount($res); } public function storeResult($res) { $thread_id = $this->getThreadId($res); /* Is there a proxy connection with an active query */ if ( isset($this->connections[$thread_id]) && $this->connections[$thread_id]['active']) { if ($this->verbose) printf("\tstoreResult() run on proxy connection\n"); $this->connections[$thread_id]['active'] = false; return $this->connections[$thread_id]['proxy']->storeResult($this->connections[$thread_id]['mysqlnd_conn']); } if ($this->verbose) printf("\tstoreResult run on original connection\n"); return parent::storeResult($res); } } /* Create proxy object and pass list of participating servers to it */ $debug_info = true; /* TODO: change connection parameter to match your setup! */ $proxy = new load_balancer_query_sharding($debug_info); /* Install mysqlnd proxy */ mysqlnd_uh_set_connection_data_proxy($proxy); /* Establish some connections */ printf("Starting load balancer demo\n"); if (!extension_loaded("mysqli")) { printf("You don't have compiled your PHP with mysqli support, can't demo mysqli...\n"); } else { printf("Your PHP supports mysqli, running demo..\n"); /* TODO: change this to match your setup! */ $mysqli = new mysqli('localhost', 'root', 'root', 'test', 3306, '/tmp/mysql.sock'); if (!$mysqli) { printf("Can't connect, check arguments, [%d] %s\n", $mysqli->connect_errno, $mysqli->connect_error); } else { $res = $mysqli->query("SELECT DATABASE() AS _greeting FROM DUAL"); $row = $res->fetch_assoc(); printf("Connected via mysqli, MySQL schema is '%s'\n", $row['_greeting']); printf("\n"); printf("Setting SQL-hint to trigger use of proxy connection\n"); $res = $mysqli->query("/* db=phptest */SELECT DATABASE() AS _greeting FROM DUAL"); $row = $res->fetch_assoc(); printf("Connected via mysqli, MySQL schema is '%s'\n", $row['_greeting']); } } printf("\n");
PHP：MySQL proxy 新思路