CodeIgniter implements read/write splitting

The current server only implements master-slave mode without configuring read/write splitting. The read/write splitting function is only implemented by the program. This article mainly describes how Codeigniter implements read/write splitting and must meet the following requirements: 1. read/write splitting should be transparent to development. There is a solution on the Internet to achieve read/write separation by manually loading multiple databases, such separation is too closely related to the business, increasing

The current server only implements master-slave mode without configuring read/write splitting. The read/write splitting function is only implemented by the program. This article mainly describes how Codeigniter implements read/write splitting and must meet the following requirements: 1. read/write splitting should be transparent to development. There is a solution on the Internet to achieve read/write separation by manually loading multiple databases, such separation is too closely related to the business, increasing

The current server only implements master-slave mode without configuring read/write splitting. The read/write splitting function is only implemented by the program. This article mainly describes how Codeigniter implements read/write splitting and must meet the following requirements:

1. read/write splitting should be transparent to development.
There is a solution on the Internet to achieve read/write splitting by manually loading multiple databases. This separation is too closely related to the business, which makes development difficult and is not conducive to maintenance. What we need to do is to implement the default read-heavy database, write is written to the master database, and read/write splitting is transparent to developers.

2. easy configuration.
Retain the existing configuration methods and add an array to configure read/write splitting without affecting the original usage.

Ideas

1. The simplest way to implement read/write splitting is to determine whether to insert the master database or read the slave database based on the query statement in the final execution of the query. Therefore, you need to find this function.

2. You should connect to the database only once. The link for the next operation should be reusable. That is to say, all the read operations after the database is reconnected are available and do not need to be connected again. The same applies to the master database. So we can put the link in the CI super object.

3. The master-slave judgment is based on the final executed SQL statement. Therefore, the autoinit parameter in the database configuration does not need to be set to true, resources are wasted if the database is connected by default and no operation is required.

4. You can use $ this-> db in the model to directly perform queries without any other adjustments.

5. Do not directly modify the file under system

Read/write splitting

The DB class of CI is fixed to read files under the system, which can be implemented through appropriate rewriting. The first is Loader. php, where the database method is used to load database objects, fixed reference of system/database/DB. PHP file. We can determine whether a custom DB exists. PHP file, introduced if it exists.

Rewrite Loader. php

public function database($params = '', $return = FALSE, $active_record = NULL){    $CI =& get_instance();    if (class_exists('CI_DB') AND $return == FALSE AND $active_record == NULL AND isset($CI->db) AND is_object($CI->db)) {        return FALSE;    }    if(file_exists(APPPATH.'core/database/DB.php')) {        require_once(APPPATH.'core/database/DB.php');    } else {        require_once(BASEPATH.'database/DB.php');    }    if ($return === TRUE) {        return DB($params, $active_record);    }    $CI->db = '';    $CI->db =& DB($params, $active_record);}/* End of file MY_Loader.php *//* Location: ./application/core/MY_Loader.php */

Next, we create database/DB. php under application/core. This file has only one DB method to read the configuration file and perform initialization. There are two places to rewrite:

Rewrite DB. php

// DB_driver.php is the parent class of all driver methods. The final query method is modified to determine whether the custom DB_driver.php exists in this file, if (file_exists (APPPATH. 'Core/database/DB_driver.php ') {require_once (APPPATH. 'Core/database/DB_driver.php ');} else {require_once (BASEPATH. 'database/DB_driver.php ');} // second place $ params ['dbdriver']. '_ driver. php 'The file is not changed, but the file has not been modified. To facilitate debugging, The // mysql driver corresponds to system/database/drivers/mysql/mysql_driver.php, the last execution method of mysql is here, // including opening, closing, and querying the database. You can add corresponding logs to the file to check whether the read/write splitting is effective if (file_exists (APPPATH. 'Core/database/drivers /'. $ params ['dbdriver ']. '/'. $ params ['dbdriver ']. '_ driver. php ') {require_once (APPPATH. 'Core/database/drivers /'. $ params ['dbdriver ']. '/'. $ params ['dbdriver ']. '_ driver. php ');} else {require_once (BASEPATH. 'database/drivers /'. $ params ['dbdriver ']. '/'. $ params ['dbdriver ']. '_ driver. php ');} // assign the current group name to param to facilitate the determination of $ params ['group _ name'] = $ active_group;/* End of file DB. php * // * Location :. /application/core/database/DB. php */

The entire DB. php adjustment is basically the introduction of files. The introduction of group name is to facilitate later judgment. If it is not introduced, you can configure it by host or database name. To disable autoint forcibly, delete the following section in DB. php:

if ($DB->autoinit == TRUE){$DB->initialize();}

The next step is the core. Read/write Splitting Based on query statements.
The simple_query method in DB_driver.php can be understood as the method for finally executing SQL statements. Here we can judge the database link.

Rewrite DB_driver.php

