mysql在開啟二進(jìn)制日志之后必盖，是不能在會話級別關(guān)閉。
# 提示屬于只讀變量
MariaDB [(none)]> set global log_bin=off;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql在數(shù)據(jù)庫恢復(fù)時剔宪，需要臨時關(guān)閉二進(jìn)制日志寫入功能.
# mysql提供了sql_log_bin=off.
MariaDB [(none)]> show global variables like "%sql_log_bin%";
| sql_log_bin | ON |
# 默認(rèn)是開啟的柴梆，也是就說在數(shù)據(jù)庫使用DDL語句陨溅，都會記錄在二進(jìn)制日志文件中，
# 會話級別關(guān)閉二進(jìn)制日志記錄功能绍在。
# set global sql_log_bin=off;
# 注意：設(shè)置此命令之后门扇，是在其他新建的mysql session中關(guān)閉了二進(jìn)制日志記錄的功能雹有。但是此前的mysql session 依然有寫入二進(jìn)制文件的能力。
# 應(yīng)該清除所有之前建立的session悯嗓，才能保證數(shù)據(jù)與二進(jìn)制日志文件真正的一致性件舵。
另：
類似此種會話級別即可修改的參數(shù)，系統(tǒng)變量是否都有此特性脯厨，铅祸？
session級別的變量修改，只是針對修改變量之后的新建session合武，對于修改變量之前的session就無作用临梗。
那么如何正常管理mysql session connect?
# 設(shè)置mysql的最大連接數(shù)，默認(rèn)情況下此值為151稼跳，
max_connections=1000
# 查看mysql 當(dāng)前connect,根據(jù)權(quán)限顯示,普通權(quán)限用戶只能顯示自己用戶名的連接盟庞。
# 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 |
+----+------+-----------+-------+---------+-------+-------+-----------------------+----------+
# 關(guān)閉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之后在此查看什猖，已經(jīng)查看不到此連接信息了，在測試shell重新輸入SQL命令红淡，將會是新的mysql連接不狮，ID與之前不一樣。
###
KILL QUERY terminates the statement the connection is currently executing, but leaves the connection itself intact.
###
終止此連接執(zhí)行的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 默認(rèn)值為151，backlog在之前的取值也是151胜茧，
backlog表示系統(tǒng)對程序的TCP連接完成之后的請求隊(duì)列值{accept()}芯咧。此值還受到其他內(nèi)核參數(shù)的影響，是否在調(diào)整此值時需要關(guān)注與其相關(guān)的內(nèi)核參數(shù)值才能使其真正有效?
