[google-cloud-sql-discuss] Export Google Cloud SQL slow log to ELK stack

Hello guys! I stumbled upon an issue and decided to ask for advice and eventually find someone with the same business need (and problem).

Summary - we've recently migrated the SQL service of one of our clients from a self-hosted MySQL to the Google CloudSQL MySQL 5.7 service and we're now looking for a way to allow our client to access/view and analyze the MySQL slow logs in their self-hosted ELK stack. 

This was not a problem before as we had access to the SQL service slow log file and managed to "follow", export and index those logs in the Elasticsearch. A quick example of how a particular Elasticsearch MySQL slow log Single Document looked like before is shown below:

https://prnt.sc/sjitys

Please note that every single document contained:

• Time

• User@Host

• Query_time

• timestamp

• Select/Insert/Update/Delete QUERY

and the ability to analyze data based on lock_time, rows_queried, rows_affected, rows_returned and etc. 

Here is an example of how the MySQL slow logs are displayed in the GCP Log viewer to which our client doesn't have access:

https://user-images.githubusercontent.com/9348708/80950753-2418bf80-8dff-11ea-9a00-3f5131b5e0f3.png

So our goal is to stream the MySQL slow logs to the ELK stack in a way similar to the one used before. 

To achieve our goal we've tried to pull the GCP MySQL slow logs via Pub/Sub exporter (https://cloud.google.com/logging/docs/export) and stream logs to the ELK stack. For that purpose, we did the following:

1. created a log sink (Log Router) by using the filter below :

resource.type="cloudsql_database" 

logName="projects/XXX/logs/cloudsql.googleapis.com%2Fmysql-slow.log"

and exported to Google's Pub/Sub sink service

2. on a Google Computer Engine VM, we installed and configured file exporting service named pubsubbeat (service is similar to the filebeat's pubsub input method) to stream the SQL slow logs from GCP to a file on the VM in question

3. configured a filebeat service to follow the logs exported by GCP on the VM and by using `include_lines: ['textPayload']` to include only the important information within each JSON object pulled by GCP

NB: GCP MySQL slow logs are accessed via google-fluentd agent and are represented using a single data type, LogEntry, which defines certain common data for all log entries as well as carrying individual payloads. Format is JSON and each log line is encapsulated to separate JSON object. Example: https://prnt.sc/sep2zk

Immediate problem - Pub/Sub makes some tradeoffs to achieve its high availability and throughput and unfortunately get messages out of order is one of them - in other words, the order of MySQL slow logs is mixed and we cannot properly encapsulate each separate slow log object by defining that it is starting with the "^# Time" string - instead, we have the following format: https://prnt.sc/seoztj

So it would be of great help if someone shares how are they exporting multi-line log files (or MySQL slow logs directly) from GCP to an external log-analyzing system (as ELK stack) so we can get a better understanding of what's the best approach in this situation?

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/486c791d-0ba9-4c41-b85a-59b1bf72c258%40googlegroups.com.