How to create a database and database user from command line

This is actually the base of MySQL command usage. By the help of a control panel we can simply create a database, create DB users, edit variables, set previeages etc. But in some situations it is necessary to manage these MySQL options via command line. Here I am explaining the commands for creating database and database user form command line.

Enter the mysql prompt by following the step below:

# mysql -u root -p

It then prompts for password. Enter the MySQL root password.

Example:

root@server [/]# mysql -u root -p
Enter password:

MySQL command to create a DB(Database).

mysql> CREATE DATABASE name_of_the_database;

Example:

mysql> CREATE DATABASE crybit_wp;
Query OK, 1 row affected (0.04 sec)

You can verify it by:

mysql> SHOW DATABASES;

MySQL command to create a DB user.

mysql> CREATE USER name_of_the_database_user;

Example:

mysql> CREATE USER crybit_wp;
Query OK, 0 rows affected (0.07 sec)

You can find out the details of all users from mysql.user file:

mysql> select user from mysql.user;
mysql> select host, user, password from mysql.user;
mysql> select * from mysql.user;

Access the database

mysql> use name_of_the_database;

Example:

mysql> use crybit_wp
Database changed

That’s it 🙂

Related:
Reset mysql root password from command line
How to allow mysql client to access remote mysql databases

Post navigation

Arunlal Ashok

DevOps Engineer. Linux lover. Traveller.
Always happy for an open discussion! Write to arun ((@)) crybit ((dot)) com.

Leave a Reply

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