How to implement mysql read and write separation in php

php method to implement mysql read-write separation: 1. Install MySQL master-slave replication, set the MySQL database instance to the master-slave replication architecture and enable binary logging; 2. Create PHP code to connect to MySQL and create two Different connection strings are implemented using PHP's "PDO" extension and "mysqlnd_ms" extension; 3. Configure the "mysqlnd_ms" extension in the php.ini file.

Operating system for this tutorial: Windows 10 system, php8.1.3 version, Dell G3 computer.

php method to achieve mysql read-write separation:

1. Install MySQL master-slave replication

First, you need to The MySQL database instance is set up in a master-slave replication architecture. This involves creating a master database on the master server and slave databases on all slave servers. In addition, binary logging needs to be enabled on the main server;

2. Create PHP code to connect to MySQL, create two different connection strings, and use PHP's "PDO" extension and "mysqlnd_ms" extension to achieve this;

The following is an example code for connecting PDO to the MySQL server:

$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'myusername';
$password = 'mypassword';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
try {
    $dbh = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

3. Finally, configure the "mysqlnd_ms" extension.

To make mysqlnd_ms work in PHP, you need to configure it in the php.ini file. The following is a sample configuration:

extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/etc/mysqlnd_ms_plugin.ini

Where /etc/mysqlnd_ms_plugin.ini is a configuration file containing detailed information about the MySQL master-slave instance. Here is a sample configuration file:

{
    "myapp": {
        "master": {
            "host": "localhost",
            "port": "3306",
            "user": "myuser",
            "password": "mypassword",
            "database": "mydatabase"
        },
        "slave": [
            {
                "host": "10.0.0.1",
                "port": "3306",
                "user": "readonly",
                "password": "readonlypw",
                "database": "mydatabase
            }
}

The above is the detailed content of How to implement mysql read and write separation in php. For more information, please follow other related articles on the PHP Chinese website!