LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

Stop the Senseless Flushing!

MySQL Dolphin

If you Google ten random MySQL/MariaDB tutorials that involve setting up a database, 9 out of 10 will go something like this:

CREATE USER 'someone'@'localhost' identified by 'secret';
GRANT ALL on somedb.* to 'someone'@'localhost';

Guess what?  That last command is completely unnecessary.

Legions of MySQL users (from professional DBAs to devs who just need to store crap in a table) have learned that after granting, you must flush privileges.  But that has never been true.

Let’s go waaay back to MySQL 3.26.  That’s about 2001.  The Earth’s crust was just cooling.  In the manual we read:

when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

In MySQL 4, a section entitled “When Privilege Changes Take Effect” clarifies matters:

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

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. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command.

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 do not seem to make any difference!

This languages continues (without the final quip, alas) in the current manual.

So the rule is quite simple:

  • If you GRANT privileges with the GRANT command, then GRANT will take care of any flushing necessary.  You do not need to flush privileges.  This is the norm.
  • If you use INSERT, UPDATE, or DELETE on the grant tables – which the current manual says “is not recommended” – then you would have to manually FLUSH TABLES.

In other words, if you do something very unusual – which the manual says not to do! – then you have to manually flush.  Otherwise, you don’t have to worry about it.

I can’t really think of a reason why you would need to directly modify the tables, though I’m sure there is some corner case out there and someone will let us know in the comments.




  1. Mario:

    Nice to know!

    February 27, 2023 @ 1:56 pm | Reply
  2. Wadih:

    I’ve been noticing over the years that adding flush or not adding it seemed to always work, good to have confirmation! :)

    May 15, 2023 @ 7:54 pm | Reply

Leave a Reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *