MySQL运维实战（一）之系统变量潜规则

Agenda

踩坑经历
测试用例
结论
实战用途

一、踩坑经历

设置了slow log 的时间，但是抓不到正确的sql
设置了read_only ，为啥还有写入进来
设置了sql_safe_update , 为啥还能全表删除
测试方法的不对，导致设置了read_only后，有的时候可以insert，有的时候不可以insert

太多这样的问题, 所以打算一窥究竟

二、测试用例

测试设置参数后，是否会生效

2.1 官方文档说明

https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

* 重点说明

If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.

If you change a global system variable, the value is remembered and used for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any current client sessions (not even the session within which the SET GLOBAL statement occurred).

官方重点说明，设置global变量的时候，只对后面连接进来的session生效，对当前session和之前的session不生效
接下来，我们好好测试下

2.2 系统变量的Scope

1. Global : 全局级别
    set global variables = xx;  --正确
    set variables = xx; --报错 （因为是scope=Global，所以不能设置session变量 ）

2. Session : 会话级别
    set variables = xx; --正确
    set global variables = xx;  --报错 （因为是Scope=session，所以不能设置Global变量）

3. Both : 两者皆可
    3.1 Global : set global variables = xx; --正确（因为是scope=both，他既可以设置全局变量，也可以设置session变量）
    3.2 Session : set variables = xx;  --正确（因为是scope=both，他既可以设置全局变量，也可以设置session变量）

2.3 Session 级别测试

1. session 级别的变量代表：sql_log_bin
2. 该类型的变量，设置后，只会影响当前session，其他session不受影响

2.4 Global 级别测试

变量代表

1. Global 级别的变量代表：read_only , log_queries_not_using_indexes

测试一

* processlist_id = 100:

lc_rx:lc> select @@global.log_queries_not_using_indexes;
+----------------------------------------+
| @@global.log_queries_not_using_indexes |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.00 sec)

lc_rx:lc> select * from lc_1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

此时查看slow log，并未发现任何slow

* processlist_id = 120:

dba:(none)> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)

* processlist_id = 100:

lc_rx:lc> select @@global.log_queries_not_using_indexes;
+----------------------------------------+
| @@global.log_queries_not_using_indexes |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

lc_rx:lc> select * from lc_1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

此时，去发现slow log

# Time: 2017-08-04T16:05:04.303005+08:00
# User@Host: lc_rx[lc_rx] @ localhost []  Id:   296
# Query_time: 0.000149  Lock_time: 0.000081 Rows_sent: 5  Rows_examined: 5
SET timestamp=1501833904;
select * from lc_1;

* 结论
    说明全局参数变量不管是在session前，还是session后设置，都是立马让所有session生效

测试二

dba:(none)> show processlist;
+-----+-------+----------------------+------+------------------+---------+---------------------------------------------------------------+------------------+
| Id  | User  | Host                 | db   | Command          | Time    | State                                                         | Info             |
+-----+-------+----------------------+------+------------------+---------+---------------------------------------------------------------+------------------+
| 303 | lc_rx | localhost            | lc   | Sleep            |      83 |                                                               | NULL             |
| 304 | dba   | localhost            | NULL | Query            |       0 | starting                                                      | show processlist |
+-----+-------+----------------------+------+------------------+---------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

* PROCESSLIST_ID=303

lc_rx:lc> select @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

lc_rx:lc> insert into lc_1 select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

* PROCESSLIST_ID=304

dba:(none)> set global read_only=on;
Query OK, 0 rows affected (0.00 sec)

* PROCESSLIST_ID=303

lc_rx:lc> select @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

lc_rx:lc> insert into lc_1 select 3;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

* 结论：
    PROCESSLIST_ID=304 设置的参数，导致PROCESSLIST_ID=303 也生效了

2.5 如何查看当下所有session中的系统变量值呢？

5.7 可以看到
遗憾的是：只能看到Both和session的变量，scope=global没法看(因为会立即生效)


dba:(none)> select * from performance_schema.variables_by_thread as a,\
    ->     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b\
    ->         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'sql_safe_updates';
+-----------+------------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME    | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+------------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       313 | sql_safe_updates | OFF            |       313 |            232 | repl             | xx.xxx.xxx.xxx   | Binlog Dump GTID    | Master has sent all binlog to slave; waiting for more updates |
|       381 | sql_safe_updates | ON             |       381 |            300 | dba              | localhost        | Query               | Sending data                                                  |
+-----------+------------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

