How to Clear MySQL Cache: Query Cache, Table Cache & InnoDB Buffer Pool (5.7 & 8.0 Guide)

1. Introduction

MySQL is one of the most widely used databases in web services and systems around the world. To improve performance and reduce server load, MySQL provides various caching mechanisms. However, in development and production environments, issues such as “the latest data is not reflected due to cache” or “old cache interferes with configuration changes or debugging” are not uncommon.

In such situations, clearing (deleting or resetting) the MySQL cache becomes extremely useful. For example, this operation is effective when you want to immediately verify updated data in a test environment, clear cache before taking a snapshot, or forcibly reset unintentionally remaining cached data.

This article is intended for those interested in “mysql cache clear” and explains the characteristics and clearing methods for each type of cache in an easy-to-understand manner. In addition, we cover differences in cache specifications depending on the MySQL version, operational considerations, common questions, and their solutions.

By properly understanding how caching works and how to clear it, you will be able to use MySQL more stably and efficiently.

2. Cache Architecture by MySQL Version

MySQL’s caching features differ significantly depending on the version. In particular, the design philosophy of caching changed between MySQL 5.7 and earlier and MySQL 8.0 and later. Here, we summarize the main types of caches used in MySQL and the differences by version.

2.1 Query Cache (MySQL 5.7 and Earlier)

In MySQL 5.7 and earlier versions, a feature called “Query Cache” was included by default. This mechanism stores executed SELECT statements and their result sets in memory, allowing the same query to return results quickly if executed again. While it can be effective in simple web services, in environments with frequent data updates, the cache is invalidated often, which may instead cause performance degradation.

2.2 InnoDB Buffer Pool (MySQL 5.5–8.0)

Since MySQL 5.5, and especially in MySQL 8.0, the “InnoDB Buffer Pool” has become the central caching mechanism. This feature allows the InnoDB storage engine to retain data and index information in memory to reduce disk I/O and improve performance. Unlike the Query Cache, the buffer pool caches data at the table or row level, providing stable performance even in large-scale systems or environments with frequent updates.

2.3 Table Cache and Other Caches

In addition, MySQL includes several other caching mechanisms such as “Table Cache (table_open_cache),” “Thread Cache,” and “User Variable Cache.” In particular, the Table Cache efficiently manages frequently accessed tables and is available in all versions.

2.4 Summary of Cache Specifications by Version

• MySQL 5.7 and earlier: Query Cache + InnoDB Buffer + Table Cache
• MySQL 8.0 and later: Query Cache removed, InnoDB Buffer Pool is primary, Table Cache continues

As shown above, the types and roles of caches change depending on the MySQL version. Therefore, it is important to understand the appropriate measures for the version you are using.

3. How to Clear the Query Cache (For MySQL 5.7 and Earlier)

If you are using MySQL 5.7 or earlier, the “Query Cache” feature is often enabled. In this section, we explain how the Query Cache works, how to clear it, and important precautions.

3.1 What Is the Query Cache?

The Query Cache stores SELECT statements and their result sets in memory, and when the same query is executed again, it immediately returns the result from the cache. It is effective mainly for websites or small-scale applications that frequently reference static data. However, in environments where data is updated frequently, the cache becomes less effective, so caution is required.

3.2 Commands to Clear the Query Cache

To clear the Query Cache, the following two commands are mainly used.

• RESET QUERY CACHE; This deletes all entries in the Query Cache. Because all cached queries and result sets are removed, it is useful when you want to completely eliminate cache effects.
• FLUSH QUERY CACHE; This deletes only “unused” entries in the cache. It is suitable when you want to clean up only old entries that have already been invalidated.

3.3 How to Run the Commands

Run the commands from a MySQL client or an administration tool (such as phpMyAdmin) as follows.

RESET QUERY CACHE;

Or:

FLUSH QUERY CACHE;

Some cases require privileges. If you get a permission error, rerun the command with administrative privileges (such as root).

3.4 Precautions and Best Practices

