Boost MySQL DBA Productivity with Essential Open‑Source Tools

SQL Diagnostic Tools

SQLTXPLAIN

SQLTXPLAIN is a lightweight utility developed by Oracle experts for diagnosing SQL problems such as execution‑plan anomalies, slow queries, and incorrect results.

Open‑Source SQL Optimization Platforms

SOAR

SOAR is an intelligent SQL optimization and rewrite tool created by the Xiaomi DBA team. It includes a syntax parser, integrated environment, optimization suggestions, rewrite logic, and auxiliary tools. Repository: https://github.com/XiaoMi/soar/

SQLAdvisor

SQLAdvisor, maintained by the Meituan‑Dianping DBA team, analyzes SQL statements and generates index‑optimization advice using MySQL’s native lexical parser. Repository: https://github.com/Meituan-Dianping/SQLAdvisor

Percona Toolkit for MySQL Management

Installation

sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum list | grep percona-toolkit

pt-duplicate-key-checker

Detects duplicate indexes and suggests remedial actions.

pt-duplicate-key-checker -h 127.0.0.1 -u root -p 123456 -d xuanyuan -t rs_hc_status

-h host – MySQL server address

-u user – MySQL username

-p password – MySQL password

-d database – Target database

-t table – Target table

pt-kill

Terminates MySQL threads that match specified criteria, preventing long‑running or resource‑intensive queries from affecting production.

Key matching options:

--match-command : match one or more commands (separated by ‘|’)

--ignore-host / --match-host : filter by host/IP

--ignore-db / --match-db : filter by database

--ignore-user / --match-user : filter by MySQL user

Action parameters:

kill – kill the connection and exit

kill-query – kill only the query, leaving the thread alive

print – display matching statements

pt-kill --match-command Sleep --victims all --interval 10 --host=localhost --port=3306 --user=root --password=********* --kill --print -S

pt-mysql-summary

Provides a concise overview of a MySQL instance, including startup time, version, data directory, connection count, master‑slave status, processlist, variables, and configuration file.

pt-mysql-summary --host 127.0.0.1 --user root --password 123456

--host : MySQL host address

--user : MySQL username

--password : MySQL password

pt-query-digest

Analyzes MySQL slow‑query logs to identify the most expensive queries and suggest optimizations.

pt-table-checksum and pt-table-sync

pt-table-checksum verifies data consistency between master and replica servers. For ROW‑based replication, include the --no-check-binlog-format flag.

pt-table-checksum --nocheck-replication-filters --replicate=rep_test.checksums --no-check-binlog-format --databases=xuanyuan -h 192.168.1.181 -u root -p 123456 -P 3306

After identifying inconsistencies, repair them with pt-table-sync:

pt-table-sync --execute --sync-to-master h=192.168.1.181,u=root,p=123456,D=rep_test

Once synchronization completes, master and replica data become consistent.