Debian8下PHP5.5升7.1后mysqlnd_ms替代方案及配置教程求助

Got it, let's tackle this problem. Since mysqlnd_ms can be tricky to get working reliably with PHP 7.1 on Debian 8, here are a few practical approaches to set up read-write separation for your web server's PHP client:

一、手动实现读写分离（原生PDO/mysqli） #

This is the most straightforward method, no extra extensions required, perfect for simple applications or if you want full control.

Step 1: Define connection parameters #

First, store your master and slave server details in a config file or class:

// Master database (for write operations: INSERT/UPDATE/DELETE)
$masterConfig = [
    'host' => 'master-mysql.yourdomain.com',
    'dbname' => 'your_database',
    'username' => 'db_user',
    'password' => 'db_password',
    'charset' => 'utf8mb4'
];

// Slave databases (for read operations: SELECT; can add multiple entries)
$slaveConfigs = [
    [
        'host' => 'slave1-mysql.yourdomain.com',
        'dbname' => 'your_database',
        'username' => 'db_user',
        'password' => 'db_password',
        'charset' => 'utf8mb4'
    ]
];

Step 2: Build a connection handler #

Create a simple class to manage connections and route operations to the right server:

class DBHandler {
    private $masterConn;
    private $slaveConns = [];

    public function __construct($masterConfig, $slaveConfigs) {
        // Initialize master connection
        $this->masterConn = new PDO(
            "mysql:host={$masterConfig['host']};dbname={$masterConfig['dbname']};charset={$masterConfig['charset']}",
            $masterConfig['username'],
            $masterConfig['password'],
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );

        // Initialize slave connections
        foreach ($slaveConfigs as $config) {
            $this->slaveConns[] = new PDO(
                "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}",
                $config['username'],
                $config['password'],
                [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
            );
        }
    }

    // Get connection for write operations
    public function getWriteConn() {
        return $this->masterConn;
    }

    // Get connection for read operations (randomly pick a slave)
    public function getReadConn() {
        // Fallback to master if no slaves are available
        if (empty($this->slaveConns)) return $this->masterConn;
        
        $randomSlaveIndex = array_rand($this->slaveConns);
        return $this->slaveConns[$randomSlaveIndex];
    }
}

Step 3: Use in your application #

Route your queries to the correct connection based on operation type:

// Initialize the handler
$dbHandler = new DBHandler($masterConfig, $slaveConfigs);

// Example: Write operation (insert data)
$writeConn = $dbHandler->getWriteConn();
$stmt = $writeConn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['Jane Smith', 'jane@yourdomain.com']);

// Example: Read operation (fetch data)
$readConn = $dbHandler->getReadConn();
$stmt = $readConn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$userData = $stmt->fetch(PDO::FETCH_ASSOC);

二、使用自带读写分离支持的ORM框架 #

如果你的项目使用ORM，主流框架基本都内置了主从连接支持，能省去大量重复代码。

Laravel Eloquent #

如果你在用Laravel，修改config/database.php配置主从连接：

'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => 'slave1-mysql.yourdomain.com',
        // 可以添加多个从库实现负载均衡：
        // 'host' => ['slave1.yourdomain.com', 'slave2.yourdomain.com'],
    ],
    'write' => [
        'host' => 'master-mysql.yourdomain.com'
    ],
    'database' => 'your_database',
    'username' => 'db_user',
    'password' => 'db_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Laravel会自动将SELECT路由到从库，写操作路由到主库。如果需要强制从主库读取数据，可以用DB::connection('mysql')->table('users')->useWritePdo()->get()。

Doctrine ORM #

对于Doctrine，在配置文件中添加从库信息（YAML示例）：

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver: pdo_mysql
                host: master-mysql.yourdomain.com
                port: 3306
                dbname: your_database
                user: db_user
                password: db_password
                charset: utf8mb4
                slaves:
                    slave1:
                        host: slave1-mysql.yourdomain.com
                        port: 3306
                        dbname: your_database
                        user: db_user
                        password: db_password
                        charset: utf8mb4

Doctrine会自动处理读写路由，你也可以根据需求手动指定使用主库连接。

三、可选：为PHP 7.1编译安装mysqlnd_ms #

如果你仍想使用mysqlnd_ms，可以在Debian 8上手动编译安装（注意：该扩展已不再积极维护，需谨慎使用）：

1. 安装依赖包：

sudo apt-get update
sudo apt-get install php7.1-dev php7.1-mysqlnd git build-essential

2. 克隆扩展源码：

git clone https://github.com/php/pecl-database-mysqlnd_ms.git
cd pecl-database-mysqlnd_ms

3. 编译并安装：

phpize
./configure --enable-mysqlnd_ms
make && sudo make install

4. 启用扩展：
   创建/etc/php/7.1/mods-available/mysqlnd_ms.ini，内容如下：

extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/etc/php/7.1/mysqlnd_ms_config.json

5. 创建配置文件/etc/php/7.1/mysqlnd_ms_config.json：

{
    "your_app_name": {
        "master": {
            "master_0": {
                "host": "master-mysql.yourdomain.com",
                "db": "your_database",
                "user": "db_user",
                "password": "db_password"
            }
        },
        "slave": {
            "slave_0": {
                "host": "slave1-mysql.yourdomain.com",
                "db": "your_database",
                "user": "db_user",
                "password": "db_password"
            }
        }
    }
}

6. 重启Web服务器或PHP-FPM：

# 针对PHP-FPM
sudo service php7.1-fpm restart

# 针对Apache
sudo service apache2 restart

内容的提问来源于stack exchange，提问作者Marius