MySQL案例:一次詭異的Aborted connection錯誤排查

簡介

  前段時間，研究怎麼去提升資料庫安全，例如禁止執行不帶條件的update操作，於是就想到了去啟用sql_safe_updates參數，這個參數Mysql默認是不啟用的，而且還不能加入到my.cnf配置裡。因此就想到了用init_connect參數，將sql_safe_updates=1放到init_connect參數裡，這樣每個用戶會話連接的時候，就會啟用sql_safe_updates參數了。

  可是用普通連接資料庫之後，使用某個庫之後，就會報錯

mysql> use information_schema;
No connection. Trying to reconnect...
Connection id: 16
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 16 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)

報錯分析

分析報錯之前，先復盤一下操作步驟

1.創建普通用戶

mysql> create user 'jim'@'%' identified by 'jim';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---+-+
| user | host |
+---+-+
| jim | % |
| repl | % |
| root | % |
| tony | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---+-+
8 rows in set (0.10 sec)

2.使用root用戶登錄資料庫，並設置init_connect參數

mysql> set global init_connect='sql_safe_updates=1';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'init_connect';
+++
| Variable_name | Value |
+++
| init_connect | sql_safe_updates=1 |
+++
1 row in set (0.00 sec)

3.使用普通用戶jim連接測試

root@18374a493e56:~# mysql -ujim -pjim
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.21

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema
No connection. Trying to reconnect...
Connection id: 19
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 19 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)

4.使用root用戶連接測試

root@18374a493e56:~# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

從上面的報錯信息也能很快的判斷出，是由於init_connect設置不合理導致的，可是這裡很奇怪的是，普通用戶會報錯，root用戶操作沒有報錯。弄不清楚為什麼，於是就去看官方文檔，看看官方文檔是怎麼描述的。

init_connect參數描述

For users that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege), the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the CONNECTION_ADMIN or SUPER privilege enables them to open a connection and fix the init_connect value.

這段話的大概意思是，當用戶具有CONNECTION_ADMIN，SUPER權限用戶登錄時，是不需要執行init_connect參數的內容的，而不具備這些權限的用戶登錄時，需要執行init_connect參數的內容，當init_connect參數的內容語句有問題時，就會報錯了，這就解釋了為什麼root用戶沒有問題，而普通用戶發生了問題。

了解報錯原因之後，需要修改init_connect的內容了，init_connect裡的內容複製出來，如果在mysql command命令行裡執行沒有問題就可以了。

5.重新設置init_connect參數值

mysql> show variables like 'init_connect';
+++
| Variable_name | Value |
+++
| init_connect | sql_safe_updates=1 |
+++
1 row in set (0.01 sec)

mysql> set session sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global init_connect='set session sql_safe_updates=1';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'init_connect';
++--+
| Variable_name | Value |
++--+
| init_connect | set session sql_safe_updates=1 |
++--+
1 row in set (0.00 sec)

6.使用普通用戶jim再次連接測試

root@18374a493e56:~# mysql -ujim -pjim
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> show variables like 'sql_safe_updates';
+---+--+
| Variable_name | Value |
+---+--+
| sql_safe_updates | ON |
+---+--+
1 row in set (0.01 sec)

從測試結果可以看到，已經可以正常使用Mysql資料庫了，而且參數sql_safe_updates也設置正確了。

總結

總之，生產操作無小事，大家在生產上執行任何操作時，一定要在測試環境充分驗證之後，了解影響範圍之後，方可上線操作，如文中操作，很可能會導致一次線上故障。