Collect MySQL logs

Supported in:

Google secops SIEM

This document explains how to ingest MySQL logs to Google Security Operations using the Bindplane agent.

MySQL is a relational database management system that generates syslog messages for authentication events, query execution, database operations, and audit trail entries. The parser extracts fields from syslog-formatted audit logs and maps them to the Unified Data Model (UDM).

Before you begin

Make sure you have the following prerequisites:

A Google SecOps instance
Windows Server 2016 or later, or Linux host with systemd
Network connectivity between the Bindplane agent and the MySQL server
If running behind a proxy, ensure firewall ports are open per the Bindplane agent requirements
SSH access to the MySQL host with root or sudo privileges

Get Google SecOps ingestion authentication file

Sign in to the Google SecOps console.
Go to SIEM Settings > Collection Agents.
Download the Ingestion Authentication File.
Save the file securely on the system where the Bindplane agent will be installed.

Note: This JSON file contains credentials for authenticating the Bindplane agent to Google SecOps.

Get Google SecOps customer ID

Sign in to the Google SecOps console.
Go to SIEM Settings > Profile.
Copy and save the Customer ID from the Organization Details section.

Note: The customer ID is required for configuring the Bindplane agent exporter.

Install the Bindplane agent

Install the Bindplane agent on your Windows or Linux operating system according to the following instructions.

Windows installation

Open Command Prompt or PowerShell as an administrator.

Run the following command:

msiexec /i "https://github.com/observIQ/bindplane-agent/releases/latest/download/observiq-otel-collector.msi" /quiet

Wait for the installation to complete.
Verify the installation by running:
```
sc query observiq-otel-collector
```
The service should show as RUNNING.

Linux installation

Open a terminal with root or sudo privileges.

Run the following command:

sudo sh -c "$(curl -fsSlL https://github.com/observiq/bindplane-agent/releases/latest/download/install_unix.sh)" install_unix.sh

Wait for the installation to complete.
Verify the installation by running:
```
sudo systemctl status observiq-otel-collector
```
The service should show as active (running).

Additional installation resources

For additional installation options and troubleshooting, see the Bindplane agent installation guide.

Configure the Bindplane agent to ingest syslog and send to Google SecOps

Locate the configuration file

Linux:

sudo nano /opt/observiq-otel-collector/config.yaml

Windows:

notepad "C:\Program Files\observIQ OpenTelemetry Collector\config.yaml"

Edit the configuration file

Replace the entire contents of config.yaml with the following configuration:

receivers:
    udplog:
        listen_address: "0.0.0.0:514"

exporters:
    chronicle/mysql:
        compression: gzip
        creds_file_path: '/etc/bindplane-agent/ingestion-auth.json'
        customer_id: '<customer_id>'
        endpoint: malachiteingestion-pa.googleapis.com
        log_type: MYSQL
        raw_log_field: body

service:
    pipelines:
        logs/mysql_to_chronicle:
            receivers:
                - udplog
            exporters:
                - chronicle/mysql

Configuration parameters

Replace the following placeholders:

Receiver configuration:
- listen_address: IP address and port to listen on:
  - 0.0.0.0 to listen on all interfaces (recommended)
  - Port 514 is the standard syslog port (requires root on Linux; use 1514 for non-root)
Exporter configuration:
- creds_file_path: Full path to ingestion authentication file:
  - Linux: /etc/bindplane-agent/ingestion-auth.json
  - Windows: C:\Program Files\observIQ OpenTelemetry Collector\ingestion-auth.json
- customer_id: Customer ID copied from the Google SecOps console
- endpoint: Regional endpoint URL:
  - US: malachiteingestion-pa.googleapis.com
  - Europe: europe-malachiteingestion-pa.googleapis.com
  - Asia: asia-southeast1-malachiteingestion-pa.googleapis.com
  - See Regional Endpoints for complete list

Save the configuration file

After editing, save the file:
- Linux: Press Ctrl+O, then Enter, then Ctrl+X
- Windows: Click File > Save

Restart the Bindplane agent to apply the changes

To restart the Bindplane agent in Linux, run the following command:

sudo systemctl restart observiq-otel-collector

Verify the service is running:

sudo systemctl status observiq-otel-collector

Check logs for errors:

sudo journalctl -u observiq-otel-collector -f

To restart the Bindplane agent in Windows, choose one of the following options:
- Command Prompt or PowerShell as administrator:
```
net stop observiq-otel-collector && net start observiq-otel-collector
```
- Services console:
  1. Press Win+R, type services.msc, and press Enter.
  2. Locate observIQ OpenTelemetry Collector.
  3. Right-click and select Restart.
  4. Verify the service is running:
```
sc query observiq-otel-collector
```
  5. Check logs for errors:
```
type "C:\Program Files\observIQ OpenTelemetry Collector\log\collector.log"
```

Configure syslog in MySQL

Sign in to the MySQL host using SSH.
Connect to the MySQL database:
```
mysql -u root -p
```
Verify the server_audit.so audit plugin:
```
show variables like 'plugin_dir';
```

If the plugin is not found, install it:

install plugin server_audit soname 'server_audit.so';

Confirm the plugin is Installed and Enabled:
```
show plugins;
```

Edit the file /etc/my.cnf and enable the following audit settings:

server_audit_events='CONNECT,QUERY,TABLE'
server_audit_file_path=server_audit.log
server_audit_logging=ON
server_audit_output_type=SYSLOG
server_audit_syslog_facility=LOG_LOCAL6

Verify the audit variables:

show global variables like "server_audit%";

Verify auditing is enabled:

Show global status like 'server_audit%';

Edit the file /etc/rsyslog.conf to enable forwarding via UDP:
```
*.* @@<bindplane-agent-ip>:<bindplane-agent-port>
```
- Replace <bindplane-agent-ip> and <bindplane-agent-port> with your Bindplane agent configuration.
Restart the MySQL service:
```
/etc/init.d/mysqld restart
```

UDM mapping table

Log field	UDM mapping	Logic
action	read_only_udm.metadata.event_type	If the value is `Created` then FILE_CREATION, if the value is `Deleted` then FILE_DELETION, otherwise no change.
database	read_only_udm.target.resource.parent
db_hostname	read_only_udm.target.hostname
db_user	read_only_udm.target.user.userid
description	read_only_udm.security_result.description
error_details		This is a temporary variable, ignore it
error_level	read_only_udm.security_result.severity	If the value is `error` then ERROR, if the value is `warning` then MEDIUM, if the value is `note` then INFORMATIONAL, otherwise no change.
error_message	read_only_udm.security_result.summary
file_path	read_only_udm.target.file.full_path
file_size	read_only_udm.target.file.size
hostname	read_only_udm.principal.hostname
inner_message	read_only_udm.security_result.description
summary	read_only_udm.metadata.product_event_type
table	read_only_udm.target.resource.name
table_not_found		This is a temporary variable, ignore it
timestamp	read_only_udm.metadata.event_timestamp
	read_only_udm.extensions.auth.type	Static value - `MACHINE`
	read_only_udm.metadata.event_type	Static value - `USER_LOGIN`, `GENERIC_EVENT`, `STATUS_UPDATE`, `FILE_CREATION`, `FILE_DELETION`
	read_only_udm.metadata.log_type	Static value - `MYSQL`
	read_only_udm.metadata.product_name	Static value - `MySQL`
	read_only_udm.metadata.vendor_name	Static value - `Oracle Corporation`
	read_only_udm.security_result.action	Static value - `BLOCK`
	read_only_udm.target.resource.resource_type	Static value - `DATABASE`, `TABLE`

Need more help? Get answers from Community members and Google SecOps professionals.