Database Master/slave settings _ PHP Tutorial

Database Master/slave settings. For projects with relatively large access volumes, we often use the database master-slave mode for read/write splitting to divert user operations and achieve load balancing. Therefore, we found related information on the internet. for some projects with relatively large access volumes, we often use the database master/slave mode for read/write splitting to divert user operations and achieve load balancing. Therefore, I searched for the relevant information on the Internet and made a summary. The following concepts are taken from encyclopedia or online PPT. the final code is from this project.

First of all, because you have not done similar functions before, you need to understand the concept:

Server load balancer
Load Balance: balances the Load (job) and distributes the Load to multiple operation units for execution to complete the job together. There are two types:
1. Cluster (clustering)
The operation of a single heavy load is distributed to multiple node devices for parallel processing. after each node device finishes processing, the results are summarized and returned to the user, greatly improving the system processing capability.
2. shunting
A large amount of concurrent access or data traffic is distributed to multiple node devices for separate processing, reducing users' waiting for response time. This is mainly for Web servers, FTP servers, enterprise key application servers, and other network applications. This is the type of server load balancer in the master-slave architecture.

Benefits of master-slave architecture
1. server load balancer (read/write splitting to improve data processing efficiency)
2. high availability and failover capabilities (improved data distribution and stability. If the master server fails, it can also be supported by the slave server)
3. backup (backup is not supported, but a backup machine is provided to facilitate database disaster tolerance, backup, recovery, and other operations)
4. data consistency to avoid conflicts
5. test Mysql upgrade

Mysql replication
1: one master and multiple slaves are supported. Data is copied to the slave server through the master server.
2: supports multi-level structure. Master-slave, slave-master, and master-slave ).
3: supports filtering (only part of the data on the master server can be copied, not all ).

Replication type
1. statement-based replication: SQL statements executed on the master server and the same SQL statements 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 (MySQL is supported ).
3. hybrid replication: Statement-based replication is used by default. If the statement-based replication fails, row-based replication is used.
There are also three types of binary logs:
1: STATEMENT
2: ROW
3: MIXED

Server structure requirements
1: tables on the master/slave server can use different table types. In addition, a primary server with multiple slave servers will affect its performance. you can use one server as the slave server proxy and use the BLOCKHOLE table type. It only records logs and does not write data. it carries multiple servers to improve performance.
2: tables on the master/slave server can use different field types.
3: tables on the master/slave server can use different indexes. The master server is mainly used for write operations. Therefore, indexes that ensure data relations, such as primary keys and Unique Indexes, can be excluded. slave servers are generally used for read operations. Therefore, you can set indexes for query features. Even different slave servers can set different indexes for different queries.

Copy process
1: The master server records changes to binary log files. these records are called binary log events)
2: The slave server copies the binary log events of the master to its relay log)
3: The slave redo relay log event will reflect the change to its own data.

PHP code implementation
1. server connection configuration file
If you have a multi-state master or slave server, you only need to increment the number.

[Php]
[Database]
Dbname = "vis_db"
Charset = "utf8"
Master
Servers.0.master = true
Servers.0.adapter = "MYSQLI"
Servers.0.host = "vis_db"
Servers.0.username = "vis"
Servers.0.password = "vis"
; From
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 the user IP address is used to obtain the remainder, determine which server to connect to the database.
The Zend Framework is used in the project.
[Php]

/**
* Database factory
*
* @ Create 2012-05-29
* @ Note: This class is used to create Zend_Db_Adapter instances with 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;
}

/**
* Obtain the request IP address
*/
Private function _ getRequestIP ()
{
$ Ip = getRequestIP (true );
Return sprintf ('% u', ip2long ($ ip ));
} Www.2cto.com

/**
* Analyze the Zend_Db_Adapter entity (because some requests consume time, this time may cause database timeout)
*/
Public static function destructDb ($ sName = null)
{
If (null ===$ sName ){
Self: $ _ dbs = null;
} Else {
Unset (self ::$ _ dbs [$ sName]);
}
}

}
When calling the code, input a flag to determine whether to operate the master or slave database:
[Php]
$ OSlaveDb = Free_Db_Factory: getDb ('Slave ');

Author: xinsheng2011

Bytes. So I found related emails online...