Trouble Enabling Query Caching on MySQL 5.6?

Question

Recently I discovered our MySQL 5.6 database is not using any query caching and I wanted to change that.

I added

[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

to a file called my-default.ini which is located under C:\Program Files\MySQL\MySQL Server 5.6. I could not find any other .ini file or .cnf file in any directory, and this computer does not have a C:\ProgramData directory where I usually would find it.

Running SHOW VARIABLES LIKE 'have_query_cache' does show have_query_cache YES. But SHOW VARIABLES LIKEquery_cache_typeshowsOFF. AndSHOW STATUS LIKE 'qcache%'` shows all zeros.

Is it possible my my.cnf is missing or deleted? Do I have to recreate it, or rename the my-default.ini to just my.ini? I think perhaps a coworker changed the file name or moved it not knowing what it was potentially.

End of the day, how can I ensure that query caching is enabled on my MySQL 5.6 server?

Running it on Windows Server 2003.

Answer 1

You are right. Your problem is actually very simple.

In Windows, mysqld is looking for my.ini, not my-default.ini

SUGGESTION

Open Windows Command Line (as Administrator) and run the following:

cd "C:\Program Files\MySQL\MySQL Server 5.6"
copy my-default.ini my.ini
net stop MySQL
net start MySQL

Keep in mind that MySQL was running with default settings and no my.ini

So, maybe the lines above

[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

should be the only lines in my.ini

Answer 2

Aside from the my.ini you can also modify the values directly in the console, from the MySQL manual:

SET GLOBAL query_cache_size = 1000000;
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_limit = 2M;

This way, you can check to see how well it performs, and adjust it accordingly, without having to modify your configuration and restarting the server every time.