Database master-slave settings_PHP tutorial-PHP Tutorial-php.cn

For some projects with relatively large access volume, we often use the database master-slave method to separate reading and writing to divert user operations and achieve load balancing. Therefore, I searched for relevant information online and made a summary. Some of the concepts below are taken from encyclopedias or online PPTs, and the codes at the end are from this project.

First of all, because I have never done a similar function before, I need to understand it conceptually:

Load Balancing
Load Balance: Balance and distribute the load (work tasks) to multiple operating units for execution, so as to complete the work tasks together. Mainly divided into two types:
1. Clustering
A single heavy-load operation is distributed to multiple node devices for parallel processing. After each node device completes processing, the results are summarized and returned to the user, greatly improving the system's processing capabilities.
2. Diversion
A large amount of concurrent access or data traffic is distributed to multiple node devices for separate processing, reducing the time users wait for responses. This is mainly targeted at network applications such as Web servers, FTP servers, and enterprise key application servers. The master-slave architecture is this type of load balancing.

Benefits of master-slave architecture
1. Load balancing (separation of reading and writing, improving data processing efficiency)
2. High availability and failover capabilities (data distribution, stability improvement. If the master server fails, the slave server can still be used for support)
3. Backup (it cannot back up itself, but it can provide a backup machine to facilitate disaster recovery, backup, recovery and other operations of the database)
4. Data consistency and avoid conflicts
5. Test Mysql upgrade

Mysql copy function
1: Supports one master and multiple slaves mechanism. Data is copied from the master server to the slave server.
2: Support multi-level structure. Master-slave, slave-slave, master-slave (mutually master-slave).
3: Support filtering function (you can copy only part of the data on the main server, not all).

Type of copy
1. Statement-based replication: a SQL statement executed on the master server, and the same SQL statement executed on the slave server. Mysql uses statement-based replication by default, which is more efficient.
2. Row-based replication: Copy the changed content instead of executing the command on the slave server (supported since mysql5.0).
3. Mixed type replication: Statement-based replication is adopted by default. When it is found that statement-based replication cannot be exact, row-based replication will be used
There are three corresponding binary logs:
1：STATEMENT
2:ROW
3: MIXED

Server structure requirements
1: Tables in the master-slave server can use different table types. In addition: a master server with multiple slave servers at the same time will affect its performance. You can use one server as a slave server proxy and use the BLOCKHOLE table type. It only records logs and does not write data. It drives multiple servers to improve performance.
2: Tables in the master-slave server can use different field types.
3: Tables in the master-slave server can use different indexes. The master server is mainly used for write operations, so indexes that ensure data relationships, such as primary keys and unique indexes, generally do not need to be added; the slave server is generally used for read operations, so indexes can be set based on query characteristics. Even more: different slave servers can set different indexes for different queries.

Copy process
1: The master server records changes to the binary log file (binary log). These records are called binary log events (binary log events)
2: The slave server copies the master’s binary log events to his relay log
3: The slave redoes events in the relay log and reflects the changes to its own data.

PHP code implementation
1. Server connection configuration file
If there is a polymorphic master|slave server, then just increase the number downwards.

[php]
[database]
dbname                              = "vis_db"
charset                             = "utf8"
;主
servers.0.master                    = true
servers.0.adapter                   = "MYSQLI"
servers.0.host                      = "vis_db"
servers.0.username                  = "vis"
servers.0.password                  = "vis"
;从
servers.1.master                    = false
servers.1.adapter                   = "MYSQLI"
servers.1.host                      = "vis_mmc"
servers.1.username                  = "vis"
servers.1.password                  = "vis"

2. Database operation code
After taking the remainder based on the user IP, determine which database on the server to connect to.
Zend Framework is used in the project.
[php]

/**
* Database factory class
*
* @create 2012-05-29
* @note: This class is used to create Zend_Db_Adapter instances of various configuration parameters
*/
include_once 'lib/getRequestIP.php';

class Free_Db_Factory
{

/**
* Zend_Db_Adapter instance array
*
* @var array
*/
protected static $_dbs = array();

Protected function __construct($sName)
{
         try {
$params = $this->_getDbConfig($sName);
                self::$_dbs[$sName] = Zend_Db::factory($params['adapter'], $params);
           } catch (Exception $e) {
If (DEBUG) {
echo $e->getMessage();
                                                                                                                                      exit;
         }
}

/**
* Get Zend_Db_Adapter instance
* @return Zend_Db_Adapter
*/
Public static function getDb($sName)
{
If (emptyempty($sName)) {
exit;
         }

If (!isset(self::$_dbs[$sName])) {
new self($sName);
         }
          return self::$_dbs[$sName];
}

/**
* Get database configuration
*/
Private function _getDbConfig($sName)
{
         $configArr = array();
         $dbConfig = Zend_Registry::get('db')->database->toArray();
          $serverConfigs = $dbConfig['servers'];
         $masters = array();
          $slaves = array();
foreach ($serverConfigs as $value) {
If (!isset($value['master'])) {
Continue;
                                                                                                                                      If (true == $value['master']) {
                   $masters[] = $value;
                                                                                                                                      If (false == $value['master']) {
                    $slaves[] = $value;
                                                                                                                                               }
          $masterNum = count($masters);
          $slaveNum = count($slaves);

$requestIP = $this->_getRequestIP();

switch ($sName) {
            case 'master' :
                if ($masterNum > 1) {
                    $configArr = $masters[$requestIP % $masterNum];
                } else {
                    $configArr = $masters[0];
                }
                break;
            case 'slave' :
                if ($slaveNum > 1) {
                    $configArr = $slaves[$requestIP % $slaveNum];
                } else {
                    $configArr = $slaves[0];
                }
                break;
            default :
                break;
        }
        if (emptyempty($configArr)) {
            return array();
        }

        $configArr['dbname'] = $dbConfig['dbname'];
        $configArr['charset'] = $dbConfig['charset'];
        return $configArr;
    }

    /**
* Get request IP
*/
    private function _getRequestIP()
    {
        $ip = getRequestIP(true);
        return sprintf('%u', ip2long($ip));
    }   www.2cto.com

    /**
* Destruct Zend_Db_Adapter entity (because some requests are time-consuming, this period may cause the database to time out)
*/
    public static function destructDb($sName = null)
    {
        if (null === $sName) {
            self::$_dbs = null;
        } else {
            unset(self::$_dbs[$sName]);
        }
    }

}
调用代码时，传入一个标志，确定是操作主还是从数据库即可：
[php]
$oSlaveDb = Free_Db_Factory::getDb('slave');

作者：xinsheng2011