mysql历史数据自动归档_sdmei-CSDN博客_mysql 归档

标签: | 发表时间:2021-07-03 11:38 | 作者:
出处:https://blog.csdn.net

数据库跑一段时间后,因为查询性能、磁盘容量,运维管理等方面的原因,需要将在线数据挪到历史库(不同的服务器)。如我们的在线订单只留3个月数据,3个月以前的就需要到历史库查了。

自动归档常见的方式有pt-archiver,但我还是觉得自己写存储过程更靠谱。。。

思路:

  • 在线库实例打开federated支持,创建数据库dborder(业务库), linkhis(归档用);
  • 历史库创建历史表dborderhis.myorder_tab_his;
  • 在linkhis库下创建federated表linkhis.myorder_tab_his,指向dborderhis.myorder_tab_his;
  • 在linkhis库下创建日志表archive_log,存储过程proc_archive,proc_archive_pkg,并通过JOB调度proc_archive_pkg;
  • 在线库的从库需要忽略linkhis的复制:replicate-ignore-db=linkhis,否则从库也会往这个历史库重复同步数据。

日志记录表archive_log

      create table archive_log
(
  id bigint auto_increment PRIMARY key,
  tab_name           varchar(40),
  archive_date_begin datetime,
  archive_date_end   datetime,
  create_time        datetime default CURRENT_TIMESTAMP(),
  status             int(1),
  insert_rows        bigint(11),
  delete_rows        bigint(11),
  remark             varchar(1000)
)

存储过程:proc_archive

      CREATE PROCEDURE proc_archive(in i_table_source varchar(40), 
                              in i_table_target varchar(40), 
                              in i_fieldname varchar(40), 
                              in i_keepdays int,
                              in i_archdays int,
                              in i_other_cond varchar(500))
begin
  /*
  入参:
  i_table_source:原表,含dbname
  i_table_target:federated表
  i_fieldname:时间字段
  i_keepdays:保留天数
  i_archdays:每次归档多少天数据
  i_other_cond:数据额外条件(如status in (2,3)不能归档,需要保留),无额外条件则输入'1=1'
  归档日志表archive_log.status字段含义:
  0:成功, 1:现有数据在保留天数内, 2:目标表含有待归档时间范围的数据, 
  3:插入数据和删除数据记录数不同, 4:SQL执行异常,具体错误见remark
  注意:
  有额外条件时,如果历史数据被修改,从不符合归档条件变成符合归档条件,
  因历史表中归档时间段内已经有之前归档的数据(@v_his_num_before>0),程序会退出,需手动处理
  */
  declare EXIT HANDLER for SQLWARNING,NOT FOUND,SQLEXCEPTION 
  begin 
      GET DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT;
      ROLLBACK;
      insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
      values(i_table_source,@v_arch_begin,@v_arch_end,4,@v_his_num_after,@v_del_num,concat('error ',@p1,' - ',@p2));
  end;
  /* 获取在线表的最小日期 */
  set @mystmt = concat("select str_to_date(date_format(min(",i_fieldname,"),'%Y%m%d'),'%Y%m%d') into @v_arch_begin from ",i_table_source,' where ',i_other_cond);
  prepare stmt from @mystmt;
  execute stmt;
  deallocate prepare stmt;

  set @v_arch_end = date_add(@v_arch_begin,interval i_archdays day);

  set @mystmt = concat("select count(*) into @v_his_num_before from ",i_table_target," where ",i_fieldname," >= ? and ",i_fieldname," < ?");
  prepare stmt from @mystmt;
  execute stmt using @v_arch_begin,@v_arch_end;
  deallocate prepare stmt;
  /* 如果在线表的数据低于keepday范围,退出 */
  if timestampdiff(day,@v_arch_begin,now()) <= i_keepdays then
      insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
      values(i_table_source,@v_arch_begin,@v_arch_end,1,0,0,concat('error, all data in keey days, min ',i_fieldname,': ',@v_arch_begin));
  end if;
  /* 如果历史表所在的日期区间有数据,退出(需要手动排查原因) */
  if @v_his_num_before <> 0 then
      insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
      values(i_table_source,@v_arch_begin,@v_arch_end,2,0,0,concat('error, data exists,row num:',@v_his_num_before));
  end if;
  
  if (timestampdiff(day,@v_arch_begin,now()) > i_keepdays and @v_his_num_before = 0) then 
      set @mystmt = concat("insert into ",i_table_target," select * from ",i_table_source," where ",i_fieldname," >= ? and ",i_fieldname," < ? and ",i_other_cond);
      prepare stmt from @mystmt;
      execute stmt using @v_arch_begin,@v_arch_end;
      deallocate prepare stmt;
      /* 因为federated引擎不支持事务,数据insert后再select下记录数,与下面的delete记录数对比,相同则提交delete操作 */
      set @mystmt = concat("select count(*) into @v_his_num_after from ",i_table_target," where ",i_fieldname," >= ? and ",i_fieldname," < ?");
      prepare stmt from @mystmt;
      execute stmt using @v_arch_begin,@v_arch_end;
      deallocate prepare stmt;

      start transaction;

      set @mystmt = concat("delete from ",i_table_source," where ",i_fieldname," >= ? and ",i_fieldname," < ? and ",i_other_cond);
      prepare stmt from @mystmt;
      execute stmt using @v_arch_begin,@v_arch_end;
      set @v_del_num = row_count();
      deallocate prepare stmt;

      if @v_del_num = @v_his_num_after then
          commit;
          insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
          values(i_table_source,@v_arch_begin,@v_arch_end,0,@v_his_num_after,@v_del_num,'success');
      else
          rollback;
          insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)
          values(i_table_source,@v_arch_begin,@v_arch_end,3,@v_his_num_after,@v_del_num,'rollback, inserted rows num not equal to deleted rows num');
      end if;
    end if;
        
