Start your 14-day free trial today & Get 20% Off All Annual Managed ELK Plans
No Credit Card Required
Try Logit.io FreeAlready have an account? Sign In
MySQL Logs
Ship logs from MySQL to logstash
Configure Filebeat to ship logs from MySQL to Logstash and Elasticsearch.
Step 1 - Install Filebeat
deb (Debian/Ubuntu/Mint)
curl -L -O https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-oss-7.8.1-amd64.deb
sudo dpkg -i filebeat-oss-7.8.1-amd64.deb
rpm (CentOS/RHEL/Fedora)
curl -L -O https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-oss-7.8.1-x86_64.rpm
sudo rpm -vi filebeat-oss-7.8.1-x86_64.rpm
macOS
curl -L -O https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-oss-7.8.1-darwin-x86_64.tar.gz
tar xzvf filebeat-oss-7.8.1-darwin-x86_64.tar.gz
Windows
- Download the filebeat Windows zip file from the official downloads page.
- Extract the contents of the zip file into C:\Program Files.
- Rename the
filebeat-<version>-windows
directory tofilebeat
. - Open a PowerShell prompt as an Administrator (right-click the PowerShell icon and select Run As Administrator). If you are running Windows XP, you may need to download and install PowerShell.
- Run the following commands to install filebeat as a Windows service:
cd 'C:\Program Files\filebeat'
.\install-service-filebeat.ps1
PowerShell.exe -ExecutionPolicy UnRestricted -File .\install-service-filebeat.ps1
.
Step 2 - Enable the Mysql Module
There are several built in filebeat modules you can use. To enable the mysql module run.
deb/rpm
filebeat modules list
filebeat modules enable mysql
Windows
.\Filebeat modules enable mysql
The default configured paths for MySQL logs are as follows.
/var/log/mysql/mysql.log
/var/log/mysql/mysql-slow.log
c:\programdata\MySQL\MySQL Server*\error.log*
c:\programdata\MySQL\MySQL Server*\mysql-slow.log*
Additional module configuration can be done using the per module config files located in the modules.d folder, most commonly this would be to read logs from a non-default location
deb/rpm /etc/filebeat/modules.d/
mac/win <EXTRACTED_ARCHIVE>/modules.d/
Step 3 - Locate Configuration File
deb/rpm /etc/filebeat/filebeat.yml
Windows Open C:\Program Files\Filebeat\filebeat.yml
Step 4 - Configure output
We'll be shipping to Logstash so that we have the option to run filters before the data is indexed.
Comment out the elasticsearch output block.
## Comment out elasticsearch output
#output.elasticsearch:
# hosts: ["localhost:9200"]
Uncomment and change the logstash output to match below.
output.logstash:
hosts: ["your-logstash-host:your-ssl-port"]
loadbalance: true
ssl.enabled: true
Step 5 - Validate configuration
Let's check the configuration file is syntactically correct by running filebeat directly inside the terminal.
If the file is invalid, filebeat will print an error loading config file
error message with details on how to correct the problem.
deb/rpm
sudo filebeat -e -c /etc/filebeat/filebeat.yml
macOS
cd <EXTRACTED_ARCHIVE>
./filebeat -e -c filebeat.yml
Windows
cd <EXTRACTED_ARCHIVE>
.\filebeat.exe -e -c filebeat.yml
Step 6 - (Optional) Update Logstash Filters
All Logit stacks come pre-configured with popular Logstash filters. We would recommend that you add MySQL specific filters if you don't already have them, to ensure enhanced dashboards and modules work correctly.
Edit your Logstash filters by choosing Stack > Settings > Logstash Filters
if [fileset][module] == "mysql" {
if [fileset][name] == "error" {
grok {
match => { "message" => ["%{LOCALDATETIME:[mysql][error][timestamp]} (\[%{DATA:[mysql][error][level]}\] )?%{GREEDYDATA:[mysql][error][message]}",
"%{TIMESTAMP_ISO8601:[mysql][error][timestamp]} %{NUMBER:[mysql][error][thread_id]} \[%{DATA:[mysql][error][level]}\] %{GREEDYDATA:[mysql][error][message1]}",
"%{GREEDYDATA:[mysql][error][message2]}"] }
pattern_definitions => {
"LOCALDATETIME" => "[0-9]+ %{TIME}"
}
remove_field => "message"
}
mutate {
rename => { "[mysql][error][message1]" => "[mysql][error][message]" }
}
mutate {
rename => { "[mysql][error][message2]" => "[mysql][error][message]" }
}
date {
match => [ "[mysql][error][timestamp]", "ISO8601", "YYMMdd H:m:s" ]
remove_field => "[mysql][error][time]"
}
}
else if [fileset][name] == "slowlog" {
grok {
match => { "message" => ["^# User@Host: %{USER:[mysql][slowlog][user]}(\[[^\]]+\])? @ %{HOSTNAME:[mysql][slowlog][host]} \[(IP:[mysql][slowlog][ip])?\](\s*Id:\s* %{NUMBER:[mysql][slowlog][id]})?\n# Query_time: %{NUMBER:[mysql][slowlog][query_time][sec]}\s* Lock_time: %{NUMBER:[mysql][slowlog][lock_time][sec]}\s* Rows_sent: %{NUMBER:[mysql][slowlog][rows_sent]}\s* Rows_examined: %{NUMBER:[mysql][slowlog][rows_examined]}\n(SET timestamp=%{NUMBER:[mysql][slowlog][timestamp]};\n)?%{GREEDYMULTILINE:[mysql][slowlog][query]}"] }
pattern_definitions => {
"GREEDYMULTILINE" => "(.|\n)*"
}
remove_field => "message"
}
date {
match => [ "[mysql][slowlog][timestamp]", "UNIX" ]
}
mutate {
gsub => ["[mysql][slowlog][query]", "\n# Time: [0-9]+ [0-9][0-9]:[0-9][0-9]:[0-9][0-9](\\.[0-9]+)?$", ""]
}
}
}
Step 7 - Validate Configuration
Let's check the configuration file is syntactically correct.
Run from the extracted archive dir:
filebeat -e -c filebeat.yml
Step 8 - Start filebeat
Ok, time to start ingesting data!
deb/rpm
sudo systemctl enable filebeat
sudo systemctl start filebeat
Windows
Start-Service filebeat
Step 9 - MySQL Logging Overview
MySQL is an open source relational database management system created by Michael Widenius in 1995, this relational database runs across the majority of operating systems & is closely associated with its usage for web applications.
MySQL powers some of the world’s highest traffic sites, including Facebook, YouTube & Pinterest.
MySQL is able to work within an operating system to organise data into multiple data tables and show which data types may be related to each other. This helps the user to easily structure their data.
When used in this way, relational databases can be used to test database integrity, manage users and create backups of vital data.
MySQL Servers create numerous logs that you can use for troubleshooting and analysis, the most important ones include: Slow query logs, General query logs & error logs.
These logs default to a text file format, which can quickly become tedious to parse and process quickly to spot functional problems, opportunities to improve performance and identify security issues.
Our built in HA (high availability) MySQL log file analyser can be used to centralise your data & set up alerts to monitor your log data in real-time as well as deliver metrics for Kibana visualisations & reports with easily.