If you are also using 宝塔面板 and happen to encounter a situation where MySQL cannot start, then I guess you are likely using the "Performance Adjustment" feature in MySQL management within the 宝塔面板，because I have stumbled here several times. Today, I will simply share the solution with you.
The main issue causing the MySQL database in 宝塔面板 to fail to start or restart is due to the parameters query_cache_type and query_cache_size. MySQL 5.6 has Query Cache disabled by default but still supports configuration. MySQL 5.7 continues to support Query Cache but sets the default value to off. Starting from version 8.0.0, MySQL has removed query_cache_type, query_cache_size, and the entire Query Cache feature. In other words, Query Cache has been completely deprecated starting from MySQL 8.0.
So if your MySQL data version is 8.x.x or above, and you have made adjustments using the optimization scheme in the MySQL management of 宝塔面板，then it is likely the reason why MySQL cannot start. The solution is to set query_cache_size to 0 and comment out the configuration related to query_cache_type.
First, set query_cache_size to 0 through performance adjustment, then add a hash symbol in front of query_cache_type to comment it out. Actually, deleting it is also fine; to be safe, you can search for anything starting with "query_cache" and comment it out. Finally, starting the service should likely work.
Query Cache is a mechanism used to cache query results, which can improve read performance. However, with the modernization of databases and the popularity of other caching strategies (such as application-level caching, Redis, Memcached), the limitations of Query Cache have gradually become apparent. The implementation of Query Cache relies on global locks, and each write operation invalidates the cache, leading to decreased concurrency performance; every update to a data table clears the related cache, and frequently updated tables may render Query Cache counterproductive.
If the project is upgraded to MySQL 8.0 or higher and originally relied on Query Cache, consider using Redis, Memcached, or other in-memory databases to cache query results, or manage the cache update and invalidation logic in the code, optimize indexing, use appropriate query rewriting techniques, and store specific query results in memory within the application. These methods can better adapt to the needs of modern databases while avoiding the drawbacks of Query Cache.
Unless otherwise noted, all content is original from 泪雪博客 and any form of reproduction is prohibited.
Article link: https://zhangzifan.com/bt-mysql-restart.html