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

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

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.

• Author
• Recent Posts

raindog308

Dread Lord of LowEnd Content at LowEndBox

I'm raindog308, techno polymath and long-time LowEndTalk community Administrator. My technical interests include all things Unix, perl, python, golang, shell scripting, vintage operating systems such as MVS, and relational database systems such as Oracle, PostgreSQL, and MySQL.

I also enjoy using LEBs! I have an empire of little guys for VPNs, larger guys for apps, and big guys for databases and storage. When I'm not in front a screen I'm into German Shepherd dogs, quality knives, target shooting, theology, tabletop roleplaying games, and forest hiking.

I enjoy writing technical articles here on LowEndBox to help people get more out of their systems. You can find me on LowEndTalk @raindog308

Latest posts by raindog308 (see all)

• MetWeb has a 30% Off Deal on Cheap VPS Offers in Utah for Our Readers! - December 21, 2024
• Is Your Soul as Dark as a Christmas Stocking’s Coal?Make Your Online World Match - December 20, 2024
• Hosteroid has a HOT, Limited Stock Offer in Vienna or Amsterdam! - December 19, 2024