Change Is Inevitable

Kedar Vaijanapurkar's Blog for MySQL, technology and various subjects

Killer Blog for MySQL Administrators

Kedar

This is by far the most killer MySQL blog that I wanted to write for really long as a part of a Killer DBA series. Time is hard to find, so let’s just start killing.

Warning: Always exercise caution when killing MySQL processes. Improper termination can lead to data loss, corruption, or service disruption.

1. Killing MySQL Server

Sometimes, you need to kill the MySQL server itself—either for maintenance, troubleshooting, or performance tuning. Here are some common ways to do this.

1.1 Stop MySQL using MySQL’s Native Shutdown Commands

The most straightforward way to gracefully shut down MySQL is using mysqladmin. This command will cleanly stop the MySQL server, allowing it to close all connections, flush buffers, and perform other necessary cleanup tasks.

mysqladmin -u root -p shutdown

1.2 Using Systemctl / Service-based Systems

For systems that use systemd or init.d for process management, you can stop MySQL with the following commands:

# For systemd-based systems:
sudo systemctl status mysqld
sudo systemctl stop mysqld

# For init.d-based systems:
sudo service mysqld stop
sudo service mysqld status

# Using the older init script (still in some systems):
sudo /etc/init.d/mysqld stop
sudo /etc/init.d/mysqld status

Ensure to verify the status of the process and verify the error log to confirm the shutdown reported no errors.

1.3 Graceful Killing (SIGTERM) Using PID

Want other ways? You might need to stop MySQL “gracefully” by sending the SIGTERM signal to the process, allowing MySQL catch it and to shut down in an orderly manner.

sudo kill $(pidof mysqld)

1.4 Forceful Killing (SIGKILL) Using PID

If MySQL is not responding to a graceful shutdown, you can forcefully terminate it by sending the SIGKILL signal. This should be used with caution as it can lead to data loss, corruption, or other unexpected consequences.

sudo kill -9 $(pidof mysqld)

2. Killing of a Query / Thread in MySQL

In many cases, especially during periods of high load, MySQL queries can become slow, stuck, or problematic, consuming excessive resources. Killing these queries or threads can help restore performance.

2.1 Killing one Query Only

This command will terminate only the currently executing query in the thread, without closing the session itself. It is useful when you want to stop a long-running query without disconnecting the client. All you need is the thread id from the processlist.

KILL QUERY <thread_id>;

2.2 Killing the Connection

If you need to terminate a specific connection, use the KILL CONNECTION command. This command kills both the query and the connection, which will also free up resources held by that session.

KILL <thread_id>;
or
KILL CONNECTION <thread_id>;

2.3 Killing Using mysqladmin

You can also kill a query using mysqladmin. This is useful when you’re operating from the command line or need to automate tasks:

mysqladmin -u -p kill <thread_id>

2.4 Killing Using Queries and Procedures

You can generate a list of queries or connections that have been running for too long and automatically generate the kill commands for them. For example, this query will create a script to kill all connections running for more than 300 seconds.

SELECT CONCAT('KILL ', id, ';') INTO OUTFILE '/tmp/kill_threads.sql'
FROM information_schema.processlist
WHERE user="username" and time > 300;

SOURCE /tmp/kill_threads.sql;

2.5 Using MySQL’s Native Event Scheduler

For automated management, you can schedule MySQL to kill long-running queries every 5 minutes using the event scheduler. This is particularly useful for background tasks or cleanup. That said, these things remain “hidden” and you may end-up wondering who’s the killer!

CREATE EVENT i_am_a_killer_dba
ON SCHEDULE EVERY 5 MINUTE
DO
  DELETE FROM information_schema.processlist WHERE user="username" and time > 300;
# This will automatically check for any query running for more than 300 seconds for user "username" and kill it.

2.6 Using Percona Toolkit’s pt-kill

pt-kill from the Percona Toolkit is a powerful tool for killing queries that match certain criteria, such as a specific command or time threshold. For instance, to kill queries running for more than 30 seconds:

pt-kill --host localhost --user root --password root --match-command Query --busy-time 30 --kill

2.7 Killing System Threads – Replication/Parallel Replication

In MySQL replication, both the SQL thread and IO thread are crucial. Sometimes, you may need to kill or stop these threads if there is a replication issue.

# Stop replication and kill both threads:
STOP REPLICA;

# Stop individual SQL and IO threads:
STOP REPLICA IO_THREAD;
STOP REPLICA SQL_THREAD;

In situations where you are managing replication or other system processes, you can kill a specific system thread by identifying it through the SHOW PROCESSLIST command and using the earlier “KILL” commands. It comes handy at times when STOP commands doesn’t respond.

2.8 Graceful Stop of Parallel Replication

If you’re using parallel replication and need to stop it gracefully, you can follow these steps:

STOP REPLICA;
START REPLICA UNTIL SQL_AFTER_MTS_GAPS;
STOP REPLICA;
START REPLICA;

This allows you to control the flow of replication and ensure there are no inconsistencies when stopping parallel replication.

3. Killing in RDS or Aurora

In managed environments like Amazon RDS or Aurora, there are special procedures to kill queries, connections, or even stop replication without impacting the entire system.

3.1 Killing Replication

If you need to stop replication in RDS/Aurora, there are specific stored procedures to manage replication:

CALL mysql.rds_stop_replication;

These commands allow you to safely stop replication threads in managed environments.

3.2 Killing a Query

To kill a specific query in RDS/Aurora, use the rds_kill_query procedure:

CALL mysql.rds_kill_query();

3.3 Killing a Connection

To terminate a connection in RDS/Aurora:

CALL mysql.rds_kill();

3.4 Mass Killing in AWS RDS / Aurora

For mass killing of queries in RDS/Aurora, you can still use Percona Toolkit’s pt-kill:

pt-kill --host localhost --user root --password root --match-command Query --busy-time 30 --kill -rds

This allows you to perform mass killings in environments where manual intervention isn’t always feasible.

4. Killing ALTER TABLE command

(Yes, a separate section for this one)

This is a special kind of daring kill but fear not, you can KILL the ongoing ALTER commands. It will just take “some” time to cleanup depending on how large your table is and how far you have gone! You might find yourself in such situations where ALTER has taken MDL and processlist is flooded with WAITING FOR TABLE LOCK. Evaluate well before making the kill.

You can check progress of the command before deciding if you really want to go for it…

SELECT EVENT_NAME, ROUND(WORK_COMPLETED/WORK_ESTIMATED*100,2) AS completed_prc, NESTING_EVENT_TYPE FROM performance_schema.events_stages_current;

Ref: https://dev.mysql.com/doc/refman/8.4/en/monitor-alter-table-performance-schema.html

4.1 Killing ALTER TABLE in GALERA Cluster

A killer DBA doesn’t kill ALTERs in Galera. Can he?

Conclusion

Killing processes or queries in MySQL is an essential part of a DBA’s toolkit. But with Great Power comes with Great Responsibility. Understanding when and why to kill a process, whether it’s a runaway query, a replication issue, or a MySQL server that’s hung, can be critical to maintaining performance and uptime.

Some Things to Keep in Mind:

That’s all for now. Hopefully, this “killer” blog helps you out in your day-to-day activities as a MySQL DBA! Keep calm, and keep killing problems, not databases!

PS

I know you have one, show me your killer command in the comments.

Exit mobile version