R-Ts NetWorks

Server the Best

MySql


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. :)

Advertisements

August 15, 2012 - Posted by | Linux

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: