How to create MySql db and username via CLI

    Posted in Linux Servers on Jun 23, 2018

    Login to mysql

    mysql -u <username> -p

    Creating a database

    create database database_name;

    put database name at the place of database_name.

    Creating a user

    create user 'username'@'localhost' IDENTIFIED BY 'passwordhere';

    Grant privileges to user on database

    grant all privileges ON database_name .* TO 'username'@'localhost';

    TO grant on all databases need to issue below query

    grant all privileges ON *.* TO 'username'@'localhost';

    The asterisks(*) in above query is representing all database(first asterisks) and all tables of that databases(second asterisks).

    Grant privileges with remote access

    grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;

    The % represent any hosts

    Reloading privileges

    flush privileges;

    View databases

    show databases;

    Drop databases

    drop database database name;

    View users with host permission

    select user, host from mysql.user;

    Delete a user

    drop user 'username'@'localhost';

    if user with any host permission

        drop user 'username'@'%';