Configure MySQL Governor Alerts to Auto‑Kill Rogue Queries

Introduction

On a busy MySQL server, occasional queries can consume large amounts of CPU, memory, or I/O and slow down every other workload. MySQL 8.0 includes the MySQL Governor framework, which lets you define alert hooks that react to resource-intensive queries in real time. By configuring these hooks to terminate offending statements automatically, you can protect your applications without manual intervention.

This guide explains how to set up MySQL Governor alert hooks that auto-kill rogue queries. It covers the necessary MySQL configuration, the creation of a custom alert plugin, and the installation steps for Debian/Ubuntu and AlmaLinux/Rocky/RHEL systems. Windows Server users should adapt the installation steps to the appropriate package manager (e.g., choco) and service handling; the MySQL-side configuration remains the same.

1. Prerequisites

• MySQL 8.0 or newer installed and running with a user that has SUPER and CREATE ROLE privileges.
• Shell access to the server (SSH for Linux, PowerShell for Windows).
• Basic familiarity with MySQL statements and Linux package managers.

Make sure the MySQL data directory resides on a filesystem with sufficient free space, as Governor logs query statistics for analysis.

2. Enable the MySQL Governor Framework

The Governor subsystem is disabled by default. Enable it in the MySQL configuration file (my.cnf or my.ini).

2.1 Edit the configuration file

# Debian/Ubuntu (apt)
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# AlmaLinux/Rocky/RHEL (dnf)
sudo nano /etc/my.cnf

Add the following lines under the [mysqld] section:

[mysqld]
governor=ON
governor_log_dir=/var/log/mysql/governor
governor_log_level=INFO

Explanation:

• governor=ON – activates the Governor subsystem.
• governor_log_dir – directory where Governor stores its runtime logs.
• governor_log_level=INFO – records detailed information useful for debugging.

2.2 Create the log directory and set permissions

# Debian/Ubuntu
sudo mkdir -p /var/log/mysql/governor
sudo chown mysql:mysql /var/log/mysql/governor
sudo chmod 750 /var/log/mysql/governor

# AlmaLinux/Rocky/RHEL
sudo mkdir -p /var/log/mysql/governor
sudo chown mysql:mysql /var/log/mysql/governor
sudo chmod 750 /var/log/mysql/governor

2.3 Restart MySQL

# Debian/Ubuntu
sudo systemctl restart mysql

# AlmaLinux/Rocky/RHEL
sudo systemctl restart mysqld

After the restart, MySQL begins collecting Governor metrics.

3. Create a Custom Alert Hook Plugin

The Governor framework calls alert hooks whenever a query exceeds defined thresholds. Below is a simple C plugin that kills a query if its runtime surpasses a limit.

3.1 Install development tools

# Debian/Ubuntu
sudo apt update
sudo apt install -y build-essential libmysqlclient-dev cmake

# AlmaLinux/Rocky/RHEL
sudo dnf groupinstall -y "Development Tools"
sudo dnf install -y mysql-devel cmake

3.2 Write the plugin source

Create a directory for the source code and open a new file named kill_rogue.c:

mkdir -p ~/governor-plugin && cd ~/governor-plugin
cat > kill_rogue.c <<'EOF'
#include 
#include 
#include 

static int kill_rogue_alert(MYSQL_THD thd,
                            const char *event_name,
                            const void *event_data,
                            size_t event_data_len) {
    /* The event_data points to a struct governor_event which contains
       runtime statistics. We only need the query execution time. */
    const struct governor_event *ge = (const struct governor_event *)event_data;

    /* Define the threshold in seconds (e.g., 5 seconds). */
    const double threshold = 5.0;

    if (ge->runtime_seconds > threshold) {
        /* Issue KILL QUERY for the offending thread. */
        char sql[128];
        snprintf(sql, sizeof(sql), "KILL QUERY %lu", (unsigned long)ge->thread_id);
        mysql_execute_sql(thd, sql);
    }
    return 0;  /* Return 0 to indicate success. */
}

/* Plugin descriptor */
static struct st_mysql_audit kill_rogue_plugin = {
    MYSQL_AUDIT_INTERFACE_VERSION,
    NULL,                     /* start function (not needed) */
    NULL,                     /* shutdown function (not needed) */
    { { kill_rogue_alert, "governor_alert", NULL } }
};

mysql_declare_plugin(kill_rogue) {
    MYSQL_AUDIT_PLUGIN,
    &kill_rogue_plugin,
    "kill_rogue",
    "AtoZNode",
    "Auto-kill queries that exceed runtime threshold",
    PLUGIN_LICENSE_GPL,
    NULL, NULL, 0x0100
}
mysql_declare_plugin_end;
EOF

Explanation:

• The plugin registers a callback kill_rogue_alert that receives Governor events.
• If ge->runtime_seconds exceeds the threshold, it issues a KILL QUERY statement for the offending thread.
• The plugin is registered as an MYSQL_AUDIT_PLUGIN named kill_rogue.

3.3 Build the plugin

