• 博客访问: 607649
  • 博文数量: 290
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-01 11:01
  • 认证徽章:
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(290)

文章存档

2016年(5)

2013年(1)

2012年(6)

2011年(7)

2010年(1)

2009年(12)

2008年(67)

2007年(142)

2006年(21)

2005年(28)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: MySQL

做为DBA,必须要做的事,就是要最大可能的降低运维的成本
尤其使用mysql,在按照业务分片以后,mysql数据库会很多,那么如何使用自动脚本,将日常运维的工作量降到最小
本文将从mysql表分区的自动维护的角度来出发,即表分区的自动创建(按天),过期数据自动清理等
还是那句话,不喜勿喷谢谢!

1、建表语句
    这里说一点,mysql的表分区比较特殊,list、range类型分区不支持varchar类型字段,这点对于一个oracle DBA来说,突然就觉得mysql有点太low了
    好在mysql中支持一种分区类型COLUMNS,即组合分区,组合分区可是使用varchar,虽说有点突兀了,感觉有点乱(是不是老版本迭代,已放弃了传统分区模式的原因啊)
    但好在能用,好,就选COLUMNS

    CREATE TABLE TLOG
(
  INSTANCE_ID    VARCHAR(20)       NOT NULL,
  TRANSIDC       VARCHAR(64)       NOT NULL,
  CONVID         VARCHAR(64)       NOT NULL,
  CUTOFFDAY      VARCHAR(8)        NOT NULL,
  ...  
  HPARTY_TIME    DATETIME(6),                   
  REQ_RCV_TIME   DATETIME(6)          DEFAULT current_timestamp(6)     NOT NULL,      
  FILE_PATH      VARCHAR(2000)
)PARTITION BY LIST COLUMNS (CUTOFFDAY)(
  PARTITION TLOG_20160514 VALUES IN ('20160514'),
  PARTITION TLOG_20160515 VALUES IN ('20160515'),
  PARTITION TLOG_20160516 VALUES IN ('20160516')
);
ALTER TABLE TLOG 
ADD INDEX tlog_index (CUTOFFDAY,OPARTY_ID ,TRANSIDO);
ALTER TABLE TLOG
ADD PRIMARY KEY (CONVID, CUTOFFDAY);


CREATE TABLE TMSG
(
  CONVID         VARCHAR(64)  NOT NULL,
  ACTION_CODE    VARCHAR(1)   NOT NULL, 
  CUTOFFDAY      VARCHAR(8)   NOT NULL, 
  MSG_HEAD       MEDIUMTEXT ,      
  BMC_STATUS     VARCHAR(1)   
)PARTITION BY LIST COLUMNS (CUTOFFDAY)(
  PARTITION TMSG_20160514 VALUES IN ('20160514'),
  PARTITION TMSG_20160515 VALUES IN ('20160515'),
  PARTITION TMSG_20160516 VALUES IN ('20160516')
);
ALTER TABLE TMSG 
ADD PRIMARY KEY (CONVID, ACTION_CODE, CUTOFFDAY);

