inside the mind of a linux admin

mysql cheatsheet


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. 🙂

1 Comment

  • Felix on Friday, July 15, 2016

    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/

Leave a Reply

Your email address will not be published. Required fields are marked *

Tweeter button Facebook button Myspace button