My sql event_scheduler_casual_slideshare__
-
Upload
tatsuro-hisamori -
Category
Documents
-
view
10.435 -
download
1
description
Transcript of My sql event_scheduler_casual_slideshare__
![Page 1: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/1.jpg)
MySQLEvent Scheduler
Casual Vol.1id:myfinder
![Page 2: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/2.jpg)
今回話すこと
Event Scheduler 機能を使うときのポイント
MySQL Event Scheduler Casual
![Page 3: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/3.jpg)
1. 機能の紹介2. 実際の用途と運用上の注意点
MySQL Event Scheduler Casual
![Page 4: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/4.jpg)
機能の紹介
MySQL Event Scheduler Casual
![Page 5: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/5.jpg)
Event Schduler とは
●指定した時刻に、指定した処理を行う機能。○cronのようなもの
MySQL Event Scheduler Casual
![Page 6: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/6.jpg)
使うには?
●MySQL 5.1以上必須●設定が必要
○default は無効
MySQL Event Scheduler Casual
![Page 7: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/7.jpg)
my.cnf
[mysql]event_scheduler = ON
MySQL Event Scheduler Casual
> SET GLOBAL event_scheduler = ON;
※ON/OFFは1/0でもOK
![Page 8: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/8.jpg)
有効になっている場合
MySQL Event Scheduler Casual
![Page 9: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/9.jpg)
イベントの登録
MySQL Event Scheduler Casual
● CREATE EVENT文で登録
CREATE EVENT log_do_event ON SCHEDULED AT '2011-08-09 19:00:00' DO INSERT INTO event_log (event_name, created_on) VALUES ('MySQL Casual', NOW() );
※2011/08/09 19時ちょうどに event_logテーブルにレコードをinsertする
![Page 10: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/10.jpg)
ステートメントを複数
MySQL Event Scheduler Casual
● DELIMITER を変えて BEGIN - ENDで指定すればOK
DELIMITER ||CREATE EVENT log_do_event ON SCHEDULE EVERY 1 DAY STARTS '2011-08-09 19:00:00'DO BEGIN INSERT INTO event_log(event_name, created_on) VALUES ('MySQL Casual', NOW()); UPDATE events SET done = 1 WHERE event_name = 'MySQL Casual';END;||DELIMITER ;
![Page 11: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/11.jpg)
スケジュールの指定
● AT 'YYYY-MM-DD HH:MM:SS'○ 指定した時間に1回限り
● EVERY 1 HOUR○ eventを登録してから一定間隔毎に延々繰り返す○ 時間以外にも分/日, 週なども指定可能
● EVERY 1 HOUR STARTS '時間' ENDS '時間'○ 開始時刻と終了時刻を指定できる○ 終了時刻指定は任意○ STARTS や ENDS には NOW() や CURRENT_TIMESTAMP
が指定できる○ ' + INTERVAL 15 SECOND' などしてもOK
![Page 12: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/12.jpg)
イベントの変更/削除/確認
MySQL Event Scheduler Casual
● ALTER EVENT文で変更
ALTER EVENT log_do_event DISABLE;● DROP EVENT文で削除
DROP EVENT log_do_event;
● 確認は下記2通り○ INFORMATION SCHEMA.EVENTS の内容○ use schema; SHOW EVENTS;
![Page 13: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/13.jpg)
レプリケーションとイベント
MySQL Event Scheduler Casual
● CREATE EVENT 文はレプリケーションされる○ スレーブ側でもイベントが登録されるが
「SLAVESIDE_DISABLED」設定になる○ マスタ側は「ENABLED」設定になっている
● mysqldumpで吐かれる設定はそれぞれの設定状況に従う○ マスタでdumpをとるとENABLEDに○ スレーブでとるとSLAVESIDE_DISABLEDになる
![Page 14: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/14.jpg)
詳しくは
●MySQLのマニュアル見て!●ググって!
MySQL Event Scheduler Casual
![Page 15: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/15.jpg)
実際の用途
MySQL Event Scheduler Casual
![Page 16: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/16.jpg)
用途(例
●partitioningした table の rotate○range partition table
● table から不要なデータを purge○range partition 以外の table
MySQL Event Scheduler Casual
※どちらも procedure を kick して実行している
![Page 17: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/17.jpg)
Eventでやればcrontabにスクリプト仕掛けないで済む\(^o^)/
MySQL Event Scheduler Casual
![Page 18: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/18.jpg)
となればいいんですがいろいろ注意点が。。。
MySQL Event Scheduler Casual
![Page 19: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/19.jpg)
運用上の注意点
MySQL Event Scheduler Casual
![Page 20: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/20.jpg)
運用上の注意点
●設定●日常の運用●障害復旧
MySQL Event Scheduler Casual
![Page 21: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/21.jpg)
●マスタ/スレーブともにONにしないと○ CREATE EVENTでこける○マスタ切替時にENABLEDにできない
●基本的にマスタでのみENABLEにする○でないとスレーブ側で2回イベントが走って
悲しいことが起こる○あるいはprocedure側で判定するか...
MySQL Event Scheduler Casual
![Page 22: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/22.jpg)
日常の運用
● 設定項目を監視すべき○ show variablesを監視(↓Nagios例)
※あとENABLED/SLAVESIDE_DISABLEDの状態も
MySQL Event Scheduler Casual
my @results = split /\n/, `$mysql -e "select @@event_scheduler\n"`;my $result = $results[1];if ($result ne 'ON') { print "[CRITICAL]$host: check_mysql_valiables - $value : $result"; exit 2;}
print qq([OK]$host: check_mysql_valiables - $result\n);exit 0;
![Page 23: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/23.jpg)
日常の運用
● Event が操作する対象も監視○ Range Partitionを監視(↓例)
※これが1未満ならpartitionが存在しない
MySQL Event Scheduler Casual
my $sth = $dbh->prepare(" select count(*) count from information_schema.PARTITIONS where TABLE_SCHEMA = ? and TABLE_NAME = ? and PARTITION_DESCRIPTION > (unix_timestamp(current_timestamp) + ?)");
![Page 24: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/24.jpg)
日常の運用
● Eventがちゃんと動いたか検知できない対策○ procedureを呼ぶ前と後にlogging
※partition監視が引っかかったらここを見る
DELIMITER //CREATE EVENT rotate_partition ON SCHEDULE EVERY 1 DAYDO BEGIN INSERT INTO rotate_log(table_name, status, created_on) VALUES('target_table', 'begin', NOW()); CALL add_partition(); CALL drop_partition(); INSERT INTO rotate_log(table_name, status, created_on) VALUES('target_table', 'end', NOW());END;//DELIMITER ;
![Page 25: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/25.jpg)
障害復旧
●スレーブをマスタに昇格させるとき○新マスタでENABLEDにする○旧マスタはSLAVESIDE_DISABLEDに
●スレーブを追加するとき○ SLVESIDE_DISABLEDか確認
MySQL Event Scheduler Casual
![Page 26: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/26.jpg)
まとめ
MySQL Event Scheduler Casual
![Page 27: My sql event_scheduler_casual_slideshare__](https://reader034.fdocument.pub/reader034/viewer/2022052505/55518f92b4c90580128b51f0/html5/thumbnails/27.jpg)
Event Schedulerは
● Event は正しく付き合えばいろいろできそう○ 既に安全に運用できる枠組みがあるならそっちでも...
● cron でも Event でも適切な監視は必須○ 適切に監視すれば Event も怖くないよ!!○ Event でやったほうが component 減らせていい(かも
MySQL Event Scheduler Casual