init-connet設(shè)置

注：該參數(shù)對(duì)超級(jí)用戶不生效

-- 創(chuàng)建測(cè)試庫(kù)
MySQL> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

-- 創(chuàng)建審計(jì)記錄表
mysql> CREATE TABLE `conn_log` (
    ->   `conn_id` int(11) DEFAULT NULL,
    ->   `conn_time` datetime DEFAULT NULL,
    ->   `user_name` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
    ->   `cur_user_name` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
    ->   `ip` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
    ->   KEY `conn_time` (`conn_time`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected (0.01 sec)

-- 設(shè)置審計(jì)內(nèi)容
mysql> set global init_connect="set @user=user(),@cur_user=current_user();insert into test.conn_log values(connection_id(),now(),@user,@cur_user,'10.0.0.1');"
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%init%';
+------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name          | Value                                                                                                                         |
+------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| init_connect           | set @user=user(),@cur_user=current_user();insert into test.conn_log values(connection_id(),now(),@user,@cur_user,'10.0.0.1'); |
| init_file              |                                                                                                                               |
| init_slave             |                                                                                                                               |
| table_definition_cache | 1400                                                                                                                          |
+------------------------+-------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

-- 創(chuàng)建普通用戶

mysql> grant select,insert on dba_test.* to 'test'@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

異常

[root@test ~]# mysql -S /data0/mysql57/mysql3307/mysqltmp/mysql3307.sock  -utest -ptest 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.7.21-log

Copyright (c) 2000, 2016, 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> show user();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    118
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 118 to db: 'unconnected' user: 'test' host: 'localhost' (init_connect command failed)
mysql> select user();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    119
Current database: *** NONE ***

異常處理

分析

通過(guò)查看erro log發(fā)現(xiàn)test用戶沒(méi)有test.conn_log表的寫權(quán)限，導(dǎo)致init-connect中的sql內(nèi)容無(wú)法進(jìn)行，
從而導(dǎo)致連接失敗

創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的霍城網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

解決

-- 賦權(quán)
mysql> grant insert on test.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

-- 登陸正常
[root@test ~]# mysql -hip地址 -P3307  -utest -ptest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 140
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 dba_test;
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> show tables;
+--------------------+
| Tables_in_dba_test |
+--------------------+
| user               |
+--------------------+
1 row in set (0.00 sec)

mysql> insert into user(user_id,username) values(4,'d');
Query OK, 1 row affected (0.00 sec)

mysql>