• Clearing the Query Cache affects the entire server, so execute carefully in a production environment.
• After clearing the cache, performance may temporarily decrease.
• In MySQL 8.0 and later, the Query Cache feature has been removed, so these commands cannot be used.

By clearing the Query Cache effectively, you can prevent unintended cache effects and enable accurate verification of the latest data and correct behavior.

4. Clearing the Table Cache and Related Caches

MySQL includes various caching mechanisms besides the Query Cache. In particular, the “Table Cache” is used to manage frequently accessed tables efficiently. This chapter explains how to clear the Table Cache and related caches.

4.1 What Is the Table Cache?

The Table Cache (table_open_cache) is a mechanism where MySQL keeps tables open internally to avoid repeatedly loading them from disk on each access. It helps improve performance when many users or applications access the database concurrently.

4.2 How to Clear the Table Cache

To clear the Table Cache, you mainly use the FLUSH TABLES command.

FLUSH TABLES;

When you run this command, MySQL closes all currently open tables once, and reopens them as needed. This resets the Table Cache contents and is useful for applying table definition changes or resolving issues caused by caching.

4.3 Clearing Other Related Caches

MySQL provides commands to clear various caches in addition to the Table Cache. Examples include the following.

• FLUSH TABLES WITH READ LOCK; Closes all tables and places them in a locked state, which can be used for backups and similar operations.
• FLUSH PRIVILEGES; Clears the cache for privilege tables (user and privilege information) and applies privilege changes immediately.
• FLUSH STATUS; Resets statistics for various status variables (viewable via SHOW STATUS, etc.).

4.4 Clearing Multiple Caches at Once

Because the clearing command differs depending on the cache type, if you want to reset multiple caches together, run each command in sequence. For example, in a development or test environment where you want to “reset all caches once,” you can combine commands like this:

FLUSH TABLES;
RESET QUERY CACHE;

(This is for MySQL 5.7 and earlier; RESET QUERY CACHE is not available in MySQL 8.0 and later.)

4.5 Notes

• Clearing the Table Cache may temporarily affect performance on systems with many open tables.
• In production environments, confirm the scope of impact in advance before running these commands.
• Depending on privileges, some commands may not be executable. If an error is displayed, rerun using a user with the appropriate privileges.

By clearing the Table Cache and related caches appropriately, you can make MySQL operations more stable and simplify troubleshooting.

5. How to “Clear” the InnoDB Buffer Pool (For MySQL 8.0)

In MySQL 8.0 and later, the Query Cache feature has been removed, and the “InnoDB Buffer Pool” plays the central role in caching. However, unlike the traditional Query Cache, the InnoDB Buffer Pool cannot be “cleared” with a single command. This chapter explains practical approaches to effectively clearing the InnoDB Buffer Pool and important precautions.

5.1 What Is the InnoDB Buffer Pool?

The InnoDB Buffer Pool is a mechanism that caches table data, indexes, and frequently accessed data pages in memory to reduce disk I/O and improve performance. In MySQL 8.0, this buffer pool is the key component for performance optimization.

5.2 How to Clear the Buffer Pool and Alternative Methods

There is no standard MySQL command that directly “clears” the InnoDB Buffer Pool. The main approaches are as follows.

• Restarting the MySQL Server Stopping and restarting the server initializes the buffer pool contents, effectively clearing all cached data. However, careful operation is required in production environments.
• Temporarily Changing the Buffer Pool Size By setting innodb_buffer_pool_size to a smaller value and restarting MySQL, then restoring it to the original value and restarting again, you can also initialize the buffer pool.
• Flushing Individual Buffer Pool Pages The following command writes modified (dirty) pages from the buffer pool to disk, but it does not completely clear the cache itself.

FLUSH TABLES;

5.3 Practical Example of Clearing the Buffer Pool

For example, in a test environment where you want to clear the buffer pool, follow these steps:

1. Stop the MySQL server.
2. Adjust innodb_buffer_pool_size if necessary.
3. Start the MySQL server.

This resets the buffer pool in memory, resulting in a state where all cached information has been removed.

