1 Easy Read/Write Splitting with PHP’s MySQLnd

以下均是使用翻译软件翻译的！

　　Note: This is part one in our Extending MySQL with PHP's MySQLnd Series, read part 2 on Advanced Read/Write Splitting, and part 3 for an Introduction to MySQL's innodb memcached interface.

　　注意:这是一个在我们的MySQL和PHP扩展的MySQLnd系列,读第2部分先进的读/写分离,和第3部分介绍MySQL的innodb memcached接口。

 

　　MySQL has always been the default go-to database server for pairing with PHP—it has been the go-to database since almost the inception of the language. Sure, some people use PostgreSQL, or SQL Server, or Oracle, but for the web workload, MySQL is usually the relational database of choice.

　　MySQL一直默认首选数据库服务器与PHP-it配对以来一直成为数据库几乎初始的语言。当然,有些人使用PostgreSQL,或SQL服务器,或甲骨文,但对于网络负载,MySQL通常是关系数据库的选择。

 

　　This was due mostly in part because it was so easy to get going. Libmysqlclient was bundled with PHP itself until it was re-licensed under the GPL. This change meant it was no longer possible to bundle with PHP and it was removed.

　　主要原因是它是那么容易实现。Libmysqlclient捆绑了PHP本身直到GPL重新许可。这种变化意味着再也不可能与PHP捆绑,它被删除了。

 

　　This made the compilation process for PHP slightly more difficult, requiring that libmysqlclient be available on the host system.

　　这使得PHP的编译过程稍微困难,如果要求libmysqlclient在主机系统可用的话。

 

　　Given the widespread nature of PHP, and the fact it was the most popular single language to use MySQL, this wasn’t ideal for Oracle (then Sun) and so they came to an agreement: they’d build the MySQL Native Driver—a PHP licensed contribution to PHP that would allow access to MySQL without libmysqlclient.

　　鉴于PHP的普遍性和事实上这是最受欢迎的使用MySQL的单一语言,这并不是甲骨文(当时太阳)所理想的,所以他们来到了一个协议:他们会建立原生的MySQL驱动——符合PHP许可的但不需要libmysqlclient 而允许访问MySQL。

 

　　MySQL Native Drive (mysqlnd) was added in PHP 5.3 and has been the default since PHP 5.4 (though you can still compile against libmysqlclient). It brings extra features, better performance, and better memory usage than libmysqlclient.

　　MySQL(mysqlnd)是被添加到PHP 5.3和PHP 5.4以来一直默认(尽管你仍然可以对libmysqlclient编译)。它带来了额外的功能,更好的性能,比libmysqlclient更好的内存使用情况。

 

From the MySQL manual:

The mysqlnd library is using PHP internal C infrastructure for seamless integration into PHP. In addition, it is using PHP memory management, PHP Streams (I/O abstraction) and PHP string handling routines. The use of PHP memory management by mysqlnd allows, for example, memory savings by using read-only variables (copy on write) and makes mysqlnd apply to PHP memory limits.

mysqlnd库是使用PHP内部C基础设施无缝集成到PHP。此外,它是使用PHP内存管理、PHP流抽象(I / O)和PHP字符串处理例程。mysqlnd允许使用PHP内存管理,例如,储蓄的内存使用只读变量(复制写),使mysqlnd适用于PHP内存限制。

 Additionally it has a plugin architecture, and a number of plugins are available.

另外它有一个插件架构,和许多插件一样可用。

 

Installation

To install, just compile one of the three MySQL extensions. In each instance, do not explicitly specify the path to the libmysqlclient library.

 

The three libraries are:

 

• ext/pdo_mysql (since PHP 5.1)
• ext/mysqli (since PHP 5.0)
• ext/mysql (since PHP 2.0, deprecated since PHP 5.6)

 

Note: If you install ext/mysql or ext/mysqli, ext/pdo_mysql is enabled automatically.

 

You can select an extension by choosingone or more of the following configure flags:

 

• --with-mysql
• --with-mysqli
• --with-pdo-mysql

 

If you are using Debian, or Ubuntu, you can easily install the php5-mysqlnd package:

 

