MySQL 5.1ÐÂÌØÐÔ֮ʼþµ÷¶ÈÆ÷(Event Scheduler)
À´×Ô£ºhttp://www.sofee.cn/blog/2007/07/18/91/
MySQL 5.1ÐÂÌØÐÔ֮ʼþµ÷¶ÈÆ÷(Event Scheduler)
CREATE-EVENT, cron, Event-Scheduler, ´¥·¢Æ÷, ¼Æ»®ÈÎÎñ, MySQL,Triggers, ʼþµ÷¶ÈÆ÷, ×÷Òµµ÷¶È
July 18, 2007 at 20:34:50 · Filed under MySQL
Ò»¡¢Ç°ÑÔ
×ÔMySQL5.1.6Æð£¬Ôö¼ÓÁËÒ»¸ö·Ç³£ÓÐÌØÉ«µÄ¹¦Äܖʼþµ÷¶ÈÆ÷(Event Scheduler)£¬¿ÉÒÔÓÃ×ö¶¨Ê±Ö´ÐÐÄ³Ð©ÌØ¶¨ÈÎÎñ£¨ÀýÈ磺ɾ³ý¼Ç¼¡¢¶ÔÊý¾Ý½øÐлã×ܵȵȣ©£¬À´È¡´úÔÏÈÖ»ÄÜÓɲÙ×÷ϵͳµÄ¼Æ»®ÈÎÎñÀ´Ö´ÐеŤ×÷¡£¸üÖµµÃÒ»ÌáµÄÊÇMySQLµÄʼþµ÷¶ÈÆ÷¿ÉÒÔ¾«È·µ½Ã¿ÃëÖÓÖ´ÐÐÒ»¸öÈÎÎñ£¬¶ø²Ù×÷ϵͳµÄ¼Æ»®ÈÎÎñ£¨È磺LinuxϵÄCRON»òWindowsϵÄÈÎÎñ¼Æ»®£©Ö»Äܾ«È·µ½Ã¿·ÖÖÓÖ´ÐÐÒ»´Î¡£¶ÔÓÚһЩ¶ÔÊý¾ÝʵʱÐÔÒªÇó±È½Ï¸ßµÄÓ¦Óã¨ÀýÈ磺¹ÉƱ¡¢ÅâÂÊ¡¢±È·ÖµÈ£©¾Í·Ç³£Êʺϡ£
ʼþµ÷¶ÈÆ÷ÓÐʱҲ¿É³ÆÎªÁÙʱ´¥·¢Æ÷(temporal triggers)£¬ÒòΪʼþµ÷¶ÈÆ÷ÊÇ»ùÓÚÌØ¶¨Ê±¼äÖÜÆÚ´¥·¢À´Ö´ÐÐijЩÈÎÎñ£¬¶ø´¥·¢Æ÷(Triggers)ÊÇ»ùÓÚij¸ö±íËù²úÉúµÄʼþ´¥·¢µÄ£¬Çø±ðÒ²¾ÍÔÚÕâÀï¡£
ÔÚʹÓÃÕâ¸ö¹¦ÄÜ֮ǰ±ØÐëÈ·±£event_schedulerÒÑ¿ªÆô£¬¿ÉÖ´ÐÐ
SET GLOBAL event_scheduler = 1;
»ò
SET GLOBAL event_scheduler = ON;
À´¿ªÆô£¬Ò²¿ÉÒÔÖ±½ÓÔÚÆô¶¯ÃüÁî¼ÓÉÏ“–event_scheduler=1”£¬ÀýÈ磺
mysqld ... --event_scheduler=1
Òª²é¿´µ±Ç°ÊÇ·ñÒÑ¿ªÆôʼþµ÷¶ÈÆ÷£¬¿ÉÖ´ÐÐÈçÏÂSQL£º
SHOW VARIABLES LIKE 'event_scheduler';
»ò
SELECT @@event_scheduler;
»ò
SHOW PROCESSLIST;
¶þ¡¢´´½¨Ê¼þ(CREATE EVENT)
ÏÈÀ´¿´Ò»ÏÂËüµÄÓï·¨£º
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
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) Ê×ÏÈÀ´¿´Ò»¸ö¼òµ¥µÄÀý×ÓÀ´ÑÝʾÿÃë²åÈëÒ»Ìõ¼Ç¼µ½Êý¾Ý±í
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INS
Ïà¹ØÎĵµ£º
ÒÔÏÂËùÓÐÃüÁî¶¼ÊÇÔÚ½øÈëmysql¼à¿ØÆ÷ÖÐÖ´Ðеģº
a. show tables»òshow tables from database_name; // ÏÔʾµ±Ç°Êý¾Ý¿âÖÐËùÓбíµÄÃû³Æ
b. show databases; // ÏÔʾmysqlÖÐËùÓÐÊý¾Ý¿âµÄÃû³Æ
c. show columns from table_name from database_name; »òshow columns from databa ......
ͨ¹ý·ÖÇø£¨Partition£©ÌáÉýMySQLÐÔÄÜ
×÷Õߣºfoxcai À´Ô´:foxcai µÄ Blog (2006-05-08 14:30:34)
ͨ¹ý·ÖÇø£¨Partition£©ÌáÉýMySQLÐÔÄÜ
——MySQL5.1ÐÂÌØÐÔ·ÒëϵÁÐ
¼¸Äêǰ£¬°³Ð´¹ýһƪÌâΪ“The Founda ......
innodb_buffer_pool_size
Èç¹ûÓÃInnodb£¬ÄÇôÕâÊÇÒ»¸öÖØÒª±äÁ¿¡£Ïà¶ÔÓÚMyISAMÀ´Ëµ£¬Innodb¶ÔÓÚbuffer size¸üÃô¸Ð¡£MySIAM¿ÉÄܶÔÓÚ´óÊý¾ÝÁ¿Ê¹ÓÃĬÈϵÄkey_buffer_sizeÒ²»¹ºÃ£¬µ«InnodbÔÚ´óÊý¾ÝÁ¿Ê±ÓÃĬÈÏÖµ¾Í¸Ð¾õÔÚÅÀÁË¡£ InnodbµÄ»º³å³Ø»á»º´æÊý¾ÝºÍË÷Òý£¬ËùÒÔ²»ÐèÒª¸øÏµÍ³µÄ»º´æÁô¿Õ¼ä£¬Èç¹ûÖ»ÓÃInnodb£¬¿ÉÒÔ°ÑÕâ¸öÖµÉèΪ ......
ÔÚ¶Ômysql½øÐе÷ÊÔµÄʱºò£¬ÓÉÓÚmysql´æÔÚquery»º´æ£¬³£³£²»ÄÜ·´Ó³ÕæÊµÇé¿ö¡£
ÓÐʱºò£¬ÓÐûÓÐʹÓÃFLUSH
QUERY
CACHEµÄȨÏÞ£»
ÔÚ query_cache_type ´ò¿ªµÄÇé¿öÏ£¬Èç¹ûÄã²»ÏëʹÓûº´æ£¬ÐèÒªÖ¸Ã÷
select sql_no_cache id,name from tableName;
»òÕß»¹Óиü¼òµ¥µÄ°ì·¨£¬ÐÞ¸ÄÒ»ÏÂsqlÀïij¸ö×Öĸµ ......
Unix&Linux£º
1.ÓÃroot»òÕßÔËÐÐmysqldµÄÓû§µÇ¼ϵͳ£»
2£®ÀûÓÃkillÃüÁî½áÊøµômysqldµÄ½ø³Ì£»
3£®Ê¹ÓÃ--skip-grant-tables²ÎÊýÆô¶¯MySQL Server
shell>mysqld_safe --skip-grant-tables &
4.Ϊroot@localhostÉèÖÃÐÂÃÜÂë
shell>mysqladmin -u root flush-privileges password "newpassword" ......