5.4 Precautions and Operational Tips

• Initializing the buffer pool (via server restart) temporarily stops the service, so prior coordination and notification are essential in production environments.
• Immediately after clearing the buffer pool, disk access increases and performance may temporarily degrade. Be cautious in high-traffic systems.
• If restarting is not possible, prepare a separate test or development environment for verification work.

By fully understanding how the InnoDB Buffer Pool works and performing resets at appropriate times, you can achieve stable operations even in MySQL 8.0 and later environments.

6. Cache Control Using Third-Party Tools

MySQL cache management can be made more efficient and easier to visualize by using third-party tools and utilities in addition to standard commands. Here, we introduce representative tools and practical use cases.

6.1 Monitoring and Optimizing Cache with MySQLTuner

“MySQLTuner” is a well-known diagnostic tool that analyzes the state of a MySQL server and automatically provides recommendations for performance improvements. It also presents usage statistics and recommended configuration values for caches such as the Query Cache, InnoDB Buffer Pool, and Table Cache.

How to Use MySQLTuner:

1. Install MySQLTuner on your server (distributed as a Perl script).
2. Run the following command to execute diagnostics.

perl mysqltuner.pl

3. The results display diagnostic items such as “Query cache” and “InnoDB Buffer Pool,” along with recommended parameter adjustments or suggestions to disable unnecessary caching features if needed.

6.2 Using Percona Toolkit

“Percona Toolkit” is a comprehensive set of tools useful for MySQL operations and performance analysis. For example, it can generate reports on buffer pool status and table cache usage with a single command, making it convenient for monitoring large-scale environments.

6.3 Examples of Monitoring and Visualization Tools

• phpMyAdmin / MySQL Workbench These management tools allow you to check the current cache status and execute some FLUSH commands via a GUI. They are user-friendly and suitable for monitoring and minor cache control tasks.
• Zabbix or Prometheus These tools monitor server memory usage and InnoDB buffer pool utilization, enabling real-time visualization of cache behavior and resource constraints. They are useful for early anomaly detection and automated alerts.

6.4 Precautions When Using Third-Party Tools

• Running these tools may require administrative privileges or specific MySQL user permissions.
• Before using tools in production, it is recommended to verify their behavior in a test environment.
• Some tools may temporarily increase server load, so consider performing operations during off-peak hours.

By effectively leveraging third-party tools, you can visualize MySQL’s cache state and perform timely clearing and optimization.

7. Risks and Precautions

While clearing MySQL caches is extremely useful, performing it at the wrong time or in the wrong way can lead to unexpected issues or performance degradation. This chapter explains the risks and precautions you must understand before clearing caches.

7.1 Impact on Performance

After clearing caches, the load on the MySQL server may temporarily increase. In particular, if large caches such as the InnoDB Buffer Pool or Table Cache are cleared, all in-memory data is lost. As a result, disk I/O occurs for each client request, which may significantly reduce response speed.

7.2 Be Extremely Careful in Production Environments

When clearing caches in a production system, special caution is required. Executing commands during peak traffic hours can negatively impact overall system performance and may cause service interruptions or slow responses. In production environments, sufficient validation, prior coordination, backups, and careful timing are essential.

7.3 Consider Data Updates and Consistency

Depending on the timing of cache clearing, data inconsistencies or unintended application behavior may occur. For example, if table structures are modified or batch processing is in progress when caches are cleared, query results or application logic may behave unexpectedly.

7.4 Avoid Unnecessary Cache Clearing

Avoid the practice of “just clearing the cache for now.” MySQL caches are designed to reduce server load and improve processing speed. Frequent clearing may instead make performance unstable. Always ensure that cache clearing is performed only when truly necessary.

7.5 Permission and Security Considerations

Cache-clearing commands and tools require sufficient privileges. Running them with overly privileged users may risk affecting other critical settings or data. Follow security best practices, such as using minimally privileged users and recording execution logs.

By understanding these risks and precautions, you can maintain MySQL performance and stability safely and efficiently.

