Sometimes we don’t get access to database client then we have only one option to create database is command line, But developers don’t like memorize commands so here we will find all necessary commands to create a MySQL database, create a new user and assign this user to newly created database also grant permission to the new database user.

Before we start we must login to MySQL shell, to do that we can run the command below.

$ sudo mysql -u root

Show Existing Databases

May be we wants to check first what databases are we have.

mysql> SHOW DATABASES;

Create New Database

Now we are in a point where we will a new database.

mysql> CREATE DATABASE awesome_db;

Create New MySQL User

You should create a new MySQL user to access your database CRUD operation.

mysql> CREATE USER 'awesome_user'@'localhost' IDENTIFIED BY 'strong-password';

Grant Permission

Now set permission to user awesome_user for access your database awesome_db

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, REFERENCES ON awesome_db.* TO 'awesome_user'@'localhost';

Great! Now we are ready to go. We can user our new database in any application.

Grant FILE Global Privilege to User

This is an optional command, if you run this command reports will be archived faster.

mysql> GRANT FILE ON *.* TO 'awesome_user'@'localhost';