Boost MySQL DBA Productivity with Essential Open‑Source Tools

1. Practical SQL Diagnosis Tool

SQLTXPLAIN, developed by an Oracle expert, is a lightweight yet powerful utility for diagnosing SQL problems, supporting detection of optimizer issues, slow queries, and incorrect results.

2. Open‑Source SQL Optimization Platforms

2.1 SOAR

SOAR is an intelligent SQL optimization and rewrite tool created by Xiaomi's DBA team. It consists of a parser, integrated environment, optimization suggestions, rewrite logic, and a toolbox, allowing automatic SQL rewriting and providing actionable improvement tips.

Repository: https://github.com/XiaoMi/soar/

2.2 SQLAdvisor

SQLAdvisor, maintained by Meituan‑Dianping's DBA team, analyzes SQL statements and offers index‑optimization recommendations based on lexical parsing and analysis of conditions, aggregates, and join relationships.

Repository: https://github.com/Meituan-Dianping/SQLAdvisor

3. Managing MySQL with Percona Toolkit (PT)

Percona Toolkit (pt‑tools) provides a suite of utilities for MySQL administration, including replication consistency checks, duplicate‑index detection, I/O‑heavy table identification, slow‑log analysis, and online DDL.

3.1 PT Installation

<code>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</code>

3.2 Common PT Utilities

3.2.1 pt‑duplicate‑key‑checker

Detects duplicate indexes and suggests fixes.

<code>pt-duplicate-key-checker -h 127.0.0.1 -u root -p 123456 -d xuanyuan -t rs_hc_status</code>

-h: host to check

-u: database user

-p: password

-d: database name

-t: table name

3.2.2 pt‑kill (processlist matching)

Matches and kills threads based on command, state, info, host/IP, database, or user.

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

3.2.3 pt‑mysql‑summary

Displays a concise overview of MySQL, including startup time, version, data directory, connection count, replication status, table cache, InnoDB details, variables, and configuration file.

<code>pt-mysql-summary --host 127.0.0.1 --user root --password 123456</code>

3.2.4 pt‑query‑digest

Analyzes MySQL slow‑query logs to identify performance bottlenecks.

3.2.5 pt‑table‑checksum & pt‑table‑sync

pt‑table‑checksum verifies master‑slave data consistency; pt‑table‑sync repairs inconsistencies. They are often used together to ensure synchronized data.

pt‑table‑checksum Example

<code># 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</code>

pt‑table‑sync Example

When checksum results indicate mismatched rows, pt‑table‑sync can be run to synchronize the data.

After successful synchronization, master and slave databases become consistent.