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

Deprecating Networking Ingress API version in Kubernetes 1.22

  Intro Kubernetes deprecates API versions over time. Usually this affects alpha and beta versions and only requires changing the apiVersion: line in your resource file to make it work. However with this Ingress object version change, additional changes are necessary. Basics For this post I am quickly creating a new cluster via Kind (Kubernetes in Docker) . Once done, we can see which API versions are supported by this cluster (version v1.21.1). $ kubectl api-versions | grep networking networking.k8s.io/v1 networking.k8s.io/v1beta1 Kubernetes automatically converts existing resources internally into different supported API versions. So if we create a new Ingress object with version v1beta1 on a recent cluster version, you will receive a deprecation warning - and the same Ingress object will exist both in version v1beta1 and v1. Create $ cat ingress_beta.yaml apiVersion: networking.k8s.io/v1beta1 kind: Ingress metadata:   name: clusterpirate-ingress spec:   rules:  ...
Post a Comment
Read more