How to implement automatic switching of MySQL master-slave replication with PHP

In the modern application architecture, the database is a crucial part. For high-load and high-availability applications, the MySQL master-slave replication architecture is a widely used solution. However, under the MySQL master-slave replication model, after the master node fails, the slave node needs to be manually switched to the master node. This will not only cause service interruption, but also require manual intervention, increasing operation and maintenance costs and risks.

In order to solve this problem, we can automatically determine and switch to a healthy slave node through the PHP program to ensure the high availability of the database. This article will introduce how to use PHP to implement automatic switching of MySQL master-slave replication.

1. Preparation

Before starting, ensure that the following conditions have been met:

1.1 MySQL master-slave replication architecture has been deployed successfully and is running normally.

1.2 PHP has been installed on the server and can call MySQL-related extension libraries.

1.3 Create an administrator account in MySQL and grant the user REPLICATION SLAVE and REPLICATION CLIENT permissions.

2. Implementation principle

In the MySQL master-slave replication architecture, data synchronization between the master node and the slave node is based on the binary log. The master node records all modification operations to the binary log and transmits the log to the slave node. The slave node ensures data consistency by reading the contents of the binary log.

After the master node fails, you need to manually switch from the slave node to the master node. However, MySQL provides the CHANGE MASTER TO statement, which can dynamically modify the master node address of the slave node. After the master node fails, we can call the CHANGE MASTER TO statement through PHP to switch the slave node to the master node to ensure the high availability of the database.

3. Implementation steps

3.1 Connect to MySQL database in PHP

Use the mysqli library in PHP to connect to the MySQL database. The sample code is as follows:

$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    die('Connect Error: ' . $mysqli->connect_errno . ":" . $mysqli->connect_error);
}

Where "localhost" is the host address, "username" and "password" are the username and password of the administrator account, and "database" is the name of the database to be connected.

3.2 Get the status of the current master node

In MySQL, you can get the status of the current master node through the SHOW MASTER STATUS statement. The sample code is as follows:

$sql = "SHOW MASTER STATUS";
$result = $mysqli->query($sql);
if ($result->num_rows == 1) {
    $row = $result->fetch_assoc();
    $file = $row['File'];
    $position = $row['Position'];
}

Among them, $ file and $position respectively save the binary log file name and offset of the current primary node.

3.3 Check the status of the slave node

In MySQL, you can obtain the status of the current slave node through the SHOW SLAVE STATUS statement. The sample code is as follows:

$sql = "SHOW SLAVE STATUS";
$result = $mysqli->query($sql);
if ($result->num_rows == 1) {
    $row = $result->fetch_assoc();
    $seconds_behind_master = $row['Seconds_Behind_Master'];
}

Among them, $seconds_behind_master The data synchronization delay time between the slave node and the master node is saved.

3.4 Determine whether switching is required

After obtaining the status of the master node and slave node, you can determine whether switching is required. Usually, when the master node fails, the status of the slave node will change, and $seconds_behind_master will become NULL or 0.

When it is detected that the master node fails, determine whether the slave node needs to be switched. If the conditions are met, execute the CHANGE MASTER TO statement to switch to the slave node as the master node:

if ($seconds_behind_master === NULL || $seconds_behind_master === 0) {
    $sql = "STOP SLAVE";
    $mysqli->query($sql);

    $sql = "CHANGE MASTER TO MASTER_HOST='hostname',
                       MASTER_USER='username',
                       MASTER_PASSWORD='password',
                       MASTER_LOG_FILE='{$file}',
                       MASTER_LOG_POS={$position}";
    $mysqli->query($sql);

    $sql = "START SLAVE";
    $mysqli->query($sql);
}

Among them, hostname is the slave node. The IP address of the node, username and password are the username and password of the administrator account, $file and $position are the name and offset of the master node binary log file obtained previously.

4. Conclusion

This article introduces how to realize automatic switching of MySQL master-slave replication through PHP program. By running a PHP script, you can automatically detect whether the master node has failed. If it fails, it will automatically switch from the slave node to the master node to ensure the high availability of the database. In practical applications, PHP scripts can be executed regularly to achieve 24-hour automatic monitoring and switching of the database.

The above is the detailed content of How to implement automatic switching of MySQL master-slave replication with PHP. For more information, please follow other related articles on the PHP Chinese website!