How to Reset MySQL Query Cache Without Restarting

MySQL provides a query cache that caches the results of recent and frequent SQL queries run on the server. It allows MySQL to quickly return results of frequent queries, without re-processing it, directly from query cache. But sometimes you may need to clear this query cache in case your query results are stale. For example, it may happen that you have multiple connections to your database and the data written by one connection is being read by another one. In such cases, you may need to reset MySQL query cache. In this article, we will learn how to reset MySQL query cache without restarting.

How to Reset MySQL Query Cache Without Restarting

It is very easy to reset MySQL query cache provided you have the RELOAD privileges. Just log into MySQL console and run the following query to reset MySQL query cache.

mysql> RESET QUERY CACHE;

Alternatively, you can defragment the query cache using the following command.

mysql> FLUSH QUERY CACHE;

The first query above will clear the query cache while the second query will only defrag the cache but retain query results.

In rare cases, you may find that even after resetting query cache and even restarting your MySQL server, it still serves old results. This may be because of memory disk caching. In such cases, run the following command from terminal to clear it.

$ sync && echo 3 | sudo tee /proc/sys/vm/drop_caches

And then login to MySQL console and reset query cache.

RESET QUERY CACHE;

Please note, query cache has been deprecated starting MySQL 8.0 so the above commands are applicable for MySQL<8.0.

In this article, we have learnt how to reset MySQL query cache. You can use it to ensure that your MySQL server returns fresh query results instead of previously stored data from query cache.

Also read:

How to Delete All Rows in MySQL Table Except Some
How to Fix Unknown Column in Field List in MySQL
How to Loop Through All Rows in MySQL Table
How to Combine Columns in MySQL
How to Get List of Stored Procedure & Functions