Syslogs in MySQL

MySQL is an relational database management system (RDBMS) that implements SQL. It was originally designed for use with small-to-medium-sized databases, but it can now handle even very large amounts of stored data.

Note: The below configuration applies to MySQL 5.7 and MariaDB 10.1 and is supported on Centos 7.

Pre-requisites

1. Need to have administrative privileges.
2. Install MySQL Database
3. Install Rsyslog

Configuring syslog forwarding

1. Log into Centos machine with administrative privileges.
2. Connect to MySQL database and verify for the audit plugin.

3. server_audit.so plugin is required to enable auditing. So check the plugins directory and run the query show variables like ‘plugin_dir’;
If you do not find the plugin file inside your plugin’s directory, download it and place it in the plugins directory manually.

4. Install the plugin using command install plugin server_audit soname ‘server_audit.so’;

5. To confirm the plugin is installed and enabled, run the query show plugins;

6. Access my.cnf configuration file, available at /etc folder.

7. Edit using text editor # vi my.cnf file and enable the following:

8. To see the currently set variables with the command show global variables like "server_audit%";

9. To verify auditing is enabled, run the query: Show global status like ‘server_audit%’;

10. Access rsyslog.conf on folder /etc. Enable syslog using text editor #vi rsyslog.conf file.
NOTE: Syslog can be enabled using TCP or UDP protocol.

Syslog enabled with TCP:

Syslog enabled with UDP:

NOTE: The IP address should be that of Forwarder and the port “11616”.

11. Restart MySQL service # /etc/init.d/mysqld restart and connect to MySQL database. Run the queries and the logs generated will be forwarded to Forwarder through Syslog.