MySQL database server

MySQL is cross-platform database. Communication with MySQL is done using SQL, which is the only as-dialect of that language with some extensions. For it performance, and easy to deploy (can be installed on Linux, Windows and other OS), mainly thanks to the fact that is opensource software, this is one of the most used databases. Very popular and often deploying is a combination within Linux, MySQL, PHP and Apache as a default software for the web server.

The MySQL server has very limited options to enable verbose logging - there is only error log, that contains only the status and errors or u can turn on logging of all queries. But it causes agreat load on the server and degradation of the performance of MySQL. Therefore, we chose freely available add-ons, which can be installed to the MySQL and set it to log only the required types of queries.

Linux version

This procedure is based on add-in MySQL Audit via:

https://github.com/mcafee/mysql-audit

By default, the add-in is set up so that it masks the passwords in SQL queries.

MySQL configuration

First, download the appropriate version of the plugin for your MySQL server via:

https://github.com/mcafee/mysql-audit

https://bintray.com/mcafee/mysql-audit-plugin/release

Determine the folder location of MySQL plugin:

mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

Find out the location of the folder to store the Audit log:
```
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';
```
Unzip the file audit-plugin-mysql-5.x-x.x.x-xxx\\lib\\libaudit_plugin.so into the folder plugin_dir

Edit the MySQL configuration file my.cnf and add to the end of section [mysqld] the following:

# loading audit add-on
plugin-load=AUDIT=libaudit_plugin.so
# setting logging, recommended option
audit_record_cmds=process,drop,truncate,alter,trigger, \
grant,super,reload,shutdown,create_user,revoke;
# set log to JSON
audit_json_file=1
# the path to logfile
audit_json_log_file=/var/log/mysql/mysql-audit.json
# dont want uninstalling plugin
audit_uninstall_plugin=0
# rotation log file:
audit_json_file_flush=on

Restart MySQL service to load the new configuration
```
service mysql restart
```
Verify that the managed add-in loaded. In the list look for an item AUDIT
```
mysql> show plugins;
```
If you see an item AUDIT, the add-in is set up correctly.
Check if the Audit log contains the data:
```
cat /var/log/mysql/mysql-audit.json
```

Configuration to sending logs into Logmanager

In this section you are going to configure sending of Audit and Error logs into Logmanager.

Edit the configuration file /etc/syslog-ng/syslog-ng.conf

Check if the file contains the following parameters:

source s_src { system(); internal(); };
@include "/etc/syslog-ng/conf.d/"

Save the file
Create a file /etc/syslog-ng/conf.d/mysql.conf

Paste the following code into the file: (set ip address and port to Logmanager and set a custom path to log files):

# MySQL audit log
source s_mysqlaudit {
    file(
        "/var/log/mysql/mysql-audit.json",
        program_override("MySQL")
        flags(no-parse)
        default-facility(local0)
        default-priority(notice)
    );
};

destination d_mysqlaudit {
    tcp(
        "192.168.1.1"
        port(514)
        template("<133>$DATE $HOST $MSGHDR$MSG\n")
        template_escape(no)
    );
};

log {
    source(s_mysqlaudit);
    destination(d_mysqlaudit);
};


# MySQL error log
source s_mysqlerror {
    file(
        "/var/log/mysql/error.log",
        program_override("MySQL")
        flags(no-parse)
        default-facility(local0)
        default-priority(error)
    );
};

destination d_mysqlerror {
    tcp(
        "192.168.1.1"
        port(514)
        template("<131>$DATE $HOST $MSGHDR$MSG\n")
        template_escape(no)
    );
};

log {
    source(s_mysqlerror);
    destination(d_mysqlerror);
};

Save the file
Restart a service syslog-ng with this command:
```
/etc/init.d/syslog-ng restart
```

Troubleshooting

If the AUDIT is not in the list of modules, you must find the file error.log, from which you will discover the cause of the error. The path to the file you can find by command:

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';

Error may be caused by the wrong version of the add-in or missing data offset, which differ depending on the distribution of Linux (the offset is a uniqueidentification of the MySQL server).

A suitable offset can be found directly in the source code for this add-in via:

https://github.com/mcafee/mysql-audit/blob/master/src/audit_offsets.cc

An example for version MySQL 5.6.26

In the file audit_offsets.cc find the line with the version 5.6.26:

//offsets for: /mysqlrpm/5.6.26/usr/sbin/mysqld (5.6.26)
{"5.6.26","560ab8bd2c6513eac8283af1630e604a", 6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128},

Now edit the MySQL configuration file my.cnf:

audit_validate_checksum=OFF
audit_offsets=6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128

Restart MySQL server:
```
service mysql restart
```
Check if the Audit plugin load correctly and continue in configuration for sending logs into the Logmanager:
```
mysql> show plugins;
```
Follow the settings Configuration to sending logs into Logmanager

Windows version

In Windows, the add-on is available that allows logging in three levels - connect, query, table. It is recommended to use only the connect level (connection to the database) or the table level (table editing). This procedure is based on add-on MariaDB Audit Plugin, which is available for free, licensed under CC-BY-SA a GFDL:

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

MySQL configuration

Download the plugin for your version of the MySQL server.
Determine the folder location to store downloaded plugin:
```
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
```
Load the file server_audit.dll into the folder x:\\mysql server\\lib\\plugin

Edit the configuration file my.ini:

# path to the plugin
plugin_dir="x:\mysql server\lib\plugin"
# logging failed login attempts
log_warnings = 2
# turn on audit log
server_audit_logging=ON
# what is required to logging
server_audit_events=connect

Save the file and restart the MySQL service.
To verify that the plugin is running, use this command:
```
mysql> SHOW GLOBAL VARIABLES LIKE 'audit_server%';
```
If you find row with name server_audit_logging = ON, then plugin was started.
The Audit log is stored in MySQL data folder: x:\\mysql server\\data\\server_audit.log

Logmanager configuration

This procedure demonstrates the GUI configuration for all available messages.

Configure the Logmanager to watch the log files on Windows with MySQL server. Follow the Chapter Windows.
We find computer with MySQL server and we need to set following properties on Edit windows agent page:
- Set log type to value mysqlwindowsaudit.
- Into log source paste the path to the log file: x:\\mysql_server\\data\\server_audit.log
- Set log type to value mysqlerror.
- Into log source paste the path to the file x:\\mysql_server\\data\\pcname.err
Adding properties for MySQL server

After updating Windows Event Sender settings MySQL messages will be sent to the Logmanager.