MySQL logins, passwords, and hosts

“I needed a password eight characters long so I picked Snow White and the Seven Dwarfes.” — Nick Helm1

MySQL Account Management is often nebulous mess to new DBAs. Frank2 is a new MySQL with many years of Linux administration duties in his background and now he has been taking on more duties after his companies previous DBA departed. One of his co-workers was in the middle of a name change and could no longer access the need instance. Frank had run

mysql -u root mysql
mysql>UPDATE user SET Name='jjones' where Name='jsmith';

But Mrs. Jones was not able to access the database. Frank tried for a while to find the answer. To greatly simplify the MySQL authorization process, the sever checks the Host of a new connection, then the User and Password. Frank had checked all three. The name of the host Mrs. Jones used had not changed. Neither had the password. Only the account name.

I gave Frank my best Zen-like answer, “Flush privileges.”

“Is that it? ‘Flush privileges’ with a semi-colon at the end?” He gasped.

“Semi-colon or back-slash followed by a letter ‘G’ of either case.” He gave the simple two word command and suddenly Mrs. Jones was back into her tables3

  1. Best Jokes from the 2011 Fringe
  2. Name changed to protect the promised quantity of adult beverage for solving problem.
  3. Some administrative tools such as Workbench would have avoided this problem by performing the flush for the DBA. But Frank is trying to learn the CLI.

6 Responses to MySQL logins, passwords, and hosts

  1. Gerry says:

    I haven’t tried changing a name, but when issuing a GRANT (or REVOKE) in version 5.1 or higher FLUSH PRIVILEGES is no longer necessary.

    My $.02

    • lgblgblgb says:

      As far as I know (at least with recent versions) flush privileges is only needed if you directly edit privilegie oriented tables. If you use the “standard” grant/revoke/etc, it’s not needed. Correct me, if I am wrong here.

  2. Shlomi Noach says:

    This is what RENAME USER is for. It both updates the login/host and flushed privileges. Tampering directly with mysql.user is usually best avoided.

  3. […] years of Linux administration duties in his background and now he has been taking on more duties… Read more… Categories: MySQL     Share | Related […]

  4. Daniël van Eeden says:

    The “FLUSH HOSTS” could sometimes also fix login issues when the maximum number of wrong logins is exceeded. This limit is configured with max_connect_errors.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: