Fortinet black logo

External Systems Configuration Guide

MySQL Server

MySQL Server

FortiSIEM Support added: 4.7.2

FortiSIEM last modification: 6.5.0

Supported Versions: :

  • MySQL 5.7 and earlier

  • MySQL 8.0

Vendor: Oracle

Product Information: https://www.mysql.com/products/

What is Discovered and Monitored

Protocol Information discovered Metrics collected

Used for

SNMP Application type Process level CPU and memory utilization Performance Monitoring
WMI Application type, service mappings Process level metrics: uptime, CPU utilization, Memory utilization, Read I/O KBytes/sec, Write I/O KBytes/sec Performance Monitoring
JDBC

Generic database information: Version, Character Setting

JDBC

Database performance metrics: User Connections, Table Updates, table Selects, Table Inserts, Table Deletes, Temp Table Creates, Slow Queries, Query cache Hits, Queries registered in cache, Database Questions, Users, Live Threads

Table space performance metrics: Table space name, table space type, Character set and Collation, table space usage, table space free space, Database engine, Table version, Table Row Format, Table Row Count, Average Row Length, Index File length, Table Create time, Table Update Time

Performance Monitoring
JDBC None Database audit trail: Successful and failed database log on, Database CREATE/DELETE/MODIFY operations, Table CREATE/DELETE/MODIFY/INSERT operations Security Monitoring

Event Types

In ADMIN > Device Support > Event Types, search for "mysql" to see the event types associated with this device.

Rules

In RESOURCES > Rules, search for "mysql" in the main content panel Search... field to see the rules associated with this application or device.

Reports

In RESOURCES > Reports, search for ""mysql" in the main content panel Search... field to see the reports associated with this application or device.

Configuration

SNMP

FortiSIEM uses SNMP to discover and monitor this device. Make sure SNMP is enabled for the device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

JDBC for Database Auditing - MySQL Server

You must configure your MySQL Server to write audit logs to a database table. This topic in the MySQL documentation explains more about how to set the destination tables for log outputs.

Take the following steps based on your version of Linux:

  1. If the binary bin/mysqld_safe exists in your version of Linux, run the following command and proceed to step 2:

    bin/mysqld_safe --user=mysql --log-output=TABLE &


    If the binary bin/mysqld_safe does not exist in your version of Linux, then take the following step based on the version of MySQL being used, then proceed to step 2.

    Note: Some Linux variants that use systemd, for example, CentOS, do not contain bin/mysqld_safe.

    For MySQL 5.7 and earlier, add the following into /etc.my.cnf.

    log-output=TABLE

    For MySQL 8.0 and later, add the following into /etc/my.cnf.d/mysql-server.cnf.

    log-output=TABLE

  2. Login to MySQL, and run the following SQL commands to enable general.log in MyISAM.

    SET @old_log_state = @@global.general_log;
    SET GLOBAL general_log = 'OFF';
    ALTER TABLE mysql.general_log ENGINE = MyISAM;
    SET GLOBAL general_log = @old_log_state;
    SET GLOBAL general_log = 'ON';

You can now configure FortiSIEM to communicate with your device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide. Use the settings in Settings for MySQL Server JDBC Access Credentials for Database Auditing to set up MySQL Audit.

Settings for Access Credentials

SNMP Access Credentials for All Devices

Use these Access Method Definition settings to allow FortiSIEM to communicate with your device over SNMP. Set the Name and Community String.

SettingValue
Name<set name>
Device TypeGeneric
Access ProtocolSNMP
Community String<your own>

Settings for MySQL Server JDBC Access Credentials for Performance Monitoring

Use these Access Method Definition settings to allow FortiSIEM to communicate with your MySQL Server over JDBC for performance monitoring:

Setting Value
Name MySQL-Performance-Monitoring
Device Type Oracle MySQL
Access Protocol JDBC
Used For Performance Monitoring
Pull Interval (minutes) 5
Port 3306
User Name The administrative user for the database server
Password The password associated with the administrative user
Settings for MySQL Server JDBC Access Credentials for Database Auditing

Use these Access Method Definition settings to allow FortiSIEM to communicate with your MySQL Server over JDBC for database auditing:

