If you are also using Baota Panel 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 Baota Panel, because I have stumbled upon this several times. Today, I will simply share a solution with you.
The main issue causing the MySQL database in Baota Panel 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 MySQL version 8.0.0, query_cache_type, query_cache_size, and the entire Query Cache feature have been removed. This means that from MySQL 8.0 onwards, Query Cache is completely deprecated.
So if your MySQL data version is 8.x.x or higher and you have made adjustments using the optimization scheme in MySQL management of Baota Panel, then it is likely the reason why MySQL cannot start. Therefore, the solution is to set query_cache_size to 0 and comment out the configurations 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. You can also delete it; to be safe, you can search for anything starting with "query_cache" and comment those out as well. 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 layer 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 relevant 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 cache updates and invalidation logic in the code, optimize indexes, 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 Leixue Blog and any form of reproduction is prohibited.
Article link: https://zhangzifan.com/bt-mysql-restart.html