Collect MYSQL logs

This document explains how to ingest MYSQL logs to Google Security Operations using Bindplane. The parser first extracts common fields from MySQL SYSLOG messages using grok. Then, it uses conditional branching (if, else if) and regular expression matching to identify specific event types within the log messages, extracting and mapping relevant information into the Unified Data Model (UDM) schema.

Before you begin

Make sure you have the following prerequisites:

• Google SecOps instance
• Windows 2016 or later, or a Linux host with systemd
• If running behind a proxy, firewall ports are open
• Privileged access to MySQL host
• Installed MySQL DB and Rsyslog

Get Google SecOps ingestion authentication file

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

Get Google SecOps customer ID

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

Install the Bindlane agent

Windows installation

1. Open the Command Prompt or PowerShell as an administrator.

2. Run the following command:

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

Linux installation

1. Open a terminal with root or sudo privileges.

2. Run the following command:

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

Additional installation resources

For additional installation options, consult the installation guide.

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

1. Access the configuration file:
   • Locate the config.yaml file. Typically, it's in the /etc/bindplane-agent/ directory on Linux or in the installation directory on Windows.
   • Open the file using a text editor (for example, nano, vi, or Notepad).

2. Edit the config.yaml file as follows:

   receivers:
       udplog:
           # Replace the port and IP address as required
           listen_address: "0.0.0.0:514"
   
   exporters:
       chronicle/chronicle_w_labels:
           compression: gzip
           # Adjust the path to the credentials file you downloaded in Step 1
           creds: '/path/to/ingestion-authentication-file.json'
           # Replace with your actual customer ID from Step 2
           customer_id: <customer_id>
           endpoint: malachiteingestion-pa.googleapis.com
           # Add optional ingestion labels for better organization
           ingestion_labels:
               log_type: 'MYSQL'
               raw_log_field: body
   
   service:
       pipelines:
           logs/source0__chronicle_w_labels-0:
               receivers:
                   - udplog
               exporters:
                   - chronicle/chronicle_w_labels
   

3. Replace the port and IP address as required in your infrastructure.

4. Replace <customer_id> with the actual customer ID.

5. Update /path/to/ingestion-authentication-file.json to the path where the authentication file was saved in the Get Google SecOps ingestion authentication file section.

Restart the Bindplane agent to apply the changes

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

  sudo systemctl restart bindplane-agent
  

• To restart the Bindplane agent in Windows, you can either use the Services console or enter the following command:

  net stop BindPlaneAgent && net start BindPlaneAgent
  

Configure Syslog in MySQL

1. Sign in to the MySQL host using SSH.

2. Connect to MySQL database:

   mysql -u root -p
   

3. Verify for the server_audit.so audit plugin:

   show variables like 'plugin_dir';
   

4. If you don't find the plugin file inside your plugin's directory, install the plugin using the command:

   install plugin server_audit soname 'server_audit.so';
   

5. Confirm the plugin is Installed and Enabled:

   show plugins;
   

6. Edit the file /etc/my.cnf using vi, enable the following and save file:

   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
   

7. Verify the audit variables with the following command:

   show global variables like "server_audit%";
   

8. Verify auditing is enabled, with the following command:

   Show global status like 'server_audit%';
   

9. Edit the file /etc/rsyslog.conf using vi, to enable using UDP and save file:

   *.* @@<bindplane-agent-ip>:<bindplane-agent-port>
   

10. Replace <bindplane-agent-ip> and <bindplane-agent-port> with your Bindplane agent configuration.

11. Restart MySQL service and connect to MySQL database.

    /etc/init.d/mysqld restart
    

UDM mapping table

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