MySQL tuning

Database

目录

操作

mysqladmin

查看状态

$ mysqladmin status
Uptime: 14279918  Threads: 1501  Questions: 2808466184  Slow queries: 498932  Opens: 75710  Flush tables: 1  Open tables: 2000  Queries per second avg: 196.672

slow query

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------+
| Variable_name             | Value                                   |
+---------------------------+-----------------------------------------+
| log_slow_admin_statements | OFF                                     |
| log_slow_slave_statements | OFF                                     |
| slow_launch_time          | 2                                       |
| slow_query_log            | OFF                                     |
| slow_query_log_file       | /var/lib/mysql/devmysql-01-dev-slow.log |
+---------------------------+-----------------------------------------+
5 rows in set (0.00 sec)

允许用户访问数据库

参考 How To Create a New User and Grant Permissions in MySQL

GRANT ALL PRIVILEGES ON pge.* to 'demo'@'localhost' WITH GRANT OPTION;

这个方法只能适用于给定的hostname。

如果希望允许用户从任何地方访问，改成：

GRANT ALL PRIVILEGES ON pge.* to 'demo'@'%' WITH GRANT OPTION;

则会报错：

Can't find any matching row in the user table

参考 这里

Slow Query

enable slow query

Find and Kill Pending Queries

显示正在运行的query

show full processlist;

或者

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST    ;

time就是执行的时长，STATE是当前状态。

如果要kill某个query，用这个命令: kill <ID> ;

如果想条件性地找到并kill一批queries，首先找到这些ID：

SELECT  CONCAT('KILL ',ID,';') 
    FROM    INFORMATION_SCHEMA.PROCESSLIST  
    WHERE   USER = 'akka_aurora' 
    AND     STATE = 'cleaned up';   

然后执行这些结果。