English
English
In modern high availability architecture, database disaster recovery (Failover) is an important means to ensure the high reliability of the system. Especially in distributed databases or master-slave architectures, when the master database fails, it is necessary to switch to the standby database to continue providing services. To achieve this, MySQL provides the property mysqli::$errno to help developers determine whether there is an error in database operations and trigger a disaster recovery switch through error codes.
This article will introduce how to determine whether the database needs disaster recovery switching through mysqli::$errno , and briefly implement a disaster recovery switching example.
mysqli::$errno is a property in the mysqli class, which represents the error code of the previous MySQL query operation. If the query is successful, the value of the property is 0; if an error occurs, the property will return the corresponding MySQL error code. This property can be used to determine whether an error has occurred and make corresponding error handling.
Common error codes are:
2002 : Connection refused , the database cannot be connected.
1040 : Too many connections , the number of database connections reaches the upper limit.
1213 : Deadlock , deadlock error.
Assuming you are using the MySQL master-slave architecture, if the master library fails, it should automatically switch to the alternate slave library. We can use mysqli::$errno to determine the error code to determine whether disaster recovery switching is needed.
Here is an example of a simplified disaster recovery switching implementation:
<?php
// Database connection configuration
$primary_db_config = [
'host' => 'primary.m66.net',
'user' => 'root',
'password' => 'password',
'database' => 'test_db'
];
$secondary_db_config = [
'host' => 'secondary.m66.net',
'user' => 'root',
'password' => 'password',
'database' => 'test_db'
];
// Create a database connection
function connectDatabase($db_config) {
$mysqli = new mysqli($db_config['host'], $db_config['user'], $db_config['password'], $db_config['database']);
if ($mysqli->connect_error) {
echo "Connection failed: " . $mysqli->connect_error . "\n";
return null;
}
return $mysqli;
}
// Try to connect to the main library
$mysqli = connectDatabase($primary_db_config);
if ($mysqli === null) {
// 如果主库Connection failed，Switch to the backup library
echo "主数据库Connection failed，Switch to standby database...\n";
$mysqli = connectDatabase($secondary_db_config);
}
// Perform query operations
if ($mysqli) {
$query = "SELECT * FROM some_table";
if ($mysqli->query($query) === FALSE) {
// Determine whether a disaster recovery switching is required based on the error code
echo "Query failed: " . $mysqli->errno . " - " . $mysqli->error . "\n";
if ($mysqli->errno == 2002 || $mysqli->errno == 1040) {
// If the database connection is wrong or the number of connections is full，Try to switch database
echo "Try to switch database...\n";
$mysqli = connectDatabase($secondary_db_config);
}
} else {
echo "Query successful\n";
}
}
Database configuration : We configured database connection information for the master and slave libraries. The connection configuration of the main library is stored in the $primary_db_config variable, and the configuration of the slave library is stored in the $secondary_db_config .
Database connection : The connectDatabase() function is used to establish a database connection. It receives database configuration information and returns a mysqli object, and if the connection fails, return null .
Switch to slave when the main library connection fails : If the main library connection fails, we try to connect to slave and point the $mysqli variable to the new connection.
Query operation : Execute database query through $mysqli->query() . If the query fails, we obtain the error code through $mysqli->errno to determine whether disaster recovery switching is needed. For example, errno == 2002 means that the database connection failed, and errno == 1040 means that the database connection number is full. In both cases, we try to switch to the standby database.
Using mysqli::$errno can effectively help us judge the errors in database operations and determine whether disaster recovery switching is required through the error code. With this simple example, we show how to perform disaster recovery switching in the master-slave database schema. In actual development, you can customize the disaster recovery switching logic based on specific error codes and system requirements to further improve the usability and stability of the system.
