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';
Now set permission to user
awesome_user for access your database
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';