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. 🙂
Tweet
These are great. Another thing I think everyone should know is if you’re doing development work with MySQL, then there’s a good chance you need to regularly create databases – a process that’s tedious, at best, if you don’t remember exactly how to do it (there are a lot of little stumbling points that can easily trip you up if you’re careless). As such, we’re going to provide you with a cheat sheet to which you can refer in the event that you need some guidance.
To create a new database, type ~/src$ mysqladmin -u root -p create nameofdatabase. You can do this either while logged in or from the shell.
Switch over to the database with:
~/src$ mysql -u root -p
~/src$ use nameofdatabase
Here’s where it gets tricky. You’ll need to create a new user and give them the ability to manipulate the database you’ve created.
Source: https://www.libertycenterone.com/blog/the-mysql-cheat-sheet-part-one-seven-useful-tidbits-that-everyone-seems-to-forget/