Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I create a user and specify a password in one line:

I thought CREATE USER 'foo'@'' IDENTIFIED BY 'bar'; would do it but apparently it doesn't.

Any thoughts? I'm on version 5.1.49 of MySQL.

share|improve this question
I am curious what you mean "apparently it doesn't"...your command should create the user (with only the ability to log on from and no other privileges) why do you say it doesn't create it? – Derek Downey Jan 7 '13 at 22:22

Your syntax is the good one :

[root@localhost] [(none)] mysql> CREATE USER 'foo'@'' IDENTIFIED BY 'bar';
Query OK, 0 rows affected (0.00 sec)

You can see if your user is created by using :

[root@localhost] [(none)] mysql> show grants for 'foo'@'';
| Grants for foo@                                                                                   |
1 row in set (0.00 sec)

But it'll work only if you connect from


share|improve this answer
you ... really didn't answer the question. – jcolebrand Jan 5 '13 at 5:27
Oh really? Sorry about that! He asked for how to create a user with a password, i did. Next i gave my opinion on his and finaly i asked him his exact problem... – Maxime FOUILLEUL Jan 5 '13 at 9:04
Ok, let's try again. The question states that he tried that, and it didn't let him use the password that he provided. Therefore, he's asking why it didn't work. It's entirely possible that he actually supplied, but I bet that's shorthand-example, just as foo and bar are. Maybe what you meant to do was to leave a comment on the question asking what the exact error is? – jcolebrand Jan 5 '13 at 9:16
Looking at this, I can say you are pretty close. However, there is another method. Your command works but is functionally incomplete. The user can login after the user's creation but that's all. Your SHOW GRANTS reveals that. Since mysql's default sql_mode allows non-ANSI stuff, you could pull it off the GRANT command. That will do three things: 1) create the user, 2) set the password, 3) set the privs IN ONE COMMAND. Check the MySQL Doc . It says if NO_AUTO_CREATE_USER is set, two commands needed. Otherwise, the one GRANT can do it. – RolandoMySQLDBA Jan 6 '13 at 5:02
I am going to give you a +1 for what you contributed because doing this in one command is not widely known unless one is fully aware of MySQL's non-ANSI shortcuts and caveats. As written, you answer does answer the question as posed in its context. – RolandoMySQLDBA Jan 6 '13 at 5:07

For the change is applied immidiately, you must use flush privileges to flush all memory cache of user table. Finally, you should make sure your client is

share|improve this answer
There is no need to do FLUSH PRIVILEGES if you use the CREATE USER or GRANT keywords, only if you directly modify the mysql.* tables. – jynus Aug 1 '14 at 8:33
Right! In the case, flush privileges is redundancy. I have read it ""; – Luke Nguyen Aug 1 '14 at 9:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.