Based on yiisoft/yii2-app-advanced, create a new repository yii2-app-advanced on github, automatic timing deletion of log messages
1. Since the log message is stored in the log table of mysql, in the production environment, a large amount of data has been accumulated, and the data length of the log table has reached hundreds of gigabytes. Therefore, it is decided to implement the timing deletion of log messages based on the console command line, and the data length of the log table of the development environment is 14.64 GB, as shown in Figure 1
Figure 1
2. Edit \Console\Controllers\LogController.php, get the current server time, determine the range ([0 points, 1 point)), if not within the range, delay execution for 10 * 60 seconds, and then exit
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2019/10/08
* Time: 13:36
*/
namespace console\controllers;
use Yii;
use console\models\Log;
use yii\console\Controller;
use yii\console\ExitCode;
use yii\helpers\ArrayHelper;
use yii\web\HttpException;
use yii\web\ServerErrorHttpException;
/**
* 日志
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/
class LogController extends Controller
{
const SLEEP_TIME = 10 * 60; // 延缓执行 10 * 60 秒
/**
* 日志的删除
*
*/
public function actionDelete()
{
/* 获取服务器当前时间，判断范围([0点，1点))，如果不在范围内，则延缓执行 10 * 60 秒，再退出 */
$time = time();
$zeroTime = strtotime("today"); // 0点
$oneTime = strtotime("today +1 hours"); // 1点
echo $time . " ";
echo date('Y-m-d H:i:s', $time) . " ";
echo $zeroTime . " ";
echo date('Y-m-d H:i:s', $zeroTime) . " ";
echo $oneTime . " ";
echo date('Y-m-d H:i:s', $oneTime) . " ";
exit;
if (!($time >= $zeroTime && $time < $oneTime)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
echo 1;
exit;
}
}
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1570518464 2019-10-08 15:29:17 1570464000 2019-10-08 00:00:00 1570467600 2019-10-08 01:00:00
3. Query the log time ($logtime) of 1 record, based on the log time ascending order, if it does not exist, the execution is delayed for 10 * 60 seconds, and then exited
/* 查询 1 条记录的日志时间($logTime)，基于 日志时间 升序排列，如果不存在，则延缓执行 10 * 60 秒，再退出 */
$logItem = Log::find()->select(['log_time'])->orderBy(['log_time' => SORT_ASC])->limit(1)->one();
if (!isset($logItem)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
$logTime = $logItem->log_time;
4. Get the value of the first 15 days of the current server (default value, customizable) ($reservedtime: retention time, 0 points), if $logtime is greater than or equal to $reservedtime, delay execution 10 * 60 seconds, then exit
/* 获取服务器当前时间的前 15 天(默认值，可自定义)的值($reservedTime：保留时间，0点)，如果 $logTime 大于等于 $reservedTime，则延缓执行 10 * 60 秒，再退出 */
$reservedTime = strtotime("today -15 days"); // 保留时间，0点
echo $logTime . " ";
echo date('Y-m-d H:i:s', $logTime) . " ";
echo $reservedTime . " ";
echo date('Y-m-d H:i:s', $reservedTime) . " ";
if ($logTime >= $reservedTime) {
echo 0;
exit;
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
echo 1;
exit;
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567046168.2703 2019-08-29 10:36:08 1569168000 2019-09-23 00:00:00 1
5. Get the value of 1 day after the log time ($logtime) of 1 record ($deadlinetime: deadline, 0 points), if $deadlinetime is greater than $reservedtime, it will be $reservedTime is assigned to $deadlinetime (this condition is never satisfied when the value is 1 day later, and when it is greater than 1 day, this condition may be satisfied)
/* 获取 1 条记录的日志时间($logTime)的后 1 天的值($deadlineTime：截止时间，0点)，如果 $deadlineTime 大于 $reservedTime，则将 $reservedTime 赋值给 $deadlineTime(后 1 天的值时，此条件永远不满足，大于 1 天时，此条件可能满足) */
$logDate = date('Y-m-d', $logTime);
$deadlineTime = strtotime("$logDate +1 days"); // 截止时间，0点
echo $logDate . " ";
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
if ($deadlineTime > $reservedTime) {
$deadlineTime = $reservedTime;
echo 1;
exit;
}
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
echo 2;
exit;
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
2019-08-29 2019-08-30 00:00:00 2019-08-30 00:00:00 2
6. Execute the delete sql(), the condition (log time is less than $deadlinetime), that is, the command line runs only for 1 day of log messages, delay the execution for 60 seconds, and then exit.
Note: Execute the delete sql(), delete from `cpa_log` where `log_time` < 1570590733, delete more than 260,000 records (1 day log message), found execution time up to 1 minute, as shown in Figure 2
Figure 2
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2019/10/08
* Time: 13:36
*/
namespace console\controllers;
use Yii;
use console\models\Log;
use yii\console\Controller;
use yii\console\ExitCode;
use yii\helpers\ArrayHelper;
use yii\web\HttpException;
use yii\web\ServerErrorHttpException;
/**
* 日志
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/
class LogController extends Controller
{
const SLEEP_TIME = 10 * 60; // 延缓执行 10 * 60 秒
/**
* 日志的删除
*
*/
public function actionDelete()
{
/* 获取服务器当前时间，判断范围([0点，1点])，如果不在范围内，则延缓执行 10 * 60 秒，再退出 */
$time = time(); // 当前时间
$zeroTime = strtotime("today"); // 0点
$oneTime = strtotime("today +1 hours"); // 1点
if (($time >= $zeroTime && $time <= $oneTime)) {
// 延缓执行 10 * 60 秒
echo 0;
exit;
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 查询 1 条记录的日志时间($logTime)，基于 日志时间 升序排列，如果不存在，则延缓执行 10 * 60 秒，再退出 */
$logItem = Log::find()->select(['log_time'])->orderBy(['log_time' => SORT_ASC])->limit(1)->one();
if (!isset($logItem)) {
// 延缓执行 10 * 60 秒
echo 1;
exit;
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
$logTime = $logItem->log_time;
/* 获取服务器当前时间的前 15 天(默认值，可自定义)的值($reservedTime：保留时间，0点)，如果 $logTime 大于等于 $reservedTime，则延缓执行 10 * 60 秒，再退出 */
$reservedTime = strtotime("today -15 days"); // 保留时间，0点
echo $logTime . " ";
echo date('Y-m-d H:i:s', $logTime) . " ";
echo $reservedTime . " ";
echo date('Y-m-d H:i:s', $reservedTime) . " ";
if ($logTime >= $reservedTime) {
echo 2;
exit;
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 获取 1 条记录的日志时间($logTime)的后 1 天的值($deadlineTime：截止时间，0点)，如果 $deadlineTime 大于 $reservedTime，则将 $reservedTime 赋值给 $deadlineTime(后 1 天的值时，此条件永远不满足，大于 1 天时，此条件可能满足) */
$logDate = date('Y-m-d', $logTime);
$deadlineTime = strtotime("$logDate +1 days"); // 截止时间，0点
echo $logDate . " ";
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
if ($deadlineTime > $reservedTime) {
$deadlineTime = $reservedTime;
}
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
/* 执行删除 SQL()，条件(日志时间 小于 $deadlineTime)，即命令行 1 次运行仅删除 1 天的日志消息，延缓执行 60 秒，再退出 */
Log::deleteAll(['<', 'log_time', $deadlineTime]);
// 延缓执行 60 秒
//sleep(60);
//return ExitCode::OK;
echo 3;
exit;
}
}
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567046168.2703 2019-08-29 10:36:08 1569168000 2019-09-23 00:00:00 2019-08-29 2019-08-30 00:00:00 2019-08-30 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567094579.432 2019-08-30 00:02:59 1569168000 2019-09-23 00:00:00 2019-08-30 2019-08-31 00:00:00 2019-08-31 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567180800.2962 2019-08-31 00:00:00 1569168000 2019-09-23 00:00:00 2019-08-31 2019-09-01 00:00:00 2019-09-01 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567267282.2226 2019-09-01 00:01:22 1569168000 2019-09-23 00:00:00 2019-09-01 2019-09-02 00:00:00 2019-09-02 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1568958887.1824 2019-09-20 13:54:47 1569168000 2019-09-23 00:00:00 2019-09-20 2019-09-21 00:00:00 2019-09-21 00:00:00
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1569832928.1543 2019-09-30 16:42:08 1569168000 2019-09-23 00:00:00 2
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1569832928.1543 2019-09-30 16:42:08 1569168000 2019-09-23 00:00:00 2
7. Set the parameters, the retention time of the log’s timing deletion (the first 15 days), the unit is days, edit \console\config\params-local.php
<?php
return [
'logDeleteReservedTime' => 15, // 日志的定时删除的保留时间(前 15 天)，单位为天
];
8. The final code implementation, the data length of the log table of the development environment is 2.36 GB, which is in line with the expectations, as shown in Figure 3
Figure 3
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2019/10/08
* Time: 13:36
*/
namespace console\controllers;
use Yii;
use console\models\Log;
use yii\console\Controller;
use yii\console\ExitCode;
/**
* 日志
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/
class LogController extends Controller
{
const SLEEP_TIME = 10 * 60; // 延缓执行 10 * 60 秒
/**
* 日志的删除
*/
public function actionDelete()
{
/* 获取服务器当前时间，判断范围([0点，1点])，如果不在范围内，则延缓执行 10 * 60 秒，再退出 */
$time = time(); // 当前时间
$zeroTime = strtotime("today"); // 0点
$oneTime = strtotime("today +1 hours"); // 1点
if (!($time >= $zeroTime && $time < $oneTime)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 查询 1 条记录的日志时间($logTime)，基于 日志时间 升序排列，如果不存在，则延缓执行 10 * 60 秒，再退出 */
$logItem = Log::find()->select(['log_time'])->orderBy(['log_time' => SORT_ASC])->limit(1)->one();
if (!isset($logItem)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
$logTime = $logItem->log_time;
/* 获取服务器当前时间的前 15 天(默认值，可自定义)的值($reservedTime：保留时间，0点)，如果 $logTime 大于等于 $reservedTime，则延缓执行 10 * 60 秒，再退出 */
$logDeleteReservedTime = Yii::$app->params['logDeleteReservedTime'];
$reservedTime = strtotime("today -$logDeleteReservedTime days"); // 保留时间，0点
if ($logTime >= $reservedTime) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 获取 1 条记录的日志时间($logTime)的后 1 天的值($deadlineTime：截止时间，0点)，如果 $deadlineTime 大于 $reservedTime，则将 $reservedTime 赋值给 $deadlineTime(后 1 天的值时，此条件永远不满足，大于 1 天时，此条件可能满足) */
$logDate = date('Y-m-d', $logTime);
$deadlineTime = strtotime("$logDate +1 days"); // 截止时间，0点
if ($deadlineTime > $reservedTime) {
$deadlineTime = $reservedTime;
}
/* 执行删除 SQL()，条件(日志时间 小于 $deadlineTime)，即命令行 1 次运行仅删除 1 天的日志消息，延缓执行 60 秒，再退出 */
Log::deleteAll(['<', 'log_time', $deadlineTime]);
// 延缓执行 60 秒
sleep(60);
return ExitCode::OK;
}
}
