Enable slow query log in MySQL

Answer:

Turn on the Slow Query log in MySQL allow you to debug the sources of slow and problematic SQL.

To activate it, edit the MySQL configurations, e.g. /etc/my.cnf

[mysqld]
...
...
log-slow-queries        = /var/log/mysqld-slow.log                                                                                
long_query_time         = 5     

Restart MySQL and you will notice the file mysqld-slow.log will be created.

Any queries take more than 5 seconds to execute will be logged in this file.

How to check and kill long running MySQL query

Answer:

1. Get into the MySQL shell, type 'SHOW PROCESSLIST' to see which query is blocking

mysql> SHOW PROCESSLIST;

2. It will print out all the current executing queries, and you will find the thread Id.

+-----+-------------+--------------------+-------+---------+-------+

| Id | User  | Host            | db   | Command | Time| State     | Info                                                                                           

+-----+-------------+--------------------+-------+---------+-------+-
| 3928 |root  |192.168.1.5:59202 |db1 |Query    | 84   | Locked  |

select * from table1 where name like '%test%' 

3. Remember this Id, to kill it, use the "KILL" command

mysql> KILL 3928;

Query OK, 0 rows affected (0.05 sec)

How to create a new MySQL user?

Answer:

1. Enter the MySQL shell using root account

mysql -u root -p mysql


2. Execute the SQL like the followings

GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;


3. Now try to login using user1 account

mysql -u user1 -p db1