首页  »   MySQL

mysql鍒涘徐瀹氭椂浠诲姟

网友分享于:2013-12-18  浏览:0次
mysql鍒涘缓瀹氭椂浠诲姟

鏉ヨ嚜锛歨ttp://blog.csdn.net/mer1234567/article/details/7514855

涓€銆佸墠瑷€
銆€銆€鑷?MySQL5.1.6璧凤紝澧炲姞浜嗕竴涓潪甯告湁鐗硅壊鐨勫姛鑳解€撲簨浠惰皟搴﹀櫒(Event Scheduler)锛屽彲浠ョ敤鍋氬畾鏃舵墽琛屾煇浜涚壒瀹氫换鍔★紙渚嬪锛氬垹闄よ褰曘€佸鏁版嵁杩涜姹囨€荤瓑绛夛級锛屾潵鍙栦唬鍘熷厛鍙兘鐢辨搷浣滅郴缁熺殑璁″垝浠诲姟鏉ユ墽琛岀殑宸ヤ綔銆傛洿鍊煎緱 涓€鎻愮殑鏄疢ySQL鐨勪簨浠惰皟搴﹀櫒鍙互绮剧‘鍒版瘡绉掗挓鎵ц涓€涓换鍔★紝鑰屾搷浣滅郴缁熺殑璁″垝浠诲姟锛堝锛歀inux涓嬬殑CRON鎴朩indows涓嬬殑浠诲姟璁″垝锛夊彧鑳界簿 纭埌姣忓垎閽熸墽琛屼竴娆°€傚浜庝竴浜涘鏁版嵁瀹炴椂鎬ц姹傛瘮杈冮珮鐨勫簲鐢紙渚嬪锛氳偂绁ㄣ€佽禂鐜囥€佹瘮鍒嗙瓑锛夊氨闈炲父閫傚悎銆?br>銆€銆€浜嬩欢璋冨害鍣ㄦ湁鏃朵篃鍙О涓轰复鏃惰Е鍙戝櫒(temporal triggers)锛屽洜涓轰簨浠惰皟搴﹀櫒鏄熀浜庣壒瀹氭椂闂村懆鏈熻Е鍙戞潵鎵ц鏌愪簺浠诲姟锛岃€岃Е鍙戝櫒(Triggers)鏄熀浜庢煇涓〃鎵€浜х敓鐨勪簨浠惰Е鍙戠殑锛屽尯鍒篃灏卞湪杩欓噷銆?br>銆€銆€鍦ㄤ娇鐢ㄨ繖涓姛鑳戒箣鍓嶅繀椤荤‘淇漞vent_scheduler宸插紑鍚紝鍙墽琛?br>銆€銆€
銆€銆€SET GLOBAL event_scheduler = 1;
銆€銆€ ---鎴栨垜浠彲浠ュ湪閰嶇疆my.cnf鏂囦欢 涓姞涓?event_scheduler = 1
銆€銆€
銆€銆€鎴?br>銆€銆€
銆€銆€SET GLOBAL event_scheduler = ON;
銆€銆€
銆€銆€鏉ュ紑鍚紝涔熷彲浠ョ洿鎺ュ湪鍚姩鍛戒护鍔犱笂鈥?-event_scheduler=1鈥濓紝渚嬪锛?br>銆€銆€
銆€銆€mysqld ... --event_scheduler=1
銆€銆€
銆€銆€瑕佹煡鐪嬪綋鍓嶆槸鍚﹀凡寮€鍚簨浠惰皟搴﹀櫒锛屽彲鎵ц濡備笅SQL锛?br>銆€銆€
銆€銆€SHOW VARIABLES LIKE 'event_scheduler';
銆€銆€
銆€銆€鎴?br>銆€銆€
銆€銆€SELECT @@event_scheduler;
銆€銆€
銆€銆€鎴?br>銆€銆€
銆€銆€SHOW PROCESSLIST;
銆€銆€
銆€銆€浜屻€佸垱寤轰簨浠?CREATE EVENT)
銆€銆€鍏堟潵鐪嬩竴涓嬪畠鐨勮娉曪細
銆€銆€
銆€銆€

