Distributed Database (Query Builder 20)
Distributed support
The data access layer supports distributed databases, including read-write separation. To enable distributed databases, you need to enable the deploy parameters:
return [ // 启用分布式数据库 'deploy' => 1, // 数据库类型 'type' => 'mysql', // 服务器地址 'hostname' => '192.168.1.1,192.168.1.2', // 数据库名 'database' => 'demo', // 数据库用户名 'username' => 'root', // 数据库密码 'password' => '', // 数据库连接端口 'hostport' => '', ];
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 main 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, you only need to set one. For different parameters, you can set them separately, for example:
return [ // 启用分布式数据库 'deploy' => 1, // 数据库类型 'type' => 'mysql', // 服务器地址 'hostname' => '192.168.1.1,192.168.1.2,192.168.1.3', // 数据库名 'database' => 'demo', // 数据库用户名 'username' => 'root,slave,slave', // 数据库密码 'password' => '123456', // 数据库连接端口 'hostport' => '', // 数据库字符集 'charset' => 'utf8', ];
Remember, either the same or each one needs to be set. set up.
Distributed database parameters support the use of array definitions (usually to avoid misparsing multiple accounts and passwords), for example:
return [ // 启用分布式数据库 'deploy' => 1, // 数据库类型 'type' => 'mysql', // 服务器地址 'hostname' =>[ '192.168.1.1','192.168.1.2','192.168.1.3'], // 数据库名 'database' => 'demo', // 数据库用户名 'username' => 'root,slave,slave', // 数据库密码 'password' => ['123456','abc,def','hello'] // 数据库连接端口 'hostport' => '', // 数据库字符集 'charset' => 'utf8', ];
Separation of reading and writing
You can also set whether the reading and writing of the distributed database are separated. By default, reading and writing are not separated, that is, each server can perform read and write operations. For a master-slave database, you need to set the read and write operations. Write separation can be achieved through the following settings:
'rw_separate' => true,
In the case of read and write separation, the default first database configuration is the configuration information of the main server, which is responsible for writing data. If the master_num parameter is set, then Multiple master server writes can be supported (randomly connecting to one master server at a time). The other addresses are all from the database, responsible for reading data, and the number is not limited. Each time you connect to the slave server and perform a read operation, the system will randomly select from the slave server. Each request for the same database connection will only connect the master server and the slave server once. If the slave server for a certain request cannot be connected, it will automatically switch to the master server for query operations.
If you do not want random reading, or in some cases other slave servers are temporarily unavailable, you can also set slave_no to specify a fixed server for read operations. The serial number specified by slave_no represents the serial number of the database address in hostname, starting from 0 start.
If you call the CURD operation of a query class or model, the system will automatically determine whether the currently executed method is a read operation or a write operation and automatically connect the master and slave servers. If you are using native SQL, you need to pay attention to the system Default rule: Write operations must use the execute method of the database, and read operations must use the query method of the database. Otherwise, master-slave read and write chaos will occur.
If the following situations occur, the main server will be automatically connected:
1. The write operation method of the database is used (execute/insert/update/delete and derived methods);
2. If the database transaction method is called, it will automatically connect to the main server;
3. If the slave server connection fails, it will automatically connect to the main server;
4. The query constructor is called lock method;
5. The master/readMaster method of the query constructor is called
The data synchronization work of the master-slave database is not implemented in the framework, and the database needs to consider its own synchronization or replication mechanism. If there may be a synchronization delay after writing data in a large amount of data or under special circumstances, you can call the master() method to query the main database.
In the actual production environment, the database distributed implementation mechanism of many cloud hosts will be different from local development, but usually the following two methods are used:
The first one: Provide The write IP and read IP (usually a virtual IP) are used to separate the read and write operations of the database;
The second type: always maintain the same IP to connect to the database, and read and write separated IP scheduling will be performed internally (Alibaba Cloud That’s how it works).
Main library reading
In some cases, it is necessary to read data directly from the main database. For example, after the data has just been written, the data from the slave database has not had time to be synchronized. You can use
Db::name('user')
->where('id', 1)
->update(['name' => 'thinkphp']);
Db::name('user')
->master(true)
->find(1);However, the actual situation is far more complicated than this. Complex, because you don't know whether there are still related query operations in the subsequent methods. At this time, we can configure the read_master configuration parameter to open the database.
// 开启自动主库读取 'read_master' => true,
After it is turned on, once we write to a data table, all subsequent queries to the table in the current request will use the main library to read.
