MySQL: When is Flush Privileges in MySQL really needed?

Question

When creating new tables and a user to go along with it, I usually just invoke the following commands:

CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY "mypassword";

I have never ever needed to utilize the FLUSH PRIVILEGES command after issuing the previous two commands. Users can log in and use their database and run PHP scripts which connect to the database just fine. Yet I see this command used in almost every tutorial I look at.

When is the FLUSH PRIVILEGES command really needed and when is it unnecessary?

It's a good thing to try when stuff doesn't work, but I haven't seen it as a necessary thing since at least MySQL 5.0. — tadman, Apr 06 '16 at 23:16
`mytable.*` uses `mytable` as a database name, not a table name. — Barmar, Apr 06 '16 at 23:31

Sanj · Accepted Answer · 2016-04-06T23:35:31.477

Privileges assigned through GRANT option do not need FLUSH PRIVILEGES to take effect - MySQL server will notice these changes and reload the grant tables immediately.

From MySQL documentation:

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Thanks, though I wonder why so many tutorials specifically say you need to use `flush privileges` after a `GRANT` command. — kojow7, Apr 07 '16 at 00:51
Can you please advise on the command to use, when i write flush privileges nothing happens — Ilan, Mar 30 '18 at 13:23
@kojow7 I don't have any hard facts backing this, but I _believe_ that back in the 3.23:ish days of MySQL, it was in fact necessary. So that's probably why it's left in the documentation as a remnant of "how it used to be". — Per Lundberg, Aug 02 '18 at 12:10

score 81 · Answer 2 · edited Jun 25 '19 at 18:42

81

TL;DR

You should use FLUSH PRIVILEGES; only if you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE.

edited Jun 25 '19 at 18:42

codeforester

39,467
16
112
140

answered Nov 07 '17 at 10:41

simhumileco

31,877
16
137
115

6

Nice and concise. – kiwicomb123 Nov 14 '17 at 14:47

score 20 · Answer 3 · answered Jun 26 '17 at 10:37

Just to give some examples. Let's say you modify the password for an user called 'alex'. You can modify this password in several ways. For instance:

mysql> update* user set password=PASSWORD('test!23') where user='alex'; 
mysql> flush privileges;

Here you used UPDATE. If you use INSERT, UPDATE or DELETE on grant tables directly you need use FLUSH PRIVILEGES in order to reload the grant tables.

Or you can modify the password like this:

mysql> set password for 'alex'@'localhost'= password('test!24');

Here it's not necesary to use "FLUSH PRIVILEGES;" If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Thank you for a concrete example. – kojow7 Jun 26 '17 at 15:39 — kojow7, Jun 26 '17 at 15:39

score 3 · Answer 4 · answered Nov 25 '18 at 23:43

2 points in addition to all other good answers:

1:

what are the Grant Tables?

from dev.mysql.com

The MySQL system database includes several grant tables that contain information about user accounts and the privileges held by them.

clariﬁcation: in MySQL, there are some inbuilt databases , one of them is "mysql" , all the tables on "mysql" database have been called as grant tables

2:

note that if you perform:

UPDATE a_grant_table SET password=PASSWORD('1234') WHERE test_col = 'test_val';

and refresh phpMyAdmin , you'll realize that your password has been changed on that table but even now if you perform:

mysql -u someuser -p

your access will be denied by your new password until you perform :

FLUSH PRIVILEGES;

MySQL: When is Flush Privileges in MySQL really needed?

4 Answers4

Linked