As I come across various tutorials and installation instruction manuals across the Web, there's one thing I see quite often: the unnecessary use of the "FLUSH PRIVILEGES" statement.
MySQL access control is based on grant tables, which are loaded in on startup and persist in memory. They're used whenever a new connection is made, or a statement is run, to ensure that the user is permitted to take the requested action.
There are two ways of modifying the grant tables:
- Using the account management statements (GRANT/REVOKE/etc.)
- Modifying the grant tables directly (with INSERT/UPDATE/DELETE statements)
The FLUSH PRIVILEGES statement simply forces a reload of the grant tables. The MySQL manual, even as far back as version 5.5, is pretty clear on the subject:
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.
What I see a lot of is people using GRANT statements, then running FLUSH PRIVILEGES "just to make sure". The only time you'd need to run FLUSH PRIVILEGES yourself is if you've changed the grant tables directly - which would be a pretty weird thing to do, apart from in a few very specialised cases. It isn't necessary, and can potentially cause nasty surprises if someone else has been modifying the grant tables - something the MySQL manual warns against:
Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is discouraged and done at your own risk. The server is free to ignore rows that become malformed as a result of such modifications.
Prior to MySQL 8.0, the grant tables were stored in MyISAM tables, which are non-transactional - by issuing a FLUSH PRIVILEGES statement, you could potentially load in somebody else's half-finished modifications, which may lock a user (or users) out. Also, with no rollback facility, if somebody had started modifying the grant tables but abandoned it, your FLUSH PRIVILEGES would load them in regardless - a nasty surprise which would have happened at next server start anyway.
So, just to be clear:
If you use the proper account management statements, you do not need to FLUSH PRIVILEGES manually. If you're modifying the grant tables directly, and/or running FLUSH PRIVILEGES manually, you're probably doing something wrong.