Setting Value
Name MySQL-Audit
Device Type Oracle MySQL
Access Protocol JDBC
Used For Audit
Pull Interval (minutes) 5
Port 3306
Database Name <database name> (MySQL)
Audit Table general_log
User Name The administrative user for the database server
Password The password associated with the administrative user
Settings for MySQL Server JDBC Access Credentials for Synthetic Transaction Monitoring, Snort Audit, McAfee VulnMgr

Use these Access Method Definition settings to allow FortiSIEM to communicate with your MySQL Server over JDBC for Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr:

Setting Value
Name <name>
Device Type Oracle MySQL
Access Protocol JDBC
Used For Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr
Pull Interval (minutes) 5
Port 3306
Database Name <database name>
User Name The administrative user for the database server
Password The password associated with the administrative user

Sample Events

System Level Performance Metrics
<134>Apr 21 19:06:07 10.1.2.8 java: [PH_DEV_MON_PERF_MYSQLDB]: [eventSeverity]=PHL_INFO,
[hostIpAddr]=172.16.22.227, [hostName]=MYSQL, [appGroupName]=MySQL Database
Server, [appVersion]=MySQL 5.6.11, [charSetting]=utf8, [dbConnections]=24, [dbComUpdate]=0, [dbComSelect]=1, [dbComInsert]=0,
[dbComDelete]=0, [dbCreatedTmpTables]=0, [dbSlowQueries]=0, [dbQcacheHits]=0, [dbQcacheQueriesinCache]=0,
[dbQuestions]=7, [dbThreadsConnected]=1, [dbThreadsRunning]=1
Table Space Performance Metrics
<134>Apr 29 10:06:07 172.16.22.227 java: [PH_DEV_MON_PERF_MYSQLDB_TABLESPACE]: [eventSeverity]=PHL_INFO, [appGroupName]=MySQL Database Server, 
[instanceName]=mysql, [tablespaceName]=general_log, [tablespaceType]=PERMANENT, [tablespaceUsage]=0.01, [tablespaceFreeSpace]=4193886, 
[dbEngine]=MyISAM, [tableVersion]=10, [tableRowFormat]=dynamic, [tableRows]=124, [tableAvgRowLength]=80, [tableIndexLength]=1024, 
[tableCreateTime]=2013-04-29 15:12:30, [tableUpdateTime]=2013-04-29 12:35:46, [tableCollation]=utf8_general_ci
Logon/Logoff Events
<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_ Logon_Success]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [logonTime]=2013-04-29 15:14:54, [logoffTime]=, [actionName]=Connect, [msg]=admin@172.16.22.227 on

<134>Apr 10 14:29:22 abc-desktop java: [MYSQL_Logoff]:[eventSeverity]=PHL_INFO, [eventTime]=2013-04-10 14:29:22, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [logonTime]=, [logoffTime]=2014-04-10 14:29:22, [actionName]=quit, [msg]=

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_ Logon_Fail]: [eventSeverity]=PHL_WARN, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227,
 [srcIp]=172.16.22.227, [user]=admin, [logonTime]=2013-04-29 15:14:54, [logoffTime]=, [actionName]=Connect, 
[msg]=Access denied for user 'admin'@'172.16.22.227' (using password: YES)
Database CREATE/DELETE/MODIFY Events
<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Create_database]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=create database sliutest

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Drop_database]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=drop database sliutest
Table CREATE/DELETE/MODIFY Events
<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Create_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=CREATE TABLE tutorials_tbl(     tutorial_id INT NOT NULL AUTO_INCREMENT, 
tutorial_title VARCHAR(100) NOT NULL,     tutorial_author VARCHAR(40) NOT NULL,     submission_date DATE,     PRIMARY KEY ( tutorial_id )    )

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Delete_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=DELETE FROM tutorials_tbl WHERE tutorial_id=2NOT NULL,     
tutorial_author VARCHAR(40) NOT NULL,     submission_date DATE,     PRIMARY KEY ( tutorial_id )    

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Insert_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=INSERT INTO tutorials_tbl       (tutorial_title, tutorial_author, submission_date)  
    VALUES      ("Learn Java", "John Smith", NOW())

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Drop_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=DROP table sliutable

MySQL Server

FortiSIEM Support added: 4.7.2

FortiSIEM last modification: 6.5.0

Supported Versions: :

  • MySQL 5.7 and earlier

  • MySQL 8.0

Vendor: Oracle

Product Information: https://www.mysql.com/products/

What is Discovered and Monitored

Protocol Information discovered Metrics collected

Used for

SNMP Application type Process level CPU and memory utilization Performance Monitoring
WMI Application type, service mappings Process level metrics: uptime, CPU utilization, Memory utilization, Read I/O KBytes/sec, Write I/O KBytes/sec Performance Monitoring
JDBC

Generic database information: Version, Character Setting

JDBC

Database performance metrics: User Connections, Table Updates, table Selects, Table Inserts, Table Deletes, Temp Table Creates, Slow Queries, Query cache Hits, Queries registered in cache, Database Questions, Users, Live Threads

Table space performance metrics: Table space name, table space type, Character set and Collation, table space usage, table space free space, Database engine, Table version, Table Row Format, Table Row Count, Average Row Length, Index File length, Table Create time, Table Update Time

Performance Monitoring
JDBC None Database audit trail: Successful and failed database log on, Database CREATE/DELETE/MODIFY operations, Table CREATE/DELETE/MODIFY/INSERT operations Security Monitoring

Event Types

In ADMIN > Device Support > Event Types, search for "mysql" to see the event types associated with this device.

Rules

In RESOURCES > Rules, search for "mysql" in the main content panel Search... field to see the rules associated with this application or device.

Reports

In RESOURCES > Reports, search for ""mysql" in the main content panel Search... field to see the reports associated with this application or device.

Configuration

SNMP

FortiSIEM uses SNMP to discover and monitor this device. Make sure SNMP is enabled for the device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

JDBC for Database Auditing - MySQL Server

You must configure your MySQL Server to write audit logs to a database table. This topic in the MySQL documentation explains more about how to set the destination tables for log outputs.

Take the following steps based on your version of Linux:

  1. If the binary bin/mysqld_safe exists in your version of Linux, run the following command and proceed to step 2:

    bin/mysqld_safe --user=mysql --log-output=TABLE &


    If the binary bin/mysqld_safe does not exist in your version of Linux, then take the following step based on the version of MySQL being used, then proceed to step 2.

    Note: Some Linux variants that use systemd, for example, CentOS, do not contain bin/mysqld_safe.

    For MySQL 5.7 and earlier, add the following into /etc.my.cnf.

    log-output=TABLE

    For MySQL 8.0 and later, add the following into /etc/my.cnf.d/mysql-server.cnf.

    log-output=TABLE

  2. Login to MySQL, and run the following SQL commands to enable general.log in MyISAM.

    SET @old_log_state = @@global.general_log;
    SET GLOBAL general_log = 'OFF';
    ALTER TABLE mysql.general_log ENGINE = MyISAM;
    SET GLOBAL general_log = @old_log_state;
    SET GLOBAL general_log = 'ON';

You can now configure FortiSIEM to communicate with your device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide. Use the settings in Settings for MySQL Server JDBC Access Credentials for Database Auditing to set up MySQL Audit.

Settings for Access Credentials

SNMP Access Credentials for All Devices

Use these Access Method Definition settings to allow FortiSIEM to communicate with your device over SNMP. Set the Name and Community String.

SettingValue
Name<set name>
Device TypeGeneric
Access ProtocolSNMP
Community String<your own>

Settings for MySQL Server JDBC Access Credentials for Performance Monitoring

Use these Access Method Definition settings to allow FortiSIEM to communicate with your MySQL Server over JDBC for performance monitoring:

Setting Value
Name MySQL-Performance-Monitoring
Device Type Oracle MySQL
Access Protocol JDBC
Used For Performance Monitoring
Pull Interval (minutes) 5
Port 3306
User Name The administrative user for the database server
Password The password associated with the administrative user
Settings for MySQL Server JDBC Access Credentials for Database Auditing

Use these Access Method Definition settings to allow FortiSIEM to communicate with your MySQL Server over JDBC for database auditing:

Setting Value
Name MySQL-Audit
Device Type Oracle MySQL
Access Protocol JDBC
Used For Audit
Pull Interval (minutes) 5
Port 3306
Database Name <database name> (MySQL)
Audit Table general_log
User Name The administrative user for the database server
Password The password associated with the administrative user
Settings for MySQL Server JDBC Access Credentials for Synthetic Transaction Monitoring, Snort Audit, McAfee VulnMgr

Use these Access Method Definition settings to allow FortiSIEM to communicate with your MySQL Server over JDBC for Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr:

Setting Value
Name <name>
Device Type Oracle MySQL
Access Protocol JDBC
Used For Synthetic Transaction Monitoring, Snort Audit, or McAfee VulnMgr
Pull Interval (minutes) 5
Port 3306
Database Name <database name>
User Name The administrative user for the database server
Password The password associated with the administrative user

Sample Events

System Level Performance Metrics
<134>Apr 21 19:06:07 10.1.2.8 java: [PH_DEV_MON_PERF_MYSQLDB]: [eventSeverity]=PHL_INFO,
[hostIpAddr]=172.16.22.227, [hostName]=MYSQL, [appGroupName]=MySQL Database
Server, [appVersion]=MySQL 5.6.11, [charSetting]=utf8, [dbConnections]=24, [dbComUpdate]=0, [dbComSelect]=1, [dbComInsert]=0,
[dbComDelete]=0, [dbCreatedTmpTables]=0, [dbSlowQueries]=0, [dbQcacheHits]=0, [dbQcacheQueriesinCache]=0,
[dbQuestions]=7, [dbThreadsConnected]=1, [dbThreadsRunning]=1
Table Space Performance Metrics
<134>Apr 29 10:06:07 172.16.22.227 java: [PH_DEV_MON_PERF_MYSQLDB_TABLESPACE]: [eventSeverity]=PHL_INFO, [appGroupName]=MySQL Database Server, 
[instanceName]=mysql, [tablespaceName]=general_log, [tablespaceType]=PERMANENT, [tablespaceUsage]=0.01, [tablespaceFreeSpace]=4193886, 
[dbEngine]=MyISAM, [tableVersion]=10, [tableRowFormat]=dynamic, [tableRows]=124, [tableAvgRowLength]=80, [tableIndexLength]=1024, 
[tableCreateTime]=2013-04-29 15:12:30, [tableUpdateTime]=2013-04-29 12:35:46, [tableCollation]=utf8_general_ci
Logon/Logoff Events
<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_ Logon_Success]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [logonTime]=2013-04-29 15:14:54, [logoffTime]=, [actionName]=Connect, [msg]=admin@172.16.22.227 on

<134>Apr 10 14:29:22 abc-desktop java: [MYSQL_Logoff]:[eventSeverity]=PHL_INFO, [eventTime]=2013-04-10 14:29:22, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [logonTime]=, [logoffTime]=2014-04-10 14:29:22, [actionName]=quit, [msg]=

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_ Logon_Fail]: [eventSeverity]=PHL_WARN, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227,
 [srcIp]=172.16.22.227, [user]=admin, [logonTime]=2013-04-29 15:14:54, [logoffTime]=, [actionName]=Connect, 
[msg]=Access denied for user 'admin'@'172.16.22.227' (using password: YES)
Database CREATE/DELETE/MODIFY Events
<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Create_database]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=create database sliutest

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Drop_database]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=drop database sliutest
Table CREATE/DELETE/MODIFY Events
<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Create_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=CREATE TABLE tutorials_tbl(     tutorial_id INT NOT NULL AUTO_INCREMENT, 
tutorial_title VARCHAR(100) NOT NULL,     tutorial_author VARCHAR(40) NOT NULL,     submission_date DATE,     PRIMARY KEY ( tutorial_id )    )

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Delete_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=DELETE FROM tutorials_tbl WHERE tutorial_id=2NOT NULL,     
tutorial_author VARCHAR(40) NOT NULL,     submission_date DATE,     PRIMARY KEY ( tutorial_id )    

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Insert_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=INSERT INTO tutorials_tbl       (tutorial_title, tutorial_author, submission_date)  
    VALUES      ("Learn Java", "John Smith", NOW())

<134>Apr 29 15:14:54 abc-desktop java: [MYSQL_Drop_table]: [eventSeverity]=PHL_INFO, [eventTime]=2013-04-29 15:14:54, [rptIp]=172.16.22.227, 
[srcIp]=172.16.22.227, [user]=admin, [actionName]=Query, [msg]=DROP table sliutable