1 CREATEEVENT [IFNOTEXISTS] event_name
2 銆€銆€聽ONSCHEDULE schedule
3 銆€銆€ [ONCOMPLETION [NOT] PRESERVE]
4 銆€銆€ [ENABLE | DISABLE]
5 銆€銆€ [COMMENT聽'comment']
6 銆€銆€ DO sql_statement;


銆€銆€
銆€銆€schedule:
銆€銆€ AT TIMESTAMP [+ INTERVAL INTERVAL]
銆€銆€ | EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
銆€銆€
銆€銆€INTERVAL:
銆€銆€ quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
銆€銆€ WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
銆€銆€ DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
銆€銆€
銆€銆€1) 棣栧厛鏉ョ湅涓€涓畝鍗曠殑渚嬪瓙鏉ユ紨绀烘瘡绉掓彃鍏ヤ竴鏉¤褰曞埌鏁版嵁琛?br>銆€銆€
銆€銆€

1 USE test;
2 銆€銆€CREATETABLEaaa (timeline聽TIMESTAMP);
3 銆€銆€CREATEEVENT e_test_insert
4 銆€銆€聽ONSCHEDULE EVERY 1聽SECOND
5 銆€銆€ DO聽INSERTINTOtest.aaa聽VALUES(CURRENT_TIMESTAMP);


銆€銆€
銆€銆€绛夊緟3绉掗挓鍚庯紝鍐嶆墽琛屾煡璇㈢湅鐪嬶細
銆€銆€
銆€銆€mysql> SELECT * FROM aaa;
銆€銆€+---------------------+
銆€銆€| timeline |
銆€銆€+---------------------+
銆€銆€| 2007-07-18 20:44:26 |
銆€銆€| 2007-07-18 20:44:27 |
銆€銆€| 2007-07-18 20:44:28 |
銆€銆€+---------------------+
銆€銆€
銆€銆€2) 5澶╁悗娓呯┖test琛細
銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE聽ATCURRENT_TIMESTAMP+ INTERVAL 5聽DAY
3 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€3) 2007骞?鏈?0鏃?2鐐规暣娓呯┖test琛細
銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE聽ATTIMESTAMP'2007-07-20 12:00:00'
3 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€4) 姣忓ぉ瀹氭椂娓呯┖test琛細
銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE EVERY 1聽DAY
3 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€5) 5澶╁悗寮€鍚瘡澶╁畾鏃舵竻绌簍est琛細
銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE EVERY 1聽DAY
3 銆€銆€ STARTS聽CURRENT_TIMESTAMP+ INTERVAL 5聽DAY
4 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€6) 姣忓ぉ瀹氭椂娓呯┖test琛紝5澶╁悗鍋滄鎵ц锛?br>銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE EVERY 1聽DAY
3 銆€銆€ ENDS聽CURRENT_TIMESTAMP+ INTERVAL 5聽DAY
4 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€7) 5澶╁悗寮€鍚瘡澶╁畾鏃舵竻绌簍est琛紝涓€涓湀鍚庡仠姝㈡墽琛岋細
銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE EVERY 1聽DAY
3 銆€銆€ STARTS聽CURRENT_TIMESTAMP+ INTERVAL 5聽DAY
4 銆€銆€ ENDS聽CURRENT_TIMESTAMP+ INTERVAL 1聽MONTH
5 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€[ON COMPLETION [NOT] PRESERVE]鍙互璁剧疆杩欎釜浜嬩欢鏄墽琛屼竴娆¤繕鏄寔涔呮墽琛岋紝榛樿涓篘OT PRESERVE銆?br>銆€銆€8) 姣忓ぉ瀹氭椂娓呯┖test琛?鍙墽琛屼竴娆★紝浠诲姟瀹屾垚鍚庡氨缁堟璇ヤ簨浠?锛?br>銆€銆€
銆€銆€

1 CREATEEVENT e_test
2 銆€銆€聽ONSCHEDULE EVERY 1聽DAY
3 銆€銆€聽ONCOMPLETION聽NOTPRESERVE
4 銆€銆€ DO聽TRUNCATETABLEtest.aaa;


