Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4826816
  • 博文数量: 971
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 12717
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(971)

文章存档

2019年(62)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Mysql/postgreSQL

2014-03-10 14:42:31


1.检查当前的job
mysql> use dev;
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> show events;
+-----+-------------+------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db  | Name        | Definer          | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+-----+-------------+------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| dev | my_job_test | hxl@192.168.56.% | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2014-03-07 18:10:00 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----+-------------+------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.23 sec)


2.检查是否开启了自动启动
mysql> show global variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)


3.设置job自动启动
mysql> set global event_scheduler = on; 
Query OK, 0 rows affected (0.00 sec)

4.设置mysql重启后,job能自动启动
编辑/etc/my.cnf文件,在[mysqld]下添加event_scheduler=1
[root@node1 etc]# more my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
event_scheduler=1


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 

5.创建events

CREATE
 DEFINER = 'root'@'192.168.50.%'
EVENT hxl.e_test
 ON SCHEDULE EVERY '1' MINUTE
 STARTS '2016-03-07 14:18:03'
 ON COMPLETION PRESERVE
 DO
call sp_mytest()

--每分钟执行一次


相应的存储过程

CREATE DEFINER = 'root'@'192.168.50.%'
PROCEDURE hxl.sp_mytest()
BEGIN

  DECLARE l_error_flag int;
  DECLARE ln_runtype int;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET l_error_flag = 1;
 
  INSERT INTO job_log(remark) VALUES('mytest');

END


表结构



CREATE TABLE hxl.job_log (
  job_id int(11) NOT NULL AUTO_INCREMENT,
  remark varchar(255) DEFAULT NULL,
  createtime datetime DEFAULT 'CURRENT_TIMESTAMP',
  PRIMARY KEY (job_id)
)
ENGINE = INNODB
AUTO_INCREMENT = 62
AVG_ROW_LENGTH = 268
CHARACTER SET utf8
COLLATE utf8_general_ci;

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

登录 注册