Introduction

In production environments, changes to the database schema such as creating new tables or altering existing ones should be tracked and communicated to database administrators or development teams. Receiving notifications whenever these Data Definition Language (DDL) operations occur helps maintain audit trails, detect unauthorized schema modifications, and improve operational visibility.

This solution monitors the MySQL General Query Log for CREATE TABLE and ALTER TABLE statements and sends an email notification containing the detected queries.

Prerequisites

Before implementing this solution, ensure the following requirements are met:

  • Python is installed on the server.
  • MySQL General Query Log is enabled.
  • The MySQL General Query Log file is accessible (default location: /var/lib/mysql/mysql-general.log).
  • A working mail transfer agent (such as Sendmail or Postfix) is installed and configured.
  • The script has read permission on the MySQL General Query Log.
  • The recipient email address is configured in the script.
  • Appropriate permissions to execute the Python script.

Implementation Steps

Step 1: Enable the MySQL General Query Log

Verify whether the General Query Log is enabled.

SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'general_log_file';

If it is disabled, enable it:

SET GLOBAL general_log = 'ON';

To make it persistent, update the MySQL configuration file (my.cnf):

[mysqld]
general_log = 1
general_log_file = /var/lib/mysql/mysql-general.log

Restart MySQL if configuration changes are made.

Step 2: Create the Monitoring Script

Create a Python script (for example: mysql_audit.py) and add the provided code.

The script performs the following tasks:

  • Reads the MySQL General Query Log.
  • Searches for:
    • CREATE TABLE
    • ALTER TABLE
  • Ignores SHOW CREATE TABLE statements.
  • Writes detected queries into a temporary report.
  • Sends the report via email.
  • Removes the temporary report after successful delivery.

Step 3: Configure Email Settings

Modify the following values in the script:

  • Sender email address
  • Recipient email address
  • Sendmail path (if different)

Example:

mysqlaudit["From"] = "mysqlauditreport@example.com"
mysqlaudit["To"] = "dba@example.com"
mysqlaudit["Subject"] = "MySQL Audit Report"

Step 4: Execute the Script

Run the script manually:

python mysql_audit.py

If DDL statements are detected, an email similar to the following will be generated:

Below Alter/Create Queries have been Run

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

CREATE TABLE audit_log (...);

Step 5: Automate Using Cron (Optional)

To continuously monitor the log, schedule the script using cron.

Example (every 5 minutes):

*/5 * * * * /usr/bin/python /path/mysql_audit.py

Conclusion

This solution provides a simple auditing mechanism for monitoring MySQL schema changes without requiring additional database plugins or third-party tools. By scanning the MySQL General Query Log and sending email alerts for CREATE TABLE and ALTER TABLE statements, administrators can quickly identify schema modifications, improve change tracking, and strengthen database governance. For production environments with high query volumes, consider using MySQL Enterprise Audit or MariaDB Audit Plugin for a more scalable and efficient auditing solution.

Post Views: 169

Leave a Reply