命令行查看与修改mysql的系统参数

前言

仅罗列出一些常用的命令，以备自己日后查找

查看mysql正在执行的操作

mysql> show processlist;
+----+------+-------------------+------+---------+------+----------+------------------+
| Id | User | Host              | db   | Command | Time | State    | Info             |
+----+------+-------------------+------+---------+------+----------+------------------+
|  7 | root | localhost:42404   | NULL | Query   |    0 | starting | show processlist |
| 11 | test | 175.0.26.64:51481 | NULL | Sleep   | 6063 |          | NULL             |
| 12 | test | 175.0.26.64:51482 | NULL | Sleep   | 6056 |          | NULL             |
| 13 | test | 175.0.26.64:51483 | fhgk | Sleep   | 5762 |          | NULL             |
| 14 | test | 175.0.26.64:51599 | NULL | Sleep   | 5760 |          | NULL             |
+----+------+-------------------+------+---------+------+----------+------------------+
5 rows in set (0.00 sec)

注意：
每一条记录代表一个连接，此时如果开启一个tomcat程序，连接池设置为10，则再次运行show processlist命令时，会多出10条记录，停止tomcat，则刚才多出来的10条记录消失，恢复成原来的5条。

查看mysql字符集

mysql> show variables like '%character%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| character_set_client     | utf8                                 |
| character_set_connection | utf8                                 |
| character_set_database   | utf8                                 |
| character_set_filesystem | binary                               |
| character_set_results    | utf8                                 |
| character_set_server     | utf8                                 |
| character_set_system     | utf8                                 |
| character_sets_dir       | /usr/local/mysql3316/share/charsets/ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)

mysql常规日志

1. 查看常规日志参数

mysql> show variables like '%general_log%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | OFF                               |
| general_log_file | /usr/local/mysql3316/data/ver.log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)

2.临时启用常规日志

mysql> set global general_log = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general_log%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | ON                                |
| general_log_file | /usr/local/mysql3316/data/ver.log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)

注意：
1. 打开后就能在general_log_file文件中查看所有执行的sql语句了，比较影响性能一般情况不要打开；
2. 此种启用方式仅针对当前进程不是永久性的，重启mysql后失效，想永久性开启就在my.cnf文件中配置；

mysql慢日志

1. 查看慢日志参数

mysql> show variables like '%query%';
+------------------------------+----------------------------------------+
| Variable_name                | Value                                  |
+------------------------------+----------------------------------------+
| binlog_rows_query_log_events | OFF                                    |
| ft_query_expansion_limit     | 20                                     |
| have_query_cache             | YES                                    |
| long_query_time              | 10.000000                              |
| query_alloc_block_size       | 8192                                   |
| query_cache_limit            | 1048576                                |
| query_cache_min_res_unit     | 4096                                   |
| query_cache_size             | 1048576                                |
| query_cache_type             | OFF                                    |
| query_cache_wlock_invalidate | OFF                                    |
| query_prealloc_size          | 8192                                   |
| slow_query_log               | OFF                                    |
| slow_query_log_file          | /usr/local/mysql3316/data/ver-slow.log |
+------------------------------+----------------------------------------+
13 rows in set (0.01 sec)

输出说明：
slow_query_log：慢日志开关；
slow_query_log_file：慢日志保存文件；
long_query_time：慢日志判断阀值，执行时长超过此值的sql都将记录入慢日志文件；
slow_launch_time：此值不是慢日志的判断阀值，注意区分，好像是废弃的；

2. 启用慢日志

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | ON                                     |
| slow_query_log_file | /usr/local/mysql3316/data/ver-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.01 sec)

3. 查看慢日志记录

# 查看当前阀值
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

# 执行超过当前阀值的sql语句
mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
|         0 |
+-----------+
1 row in set (11.00 sec)

此时查看slow_query_log_file文件，多出如下记录：

# Time: 2017-10-12T09:47:03.506229Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     7
# Query_time: 11.000290  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1507801623;
select sleep(11);

4. 修改慢日志阀值

mysql> set long_query_time=0.5;   # 注意：此参数非global，不需要加global
Query OK, 0 rows affected (0.00 sec)

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

5. 永久启用慢日志

vim my.cnf
# 添加以下项即可
[mysqld]
long-query-time=0.5
slow-query-log=on