$ sudo apt-get install php5-mysqlnd

 

This will remove the libmysqlclient-based php5-mysql package, and includes all three extensions.

MySQL Native Driver Plugins

　　Aside from the performance benefits, the biggest benefit to mysqlnd are it’s plugins. These plugins are available via PECL, and can be installed easily using:

　　除了性能好处,最大的好处是mysqlnd插件。这些插件可以通过PECL,可以轻松地安装使用:　　

$ pecl install mysqlnd_<name>

The available (stable) plugins are:

• mysqlnd_memcache: Transparently translate SQL to use the MySQL 5.6 memcache-protocol compatible NoSQL daemon
  • 透明地将SQL使用MySQL 5.6 memcache-protocol 兼容NoSQL守护程序
• mysqlnd_ms: Easily perform read/write splitting between master and slave (ms) servers, with simple load balancing
  • master 和slave 之间轻松地执行读/写分离(ms)服务器,使用简单的负载平衡
• mysqlnd_qc: Adds a simple query cache to PHP
• mysqlnd_uh: Allows writing mysqlnd plugins in PHP
  • 允许用PHP编写mysqlnd插件

Because these plugins are for mysqlnd itself, they apply to all three extensions.

Read/Write Splitting

　　The most useful plugin is mysqlnd_ms, or master/slave. This plugin allows you to transparently—albeit somewhat naively—split reads and writes between different servers.

　　最有用的插件mysqlnd_ms或master/slave。这个插件允许您  显然—尽管 在有些 天然—分离 不同服务器之间的读和写。

CONFIGURATION

Once you have installed using pecl, you need to configure both the php.ini, and the mysqlnd_ms configuration file.

In php.ini (or mysqlnd_ms.ini on Debian-like systems):

extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms.json

Then you need to create the mysqlnd_ms.json file. This file defines the master and slave servers, as well as the read/write splitting and load balancing strategies.

然后您需要创建mysqlnd_ms.json文件。这个文件定义了主、从服务器,以及读/写分离和负载平衡策略。

{
    "appname": {
        "master": {
            "master_0": {
                "host": "master.mysql.host",
                "port": "3306",
                "user": "dbuser",
                "password": "dbpassword",
                "db": "dbname"
            }
        },
        "slave": {
            "slave_0": {
                "host": "slave.mysql.host",
                "port": "3306"
                "user": "dbuser",
                "password": "dbpassword",
                "db": "dbname"
            },
        }
    }
}

The only required setting is the host. All others are optional.

唯一需要设置主机。其他都是可选的。

LOAD BALANCING

Additionally, mysqlnd_ms can do simple load balancing in one of several strategies:

另外,mysqlnd_ms可以做为简单的负载均衡策略之一:

• random—a random slave is picked for each read query
• random once—a random slave is picked for the first read query and re-used for the remainder of the request
  • 随机slave被选择给第一个读查询并被重用给其余请求
• round robin—a new slave is picked for *each *read query, in the order they are defined
  • 循环--新的slave被选择给每个读查询，在他们被定义的顺序里
• user—a user-specified callback determines which slave will be called for each query
  • 用户指定的回调决定哪个slave被每个查询调用

It is important to understand that unless you use the last strategy and maintain state yourself, every single request will execute the load balancing strategy in isolation. So, round-robin applies to each query within the same request, and it isn’t that one server is picked per request in sequential order as you might expect of an actual hardware or software load balancer.

重要的是要理解,除非你使用过去的策略和维护自己的状态,每一个独立请求将被隔离执行负载均衡策略。所以,循环适用于每个查询在相同的请求里,而且那不是一个服务器被按顺序执行每个请求的你可能会认为像实际的硬件或软件负载平衡器。

With that in mind, I would recommend that youdo not use the load balancing aspect of this plugin, and instead use an actual load balancer for your slaves—such as haproxy—and simply point the configuration to the load balancer as the only slave.

记住这一点,我建议不使用这个插件的负载均衡策略,而是为从服务器使用一个实际负载均衡器--作为高可用代理--简单点配置负载平衡器作为唯一的奴隶。

ROUTING QUERIES -- 路由查询

