版本：v4.0.0-rc.1
限定IP访问，但是好像没用
@peng-xin 您好，请问您是怎么在创建用户时指定 IP 的？ 能否给个例子，把涉及的用户名和密码换成一个假的？
CREATE USER ‘user’@‘172.18.27.151’ IDENTIFIED BY ‘pw’;
GRANT select ON xx.xx TO ‘user’@‘172.18.27.151’;
那么 eagle@eagle73 这台机器的 IP 地址是多少呢？
你好
这边没有复现你的问题，请检查下当前集群环境和访问是否有问题，可以贴一下复现流程，这边也作参考：
【环境】
tidb 环境：172.16.5.169
【创建 user 语句】
CREATE USER ‘usertest’@‘172.16.5.171’ IDENTIFIED BY ‘123’;
GRANT select ON . TO ‘usertest’@‘172.16.5.171’;
【复现流程】
- 通过 172.16.5.169 使用 usertest 访问 tidb：
- 通过 172.16.5.171 usertest 用户，访问 172.16.5.169 tdib：
[root@node5171 ~]# mysql -uusertest -p -h172.16.5.169 -P24000
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.7.25-TiDB-v4.0.0-rc-141-g7267747ae TiDB Server (Apache License 2.0), MySQL 5.7 compatible
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show grants;
+--------------------------------------------------+
| Grants for User |
+--------------------------------------------------+
| GRANT Select ON *.* TO 'usertest'@'172.16.5.171' |
+--------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| mysql |
| test |
| test-1 |
| tidb_loader |
+--------------------+
7 rows in set (0.00 sec)
MySQL [(none)]> use 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 [test]> select * from t;
+---+------+
| a | b |
+---+------+
| a | 2 |
| b | 3 |
+---+------+
2 rows in set (0.01 sec)
MySQL [test]> insert into t select '1',1;
ERROR 1142 (42000): INSERT command denied to user '172.16.5.171'@'usertest' for table 't'
MySQL [test]> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-rc-141-g7267747ae
Git Commit Hash: 7267747ae0ec624dffc3fdedb00f1ed36e10284b
Git Branch: jira-4.0
UTC Build Time: 2020-04-28 08:28:57
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [test]> system ip a |grep 172.16
inet 172.16.5.171/23 brd 172.16.5.255 scope global noprefixroute dynamic eth0
MySQL [test]>
你好，
通过 haproxy 连入 tidb 的时候，看下 show processlist 看下 ip 为多少，是否为 haproxy ip。
是haproxy的问题，不走haproxy之后就没有这个问题了。
谢谢大佬
你好，
要想通过 haproxy 也可以限制的话可以看下以下文章
https://www.percona.com/blog/2015/10/15/proxy-protocol-percona-xtradb-cluster-quick-guide/
解决方案也和这篇文章类似， tidb 也支持 --proxy-protocol-networks 功能