How to create a MySQL database with the command line and set privileges to user

In order to manipulate users and privileges from users in MySQL you will need to access the MySQL console as root/admin or an user with elevated privileges:

# Start MySQL console as root user and prompt for password
mysql -u root -p

This will start the console as the root user to execute queries from the CLI. Here you will able to grant access to any database to other users using the queries that we've wrote in this article.

1. Create database

Initially, you need a database to grant permissions on, so in case that you don't have any or you're learning, create a database using the following statement:

create database MyDatabase;

In this case the name of our database is MyDatabase.

2. Grant usage to user with password

As next you need to allow the access to the database, that in our case is MyDatabase to an user, which in this case is MyDatabaseUsername and is identified with MyPassword:

grant usage on *.* to MyDatabaseUsername@localhost identified by 'MyPassword';

3. Grant all privileges to database for user

Finally grant all privileges for the database to the previously granted user:

grant all privileges on MyDatabase.* to MyDatabaseUsername@localhost;

Now you should be able to access the database with the specified user in MySQL (in PHP applications etc).

Happy coding !