Mysql – Trouble Enabling Query Caching on MySQL 5.6

MySQLmysql-5.6

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.

Best Answer

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

Related Question