e r i k i m h d o t c o m

How to check for low mem prunes:

mysql> show status like ‘%cache%’;

Shows all running queries and processes from command line:

mysql> show full processlist;

How to give user all privileges, including RELOAD which cannot be turned on in cPanel.

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@'localhost’;

How to set the global query cache size in a live environment

set GLOBAL query_cache_size=xxx;

or in /etc/my.cnf

set-variable = query_cache_size=32M

The mysqladmin tool’s version ouput tells you some much needed and valuable information about your mySQL databases and usage. Check it out from the shell command line:

mysqladmin version

BRIEF LOGGING HOWTO:

Logging is very resource intensive on a highly utilized mySQL server. However, if you must enable it for diagnostic purposes, here’s how:

In your /etc/my.cnf add:

log = /var/log/mysql.log

You will need to actually ‘touch’ this file and set the ownership of it to be writable by the mySQL server. Typically, this would be done like so:

# touch /var/log/mysql.log ; chown mysql:mysql /var/log/mysql.log

Less intensive, and widely used for optimizing slow queries, is the “slow query log”. You define in my.cnf what a “slow query” is (in seconds), and then only queries that exceed this run time are logged.

#enable slowq logging in /etc/my.cnf

log-slow-queries = /var/log/slowqueries

The same permissions caveat applies here.

# touch /var/log/slowqueries ; chown mysql:mysql /var/log/slowqueries

These are just some notes, and more info will eventually find its’ why in here as I advance my SQL skillz.

Share on Facebook