2.6 Both 级别测试

用我们刚刚学到的知识，来验证更加快速和靠谱

变量代表

1. Both 级别的变量代表：sql_safe_updates , long_query_time

测试

* 第一次查看long_query_time参数，PROCESSLIST_ID=307，308，309 都是一样的，都是300s

dba:(none)> select * from performance_schema.variables_by_thread as a,     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'long_query_time';
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       388 | long_query_time | 300.000000     |       388 |            307 | dba              | localhost        | Sleep               | NULL                                                          |
|       389 | long_query_time | 300.000000     |       389 |            308 | dba              | localhost        | Query               | Sending data                                                  |
|       390 | long_query_time | 300.000000     |       390 |            309 | dba              | localhost        | Sleep               | NULL                                                          |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

* 我们再PROCESSLIST_ID=308的session上进行设置long_query_time=100，我们能看到这个时候所有的session都还是300，没有生效

dba:(none)> set global long_query_time=100;
Query OK, 0 rows affected (0.00 sec)

dba:(none)> select * from performance_schema.variables_by_thread as a,     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'long_query_time';
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       388 | long_query_time | 300.000000     |       388 |            307 | dba              | localhost        | Sleep               | NULL                                                          |
|       389 | long_query_time | 300.000000     |       389 |            308 | dba              | localhost        | Query               | Sending data                                                  |
|       390 | long_query_time | 300.000000     |       390 |            309 | dba              | localhost        | Sleep               | NULL                                                          |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

* 接下来，我们再断开309，重连时，processlist id 应该是310，这时候的结果就是100s了。这一点说明，在执行set global参数后进来的session才会生效，对当前session和之前的session不生效

dba:(none)> select * from performance_schema.variables_by_thread as a,     (select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_STATE from performance_schema.threads where PROCESSLIST_USER<>'NULL') as b         where a.THREAD_ID = b.THREAD_ID and a.VARIABLE_NAME = 'long_query_time';
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE                                             |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
|       388 | long_query_time | 300.000000     |       388 |            307 | dba              | localhost        | Sleep               | NULL                                                          |
|       389 | long_query_time | 300.000000     |       389 |            308 | dba              | localhost        | Query               | Sending data                                                  |
|       391 | long_query_time | 100.000000     |       391 |            310 | dba              | localhost        | Sleep               | NULL                                                          |
+-----------+-----------------+----------------+-----------+----------------+------------------+------------------+---------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

三、结论

官方文档也不是很靠谱，也有很多差强人意的地方
自己动手，测试验证的时候做好测试方案和计划，以免遗漏导致测试失败，得出错误的结论

四、实战意义

4.1 项目背景

a. 修改sql_safe_update=on, 这里面有很多难点，其中的一个难点就是如何让所有session生效

4.2 解决方案

MySQL5.7+

结合今天的知识，通过performance_schema.variables_by_thread，performance_schema.threads表，可以知道哪些变量已经生效，哪些变量还没生效

MySQL5.7-

1. 如果对今天的Both变量知识理解了，不难发现，还有一个变通的办法

2. 执行这条命令即可
    2.1 set global $both_scope_variables = on|off
    2.2 select max(ID) from information_schema.PROCESSLIST;

3. kill掉所有小于processlist < max(ID) 的session即可
    3.1 当然，系统用户进程你不能kill，read_only的用户你没必要kill
    3.2 其他的自行脑补

时间： 2024-08-03 19:08:34

MySQL运维实战（一）之系统变量潜规则的相关文章

MySQL运维实战（二）之巧用P_S解决账号host访问的荣耀王者之路

背景一个MySQL实例中,如何验证一个账号上面是否还有访问? 一个MySQL实例中,如何验证某个业务ip是否还有访问? 倔强青铜级别打开general log 优点: 全量缺点: 性能差秩序白银级别打开slow log,设置long_query_time = 0 优点: 全量缺点: 性能比较差荣耀黄金级别 tshark | tcpdump | tcpcopy tshark -i any dst host ${ip} and dst port 3306 -l -d tcp.port=

mysql运维之二进制日志。（east_sun原创参考文档centos 7）

mysql运维之二进制日志.(east_sun参考文档centos 7) 1.二进制日志开启服务器的二进制日志(binary log简称binlog)是备份的最重要因素之一,它们对于基于时间点的恢复操作是必要的,并且通常比数据要小,所以更容易进行频繁的备份.MySQL 二进制日志是非常重要的,所以DBA们应该尽可能将二进制日志和数据库文件分开存储. 二进制日志主要作用有三个:1.基于备份恢复数据 2.数据库主从复制3.挖掘分析SQL语句. 首先我们需要知道如何开启二进制日志.在centos 7

