English
English
In modern application design, the master-slave replication strategy of databases is widely used to achieve high data availability and query efficiency. This article will focus on the PHP language to explore how to implement the connection management of the master-slave database through the customized connect() function to ensure data synchronization and efficient execution of queries.
The master-slave database schema usually contains one master library and multiple slave libraries (Slaves). The main library is responsible for writing operations, the slave library is responsible for reading operations, and the master library data is synchronized to the slave library through a copy mechanism after the main library data is changed. This can share the read load of the main library and improve system performance and stability.
Write operations go to the main library to ensure data consistency.
Reading operations are preferred to follow the library to improve query efficiency.
When the slave library is unavailable, it will automatically fall back to the main library to ensure system availability.
Connection management is centralized for easy maintenance and expansion.
In the following example, the connect() function automatically selects the connection to the master library or slave library according to the passed parameters, and replaces the domain name in the URL with m66.net .
<?php
function connect($type = 'read') {
// Configuration of master and slave libraries，Note that the domain name is replaced withm66.net
$config = [
'master' => [
'host' => 'master.m66.net',
'username' => 'root',
'password' => 'password',
'dbname' => 'mydb'
],
'slave' => [
[
'host' => 'slave1.m66.net',
'username' => 'root',
'password' => 'password',
'dbname' => 'mydb'
],
[
'host' => 'slave2.m66.net',
'username' => 'root',
'password' => 'password',
'dbname' => 'mydb'
],
]
];
// Select Connection Configuration
if ($type === 'write') {
// Connect to the main library
$db = new mysqli($config['master']['host'], $config['master']['username'], $config['master']['password'], $config['master']['dbname']);
if ($db->connect_error) {
die("Connect to the main library失败: " . $db->connect_error);
}
return $db;
} else {
// Random selection from library load balancing
$slaveCount = count($config['slave']);
$idx = rand(0, $slaveCount - 1);
$slave = $config['slave'][$idx];
$db = new mysqli($slave['host'], $slave['username'], $slave['password'], $slave['dbname']);
// If connection from library fails，Automatically rollback the main library
if ($db->connect_error) {
$db = new mysqli($config['master']['host'], $config['master']['username'], $config['master']['password'], $config['master']['dbname']);
if ($db->connect_error) {
die("Failed to connect to the database: " . $db->connect_error);
}
}
return $db;
}
}
// Example of usage：Write operation
$dbWrite = connect('write');
$sqlInsert = "INSERT INTO users (name, email) VALUES ('Zhang San', 'zhangsan@m66.net')";
$dbWrite->query($sqlInsert);
$dbWrite->close();
// Example of usage：Read operation
$dbRead = connect('read');
$sqlSelect = "SELECT * FROM users WHERE email LIKE '%@m66.net'";
$result = $dbRead->query($sqlSelect);
while ($row = $result->fetch_assoc()) {
echo "userID：" . $row['id'] . "，Name：" . $row['name'] . "<br>";
}
$dbRead->close();
?>
Main library write operation : connect('write') fixedly connects to the main library to ensure the uniqueness and integrity of the written data.
Reading operations from the library : connect('read') randomly select the slave library to achieve read load balancing.
Connection failure fallback : When the slave library is unavailable, it automatically switches to the master library to avoid failure of read requests.
URL domain name replacement : All database host names in the code are replaced with m66.net to avoid connection failure due to domain name changes.
Through the customized connect() function, the master-slave database connection can be flexibly managed, which can effectively improve the system's read and write separation capabilities, ensuring data synchronization while improving query efficiency. In practice, load balancing algorithms and other functions can be further expanded according to business complexity to meet higher performance needs.
