GBase 8a通过show processlist得到当前节点的连接信息,本文介绍host列的信息,包括带%百分号,主机名,IP加端口的。其中显示百分号%是因为创建的存储过程的用户,是允许任意主机连接,而当前用户又不是创建者时才显示%。
目录导航
显示localhost
没有端口信息,表示是通过socket方式连接的本地客户端。
gbase> show processlist;
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 11232 | Waiting for next activation | NULL |
| 534 | gbase | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+-----------------+-----------+
复现方法是不加-h参数,用本地socket登录。当然如果需要密码的化,还是要输入的。
[gbase@gbase_rh7_001 ~]$ gccli
可以在本机查看进程,比如
[root@gbase_rh7_001 ~]# ps -ef|grep gccli
gbase 20066 2661 0 12:02 pts/0 00:00:00 gccli -ugbase -pxxxxxxxxxxxxx
root 20131 20089 0 12:02 pts/1 00:00:00 grep --color=auto gccli
[root@gbase_rh7_001 ~]#
显示IP和端口
其中10.0.2.102:42014中的10.0.2.102是连接发起端的IP, 42014是发起端连接端口
gbase> show processlist;
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 12340 | Waiting for next activation | NULL |
| 576 | root | localhost | testdb | Query | 0 | NULL | show processlist |
| 577 | root | 10.0.2.102:42014 | NULL | Sleep | 26 | | NULL |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
3 rows in set (Elapsed: 00:00:00.00)
复现如下
[gbase@gbase_rh7_001 ~]$ gccli -h10.0.2.101
,如果是本机,还可用 -h127.0.0.1.
[gbase@gbase_rh7_001 ~]$ gccli -h127.0.0.1
GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase. All Rights Reserved.
gbase> show processlist;
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 12583 | Waiting for next activation | NULL |
| 576 | root | localhost | testdb | Sleep | 209 | | NULL |
| 579 | root | 10.0.2.102:42016 | NULL | Sleep | 219 | | NULL |
| 584 | root | 127.0.0.1:40108 | NULL | Query | 0 | NULL | show processlist |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
4 rows in set (Elapsed: 00:00:00.00)
排查方法
去IP的主机,用lsof -I:端口查找对应的进程
[gbase@gbase_rh7_001 gbase]$ gccli -uroot testdb -h10.0.2.101
GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase. All Rights Reserved.
gbase> show processlist;
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13023 | Waiting for next activation | NULL |
| 589 | root | 10.0.2.101:60452 | testdb | Query | 0 | NULL | show processlist |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)
101机器上可以看到是gccli连接的。
[gbase@gbase_rh7_001 ~]$ lsof -i:60452
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
gclusterd 2726 gbase 78u IPv6 8055325 0t0 TCP gbase_rh7_001:5258->gbase_rh7_001:60452 (ESTABLISHED)
gccli 31684 gbase 3u IPv4 8055324 0t0 TCP gbase_rh7_001:60452->gbase_rh7_001:5258 (ESTABLISHED)
[gbase@gbase_rh7_001 ~]$
显示百分号%
目前已知的可能是编写了存储过程,在其定义里加入了用户和主机的声明。如果没显示指定,则默认的用户的权限是%(任何主机都能访问),则也会自动生成%。
比如
DELIMITER $$
DROP PROCEDURE IF EXISTS `bizarre` $$
CREATE DEFINER=`gbase`@`%` PROCEDURE `bizarre`()
BEGIN
show processlist;
END $$
DELIMITER ;
自动生成的例子
[gbase@gbase_rh7_001 gbase]$ gccli -uroot testdb -h10.0.2.101
GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase. All Rights Reserved.
gbase> DELIMITER $$
gbase> DROP PROCEDURE IF EXISTS `testhost` $$
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.00)
gbase> CREATE PROCEDURE `testhost`()
-> BEGIN
-> show processlist;
-> END $$
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> DELIMITER ;
gbase> show create procedure testhost;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| testhost | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH | CREATE DEFINER="root"@"%" PROCEDURE "testhost"()
BEGIN
show processlist;
END | utf8 | utf8_general_ci | utf8_general_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> call testhost();
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13186 | Waiting for next activation | NULL |
| 589 | root | 10.0.2.101:60452 | testdb | Query | 0 | NULL | show processlist |
| 592 | root | 10.0.2.101:60492 | testdb | Sleep | 19 | | NULL |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
3 rows in set (Elapsed: 00:00:00.00)
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
如上运行存储过程的用户是定义的用户,显示的是正常的主机名或IP+端口。
如果运行存储过程的用户,不是代码里定义的用户,例子里是gbase,运行时,显示的就是%。
注意如下例子中的3次show processlist. 第1和第3是当前用户执行的,显示为localhost, 而第二个是通过当前root用户,执行了一个定义为 'gbase'@'%'的存储过程输出的,显示为%。
[gbase@gbase_rh7_001 gbase]$ gccli -uroot testdb
GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase. All Rights Reserved.
gbase> show processlist;
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 12844 | Waiting for next activation | NULL |
| 588 | root | localhost | testdb | Query | 0 | NULL | show processlist |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> call bizarre();
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 12845 | Waiting for next activation | NULL |
| 588 | gbase | % | testdb | Query | 0 | NULL | show processlist |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.01)
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show processlist;
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 12849 | Waiting for next activation | NULL |
| 588 | root | localhost | testdb | Query | 0 | NULL | show processlist |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)
总结
数据库用户,允许所有IP可以连接,还是存在一定的安全隐患,建议生产环境,通过hosts参数,限定可以连接的IP,请参考