二、自动创建表分区的存储过程
这里的逻辑是查询information_schema.partitions的到表分区相关信息,然后分区名是按照表名+日期组合的特性,进行更新
每次执行时拿到最大的分区名称,和当前时间比,最终新分区创建到当前时间往后一周
这里用到了游标、循环、HANDLER(可以做异常处理相关逻辑)、动态sql等
对sql开发有爱好的人,可以读读(这里,个人一直认为:sql是DBA的基础,如果SQL都写不好的DBA,不算一个合格的DBA)
好了,不絮叨
delimiter //
drop procedure if exists auto_add_partition;
CREATE PROCEDURE auto_add_partition()
####################################################
#auto create partitions for "LIST COLUMNS" partition
#by Eric.zhaoxt
#date:20160523
#database:zxt--need to be modified at line "TABLE_SCHEMA ='zxt'"
####################################################
begin
  DECLARE v_date_final varchar(8);
  DECLARE v_user varchar(32);
  DECLARE v_tabname varchar(32);
  DECLARE v_max_partname varchar(32);
  DECLARE v_fromdate varchar(8);
  DECLARE v_sql varchar(500);
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_cur CURSOR FOR select TABLE_SCHEMA,table_name,max(PARTITION_NAME) from information_schema.partitions t where TABLE_SCHEMA ='zxt' and t.PARTITION_NAME like CONCAT(t.table_name,'%') and t.PARTITION_METHOD='LIST COLUMNS' group by TABLE_SCHEMA,table_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  select date_format(DATE_ADD(sysdate(),INTERVAL 7 DAY),'%Y%m%d') into v_date_final;

  OPEN v_cur;
  read_loop: LOOP
  FETCH v_cur INTO v_user,v_tabname,v_max_partname;
  IF done THEN
      LEAVE read_loop;
  END IF;

  select substr(v_max_partname,length(v_tabname)+2) into v_fromdate;

  while v_fromdate<v_date_final do

  select date_format(DATE_ADD(str_to_date(v_fromdate,'%Y%m%d'),INTERVAL 1 DAY),'%Y%m%d') into v_fromdate;
  SET v_sql=CONCAT_ws('', 'alter table ',v_user,'.',v_tabname,' add PARTITION ( partition ',v_tabname,'_',v_fromdate,' VALUES IN(''',v_fromdate,'''))');
#select v_sql;
  set @v_sql=v_sql;
  prepare stmt from @v_sql;
  EXECUTE stmt;
  deallocate prepare stmt;

  end while;

  END LOOP;
  CLOSE v_cur;
end
//
delimiter ;

存储过程以//开始,以//结束,这个,写惯了oracle的存储过程,这么写mysql的,还挺好玩的....

3、自动job
mysql的自动job是用event来实现的,介个...我确实有点凌乱了
如下是每天晚上1点执行的job
delimiter //
create event event_auto_add_partition  
on schedule every 1 day starts date_add(date(curdate() + 1),interval 1 hour)
on completion preserve enable
do
begin
    call zxt.auto_add_partition();
end
//
delimiter ;


4、测试一把哈

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
+--------------------+
5 rows in set (0.36 sec)


mysql> use zxt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> 
mysql> 
mysql> 
mysql> show tables;
+---------------+
| Tables_in_zxt |
+---------------+
| TLOG          |
| TMSG          |
| customers_1   |
| zxt           |
+---------------+
4 rows in set (0.00 sec)


mysql> drop table TLOG;
Query OK, 0 rows affected (0.12 sec)


mysql> drop table TMSG;
Query OK, 0 rows affected (0.14 sec)


mysql> CREATE TABLE TLOG
    -> (
    ->   INSTANCE_ID    VARCHAR(20)       NOT NULL,
    ->   TRANSIDC       VARCHAR(64)       NOT NULL,
    ->   CONVID         VARCHAR(64)       NOT NULL,
    ->   CUTOFFDAY      VARCHAR(8)        NOT NULL,
    ->   BIP_ID         VARCHAR(8)        NOT NULL,
    ...
    ->   RSP_FWD_TIME   DATETIME(6),    
    ->   FILE_PATH      VARCHAR(2000)
    -> )PARTITION BY LIST COLUMNS (CUTOFFDAY)(
    ->   PARTITION TLOG_20160514 VALUES IN ('20160514'),
    ->   PARTITION TLOG_20160515 VALUES IN ('20160515'),
    ->   PARTITION TLOG_20160516 VALUES IN ('20160516')
    -> );
Query OK, 0 rows affected (0.06 sec)


mysql> ALTER TABLE TLOG 
    -> ADD INDEX `tlog_index` (`CUTOFFDAY`,`OPARTY_ID` ,`TRANSIDO`);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE TLOG
    -> ADD PRIMARY KEY (`CONVID`, `CUTOFFDAY`);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> 
mysql> 
mysql> 
mysql> CREATE TABLE TMSG
    -> (
    ->   CONVID         VARCHAR(64)  NOT NULL,
    ->   ACTION_CODE    VARCHAR(1)   NOT NULL, 
    ->   CUTOFFDAY      VARCHAR(8)   NOT NULL, 
...    
    ->   BMC_STATUS     VARCHAR(1)   
    -> )PARTITION BY LIST COLUMNS (CUTOFFDAY)(
    ->   PARTITION TMSG_20160514 VALUES IN ('20160514'),
    ->   PARTITION TMSG_20160515 VALUES IN ('20160515'),
    ->   PARTITION TMSG_20160516 VALUES IN ('20160516')
    -> );
Query OK, 0 rows affected (0.05 sec)


mysql> ALTER TABLE TMSG 
    -> ADD PRIMARY KEY (`CONVID`, `ACTION_CODE`, `CUTOFFDAY`);
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> 
mysql> select TABLE_SCHEMA,table_name,PARTITION_NAME from information_schema.partitions t where t.TABLE_SCHEMA='zxt';
+--------------+-------------+----------------+
| TABLE_SCHEMA | table_name  | PARTITION_NAME |
+--------------+-------------+----------------+
| zxt          | TLOG        | TLOG_20160514  |
| zxt          | TLOG        | TLOG_20160515  |
| zxt          | TLOG        | TLOG_20160516  |
| zxt          | TMSG        | TMSG_20160514  |
| zxt          | TMSG        | TMSG_20160515  |
| zxt          | TMSG        | TMSG_20160516  |
| zxt          | customers_1 | pRegion_1      |
| zxt          | customers_1 | pRegion_2      |
| zxt          | customers_1 | pRegion_3      |
| zxt          | customers_1 | pRegion_4      |
| zxt          | zxt         | NULL           |
+--------------+-------------+----------------+
11 rows in set (0.00 sec)


mysql> call auto_add_partition();
Query OK, 0 rows affected (4.89 sec)


mysql> select TABLE_SCHEMA,table_name,PARTITION_NAME from information_schema.partitions t where t.TABLE_SCHEMA='zxt';
+--------------+-------------+----------------+
| TABLE_SCHEMA | table_name  | PARTITION_NAME |
+--------------+-------------+----------------+
| zxt          | TLOG        | TLOG_20160514  |
| zxt          | TLOG        | TLOG_20160515  |
| zxt          | TLOG        | TLOG_20160516  |
| zxt          | TLOG        | TLOG_20160517  |
| zxt          | TLOG        | TLOG_20160518  |
| zxt          | TLOG        | TLOG_20160519  |
| zxt          | TLOG        | TLOG_20160520  |
| zxt          | TLOG        | TLOG_20160521  |
| zxt          | TLOG        | TLOG_20160522  |
| zxt          | TLOG        | TLOG_20160523  |
| zxt          | TLOG        | TLOG_20160524  |
| zxt          | TLOG        | TLOG_20160525  |
| zxt          | TLOG        | TLOG_20160526  |
| zxt          | TLOG        | TLOG_20160527  |
| zxt          | TLOG        | TLOG_20160528  |
| zxt          | TLOG        | TLOG_20160529  |
| zxt          | TLOG        | TLOG_20160530  |
| zxt          | TLOG        | TLOG_20160531  |
| zxt          | TLOG        | TLOG_20160601  |
| zxt          | TLOG        | TLOG_20160602  |
| zxt          | TLOG        | TLOG_20160603  |
| zxt          | TLOG        | TLOG_20160604  |
| zxt          | TLOG        | TLOG_20160605  |
| zxt          | TLOG        | TLOG_20160606  |
| zxt          | TLOG        | TLOG_20160607  |
| zxt          | TLOG        | TLOG_20160608  |
| zxt          | TLOG        | TLOG_20160609  |
| zxt          | TMSG        | TMSG_20160514  |
| zxt          | TMSG        | TMSG_20160515  |
| zxt          | TMSG        | TMSG_20160516  |
| zxt          | TMSG        | TMSG_20160517  |
| zxt          | TMSG        | TMSG_20160518  |
| zxt          | TMSG        | TMSG_20160519  |
| zxt          | TMSG        | TMSG_20160520  |
| zxt          | TMSG        | TMSG_20160521  |
| zxt          | TMSG        | TMSG_20160522  |
| zxt          | TMSG        | TMSG_20160523  |
| zxt          | TMSG        | TMSG_20160524  |
| zxt          | TMSG        | TMSG_20160525  |
| zxt          | TMSG        | TMSG_20160526  |
| zxt          | TMSG        | TMSG_20160527  |
| zxt          | TMSG        | TMSG_20160528  |
| zxt          | TMSG        | TMSG_20160529  |
| zxt          | TMSG        | TMSG_20160530  |
| zxt          | TMSG        | TMSG_20160531  |
| zxt          | TMSG        | TMSG_20160601  |
| zxt          | TMSG        | TMSG_20160602  |
| zxt          | TMSG        | TMSG_20160603  |
| zxt          | TMSG        | TMSG_20160604  |
| zxt          | TMSG        | TMSG_20160605  |
| zxt          | TMSG        | TMSG_20160606  |
| zxt          | TMSG        | TMSG_20160607  |
| zxt          | TMSG        | TMSG_20160608  |
| zxt          | TMSG        | TMSG_20160609  |
| zxt          | customers_1 | pRegion_1      |
| zxt          | customers_1 | pRegion_2      |
| zxt          | customers_1 | pRegion_3      |
| zxt          | customers_1 | pRegion_4      |
| zxt          | zxt         | NULL           |
+--------------+-------------+----------------+
59 rows in set (0.13 sec)


mysql> 
阅读(639) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册