Skip to main content

Analysing and replaying MySQL database queries using tcpdump

Why

There are situations where you want to quickly enable query logging on a MySQL Database or trouble shoot queries hitting the Database server in real-time. Yes, you can enable the DB query log and there are other options available, however the script below has helped me in many cases as it is non intrusive and does not require changing the DB server, state or configuration in any way.

Limitations

The following only works if the DB traffic is not encrypted (no SSL/TLS transport enabled). Also this needs to be run directly on the DB server host (as root / admin). Please also be aware that this should be done on servers and data you own only.

Script

This script has been amended to suit my individual requirements.

#!/bin/sh
tcpdump -i any -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(ALTER|COMMIT|CREATE|DELETE|DROP|INSERT|SELECT|SET|UPDATE|ROLLBACK)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

The output of this script is similar to this:


$ ./log_mysql_queries.sh
tcpdump: listening on any, link-type LINUX_SLL (Linux cooked), capture size 262144 bytes

UPDATE `tasks` SET `nodes`=625 WHERE id=630393618;
SELECT * FROM `messages` WHERE status < 10;
UPDATE `tasks` SET `end_st`=1622947685 WHERE id=628262011 AND user_id=2341969;

344 packets received by filter
0 packets dropped by kernel

You can obviously also limit the filter to certain queries only, run the capture for a few minutes and analyse queries based on occurrence etc.

Replay

I have worked on very high throughput MySQL servers and there can be situations where a simple load test based on inserts or just loading and timing a DB dump is not sufficient in order to judge performance of a newly built system. 

Yes, you can enable replication, however this may mean downtime or configuration changes and replication does not replicate any search queries (and batches updates).

The script above is also great to replay existing traffic on a new cluster by just piping the output to a mysql client connected to the second DB server.


$ ./log_mysql_queries.sh | mysql -h new -unew -pnew somenewdb

Conclusion

Sometimes the easiest option is the best and I hope this post helps somebody in the future.



Labels:

Comments

Post a Comment

Popular posts from this blog

Manual Kubernetes TLS certificate renewal procedure

Intro Kubernetes utilizes TLS certificates to secure different levels of internal and external cluster communication.  This includes internal services like the apiserver, kubelet, scheduler and controller-manager etc. These TLS certificates are created during the initial cluster installation and are usually valid for 12 months. The cluster internal certificate authority (CA) certificate is valid for ten years. There are options available to automate certificate renewals, but they are not always utilised and these certs can become out of date. Updating certain certificates may require restarts of K8s components, which may not be fully automated either. If any of these certificates is outdated or expired, it will stop parts or all of your cluster from functioning correctly. Obviously this scenario should be avoided - especially in production environments. This blog entry focuses on manual renewals / re-creation of Kubernetes certificates. For example, the api-server certificate below...
Post a Comment
Read more

Enhancing Kubernetes Security: Enabling API Server Audit Logging

      In the ever-evolving landscape of container orchestration and management, Kubernetes remains at the forefront, empowering organizations to deploy, manage, and scale their containerized applications efficiently. However, as Kubernetes environments grow in complexity, ensuring robust security measures becomes paramount. One crucial aspect of Kubernetes security is audit logging, particularly concerning the Kubernetes API server, which serves as the primary point of interaction with the cluster. The Importance of Audit Logging Audit logging provides visibility into the activities and interactions within a Kubernetes cluster. By recording requests and responses made to the API server, audit logs offer valuable insights into user actions, system modifications, and potential security breaches. This transparency is essential for compliance with regulatory requirements such as HIPAA, GDPR, and PCI DSS, as well as for detecting and investigating security incidents. Understan...
Post a Comment
Read more