mysql通过/etc/hosts文件进行访问授权|IT运维中文博客

mysql通过/etc/hosts文件进行访问授权

时间:2013-10-30 05:10:00 作者:beebol 标签: 分类: Mysql

mysql要对所有访问数据库的应用进行访问授权，那么需要添加相应的用户及密码，另外还需要在mysql.user表中允许指定的host连接数据库。下面介绍利用/etc/hosts来进hosts授权。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| student            |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> grant all on student.* to dba@'student_%' identified by 'ding';
Query OK, 0 rows affected (0.13 sec)

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

mysql> Select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *0E16069C30DC5FEDD519C3C83F73946CA578BCD4 |
| root | 127.0.0.1 |                                           |
| dba  | student_% | *0E16069C30DC5FEDD519C3C83F73946CA578BCD4 |
+------+-----------+-------------------------------------------+3 rows in set (0.00 sec)
3 rows in set (0.00 sec)

首先，创建一个dba用户，密码为ding,能够通过student_%主机访问数据库。

然后在/etc/hosts添加如下东东。

[root@client1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.0.2 student_0.2
192.168.0.2 student_0.2

表示可以从192.168.0.2连接这台数据库。原因是这两台应用连接进来后，会通过hosts进行域名解析成student0.2,正好在dba用户授权时，是允许student%进来的。%表示是任意字符。

然后我在0.2服务器上登录，看是否ok

[root@server ~]# mysql -u dba -p -h192.168.0.3
Enter password: ERROR 1130 (00000): Host '192.168.0.2' is not allowed to connect to this MySQL server
ERROR 1130 (00000): Host '192.168.0.2' is not allowed to connect to this MySQL server

修改hosts文件后，直接登录是不行的，显示还是从192.168.0.2这个ip进行登录的，hosts是不生效。重新刷新mysql的hosts就成功了，刷新方法有两种：

mysql> flush hosts;Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

登录mysql终端可以用上面这种方式，也可以在命令提示符中用如下方式。

[root@client1 ~]# mysqladmin -uroot -p flush-hostsEnter password:
Enter password:

通过如上步骤就可以成功授权，这样做的好处是有新的应用进来，不需要再去grant添加授权，只需要在/etc/hosts添加相应解析，然后再刷新hosts就可以了。

mysql> show databases;+--------------------+| Database |+--------------------+| informationschema || mysql || student || test |+--------------------+4 rows in set (0.00 sec) mysql> grant all on student.* to dba@'student%' identified by 'ding';Query OK, 0 rows affected (0.13 sec) mysql> flush privileges;Query OK, 0 rows affected (0.00 sec) mysql> Select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | 0E16069C30DC5FEDD519C3C83F73946CA578BCD4 || root | 127.0.0.1 | || dba | student_% | 0E16069C30DC5FEDD519C3C83F73946CA578BCD4 |+------+-----------+-------------------------------------------+3 rows in set (0.00 sec)

时间:2013-10-30 05:10:00 作者:beebol 标签: 分类: Mysql