How to Create MySQL Databases, User Accounts and Grant Privileges

John on June 23, 2020

MySQL is a powerful open-source relational database system that can be used to manage databases of various sizes.

In this tutorial we are going to look at how to create databases, create users and grant those users database permissions in MySQL. The article assumes that you have already got MySQL installed on your server and it has been configured.

Log into MySQL Server

Firstly you will need to log into your MySQL server as the root user.

mysql -u root -p

You will then be asked to enter the password you created for the root user account during the MySQL installation process. 

Create a MySQL Database

From the MySQL console, we are going to create a database. This following command consists of two main parts; the name of the database and its default character encoding.

CREATE DATABASE newdatabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Before entering the above command replace newdatabase with the name you wish to call your database.

Create a MySQL User and Grant Permissions

Now we are going to create a new user at localhost, set a password and grant it permissions to access the database. You can change localhost to whatever your particular set-up is though by default localhost should work for most cases.

GRANT ALL ON newdatabase.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';

Before running the above command you should change newdatabase to the name of your previously created database, dbuser should be changed to a desired one and the same should be done for the password.

Pro-tip - You should use a strong password generator like this one to create a random password and store it in a secure location. The username shouldn't be something super generic like “admin” either.

MySQL Privileges

In the above command, we created a user and granted it all permissions on the database. “All" means that all of the following functions are granted:

• CREATE - can create databases and tables
• DROP - can drop database tables
• DELETE - can delete records (rows) from a table
• INSERT - can insert records (row) into tables
• SELECT - can read the database
• UPDATE - can update rows

If there are cases where you don't want to grant users all permissions, you can add permissions individually separated by commas.

GRANT CREATE, SELECT, UPDATE ON newdatabase.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';

Flush MySQL Privileges

Next, we need to flush MySQL privileges to update the MySQL server on the changes we just made.

FLUSH PRIVILEGES;

Now we just need to leave the MySQL console by typing the following command:

EXIT;

Conclusion

You have successfully set-up a new database and user and granted necessary privileges for that user to manipulate the contents of the database.