如何通过CREATE EVENT每日创建带日期的MariaDB视图快照及相关咨询

我正在通过DBeaver操作MariaDB数据库，想要每日午夜为view1视图生成快照，创建形如snapshot_11_12_2022、snapshot_11_13_2022的带日期新表，并存储到单独数据库中。现有代码如下：

CREATE EVENT view_snapshot
    ON SCHEDULE EVERY 1 DAY STARTS '2022-11-12 00:00:00'
    DO
        CREATE TABLE snapshot AS
        select * from view1

疑问与解答 #

1. 用CREATE EVENT生成每日快照是否合适？数据量大时的替代方案

• 这种方式本身是MariaDB官方支持的定时任务方案，逻辑简单直接，适合中小数据量场景。但数据量较大时，午夜全量复制会占用大量IO/CPU资源，甚至引发锁表，影响业务。
• 替代方案：
  • 增量快照：如果视图数据包含时间戳字段，可仅复制当日新增/变更的数据（例如WHERE update_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)），提前规划快照表结构，后续合并增量即可。
  • 分区表替代快照：将存储快照的表设为按日期分区，每日午夜将视图数据写入对应分区，无需创建新表，管理和查询更高效。
  • 物理备份工具：若仅需数据备份而非可查询快照，使用MariaBackup这类物理备份工具，速度远快于逻辑复制，适合超大数据量场景。

2. 如何用mysqldump实现需求

mysqldump是逻辑备份工具，需配合系统定时任务（Linux的crontab、Windows的任务计划）使用：

1. 编写脚本实现导出+导入逻辑：
   • Linux shell脚本示例：

     #!/bin/bash
     DATE=$(date +%m_%d_%Y)
     # 仅导出view1的数据（不包含建表语句）
     mysqldump -u用户名 -p密码 源数据库 --no-create-info --complete-insert view1 > /tmp/view1_data.sql
     # 连接目标数据库，创建带日期的表并导入数据
     mysql -u用户名 -p密码 目标数据库 << EOF
     CREATE TABLE snapshot_$DATE LIKE 源数据库.view1;
     LOAD DATA INFILE '/tmp/view1_data.sql' INTO TABLE snapshot_$DATE;
     EOF
     # 清理临时文件
     rm /tmp/view1_data.sql
     

2. 配置定时任务：
   • Linux下编辑crontab，设置每日0点执行脚本：

     0 0 * * * /path/to/your/snapshot_script.sh
     

3. 如何在表名中嵌入当前日期

MariaDB的CREATE EVENT无法直接拼接变量表名，需用动态SQL实现：

方法一：在EVENT中直接使用预处理语句

CREATE EVENT view_snapshot
ON SCHEDULE EVERY 1 DAY STARTS '2022-11-12 00:00:00'
DO
BEGIN
    SET @table_name = CONCAT('目标数据库名.snapshot_', DATE_FORMAT(CURDATE(), '%m_%d_%Y'));
    SET @sql = CONCAT('CREATE TABLE ', @table_name, ' AS SELECT * FROM 源数据库名.view1');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

注意替换语句中的目标数据库名和源数据库名，且确保EVENT定义者拥有对应权限。

方法二：封装为存储过程后调用

先创建存储过程：

DELIMITER //
CREATE PROCEDURE create_view_snapshot()
BEGIN
    SET @table_name = CONCAT('目标数据库名.snapshot_', DATE_FORMAT(CURDATE(), '%m_%d_%Y'));
    SET @sql = CONCAT('CREATE TABLE ', @table_name, ' AS SELECT * FROM 源数据库名.view1');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

再修改EVENT：

CREATE EVENT view_snapshot
ON SCHEDULE EVERY 1 DAY STARTS '2022-11-12 00:00:00'
DO
    CALL create_view_snapshot();

内容的提问来源于stack exchange，提问作者stackoverflowme