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
- 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.
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
- 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
- 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
- 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.
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.
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.