# Create a simple CMakeLists.txt
cat > CMakeLists.txt <<'EOF'
cmake_minimum_required(VERSION 3.5)
project(kill_rogue_plugin C)

find_path(MYSQL_INCLUDE_DIR mysql.h
          HINTS /usr/include/mysql /usr/include/mariadb)

find_library(MYSQL_CLIENT_LIB mysqlclient
             HINTS /usr/lib /usr/lib64)

add_library(kill_rogue SHARED kill_rogue.c)
target_include_directories(kill_rogue PRIVATE ${MYSQL_INCLUDE_DIR})
target_link_libraries(kill_rogue ${MYSQL_CLIENT_LIB})
set_target_properties(kill_rogue PROPERTIES PREFIX "" SUFFIX ".so")
EOF

# Build
mkdir build && cd build
cmake ..
make

After compilation, kill_rogue.so will be in the build directory.

3.4 Install the plugin

# Debian/Ubuntu
sudo cp build/kill_rogue.so /usr/lib/mysql/plugin/

# AlmaLinux/Rocky/RHEL
sudo cp build/kill_rogue.so /usr/lib64/mysql/plugin/

Set ownership to the MySQL user:

sudo chown mysql:mysql /usr/lib/mysql/plugin/kill_rogue.so   # Debian/Ubuntu
sudo chown mysql:mysql /usr/lib64/mysql/plugin/kill_rogue.so # AlmaLinux/Rocky/RHEL

4. Register and Configure the Alert Hook in MySQL

With the shared object in place, load it from the MySQL client.

4.1 Load the plugin

mysql -u root -p

Inside the MySQL shell, run:

INSTALL PLUGIN kill_rogue SONAME 'kill_rogue.so';

If the command succeeds, MySQL reports “Query OK”. The plugin will now receive Governor alerts.

4.2 Define the alert criteria

The Governor framework lets you set thresholds for CPU, memory, or runtime. Here we focus on runtime:

SET GLOBAL governor_alert_threshold_runtime = 5;   -- seconds

You can also set other limits, for example:

SET GLOBAL governor_alert_threshold_cpu = 80;      -- percent
SET GLOBAL governor_alert_threshold_io = 100000;   -- I/O ops per second

When any of these limits are breached, the Governor will invoke the kill_rogue alert hook, which in turn kills the offending query.

4.3 Persist the settings

Add the following lines to my.cnf (or my.ini) under the [mysqld] section to keep the configuration across restarts:

governor_alert_threshold_runtime=5
governor_alert_threshold_cpu=80
governor_alert_threshold_io=100000
plugin-load-add=kill_rogue.so

Restart MySQL once more to apply the persisted settings.

5. Verify the Auto-Kill Behaviour

Testing ensures the hook works before you rely on it in production.

5.1 Simulate a long-running query

mysql -u root -p -e "SELECT SLEEP(10);"

Because the query runs for 10 seconds—exceeding the 5-second threshold—the Governor should trigger the alert hook, and MySQL will terminate the query. You will see an error similar to:

ERROR 1317 (70100): Query execution was interrupted

5.2 Check the Governor log

# Debian/Ubuntu
sudo tail -n 20 /var/log/mysql/governor/governor.log

# AlmaLinux/Rocky/RHEL
sudo tail -n 20 /var/log/mysql/governor/governor.log

The log entry will include the thread ID, runtime, and a note that the alert hook executed the kill command.

5.3 Monitor ongoing activity

Use the built-in performance schema to watch active queries:

SELECT thd_id, event_name, timer_start, timer_end
FROM performance_schema.events_statements_current
WHERE timer_end - timer_start > 5000000000;   -- 5 seconds in nanoseconds

If the hook is active, you should not see any rows persisting beyond the threshold.

6. Best Practices and Troubleshooting

• Start with a conservative threshold. Set a higher limit (e.g., 30 seconds) and observe the log before tightening it.
• Combine runtime with resource metrics. A query that uses little CPU but runs for a long time may still be harmless; adding governor_alert_threshold_cpu can reduce false positives.
• Log rotation. The Governor log can grow quickly. Configure logrotate for /var/log/mysql/governor to keep disk usage in check.
• Plugin reload. After modifying the plugin source, re-compile, replace the .so file, and run UNINSTALL PLUGIN kill_rogue; followed by INSTALL PLUGIN kill_rogue SONAME 'kill_rogue.so'; to apply updates without restarting MySQL.
• Windows note. On Windows Server, place the plugin file in the MySQL plugin directory (typically C:\Program Files\MySQL\MySQL Server 8.0\plugin) and load it with the same INSTALL PLUGIN command. Service management uses net stop mysql / net start mysql instead of systemctl.

Conclusion

MySQL Governor alert hooks give you a lightweight way to protect your database from runaway queries. By enabling the Governor, compiling a small C plugin that issues KILL QUERY, and configuring appropriate thresholds, you can automate the termination of rogue statements without constant manual monitoring. The steps above work on Debian/Ubuntu and AlmaLinux/Rocky/RHEL, providing Indian developers and site owners with a reliable safeguard for their cloud VPS or dedicated servers hosted with AtoZNode.