MySQL Logs

Loggly provides the infrastructure to aggregate and normalize log events so they are available to explore interactively, build visualizations, or create threshold-based alerting. In general, any method to send logs from a system or application to an external source can be adapted to send logs to Loggly. The following instructions provide one scenario for sending logs to Loggly.

You can send your MySQL logs to Loggly over syslog. We will automatically parse fields from the MySQL slow query log as well, so you can easily view MySQL logs in Loggly. This script configures the rsyslog daemon to monitor the file for the newest events, and then sends them to Loggly. This guide assumes you’re running on Ubuntu. For alternatives, please see the Advanced Options section.

MySQL Logging Setup

1. Configure MySQL To Write Log Files

If you haven’t configured MySQL to write general and slow query log files, here is how you can do it. Open my.cnf configuration file of MySQL.

vim /etc/mysql/my.cnf

Uncomment / paste the following lines in it.

general_log_file = /var/log/mysql/mysql.log
general_log= 1
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1

2. Setup File Monitoring for MySQL

Download the File Monitoring script and configure your mysql log files to send logs to Loggly over syslog. Setup file monitoring for the /var/log/mysql directory which contains MySQL log files.

curl -O https://www.loggly.com/install/configure-file-monitoring.sh
sudo bash configure-file-monitoring.sh -a SUBDOMAIN -u USERNAME -f /var/log/mysql -l mysql -tag MySQL

Replace:

SUBDOMAIN: your account subdomain that you created when you signed up for Loggly
USERNAME: your Loggly username

Note: MySQL log file path may change in different distributions. Here are some default paths given to the MySQL log file in the different distributions.

Ubuntu: /var/log/mysql/mysql.log
RedHat: /var/log/mysqld.log

3. Verify Events

Search Loggly for events with the tag MySQL over the past hour. It may take few minutes to index the event. If if doesn’t work, see the troubleshooting section below.

tag:MySQL

Click on one of the logs to show a list of MySQL fields (see screenshot below). If you don’t see them, please check that you are using one of our automatically parsed formats.

MySQL Logs Example

Advanced MySQL Logging Options

Syslog from MySQL – You can configure mysql to log over syslog directly by adding this to the my.cnf file "[mysqld_safe] syslog" then restart the server
MySQL Automated Parsing – the lines we parse from the MySQL slow query log
File Monitoring – how to setup file monitoring on any file
MySQL Slow Query Logs – instructions to configure mysql.log file to hold slow query logs
RHEL and CentOS – the default file location is /var/log/mysqld.log

Troubleshooting MySQL Logging

If you don’t see any data show up in the verification step, then check for these common problems.

Wait a few minutes in case indexing needs to catch up
Check if mysql log file exists on the path you included in the arguments and there are recent logs in it
Troubleshooting Rsyslog if the files are being written but not being sent to Loggly
Search or post your own MySQL logs questions in the community forum.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.