MySQL Query Reference - aeggermont/datastores GitHub Wiki

Bench Marking Queries:

Show all processes in the SQL server:

SHOW FULL PROCESSLIST\G

SHOW session status LIKE "select%";
SHOW session status LIKE "Sort%";
SHOW session status LIKE "select%";
SHOW session status LIKE "select%";

Kill a specific Query

Get the thread id from PROCESSLIST

KILL thread_id

To get the current UNIX time stamp:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');

Some Operations with dates and times

SELECT now()
SELECT UNIX_TIMESTAMP()
SELECT CURDATE()
SELECT CURTIME()  

To check whether the query cache is present in your MySQL server:

SHOW VARIABLES LIKE 'have_query_cache';

To monitor query cache performance:

SHOW STATUS LIKE 'Qcache%';

The total number of SELECT queries is given by this formula:

Com_select 
+ Qcache_hits
+ queries with errors found by parser

Changing passwords:

UPDATE mysql.user SET Password=PASSWORD('cleartext password')
WHERE User='bob' AND Host='%.example.org';
FLUSH PRIVILEGES;

or:

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

Granting privileges to accounts:

Granting all privileges to an account:
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

To provide a specific user with a permission
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

To revoke a permission, the structure is almost identical to granting it:
REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:
DROP USER ‘demo’@‘localhost’;

List all users

SELECT User FROM mysql.user;