MYSQL 创建JOB 折腾过程

常见1045错误

在/etc/my.cnf 中mysqlid 添加:skip-grant-tables

# /etc/init.d/mysql stop  

/etc/init.d/mysql start
mysql> UPDATE user SET Password=PASSWORD(’newpassword’) where USER=’root’;  
mysql> FLUSH PRIVILEGES;  
mysql> quit  
# /etc/init.d/mysql restart  
# mysql -uroot -p  
Enter password: <输入新设的密码newpassword>  
mysql>  

 

登录时root无法登录 加个-h试试

mysql -uroot -p123456 -h127.0.0.1

 

开启JOB 功能

 在my.cnf添加mysqlid event_scheduler=ON

表链接的时候需要添加：federated参数

修改后：

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
federated
event_scheduler=ON
#skip-grant-tables

 

下面方法开启后，重启JOB功能又OFF了：

 set global event_scheduler=1;
查看JOB 是否开启用以下命令 

show global variables like "event_scheduler";

 

 

/*建表*/

CREATE TABLE maillog.test(aa VARCHAR(10),bb VARCHAR(10))

 

 

用SQLyog

/*建JOB*/

 

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create   

CREATE /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `maillog`.`test`

ON SCHEDULE
  /* uncomment the example below you want to use */

 -- scheduleexample 1: run once

    --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

 -- scheduleexample 2: run at intervals forever after creation

    -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

 -- scheduleexample 3: specified start time, end time and interval for execution
    /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]

    STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }

    ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */

/*[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']*/
EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP
DO
 BEGIN
     INSERT INTO  maillog.test VALUES('1','111');
     COMMIT;
 END$$

DELIMITER ;