【Mysql】flush privilges 刷新系统权限相关表

对mysql.user进行手工操作，比如添加新用户或更改用户的旧密码后，需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问！

以root身份登录数据库，创建用户yangql

mysql> create user yangql  identified by 'yangql';

Query OK, 0 rows affected (0.02 sec)

mysql> exit

Bye

测试连接。

[root@rac3 home]# mysql -uyangql -pyangql

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| test               | 

+--------------------+

2 rows in set (0.00 sec)

mysql> exit

Bye

以root用户登录，手工修改mysql.user表，这里演示更新用户的密码，对于插入新建用户操作类似！

[root@rac3 home]# mysql 

mysql> show databases;                            

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| latin              | 

| lyz                | 

| momo               | 

| mysql              | 

| test               | 

| test2              | 

| yangdb             | 

| yangutf            | 

+--------------------+

9 rows in set (0.00 sec)

mysql> update mysql.user set password=password('yql') where user='yangql'; 

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mysql.user where user='yangql';

+------+--------+------------------+-------------+

| Host | User   | Password         | Select_priv |

+------+--------+------------------+-------------+

| %    | yangql | 66d7dc1c3dfc53e6 | N           |

+------+--------+------------------+-------------+

1 row in set (0.00 sec)

mysql> exit

Bye

没有使用flush privilges之前，可以使用老的密码进行登录。

[root@rac3 home]# mysql -uyangql -pyangql

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| test               | 

+--------------------+

2 rows in set (0.00 sec)

mysql> exit

Bye

执行刷新操作：

[root@rac3 home]# mysql

mysql> flush privilges;//刷新系统权限相关的表

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'privilges' at line 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> 

再次使用旧密码登录

[root@rac3 ~]# mysql -uyangql  -pyangql

ERROR 1045 (28000): Access denied for user 'yangql'@'localhost' (using password: YES)

再次使用新密码登录

[root@rac3 ~]# mysql -uyangql  -pyql

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| test               | 

+--------------------+

2 rows in set (0.00 sec)

mysql> exit

Bye

[root@rac3 ~]# 

还有一种方法，就是重新启动mysql服务器，来使新设置生效。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-709836/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-709836/