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

User · Answer

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

User · Answer

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

User · Answer

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 gt  update  user set password PASSWORD  test 23   where user  alex    mysql gt  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 gt  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

User · Answer

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

[mysql] MySQL: When is Flush Privileges in MySQL really needed?

Examples related to mysql