Zabbix MySQL Database partitioning helper script

zabbix_partition_builder

#!/usr/bin/env ruby

require 'time'
require 'activesupport' # install with: gem install 'activesupport'

exit 1 if ARGV.size == 0

def build_history(t,table)
  result = "ALTER TABLE `#{table}` PARTITION BY RANGE ( clock )\n("
  while t < Time.now do
    n = t + 86400
    result << %[PARTITION p#{ t.to_date.to_s.tr("-","_") } VALUES LESS THAN (UNIX_TIMESTAMP("#{ n.to_date.to_s } 00:00:00")) ENGINE = InnoDB,\n]
    t = n
  end
  result[0..-3] + ");"
end

def build_trends(t,table)
  result = "ALTER TABLE `#{table}` PARTITION BY RANGE ( clock )\n("
  while t < Time.now do
    n = t.beginning_of_month + 1.month
    result << %[PARTITION p#{ t.to_date.to_s.tr("-","_")[0..-4] } VALUES LESS THAN (UNIX_TIMESTAMP("#{ n.to_date.to_s } 00:00:00")) ENGINE = InnoDB,\n]
    t = n
  end
  result[0..-3] + ");"
end

start_time = Time.parse( ARGV[0] )

["history","history_log","history_str","history_text","history_uint"].each do |table|
  puts build_history start_time, table
end

["trends","trends_uint"].each do |table|
  puts build_trends start_time, table
end

exit 0

Execute it with

gem install activesupport
./zabbix_partition_builder "2020-03-09 14:24:50"

and run the output in a mysql session.