MySQL database server

MySQL is a cross-platform database. Communication with MySQL is done using SQL, which is the only a dialect of that language with some extensions. For its performance, and ease of deployment (can be installed on Linux, Windowsy and other OS), mainly thanks to the fact that it is opensource software, this is one of the most used databases. Very popular and often deployed is a combination of 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 an error log, that contains only the status and errors, or you can turn on logging of all queries. But it causes a great 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 the MySQL add-in 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 the 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 the 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 for 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 unique identification 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

Find the line with the version 5.6.26 in the file audit_offsets.cc:

//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 loads correctly and continue in configuration for sending logs into Logmanager:
```
mysql> show plugins;
```
Follow the settings Configuration to sending logs into Logmanager

Windows version

In Windows, an add-on is available, that allows logging on 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 the 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 the 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
Find a computer with a MySQL server and set following properties on the Edit windows agent page:
- Set log type to mysqlwindowsaudit.
- In log source, paste the path to the log file: x:\\mysql_server\\data\\server_audit.log
- Set log type to mysqlerror.
- In log source, paste the path to the file x:\\mysql_server\\data\\pcname.err
Adding properties for MySQL server

Once the Windows Event Sender settings are updated, MySQL messages will be forwarded to Logmanager.