Call my on Skype Follow me on Twitter Join me on Facebook Connect with LinkedIn Hire me on UpWork
Richard Wallman - a MySQL® DBA (database administrator)


Blog: Things you probably don't need to do: FLUSH PRIVILEGES
(2018-07-05 11:47:12)
Blog: Why isn't MySQL using the index I expect?
(2018-06-06 10:05:48)
Going to change my policy towards NDAs - I have no problem with them, unless they gag me and create empty spaces in my work history
(2017-08-20 14:27:45)
My week in tags: #cpp #VideoStreaming #handlebarsjs #mongodb #español #holiday #family #perl #nda
(2017-07-02 22:40:00)
Lesson of the week: #MongoDB update() is not the same as SQL UPDATE. It's okay - I didn't need that document anyway 😉#rtfm
(2017-06-25 20:47:31)
My week in tags: #MEAN #MongoDB #JavaScript #NodeJS #expressjs #mustache #PHP #ceph #debian #cpp #html5 #VideoStreaming #fpga #verilog
(2017-06-25 20:38:53)
That was "fun" - setting up new @Percona #MySQL cluster, initial SST kept failing. Culprit: "skip-name-resolve" makes sstuser@localhost fail
(2017-06-20 22:17:45)
Hoping to finally find some time to have a quality play with @percona #MongoDB this weekend - only tested successful install so far
(2017-06-17 15:45:26)
Starting to clear old contracts/NDAs - been quite a year, doing coding, providing training, working with NoSQL databases. Full stack indeed!
(2017-04-21 21:32:02)
Just read the MySQL/HAProxy tutorial on @digitalocean - it's so awful it literally took my breath away for a second. Top result in Google :(
(2016-04-26 15:02:46)

Things you probably don't need to do: FLUSH PRIVILEGES

Posted on 2018-07-05 @ 10:00:00

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:

  1. Using the account management statements (GRANT/REVOKE/etc.)
  2. 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.

Comments from visitors

Leave your own comment