PHP uses SWOOLE extension to implement scheduled synchronization of MySQL data

This article introduces you to a relatively special task. We use PHP SWOOLE to make an asynchronous scheduled task system. How to implement it? Let’s take a closer look.

Nanning Company We use a calling system with several branches, and now we need to do a call data analysis. Since the branch's call server is on the intranet and mapped out through technical means, the network between the branch and Nanning is unstable. , so the call data of the branch needs to be synchronized to Nanning.

The simplest method is to directly configure the master-slave synchronization of MySQL to synchronize the data to Nanning. But the company on the sales call system doesn't give us MySQL permissions. So this method can only be abandoned.

So we simply thought of using PHP to implement a simple PHP timing synchronization tool, and then the PHP process ran in the background, so first we came to a PHP component: SWOOLE. After discussion, the branch company The maximum amount of data generated in half a day is about 5,000, so this solution is feasible, so just do it.

We use PHP SWOOLE to make an asynchronous scheduled task system.

The master-slave synchronization of the MySQL database itself synchronizes data to the slave database by parsing the binary-log in the Master database. However, when we use PHP to synchronize data, we can only query the data in batches from the master library and then insert it into the slave library in Nanning.

The framework we use here is ThinkPHP 3.2 .

First install the PHP extension: SWOOLE, because no special functions are used, so here we use pecl for quick installation :

pecl install swoole

After the installation is complete, add extension="swoole.so" to php.ini. The installation is complete. After that, we use phpinfo() to check whether it is successful.

After the installation is successful, we will write the business.

Server

1. First start a background server and listen to port 9501

public function index()
{
 $serv = new \swoole_server("0.0.0.0", 9501);
 $serv->set([
  'worker_num' => 1,//一般设置为服务器CPU数的1-4倍
  'task_worker_num' => 8,//task进程的数量
  'daemonize' => 1,//以守护进程执行
  'max_request' => 10000,//最大请求数量
  "task_ipc_mode " => 2 //使用消息队列通信，并设置为争抢模式
 ]);
 $serv->on('Receive', [$this, 'onReceive']);//接收任务，并投递
 $serv->on('Task', [$this, 'onTask']);//可以在这个方法里面处理任务
 $serv->on('Finish', [$this, 'onFinish']);//任务完成时候调用
 $serv->start();
}

2. Receive and Delivery task

public function onReceive($serv, $fd, $from_id, $data)
{
 //使用json_decode 解析任务数据
 $areas = json_decode($data,true);
 foreach ($areas as $area){
  //投递异步任务
  $serv->task($area);
 }
}

3. Task execution, data is queried and written from the master database to the slave database

public function onTask($serv, $task_id, $from_id, $task_data)
{
 $area = $task_data;//参数是地区编号
 $rows = 50; //每页多少条
 //主库地址,根据参数地区（$area）编号切换master数据库连接
 //从库MySQL实例,根据参数地区（$area）编号切换slave数据库连接
 //由于程序是常驻内存的，所以MySQL连接可以使用长连接，然后重复利用。要使用设计模式的，可以使用对象池模式
 Code......

 //master 库为分公司的数据库，slave库为数据同步到南宁后的从库
 Code......

 //使用$sql获取从库中最大的自增: SELECT MAX(id) AS maxid FROM ss_cdr_cdr_info limit 1
 $slaveMaxIncrementId = ...;

 //使用$sql获取主库中最大的自增: SELECT MAX(id) AS maxid FROM ss_cdr_cdr_info limit 1
 $masterMaxIncrementId = ...;

 //如果相等的就不同步了
 if($slaveMaxIncrementId >= $masterMaxIncrementId){
  return false;
 }

 //根据条数计算页数
 $dataNumber = ceil($masterMaxIncrementId - $slaveMaxIncrementId);
 $eachNumber = ceil($dataNumber / $rows);
 $left = 0;

 //根据页数来进行分批循环进行写入，要记得及时清理内存
 for ($i = 0; $i < $eachNumber; $i++) {
  $left = $i == 0 ? $slaveMaxIncrementId : $left + $rows;
  $right = $left + $rows;
  //生成分批查询条件
  //$where = "id > $left AND <= $right";
  $masterData = ...;//从主库查询数据
  $slaveLastInsertId = ...;//插入到从库
  unset($masterData,$slaveLastInsertId);
 }

 echo "New AsyncTask[id=$task_id]".PHP_EOL;
 $serv->finish("$area -> OK");
}

4. When the task is completed, call

public function onFinish($serv, $task_id, $task_data)
{
 echo "AsyncTask[$task_id] Finish: $task_data".PHP_EOL;
}

Client push task

This is basically it Completed, the rest is for us to write the client task push

public function index()
{
 $client = new \swoole_client(SWOOLE_SOCK_TCP);
 if (!$client->connect('127.0.0.1', 9501, 1)) {
  throw new Exception('链接SWOOLE服务错误');
 }
 $areas = json_encode(['liuzhou','yulin','beihai','guilin']);
 //开始遍历检查
 $client->send($areas);
 echo "任务发送成功".PHP_EOL;
}

This is basically completed, the rest is for us to write a shell script for regular execution: /home/ wwwroot/sync_db/crontab/send.sh

#!/bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
export PATH

# 定时推送异步的数据同步任务
/usr/bin/php /home/wwwroot/sync_db/server.php home/index/index

Using crontab scheduled tasks, we add the script to the scheduled tasks

#设置每天12：30执行数据同步任务
30 12 * * * root /home/wwwroot/sync_db/crontab/send.sh
#设置每天19：00执行数据同步任务
0 19 * * * root /home/wwwroot/sync_db/crontab/send.sh

Tips: It is best to add the log writing operation to it, so that you can know whether the task push and execution are successful.

The above is the entire content of this article, I hope it will be helpful to everyone's study.

Related recommendations:

php Detailed explanation of UNIX timestamp usage

Ajax PHP JavaScript MySQL implements a simple non-refresh online chat room

Solve the problem of PHP processing form submission of multiple input tags with the same name attribute value

The above is the detailed content of PHP uses SWOOLE extension to implement scheduled synchronization of MySQL data. For more information, please follow other related articles on the PHP Chinese website!