Powershell script to generate Database SQL

[DavidRickard]

Hello all,

I've just been deploying Zabbix 7.0 into our environment - utterly loving it, and it has already found things we'd have otherwise missed.

I've been working on partitioning the database and getting that done up front to avoid it all keeling over one day; it made sense to me to build the DB as we mean to go on. I found the partitioning script, and the blog which says how to set it all up. I've had Zabbix running for a few weeks now, and hand turning the SQL queries seemed less fun than writing a powershell script to generate it for me. So that's what I did. I hope these are a) useful and b) correct (!!!). They run in PS7.

This first script is for all the daily partitions

# This is hacky, and could corrupt your database. No warranties implied or given!
# CHECK THE OUTPUT BEFORE PASTING. YOU HAVE BEEN WARNED.

# Basically, tweak the $Timestamp and $Table variables for the earliest timestamp and name of the table.
# When you run the script it'll generate a per-day partition for the selected table as a massive SQL query
# Splat that into MySQL/Percona/MariaDB and it should work.

# https://blog.zabbix.com/partitioning-a-zabbix-mysql-database-with-perl-or-stored-procedures/13531/

# DO NOT USE THIS SCRIPT FOR THE TRENDS TABLES! They should use a monthly partition

$Timestamp = [DateTime]"2024-07-15 15:00:00"
$Table = "history_bin"

# Don't change this stuff...
$Today = Get-Date -Hour 0 -Minute 00 -Second 00

Write-Host "ALTER TABLE $Table PARTITION BY RANGE (clock)
("
$Counter = 0
while ($Today.AddDays(0) -ge $Timestamp.AddDays($Counter)) {
    $Curr1 = ($Timestamp).AddDays($Counter).ToString("yyyy_MM_dd")
    $Curr2 = ($Timestamp).AddDays($Counter+1).ToString("yyyy-MM-dd 00:00:00")

    Write-Host "PARTITION p$Curr1 VALUES LESS THAN (UNIX_TIMESTAMP(""$Curr2"")) ENGINE = InnoDB,"
    $Counter++
}
$Curr1 = ($Timestamp).AddDays($Counter).ToString("yyyy_MM_dd")
$Curr2 = ($Timestamp).AddDays($Counter+1).ToString("yyyy-MM-dd 00:00:00")
Write-Host "PARTITION p$Curr1 VALUES LESS THAN (UNIX_TIMESTAMP(""$Curr2"")) ENGINE = InnoDB);"

Then this script is for the monthly partitions (Trends tables)

# This is hacky, and could corrupt your database. No warranties implied or given!
# CHECK THE OUTPUT BEFORE PASTING. YOU HAVE BEEN WARNED.

# Basically, tweak the $DBTimestamp and $Table variables for the earliest timestamp and name of the table.
# When you run the script it'll generate a per-day partition for the selected table as a massive SQL query
# Splat that into MySQL/Percona/MariaDB and it should work.

# https://blog.zabbix.com/partitioning-a-zabbix-mysql-database-with-perl-or-stored-procedures/13531/

# THIS IS THE SCRIPT FOR THE TRENDS* TABLES!

$DBTimestamp = [DateTime]"2024-06-28 15:00:00"
$Table = "trends_uint"

# Don't change this stuff...
$Today = Get-Date -Hour 0 -Minute 00 -Second 00 -Day 01
$Timestamp = Get-Date -Date $DBTimestamp -Day 01

Write-Host "ALTER TABLE $Table PARTITION BY RANGE (clock)
("
$Counter = 0
while ($Today -ge $Timestamp.AddMonths($Counter)) {
    $Curr1 = ($Timestamp).AddMonths($Counter).ToString("yyyy_MM")
    $Curr2 = ($Timestamp).AddMonths($Counter+1).ToString("yyyy-MM-dd 00:00:00")

    Write-Host "PARTITION p$Curr1 VALUES LESS THAN (UNIX_TIMESTAMP(""$Curr2"")) ENGINE = InnoDB,"
    $Counter++
}
$Curr1 = ($Timestamp).AddMonths($Counter).ToString("yyyy_MM")
$Curr2 = ($Timestamp).AddMonths($Counter+1).ToString("yyyy-MM-dd 00:00:00")
Write-Host "PARTITION p$Curr1 VALUES LESS THAN (UNIX_TIMESTAMP(""$Curr2"")) ENGINE = InnoDB);"

​

Anyway, I hope somebody finds these useful, especially on a bigger DB. Once the partitions are in place the script will happily maintain them.