How to add a 32M query cache with an item limit of 1M.
NOTE: You might need to add '-p' to the following command if 'root'@'localhost' requires a password on your environment.
# mysql \
-e "SET GLOBAL query_cache_limit = 1048576;" \
-e "SET GLOBAL query_cache_size = 33554432;" \
-e "SET GLOBAL query_cache_type = 1;"
# mysql \
-e "SHOW VARIABLES LIKE '%query_cache%';" \
-e "SHOW STATUS LIKE 'qcache%';"
To ensure the changes persist a service restart append the settings to the MySQL configuration.
# if ! grep -qP -- '^query[_-]cache[_-]limit[ ]*=[ ]*[0-9]+$' /etc/my.cnf; then \
sed -i -r \
-e '/\[mysqld\]/,/\[mysqld_safe\]/ s~(\[mysqld_safe\])~query_cache_limit=1048576\n\n\1~' \
/etc/my.cnf; \
else \
sed -i -r \
-e '/\[mysqld\]/,/\[mysqld_safe\]/ s~^(query[_-]cache[_-]limit[ ]*=[ ]*).*$~\11048576~' \
/etc/my.cnf; \
fi; \
if ! grep -qP -- '^query[_-]cache[_-]size[ ]*=[ ]*[0-9]+$' /etc/my.cnf; then \
sed -i -r \
-e '/\[mysqld\]/,/\[mysqld_safe\]/ s~^(query[_-]cache[_-]limit[ ]*=[ ]*[0-9]+)$~\1\nquery_cache_size=33554432~' \
/etc/my.cnf; \
else \
sed -i -r \
-e '/\[mysqld\]/,/\[mysqld_safe\]/ s~(query[_-]cache[_-]size[ ]*=[ ]*)[0-9]+$~\133554432~' \
/etc/my.cnf; \
fi; \
if ! grep -qP -- '^query[_-]cache[_-]type[ ]*=[ ]*.*$' /etc/my.cnf; then \
sed -i -r \
-e '/\[mysqld\]/,/\[mysqld_safe\]/ s~^(query[_-]cache[_-]size[ ]*=[ ]*[0-9]+)$~\1\nquery_cache_type=1~' \
/etc/my.cnf; \
else \
sed -i -r \
-e '/\[mysqld\]/,/\[mysqld_safe\]/ s~(query[_-]cache[_-]type[ ]*=[ ]*).*$~\11~' \
/etc/my.cnf; \
fi;
Restart MySQL service for to reload changes from the configuration file. This step is not necessary if using the method above to apply the settings to a running instance of MySQL however it is necessary to prove the configuration file changes won't cause the service to fail to start on next server/service restart.
# service mysqld restart