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

Step 1: Login to MySQL ( you will need an account )
[code]user@server:~$ mysql -u mysql_user -p
Enter password:[/code]

Step 2: Create the Database
[code]mysql > create database db_name;[/code]

Step 3: Verify that it’s there
[code]mysql > show databases;[/code]

Step 4: Create the User
[code]mysql > create user db_user;[/code]

Step 5: Grant privileges while assigning the password
[code]mysql > grant all on db_name.* to ‘db_user’@’localhost’ identified by ‘db_password’;[/code]
*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:
[code]mysql > grant select, insert, delete, update on db_name.* to ‘db_user’@’localhost’ identified by ‘db_password’;[/code]


Doug is an experienced project leader who strives to keep our clients' projects flowing smoothly. As a Developer, Doug is able to ensure a high level of customer satisfaction by focusing his efforts on the setup & support of content management systems (i.e. WordPress & Drupal), custom theme development, CSS/HTML coding, and graphic design. When Doug is not working on web projects, he can be found performing with several professional bands around San Diego.

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

  1. Pingback: Homepage
  2. Pingback: URL
  3. 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?

  4. 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

Leave a Reply

Your email address will not be published. Required fields are marked *