OpenStack 部署运维实战

OpenStack 部署运维实战 OpenStack 简介 OpenStack 是一个开源的 IaaS 实现,它由一些相互关联的子项目组成,主要包括计算.存储.网络.由于以 Apache 协议发布,自 2010 年项目成立以来,超过 200 个公司加入了 OpenStack 项目,其中包括 AT&T.AMD.Cisco.Dell.IBM.Intel.Red Hat 等.目前参与 OpenStack 项目的开发人员有 17,000+,来自 139 个国家,这一数字还在不断增长中. OpenStac

MySQL运维案例分析：Binlog中的时间戳

背景众所周知,在Binlog文件中,经常会看到关于事件的时间属性,出现的方式都是如下这样的. #161213 10:11:35 server id 11766 end_log_pos 263690453 CRC32 0xbee3aaf5 Xid = 83631678 我们清楚地知道,161213 10:11:35表示的就是时间值,但除此之外呢?还能知道它的什么信息呢? 案例分析先从一个典型的案例入手来讲述其中的细节,比如曾经在Galera Cluster碰到的一个问题,可以先看一段Binlo

Elasticsearch hadoop使用示例 & 运维实战之集群规划 &presto-elasticsearch connector

elasticsearch-hadoop使用示例在elasticsearch-hadoop的具体使用中碰到了几个问题,有必要记录一下,避免下次遇到时又要重新研究. 利用spark读取es数据源的简单示例 import org.elasticsearch.spark.sql._ val esOptions = Map("es.nodes"->"192.168.1.2,192.168.1.3", "es.scroll.size"->&q

网易OpenStack部署运维实战

OpenStack自 2010 年项目成立以来,已经有超过 200 个公司加入了 OpenStack 项目,目前参与 OpenStack 项目的开发人员有 17,000+,而且这些数字还在增加,作为一个开源的IaaS实现,目前在企业的应用越来越普遍,网易公司私有云团队分享了他们在基于OpenStack 开发的一套云计算管理平台的实战经验,期待和广大的OpenStack 使用者进行交流. 本文为您介绍了网易公司基于 OpenStack 开发的一套云计算管理平台,以及在开发.运营.维护过程中遇到的问

美图秀秀DBA谈MySQL运维及优化

随着MySQL应用的不断普及和自身发展,如何更好的优化MySQL和使用MySQL,依然是一个比较有挑战的问题,尤其是在业务快速增长的场景下.本次分享主要介绍一些通用的运维优化实践和问题,以及未来的一些方向. 目录 MySQL的优势和劣势数据库规范化 Sharding拆分数据库备份性能优化从每个月的db engines排名可以看到,关系数据库依然占主导地位,nosql的种类和可选择空间更大,总共283种数据库,里面大多数也是NoSQL. 如何选择数据库,从以下几个因素考虑: 应用场景:O

《Splunk智能运维实战》——1.4　使用脚本输入

1.4 使用脚本输入智能运维所需的数据并非全部来自日志文件或网络端口.Splunk也可获取命令或脚本的输出,并将其与其他数据一起索引. 脚本输入是针对较难获取数据的一种有效方法.如果收集第三方提供的命令行程序输出的数据,Splunk可定期运行该命令来索引结果.通常来说,脚本输入常用于从某个源抓取信息,而网络输入则等待数据从某个源不断推送. 本节将介绍如何配置Splunk,使其每隔一段时间执行命令并将输出的数据导入Splunk. 做好准备要进行本节的操作,需要运行Splunk Enterpri

《Splunk智能运维实战》——第1章游戏时间——导入数据 1.1 简介

第1章游戏时间--导入数据 1.1 简介加快运维智能的机器数据有很多不同的形式,来源也各不相同.Splunk可从多种来源收集并索引数据,其中包括Web服务器或商业应用程序创建的日志文件,网络设备生成的系统日志数据,及自定义开发脚本输出的数据.即便数据一开始看上去很复杂,我们也可以借助Splunk轻松地实时收集.索引.转化和呈现数据. 本章将学习一些基本的技巧,掌握如何将所需的数据导入Splunk,介绍如何使用样本数据集来构建自己的Splunk智能运维应用程序.该数据集是由一个虚拟的三层式电子

MySQL运维实战（一）之 系统变量潜规则