iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 21
0
自我挑戰組

MariaDB 的使用與管理系列 第 21

DBA Daily Check Report 的製作 [4]

今天介紹的是 Event Status。

這個取得資料的方式,除了之前提到的擴充 Event 可以得到 Event 結果以外,就是系統的 information_schema.EVENTS 可以得到相關資訊。

DB, Event Name, Last Run Time

這些都能從 information_schema.EVENTS 拿 (event_schema, event_name, last_executed)。

Event 種類

Event 種類有 ENABLE, DISABLE, SLAVESIDE_DISABLED (Replication 時從 Master 傳來的 Event,不會在 Slave 執行),根據實際情形作標示。

CASE status 
  WHEN 'ENABLED' THEN '<div class="Healthy">Yes</div>' 
  WHEN 'SLAVESIDE_DISABLED' THEN '<div class="Healthy">No</div>' 
  ELSE '<div class="Warning">No</div>' 
END, 

Succeed

根據 master.eventinformation_schema.EVENTS 來 count,沒有 errno 的就是成功的。

CASE (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is null and start >= subdate(now(), @NumDays)) 
  WHEN 0 THEN CONCAT('<div class="Warning">', (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is null and start >= subdate(now(), @NumDays)), '</div>') 
  ELSE (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is null and start >= subdate(now(), @NumDays)) 
END,

Failed

根據 master.eventinformation_schema.EVENTS 來 count,有 errno 的就是失敗的。

CASE (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is not null and start >= subdate(now(), @NumDays)) 
  WHEN 0 THEN '<div class="Healthy">0</div>' 
  ELSE CONCAT('<div class="Critical">', (select count(*) from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME and errno is not null and start >= subdate(now(), @NumDays)),'</div>') 
END,

Next Run Time

根據 master.event 可以用算的,但是算的動作不能直接傳入 column 裡頭的值 (select date_add(colA, interval interval_value interval_field)...),所以寫成醜醜的 CASE

這個部份是筆者覺得最沒有自動化的地方。

case interval_field
  when "YEAR"	    then date_add(last_executed, interval interval_value YEAR)
  when "QUARTER"	then date_add(last_executed, interval interval_value QUARTER)
  when "MONTH"	    then date_add(last_executed, interval interval_value MONTH)
  when "DAY"	    then date_add(last_executed, interval interval_value DAY)
  when "HOUR"	    then date_add(last_executed, interval interval_value HOUR)
  when "MINUTE"	    then date_add(last_executed, interval interval_value MINUTE)
  when "WEEK"	    then date_add(last_executed, interval interval_value WEEK)
  when "SECOND"	    then date_add(last_executed, interval interval_value SECOND)
end,

(這個 interval 不是全部,筆者只有寫常用的)

Last Result

根據 master.eventinformation_schema.EVENTS 來看最後一次結果的內容。

(select CASE 
          WHEN errno is null THEN CONCAT('<span class="Healthy">', message_text, '</span>') 
          ELSE CONCAT('<span class="Critical">', message_text, '</span>') 
        END 
 from master.event_history where db = ev.EVENT_SCHEMA and name = ev.EVENT_NAME order by start limit 1),

上一篇
DBA Daily Check Report 的製作 [3]
下一篇
DBA Daily Check Report 的製作 [5]
系列文
MariaDB 的使用與管理30

尚未有邦友留言

立即登入留言