Read write separation:

Before we get to the point, let's get to know the separation of reading and writing

What is read write separation?

Read write separation is to let the main database process transactional queries and the slave database process select queries. Database replication is used to synchronize the data changes caused by transactional queries to the slave database. Of course, the master database can also select queries.

The biggest function of read-write separation is to relieve the pressure on the server.

What are the benefits of separation of reading and writing?

Increase redundancy;
Ease the pressure on the server; Increase the processing capacity of the machine;
For applications with many read applications, read-write separation is the best, which can ensure that the write server has less pressure, and the read server can accept some time delay.

Note: before realizing the separation of reading and writing, we should first set up the master-slave replication of mysql. For the tutorial of configuring master-slave replication, you can see my other blogs at: mysql8.0 configuring master-slave separation

After understanding read-write separation, let's start to use TP6 to realize read-write separation.

The data access layer supports distributed databases, including read-write separation. To enable distributed databases, you need to turn on the deploy parameter in the database configuration file. Locate the database.php file in the config folder

return [
    'default'    =>    'mysql',
    'connections'    =>    [
        'mysql'    =>    [
            // Enable distributed database deployment mode: 0 centralized (single server), 1 distributed (master-slave server)
            'deploy'    =>  1,
            // Database type
            'type'        => 'mysql',
            // server address
            'hostname'    => '192.168.1.1,192.168.1.2',
            // Database name
            'database'    => 'demo',
            // Database user name
            'username'    => 'root',
            // Database password
            'password'    => '',
            // Database connection port
            'hostport'    => '',
            // Database connection parameters
            'params'          => [],
            // The database code is utf8 by default
            'charset'         =>  'utf8',
            // Database table prefix
            'prefix'          => '',
            // Whether the database read-write separation is effective
            'rw_separate'     => true,
            // Number of primary servers after read / write separation
            'master_num'      => 1,
            // Specifies the serial number from the server
            'slave_no'        => '',
            // Strictly check whether the field exists
            'fields_strict'   => true,
            // Is disconnection reconnection required
            'break_reconnect' => false,
            // Listen for SQL
            'trigger_sql'     => true,
            // Enable field cache
            'fields_cache'    => false,
        ],
    ],
];

After enabling the distributed database, the hostname parameter is the key. The number of hostnames determines the number of distributed databases. By default, the first address is the primary server.

The master-slave server supports setting different connection parameters, including:

Connection parameters
username
password
hostport
database
dsn
charset

If the above parameters of the master and slave servers are consistent, only one needs to be set. Different parameters can be set separately, for example:

return [
    'default'    =>    'mysql',
    'connections'    =>    [
        'mysql'    =>    [
            // Enable distributed database
            'deploy'   => 1,
            // Database type
            'type'     => 'mysql',
            // server address
            'hostname' => '192.168.1.1,192.168.1.2,192.168.1.3',
            // Database name
            'database' => 'demo',
            // Database user name
            'username' => 'root,slave,slave',
            // Database password
            'password' => '123456',
            // Database connection port
            'hostport' => '',
            // Database character set
            'charset'  => 'utf8',
        ],
    ],
];

Remember, it's either the same or each one has to be set.

Distributed database parameters support the use of array definitions (usually to avoid incorrect parsing of multiple accounts and passwords), for example:

return [
    'default'    =>    'mysql',
    'connections'    =>    [
        'mysql'    =>    [
            // Enable distributed database
            'deploy'   => 1,
            // Database type
            'type'     => 'mysql',
            // server address
            'hostname' =>[ '192.168.1.1','192.168.1.2','192.168.1.3'],
            // Database name
            'database' => 'demo',
            // Database user name
            'username' => 'root,slave,slave',
            // Database password
            'password' => ['123456','abc,def','hello']
            // Database connection port
            'hostport' => '',
            // Database character set
            'charset'  => 'utf8',
        ],
    ],
];

You can also set whether the read and write of the distributed database are separated. By default, the read and write are not separated, that is, each server can read and write. For the master-slave database, you need to set the read-write separation. You can use the following settings:

'rw_separate' => true

Now that we have basically understood the basic configuration of TP6 read-write separation, let's test it!

This is the database connection information I configured

Let me run the following code to test it

The above code performs a query operation and an add operation respectively.
Open the debugging tool provided with tp6 to view the sql

You will find that the read operation of the first sentence is performed on the server 192.168.199.131, while the write operation of the following sentence is performed on the server 192.168.199.130.

At this point, our tp6 read / write separation has been configured successfully. Is it very simple!