MySQL运维记录

MySQL 实时查看登录的连接以及操作的库、状态、host等：

mysql> SELECT id, user, host, db, command, time, state FROM information_schema.processlist;
+----+------+-----------+------+---------+------+-----------+
| id | user | host      | db   | command | time | state     |
+----+------+-----------+------+---------+------+-----------+
| 25 | root | localhost | NULL | Sleep   |   18 |           |
| 22 | root | localhost | NULL | Sleep   |   12 |           |
| 24 | root | localhost | NULL | Sleep   |   95 |           |
| 23 | root | localhost | NULL | Query   |    0 | executing |
+----+------+-----------+------+---------+------+-----------+
4 rows in set (0.00 sec)

Query 表示当前正在查询的连接
Sleep 表示当前正在休眠的连接

统计登录的连接以及连接的状态：

mysql> SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;
+-----------+-----------+----------+
| host_name | state     | count(*) |
+-----------+-----------+----------+
| localhost |                      |        2 |
| localhost | executing |        1 |
+-----------+-----------+----------+
2 rows in set (0.00 sec)

MySQL 查看所有用户以及允许的ip：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT host, user FROM user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

衡量MySQL性能的重要指标

IOPS：(Input/Output operations Per Second,既每秒处理I/O的请求次数)
像Fusion-IO这种变态级的IOPS都可以干到百万级别.一般情况下用SSD基本上可以满足需求了
IOPS = 1000ms/(寻道时间+旋转延迟时间)

QPS(Query Per Second,既每秒请求、查询次数)
QPS = Questions/Uptime(Uptime换成自己定义的时间单位)

mysql> show global status like "Questions";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 5    |
+---------------+-------+
row in set (0.02 sec)

mysql> show global status like "Uptime";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 2096   |
+---------------+-------+
row in set (0.02 sec)

TPS(Transcantion Per Second,既每秒事务数) MySQL不是每个存储引擎都支持事务.所以就拿InnoDB来说好了.TPS主要涉及提交和回滚。
TPS = (Commit+Rollback)/Seconds

mysql> show global status like "Com_commit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 0     |
+---------------+-------+
row in set (0.02 sec)

mysql> show global status like "Com_rollback";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback  | 0     |
+---------------+-------+
row in set (0.01 sec)

MySQL最大连接数、活跃数

1、最大连接数
max_connections表示最大连接（用户）数。每个连接MySQL的用户均算作一个连接。

mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

修改最大连接数

mysql> set GLOBAL max_connections=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1024  |
+-----------------+-------+
1 row in set (0.00 sec)

查看连接失败的连接数

mysql> show status like '%Connection_errors_max_connections%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 2     |
+-----------------------------------+-------+
1 row in set (0.00 sec)

Connection_errors_max_connections表示有连接请求因数据库连接数已达到最大而失败
2、当前活跃连接数

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 2     |
+----------------------+-------+
1 row in set (0.00 sec)

正常情况下应保证 max_used_connections / max_connections * 100% （理想值≈ 85%）
如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。
3、status Thread查看

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 5     |
| Threads_connected | 1     |
| Threads_created   | 6     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached：线程缓存内的线程的数量。
Threads_connected：当前打开的连接的数量。
Threads_created：表示创建过的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
Threads_running：激活的（非睡眠状态）线程数。
官方解释