【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';

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

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

| % | 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服务器，来使新设置生效。