{"id":3420,"date":"2024-12-09T07:00:00","date_gmt":"2024-12-09T07:00:00","guid":{"rendered":"https:\/\/kedar.nitty-witty.com\/blog\/?p=3420"},"modified":"2024-12-08T07:11:50","modified_gmt":"2024-12-08T07:11:50","slug":"killer-blog-for-mysql-administrators","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/killer-blog-for-mysql-administrators","title":{"rendered":"Killer Blog for MySQL Administrators"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

Warning: Always exercise caution when killing MySQL processes. Improper termination can lead to data loss, corruption, or service disruption.<\/p>\n\n\n\n

1. Killing MySQL Server<\/h2>\n\n\n\n

Sometimes, you need to kill the MySQL server itself\u2014either for maintenance, troubleshooting, or performance tuning. Here are some common ways to do this.<\/p>\n\n\n\n

1.1 Stop MySQL using MySQL\u2019s Native Shutdown Commands<\/strong><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

mysqladmin -u root -p shutdown<\/code><\/pre>\n\n\n\n
1.2 Using Systemctl \/ Service-based Systems<\/strong><\/p>\n\n\n\n
For systems that use systemd or init.d for process management, you can stop MySQL with the following commands:<\/p>\n\n\n\n
# For systemd-based systems:\nsudo systemctl status mysqld\nsudo systemctl stop mysqld\n\n# For init.d-based systems:\nsudo service mysqld stop\nsudo service mysqld status\n\n# Using the older init script (still in some systems):\nsudo \/etc\/init.d\/mysqld stop\nsudo \/etc\/init.d\/mysqld status\n<\/code><\/pre>\n\n\n\n
Ensure to verify the status of the process and verify the error log to confirm the shutdown reported no errors.<\/p>\n\n\n\n
1.3 Graceful Killing (SIGTERM) Using PID<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
sudo kill $(pidof mysqld)<\/code><\/pre>\n\n\n\n
1.4 Forceful Killing (SIGKILL) Using PID<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
sudo kill -9 $(pidof mysqld)<\/code><\/pre>\n\n\n\n
2. Killing of a Query \/ Thread in MySQL<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
2.1 Killing one Query Only<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
KILL QUERY <thread_id>;<\/code><\/pre>\n\n\n\n
2.2 Killing the Connection<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
KILL <thread_id>;\nor\nKILL CONNECTION <thread_id>;<\/code><\/pre>\n\n\n\n
2.3 Killing Using mysqladmin<\/strong><\/p>\n\n\n\n
You can also kill a query using mysqladmin. This is useful when you\u2019re operating from the command line or need to automate tasks:<\/p>\n\n\n\n
mysqladmin -u -p kill <thread_id><\/code><\/pre>\n\n\n\n
2.4 Killing Using Queries and Procedures<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
SELECT CONCAT('KILL ', id, ';') INTO OUTFILE '\/tmp\/kill_threads.sql'\nFROM information_schema.processlist\nWHERE user=\"username\" and time > 300;\n\nSOURCE \/tmp\/kill_threads.sql;<\/code><\/pre>\n\n\n\n
2.5 Using MySQL\u2019s Native Event Scheduler<\/strong><\/p>\n\n\n\n
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!<\/p>\n\n\n\n
CREATE EVENT i_am_a_killer_dba\nON SCHEDULE EVERY 5 MINUTE\nDO\n  DELETE FROM information_schema.processlist WHERE user=\"username\" and time > 300;\n# This will automatically check for any query running for more than 300 seconds for user \"username\" and kill it.<\/code><\/pre>\n\n\n\n
2.6 Using Percona Toolkit\u2019s pt-kill<\/strong><\/p>\n\n\n\n
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:<\/p>\n\n\n\n
pt-kill --host localhost --user root --password root --match-command Query --busy-time 30 --kill<\/code><\/pre>\n\n\n\n
2.7 Killing System Threads \u2013 Replication\/Parallel Replication<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
# Stop replication and kill both threads:\nSTOP REPLICA;\n\n# Stop individual SQL and IO threads:\nSTOP REPLICA IO_THREAD;\nSTOP REPLICA SQL_THREAD;<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
2.8 Graceful Stop of Parallel Replication<\/strong><\/p>\n\n\n\n
If you’re using parallel replication and need to stop it gracefully, you can follow these steps:<\/p>\n\n\n\n
STOP REPLICA;\nSTART REPLICA UNTIL SQL_AFTER_MTS_GAPS;\nSTOP REPLICA;\nSTART REPLICA;<\/code><\/pre>\n\n\n\n
This allows you to control the flow of replication and ensure there are no inconsistencies when stopping parallel replication.<\/p>\n\n\n\n
3. Killing in RDS or Aurora<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
3.1 Killing Replication<\/strong><\/p>\n\n\n\n
If you need to stop replication in RDS\/Aurora, there are specific stored procedures to manage replication:<\/p>\n\n\n\n
CALL mysql.rds_stop_replication;<\/code><\/pre>\n\n\n\n
These commands allow you to safely stop replication threads in managed environments.<\/p>\n\n\n\n
3.2 Killing a Query<\/strong><\/p>\n\n\n\n
To kill a specific query in RDS\/Aurora, use the rds_kill_query procedure:<\/p>\n\n\n\n
CALL mysql.rds_kill_query();<\/code><\/pre>\n\n\n\n
3.3 Killing a Connection<\/strong><\/p>\n\n\n\n
To terminate a connection in RDS\/Aurora:<\/p>\n\n\n\n
CALL mysql.rds_kill();<\/code><\/pre>\n\n\n\n
3.4 Mass Killing in AWS RDS \/ Aurora<\/strong><\/p>\n\n\n\n
For mass killing of queries in RDS\/Aurora, you can still use Percona Toolkit\u2019s pt-kill:<\/p>\n\n\n\n
pt-kill --host localhost --user root --password root --match-command Query --busy-time 30 --kill -rds<\/code><\/pre>\n\n\n\n
This allows you to perform mass killings in environments where manual intervention isn\u2019t always feasible.<\/p>\n\n\n\n
4. Killing ALTER TABLE command<\/h2>\n\n\n\n
(Yes, a separate section for this one)<\/em><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
You can check progress of the command before deciding if you really want to go for it…<\/p>\n\n\n\n
SELECT EVENT_NAME, ROUND(WORK_COMPLETED\/WORK_ESTIMATED*100,2) AS completed_prc, NESTING_EVENT_TYPE FROM performance_schema.events_stages_current;<\/code><\/pre>\n\n\n\n
Ref: https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/monitor-alter-table-performance-schema.html<\/p>\n\n\n\n
4.1 Killing ALTER TABLE in GALERA Cluster<\/strong><\/p>\n\n\n\n
A killer DBA doesn’t kill ALTERs in Galera. Can he?<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Killing processes or queries in MySQL is an essential part of a DBA\u2019s toolkit. But with Great Power comes with Great Responsibility<\/strong>. Understanding when and why to kill a process, whether it’s a runaway query, a replication issue, or a MySQL server that\u2019s hung, can be critical to maintaining performance and uptime.<\/p>\n\n\n\n
Some Things to Keep in Mind:<\/p>\n\n\n\n
    \n
  • Graceful shutdowns (like SIGTERM) are always preferred over forceful kills (like SIGKILL), as they allow MySQL to clean up resources properly.<\/li>\n\n\n\n
  • Mass killings should be used with caution, especially in production environments. Test your commands, queries and conditions in where clause!! Automated tools like pt-kill should be configured carefully to avoid killing critical processes.<\/li>\n\n\n\n
  • Replication issues can sometimes be resolved by stopping and restarting threads, but be sure to understand the impact on your replication setup before making changes.<\/li>\n<\/ul>\n\n\n\n
    That\u2019s 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!<\/strong> 
    <\/p>\n\n\n\n
    PS<\/h2>\n\n\n\n
    I know you have one, show me your killer command in the comments.<\/p>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"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,…\n","protected":false},"author":1,"featured_media":3424,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[868,864,869,8,377,6],"tags":[1001,1051,1056,1050,1052,1053,1054,1006,1049,1048,833,75,1055],"class_list":{"0":"post-3420","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-aws-rds","8":"category-mysql-galera-cluster","9":"category-mariadb","10":"category-mysql","11":"category-mysql-articles","12":"category-technical","13":"tag-database-management","14":"tag-database-query-management","15":"tag-kill-query","16":"tag-killing-alter-operations","17":"tag-killing-mysql-processes","18":"tag-killing-queries","19":"tag-mysql-administration","20":"tag-mysql-best-practices","21":"tag-mysql-dba","22":"tag-mysql-dba-tasks","23":"tag-mysql-performance","24":"tag-mysql-server","25":"tag-mysql-server-management"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=3420"}],"version-history":[{"count":5,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3420\/revisions"}],"predecessor-version":[{"id":3427,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3420\/revisions\/3427"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/3424"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=3420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=3420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=3420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}