Create a MySQL Database, Username, Password, and Privileges from the command line

Step 1: Login to MySQL ( you will need an account )

user@server:~$ mysql -u mysql_user -p
Enter password:

Step 2: Create the Database

mysql > create database db_name;

Step 3: Verify that it’s there

mysql > show databases;

Step 4: Create the User

mysql > create user db_user;

Step 5: Grant privileges while assigning the password

mysql > grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';

*Note: The localhost field usually doesn’t have to be edited, but you can set it to the specific address.

The above example grants all privileges, obviously. But you will likely want to limit privileges under many circumstances. These parameters include select, insert, and delete.
Choose all that apply and separate by comma:

mysql > grant select, insert, delete, update on db_name.* to 'db_user'@'localhost' identified by 'db_password';


19 Responses to “Create a MySQL Database, Username, Password, and Privileges from the command line”

  • Homepage says: Jan 29, 2013 at 6:18 pm

    ... [Trackback]... [...] Read More here: [...]...

  • Harish says: Mar 25, 2013 at 9:18 pm

    ThanQ..... it worked for me.

  • Besnik Br. says: Jun 5, 2013 at 12:46 pm

    Thanks a lot! Its very useful!

  • URL says: Jun 19, 2013 at 4:01 pm

    ... [Trackback]... [...] Read More: [...]...

  • Faisal Rehman says: Jul 23, 2013 at 8:22 pm

    Thanks for a lot. It is very useful .I need it realy. Thanks

  • sonu says: Aug 5, 2013 at 1:24 am

    Thanks a lot! it is very nice article.

  • madhu says: Aug 9, 2013 at 10:55 am

    i encountered with an error saying "fatal error call to undefined function mysql_connect() in addemail.php on line 8" how to rectify this??? how can i connect to mysql?

  • Brian Beattie says: Aug 21, 2013 at 2:00 am

    can't get it to accept the mysql inputs

  • How do I create a MySQL database? - Open Classifieds says: Feb 24, 2014 at 1:43 am

    […] via […]

  • Setting up automated database backups for your WordPress, Drupal, or Joomla website | says: Apr 17, 2014 at 9:25 am

    […] REFERENCES:… […]

  • Yogesh Jadhav says: Jul 3, 2014 at 10:01 am

    Very useful, Very nice and it worked for me.

  • Nagasai says: Sep 13, 2014 at 8:25 pm

    thanks a lot buddy... works perfectly

  • Justin Hellings says: Nov 11, 2014 at 3:42 am

    The commands in this blog post previously worked for me without a problem. the behaviour is different on my latest computer (running Linux Mint 17 based on Ubuntu 14.04LTS) Step 4 mysql > create user db_user; creates a user with host '%' Step 5 mysql > grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password'; creates a second user with host 'localhost' Both users are denied access to the new database. Manually changing the password through PhpMyAdmin for the user with host 'localhost', gave that user access to the database. Conclusions: On the verison of MySQL I'm using 1) step 4 seems to be redundant, and 2) step 5 seems to mangle the password somehow. (When I logged in, I tried putting the quotation marks around the password too but that didn't work either.) MySQL client(?) version: $ mysql -V mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3

  • MySql Commands | marcus talcott says: Dec 17, 2014 at 3:51 pm

    […]… […]

  • Wandi says: Feb 25, 2015 at 6:20 am

    Thanks a lot, very helpful

  • Naveen says: Oct 11, 2015 at 5:29 pm

    Thanks a lot buddy. It worked perfect for me.

  • Antonio says: Apr 8, 2016 at 9:25 am

    Sorry but the prompt says ERROR 1046 :no database selected

    • Antonio says: Apr 8, 2016 at 9:26 am

      There're are no dashes or dot in my db name

  • BalaSundaram Chinniyan says: May 3, 2016 at 8:00 pm

    Thanks Lot

Leave a Comment