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> 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-2112938/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-2112938/