How to automatically reconnect when PHP connects to MySql_MySQL

Use PHP as a background running program (such as bulk SMS) and execute PHP in cli mode. PHP needs to connect to MySQL to perform database processing in a loop.

When the mysql connection is interrupted, subsequent execution of the loop will fail.

We need to design a method that can automatically reconnect when mysql is disconnected, so that subsequent programs can execute normally.

1. Create test data table

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Insert test data

insert into user(name) values('fdipzone'),('xfdipzone'),('terry');
mysql> select * from user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | fdipzone |
| 2 | xfdipzone |
| 3 | terry |
+----+-----------+

3. PHP files running in the background

db.php

<&#63;php
// 数据库操作类
class DB{
// 保存数据库连接
private static $_instance = null;
// 连接数据库
public static function get_conn($config){
if(isset(self::$_instance) && !empty(self::$_instance)){
return self::$_instance;
}
$dbhost = $config['host'];
$dbname = $config['dbname'];
$dbuser = $config['user'];
$dbpasswd = $config['password'];
$pconnect = $config['pconnect'];
$charset = $config['charset'];
$dsn = "mysql:host=$dbhost;dbname=$dbname;";
try {
$h_param = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);
if ($charset != '') {
$h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $charset; //設置默認編碼
}
if ($pconnect) {
$h_param[PDO::ATTR_PERSISTENT] = true;
}
$conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);
} catch (PDOException $e) {
throw new ErrorException('Unable to connect to db server. Error:' . $e->getMessage(), 31);
}
self::$_instance = $conn;
return $conn;
}
// 执行查询
public static function query($dbconn, $sqlstr, $condparam){
$sth = $dbconn->prepare($sqlstr);
try{
$sth->execute($condparam);
} catch (PDOException $e) {
echo $e->getMessage().PHP_EOL;
}
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
}
&#63;>

test.php

<&#63;php
require 'db.php';
// 数据库设定
$config = array(
'host' => 'localhost',
'dbname' => 'user',
'user' => 'root',
'password' => '',
'pconnect' => 0,
'charset' => ''
);
// 循环执行
while(true){
// 创建数据连接
$dbconn = DB::get_conn($config);
// 执行查询
$sqlstr = 'select * from user where id=&#63;';
$condparam = array(mt_rand(1,3));
$data = DB::query($dbconn, $sqlstr, $condparam);
print_r($data);
// 延时10秒
echo 'sleep 10'.PHP_EOL.PHP_EOL;
sleep(10);
}
&#63;>

4. Execute steps

Execute test.php in php cli mode, and then immediately execute mysql.server stop and mysql.server start to simulate a flash outage

mysql.server stop
Shutting down MySQL
.. SUCCESS! 
mysql.server start
Starting MySQL
SUCCESS!

As you can see, the database cannot be reconnected after the interruption, and subsequent programs cannot be executed.

Array
(
[0] => Array
(
[id] => 3
[name] => terry
)
)
sleep 10
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10
...

5. Add reconnection mechanism

if(isset(self::$_instance) && !empty(self::$_instance)){
return self::$_instance;
}

After the interruption, because the value of self::$_instance exists, calling get_conn will not reconnect, but will use the saved connection for processing.

This actually means that when the connection exists, there is no need to create a mysql connection again, reducing the number of mysql connections.

So you need to clear the value of self::$_instance after the interruption so that you can re-obtain the connection next time without using the database connection that has been created but failed.

The improvement method is as follows:

Added reset_connect method, called when an error occurs. If the error is judged to be MySQL server has gone away, the existing database connection will be cleared. After clearing, MySQL will be reconnected next time.

The modified php file is as follows:

db.php

<?php
// 数据库操作类
class DB{
// 保存数据库连接
private static $_instance = null;
// 连接数据库
public static function get_conn($config){
if(isset(self::$_instance) && !empty(self::$_instance)){
return self::$_instance;
}
$dbhost = $config['host'];
$dbname = $config['dbname'];
$dbuser = $config['user'];
$dbpasswd = $config['password'];
$pconnect = $config['pconnect'];
$charset = $config['charset'];
$dsn = "mysql:host=$dbhost;dbname=$dbname;";
try {
$h_param = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);
if ($charset != '') {
$h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $charset; //設置默認編碼
}
if ($pconnect) {
$h_param[PDO::ATTR_PERSISTENT] = true;
}
$conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);
} catch (PDOException $e) {
throw new ErrorException('Unable to connect to db server. Error:' . $e->getMessage(), 31);
}
self::$_instance = $conn;
return $conn;
}
// 执行查询
public static function query($dbconn, $sqlstr, $condparam){
$sth = $dbconn->prepare($sqlstr);
try{
$sth->execute($condparam);
} catch (PDOException $e) {
echo $e->getMessage().PHP_EOL;
self::reset_connect($e->getMessage()); // 出错时调用重置连接
}
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
// 重置连接
public static function reset_connect($err_msg){
if(strpos($err_msg, 'MySQL server has gone away')!==false){
self::$_instance = null;
}
}
}
?>

6. Perform flash execution again

You can see the improved effect. After the interruption, the current execution will fail, but subsequent executions can be re-created and a new connection continued.

Array
(
[0] => Array
(
[id] => 2
[name] => xfdipzone
)
)
sleep 10
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Array
(
)
sleep 10
Array
(
[0] => Array
(
[id] => 1
[name] => fdipzone
)
)
sleep 10
...

The above is the method that the editor introduces to you how to automatically reconnect when PHP connects to MySql. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support of the website!