8. Procedure Summary (Quick Reference Table)

Below is a quick reference table summarizing the MySQL cache-clearing procedures introduced so far, organized by cache type and MySQL version. Use this table during operations or troubleshooting.

Quick Explanation:

• RESET QUERY CACHE; Resets the entire Query Cache (MySQL 5.7 and earlier only).
• FLUSH QUERY CACHE; Deletes only invalidated and unused Query Cache entries.
• FLUSH TABLES; Closes all open tables once and resets the Table Cache.
• FLUSH PRIVILEGES; Applies user privilege changes immediately.
• FLUSH STATUS; Resets various status statistics, useful during performance analysis.
• Initialize InnoDB Buffer Pool Achieved indirectly via server restart or modifying innodb_buffer_pool_size (MySQL 8.0 and later).

By using this table, you can quickly select the appropriate cache-clearing procedure based on your environment and objectives.

9. FAQ (Frequently Asked Questions)

Below are common questions about clearing MySQL caches that are frequently raised by operators and developers, along with their answers. Use these as a practical reference.

Q1. Are the Query Cache and the InnoDB Buffer Pool the same?

A. No, they are different mechanisms. The Query Cache stores SQL query result sets themselves, whereas the InnoDB Buffer Pool keeps table data and indexes in memory. Their purposes and internal mechanisms are completely different, so do not confuse them.

Q2. How much does performance drop after clearing the cache?

A. Performance temporarily decreases. Especially in environments with large caches, disk access increases during initial query execution, which may significantly reduce response speed. However, performance gradually recovers as the cache is rebuilt.

Q3. Is it safe to clear caches in a production environment?

A. It is generally not recommended. Clearing caches in production directly affects performance and service stability. Sufficient testing, preparation, and timing adjustments are essential. If you must proceed, make sure to notify stakeholders in advance and take backups.

Q4. Can I enable Query Cache in MySQL 8.0?

A. No. The Query Cache feature has been completely removed in MySQL 8.0. If you need Query Cache functionality, you must use MySQL 5.7 or earlier.

Q5. Can I clear caches in cloud services such as AWS RDS or Cloud SQL?

A. Yes, but there may be restrictions depending on the service. For example, some FLUSH commands or server restart operations may be limited in RDS. Always check official documentation and management console guidelines before proceeding.

Q6. Is there a way to clear caches automatically?

A. You can automate periodic execution of FLUSH commands using shell scripts or cron jobs. However, frequent cache clearing is not recommended. Use automation only when necessary, such as during scheduled maintenance.

By reviewing these FAQs in advance, you can resolve operational concerns and perform MySQL cache-clearing tasks with greater confidence.

10. Summary and Best Practices

Clearing MySQL caches is an essential operation in development and production environments. In this article, we covered cache types by MySQL version, clearing methods, precautions, and frequently asked questions. Based on this information, here are the key best practices.

10.1 Actively Use Cache Clearing in Test Environments

During testing, validation, and debugging, you often need to remove cache effects to verify actual behavior. Use cache-clearing commands appropriately to improve reproducibility and testing accuracy.

10.2 Operate Carefully in Production

Clearing caches in production can significantly impact performance and stability. Always evaluate the scope of impact and timing before execution. Notify relevant parties and take backups when necessary. Avoid performing cache clearing indiscriminately—execute it only when truly required.

10.3 Understand Versions and Cache Types Correctly

Because MySQL caching mechanisms differ by version, it is important to understand which caches and clearing methods apply to your environment. Each cache type has different commands and impact scopes, so choose the most appropriate procedure based on your objective.

10.4 Leverage Third-Party and Monitoring Tools

Tools such as MySQLTuner and Percona Toolkit help evaluate server conditions and cache utilization objectively. Utilize visualization and automation tools to support advanced operations and prevent issues proactively.

10.5 Final Thoughts

When properly executed, MySQL cache clearing greatly contributes to stable database operations, troubleshooting, and performance improvement. Use this guide to apply the most suitable cache-clearing methods for your environment and achieve high-quality system management.