// Add attribute, indicating the current group var $ active_group; // Add attribute, use force to use the master database var $ db_force_master; // This method is a must for query execution, here we can determine which link to use based on the type. Function simple_query ($ SQL) {// The load_db_proxy_setting method is written in helper, or directly in this class, in helper, the method needs to be loaded automatically. The function of this method is to determine which link to use based on the current link group name and SQL read/write type, and whether to force the master database. Use master database OR repeated database? // Server Load balancer for the primary and secondary databases. single point of failure (spof) can be considered here. That is, an available configuration array is returned based on the three parameters. $ Proxy_setting = load_db_proxy_setting ($ this-> group_name, $ this-> is_write_type ($ SQL), $ this-> db_force_master); if (is_array ($ proxy_setting )&&! Empty ($ proxy_setting) {$ proxy_setting_key = key ($ proxy_setting); $ this-> group_name = $ proxy_setting_key; // assign the current configuration to the attributes of the class, if the database. php configures a DSN string, which must be processed in load_db_proxy_setting ($ proxy_setting [$ proxy_setting_key] as $ key => $ val) {$ this-> $ key = $ val;} // define the link ID as the conn _ prefix $ proxy_conn_id = 'conn _'. $ proxy_setting_key; $ CI = & get_instance (); // assign a value to the CI super object or directly read the if (isset ($ CI-> $ proxy_conn _ Id) & is_resource ($ CI-> $ proxy_conn_id) {$ this-> conn_id = $ CI-> $ proxy_conn_id;} else {$ this-> conn_id = false; $ this-> initialize (); $ CI-> $ proxy_conn_id = $ this-> conn_id;} // The value is valid only once. The next query is invalid, prevent force master database $ this-> reset_force_master ();} if (! $ This-> conn_id) {$ this-> initialize ();} return $ this-> _ execute ($ SQL) ;}// in some cases, the master database is forcibly used, run this method first to implement public function force_master () {$ this-> db_force_master = TRUE;} public function reset_force_master () {$ this-> db_force_master = FALSE ;} /* End of file DB_driver.php * // * Location :. /application/core/database/DB_driver.php */

Here, read/write splitting is basically implemented, but the database object to be linked needs to be closed, and the connection can be closed after execution in the public controller.
DB_driver.php also has the close method. Can I disable it in this method? This is not acceptable.

Close Database Link

Class MY_Controller extends CI_Controller {public function _ construct () {parent ::__ construct (); $ this-> load-> service ('common/helper_service ', NULL, 'helper '); // The following section closes the connection between the database object and the database in the CI super object, and the database object Codeigniter. in php, register_shutdown_function (function () {foreach (get_object_vars ($ this) as $ key => $ val) {if (substr ($ key, 0, 3) is disabled) = 'db _ '& is_object ($ this->{$ key}) & method_exists ($ this->{$ key}, 'close ')) {$ this-> {$ key}-> close ();} if (substr ($ key, 0, 5) = 'conn _ '& is_resource ($ this-> {$ key}) {$ this-> db-> _ close ($ val ); unset ($ this-> {$ key}) ;}}}}/ * End of file MY_Controller.php * // * Location :. /application/core/MY_Controller.php */

To enable $ this-> db in each model and connect to the database multiple times, the connection is also placed in the CI super object. This can be done without read/write splitting, which makes it easy to connect multiple databases. To use other databases for a specific model, you only need to input the group name in the constructor.

Model Adjustment

public function __construct($group_name = ''){    parent::__construct();    $this->initDb($group_name);}private function initDb($group_name = ''){    $db_conn_name = $this->getDbName($group_name);    $CI = & get_instance();    if(isset($CI->{$db_conn_name}) && is_object($CI->{$db_conn_name})) {        $this->db = $CI->{$db_conn_name};    } else {        $CI->{$db_conn_name} = $this->db = $this->load->database($group_name, TRUE);    }}private function getDbName($group_name = ''){    if($group_name == '') {        $db_conn_name = 'db';    } else {        $db_conn_name = 'db_'.$group_name;    }    return $db_conn_name;}/* End of file MY_Model.php *//* Location: ./application/core/MY_Model.php */

In the final database configuration method, you only need to configure an array on the original basis. Whether to use dual-master or one-master multi-slave mode. I first thought of adding a key name to the original configuration, but the relationship between the master and slave is still not clear. The definition method here determines the implementation method of load_db_proxy_setting.

Database. php configuration

$_master_slave_relation = array(    'default_master' => array('default_slave1', 'default_slave2', 'default_slave3'),);/* End of file database.php *//* Location: ./application/config/database.php */

The initial database link is not placed in the CI super object. It is found that the link is opened every time when multiple models are loaded. Therefore, you must test the link after completing the read/write splitting, you can check whether execution is performed as expected at the place where the database link is opened and closed (the method corresponds to db_connect and _ close in application/core/database/drivers/mysql/mysql_driver.php ). The two most important aspects of the adjustment process are the simple_query method and the closure of database links in the constructor. The adjustment in the model is to facilitate the connection to multiple databases, which is also adjusted when read/write splitting is not implemented. Common methods are independent into a file, and MY_Model is inherited.

There are many middleware for implementing MYSQL read/write splitting. When these middleware are not used, you can implement read/write splitting through program control. Of course, only read/write splitting is implemented here, and the master database can be forcibly used. If you want a better allocation method, consider the allocation method in load_db_proxy_setting.

Code reference: https://github.com/BobbyPeng/CodeIgniter-mysql-proxy

Original article address: CodeIgniter implements read/write splitting. Thank you for sharing it with the original author.