銆€銆€
銆€銆€[ENABLE | DISABLE]鍙槸璁剧疆璇ヤ簨浠跺垱寤哄悗鐘舵€佹槸鍚﹀紑鍚垨鍏抽棴锛岄粯璁や负ENABLE銆?br>銆€銆€[COMMENT 鈥榗omment鈥橾鍙互缁欒浜嬩欢鍔犱笂娉ㄩ噴銆?br>銆€銆€涓夈€佷慨鏀逛簨浠?ALTER EVENT)
銆€銆€
銆€銆€

1 ALTEREVENT event_name
2 銆€銆€ [ONSCHEDULE schedule]
3 銆€銆€ [RENAME聽TOnew_event_name]
4 銆€銆€ [ONCOMPLETION [NOT] PRESERVE]
5 銆€銆€ [COMMENT聽'comment']
6 銆€銆€ [ENABLE | DISABLE]
7 銆€銆€ [DO sql_statement]


銆€銆€
銆€銆€1) 涓存椂鍏抽棴浜嬩欢
銆€銆€
銆€銆€ALTER EVENT e_test DISABLE;
銆€銆€
銆€銆€2) 寮€鍚簨浠?br>銆€銆€
銆€銆€ALTER EVENT e_test ENABLE;
銆€銆€
銆€銆€3) 灏嗘瘡澶╂竻绌簍est琛ㄦ敼涓?澶╂竻绌轰竴娆★細
銆€銆€
銆€銆€ALTER EVENT e_test
銆€銆€ ON SCHEDULE EVERY 5 DAY;
銆€銆€
銆€銆€鍥涖€佸垹闄や簨浠?DROP EVENT)
銆€銆€璇硶寰堢畝鍗曪紝濡備笅鎵€绀猴細
銆€銆€
銆€銆€DROP EVENT [IF EXISTS] event_name
銆€銆€
銆€銆€渚嬪鍒犻櫎鍓嶉潰鍒涘缓鐨別_test浜嬩欢
銆€銆€
銆€銆€DROP EVENT e_test;
銆€銆€
銆€銆€褰撶劧鍓嶆彁鏄繖涓簨浠跺瓨鍦紝鍚﹀垯浼氫骇鐢烢RROR 1513 (HY000): Unknown event閿欒锛屽洜姝ゆ渶濂藉姞涓奍F EXISTS
銆€銆€
銆€銆€DROP EVENT IF EXISTS e_test;
銆€銆€娉ㄦ剰锛氬鏋滀綘灏唀vent鎵ц浜咥lter event event_name disable.閭d箞褰撲綘閲嶆柊鍚姩mysql鏈嶅姟
銆€銆€鍣ㄥ悗锛岃event灏嗚鍒犻櫎锛堟祴璇曠増鏈細5.1.30锛?br>銆€銆€
銆€銆€搴旂敤妗堜緥
銆€銆€鏈渚嬫槸鍒╃敤 event scheduler 鐨勭壒鎬э紝姣忕閽熻皟鐢ㄤ竴娆″瓨鍌ㄨ繃绋嬶紝鐢ㄤ簬鍒ゆ柇 SLAVE 鏄惁姝e父杩愯锛屽鏋滃彂鐜?SLAVE 鍏抽棴浜嗭紝蹇界暐 0 娆¢敊璇紝鐒跺悗閲嶆柊鍚姩 SLAVE銆?br>銆€銆€
銆€銆€ * 棣栧厛鍒涘缓瀛樺偍杩囩▼
銆€銆€
銆€銆€

01 delimiter //
02 銆€銆€聽createprocedure`Slave_Monitor`()
03 銆€銆€聽begin
04 銆€銆€聽SELECTVARIABLE_VALUE聽INTO@SLAVE_STATUS
05 銆€銆€聽FROMinformation_schema.GLOBAL_STATUS
06 銆€銆€聽WHEREVARIABLE_NAME='SLAVE_RUNNING';
07 銆€銆€ IF ('ON'!= @SLAVE_STATUS)聽THEN
08 銆€銆€聽SETGLOBALSQL_SLAVE_SKIP_COUNTER=0;
09 銆€銆€ SLAVE START;
10 銆€銆€聽ENDIF;
11 銆€銆€聽end; //
12 銆€銆€ delimiter ;


