How to Identify and Kill MySQL Queries Using Command Line

#database #sql #devops #mysql

There comes a moment, usually at the worst possible time, when your application suddenly crawls. Pages take forever to load, API calls time out, and users start complaining. Knowing how to kill MySQL queries quickly can help restore performance and get your application back on track.

In my experience, the culprit behind most sudden MySQL slowdowns isn’t the server running out of memory or CPU, it’s almost always one or two queries that have gone rogue. A missing index on a frequently queried table. A bulk UPDATE running during peak traffic. A cron job that got stuck in a join it can’t finish.

When that happens, you need to get in, find the problem fast, and fix it, without panicking. The MySQL command-line client gives you everything you need to do exactly that. No third-party tools, no elaborate setup. Just SSH in and you’re already diagnosing.

In this guide, I’m going to walk you through the entire workflow: spotting slow queries, understanding why they’re slow, safely terminating them, and putting safeguards in place so the same problem doesn’t come back an hour later. Sound good? Let’s get into it.

TL;DR

Why Do MySQL Queries Slow Down or Get Stuck?

Before we dive into the command-line tools, it helps to know what you’re actually looking at. Most query performance problems fall into a few predictable categories.

Missing or unused indexes are the number one cause in my experience. When MySQL has to scan every row in a table to find a match, queries that should take milliseconds suddenly take minutes. This is especially common on tables that grow quickly, say, a logging table that wasn’t indexed on the timestamp column.
Large result sets are another frequent offender. A query like “SELECT * FROM orders WHERE status = 'pending'” looks harmless until that table hits a million rows. MySQL will happily try to return all of them, locking rows along the way.
Lock contention happens when two queries try to modify the same row at the same time. InnoDB handles this with row-level locking, but if one transaction holds a lock too long, others queue up behind it. In my testing, I found that a single UPDATE running without an index can lock an entire table for 30 seconds or more under moderate load.
Poorly written SQL, nested subqueries, SELECT * in joins, missing WHERE clauses, can also drag a server down. These don’t always show up in development because test data is small. They reveal themselves in production.

The point is: slow queries aren’t random. They follow patterns. Your job at the command line is to spot those patterns quickly.

Connecting to MySQL via Command Line

The first step is getting into MySQL. You’ll need shell access to your server and a MySQL user with the appropriate privileges. For most admin tasks, the MySQL root user is what I reach for, it has full visibility into every connection on the server.

Open your terminal and connect:

mysql -u root -p

Here’s what each part means:

mysql : the MySQL command-line client
-u root : log in as the MySQL root user (swap in a different user if needed)
-p : prompt for the password interactively (never type the password directly in the command, that’s a security risk)

Once you enter your password and hit Enter, you’ll see the MySQL monitor prompt:

mysql>

As shown below:

You’re in. Everything from here runs at this prompt. To exit at any time, just type exit or press Ctrl+C.

One thing I always double-check before proceeding: am I connected to the right database server?

If you’re managing multiple MySQL instances (say, a primary and a replica), it’s worth confirming which one you’re on with:

SELECT @@hostname;

Small step, but it saves you from killing a query on the wrong server, a mistake I’d rather not make twice.

Viewing Running Processes: SHOW FULL PROCESSLIST

Now that you’re in, the first real diagnostic command is “SHOW FULL PROCESSLIST”. This is your window into everything currently happening inside MySQL.

Run it like this:

SHOW FULL PROCESSLIST;

The “FULL” keyword is important; without it, MySQL truncates long queries in the output. You want to see the complete SQL statement, so you know exactly what you’re looking at. The official MySQL documentation covers all the details if you need a reference.