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

设置了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)


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)


# 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 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 其他的自行脑补

【大数据开发运维解决方案】Sqoop增量同步mysql/oracle数据到hive(merge-key/append）测试文档 上一篇文章介绍了sqoop全量同步数据到hive， 本片文章将通过实验详细介绍如何增量同步数据到hive，以及sqoop job与crontab定时结合无密码登录的增量同步实现方法。
【大数据开发运维解决方案】Sqoop全量同步mysql/Oracle数据到hive 前面文章写了如何部署一套伪分布式的handoop+hive+hbase+kylin环境,也介绍了如何在这个搭建好的伪分布式环境安装配置sqoop工具以及安装完成功后简单的使用过程中出现的错误及解决办法， 接下来本篇文章详细介绍一下使用sqoop全量同步oracle/mysql数据到hive，这里实验采用oracle数据库为例，
【大数据开发运维解决方案】sqoop增量导入oracle/mysql数据到hive时时间字段为null处理 前面几篇文章详细介绍了sqoop全量增量导入数据到hive，大家可以看到我导入的数据如果有时间字段的话我都是在hive指定成了string类型，虽然这样可以处理掉时间字段在hive为空的问题，但是在kylin创建增量cube时需要指定一个时间字段来做增量，所以上面那种方式不行，这里的处理方式为把string改成timestamp类型，看实验：
大数据运维之MySQL备份及恢复 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。 冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。
《MySQL 技术大全：开发、优化与运维实战》电子版地址 MySQL 具有小巧、灵活和免费等特性，这使得它越来越多地被用于企业的实际开发中。 特别是 MySQL 数据库的开源特性，更使它得到了广泛应用。程序员要想进入 MySQL 开发 领域，除了需要有扎实的编程基础外，还需要掌握 SQL 语句的编写，熟悉 MySQL 数据库的 优化和运维，了解 MySQL 数据库的常见故障和解决方案，这样才能在竞争日益激烈的数据 库领域提高竞争力，进而实现自身的价值。
兰春 数据库技术爱好者，专注于MySQL领域的运维与运营，擅长性能调优，系统瓶颈分析，热爱数据领域的一切