How to Create MySQL Databases and Users Via the Command Line

This article explains how to create a new MySQL database and user using the command line. If MySQL is not installed yet, follow our guide to install MySQL on CentOS 7 first.

Quick Steps:

1.  Login to the Database Server as the root user. 
2.  Create a new user with the command “CREATE DATABASE databasename;“
3.  Select the new database using the command “USE databasename;“
4.  Create a new user for the database by running the command  “CREATE USER ‘unsername’@’localhost’ IDENTIFIED BY ‘password’;“
5.  Grant permissions to the user via the command “GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’;“

Verify MySQL is installed.

mysql --version

Login to the MySQL server as the root user.

mysql -u root -p

You will be asked for your root’s password, provide the password and then press the “Enter” key.

Now, create a database with the command below.

CREATE DATABASE veerotechDB;

The database “veerotechDB” is now created. Select the new database with the following command.

use veerotechDB;

The database “veerotechDB” has been selected. Create a new user for the database “veerotechDB” with the command below.

CREATE USER 'veerotech'@'localhost' IDENTIFIED BY 'veerotech_pw';

In the above syntax, “veerotech” is the user and “veerotech_pw” is the password. Now, verify whether the user is created successfully using the command below.

Select host, user, password from mysql.user;

A generic output for the above command is shown below.

+-----------+----------------+-------------------------------------------+
| host | user | password |
+-----------+----------------+-------------------------------------------+
| localhost | root | *C2BD8E7A5247DF69A9A8CB29C8C6E8FC83D3681F |
| 127.0.0.1 | root | *C2BD8E7A5247DF69A9A8CB29C8C6E8FC83D3681F |
| ::1 | root | *C2BD8E7A5247DF69A9A8CB29C8C6E8FC83D3681F |
| % | veerotech | *32D8ED777E1B90734ED5A6AFCD0E354230826743 |
| % | rest_demo | *3B8DD81985A42FD9B56133326F3B25A2985A3F75 |
*** | localhost | veerotech | *68DC5C435B9AAA7280CA4C89391C28EFEEC0E946 |***
| localhost | veerotech_user | *F776A21503EFA57908FEF30C914DFB9A9FC78EF3 |
+-----------+-------------+----------------------------------------------+

You can see the details about the new user in the table, the password will be in encrypted form.

Add privileges for the user “veerotech” to the database “veerotechDB“.

First, lets check for the privileges assigned.

SELECT host, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, alter_priv, password FROM mysql.user WHERE user='veerotech';

The output is shown below.

+-----------+------------+-------------+-------------+-------------+-------------+-------------+------------+-
| host | user | select_priv | insert_priv | update_priv | delete_priv | create_priv | alter_priv |
+-----------+------------+-------------+-------------+-------------+-------------+-------------+------------+-
| localhost | veerotech | N | N | N | N | N | N |
+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+-

“N” means no privileges

Adding privileges to the user ‘veerotech” using the command below.

GRANT ALL PRIVILEGES ON *.* TO 'veerotech'@'localhost';

Check for privileges again.  You should see multiple letter “Y’s ” which indicates that the permissions were set conrrectly. 

SELECT host, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, alter_priv, password FROM mysql.user WHERE user=’veerotech’;

Output.

+———–+————+————-+————-+————-+————-+————-+————+
| host | user | select_priv | insert_priv | update_priv | delete_priv | create_priv | alter_priv |
+———–+————+————-+————-+————-+————-+————-+————+
| localhost | veerotech | Y | Y | Y | Y | Y | Y |
+———–+——+————-+————-+————-+————-+————-+————+

To exit from MySQL, you  type “exit” and enter.

Our Guiding Principles

• Provide consistent, stable, and reliable web hosting services.
• Ensure rapid ticket response and quick resolutions to issues.
• Never saturate or over-provision servers to ensure stability and speed for our customers.
• Use only high-quality enterprise-class hardware to ensure minimal downtime from hardware failures.
• Provide clear pricing with no hidden fees or gotchas.