銆€銆€
銆€銆€ 鐢变簬瀛樺偍杩囩▼涓棤娉曡皟鐢ㄧ被浼?SHOW SLAVE STATUS 杩欐牱鐨勮鍙ワ紝鍥犳鏃犳硶寰楀埌纭垏鐨勫鍒堕敊璇俊鎭拰閿欒浠g爜锛屼笉鑳借繘涓€姝ョ殑澶勭悊 SLAVE 鍋滄鐨勫悇绉嶆儏鍐点€?br>銆€銆€ * 鎺ョ潃锛屽垱寤轰换鍔?br>銆€銆€
銆€銆€

1 CREATEEVENT IFNOTEXISTS `Slave_Monitor`
2 銆€銆€聽ONSCHEDULE EVERY 5聽SECOND
3 銆€銆€聽ONCOMPLETION PRESERVE
4 銆€銆€ DO
5 銆€銆€ CALL Slave_Monitor();


銆€銆€
銆€銆€
銆€銆€ 鍒涘缓浜嗕竴涓换鍔★紝姣?5绉掗挓鎵ц涓€娆★紝浠诲姟缁撴潫鍚庝緷鏃т繚鐣欒浠诲姟锛岃€屼笉鏄垹闄ゃ€傚綋鐒朵簡锛屽湪鏈緥涓殑浠诲姟涓嶄細缁撴潫锛岄櫎闈炲皢瀹冩墜鍔ㄧ姝簡銆?br>銆€銆€ *
銆€銆€ 濡傛灉鍦ㄨ繍琛屼腑鎯宠涓存椂鍏抽棴涓€涓嬫煇涓换鍔★紝鎵ц ALTER EVENT 璇彞鍗冲彲锛?br>銆€銆€
銆€銆€ (root:localhost:)test> alter event `Slave_Monitor` ON
銆€銆€ COMPLETION PRESERVE DISABLE;
銆€銆€ (root:localhost:)test> alter event `Slave_Monitor` ON
銆€銆€ COMPLETION PRESERVE ENABLE;

=================================================================================

鏌ョ湅浜嬩欢

SHOW EVENTS

select * from聽mysql.event

鏌ョ湅鏄惁寮€鍚畾鏃跺櫒

SHOW VARIABLES LIKE 'event_scheduler';

寮€鍚畾鏃跺櫒 0锛歰ff 1锛歰n

SET GLOBAL event_scheduler = 1; .

鍒犻櫎

drop 聽EVENT *_event;

鍒涘缓

CREATE EVENT IF NOT EXISTS *_event_test

ON SCHEDULE 聽every 1 Hour

DO CALL update_*_proc();聽

鎴栬€?/p>

CREATE EVENT IF NOT EXISTS update_*_event

ON SCHEDULE 聽every 1 DAY STARTS TIMESTAMP(CURRENT_DATE,'00:05:00')

ON COMPLETION PRESERVE ENABLE

DO CALL update_*_proc();聽

date_add(date(curdate() + 1),interval 8 hour)聽

鏌ョ湅event鏄惁寮€鍚?: SHOW VARIABLES聽LIKE'%event_sche%';
灏嗕簨浠惰鍒掑紑鍚?:聽SETGLOBALevent_scheduler = 1;
灏嗕簨浠惰鍒掑叧闂?:聽SETGLOBALevent_scheduler = 0;
鍏抽棴浜嬩欢浠诲姟 :聽ALTEREVENT eventName聽ONCOMPLETION PRESERVE DISABLE;
寮€鍚簨浠朵换鍔?:聽ALTEREVENT eventName聽ONCOMPLETION PRESERVE ENABLE;
鏌ョ湅浜嬩欢浠诲姟 : SHOW EVENTS ;

相关解决方案

最新解决方案