0

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?

2
  • Would it be reasonable for your client to connect to Google CloudSQL MySQL 5.7 service for their slow log viewing requirements, using built in Google Cloud utilities? May 20, 2020 at 14:21
  • @Ivan, I’m facing a similar problem. Did you find a solution for this problem?
    – ChrisP
    Apr 13, 2022 at 13:13

1 Answer 1

0

This is correct. Pub/Sub does not guarantee the order of delivery. What you can do is order by the publishTime. There are some samples in the Pub/Sub documentation.

Alternatively, you can add a Dataflow Pipeline to the equation and have it order the data for you. You can then run a batch job as needed by your client’s business needs. This method will involve a lot of work to configure and it will cost significantly more.

I highly recommend that you look into some of the samples above and see if they suit your needs.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .