<?php

/**
 * 宝塔的mysql备份 不能备份某个表
 *
 */
include_once "../vendor/autoload.php";

$dir = __DIR__."/mysql_back/";
if(!is_dir($dir)){
    mkdir($dir,0775,true);
}

$tables = db()->query("show tables;")->fetchAll();
_echo("开始备份mysql数据");

foreach ($tables as $table){
    $table = array_values($table)[0];

    $name = $dir.$table.".create.sql";
    echo "正在备份表结构 ".$table."\n";
    // 显示表结构
    $c = db()->query("show create table {$table}")->fetch();

    file_put_contents($name,$c['Create Table']);

    if(in_array($table,['lists_auto','lists','bodies','bodies_back','lists_hot'])){
        continue;
    }

    $datafile = $dir.$table.'.'.date('Ymd').".data.json";
    // 先删除已存在的
    @unlink($datafile);

    // 删除2天前的数据
    @unlink($dir.$table.'.'.date('Ymd',strtotime("-2day")).".data.json");

    $p = 0;
    $limit = 1000;
    $totalBackupCount = 0;
    while (1){
        echo "正在备份数据 当前 $p 页\r";
        $lists = db()->all("select * from `{$table}` limit {$limit} offset ".($p*$limit));
        if($lists){
            $backupCount = 0;
            foreach ($lists as $list) {
                // 每行一个独立的JSON对象，添加换行符以便恢复时正确解析
                file_put_contents($datafile, json_encode($list, JSON_UNESCAPED_UNICODE) . "\n", FILE_APPEND | LOCK_EX);
                $backupCount++;
                $totalBackupCount++;
            }
            // 如果当前页数据少于限制数量，说明已经是最后一页
            if ($backupCount < $limit) {
                break;
            }
        }else{
            break;
        }
        $p++;
    }

    echo "表 {$table} 备份完成，共 {$totalBackupCount} 条记录\n";

}