Master MySQL Optimization: Essential Commands, Logs, and Tools

1. SQL Statement Optimization Steps

Start by examining the MySQL server status and configuration: show status – view current connection status. show global status – view server-wide status since startup. show global variables – list all configuration variables.

Collect statistics on DML operations: show global status like "com_insert%" – count INSERT statements. show global status like "com_delete%" – count DELETE statements. show global status like "com_update%" – count UPDATE statements. show global status like "com_select%" – count SELECT statements.

Monitor InnoDB row operations: show global status like "innodb_rows%" Check connection counts and uptime: show global status like "connection%" – total connections (successful and failed). show global status like "uptime%" – server uptime in seconds.

Inspect slow‑query statistics and configuration: show global status like "%slow%" – number of slow queries. show global variables like "%slow%" – verify that log_slow_queries and slow_query_log are enabled. show global variables like "%long_query%" – view the slow‑query time threshold.

Enable the general query log by editing my.cnf:

general_log = on
general_log_file = /home/mysql-run/mysql.log

Enable the slow‑query log similarly and verify its path.

2. Common MySQL Management Commands

mysql> \s

– display server information. show engines; – list available storage engines. show plugins; – list installed plugins. show processlist; – view active connections and queries.

3. Using the mysqldump Tool

The executable resides at /usr/local/mysql/bin/mysqldump. Typical usages include:

Backup all databases: mysqldump -u root -p --all-databases > all.sql Backup a single database (e.g., smudge): mysqldump -u root -p smudge > smudge.sql Export a specific table with its structure only:

mysqldump -u root -p --no-data smudge cs_line > cs_line_schema.sql

Export a table filtered by a condition (e.g., line_id = 6) and include its structure:

mysqldump -u root -p smudge cs_line --where="line_id=6" > cs_line_subset.sql

Generate a new binary log file during backup with -F.

4. Analyzing Slow Queries with mysqldumpslow

Typical options: -s – sort method (c, t, l, r for count, time, lock‑time, rows). Adding a before the letter sorts in descending order. -t – limit output to the top N entries. -g – apply a case‑insensitive regular‑expression filter.

Examples:

Sort by rows returned (descending):

mysqldumpslow -s -r /home/mysql-run/mysql_slow.log

Show the single most frequent slow query:

mysqldumpslow -t 1 /home/mysql-run/mysql_slow.log

Filter out SELECT statements and display the first two results:

mysqldumpslow -lt slow /home/mysql-run/mysql_slow.log | grep -v "SELECT" | head -n 2

5. Using mysqlsla for Detailed SLA Analysis

After installing the tool (binary at /usr/local/bin/mysqlsla), ensure required Perl modules are present:

DBI‑1.608.tar.gz

DBD‑mysql‑4.013.tar.gz

mysqlsla‑2.03.tar.gz

Typical usage to filter slow queries from the smudge database:

mysqlsla -lt slow /home/mysql-run/mysql_slow.log

Further filtering (e.g., exclude SELECT statements and limit to two entries):

mysqlsla -lt slow /home/mysql-run/mysql_slow.log | grep -v "SELECT" | head -n 2

These commands provide a concise view of SLA‑critical queries, helping pinpoint performance bottlenecks.