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

Doug Support 20 Comments

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';

Comments 20

  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. Pingback: How do I create a MySQL database? - Open Classifieds

  5. Pingback: Setting up automated database backups for your WordPress, Drupal, or Joomla website | KiloRoot.com

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

  7. Pingback: MySql Commands | marcus talcott

Leave a Reply

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