end;

 

存储过程proc_archive_pkg

      CREATE PROCEDURE `proc_archive_pkg`()
begin
call proc_archive(
                  'dborder.myorder_tab', -- tabel source
                  'myorder_tab_his',     -- table target
                  'create_time',         -- time field name
                  120,                   -- i_keepdays
                  1,                     -- i_archdays
                  '1=1'                  -- i_other_cond
                  );

end;

归档日志表记录

最后一步就是用zabbix监控这个表的状态了。

相关 [mysql 历史 数据] 推荐:

mysql历史数据自动归档_sdmei-CSDN博客_mysql 归档

- -
数据库跑一段时间后,因为查询性能、磁盘容量,运维管理等方面的原因,需要将在线数据挪到历史库(不同的服务器). 如我们的在线订单只留3个月数据,3个月以前的就需要到历史库查了. 自动归档常见的方式有pt-archiver,但我还是觉得自己写存储过程更靠谱. 在线库实例打开federated支持,创建数据库dborder(业务库), linkhis(归档用);.

MySQL存储过程-->通过游标遍历和异常处理迁移数据到历史表

- - CSDN博客数据库推荐文章
--  大表数据迁移 , 每天凌晨 1 点到 5 点执行 ,执行间隔时间10分钟, 迁移旧数据到历史表.    DECLARE EXIT HANDLER FOR SQLSTATE '02000'  /**包含游标not found*/.          SELECT1, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 游标执行正常结束.

mysql 数据分离

- - 数据库 - ITeye博客
网上看到一个读写分离的帖子,感觉不错. 构建高性能web之路------mysql读写分离实战(转). 一个完整的mysql读写分离环境包括以下几个部分:. 在本次实战中,应用程序client基于c3p0连接后端的database proxy. database proxy负责管理client实际访问database的路由策略,采用开源框架amoeba.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

同步mysql数据到hive

- - ITeye博客
地址为:http://archive.cloudera.com/cdh/3/下载相应版本,如sqoop-1.2.0-CDH3B4.tar.gz. 地址为:http://archive.cloudera.com/cdh/3/,版本可以为hadoop-0.20.2-CDH3B4.tar.gz. 3.解压 sqoop-1.2.0-CDH3B4.tar.gz ,hadoop-0.20.2-CDH3B4.tar.gz 到某目录如/home/hadoop/,解压后的目录为.

mysql数据整理命令

- - SQL - 编程语言 - ITeye博客
Analyze Table(修复索引). MySQL 的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了.

mysql 数据文件迁移

- - 数据库 - ITeye博客
1.新mysql存储路径:/data1/mysql/. 2.cp /var/lib/mysql到新存储路径:. 存储文件: cp -rp /var/lib/mysql /data1/mysql/data/. 日志文件: cp -rp /var/log/mysql /data1/mysql/log/.

excel数据导入mysql数据库

- - 互联网 - ITeye博客
1、excel另存为txt.       选中将要导出的数据列,然后另存为选择其它格式=>文本文件(制表符分割). E:\项目\fblike\game_code_san.txt. 2、txt导入到mysql数据库. load data infile 'E:\\项目\\fblike\\game_code_san.txt' into table game_code_san(code).

[MySQL] 生产环境MySQL数据库事务一直在RUNNING

- - CSDN博客数据库推荐文章
运营人员反映,有一单子提交卡住了,页面一直没有返回. 1,刚开始怀疑是应用服务器或者db压力过高hang住了,马上去check应用服务器以及db的负载,看起来都OK,蛮低的,应该不是DB性能问题. 2,最后去看下是否是表锁住了,查看到有2个事务一直RUNNING,没有结束. 3,通过trx_mysql_thread_id: 1662332的去查询information_schema.processlist找到执行事务的客户端请求的SQL线程.

Python3连接MySQL数据库之mysql-client - Ethan_zhang - 博客园

- -
要想使 python 可以操作 mysql 就需要 MySQLdb 驱动,它是 python 操作 mysql 必不可少的模块. 在此站点下载mysqlclient安装包:https://www.lfd.uci.edu/~gohlke/pythonlibs/# 进行本地安装. 以下是从这个网站上面检索到的mysqlclient的所有版本.