By default mysqlnd_ms will transparently route all queries starting with SELECT to the slave servers, and anything else to the master.

默认情况下mysqlnd_ms将透明地路由所有查询 从把 ‘SELECT’路由到从服务器开始,和其他查询路由到的主服务器。

 

This is both good and bad. Being transparent, it means zero changes to your code. But it is also simplistic, and does not analyze the query to ensure it is actually a read-only query.

这样有好有坏。是透明的,这意味着零修改您的代码。但它也简单,不分析查询,以确保它确实是一个只读查询。

 

Not only will it not send a query that starts with (SELECT to the master, it will also send a write query using SELECT … INTO to the slave, which could be a disaster.

并不是所有以SELECT开始的查询都要到从服务器，比如SELECT ...INTO 对于从服务器来说 是一场灾难！

 

Luckily, the plugin includes the ability to hint which server the query should be sent to. This is done by placing one of three SQL hint constants in the query:

幸运的是,这个插件包括暗示的能力用来指定应该发送到哪个服务器查询。这是通过将SQL查询中包含三种提示常量:

• MYSQLND_MS_MASTER_SWITCH—Run the statement on the master
• MYSQLND_MS_SLAVE_SWITCH—Run the statement on the slave
• MYSQLND_MS_LAST_USED_SWITCH—Run the statement on whichever server was last used
  • 在上一个使用的服务器上执行语句

These three constants are simple placeholders for strings, ms=master, ms=slave, and ms=last_usedrespectively. However these strings may change in the future and therefore the constants should be used.

这三个简单的占位符字符串常量,ms=master, ms=slave, and ms=last_usedrespectively。然而这些字符串在未来可能会改变,因此应该使用常量。

 

To use a SQL hint, add a comment before the query with whichever one you wish to use. The easiest way to do this is to use sprintf() which will replace placeholders (in this case %s,the string placeholder) for the given arguments.

使用SQL提示,添加一个评论之前,与任何一个您希望使用的查询。最简单的方法是使用sprintf()将替换占位符(在本例中% s,字符串占位符)为给定的参数。

 

For example, to send a SELECT to the master:

$sql = sprintf("/*%s*/ SELECT * FROM table_name;", MYSQLND_MS_MASTER_SWITCH);

Or, to send a non-SELECT to a slave:

$sql = sprintf("/*%s*/ CREATE TEMPORARY TABLE `temp_table_name` SELECT * FROM table_name;", MYSQLND_MS_SLAVE_SWITCH);

 

The last hint will let you ensure that the same connection is used as for the previous query. This is particularly useful for ensuring that you switch to reading from the master after data has been modified but potentially not yet replicated, or when performing transactions that include both read and write statements.

最后提示会让你确保使用之前的查询相同的连接。这是特别有用的,以确保您切换到主服务器读取，比如数据修改后但可能没有复制,或当执行事务,包括读和写语句。

if ($request->isPost() && $form->isValid()) {
    $user>setValues($form->getValues());
    $user->save();
}

$sql = sprintf("/*%s*/ SELECT * FROM user_session WHERE user_id = :user_id", MYSQLND_LAST_USED_SWITCH);

This will use the master for the query if—and only if—the master was previously used. In this case the master is used if the user data is updated (with $user->save()).

Conclusion -- 结论

The mysqlnd_ms plugin is incredibly useful, especially when you want to move large legacy applications to using distributed read/write. While it’s not perfect, it should get you 80-90% of the way there for most applications without changing a single line of code.

mysqlnd_ms插件非常有用,尤其是当您想要移动大型遗留应用程序使用分布式读/写。虽然不是完美的,它应该让你80 - 90%的方式没有改变,对于大多数应用程序一行代码。

In the second installment in this series, we’ll look at more advanced usage of the mysqlnd_ms plugin.

在本系列的第二部分中,我们将看看mysqlnd_ms插件的更高级的用法。

Note: This is part one in our Extending MySQL with PHP's MySQLnd Series, read part 2 on Advanced Read/Write Splitting, and part 3 for an Introduction to MySQL's innodb memcached interface.

 

原文地址：

https://blog.engineyard.com/2014/easy-read-write-splitting-php-mysqlnd