mysql在开启二进制日志之后，是不能在会话级别关闭。
# 提示属于只读变量
MariaDB [(none)]> set global log_bin=off;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql在数据库恢复时，需要临时关闭二进制日志写入功能.
# mysql提供了sql_log_bin=off.
MariaDB [(none)]> show global variables like "%sql_log_bin%";
| sql_log_bin | ON |
# 默认是开启的，也是就说在数据库使用DDL语句，都会记录在二进制日志文件中，
# 会话级别关闭二进制日志记录功能。
# set global sql_log_bin=off;
# 注意：设置此命令之后，是在其他新建的mysql session中关闭了二进制日志记录的功能。但是此前的mysql session 依然有写入二进制文件的能力。
# 应该清除所有之前建立的session，才能保证数据与二进制日志文件真正的一致性。
另：
类似此种会话级别即可修改的参数，系统变量是否都有此特性，？
session级别的变量修改，只是针对修改变量之后的新建session，对于修改变量之前的session就无作用。
那么如何正常管理mysql session connect?
# 设置mysql的最大连接数，默认情况下此值为151，
max_connections=1000
# 查看mysql 当前connect,根据权限显示,普通权限用户只能显示自己用户名的连接。
# show full processlist;
+----+------+-----------+-------+---------+-------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+-------+---------+-------+-------+-----------------------+----------+
| 9 | root | localhost | NULL | Sleep | 14912 | | NULL | 0.000 |
| 10 | root | localhost | NULL | Query | 0 | init | show full processlist | 0.000 |
| 11 | root | localhost | mysql | Sleep | 10431 | | NULL | 0.000 |
+----+------+-----------+-------+---------+-------+-------+-----------------------+----------+
# 关闭mysql connecttion,使用kill命令。KILL [CONNECTION | QUERY] processlist_id
###
KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given processlist_id, after terminating any statement the connection is executing.
将process id此连接终止，KILL之后在此查看，已经查看不到此连接信息了，在测试shell重新输入SQL命令，将会是新的mysql连接，ID与之前不一样。
###
KILL QUERY terminates the statement the connection is currently executing, but leaves the connection itself intact.
###
终止此连接执行的SQL语句，但是连接不会终止，KILL之后，此连接信息依然能看到，但是Command位置显示Killed，在测试shell重新输入SQL命令，此连接被重新激活，ID依然是之前的ID号。
When you use KILL a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die because the kill flag is checked only at specific intervals:
1.During SELECT operations, for ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
2.ALTER TABLE operations that make a table copy check the kill flag periodically for each few copied rows read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.
3.The KILL statement returns without waiting for confirmation, but the kill flag check aborts the operation within a reasonably small amount of time. Aborting the operation to perform any necessary cleanup also takes some time.
4.During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. If you are not using transactions, the changes are not rolled back.
5.GET_LOCK() aborts and returns NULL.
6.An INSERT DELAYED thread quickly flushes (inserts) all rows it has in memory and then terminates.
7.If the thread is in the table lock handler (state: Locked), the table lock is quickly aborted.
8.If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.
max_connection 默认值为151，backlog在之前的取值也是151，
backlog表示系统对程序的TCP连接完成之后的请求队列值{accept()}。此值还受到其他内核参数的影响，是否在调整此值时需要关注与其相关的内核参数值才能使其真正有效?
网友评论