How to Build a Low-Cost Bash and MySQL Audit System for Server Operations

Overview

The operations team needs reliable records of every action performed on servers—both shell commands and MySQL statements—to troubleshoot incidents and investigate security events. Existing bastion‑host hardware is expensive and prone to single‑point failure, while open‑source tools such as ttyrec or modified OpenSSH have serious bugs.

Solution Architecture

A low‑cost, high‑performance audit system is built by modifying Bash and MySQL source code, adding a fingerprint variable to identify the actual operator, and forwarding all logs to a remote log center.

1. Linux Bash Audit

Precise per‑system‑user shell operation records

Logs for remote ssh IP "command" and scp actions

Continues logging after user switching (e.g., su)

Supports MySQL operation logs

The core implementation modifies bashhist.c around line 701, changing the bash_syslog_history function to include a custom variable NAME_OF_KEY (treated as a fingerprint). After recompiling and installing, the system logs each command with the fingerprint; if the variable is missing, only the generic system account appears.

Fingerprint handling is done by a script placed at /etc/bash_ywjt. When a user adds an SSH key in ~/.ssh/authorized_keys, the third field contains a string like employeeID@pinyinName, which the script extracts and exports as NAME_OF_KEY.

SSH daemon debugging is enabled by adding LogLevel DEBUG to /etc/ssh/sshd_config and restarting the service.

For su tracking, the solution monitors PID values in /var/log/secure and matches them against ps -ef output to trace the original user.

Remote command logging is achieved by adding a line to ~/.bashrc (not ~/.bash_profile) that checks the BASH_EXECUTION_STRING variable and logs accordingly.

2. MySQL Operation Log

Standard MySQL binlog or .mysql_history does not meet the requirement of per‑user auditing. The team uses Percona‑Server‑5.5 and enables syslog logging by adding syslog to the client section of /etc/my.cnf.

Initial problems:

mysqldump fails when syslog is enabled.

Users can bypass logging with --no-defaults.

Logs only show the root account, not the actual operator.

To solve these, the source code is patched:

Enable the opt_syslog flag in client/mysql.cc and recompile.

Interactive mode logging required removing the CLIENT_INTERACTIVE condition so that commands executed with mysql -e are also recorded.

To prevent massive log files when importing large SQL dumps, the write_syslog function is modified to truncate messages exceeding MAX_SYSLOG_MESSAGE size and to prepend the NAME_OF_KEY fingerprint.

A configurable no-syslog option is added, allowing administrators to disable the feature via a custom parameter in my.cnf.

3. Remote Log Center

All syslog entries are forwarded to a remote server by adding a destination rule in /etc/rsyslog.conf and restarting the rsyslog service.

The remote server can run Splunk, Logstash, or any other log‑analysis platform for centralized monitoring.

4. Internal Network Log Interception

To retain original internal IP addresses, logs are intercepted at the Juniper firewall and forwarded to an internal log server.

Summary

Deploying a customized Bash and MySQL audit system, combined with a remote syslog collector, provides fine‑grained visibility into server operations, helps identify the true operator behind each action, reduces log volume, and supports secure, centralized incident investigation.